(3) MySQL advanced SQL optimization (index)


1. Index problem

Index is one of the most commonly used and important methods in database optimization. Index can usually help users solve most SQL performance problems. This chapter will introduce the classification, storage and usage of indexes in MySQL in detail.

2. Storage classification of index

The table data and index of MyISAM storage engine are automatically stored separately, and each is an independent file; The table data and index of InnoDB storage engine are stored in the same table space, but can be composed of multiple files. At present, there are only two storage types of indexes in MySQL (BTREE and hash), which are related to the table storage engine: MyISAM and InnoDB storage engines only support BTREE indexes; Memory / heap storage engine can support hash and BTREE indexes. MySQL currently does not support functional indexes, but can index the first part of the column, such as the inventory table goods in the previous chapter_ stock. Lotno batch field can index only the first four characters of the model. This feature can greatly reduce the size of the index file. When designing the table structure, we can also flexibly design the text column according to this feature. The following is an example of creating a prefix index:

EXPLAIN SELECT * FROM goods_stock WHERE LotNO LIKE '2021%';

--Create prefix index
CREATE INDEX idx_stock_2 ON goods_stock (LotNO(4));

3. How to use index

Indexes are used to quickly find rows with a specific value in a column. Using indexes on related columns is the best way to improve the performance of select operation. The most important condition for query to use index is that the index keyword needs to be used in the query condition. If it is a multi column index, the index can be used only when the query condition uses the leftmost prefix of the multi column keyword, otherwise the index cannot be used.

3.1 use index

In mysql, indexes may be used in the following cases.
For the created multi column index, as long as the leftmost column is used in the query conditions, the index will generally be used. An example is as follows:

--First, in the inventory table goods_ Stock create a composite index idx in the order of model and brand_ stock_ 1 ON goods_ stock (Model,Brand);

Then query the table by model. The specific commands are as follows:

EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358DT';

It can be found that even if the combination of model and brand fields is not used in the where condition, the index can still be used. This is the prefix feature of the index (query according to the order of index columns). However, if the table is queried only by brand criteria, the index will not be used. The details are as follows:

EXPLAIN SELECT * FROM goods_stock WHERE Brand='TI';

For queries using like, if the following is a constant and only the% sign is not in the first character, the index may be used. Take the following two execution plans:

EXPLAIN SELECT * FROM goods_stock WHERE Model LIKE '%LM358';

EXPLAIN SELECT * FROM goods_stock WHERE Model LIKE 'LM358%';

The difference is that the first index can be used, but the second index can’t be used because it can’t be used. In addition, if like is followed by the name of a column, the index will not be used. If searching for large text, use full-text indexing instead of like ‘%…%’.
If the column name is an index, use column_ Index will be used when name is null. In the following example, the index will be used when querying records with lotno null:


3.2 index exists but not used

In the following cases, although there is an index, MySQL will not use the corresponding index.
If MySQL estimates that using indexes is slower than full table scanning, indexes are not used. For example, if the column key_ Part1 is evenly distributed between 1 and 100. It is not good to use indexes in the following queries:

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

If the memory / heap table is used and “=” is not used for the index column in the where condition, the index will not be used. The heap table can only use indexes if ‘=’.
For the condition separated by or, if the column in the condition before or has an index but there is no index in the following column, the indexes involved will not be used, for example:

SHOW INDEX FROM goods_stock;

You can see goods through the command_ The stock inventory table has two indexes, and then we execute the following statement to see whether to use the index:

EXPLAIN SELECT * FROM goods_ Stock where lotno ='20200821 'or packageunit =' package ';

It can be seen that although there is index idx on the lotno column_ stock_ 2. However, this SQL statement does not use this index because there is a condition in or that has no index.
If the column type is a string, you must remember to enclose the character constant value in quotation marks in the where condition. Otherwise, MySQL will not use it even if there is an index on the column, because MySQL does not retrieve the input constant value until it is converted by default. See the following example:

