MySQL的索引優(yōu)化

MySQL支持的索引類(lèi)型

B-tree的特點(diǎn)

• 加快查詢(xún)的速度
• 更適合返回查找

enter description here

什么情況下用到B-tree

• 全值匹配查詢(xún)
• 匹配最左前綴的查詢(xún)
• 匹配列前綴的查詢(xún)
• 匹配范圍查找
• 精確匹配左前并方位匹配另外一列
• 只訪(fǎng)問(wèn)索引的查詢(xún)

B-tree的限制

• 如果是按照索引的最左列開(kāi)始查找的，則無(wú)法使用索引
• 使用索引時(shí)不能跳過(guò)索引中的列
• Not in 和 <> 無(wú)法使用索引
• 如果查詢(xún)中又某個(gè)列的范圍查詢(xún)，則右邊的列都無(wú)法使用索引

Hash索引

特點(diǎn)

基于Hash表實(shí)現(xiàn)的，只能查詢(xún)條件精確的匹配。

對(duì)于Hash索引中所有的列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)Hash碼，Hash索引中存放的就是Hash碼。

限制

enter description here

• Hash索引必須進(jìn)行二次查找。

• Hash索引無(wú)法用于排序

• 只能進(jìn)行全值匹配，不支持范圍查找。不選擇列重復(fù)的字段上加這個(gè)索引。

聯(lián)合索引

什么是聯(lián)合索引：

聯(lián)合索引又叫復(fù)合索引。對(duì)于復(fù)合索引:Mysql從左到右的使用索引中的字段，一個(gè)查詢(xún)可以只使用索引中的一部份，但只能是最左側(cè)部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進(jìn)行查找，但不支持 b,c進(jìn)行查找 .當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效。

注意：

1、需要加索引的字段，要在where條件中
2、數(shù)據(jù)量少的字段不需要加索引
3、如果where條件中是OR關(guān)系，加索引不起作用
4、符合最左原則

如何選擇索引列的順序？

• 經(jīng)常被使用到的列優(yōu)先（經(jīng)常查詢(xún)的放在最左面）
• 選擇性能高的列優(yōu)先
• 寬度小的列優(yōu)先

覆蓋索引

優(yōu)點(diǎn)

• 可以?xún)?yōu)化緩存，減少磁盤(pán)I/o
• 可以減少隨機(jī)I/O，將隨機(jī)IO變成順序IO
• 可以避免對(duì)Innodb主鍵引起的二次查詢(xún)
• 可以避免MYISAM表進(jìn)行系統(tǒng)調(diào)用

缺點(diǎn)

• 存儲(chǔ)引擎不支持覆蓋索引
• 查詢(xún)使用了太多的列
• 使用率了%like%查詢(xún)

使用索引來(lái)優(yōu)化查詢(xún)

利用索引優(yōu)化鎖

• 索引可以減少鎖定的行數(shù)
• 可以加快處理的速度，同時(shí)也加快了鎖的釋放

刪除重復(fù)和冗余的索引

定期更新索引的統(tǒng)計(jì)信息以及減少索引碎片

analyze table table_name

建立了主鍵索引就沒(méi)必要?jiǎng)?chuàng)建唯一索引了

代碼實(shí)例

覆蓋查詢(xún)

查詢(xún)所有的數(shù)據(jù)是無(wú)法使用覆蓋索引的

使用索引引來(lái)優(yōu)化索引

使用索引掃描來(lái)優(yōu)化排序

• 索引順序和Order By 的子句的循序完全一致
• 索引中所有的方向（升序、降序）和Order by 子句完全一致
• Order by 中的字段全部在關(guān)聯(lián)中的第一張表中

為什么使用索引

• 加上存儲(chǔ)引擎需要掃描數(shù)據(jù)的數(shù)量
• 避免我們使用臨時(shí)表
• 可以把隨機(jī)I/O變成順序I/O

索引不是越多越好

• 索引會(huì)增加寫(xiě)操作成本
• 太多的索引會(huì)增加查詢(xún)優(yōu)化器選擇的時(shí)間

索引優(yōu)化策略

image

image