Summary of essential knowledge points for MySQL query optimization

Preface

Query optimization is not something that can be achieved overnight. You need to learn how to use the corresponding tools, learn from others' experience to optimize SQL, and improve yourself.

Let's first consolidate the advantages of indexes: fast data retrieval, stable query, sequential storage to avoid the server creating temporary tables, and turning random I/O into ordered I/O.

However, if the index is not created in a standardized manner, it will cause the following problems: it will take up extra space, waste memory, and reduce the performance of adding, deleting, and modifying data.

Therefore, efficient indexes can only be created based on understanding the index data structure.

**All operations in this article are performed in MySQL 8.0.12**

1. Create index specifications

Before learning index optimization, you need to have a certain understanding of the specifications for creating indexes, which come from the Alibaba Development Manual.

Primary key index: pk_column_column

Unique index: uk_column_column

Common index: idx_column_column

2. Reasons for Index Failure

When creating an index, you need to know under what circumstances the index will fail. Only by understanding the reasons for index failure can you avoid some known errors when creating the index.

1. The leader cannot die

This classic statement covers the fact that when creating an index, you must comply with the leftmost principle.

For example, the table structure is u_id，u_name，u_age，u_sex，u_phone，u_time

Create an index called idx_user_name_age_sex .

The query condition must include the u_name column.

2. Do not perform any operations on the index column

Do not perform any calculations, functions, automatic or manual type conversions on the index columns, otherwise a full table scan will be performed. In short, don't do any operations on the index column.

3. The types of the two sides are not the same

For example, the index idx_user_name is created, and the name field type is varchar

When querying, use where name = kaka . This query method will directly cause the index to fail.

The correct usage is where name = "kaka" .

4. Inappropriate like queries can cause index failure

Create an index called idx_user_name

The execution statement is select * from user where name like "kaka%"; and the index can be hit.

The execution statement is select name from user where name like "%kaka"; the index can be used (only in versions above 8.0).

The execution statement is select * from user where name like ''%kaka"; which will directly cause the index to fail.

5. The index after the range condition will become invalid

Create an index called idx_user_name_age_sex

Execute the statement select * from user where name = 'kaka' and age > 11 and sex = 1;

The above SQL statement will only hit the name and age indexes, and the sex index will be invalid.

If a composite index fails, you only need to check the length of key_len.

Summary: % The index will be commanded later. When a covering index is used, any query method can hit the index.

The above is Kaka’s summary of the reasons why indexes may fail. In many articles, the MySQL version is not marked, so you may see the conclusion that is null or or indexes may fail.

3. Explain, the killer feature of SQL optimization

After writing the SQL statement, one thing you must do is to use Explain to check the SQL statement to see whether it hits the index.

The following figure shows the output format using explain. The output format will be briefly explained below.

1. The id column is the query ID. If there is no subquery or joint query in the query statement, this ID is always 1.

If there is a subquery or a union query, this number will be incremented.

2.select_type

The most common types are SIMPLE and PRIMARY, and you only need to know these columns.

3.table

Just understand it as the table name

4. **type

This column is one of the most important columns to pay attention to when optimizing SQL statements. This column shows what type the query uses.

The following are ranked from best to worst.

• system: There is only one row of data in the table
• const: at most one record will match, often used for primary key or unique index conditional query
• eq_ref: appears when the index used by the connection is the primary key and unique
• ref: Using normal indexing = or <=> operators for comparison will result in
• fulltext: Use full-text index
• ref_or_null: Similar to the ref type, but adds a null value check, which is not often used in practice. The statement is where name = 'kaka' and name is null，name is a normal index.
• index_merge: The query statement uses more than two indexes. This is common when using and or or. The official document puts this type after ref_or_null. However, in many cases, the performance may not be as good as range due to too many indexes being read.
• unique_subquery: used for in query in where, completely replaces subquery, which is more efficient. The statement is value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery: The combination of fields returned in the subquery is an index (or index combination), but not a primary key or unique index
• range: Index range query, commonly used in queries using operators such as =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN(), or like.
• index: full table scan of the index, scan the index from beginning to end
• all: full table scan, worst performance.

