Detailed description of index problems of MySQL SQL Optimization

The following articles mainly introduce the index problem of MySQL SQL optimization. We all know that in general data, many people prefer to use related indexes to optimize MySQL databases. In general, related indexes help us solve most MySQL SQL Performance problems.

1. Storage Classification of Indexes

The data and indexes of MyISAM storage engine tables are automatically stored separately. They are independent files. The data and indexes of InnoDB Storage engine tables are stored in the same tablespace, but it can contain multiple files.

Currently, there are only two types of indexes (BTREE and HASH) in MySQL, which are related to the table storage engine. Both MyISAM and InnoDB Storage engines support only the BTREE index; the MEMORY/HEAP Storage engine supports HASH and BTREE indexes.

MySQL currently does not support function indexing, but can index the first part of the column. For example, the name field can be indexed with only the first four characters of name, this feature can greatly reduce the size of the index file. When designing the table structure, you can also flexibly design the text column Root Based on this feature. For example

Reference

 
 

  
  
create index ind_company2_name on company2(name(4))  
 
 

2. How to Use indexes in MySQL

The index is used to quickly find rows with a specific value in a column. The best way to improve the performance of SELECT operations when using indexes for related columns.
The most important condition for an index to be used for a query is that the index keyword needs to be used in the query condition. If multiple MySQL SQL statements optimize the index, only when the query condition uses the leftmost prefix of multiple key columns, to use the index. Otherwise, the index cannot be used.

1. Use Indexes

In MySQL, indexes may be used in the following situations.

For the created multi-column index, the index is generally used as long as the leftmost column is used in the query conditions.

For example:

Reference

Create a composite index in the order of company_id and Moneys.

 
 

  
  
create index ind_sales2_companyid_moneys on sales2(company_id,moneys)  
 
 

If you query a table by company_id

Reference

Use explain to analyze

 
 

  
  
explain select * from sales2 where company_id =2000 \G;   
  
  
explain select * from sales2 where moneys = 1\G;  
 
 

Through the above, you can find that even if the where condition is not a combination of company_id and moneys, the index of MySQL SQL optimization can still be used, which is the prefix feature of the index. However, if you only query the table according to the moneys condition, the index will not be used.

For a query using like, if it is followed by a constant and the % number is not the first character, the index can be used. For example:

Reference

 
 

  
  
explain select * from company2 where name like "%3"\G;   
  
  
explain select * from company2 where name like "3%"\G;  
 
 

The above two sentences are the same. They are actually different. The first sentence does not actually use indexes, but the second sentence can use indexes. In addition, if like is followed by a column name, the index will not be used.

If you search for large text, you do not need to use like "% .. % "if the column name is an index, using column_name is null will use the index optimized by MySQL SQL. For example, if the query name is nll, the index will be used.

Reference

 
 

  
  
explain select * from company2 where name is null \G;  
 
 

2. In the following situations, indexes exist but are not used. You may think they will be used, but they are useless.

Reference

1. If Mysql estimates that indexes are slower than full table scans, no indexes will be used.

For example, if the column key_part1 is evenly distributed between 1 and 100, the index used in the following queries is not very good.

 
 

  
  
select * from table_name where key_part1 > 1 and key_part1 < 90;  
 
 

2. If the MEMORY/HEAP table is used and "=" is not used in the where condition, the index is not used. The heap table uses indexes only when "=" is specified.

3. Use or to split the conditions. If the column in the condition before or has an index, and the index in the column below does not use MySQL SQL optimization, the indexes involved will not be used.

4. If it is not the first part of the index column, it will not be used.

5. If like starts with "%"

6. If the column type is a string, remember to enclose the constant value in quotation marks in the where condition. Otherwise, Mysql will not use this column even if it has an index. Because MYSQL converts the input constant value by default before retrieval.
 

Finally, check the index usage.

If the index is working, the value of Handler_read_key will be very high, which indicates the number of times a row is read by the index value. A very low value indicates that the performance improved by increasing the index is not high, because indexes are often not used. If the value of Handler_read_rnd_next is high, the query operation is inefficient and the index recovery should be established. This value indicates the number of requests to read the next row in the data file. If a large number of table scans are being performed and the value of Handler_read_rnd_next is high, it usually indicates that the table index is incorrect or that the written query does not use the index optimized by MySQL SQL.

Remember how to read Handler_read_rnd_next? Use

 
 

  
  
show statuts like 'Handler_read_%';  
 
 

The above content is an introduction to the index problem of MySQL SQL optimization. I hope you will have some gains.