一個案例徹底弄懂如何正確使用mysql inndb聯(lián)合索引
有一個業(yè)務是查詢最新審核的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;
查看當時的監(jiān)控情況 cpu 使用率是超過了100%，show processlist看到很多類似的查詢都是處于create sort index的狀態(tài)。
查看該表的結構
CREATE TABLE `th_content` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '內容標題',
`content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT '正文內容',
`audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '審核時間',
`last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近編輯時間',
`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;
索引有一個audit_time在左邊的聯(lián)合索引，沒有關于status的索引。
分析上面的sql執(zhí)行的邏輯：
- 從聯(lián)合索引里找到所有小于該審核時間的主鍵id（假如在該時間戳之前已經審核了100萬條數(shù)據(jù)，則會在聯(lián)合索引里取出對應的100萬條數(shù)據(jù)的主鍵 id）
- 未來如果有一個優(yōu)化就好了，目前還有：對100個主鍵 id 排序，然后在下面一步回表操作中挨得近的主鍵可能一次磁盤 I/O 就都取到了
- 逐個回表，查出100萬行記錄，篩選出status='ONLINE'的行記錄
- 最后對查詢的結果進行排序（假如有50萬行都是ONLINE，則繼續(xù)對這50萬行進行排序）
最后因為數(shù)據(jù)量很大，雖然只取5行，但是按照我們剛剛舉的極端例子，實際查詢了100萬行數(shù)據(jù)，而且最后還在內存中進行了50萬行數(shù)據(jù)庫的內存排序。
所以是非常低效的。
畫了一個示意圖，說明第一步的查詢過程，粉紅色部分表示最后需要回表查詢的數(shù)據(jù)行。
圖中我按照索引存儲規(guī)律來YY偽造填充了一些數(shù)據(jù)，如有不對請留言指出。希望通過這張圖大家能夠看到聯(lián)合索引存儲的方式和索引查詢的方式
改進思路 1
范圍查找向來不太好使用好索引的，如果我們增加一個audit_time, status的聯(liá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 | +----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
細節(jié)：因為audit_time是一個范圍查找，所以第二列的索引用不上了，只能用到audit_time，所以key_len是4。而下面思路2中，還是這兩個字段key_len則是5。
還是分析下在添加了該索引之后的執(zhí)行過程：
- 從聯(lián)合索引里找到小于該審核時間的
audit_time最大的一行的聯(lián)合索引 - 然后依次往下找，因為
< audit_time是一個范圍查找，而第二列索引的值是分散的。所以需要依次往前查找，匹配出滿足條件（status='ONLINE'）的索引行，直到取到第5行為止。 - 回表查詢需要的具體數(shù)據(jù)
在上面的示意圖中，粉紅色標識滿足第一列索引要求的行，依次向前查詢，本個葉子節(jié)點上篩選到了3條記錄，然后需要繼續(xù)向左，到前一個葉子節(jié)點繼續(xù)查詢。直到找到5條滿足記錄的行，最后回表。
改進之處
因為在索引里面有status的值，所以在篩選滿足status='ONLINE'行的時候，就不用回表查詢了。在回表的時候只有5行數(shù)據(jù)的查詢了，在iops上會大大減少。
該索引的弊端
如果idx_audit_status里掃描5行都是status是ONLINE，那么只需掃描5行；
如果idx_audit_status里掃描前100萬行中，只有4行status是ONLINE，則需要掃描100萬零1行，才能得到需要的5行記錄。索引需要掃描的行數(shù)不確定。
改進思路 2
ALTER TABLE `th_content` DROP INDEX `idx_audit_status`; ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);
這樣不管是排序還是回表都毫無壓力啦。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。如果你想了解更多相關內容請查看下面相關鏈接
相關文章
這篇文章主要給大家介紹了關于mysql使用from與join兩表查詢的區(qū)別的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
這篇文章主要介紹了Mysql字符串字段判斷是否包含某個字符串的2種方法,本文使用Like和find_in_set兩種方法實現(xiàn),需要的朋友可以參考下2015-01-01
explain?是MySql提供的SQL語句查詢性能的工具,是我們優(yōu)化SQL的重要指標手段,要看懂explain返回的結果集就尤為重要,這篇文章主要介紹了MySql?explain命令返回結果解讀,需要的朋友可以參考下2023-09-09
這篇文章主要為大家詳細介紹了centos7下mysql安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
本文給大家分享的是如何在linux下安裝mysql 圖解教程，步奏非常詳細，也很實用，這里推薦給大家2016-04-04
這篇文章主要介紹了MySQL修改密碼的幾種方式，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
這篇文章主要介紹了一文說透什么是MySQL的預編譯，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-11-11
Finished with error:Navicat運行SQL文件報錯的解決
這篇文章主要介紹了Finished with error:Navicat運行SQL文件報錯的問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
mysql8.0.18下安裝winx64的詳細教程(圖文詳解)
這篇文章主要介紹了安裝mysql-8.0.18-win-x64的詳細教程，本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-11-11
在使用mysql的時候經常要用到拼接的功能，最近的工作就遇到拼接的問題，在將字符串拼接Num類型的時候發(fā)現(xiàn)居然報錯，下面通過這篇文章來看看解決的方法吧，有需要的朋友們可以參考借鑒。2016-10-10