Home >
Database >
Mysql Tutorial >
You must understand the summary of the most essential things about SQL optimization
You must understand the summary of the most essential things about SQL optimization
WBOY
Release： 2021-12-23 18:58:31
forward
1739 people have browsed it
This article brings you relevant knowledge about mysql database optimization, I hope it will be helpful to you.
mysql database optimization
Reduce data access: Set reasonable field types and enable Compression, reducing disk IO through index access, etc.
Return less data: Only return required fields and data paging processing to reduce disk IO and network IO
Reduce the number of interactions: Batch DML operations, Function storage, etc. reduce the number of data connections
Reduce server CPU overhead: Minimize database sorting operations and full table queries to reduce CPU memory usage
Utilize more resources: Use table partitioning to increase parallelism Operation, maximize the use of cpu resources
Summary to SQL optimization, there are three points:
Maximize the use of indexes;
Avoid as much as possible Full table scan;
Reduce invalid data queries;
To understand the principles of SQL optimization, you must first understand the SQL execution sequence:
SELECT statement-grammar sequence:
1. SELECT
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>
Copy after login
SELECT statement - Execution order:
FROM
# Select the table and add multiple Table data is transformed into a table through Cartesian product. ON # Filter the virtual table of the Cartesian product JOIN # Specify join to add data to the virtual table after on. For example, left join will add the remaining data from the left table Add to the virtual table WHERE # Filter the above virtual table GROUP BY # Grouping # Used for judgment in having clause. In writing, this type of aggregate function is written in having judgment. of HAVING # Perform aggregate filtering on the grouped results SELECT # The single column returned must be in the group by clause, except for aggregate functions DISTINCT # Data deduplication ORDER BY # Sort LIMIT
SQL optimization strategy
Statement: The following SQL optimization strategy is suitable for scenarios with large amounts of data. If the amount of data is small, there is no need to use this shall prevail, so as not to add any unnecessary extravagance to the matter.
1. Avoid the scenario of not using the index
1. Try to avoid fuzzy queries at the beginning of the field, which will cause the database engine to abandon the index and perform a full table scan. is as follows:
SELECT * FROM t WHERE username LIKE '%陈%'
Copy after login
Optimization method: Try to use fuzzy query after the field. As follows:
SELECT * FROM t WHERE username LIKE '陈%'
Copy after login
If the requirement is to use fuzzy query in front,
Use MySQL built-in function INSTR(str, substr) to match, which is similar to indexOf() in java , query the subscript position where the string appears, please refer to "MySQL fuzzy query usage (regular, wildcard, built-in functions, etc.)"
Use FullText full-text index, use match against to retrieve
data When the amount of data is large, it is recommended to use ElasticSearch and Solr. The retrieval speed of hundreds of millions of data is in seconds. Use like '%xx%'.
#2. Try to avoid using in and not in, which will cause the engine to perform a full table scan.
is as follows:
SELECT * FROM t WHERE id IN (2,3)
Copy after login
Optimization method: If it is a continuous value, it can be replaced by between. As follows:
SELECT * FROM t WHERE id BETWEEN 2 AND 3
Copy after login
If it is a subquery, it can be replaced by exists. For details, see "How to use exists instead of in in MySql" as follows:
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
Copy after login
3. Try to avoid using or, which will cause the database engine to give up the index and perform a full table scan.
is as follows:
SELECT * FROM t WHERE id = 1 OR id = 3
Copy after login
Optimization method: Union can be used instead of or. As follows:
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
Copy after login
4. Try to avoid judging null values, which will cause the database engine to abandon the index and perform a full table scan.
is as follows:
SELECT * FROM t WHERE score IS NULL
Copy after login
Optimization method: You can add a default value of 0 to the field and judge the 0 value. As follows:
SELECT * FROM t WHERE score = 0
Copy after login
5. Try to avoid performing expressions and function operations on the left side of the equal sign in the where condition, which will cause the database engine to abandon the index and perform a full table scan.
Expressions and function operations can be moved to the right side of the equal sign. as follows:
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9
默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。
因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：
SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;
The above is the detailed content of You must understand the summary of the most essential things about SQL optimization. For more information, please follow other related articles on the PHP Chinese website!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