DESC goods_stock;

Through the desc command, we can see the goods_ The lotno field in the stock inventory table is of character type. If we add a condition value with numerical type 20200821 to the lotno field in the SQL statement, MySQL cannot correctly use the index even if there is an index on lotno, but continues to scan the whole table, as follows:

EXPLAIN SELECT * FROM goods_stock WHERE LotNO=20200821;

4. Check index usage

If the index is working, the handler_ read_ The value of key will be very high. This value represents the number of times a row is read by the index value. A very low value indicates that the performance improvement obtained by increasing the index is not high, because the index is not often used. Handler_ read_ rnd_ A high value of next means that the query is inefficient and should be indexed. This value means the number of requests to read the next line in the data file. If a large number of table scans are in progress, the handler_ read_ rnd_ If the value of next is high, it usually indicates that the table index is incorrect or the written query does not use the index. You can refresh the status before querying, as follows:

SHOW STATUS LIKE 'Handler_read%';

The parameters are explained as follows:
Handler_read_first:This option indicates that SQL is doing a full index scan. Note that it is all, not part. Therefore, if there is a where statement, this option will not change.
Handler_read_key:If the value of this option is very high, MySQL uses the index efficiently and everything works well.
Handler_read_next:This option indicates the number of times to retrieve data from the data file according to the index during index scanning.
Handler_read_prev:This option indicates the number of times to retrieve data from the data file in the reverse order of the index during index scanning, which is generally order by… Desc.
Handler_read_rnd:That is, the query directly operates the data file, which is often shown as not using index or file sorting.
Handler_read_rnd_next:When the value of this option is high, it usually indicates that the table index is incorrect or the written query does not use the index.

5. Two simple and practical optimization methods

For most developers, they may only want to master some simple and practical optimization methods. For more and more complex optimization, they prefer to leave it to professional DBAs. This section will introduce you to two simple and applicable optimization methods.

5.1 periodic analysis table and checklist

The syntax of the analysis table is as follows:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

This statement is used to analyze and store the keyword distribution of the table. The analysis results will enable the system to obtain accurate statistical information and enable SQL to generate the correct execution plan. If the user feels that the actual execution plan is not the expected execution plan, executing an analysis table may solve the problem. During analysis, a read lock is used to lock the table. This works for MyISAM, BDB and InnoDB tables. For MyISAM table, this statement is equivalent to using myisamchk – A. in the following example, for goods_ The stock table is analyzed as follows:

ANALYZE TABLE goods_stock;

● the syntax of the checklist is as follows:

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 

The purpose of a checklist is to check one or more tables for errors. Check table works on MyISAM and InnoDB tables. For MyISAM table, keyword statistics are updated, for example:

CHECK TABLE goods_stock;

Check table can also check whether there are errors in the view. For example, the referenced table in the view definition no longer exists, for example:
(1) First, we create a table.

  ID INT(11)

(2) Create another view.

CREATE VIEW test_view AS SELECT * FROM test;

(3) Then check the view and find that there is no problem.

CHECK TABLE test_view;

(4) Now delete the table that the view depends on.


(5) Check the view just now and find that there is an error.

CHECK TABLE test_view;

5.2 periodic optimization table

The syntax of the optimized table is as follows:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

If you have deleted a large portion of a table, or if you have made many changes to a table with variable length rows (a table with varchar, blob, or text columns), you should use the optimize table command for table optimization. This command can merge the space fragments in the table and eliminate the space waste caused by deletion or update. However, the optimize table command only works on MyISAM, BDB and InnoDB tables. The following example shows optimizing goods_ Process of stock inventory table:

--Check out the goods first_ What is the type of stock table
SHOW TABLE STATUS LIKE 'goods_stock%';

OPTIMIZE TABLE goods_stock;

Note: the tables will be locked during the execution of analyze, check and optimize. Therefore, be sure to perform relevant operations when the database is not busy.

MySQL in simple terms