問題一：同一個sql執(zhí)行倒序和正序性能差別很大茸习，sql明細(xì)如下：
-- 表結(jié)構(gòu)：
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 '開發(fā)者用戶id',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
`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;
-- 性能對比有l(wèi)imit限制廷支，差異很大：
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) -- 正序異常慢
-- 性能對比沒有l(wèi)imit限制瓦呼，兩者幾乎一樣：
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形式 執(zhí)行計劃區(qū)別：
-- 1划咐、沒有l(wèi)imit:
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熟史、有l(wèi)imit限制：
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 |
+----+-------------+-----------------------+------------+-------+-------------------------------+---------+---------+------+------+----------+-------------+
-- 從執(zhí)行計劃可以看出有l(wèi)imit限制的sql選擇了主鍵掃描箱熬，基于不同排序規(guī)則差別在于：
這個表的單行數(shù)據(jù)大小約100B类垦，總1000W數(shù)據(jù)兩層索引樹就能存下，而select字句的查詢列超過了max_length_for_sort_data的長度城须，所以mysql選擇了代價較小的掃主鍵的方式避免filesort蚤认，
而project_id = 'p13651f79'的數(shù)據(jù)group_id全部為0并且id集中分布在索引樹右側(cè)，desc下從右開始掃描糕伐，根節(jié)點和葉節(jié)點掃面的數(shù)據(jù)頁相對較少砰琢，并且8.0還使用了Backward index scan(索引倒序)做了進一步優(yōu)化。
問題二：為何在DMS上根據(jù)主鍵回表查詢正序和倒序也很慢？
原因：DMS在執(zhí)行sql時默認(rèn)會控制返回行數(shù)100條陪汽，實際在mysql-server中執(zhí)行會 SET SQL_SELECT_LIMIT=200训唱，所以看執(zhí)行計劃使用了idx_prj_uid，但實際執(zhí)行卻是掃描了主鍵挚冤，就會出現(xiàn)問題一中的現(xiàn)象况增。
DMS執(zhí)行流程：
-- 實際測試sql:
SELECT * FROM `rds_db_info`
-- DMS執(zhí)行邏輯：
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有序的训挡，所以不需要再內(nèi)存排序
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 |
+----+-------------+-----------------------+------------+------+----------------------------------------------+----------------+---------+-------+--------+----------+------------------------------------+
