select * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20; 表trade_info上有索引idx_status_create_time(status,create_time)，通過(guò)上面分析知道，等價(jià)于索引**（status,create_time,id)**，對(duì)于典型的分頁(yè)limit m, n來(lái)說(shuō)，越往后翻頁(yè)越慢，也就是m越大會(huì)越慢，因?yàn)橐ㄎ籱位置需要掃描的數(shù)據(jù)越來(lái)越多，導(dǎo)致IO開(kāi)銷比較大，這里可以利用輔助索引的覆蓋掃描來(lái)進(jìn)行優(yōu)化，先獲取id，這一步就是索引覆蓋掃描，不需要回表，然后通過(guò)id跟原表trade_info進(jìn)行關(guān)聯(lián)，改寫(xiě)后的SQL如下：
select * from trade_info a ,(select id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b //這一步走的是索引覆蓋掃描，不需要回表 where a.id = b.id; 很多同學(xué)只知道這樣寫(xiě)效率高，但是未必知道為什么要這樣改寫(xiě)，理解索引特性對(duì)編寫(xiě)高質(zhì)量的SQL尤為重要。
分而治之總是不錯(cuò)的
營(yíng)銷系統(tǒng)有一批過(guò)期的優(yōu)惠卷要失效，核心SQL如下：
-- 需要更新的數(shù)據(jù)量500wupdate coupons set status = 1 where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 在Oracle里更新500w數(shù)據(jù)是很快，因?yàn)榭梢岳枚鄠€(gè)cpu core去執(zhí)行，但是MySQL就需要注意了，一個(gè)SQL只能使用一個(gè)cpu core去處理，如果SQL很復(fù)雜或執(zhí)行很慢，就會(huì)阻塞后面的SQL請(qǐng)求，造成活動(dòng)連接數(shù)暴增，MySQL CPU 100%，相應(yīng)的接口Timeout，同時(shí)對(duì)于主從復(fù)制架構(gòu)，而且做了業(yè)務(wù)讀寫(xiě)分離，更新500w數(shù)據(jù)需要5分鐘，Master上執(zhí)行了5分鐘，binlog傳到了slave也需要執(zhí)行5分鐘，那就是Slave延遲5分鐘，在這期間會(huì)造成業(yè)務(wù)臟數(shù)據(jù)，比如重復(fù)下單等。
先獲取要更新的數(shù)據(jù)范圍內(nèi)的最小id和最大id（表沒(méi)有物理delete，所以id是連續(xù)的） mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; +----+-------------+-------+------------+-------+------------------------+------------------------+---------+---| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---| 1 | SIMPLE | users | NULL | range | idx_status_create_time | idx_status_create_time | 6 | NULL | 180300 | 100.00 | Using where; Using index | Extra=Using where; Using index使用了索引idx_status_create_time，同時(shí)需要的數(shù)據(jù)都在索引中能找到，所以不需要回表查詢數(shù)據(jù)。
以每次1000條commit一次進(jìn)行循環(huán)update，主要代碼如下： current_id = min_id;for current_id < max_id do update coupons set status = 1 where id >=current_id and id <= current_id + 1000; //通過(guò)主鍵id更新1000條很快commit;current_id += 1000;done 這兩個(gè)案例告訴我們，要充分利用輔助索引包含主鍵id的特性，先通過(guò)索引獲取主鍵id走覆蓋索引掃描，不需要回表，然后再通過(guò)id去關(guān)聯(lián)操作是高效的，同時(shí)根據(jù)MySQL的特性使用分而治之的思想既能高效完成操作，又能避免主從復(fù)制延遲產(chǎn)生的業(yè)務(wù)數(shù)據(jù)混亂。
-- 創(chuàng)建前綴索引，前綴長(zhǎng)度為30mysql> create index idx_nickname_part on users(nickname(30));-- 查看執(zhí)行計(jì)劃mysql> explain select * from users where nickname = 'Laaa';+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+--------------------------------+-------------------+---------+-| 1 | SIMPLE | users | NULL | ref | idx_nickname_part,idx_nickname | idx_nickname_part | 123 | const | 1 | 100.00 | Using where | 可以看到優(yōu)化器選擇了前綴索引，索引長(zhǎng)度為123，即30 x 4 + 2 + 1 = 123 Bytes，大小不到原來(lái)的四分之。
前綴索引雖然可以減小索引的大小，但是不能消除排序。
mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+--------------------------------+--------------+---------+-----| 1 | SIMPLE | users | NULL | range | idx_nickname_part,idx_nickname | idx_nickname | 515 | NULL | 899 | 100.00 | Using index condition |--可以看到Extra= Using index condition表示使用了索引，但是需要回表查詢數(shù)據(jù)，沒(méi)有發(fā)生排序操作。mysql> explain select gender,count(*) from users where nickname like 'User100%' group by nickname limit 10;+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------| 1 | SIMPLE | users | NULL | range | idx_nickname_part | idx_nickname_part | 123 | NULL | 899 | 100.00 | Using where; Using temporary |--可以看到Extra= Using where; Using temporaryn表示在使用了索引的情況下，需要回表去查詢所需的數(shù)據(jù)，同時(shí)發(fā)生了排序操作。 復(fù)合索引 在單列索引不能很好的過(guò)濾數(shù)據(jù)的時(shí)候，可以結(jié)合where條件中其他字段來(lái)創(chuàng)建復(fù)合索引，更好的去過(guò)濾數(shù)據(jù)，減少IO的掃描次數(shù)，舉個(gè)例子：業(yè)務(wù)需要按照時(shí)間段來(lái)查詢交易記錄，有如下的SQL：
select * from trade_info where status = 1 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59'; 開(kāi)發(fā)同學(xué)根據(jù)以往復(fù)合索引的設(shè)計(jì)的經(jīng)驗(yàn)：唯一值多選擇性好的列作為復(fù)合索引的前導(dǎo)列，所以創(chuàng)建復(fù)合索idx_create_time_status是高效的，因?yàn)閏reate_time是一秒一個(gè)值，唯一值很多，選擇性很好，而status只有離散的6個(gè)值，所以認(rèn)為這樣創(chuàng)建是沒(méi)問(wèn)題的，但是這個(gè)經(jīng)驗(yàn)只適合于等值條件過(guò)濾，不適合有范圍條件過(guò)濾的情況，例如idx_user_id_status(user_id，status)這個(gè)是沒(méi)問(wèn)題的，但是對(duì)于包含有create_time范圍的復(fù)合索引來(lái)說(shuō)，就不適應(yīng)了，我們來(lái)看下這兩種不同索引順序的差異，即idx_status_create_time和idx_create_time_status。
-- 分別創(chuàng)建兩種不同的復(fù)合索引mysql> create index idx_status_create_time on trade_info(status, create_time);mysql> create index idx_create_time_status on trade_info(create_time,status);-- 查看SQL的執(zhí)行計(jì)劃mysql> explain select * from users where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';+----+-------------+-------+------------+-------+-----------------------------------------------+---------------| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+-----------------------------------------------+---------------| 1 | SIMPLE | trade_info | NULL | range | idx_status_create_time,idx_create_time_status | idx_status_create_time | 6 | NULL | 98518 | 100.00 | Using index condition | 從執(zhí)行計(jì)劃可以看到，兩種不同順序的復(fù)合索引都存在的情況，MySQL優(yōu)化器選擇的是idx_status_create_time索引，那為什么不選擇idx_create_time_status，我們通過(guò)optimizer_trace來(lái)跟蹤優(yōu)化器的選擇。
-- 開(kāi)啟optimizer_trace跟蹤mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;-- 執(zhí)行SQL語(yǔ)句mysql> select * from trade_info where status = 1 and create_time >='2021-10-01 00:00:00' and create_time <= '2021-10-07 23:59:59';-- 查看跟蹤結(jié)果mysql>SELECT trace FROM information_schema.OPTIMIZER_TRACE/G;
對(duì)比下兩個(gè)索引的統(tǒng)計(jì)數(shù)據(jù)，如下所示：
復(fù)合索引 Type Rows 參與過(guò)濾索引列 Chosen Cause idx_status_create_time Index Range Scan 98518 status AND create_time True Cost低 idx_create_time_status Index Range Scan 98518 create_time False Cost高 MySQL優(yōu)化器是基于Cost的，COST主要包括IO_COST和CPU_COST，MySQL的CBO（Cost-Based Optimizer基于成本的優(yōu)化器）總是選擇Cost最小的作為最終的執(zhí)行計(jì)劃去執(zhí)行，從上面的分析，CBO選擇的是復(fù)合索引idx_status_create_time，因?yàn)樵撍饕械膕tatus和create_time都能參與了數(shù)據(jù)過(guò)濾，成本較低；而idx_create_time_status只有create_time參數(shù)數(shù)據(jù)過(guò)濾，status被忽略了，其實(shí)CBO將其簡(jiǎn)化為單列索引idx_create_time，選擇性沒(méi)有復(fù)合索引idx_status_create_time好。
