测试环境
MySQL Community Server 8.0.17
准备测试数据
DROP TABLE TB001; CREATE TABLE TB001(ID INT PRIMARY KEY AUTO_INCREMENT,C1 INT); INSERT INTO TB001(C1) SELECT 1 FROM information_schema.columns LIMIT 1000; DROP TABLE TB002; CREATE TABLE TB002(ID INT PRIMARY KEY AUTO_INCREMENT,C1 INT,C2 INT,C3 CHAR(100)); INSERT INTO TB002(C1,C2,C3) SELECT T1.ID,T2.ID,REPEAT('您',100) FROM TB001 AS T1,TB001 AS T2 WHERE T1.ID<300 AND T2.ID<3000;
测试SQL:
SELECT * FROM TB002 WHERE C1=20 ORDER BY C2 DESC LIMIT 10;
使用普通索引
ALTER TABLE TB002 ADD INDEX IDX_C1_C2(C1,C2);
执行计划：
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------+ | 1 | SIMPLE | TB002 | NULL | ref | IDX_C1_C2 | IDX_C1_C2 | 5 | const | 1000 | 100.00 | Backward index scan | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------+
资源消耗情况：
+--------------------------------+----------+----------+------------+--------------+---------------+-------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ | starting | 0.000048 | 0.000000 | 0.000043 | 0 | 0 | 0 | | Executing hook on transaction | 0.000004 | 0.000000 | 0.000002 | 0 | 0 | 0 | | starting | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | | checking permissions | 0.000005 | 0.000000 | 0.000005 | 0 | 0 | 0 | | Opening tables | 0.000025 | 0.000000 | 0.000026 | 0 | 0 | 0 | | init | 0.000005 | 0.000000 | 0.000004 | 0 | 0 | 0 | | System lock | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | | optimizing | 0.000007 | 0.000000 | 0.000007 | 0 | 0 | 0 | | statistics | 0.000042 | 0.000000 | 0.000043 | 0 | 0 | 0 | | preparing | 0.000011 | 0.000000 | 0.000010 | 0 | 0 | 0 | | executing | 0.000066 | 0.000000 | 0.000066 | 0 | 0 | 0 | | end | 0.000003 | 0.000000 | 0.000002 | 0 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000003 | 0 | 0 | 0 | | waiting for handler commit | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | | closing tables | 0.000004 | 0.000000 | 0.000004 | 0 | 0 | 0 | | freeing items | 0.000008 | 0.000000 | 0.000008 | 0 | 0 | 0 | | cleaning up | 0.000007 | 0.000000 | 0.000007 | 0 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ 总执行时间：0.00025375
使用倒序索引
ALTER TABLE TB002 ADD INDEX IDX_C1_C2_DESC(C1,C2 DESC);
执行计划：
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | TB002 | NULL | ref | IDX_C1_C2_DESC | IDX_C1_C2_DESC | 5 | const | 1000 | 100.00 | NULL | +----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
资源消耗情况：
+--------------------------------+----------+----------+------------+--------------+---------------+-------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ | starting | 0.000051 | 0.000040 | 0.000008 | 0 | 0 | 0 | | Executing hook on transaction | 0.000004 | 0.000002 | 0.000000 | 0 | 0 | 0 | | starting | 0.000006 | 0.000005 | 0.000001 | 0 | 0 | 0 | | checking permissions | 0.000005 | 0.000005 | 0.000001 | 0 | 0 | 0 | | Opening tables | 0.000026 | 0.000021 | 0.000004 | 0 | 0 | 0 | | init | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | | System lock | 0.000006 | 0.000006 | 0.000001 | 0 | 0 | 0 | | optimizing | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | 0 | | statistics | 0.000045 | 0.000038 | 0.000007 | 0 | 0 | 0 | | preparing | 0.000012 | 0.000010 | 0.000002 | 0 | 0 | 0 | | executing | 0.000071 | 0.000059 | 0.000012 | 0 | 0 | 0 | | end | 0.000004 | 0.000004 | 0.000001 | 0 | 0 | 0 | | query end | 0.000004 | 0.000003 | 0.000000 | 0 | 0 | 0 | | waiting for handler commit | 0.000007 | 0.000006 | 0.000002 | 0 | 0 | 0 | | closing tables | 0.000021 | 0.000018 | 0.000003 | 0 | 0 | 0 | | freeing items | 0.000013 | 0.000010 | 0.000002 | 0 | 0 | 0 | | cleaning up | 0.000009 | 0.000007 | 0.000002 | 0 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ 总执行时间：0.00029675
总结
对于查询：
SELECT * FROM TB002 WHERE C1=20 ORDER BY C2 DESC LIMIT 10;
对下面的普通索引还是倒序索引，对性能影响较小。
ALTER TABLE TB002 ADD INDEX IDX_C1_C2(C1,C2); ALTER TABLE TB002 ADD INDEX IDX_C1_C2_DESC(C1,C2 DESC);