一次关于 Mysql 索引优化的思考
點(diǎn)擊上方?好好學(xué)java?，選擇?星標(biāo)?公眾號
轉(zhuǎn)自：簡書? ?作者：Fooisart
www.jianshu.com/p/efb7881f18b2
查看系統(tǒng)性能監(jiān)控，發(fā)現(xiàn)有十多條慢查詢，決定將其優(yōu)化。挑選其中一條典型Sql記錄其優(yōu)化歷程。
1.概述
在下文的查詢優(yōu)化中，主要圍繞的問題：Mysql為何會選錯(cuò)索引？
2.優(yōu)化歷程
2.1，定位問題
為了便于描述，貼出建表語句(表中數(shù)據(jù)約有400萬行)：
MySQL > show create table demo_table\G*************************** 1\. row ***************************Table: demo_tableCreate Table: CREATE TABLE `demo_table` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',`user_id` varchar(100) NOT NULL DEFAULT '' COMMENT '用戶ID',`source_channel` int(11) NOT NULL DEFAULT '0' COMMENT '來源',`source_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '來源ID',`category_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分類ID',`classify_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '歸類時(shí)間',`content_id` bigint(20) DEFAULT NULL COMMENT '語義模板ID',PRIMARY KEY (`id`),KEY `idx_category_id` (`category_id`),KEY `idx_classify_time` (`classify_time`),KEY `idx_channel_source_id` (`source_channel`,`source_id`)) ENGINE=InnoDB AUTO_INCREMENT=3081571 DEFAULT CHARSET=utf8mb4業(yè)務(wù)查詢SQL-1(統(tǒng)計(jì)在近七天內(nèi)每條content_id在表中出現(xiàn)的次數(shù))：
select content_id, count(1) as c from demo_table where source_channel = 2and classify_time between 1556019882000 and 1556624682000and content_id is not null group by content_id;執(zhí)行SQL-1，顯示耗時(shí) 9.35sec。顯然是不樂觀的一個(gè)值，查看其執(zhí)行計(jì)劃(explain):?
id: 1select_type: SIMPLEtable: demo_tablepartitions: NULLtype: refpossible_keys: idx_classify_time,idx_channel_source_idkey: idx_channel_source_idkey_len: 4ref: constrows: 1487434filtered: 42.67Extra: Using where; Using temporary; Using filesort從執(zhí)行計(jì)劃中，可以看出，用到了索引idx_channel_source_id。而source_channel是個(gè)區(qū)分度很低的字段，當(dāng)?shù)弥狹ysql優(yōu)化器選擇了idx_channel_source_id，個(gè)人感覺是比較吃驚的。idx_classify_time是區(qū)分度更好的索引卻沒有被選中？
強(qiáng)制使用idx_classify_time，驗(yàn)證是否會執(zhí)行效率更高，SQL-2:
select content_id, count(1) as c from demo_table force index(idx_classify_time)where source_channel = 2 and classify_time between 1556019882000and 1556624682000 and content_id is not null group by content_id;果不其然，執(zhí)行時(shí)間為：2.19 sec。那么這里開始疑惑了，Mysql優(yōu)化器為何會選錯(cuò)索引？繼續(xù)探究。
Mysql優(yōu)化器會根據(jù) ①掃描行數(shù)、②是否使用臨時(shí)表、③是否排序等因素進(jìn)行綜合判斷。
貼出SQL-1、SQL-2的執(zhí)行計(jì)劃：
SQL-1*************************** 1\. row ***************************id: 1select_type: SIMPLEtable: demo_tablepartitions: NULLtype: refpossible_keys: idx_classify_time,idx_channel_source_idkey: idx_channel_source_idkey_len: 4ref: constrows: 1487434filtered: 42.67Extra: Using where; Using temporary; Using filesortSQL-2*************************** 1\. row ***************************id: 1select_type: SIMPLEtable: demo_tablepartitions: NULLtype: rangepossible_keys: idx_classify_timekey: idx_classify_timekey_len: 8ref: NULLrows: 1410438filtered: 45.00Extra: Using where; Using temporary; Using filesort從執(zhí)行計(jì)劃的Extra參數(shù)中，可以看出 ②是否使用臨時(shí)表、③是否排序 這兩個(gè)因素完全一樣，再看掃描行數(shù)，idx_classify_time是141萬行，idx_channel_source_id是148萬行。明顯是idx_classify_time更少，為何沒有選它呢？
其實(shí)這里，優(yōu)化器認(rèn)為他們倆的行數(shù)是差不多的，沒有本質(zhì)的差別。而在執(zhí)行計(jì)劃中，有個(gè)參數(shù)確差別很大：type。
type最重要且經(jīng)常遇見的六種類型：all, index, range, ref, eq_ref，const。從左到右，它們的效率依次是增強(qiáng)的，所以優(yōu)化器根據(jù) type類型更傾向于idx_channel_source_id。而且idx_channel_source_id的 key_len更小，這樣的話，一頁中可以掃描更多行數(shù)。
2.2，解決方案
既然Mysql優(yōu)化器選錯(cuò)了索引，我們?nèi)绾稳ゼm正它呢？
第一種方式：使用SQL-2中的方式，在應(yīng)用程序中顯示選擇索引。由于索引可能會變更，名稱更改或者索引刪除等，這樣明顯會影響應(yīng)用程序的可用性。
第二種方式：創(chuàng)建更合適的索引。
2.2.1 回表
在介紹如何創(chuàng)建更合適索引之前，先簡單介紹Mysql中的兩個(gè)基礎(chǔ)概念：聚簇索引，普通索引。
image-1為聚簇索引(clustered index)，B+樹的節(jié)點(diǎn)存放的是每一行記錄；image-2為普通索引(secondary index)，B+樹的節(jié)點(diǎn)存放的是其對應(yīng)的主鍵ID。
使用索引查詢具體執(zhí)行流程：
聚簇索引：如果我是根據(jù)主鍵id查詢某個(gè)值，只需要查詢主鍵索引樹即可獲取內(nèi)容行R；
普通索引：第一次查詢普通索引樹，拿到字段ID,然后拿著ID值去主鍵索引樹再次查找內(nèi)容行R。
從查詢流程可以看出，使用普通索引需要多掃描一次索引樹。而這個(gè)過程，稱為 回表。
2.2.2 覆蓋索引
那么如果能夠減少回表的次數(shù)，會很大程度地提升性能，這里就用到了聯(lián)合索引。將需要用到的字段，建立成一個(gè)聯(lián)合索引，那么這樣就無需再次回表。這樣也就用到了覆蓋索引，效率更高。
為了優(yōu)化SQL-1,創(chuàng)建的索引語句如下：
alter table `demo_table`add index idx_content_id_calssify_time_source_channel(content_id,classify_time,source_channel);
再次執(zhí)行SQL-1,顯示耗時(shí)?0.02sec,性能成飛躍式提升。查看SQL-1執(zhí)行計(jì)劃：
從執(zhí)行計(jì)劃中，看Extra比之前多了個(gè)Using index，這就表示本次查詢用到了覆蓋索引，一般效率較高(基本達(dá)成三星索引的標(biāo)準(zhǔn))。
至此，優(yōu)化就結(jié)束了。
最后，再附上我歷時(shí)三個(gè)月總結(jié)的?Java 面試 + Java 后端技術(shù)學(xué)習(xí)指南，筆者這幾年及春招的總結(jié)，github 1.5k star，拿去不謝！ 下載方式1.?首先掃描下方二維碼2.?后臺回復(fù)「Java面試」即可獲取總結(jié)
以上是生活随笔為你收集整理的一次关于 Mysql 索引优化的思考的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 全网最全程序员效率工具及小技巧
- 下一篇： ThreadPoolExecutor 八