5.possible_keys

This column shows the possible indexes that may be used.

6. **key

The index hit by the optimizer from possible_keys

7.key_len

The length (number of bytes) of the index used for the query. key_len only calculates the length of the index used in the where condition. Even if the index is used for sorting and grouping, it is not calculated in key_len.

8.ref

If you are using a constant equal value query, const will be displayed here.

If it is a join query, the execution plan of the driven table will display the associated fields of the driving table.

If the condition uses an expression or function, or an internal implicit conversion occurs in the condition column, it may be displayed as func.

9. **rows

This is MySQL's estimate of the number of rows that need to be scanned (not an exact value).

This value directly shows the efficiency of SQL. In principle, the fewer rows, the better.

10.filtered

This column indicates the proportion of the number of records that meet the query after the data returned by the storage engine is filtered at the server level. Note that it is a percentage, not a specific number of records.

11. **extra

In most cases the following situations occur.

• Using index: A covering index is used, and the query columns are all index fields.
• Using where: Using the where statement
• Using temporary: A temporary table is used when sorting query results.
• Using filesort: Use an external index to sort the data
• Using index condition: Index pushdown is used. For more information about index pushdown, please refer to Kaka’s previous article MySQL Index

12. Summary

The above is the description of all columns in Explain. In the normal development process, we usually only pay attention to the type, key, rows, and extra columns.

• The type optimization goal must reach at least the range level, and the requirement is the ref level. If consts is possible, that’s best.
• key is the index used for the query. If this column is empty, either the index is not created or the index is invalid.
• rows is the number of rows scanned by this SQL statement, the fewer the better.
• extra: This column is an extended column. If temporary tables or file sorting occur, optimization is required.

4. SQL optimization killer: slow query

As mentioned above, you can directly use explain to analyze whether your SQL statements are reasonable. Next, let’s talk about slow queries.

Check whether slow query is turned on

Check whether SQL statements that do not use indexes are logged

Enable slow query and record SQL statements that do not use indexes

set global log_queries_not_using_idnexes='on';

set global log_queries_not_using_indexes='on';

Check whether the above two configurations are turned on

Set the slow query time, which is controlled by yourself, usually 1 second is enough. set globle long_query_time=1;

If the time has not changed, just reconnect the client.

View the slow query storage location

Then execute any statement that does not execute the index and you can see this statement in this log

In the above figure, the main things to observe are Query_time and SQL statement content.

The above is about how to use slow queries to view SQL statements that have problems in the project.

5. Optimization Method

Here I will talk to you about some commonly used SQL statement optimization solutions. The above two tools should be used well to help us fight monsters.

• It is forbidden to use select *. What fields are needed to query what fields
• where field sets index
• Set index for group by and order by fields
• Abandon offset and limit paging and use delayed association to implement paging (not necessary when the amount of data is not large)
• When writing paging, when count is 0, return directly to avoid executing paging statements
• Use covering indexes to avoid table return
• When creating a composite index, the index with the highest discrimination is placed on the far left.
• To count the number of rows of data, just use count(*) instead of using fancy words.
• Regarding in and exists, if the two tables being queried are of the same size, the performance difference can be ignored. If the subquery table is large, use exist, otherwise use in.
• Add limit 1 when querying a row of data
• Choose a reasonable data type. The smaller the data type, the better.
• A union query joins up to three tables, and the data types of the fields to be joined must be consistent.
• If the in operation can be avoided, try to avoid it. If it cannot be avoided, keep the number of in elements within 1000.
• Columns with frequent data updates and low differentiation are not suitable for indexing.
• The type in explain must be at least range and must be ref
• The joint index satisfies the leftmost principle

VI. Conclusion

This concludes this article on the essential knowledge points for MySQL query optimization. For more relevant MySQL query optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!