10 - MySQL選錯(cuò)索引
關(guān)鍵字
索引選擇
0.錯(cuò)誤情況
首先，建立一個(gè)簡(jiǎn)單的表猴伶，表中有 a课舍、b 兩個(gè)字段，并且分別建立了索引：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB他挎；
然后筝尾，我們往表 t 中插入 10 萬(wàn)行記錄，取值按整數(shù)遞增办桨，即：(1,1,1)筹淫，(2,2,2)，(3,3,3) 直到 (100000,100000,100000):
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
接下來(lái)呢撞，我們分析一條語(yǔ)句：
mysql> select * from t where a between 10000 and 20000;
使用 explain 分析這條語(yǔ)句的執(zhí)行情況：
沒(méi)錯(cuò)损姜，這條語(yǔ)句的執(zhí)行是符合預(yù)期的。下面殊霞，我們來(lái)看一個(gè)會(huì)出錯(cuò)的情況摧阅，我們?cè)谶@個(gè)已經(jīng)有數(shù)據(jù)的表中，再做如下操作：
這時(shí)绷蹲，session B 的 select * from t where a between 10000 and 20000 語(yǔ)句就不會(huì)選擇 a 索引了棒卷，而是直接使用了全表掃描。
執(zhí)行下面三條語(yǔ)句：
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- 第一句祝钢，是將慢查詢(xún)?nèi)罩镜拈撝翟O(shè)置為 0比规，表示這個(gè)線程接下來(lái)的語(yǔ)句都會(huì)被記錄入慢查詢(xún)?nèi)罩局校?/li>
- 第二句，Q1 是 session B 原來(lái)的查詢(xún)拦英；
- 第三句苞俘，Q2 是加了 force index(a) 來(lái)和 session B 原來(lái)的查詢(xún)語(yǔ)句執(zhí)行情況對(duì)比。
下圖是他們生成的慢查詢(xún)?nèi)罩荆?div id="gr8biw8" class="image-package">
10-慢查詢(xún)?nèi)罩?png
可以看到龄章，Q1 掃描了 10萬(wàn) 行吃谣，進(jìn)行了全表掃描，Q2 使用了 a 索引掃描了 10001行做裙。
為什么會(huì)這樣呢岗憋？實(shí)際上，索引的選擇是由優(yōu)化器決定的锚贱，但是在某些特殊情況下仔戈，優(yōu)化器會(huì)選擇并非最優(yōu)的方案。一般我們很難事先防范這種 bug ，但是你有必要理解其中的邏輯监徘。
1.優(yōu)化器的邏輯
優(yōu)化器判斷語(yǔ)句執(zhí)行方式晋修，會(huì)用到 掃描行數(shù)、數(shù)據(jù)排序 等凰盔。
1.1掃描行數(shù)
優(yōu)化器選擇索引的邏輯之一墓卦，就是計(jì)算需要掃描的行數(shù)。需要執(zhí)行的行數(shù)越少户敬，意味著消耗資源越少落剪，優(yōu)化器也就更加傾向選擇這種操作。
在上面的例子中尿庐，影響優(yōu)化器選擇的最主要的原因就是掃描行數(shù)忠怖。實(shí)際上，MySQL 在執(zhí)行一個(gè)語(yǔ)句之前是不可能知道它需要掃描多少行的抄瑟，所以它需要使用表的統(tǒng)計(jì)信息來(lái)估算記錄數(shù)凡泣。
1.1.1索引基數(shù)
一個(gè)索引上有多少個(gè)不同的值，這稱(chēng)之為“基數(shù)”皮假。一個(gè)索引的基數(shù)越大问麸，索引越容易被區(qū)分。在 MySQL 中钞翔，使用抽樣統(tǒng)計(jì)的方法獲取索引的基數(shù)值。而索引的基數(shù)席舍，可以通過(guò) show index 查看：
你會(huì)發(fā)現(xiàn)布轿，即使抽樣統(tǒng)計(jì)有誤差，但是三個(gè)索引的索引基數(shù)是差不多的来颤，所以汰扭，其實(shí)選錯(cuò)索引這件事，和索引基數(shù)的關(guān)系并不太大福铅。
1.1.2預(yù)估掃描行數(shù)
既然索引錯(cuò)誤與索引基數(shù)關(guān)系不大萝毛，那么我們不妨分析一下 Q1 和 Q2 這兩條語(yǔ)句：
在圖中，row 表示預(yù)計(jì)掃描的行數(shù)：
- Q1 的結(jié)果是符合預(yù)期的滑黔，預(yù)估將掃描 10w+ 數(shù)據(jù)笆包。
- Q2 的結(jié)果就不對(duì)了，我們已經(jīng)在最開(kāi)始試過(guò)略荡，該命令當(dāng)時(shí)的 row 只有 10001 庵佣，是這個(gè)誤差誤導(dǎo)了優(yōu)化器的判斷。
你可能會(huì)疑問(wèn)汛兜，即使 Q2 的 row 錯(cuò)誤巴粪，但是 3w+ 的值依然小于 10w+ 啊，為什么優(yōu)化器依然選擇了 10w+ 的索引方式呢？這里簡(jiǎn)單說(shuō)一下：因?yàn)槭褂?a 索引涉及到回表操作肛根，優(yōu)化器將這部分內(nèi)容也算了進(jìn)去辫塌，綜合來(lái)看，優(yōu)化器認(rèn)為使用主鍵索引更快派哲。
所以臼氨，歸根結(jié)底，MySQL 選錯(cuò)索引狮辽，主要原因是它錯(cuò)誤的判斷了掃描行數(shù)一也，至于為什么會(huì)有錯(cuò)誤的掃描行數(shù)，這留作今天的思考題喉脖。
既然問(wèn)題出在統(tǒng)計(jì)信息椰苟，那么我們可以使用 analyze table t ，重新統(tǒng)計(jì) t 表的索引信息树叽，執(zhí)行效果如下：
這次就對(duì)了舆蝴。
1.2數(shù)據(jù)排序
同樣是這個(gè)表，我們執(zhí)行下面的語(yǔ)句：
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
很明顯题诵，
- 我們知道 t 表中所有數(shù)據(jù)的內(nèi)容洁仗，使用 a 索引是最快的，只需要掃描 a 的前 1000 個(gè)值性锭，然后進(jìn)行回表赠潦。
- 而如果使用 b 索引，需要掃描 5w 行草冈，再進(jìn)行回表她奥。
然而，MySQL 又選錯(cuò)了索引怎棱，如果不使用強(qiáng)制索引哩俭，MySQL 會(huì)使用 b 作為索引，而它的執(zhí)行花費(fèi)非常高拳恋。
為什么會(huì)這樣呢凡资？因?yàn)檎Z(yǔ)句的末尾有 order by b，所以?xún)?yōu)化器認(rèn)為使用 b 索引可以避免排序谬运，而使用 a 的化還需要根據(jù) b 再進(jìn)行一次排序隙赁。這就導(dǎo)致了錯(cuò)誤選擇。
2.如何處理索引選擇異常 & 總結(jié)
之前已經(jīng)說(shuō)過(guò)梆暖，索引選擇異常非常復(fù)雜鸳谜。實(shí)際上，在大多數(shù)情況下式廷，優(yōu)化器的選擇都是正確的咐扭，只有在非常特殊的時(shí)候，才會(huì)遇到上面我們說(shuō)的情況。因此蝗肪，因?yàn)橐粋€(gè)較小概率的事重建優(yōu)化器的選擇方案袜爪，是得不償失的。
所以薛闪，在出現(xiàn)索引選擇異常的時(shí)候辛馆，給你一些小的處理方法：
- 第一種方法，使用force index強(qiáng)行選擇索引：在你非常確定使用某個(gè)索引是最優(yōu)選擇的時(shí)候豁延，你可以為 MySQL 決定選擇哪個(gè)索引昙篙。
- 第二種方法，使用analyze table重建表的統(tǒng)計(jì)信息诱咏。
- 第三種方法苔可，修改語(yǔ)句，引導(dǎo) MySQL 使用我們期望的索引：在排序的例子中袋狞，把“order by b limit 1” 改成 “order by b,a limit 1” 焚辅，語(yǔ)義的邏輯是相同的。但是在這種引導(dǎo)下苟鸯，MySQL 會(huì)使用 a 作為索引同蜻。但是，這樣的方法并不通用早处，因?yàn)槟銦o(wú)法確定 a和b 之間的關(guān)系湾蔓。
- 第四種方法，新建一個(gè)更適合的索引砌梆，來(lái)給優(yōu)化器做選擇默责，或者刪掉誤用的索引：當(dāng)然，你要確定這個(gè)索引可以刪除么库。
思考題
前面我們?cè)跇?gòu)造第一個(gè)例子的過(guò)程中，通過(guò) session A 的配合甘有，讓 session B 刪除數(shù)據(jù)后又重新插入了一遍數(shù)據(jù)诉儒，然后就發(fā)現(xiàn) explain 結(jié)果中，rows 字段從 10001 變成 37000 多亏掀。
而如果沒(méi)有 session A 的配合忱反，只是單獨(dú)執(zhí)行 delete from t 、call idata()滤愕、explain 這三句話温算，會(huì)看到 rows 字段其實(shí)還是 10000 左右。你可以自己驗(yàn)證一下這個(gè)結(jié)果间影。
這是什么原因注竿？請(qǐng)你分析一下。
上期答案
上一篇文章的問(wèn)題是，如果某次寫(xiě)入使用了 change buffer 機(jī)制巩割，之后主機(jī)異常重啟裙顽，是否會(huì)丟失 change buffer 和數(shù)據(jù)。
答案是不會(huì)丟失宣谈，雖然是只更新內(nèi)存愈犹，但是在事務(wù)提交的時(shí)候，我們把 change buffer 的操作也記錄到 redo log 里了闻丑，所以崩潰恢復(fù)的時(shí)候漩怎，change buffer 也能找回來(lái)。
以上就是本節(jié)內(nèi)容嗦嗡，希望在人生路上勋锤，你能獲得正確索引的指引。
注：本文章的主要內(nèi)容來(lái)自我對(duì)極客時(shí)間app的《MySQL實(shí)戰(zhàn)45講》專(zhuān)欄的總結(jié)酸钦，我使用了大量的原文怪得、代碼和截圖，如果想要了解具體內(nèi)容卑硫，可以前往極客時(shí)間
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
序言：七十年代末徒恋，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子欢伏，更是在濱河造成了極大的恐慌入挣，老刑警劉巖，帶你破解...
沈念sama閱讀 217,406評(píng)論 6贊 503 序言：濱河連續(xù)發(fā)生了三起死亡事件硝拧，死亡現(xiàn)場(chǎng)離奇詭異径筏，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)障陶，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,732評(píng)論 3贊 393 文/潘曉璐 我一進(jìn)店門(mén)滋恬，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人抱究，你說(shuō)我怎么就攤上這事恢氯。” “怎么了鼓寺？”我有些...
文/不壞的土叔 我叫張陵勋拟，是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我妈候，道長(zhǎng)敢靡，這世上最難降的妖魔是什么？ 我笑而不...
正文 為了忘掉前任苦银，我火速辦了婚禮啸胧，結(jié)果婚禮上赶站，老公的妹妹穿的比我還像新娘。我一直安慰自己吓揪，他們只是感情好亲怠，可當(dāng)我...
文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著柠辞，像睡著了一般团秽。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上叭首，一...
那天习勤，我揣著相機(jī)與錄音，去河邊找鬼焙格。 笑死图毕，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的眷唉。 我是一名探鬼主播予颤，決...
沈念sama閱讀 40,145評(píng)論 3贊 418 文/蒼蘭香墨 我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼冬阳！你這毒婦竟也來(lái)了蛤虐？” 一聲冷哼從身側(cè)響起，我...
序言：老撾萬(wàn)榮一對(duì)情侶失蹤肝陪，失蹤者是張志新（化名）和其女友劉穎驳庭，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體氯窍，經(jīng)...
沈念sama閱讀 45,443評(píng)論 1贊 314 正文 獨(dú)居荒郊野嶺守林人離奇死亡饲常，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了狼讨。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片贝淤。...
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖政供，靈堂內(nèi)的尸體忽然破棺而出播聪，到底是詐尸還是另有隱情，我是刑警寧澤鲫骗，帶...
沈念sama閱讀 35,501評(píng)論 5贊 345 正文 年R本政府宣布犬耻，位于F島的核電站踩晶，受9級(jí)特大地震影響执泰，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜渡蜻，卻給世界環(huán)境...
文/蒙蒙 一术吝、第九天 我趴在偏房一處隱蔽的房頂上張望计济。 院中可真熱鬧，春花似錦排苍、人聲如沸沦寂。這莊子的主人今日做“春日...
文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)传藏。三九已至，卻和暖如春彤守，著一層夾襖步出監(jiān)牢的瞬間毯侦，已是汗流浹背。 一陣腳步聲響...
我被黑心中介騙來(lái)泰國(guó)打工具垫， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留侈离，地道東北人。 一個(gè)月前我還...
沈念sama閱讀 47,899評(píng)論 2贊 370 正文 我出身青樓筝蚕，卻偏偏與公主長(zhǎng)得像卦碾，于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子起宽，可洞房花燭夜當(dāng)晚...
