《高性能MySQL》筆記(2)——?jiǎng)?chuàng)建高性能索引

創(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ǔ)索引

B+樹(shù)

參考鏈接:理解B+樹(shù)算法和Innodb索引

為什么通過(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_idstaff_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ì)很好瘦材,否則可能適得其反

示例:

  1. abc這個(gè)actor的條目有30000條,其中20000條title包含有abc => 這個(gè)屬于規(guī)模大仿畸、過(guò)濾少的,效果不好
  2. abc這個(gè)actor的條目有30000條,其中40條title包含有abc => 這個(gè)屬于規(guī)模大错沽、過(guò)濾多的簿晓,效果很好
  3. 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è)出

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末蚜迅,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子俊抵,更是在濱河造成了極大的恐慌谁不,老刑警劉巖,帶你破解...
    沈念sama閱讀 217,277評(píng)論 6 503
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件徽诲,死亡現(xiàn)場(chǎng)離奇詭異刹帕,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)谎替,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,689評(píng)論 3 393
  • 文/潘曉璐 我一進(jìn)店門(mén)偷溺,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人钱贯,你說(shuō)我怎么就攤上這事挫掏。” “怎么了秩命?”我有些...
    開(kāi)封第一講書(shū)人閱讀 163,624評(píng)論 0 353
  • 文/不壞的土叔 我叫張陵尉共,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我弃锐,道長(zhǎng)袄友,這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 58,356評(píng)論 1 293
  • 正文 為了忘掉前任霹菊,我火速辦了婚禮剧蚣,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘浇辜。我一直安慰自己券敌,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,402評(píng)論 6 392
  • 文/花漫 我一把揭開(kāi)白布柳洋。 她就那樣靜靜地躺著待诅,像睡著了一般。 火紅的嫁衣襯著肌膚如雪熊镣。 梳的紋絲不亂的頭發(fā)上卑雁,一...
    開(kāi)封第一講書(shū)人閱讀 51,292評(píng)論 1 301
  • 那天募书,我揣著相機(jī)與錄音,去河邊找鬼测蹲。 笑死莹捡,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的扣甲。 我是一名探鬼主播篮赢,決...
    沈念sama閱讀 40,135評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼琉挖!你這毒婦竟也來(lái)了启泣?” 一聲冷哼從身側(cè)響起,我...
    開(kāi)封第一講書(shū)人閱讀 38,992評(píng)論 0 275
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤示辈,失蹤者是張志新(化名)和其女友劉穎寥茫,沒(méi)想到半個(gè)月后,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體矾麻,經(jīng)...
    沈念sama閱讀 45,429評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡纱耻,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,636評(píng)論 3 334
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了险耀。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片弄喘。...
    茶點(diǎn)故事閱讀 39,785評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖胰耗,靈堂內(nèi)的尸體忽然破棺而出限次,到底是詐尸還是另有隱情,我是刑警寧澤柴灯,帶...
    沈念sama閱讀 35,492評(píng)論 5 345
  • 正文 年R本政府宣布卖漫,位于F島的核電站,受9級(jí)特大地震影響赠群,放射性物質(zhì)發(fā)生泄漏羊始。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,092評(píng)論 3 328
  • 文/蒙蒙 一查描、第九天 我趴在偏房一處隱蔽的房頂上張望突委。 院中可真熱鬧,春花似錦冬三、人聲如沸匀油。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 31,723評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)敌蚜。三九已至,卻和暖如春窝爪,著一層夾襖步出監(jiān)牢的瞬間弛车,已是汗流浹背齐媒。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 32,858評(píng)論 1 269
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留纷跛,地道東北人喻括。 一個(gè)月前我還...
    沈念sama閱讀 47,891評(píng)論 2 370
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像贫奠,于是被迫代替她去往敵國(guó)和親唬血。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,713評(píng)論 2 354

推薦閱讀更多精彩內(nèi)容