創(chuàng)建高性能索引
索引是什么?有什么作用壳咕?
索引是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)
如書(shū)的目錄索引一般拟糕,數(shù)據(jù)庫(kù)中的索引也是另外存儲(chǔ)一些數(shù)據(jù)表的關(guān)鍵信息屡限,在查詢(xún)的時(shí)候可以更方便定位到想要找的那一些內(nèi)容頁(yè)
在數(shù)據(jù)量小、負(fù)載低的時(shí)候压昼,索引的作用可能并不明顯求冷。但一旦到達(dá)一定量級(jí)瘤运,正確的索引往往可以輕易將性能提升幾個(gè)數(shù)量級(jí)。所以匠题,索引優(yōu)化是查詢(xún)性能優(yōu)化最有效的手段
大多數(shù)框架都實(shí)現(xiàn)了ORM拯坟,而ORM可能很難兼顧到 每個(gè)開(kāi)發(fā)者、每個(gè)業(yè)務(wù) 專(zhuān)門(mén)定制化的索引韭山。但這并不意味著使用框架和ORM就不需要理解索引郁季!最好的方法是:
簡(jiǎn)單的、數(shù)據(jù)量較小的查詢(xún)可以直接使用ORM钱磅,當(dāng)某些復(fù)雜的查詢(xún)出現(xiàn)性能問(wèn)題時(shí)梦裂,考慮舍棄ORM,采用原生的SQL語(yǔ)句去優(yōu)化性能
索引的類(lèi)型
索引是在存儲(chǔ)引擎層上實(shí)現(xiàn)的盖淡,而非服務(wù)器層年柠。所以每個(gè)引擎支持的索引類(lèi)型、實(shí)現(xiàn)方法都有所不同褪迟。在使用上需要有所注意冗恨。這里主要討論的是InnoDB引擎的索引
B-Tree 索引
最常見(jiàn)的索引類(lèi)型,絕大多數(shù)MySQL引擎都支持味赃。一般沒(méi)有特殊說(shuō)明掀抹,索引即B-Tree索引(這類(lèi)索引采用類(lèi)似B-Tree的結(jié)構(gòu)來(lái)存儲(chǔ)數(shù)據(jù),如 B樹(shù)洁桌、B+樹(shù) 等)
InnoDB使用B+ Tree的結(jié)構(gòu)存儲(chǔ)索引
為什么通過(guò)索引查找數(shù)據(jù)渴丸,可以加快訪問(wèn)的速度?
B-Tree 結(jié)構(gòu)存儲(chǔ)索引另凌,通常意味著所有數(shù)據(jù)按照一定的順序存儲(chǔ)。在查找的時(shí)候戒幔,引擎就可以從根結(jié)點(diǎn)比較節(jié)點(diǎn)頁(yè)的值和要查找的值吠谢,找到合適的指針進(jìn)入下層子節(jié)點(diǎn),不斷重復(fù)比較最終找到相應(yīng)值诗茎。避免了使用全表掃描
特別的工坊,在B+ Tree結(jié)構(gòu)中,所有的非葉子節(jié)點(diǎn)都用作“查找”敢订,葉子節(jié)點(diǎn)則指向具體的數(shù)據(jù)王污。如:有100條記錄,對(duì)于一個(gè)索引樹(shù)楚午,有且僅有100個(gè)葉子節(jié)點(diǎn)昭齐,它們分別指向每一條記錄,這些記錄本身是有序且用指針相連
所以矾柜,索引在按照索引順序去查找范圍數(shù)據(jù)的時(shí)候阱驾,效率也非常高
索引對(duì)多個(gè)值進(jìn)行排序的記錄順序
如就谜,組合索引key(last_name, first_name, birth)
,這個(gè)索引對(duì)應(yīng)的記錄的順序是里覆,先按照l(shuí)ast_name排序丧荐,如果相同,則按first_name喧枷,最后按birth虹统。
這就是為什么條件的順序必須與組合索引的順序一致
order by也可以使用索引
因?yàn)樗饕龢?shù)中的節(jié)點(diǎn)是有序的,所以除了按值查找之外隧甚,索引還可以用于查詢(xún)中的order by操作
可以使用B-Tree 索引的查詢(xún)類(lèi)型
假設(shè)有一索引窟却,key(last_name, first_name, birth)
- 全值匹配
last_name='ABC' and first_name='DEF' and birth='1999-01-01'
- 匹配最左前綴
last_name='ABC'
- 匹配列前綴
last_name LIKE 'AB%'
- 匹配范圍值
last_name >= 'A' and last_name <= 'C'
- 精確匹配某一列,并范圍匹配另一列
last_name='ABC' and first_name > 'C'
- 只訪問(wèn)索引(不訪問(wèn)值)的查詢(xún) (覆蓋索引)
select last_name, first_name, birth from xxtbl
B-Tree 索引的查詢(xún)限制
- 必須從最左列開(kāi)始匹配呻逆,否則該索引不可用
- 不能跳過(guò)索引的某一列
- 如果查詢(xún)中有某個(gè)列是范圍匹配夸赫,則右邊的其他列無(wú)法使用索引
哈希索引
- 基于哈希表實(shí)現(xiàn),只有精確匹配索引所有列的查詢(xún)才有效
- 存儲(chǔ)引擎針對(duì)每一行計(jì)算出一個(gè)哈希碼咖城,與行指針保存成一張表茬腿,便于查詢(xún)
- 常用的InnoDB和MyISAM引擎并不支持,但I(xiàn)nnoDB引擎有自適應(yīng)哈希索引宜雀,它會(huì)根據(jù)查詢(xún)切平,動(dòng)態(tài)優(yōu)化(不需要自己設(shè)置索引)
自定義(偽)哈希索引
不支持哈希索引的引擎,可以通過(guò)新增一個(gè)被索引的hash列辐董,用crc32等算法做哈希悴品,可以?xún)?yōu)化查詢(xún)
例子:需要存儲(chǔ)并查詢(xún)一個(gè)url列
select * from tbl where url = 'http://xx.com/xx/yy/?zz=aa&bb=cc'
上面的語(yǔ)句明顯在比較上非常吃力,如果對(duì)url列設(shè)置索引简烘,需要比較大的代價(jià)苔严。如果引入一個(gè)被索引的hash列,在查找的時(shí)候孤澎,性能會(huì)變得很高
select * from tbl where url_crc = CRC32('URL...') and url = 'URL...'
注意:
- 不要直接使用強(qiáng)加密的hash算法届氢,如md5、sha1等覆旭,否則生成過(guò)長(zhǎng)的字符串退子,效果不好
- 也需要注意避免過(guò)多的哈希沖突,如果crc32不滿(mǎn)足型将,可以使用fnv64或自行實(shí)現(xiàn)一些哈希方案
- 精確匹配查詢(xún)的時(shí)候寂祥,必須在條件中同時(shí)帶上原列和hash列,如
where crc=CRC32('xx') and url='xx'
- 如果不想維護(hù)hash列七兜,可以創(chuàng)建觸發(fā)器丸凭,在創(chuàng)建的時(shí)候自動(dòng)填充
全文索引
一種特殊類(lèi)型的索引,查找的是文本中的關(guān)鍵詞,而不是直接比較索引中的值贮乳。它更類(lèi)似于搜索引擎做的事情忧换。另外,在列上同時(shí)創(chuàng)建全文索引和B-Tree索引向拆,不會(huì)有沖突
索引的優(yōu)點(diǎn)
- 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
- 索引可以幫助服務(wù)器避免排序和臨時(shí)表
- 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O
另外需要注意的是亚茬,索引的建立和使用需要一些空間和額外工作。所以浓恳,如果表本身很小且可控刹缝,全表掃描或許更快
高性能的索引策略(如何在SQL語(yǔ)句中使用索引)
獨(dú)立的列
如果列本身是表達(dá)式的一部分,或是函數(shù)的參數(shù)颈将,這種“不獨(dú)立的列”無(wú)法使用此列索引梢夯。如:select * from tbl where id + 1 < 5
前綴索引
有時(shí)需要索引一個(gè)較長(zhǎng)的字符列,這樣會(huì)使索引變得大且慢晴圾,解決方案可以是之前說(shuō)的哈希索引颂砸,但有時(shí)這樣仍然不夠好。這時(shí)候需要考慮使用前綴索引死姚。
對(duì)于TEXT人乓、BLOB和較長(zhǎng)的VARCHAR,如果確實(shí)需要索引都毒,則必須創(chuàng)建前綴索引色罚,設(shè)置方法如下:
alter table tbl add key (city(7))
這樣就設(shè)置了一個(gè)針對(duì)city字段前7個(gè)字符做索引的前綴索引。
前綴索引是一種能使索引更小账劲、更快的有效方法戳护。但缺點(diǎn)是不支持order by, group by和覆蓋掃描
難點(diǎn):如何確定一個(gè)較好的前綴值?(上面的例子是7)
-- 首先確定所有記錄中瀑焦,distinct的記錄占比
select count(distinct city)/count(*) from tbl;
-- 假設(shè)上面得到的結(jié)果值為0.3腌且,則表示城市平均重復(fù)2次左右
-- 為了前綴索引有良好的區(qū)分度,要求使用索引之后蝠猬,占比較接近實(shí)際的0.3
select count(distinct left(city, 3))/count(*) as city3,
count(distinct left(city, 4))/count(*) as city4,
count(distinct left(city, 5))/count(*) as city5,
...
from tbl;
-- 最后取一個(gè)接近0.3切蟋,且前綴值不太大的一個(gè)值,作為最后的前綴值
多列索引
一個(gè)錯(cuò)誤的索引設(shè)置方案:WHERE條件的列都建立一個(gè)單獨(dú)的索引
示例: create table t (c1 int, c2 int, c3 int, KEY(c1), KEY(c2), KEY(c3));
導(dǎo)致的結(jié)果是在多條件查詢(xún)的時(shí)候榆芦,無(wú)論怎么選擇,都只能使用其中一個(gè)索引喘鸟,而之后的條件則沒(méi)有索引可以走
在新版本的MySQL中匆绣,查詢(xún)會(huì)被優(yōu)化成多條走索引的語(yǔ)句,然后聯(lián)合或相交得出結(jié)果什黑。但這樣做相當(dāng)于多次查詢(xún)崎淳,明顯不是最優(yōu)的索引策略
應(yīng)該采用的方式是,根據(jù)查詢(xún)愕把,設(shè)置組合索引
選擇合適的索引列順序
我們已知查詢(xún)的順序會(huì)影響到索引列順序的設(shè)置
- 將選擇性最高的列放在前面通常是比較好的
- 除了考慮WHERE條件的優(yōu)化拣凹,也需要考慮GROUP BY, ORDER BY, DISTINCT
- 考慮值的分布情況森爽。可以把區(qū)分度大的列放在前面嚣镜,適當(dāng)去調(diào)整語(yǔ)句的編寫(xiě)
- 極端的一些情況爬迟。比如某一列中,某個(gè)值出現(xiàn)的概率極高菊匿,這種可能不太適合建立索引
針對(duì)第三個(gè)情況的例子:
假設(shè)有一張表有customer_id
和staff_id
付呕,這兩列經(jīng)常作為條件用于篩選。此時(shí)可以通過(guò)實(shí)際情況或SQL進(jìn)行查詢(xún)跌捆,不同的值更多的列可以考慮放在前面
select
count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*)
from payment;
-- res:
-- staff_id_selectivity: 0.0001, customer_id_selectivity: 0.0373
-- 此時(shí)應(yīng)該選擇 KEY(customer_id, staff_id)
聚簇索引
聚簇索引并不是一種單獨(dú)的索引類(lèi)型徽职,而是一種數(shù)據(jù)存儲(chǔ)方式。對(duì)于InnoDB來(lái)說(shuō)佩厚,聚簇索引實(shí)際上是同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行
一張表只能有一個(gè)聚簇索引姆钉,因?yàn)橐环輸?shù)據(jù)只能保存在一個(gè)地方。在InnoDB中抄瓦,不能直接選擇某一列成為聚簇索引潮瓶,而是直接通過(guò)主鍵作為聚簇索引
聚簇索引的優(yōu)點(diǎn):
- 可以把相關(guān)的數(shù)據(jù)保存在一起。例如通過(guò)用戶(hù)id獲取用戶(hù)的數(shù)據(jù)闺鲸,如果用戶(hù)id不是聚簇索引筋讨,則找到用戶(hù)id節(jié)點(diǎn)之后,還需要一次I/O才能拿到用戶(hù)的數(shù)據(jù)
- 相比非聚簇索引摸恍,數(shù)據(jù)訪問(wèn)更快悉罕,理由同上
- 使用覆蓋索引掃描的查詢(xún),可以直接使用頁(yè)節(jié)點(diǎn)中的(其他)主鍵值
聚簇索引的缺點(diǎn):
- 聚簇索引可以最大限度提高I/O密集型應(yīng)用的效率立镶。但如果所有數(shù)據(jù)都已經(jīng)加載到內(nèi)存中壁袄,則訪問(wèn)順序沒(méi)那么重要,聚簇索引也就沒(méi)什么優(yōu)勢(shì)
- 插入速度嚴(yán)重依賴(lài)插入排序媚媒,如果不是按照主鍵順序加載數(shù)據(jù)嗜逻,那么在加載完成后最好使用
OPTIMAZE TABLE
命令重新組織一下表 - 更新聚簇索引列的代價(jià)很高,因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置
- 基于聚簇索引的表在插入新行缭召,或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候栈顷,可能面臨“頁(yè)分裂”的問(wèn)題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿(mǎn)的頁(yè)中時(shí)嵌巷,存儲(chǔ)引擎會(huì)將該頁(yè)分裂成兩個(gè)頁(yè)面來(lái)容納該行萄凤,這就是一次頁(yè)分裂操作,頁(yè)分裂會(huì)導(dǎo)致表占用更多的磁盤(pán)空間
- 聚簇索引可能導(dǎo)致全表掃描變慢搪哪,尤其是行比較稀疏靡努,或者由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí)候
- 二級(jí)索引(非聚簇索引)可能比想象中的要更大,因?yàn)樵诙?jí)索引的葉子節(jié)點(diǎn)包含了引用行的主鍵列
- 二級(jí)索引訪問(wèn)需要兩次索引查找,而不是一次
對(duì)于InnoDB引擎的表來(lái)說(shuō)
- 如果沒(méi)有什么數(shù)據(jù)需要聚集惑朦,可以設(shè)置一個(gè)自增列作為主鍵兽泄。保證順序?qū)懭牒鸵恍┎僮鞯谋憬菪?/li>
- 最好避免隨機(jī)的(不連續(xù)且值的分布范圍非常大)聚簇索引,特別是對(duì)于I/O密集型的應(yīng)用漾月。從性能上來(lái)說(shuō)病梢,類(lèi)似UUID這種隨機(jī)性很大的內(nèi)容,作為聚簇索引會(huì)非常糟糕
所以通常情況下栅屏,保證InnoDB的主鍵是順序插入的飘千,減少不必要的調(diào)整和頁(yè)分裂,性能更優(yōu)
在高并發(fā)的時(shí)候栈雳,順序的主鍵可能會(huì)造成爭(zhēng)用护奈,這種情況下不使用順序的主鍵
覆蓋索引
如果一個(gè)索引包含(覆蓋)所有需要查詢(xún)的字段的值,我們就稱(chēng)之為“覆蓋索引”
好處:索引中已經(jīng)包含了全部數(shù)據(jù)哥纫,不需要回表查詢(xún)霉旗。通常來(lái)說(shuō),索引沒(méi)有太多多余的數(shù)據(jù)蛀骇,規(guī)模小厌秒,所以查起來(lái)速度很快(MySQL中只有B-Tree 索引可以做覆蓋索引)
當(dāng)發(fā)起一個(gè)覆蓋索引的查詢(xún)時(shí),在EXPLAIN的Extra列可以看到“Using index”的信息
一則簡(jiǎn)單明了的例子:
有一張products表擅憔,有主鍵prod_id鸵闪,以及關(guān)聯(lián)其他表的的兩個(gè)id:aid、bid暑诸,這兩個(gè)id形成一個(gè)key(aid, bid)
蚌讼,然后還有其他的一些產(chǎn)品信息
select aid, bid from products;
上述語(yǔ)句就是最簡(jiǎn)單的覆蓋索引查詢(xún),涉及的列只有索引列
復(fù)雜一些的例子:
products表中有一個(gè)key(actor, title)
个榕,但是現(xiàn)在要查出滿(mǎn)足條件的所有數(shù)據(jù)項(xiàng)
select * from products where actor='abc' and title like '%abc%';
上述語(yǔ)句并不能覆蓋索引篡石。且title前面也有%,故最終只能使用actor索引數(shù)據(jù)西采,然后回表查滿(mǎn)足條件的title凰萨,再返回整行數(shù)據(jù)
解決方案:
建立索引key(actor, title, prod_id)
select * from products join (
select prod_id from products where actor='abc' and title like '%abc%'
) t1 on (t1.prod_id = products.prod_id);
上述語(yǔ)句的子查詢(xún)滿(mǎn)足覆蓋索引的查詢(xún)(一個(gè)索引包含所有需要查詢(xún)的字段的值)。另外械馆,where條件也可以匹配最左前綴actor
如果在上述子查詢(xún)中胖眷,過(guò)濾掉了大多數(shù)的不符合條件的products,且本身此表的規(guī)模有一定大小霹崎,則優(yōu)化效果會(huì)很好瘦材,否則可能適得其反
示例:
- abc這個(gè)actor的條目有30000條,其中20000條title包含有abc => 這個(gè)屬于規(guī)模大仿畸、過(guò)濾少的,效果不好
- abc這個(gè)actor的條目有30000條,其中40條title包含有abc => 這個(gè)屬于規(guī)模大错沽、過(guò)濾多的簿晓,效果很好
- abc這個(gè)actor的條目有50條,其中10條title包含有abc => 這個(gè)屬于規(guī)模小千埃,效果不好(子查詢(xún)需要耗費(fèi)一些性能)
另外的:
InnoDB引擎的二級(jí)索引的葉子節(jié)點(diǎn)是包含主鍵的值的憔儿,所以,主鍵是可以作為查詢(xún)列覆蓋索引的
使用索引掃描來(lái)做排序
MySQL有兩種方式可以生成有序的結(jié)果:
- 通過(guò)排序操作
- 按索引順序掃描
當(dāng)按索引順序掃描排序時(shí)放可,在EXPLAIN的type列會(huì)顯示index谒臼。這里注意區(qū)分之前的覆蓋索引,覆蓋索引是在Extra列顯示“Using index”
掃描索引本身速度很快耀里,但是如果不能覆蓋查詢(xún)的所有列蜈缤,就不得不每掃描一條索引記錄就回表查詢(xún)一次對(duì)應(yīng)的行,這樣會(huì)造成隨機(jī)I/O冯挎,這種情況是不如順序地全表掃描的
所以底哥,只有以下條件同時(shí)滿(mǎn)足,引擎才會(huì)按索引順序掃描:
- 滿(mǎn)足最左前綴的要求房官,即order by的列必須是索引列的最左前綴子列
- order by的列順序和索引列順序一致趾徽,且所有列的排列方向(正序、倒序)必須一致
- 如果查詢(xún)需要關(guān)聯(lián)多張表翰守,則只有當(dāng)order by的列都是第一張表的字段才可以
有一種情況可以不滿(mǎn)足最左前綴孵奶,就是前導(dǎo)列為常量的時(shí)候。即假設(shè)有
KEY(a,b,c)
蜡峰,WHERE或JOIN子句對(duì)a已經(jīng)指定了定值了袁,則ORDER BY可以只針對(duì)b和c字段
MySQL可以使用同一個(gè)索引滿(mǎn)足排序和查找行的任務(wù),如果可能事示,盡量設(shè)計(jì)這樣的索引
例子:假設(shè)有KEY(rental_date, inventory_id, customer_id)
以下方式可以實(shí)現(xiàn)索引掃描:
- 前導(dǎo)列為常量早像,最左前綴:
... where rental_date = '2005-05-25' order by inventory_id desc
- 純粹的最左前綴(與where無(wú)關(guān)):
... where rental_date > '2005-05-25' order by rental_date, inventory_id
以下方式不能實(shí)現(xiàn)索引掃描:
- 排列方向不一致:
... where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc
- 引用了不在索引的列:
... where rental_date = '2005-05-25' order by inventory_id, staff_id
- 不能組合成最左前綴:
... where rental_date = '2005-05-25' order by customer_id
- 前導(dǎo)列為范圍值:
... where rental_date > '2005-05-25' order by inventory_id, customer_id
- 前導(dǎo)列使用了IN,相當(dāng)于范圍值:
... where rental_date = '2005-05-25' and inventory_id in (1, 2) order by customer_id
壓縮(前綴壓縮)索引
MyISAM使用前綴壓縮來(lái)減少索引的大小肖爵,從而讓更多的索引放入內(nèi)存
特點(diǎn):
- 默認(rèn)只壓縮字符串
- 方式大致是:對(duì)于一個(gè)索引塊卢鹦,先保存一個(gè)完整的字符串,然后后面如果有前綴相同的字符串劝堪,則只存儲(chǔ)不相同的值冀自。比如,存儲(chǔ)一個(gè)“perform”秒啦,之后存儲(chǔ)“performance”只需要存儲(chǔ)“7,ance”即可
- 無(wú)法在索引塊中使用二分查找熬粗,如果倒序掃描——
order by xx desc
的話(huà),由于需要從頭開(kāi)始掃描余境,所以會(huì)比較慢 - 適合I/O密集型的應(yīng)用驻呐,不適合CPU密集型的應(yīng)用
重復(fù)索引灌诅、冗余索引
MySQL允許在相同列創(chuàng)建多個(gè)索引,并需要維護(hù)重復(fù)的索引含末,在優(yōu)化器優(yōu)化查詢(xún)時(shí)猜拾,逐個(gè)考慮(這種情況是影響性能的。有些沒(méi)有意義佣盒,需要去掉挎袜,但有時(shí)候是需要的)
重復(fù)索引:在相同的列按照相同的順序創(chuàng)建相同類(lèi)型的索引。這種是必須去掉的
錯(cuò)誤示范:
create table t(
ID int NOT NULL PRIMARY KEY,
A int NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
上面三種索引(主鍵肥惭、唯一盯仪、普通索引)是同類(lèi)型的B-Tree 索引,必須去掉(如果索引分別是INDEX和FULLTEXT蜜葱,它們屬于不同類(lèi)型的索引全景,也不是重復(fù)索引,是完全沒(méi)問(wèn)題的)
冗余索引:如果已有KEY(A, B)
笼沥,這時(shí)再創(chuàng)建一個(gè)KEY(A)
蚪燕,由于前者的左前綴包含A,所以后者是冗余索引奔浅。這種需要按實(shí)際情況分析
另外馆纳,在InnoDB中,主鍵默認(rèn)已經(jīng)包含在二級(jí)索引中汹桦,所以 KEY(A)
和 KEY(A, ID)
如果同時(shí)存在鲁驶,也屬于冗余索引
大多數(shù)情況,冗余索引是不需要的舞骆,應(yīng)該盡量擴(kuò)展已有的索引而不是建立新的索引钥弯。但如果擴(kuò)展索引會(huì)導(dǎo)致索引太大,影響原有的查詢(xún)督禽,這時(shí)就需要考慮冗余索引了
例子:如果在整數(shù)列上有一個(gè)索引脆霎,現(xiàn)在需要額外加一個(gè)長(zhǎng)的VARCHAR列來(lái)擴(kuò)展索引
假設(shè)原來(lái)有以下需求,記作Q1狈惫。對(duì)應(yīng)有一個(gè)索引KEY(state_id)
select count(*) from userinfo where state_id = 5;
現(xiàn)在有另外的新需求睛蛛,記作Q2。如果引入覆蓋索引胧谈,對(duì)應(yīng)的索引是KEY(state_id, city, address)
select city, address from userinfo where state_id = 5;
由于MyISAM前綴索引的原因忆肾,如果采用直接擴(kuò)展的方式,性能下降很?chē)?yán)重菱肖;InnoDB影響不太大客冈。書(shū)上例子的測(cè)試結(jié)果:
只有state_id | 只有擴(kuò)展索引 | 同時(shí)有state_id和擴(kuò)展索引 | |
---|---|---|---|
MyISAM, Q1 | 114.96 | 25.40 | 112.19 |
MyISAM, Q2 | 9.97 | 16.34 | 16.37 |
InnoDB, Q1 | 108.55 | 100.33 | 107.97 |
InnoDB, Q2 | 12.12 | 28.04 | 28.06 |
有兩個(gè)索引的缺點(diǎn)就是,索引成本高稳强,增刪改記錄的效率變低场仲。書(shū)上例子的測(cè)試結(jié)果:
插入100W行數(shù)據(jù)(單位:秒) | 只有state_id | 同時(shí)有state_id和擴(kuò)展索引 |
---|---|---|
InnoDB | 80 | 136 |
MyISAM(壓縮索引) | 72 | 470 |
在決定刪除“認(rèn)為無(wú)用”的冗余索引時(shí)和悦,需要檢查是否有采用到主鍵作為二級(jí)索引的查詢(xún),如... where A = 1 order by id
燎窘。如果有摹闽,當(dāng)去掉
KEY(A)
,只保留 KEY(A, B)
褐健,會(huì)導(dǎo)致此查詢(xún)不能用索引掃描排序
可以使用一些工具檢測(cè)重復(fù)和冗余的索引
未使用的索引
可能會(huì)有服務(wù)器永遠(yuǎn)不用的索引,這種也建議刪除澜汤。同樣可以通過(guò)工具檢測(cè)出