MySQL倒序索引测试2
测试环境
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 ORDER BY c1,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 | ALL | NULL | NULL | NULL | NULL | 292380 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
消耗资源情况：
+--------------------------------+----------+----------+------------+--------------+---------------+-------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ | starting | 0.000046 | 0.000036 | 0.000006 | 0 | 0 | 0 | | Executing hook on transaction | 0.000004 | 0.000003 | 0.000001 | 0 | 0 | 0 | | starting | 0.000007 | 0.000006 | 0.000001 | 0 | 0 | 0 | | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | | Opening tables | 0.000024 | 0.000021 | 0.000004 | 0 | 0 | 0 | | init | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | | System lock | 0.000007 | 0.000005 | 0.000001 | 0 | 0 | 0 | | optimizing | 0.000004 | 0.000004 | 0.000000 | 0 | 0 | 0 | | statistics | 0.000012 | 0.000009 | 0.000002 | 0 | 0 | 0 | | preparing | 0.000013 | 0.000011 | 0.000002 | 0 | 0 | 0 | | executing | 0.313603 | 0.313474 | 0.000000 | 0 | 0 | 0 | | end | 0.000011 | 0.000006 | 0.000000 | 0 | 0 | 0 | | query end | 0.000004 | 0.000004 | 0.000000 | 0 | 0 | 0 | | waiting for handler commit | 0.000008 | 0.000008 | 0.000000 | 0 | 0 | 0 | | closing tables | 0.000006 | 0.000006 | 0.000000 | 0 | 0 | 0 | | freeing items | 0.000013 | 0.000013 | 0.000000 | 0 | 0 | 0 | | logging slow query | 0.000033 | 0.000033 | 0.000000 | 0 | 8 | 0 | | cleaning up | 0.000014 | 0.000013 | 0.000000 | 0 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ 总执行时间：0.31381650
使用倒序索引
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 | index | NULL | IDX_C1_C2_DESC | 10 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
资源消耗情况：
+--------------------------------+----------+----------+------------+--------------+---------------+-------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ | starting | 0.000046 | 0.000036 | 0.000007 | 0 | 0 | 0 | | Executing hook on transaction | 0.000004 | 0.000002 | 0.000000 | 0 | 0 | 0 | | starting | 0.000006 | 0.000006 | 0.000001 | 0 | 0 | 0 | | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | | Opening tables | 0.000024 | 0.000020 | 0.000004 | 0 | 0 | 0 | | init | 0.000005 | 0.000004 | 0.000000 | 0 | 0 | 0 | | System lock | 0.000006 | 0.000005 | 0.000001 | 0 | 0 | 0 | | optimizing | 0.000004 | 0.000004 | 0.000001 | 0 | 0 | 0 | | statistics | 0.000011 | 0.000009 | 0.000002 | 0 | 0 | 0 | | preparing | 0.000022 | 0.000019 | 0.000003 | 0 | 0 | 0 | | explaining | 0.000033 | 0.000027 | 0.000005 | 0 | 0 | 0 | | end | 0.000004 | 0.000003 | 0.000001 | 0 | 0 | 0 | | query end | 0.000003 | 0.000003 | 0.000000 | 0 | 0 | 0 | | waiting for handler commit | 0.000007 | 0.000006 | 0.000002 | 0 | 0 | 0 | | closing tables | 0.000006 | 0.000005 | 0.000001 | 0 | 0 | 0 | | freeing items | 0.000009 | 0.000007 | 0.000001 | 0 | 0 | 0 | | cleaning up | 0.000125 | 0.000107 | 0.000020 | 0 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+-------+ 总执行时间：0.00032000
总结
对于查询：
SELECT * FROM TB002 ORDER BY c1,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);
对于普通索引，查询无法使用索引且使用filesort，导致严重的性能问题。
对于倒序索引，查询使用倒序索引，能快速返回数据，性能较好。
MySQL从"最开始不支持倒序索引"到"支持倒序索引"，功能在不断完善，但相对于商用数据库来说，这还是很"辣鸡"啊，相同的数据结构下，SQL Server能完美使用"普通索引"来优化查询，MySQL的查询优化器还有很长的路要走！！！
