Remember a SQL tuning

Let me talk about the parameters in extal first:
Using index indicates that the query column is covered by the index, so there is no need to go back to the table for query, so the efficiency is higher. For example: select id from test where id = 5; where id is the primary key

Using where; Using index means that the query column is covered by the index, and the where filter condition is a range of the leading column of the index column, or a non-leading column of the index column, for example: select id from test where id> 5;. Obviously, the efficiency is also very high.

Using where indicates that the query column is not covered by the index, and the where filter condition is a range of the leading column of the index column, or a non-leading column of the index column, or a non-index column, for example: select * from test where id> 30;. Because it is not covered by the index, it needs to return to the table, so the performance is worse than the previous two.

Extra is null means that the query column is not covered by the index, and the where filter condition is the leading column of the index, which means that the index is used, but some fields are not covered by the index, which must be implemented by "back to the table", so the performance is also better than before The two are worse.

Using index condition is a new feature introduced in MySQL 5.6, called Index Condition Pushdown (ICP), which is an optimized way to filter data using indexes at the storage engine layer. The "push down" here refers to the part that can be indexed in the table filter originally performed at the server layer, and the index filter is used for processing at the engine level, and there is no need to return to the table for table filter. Using ICP can reduce the number of rows returned by the storage engine layer that need to be filtered out by the index filter, eliminating the number of times the storage engine accesses the base table and the number of times the MySQL server accesses the storage engine. Using index condition is only applicable to secondary indexes, because the purpose of ICP is to reduce the number of full-line reads, thereby reducing IO operations. For the innodb clustered index, the complete record has been read into the innodb buffer. In this case, ICP will not reduce io, so ICP only applies to secondary indexes, which generally occurs when the query field cannot be covered by the secondary index In this scenario, it is often necessary to return to the table. Through ICP, you can reduce the number of rows returned by the storage engine, thereby reducing IO operations

There is a table read_user_collection_record in the database

1. The index I created for it is:

Insert picture description here

2. The sql I executed is:

SELECT count(*) from read_user_collection_record where app_id=28 and state=1;

3. The result of execution is: 4. The result of

Insert picture description here


my execution of explain is:

Insert picture description here


5. Results: According to the leftmost search of the index, as well as the key and extra to observe, the index is indeed gone and the
sql becomes:

SELECT count(*) from read_user_collection_record where app_id=28 and state=1 and created_at > "2021-05-01";

6. The result of executing explain is:

Insert picture description here


7. According to the search on the leftmost side of the index, when the index goes to create_at, although it is not using index, although create_at is a range query, create_at is covered by the index, so the efficiency is still ok

8. Use explain to execute:

EXPLAIN SELECT count(*) from read_user_collection_record where app_id=28 and state=1 and created_at = "2021-05-01";

9. Execution result

Insert picture description here


10. See that extra is using index, indicating that the index is used and there is no return to the table.
Reference article: Address