Indexes are often not used as expected in MySQL, and logging the use of an Order by statement today results in an index not being used as expected.
1. Problem phenomena
1.1 SQL statement:
SELECT DISTINCT p.* FROM tb_name p WHERE 1=1 AND p.createDate >= '2019-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' ORDER BY p.payDate DESC LIMIT 0 , 15
1.2 The implementation plan is as follows:
+----+-------------+-------+------------+-------+-------------------------------------------------------------+--------------------+---------+------+--------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-------------------------------------------------------------+--------------------+---------+------+--------+----------+------------------------------------+ | 1 | SIMPLE | p | NULL | range | createDate,idx_status_payDate | idx_status_payDate | 108 | NULL | 880063 | 0.74 | Using index condition; Using where | +----+-------------+-------+------------+-------+-------------------------------------------------------------+--------------------+---------+------+--------+----------+------------------------------------+
The index information in the 1.3 table is as follows:
+------------------+------------+-------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_name | 0 | PRIMARY | 1 | id | A | 1760103 | NULL | NULL | | BTREE | | | | tb_name | 1 | idx_payDate | 1 | payDate | A | 1734626 | NULL | NULL | YES | BTREE | | | | tb_name | 1 | createDate | 1 | createDate | A | 1736316 | NULL | NULL | YES | BTREE | | | | tb_name | 1 | idx_status_payDate | 1 | status | A | 2 | NULL | NULL | YES | BTREE | | | | tb_name | 1 | idx_status_payDate | 2 | payDate | A | 1741214 | NULL | NULL | YES | BTREE | | | +------------------+------------+-------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 16 rows in set (0.00 sec)
1.4. Ideal
Running this SQL takes about 5.7s.From the SQL and indexing perspective, it would be better to use the index of the createDate field, and to verify this, use force index to force the createDate index to run once to see the results.
Change the SQL to the following:
SELECT DISTINCT p.* FROM tb_name p FORCE INDEX (createDate) WHERE 1=1 AND p.createDate >= '2019-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' ORDER BY p.payDate DESC LIMIT 0 , 15
The modified execution plan is as follows:
root@db09:03:13>explain SELECT DISTINCT p.* FROM tb_namep FORCE INDEX (createDate) -> WHERE 1=1 AND p.createDate >= '2019-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' -> ORDER BY p.payDate DESC LIMIT 0 , 15; +----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | p | NULL | range | createDate | createDate | 6 | NULL | 117858 | 1.11 | Using index condition; Using where; Using filesort | +----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------------------------+ 1 row in set, 3 warnings (0.00 sec)
The actual time it takes to run the SQL is about 0.15s, a difference of about 50 times.
1.5 Simple Analysis
Comparing execution plans, it is not difficult to understand that using createDate would result in additional ordering (Using filesort).
2. Various unreasonable attempts
2.1 Mandatory use of indexes
Using force index (createDate) is solvable and has been tested above
2.2 Ignore poor indexes
Like force index, you can use IGNORE INDEX for the purpose of using the createDate index, for example:
SELECT DISTINCT p.* FROM tb_name p IGNORE INDEX (idx_status_payDate,idx_payDate) WHERE 1=1 AND p.createDate >= '2019-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' ORDER BY p.payDate DESC LIMIT 0 , 15
The effect is the same as force index, and the running time is about 0.15s.
2.3 Add Composite Index
payDate and createDate are added as composite indexes, but this is not a good idea and the execution plan is not working as expected.
3. Relatively reasonable way
Whether you use force index or ignore index will affect the execution of the MySQL optimizer itself.For example, if createDate has a large range, it will actually take the first 15 records of payDate's index faster. To minimize changes in the application and not fail to walk a reasonable index due to changes in other conditions, choose another optimization scheme and change the SQL to the following:
SELECT DISTINCT p.* FROM tb_name p WHERE 1=1 AND p.createDate >= '2019-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' ORDER BY p.payDate DESC, createDate LIMIT 0 , 15
At this time, the execution plan is as follows:
+----+-------------+-------+------------+-------+-------------------------------+------------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-------------------------------+------------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | p | NULL | range | createDate,idx_status_payDate | createDate | 6 | NULL | 123024 | 5.55 | Using index condition; Using where; Using filesort | +----+-------------+-------+------------+-------+-------------------------------+------------+---------+------+--------+----------+----------------------------------------------------+ 1 row in set, 3 warnings (0.00 sec)
After adjusting the createDate, execute the execution plan:
root@db 09:51:00>EXPLAIN -> SELECT DISTINCT p.* FROM tb_name p IGNORE INDEX (idx_status_synIs_deleteStatus) -> WHERE 1=1 AND p.createDate >= '2009-10-23' AND p.createDate <= '2019-11-20 24:00:00' AND p.status = '1' AND p.areaName LIKE '%Shanghai%' -> ORDER BY p.payDate DESC,createDate DESC LIMIT 0 , 15; +----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | p | NULL | ref | createDate,idx_status_payDate | idx_status_payDate | 108 | const | 880205 | 5.56 | Using index condition; Using where; Using filesort | +----+-------------+-------+------------+------+-------------------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ 1 row in set, 3 warnings (0.00 sec)
Normal as expected.This seems to be the best approach compared to the previous one.