问题一：同一个sql执行倒序和正序性能差别很大，sql明细如下：
-- 表结构：
CREATE TABLE `tb_project_white_list` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`project_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '活动id',
`partner_user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '开发者用户id',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`group_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '分组id',
`partner_extra` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '扩展字段',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_project_group` (`project_id`,`group_id`,`partner_user_id`),
KEY `idx_gmt` (`gmt_create`),
KEY `idx_prj_uid` (`project_id`(20),`partner_user_id`(100)),
KEY `idx_oo` (`gmt_modified`)
) ENGINE=InnoDB AUTO_INCREMENT=31853378 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci STATS_SAMPLE_PAGES=40;
-- 性能对比有limit限制，差异很大：
MySQL [projectx]> SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id desc limit 10;
10 rows in set (0.00 sec) -- 倒序很快
MySQL [projectx]> SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc limit 10;
10 rows in set (5.03 sec) -- 正序异常慢
-- 性能对比没有limit限制，两者几乎一样：
MySQL [projectx]> SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id desc;
168023 rows in set (0.33 sec) -- 倒序
MySQL [projectx]> SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc;
168023 rows in set (0.36 sec) -- 正序
-- 不同sql形式 执行计划区别：
-- 1、没有limit:
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id desc;
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
| 1 | SIMPLE | tb_project_white_list | NULL | ref | idx_project_group,idx_prj_uid | idx_prj_uid | 62 | const | 337324 | 10.00 | Using where; Using filesort |
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc;
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
| 1 | SIMPLE | tb_project_white_list | NULL | ref | idx_project_group,idx_prj_uid | idx_prj_uid | 62 | const | 337324 | 10.00 | Using where; Using filesort |
+----+-------------+-----------------------+------------+------+-------------------------------+-------------+---------+-------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
-- 2、有limit限制：
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id desc limit 10;
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | tb_project_white_list | NULL | index | idx_project_group,idx_prj_uid | PRIMARY | 8 | NULL | 392 | 2.65 | Using where; Backward index scan |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc limit 10;
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_project_white_list | NULL | index | idx_project_group,idx_prj_uid | PRIMARY | 8 | NULL | 392 | 2.65 | Using where |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
-- 从执行计划可以看出有limit限制的sql选择了主键扫描，基于不同排序规则差别在于：
这个表的单行数据大小约100B，总1000W数据两层索引树就能存下，而select字句的查询列超过了max_length_for_sort_data的长度，所以mysql选择了代价较小的扫主键的方式避免filesort，
而project_id = 'p13651f79'的数据group_id全部为0并且id集中分布在索引树右侧，desc下从右开始扫描，根节点和叶节点扫面的数据页相对较少，并且8.0还使用了Backward index scan(索引倒序)做了进一步优化。
问题二：为何在DMS上根据主键回表查询正序和倒序也很慢？
原因：DMS在执行sql时默认会控制返回行数100条，实际在mysql-server中执行会 SET SQL_SELECT_LIMIT=200，所以看执行计划使用了idx_prj_uid，但实际执行却是扫描了主键，就会出现问题一中的现象。
DMS执行流程：
-- 实际测试sql:
SELECT * FROM `rds_db_info`
-- DMS执行逻辑：
220302 12:25:59 40 Query show variables like '%character_set_results%'
40 Query /* Query from DMS-WEBSQL-0-Qid_1646195158245 by user 262459518802646572 */ explain SELECT * FROM `rds_db_info`
40 Query SET autocommit=1
40 Query SET SQL_SELECT_LIMIT=200 --这里会做变量重置
40 Query show variables like '%character_set_results%'
40 Query SET SQL_SELECT_LIMIT=10
40 Query /* Query from DMS-WEBSQL-0-Qid_1646195158245 by user 262459518802646572 */ SELECT * FROM `rds_db_info`
测试详情如下：
-- 控制变量：
MySQL [projectx]> SET SQL_SELECT_LIMIT=200;
Query OK, 0 rows affected (0.00 sec)
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc;
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_project_white_list | NULL | index | idx_project_group,idx_prj_uid | PRIMARY | 8 | NULL | 7842 | 2.65 | Using where |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc limit 100;
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_project_white_list | NULL | index | idx_project_group,idx_prj_uid | PRIMARY | 8 | NULL | 4105 | 2.55 | Using where |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL [projectx]> desc SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id desc limit 100;
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_project_white_list | NULL | index | idx_project_group,idx_prj_uid | PRIMARY | 8 | NULL | 4105 | 2.55 | Using where |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
主键扫描 不同排序方式性能差别：
不同排序方式性能差别
如何消除filesort:
-- 增加project_id单列索引，这时where条件检索project_id时本身主键id就是asc有序的，所以不需要再内存排序
MySQL [projectx]> explain SELECT id, project_id, partner_user_id, partner_extra, group_id , gmt_create, gmt_modified FROM tb_project_white_list WHERE project_id = 'p13651f79' AND group_id = '0' ORDER BY id asc;
+----+-------------+-----------------------+------------+------+----------------------------------------------+----------------+---------+-------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+------+----------------------------------------------+----------------+---------+-------+--------+----------+------------------------------------+
| 1 | SIMPLE | tb_project_white_list | NULL | ref | idx_project_group,idx_prj_uid,idx_project_id | idx_project_id | 98 | const | 320814 | 10.00 | Using index condition; Using where |
+----+-------------+-----------------------+------------+------+----------------------------------------------+----------------+---------+-------+--------+----------+------------------------------------+
网友评论