有一個(gè)業(yè)務(wù)是查詢最新審核的5條數(shù)據(jù)
SELECT `id`, `title`
FROM `th_content`
WHERE `audit_time` 1541984478
AND `status` = 'ONLINE'
ORDER BY `audit_time` DESC, `id` DESC
LIMIT 5;
查看當(dāng)時(shí)的監(jiān)控情況 cpu 使用率是超過(guò)了100%,show processlist
看到很多類似的查詢都是處于create sort index
的狀態(tài)。
查看該表的結(jié)構(gòu)
CREATE TABLE `th_content` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '內(nèi)容標(biāo)題',
`content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT '正文內(nèi)容',
`audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '審核時(shí)間',
`last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近編輯時(shí)間',
`status` enum('CREATED','CHECKING','IGNORED','ONLINE','OFFLINE') CHARACTER SET utf8 NOT NULL DEFAULT 'CREATED' COMMENT '資訊狀態(tài)',
PRIMARY KEY (`id`),
KEY `idx_at_let` (`audit_time`,`last_edit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
索引有一個(gè)audit_time
在左邊的聯(lián)合索引,沒(méi)有關(guān)于status
的索引。
分析上面的sql執(zhí)行的邏輯:
- 從聯(lián)合索引里找到所有小于該審核時(shí)間的主鍵id(假如在該時(shí)間戳之前已經(jīng)審核了100萬(wàn)條數(shù)據(jù),則會(huì)在聯(lián)合索引里取出對(duì)應(yīng)的100萬(wàn)條數(shù)據(jù)的主鍵 id)
- 未來(lái)如果有一個(gè)優(yōu)化就好了,目前還有:對(duì)100個(gè)主鍵 id 排序,然后在下面一步回表操作中挨得近的主鍵可能一次磁盤 I/O 就都取到了
- 逐個(gè)回表,查出100萬(wàn)行記錄,篩選出status='ONLINE'的行記錄
- 最后對(duì)查詢的結(jié)果進(jìn)行排序(假如有50萬(wàn)行都是ONLINE,則繼續(xù)對(duì)這50萬(wàn)行進(jìn)行排序)
最后因?yàn)閿?shù)據(jù)量很大,雖然只取5行,但是按照我們剛剛舉的極端例子,實(shí)際查詢了100萬(wàn)行數(shù)據(jù),而且最后還在內(nèi)存中進(jìn)行了50萬(wàn)行數(shù)據(jù)庫(kù)的內(nèi)存排序。
所以是非常低效的。
畫(huà)了一個(gè)示意圖,說(shuō)明第一步的查詢過(guò)程,粉紅色部分表示最后需要回表查詢的數(shù)據(jù)行。
圖中我按照索引存儲(chǔ)規(guī)律來(lái)YY偽造填充了一些數(shù)據(jù),如有不對(duì)請(qǐng)留言指出。希望通過(guò)這張圖大家能夠看到聯(lián)合索引存儲(chǔ)的方式和索引查詢的方式
改進(jìn)思路 1
范圍查找向來(lái)不太好使用好索引的,如果我們?cè)黾右粋€(gè)audit_time
, status
的聯(lián)合索引,會(huì)有哪些改進(jìn)呢?
ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);
mysql> explain select `id`, `title` from `th_content` where `audit_time` 1541984478 and `status` = 'ONLINE' order by `audit_time` desc, `id` desc limit 5;
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
| 1 | SIMPLE | th_content | range | idx_at_ft_pt_let,idx_audit_status | idx_audit_status | 4 | NULL | 209754 | Using where |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
細(xì)節(jié):因?yàn)?code>audit_time是一個(gè)范圍查找,所以第二列的索引用不上了,只能用到audit_time
,所以key_len
是4。而下面思路2中,還是這兩個(gè)字段key_len
則是5。
還是分析下在添加了該索引之后的執(zhí)行過(guò)程:
- 從聯(lián)合索引里找到小于該審核時(shí)間的
audit_time
最大的一行的聯(lián)合索引
- 然后依次往下找,因?yàn)?code> audit_time是一個(gè)范圍查找,而第二列索引的值是分散的。所以需要依次往前查找,匹配出滿足條件(
status
='ONLINE')的索引行,直到取到第5行為止。
- 回表查詢需要的具體數(shù)據(jù)
在上面的示意圖中,粉紅色標(biāo)識(shí)滿足第一列索引要求的行,依次向前查詢,本個(gè)葉子節(jié)點(diǎn)上篩選到了3條記錄,然后需要繼續(xù)向左,到前一個(gè)葉子節(jié)點(diǎn)繼續(xù)查詢。直到找到5條滿足記錄的行,最后回表。
改進(jìn)之處
因?yàn)樵谒饕锩嬗?code>status的值,所以在篩選滿足status
='ONLINE'行的時(shí)候,就不用回表查詢了。在回表的時(shí)候只有5行數(shù)據(jù)的查詢了,在iops
上會(huì)大大減少。
該索引的弊端
如果idx_audit_status
里掃描5行都是status
是ONLINE
,那么只需掃描5行;
如果idx_audit_status
里掃描前100萬(wàn)行中,只有4行status
是ONLINE
,則需要掃描100萬(wàn)零1行,才能得到需要的5行記錄。索引需要掃描的行數(shù)不確定。
改進(jìn)思路 2
ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);
這樣不管是排序還是回表都毫無(wú)壓力啦。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接
您可能感興趣的文章:- mysql的聯(lián)合索引(復(fù)合索引)的實(shí)現(xiàn)
- MySQL全文索引、聯(lián)合索引、like查詢、json查詢速度哪個(gè)快
- 深入淺析Mysql聯(lián)合索引最左匹配原則
- MySQL聯(lián)合索引功能與用法實(shí)例分析
- MySQL聯(lián)合索引用法示例
- MySQL中的聯(lián)合索引學(xué)習(xí)教程
- MySQL 聯(lián)合索引與Where子句的優(yōu)化 提高數(shù)據(jù)庫(kù)運(yùn)行效率
- MySQL 獨(dú)立索引和聯(lián)合索引的選擇