SQL optimize index operations and functions

The following articles mainly introduce the SQL Optimization Index problem. In general databases, the usage of optimizing indexes for related databases using MySQLSQL is quite high. The following articles mainly use indexes to solve most SQL Performance problems. 1. Storage category of indexes MyISAM storage engine automatically divides table data and Indexes

The following articles mainly introduce the SQL Optimization Index problem. In general databases, the usage of optimizing indexes for related databases using MySQLSQL is quite high. The following articles mainly use indexes to solve most SQL Performance problems. 1. Storage category of indexes MyISAM storage engine automatically divides table data and Indexes

The following articles mainly introduce the SQL Optimization Index problem. In general databases, the usage of optimizing indexes for related databases using MySQLSQL is quite high. The following articles mainly use indexes to solve most 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, for the name field, you can optimize the index of MySQLSQL 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 must be used in the query condition. If multiple-column indexes are used, only the query condition uses multiple-column indexes.

SQL INDEX OPTIMIZATION

In data, many friends like to use MySQLSQL to optimize indexes for databases. Indexing can usually help us solve most 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, for the name field, you can optimize the index of MySQLSQL 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 must be used in the query condition. If multiple-column indexes are used, the index can be used only when the query condition uses the leftmost prefix of multiple-column keywords, otherwise, indexes cannot be used.

1. Use Indexes

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

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

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 does not use the composite condition of company_id and moneys, the index 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 queries that use like, if it is followed by a constant and only % is not the first character, the MySQLSQL optimized 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, use full-text indexing instead of using like "%... %"

If the column name is an index, column_name is null will use the index as shown in Figure

An index is used to query records whose name is nll.

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, but the column below does not have an index, 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 that a row is read by the index value optimized by MySQLSQL. A very low value indicates that the performance improvement caused by the index increase 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.

Remember how to read Handler_read_rnd_next? Use

 
 

  
  
show statuts like 'Handler_read_%'; 
 
 

The index can be used only when the leftmost prefix of the key word is used. 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, as long as the leftmost column is used in the query conditions, the MySQLSQL optimize index is generally used.
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 does not use the composite condition of company_id and moneys, the index 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 queries that use like, if it is followed by a constant and only % is not the first character, the MySQLSQL optimized 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, use full-text indexing instead of using like "%... %"

If the column name is an index, column_name is null will use the index as shown in Figure

An index is used to query records whose name is nll.