《高性能Mysql》-第五章-創(chuàng)建高性能的索引

1.b-樹索引

索引首先要回顧一下b樹b+樹的特點和區(qū)別，數(shù)據(jù)庫引擎用b+樹的好處有查詢時間比較穩(wěn)定，b+樹比較適合范圍查詢,b+樹比較矮胖，b樹的高度代表隨機(jī)io的次數(shù)，相對的查詢時間會比較短。

如圖是索引的一般設(shè)計

mysql索引

非葉子也擁有指向葉子頁的指針，葉子頁之間也存在指針。

文中說道:邏輯頁依賴于不同的存儲引擎，對于InnoDB為16k。對此“頁”的概念做一些資料查找。

頁是innodb存儲引擎的最小存儲單位，有數(shù)據(jù)頁,undo頁，系統(tǒng)頁，事務(wù)處理頁。默認(rèn)的頁是16kb,每個頁上至少有2條以上的記錄。

b-樹適合的查詢類型

  • 全鍵值
  • 鍵值范圍
  • 鍵值前綴(最左前綴)

其中具體包含如下(索引為lastname,firstname,birthday)

  • 全部鍵值 查找lastname=Allen,firstname=Cuba,birthday='1960-01-01'
  • 匹配最左前綴 查找lastname=Allen
  • 匹配列前綴 查找lastname like"A%"
  • 匹配范圍值 查找lastname>'A' and lastname<'D'
  • 精確匹配某一列并范圍匹配另一列 lastname='Allen' and firstname like "f%"
  • 覆蓋索引的查詢

orderBy是否能用到索引的條件和查詢是否能用到索引的條件一致

不能使用索引的限制

  • 不是從最左開始 查找 firstname='Cuba'
  • 不能跳過索引中的列 查找 lastname='Allen' and birthday='2019-01-01'
  • 查詢中某一列用了范圍查詢，右邊的所有列無法用索引再優(yōu)化 查找 lastname='A' and firstname like "A%" and birthday = '2019-01-01'

2.哈希索引

哈希索引基于哈希表實現(xiàn)，只有精確匹配索引所有列才有效。對于每一行數(shù)據(jù)，對所有索引列計算哈希碼，哈希索引將哈希碼存儲在索引中，同時在哈希表中保存指向每個數(shù)據(jù)行的指針。

Memory引擎顯示支持哈希索引，同時也支持B-tree索引。而且Memory引擎支持非唯一哈希索引，索引會以鏈表的方式存在同一條哈希條目中。

哈希索引速度非?？?，但是限制如下:

  • 哈希索引只包含哈希值和行指針，不能在索引中直接查到數(shù)據(jù)行(對速度影響較小)。
  • 哈希索引不是按照索引值順序排序的，無法用于排序。
  • 不支持單個索引列查找，只有部分列是無法使用索引的
  • 只支持等值查詢，不支持范圍查詢
  • 哈希沖突時查詢速度會變慢

innodb有個特殊功能叫"自適應(yīng)哈希索引(adaptive hash index)",當(dāng)Innodb注意到某些索引值使用非常頻繁的時候，會在內(nèi)存基于B-tree索引之上創(chuàng)建一個哈希索引，讓B-Tree索引具有哈希索引的優(yōu)點，這個功能是完全自動的，內(nèi)部行為，用戶無法控制或者配置。

這里有個擴(kuò)展知識就是在平時使用Mysql時我們對一個字段添加了hash索引的話，重新show INDEXES from tableA 會發(fā)現(xiàn)的index_type還是BTREE，這個就是上面所說的自適應(yīng)哈希，其實還是在BTREE的基礎(chǔ)上做了哈希索引。

在InnoDB中如何優(yōu)化如下查詢
select id from url where url = "http://www.mysql.com";
可以在該數(shù)據(jù)庫手動建立一個哈希索引列，基于這一列來查詢會更快
select id from url where url = "http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com")

這種方案麻煩的地方在于需要手動維護(hù)url_crc

全文索引

更類似于搜索引擎做的事情，不是簡單的where匹配，全文索引適用于match against操作。

索引的優(yōu)點

  • 減少服務(wù)器需要掃描的數(shù)據(jù)量
  • 幫助服務(wù)器避免排序和臨時表
  • 索引可以將隨機(jī)IO變?yōu)轫樞騃O

如何才能使用到索引

1.獨立的列

索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)

select * from where To_DAYS(current_date) < 10

2.前綴索引和索引選擇性

索引選擇性就是說用了這個索引可以篩選多少行，可以過濾多少行，不重復(fù)的索引值越多，選擇性越大。

文中的例子是對city這個地段做索引，可以用這個公式來測算索引選擇性

select count(distinct city)/count(*) from city_demo

計算前綴索引長度的一個建議就是計算完整列的選擇性，使得前綴的選擇接近于完整列。

select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4 from city_demo

當(dāng)索引字節(jié)增加，索引選擇性沒有太大變化時，即認(rèn)為當(dāng)前長度足夠了。

alter table city_demo add key (city(7));

3.多列索引

4.B—tree索引選擇合適的索引列順序

確定索引列的順序的其中一個原則是將選擇性最高的列放在最前面。

以下面查詢?yōu)槔?/p> 

select * from payment where staff_id = 2 and customer_id = 584;

是應(yīng)該創(chuàng)建(staff_id,customer_id)的索引還是顛倒下順序。
實際上如果staff_id和customer_id的組合數(shù)接近總行數(shù)，那就說明數(shù)據(jù)沒什么區(qū)分度

select count(distinct staff_id,customer_id)/count(*) 比值非常小，說明staff_id,customer_id的組合重復(fù)非常多的時候

那么這個聯(lián)合索引就沒啥用，反而如果customer_id的區(qū)分度很高，只用customer_id做索引會是查詢效率提高很多

聚簇索引

聚簇索引是一種數(shù)據(jù)存儲方式，當(dāng)表有聚簇索引時，數(shù)據(jù)行實際存放在索引的葉子頁。屬于聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。因為無法把數(shù)據(jù)行放在兩個不同的地方，所以一個表只能有一個聚簇索引。

聚簇索引實際上是b-tree+數(shù)據(jù)行

image1

Mysql的內(nèi)建存儲引擎都不支持服務(wù)器選擇哪個索引作為聚簇索引，InnoDB通過主鍵聚集數(shù)據(jù)，即上圖中被索引的列是主鍵列。

聚簇索引的優(yōu)點：

  • 可以把相關(guān)數(shù)據(jù)保存在一起，例如根據(jù)用戶id來查找用戶的郵件，如果沒有使用聚簇索引，可能每封郵件都需要一次IO。（讀到這里我是有疑問的，前面說innodb聚簇索引是主鍵，那主鍵做索引查郵件只可能是一次IO,一個用戶id就一行數(shù)據(jù)。這里我認(rèn)為應(yīng)該是我的誤解，這個說法是針對所有聚簇索引的，而不是單指innodb，而innodb是基于主鍵的)
  • 數(shù)據(jù)訪問更快，索引中直接包含數(shù)據(jù)，比普通索引更快
  • 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點的值

聚簇索引的缺點

  • 聚簇索引是最大限度提高了IO密集型應(yīng)用的性能，但是如果數(shù)據(jù)都在內(nèi)存里，訪問順序就不重要了，聚簇索引也沒什么優(yōu)勢。
  • 插入速度依賴于插入順序，innodb是主鍵做聚簇索引，索引按照主鍵順序插入是最快的
  • 更新聚簇索引列代價比較高。 比如Innodb中的主鍵
  • 基于聚簇索引插入行，會面臨"頁分裂"問題，當(dāng)主鍵插入一個已經(jīng)滿了的頁中，存儲引擎會將該頁分為兩個頁面，會造成占用更多的磁盤空間。
  • 聚簇索引導(dǎo)致全表掃描變慢，由于頁存儲不連續(xù)的時候。
  • 非聚簇索引會比較大，因為二級索引包含引用行的主鍵列
  • 二級索引訪問需要兩次索引查找(innodb一次b-tree，找到主鍵后查找聚簇索引的b-tree,對于innodb，自適應(yīng)哈希能夠減少這種重復(fù)工作)

下面看下MyISAM和Innodb對于索引實現(xiàn)的區(qū)別，創(chuàng)建如下的數(shù)據(jù)表,插入10000行數(shù)據(jù)

create table layout_test(
    col1 int not null,
    clo2 int not null,
    PRIMARY KEY(col1),
    KEY(col2)
)

對于MyISAM引擎，如下:

數(shù)據(jù)列如下


image1

主鍵索引如下


image2

col2索引如下


image3

可以看到主鍵索引和普通索引沒有什么區(qū)別，葉子頁都存放的是行號，用來索引到具體的數(shù)據(jù)行。

對于Innodb引擎，聚簇索引如下:


image4

可以看到innodb主鍵索引的葉子節(jié)點存放的就是行數(shù)據(jù)。每個葉子節(jié)點都包含了主鍵值，事務(wù)ID,用于事務(wù)和MVCC的回滾的指針，以及剩余的列。

還有一個MyISAM和Innodb的二級索引和聚簇索引很不相同。Innodb的二級索引的葉子節(jié)點不是行指針，而是主鍵值，并用主鍵值當(dāng)做行指針，這樣就減少了當(dāng)前行移動或者數(shù)據(jù)頁分裂時二級索引的維護(hù)工作。innodb在移動行時不需要更新二級索引上的指針。

二級索引列的數(shù)據(jù)如下：

image5

下圖可以清楚看出MyISAM和Innodb的區(qū)別

image6

一個問題:在InnoDB表中按主鍵順序插入行的兩種方案，一種主鍵是遞增的id,一種主鍵是uuid，哪種插入速度會比較快?

兩者的區(qū)別如下
順序：


image7

uuid:


image7
  • 寫入的目標(biāo)頁可能已經(jīng)刷到磁盤并從緩存刪除，Innodb在插入前不得不找到并且從目標(biāo)頁存到內(nèi)存中，這會導(dǎo)致大量隨機(jī)IO。
  • 插入是亂序的，Innodb會不得不頻繁地做頁分裂操作，為新的行分配空間，一次插入最少修改三個頁而不是一個頁。
  • 頻繁的頁分離會造成頁變得稀疏，最終會有碎片。

所以當(dāng)隨機(jī)值加入后，需要做一次OPTIMIZE TABLE來重新建表并優(yōu)化頁的填充

覆蓋索引

如果索引的葉子節(jié)點就包含所有要查詢的數(shù)據(jù)，就稱改索引為"覆蓋索引"。例如對表中某兩個字段(A,B)做了聯(lián)合索引，則如果一個Sql語句是查詢A,B兩個字段，那就可以用到覆蓋索引。

覆蓋索引的好處:

  • 索引條目遠(yuǎn)小于數(shù)據(jù)行大小，用覆蓋索引會極大減小數(shù)據(jù)訪問量。
  • 索引是順序存儲的，查詢的IO會比從磁盤讀小很多
  • MyISAM這種數(shù)據(jù)內(nèi)存中只緩存索引，如果調(diào)用數(shù)據(jù)需要系統(tǒng)調(diào)用，會有很大的損耗，直接查索引可以減小很多開銷。
  • InnoDB是聚簇索引，覆蓋索引可以避免對主鍵索引的二次查詢。

覆蓋索引必須要存索引列的值，這個在MySQL只能用B-Tree,哈希全文空間都不行。

在使用覆蓋索引時，explain時會看到extra有"Using index"

image6

如果想查一個表的所有數(shù)據(jù)，但是又想用到覆蓋索引，可以用延遲關(guān)聯(lián)的方法

image6

innodb所有二級索引都包含主鍵，所以本來用到覆蓋索引的再增加主鍵值，依然可以用到覆蓋索引。

使用索引做排序

在rental表添加一個包含三個列的索引(rental_date,inventory_id,customer_id)

如下語句可以用到索引

select * from rental where rental_date='2019-01-01' order by inventory_id,customer_id

下面這個也可以，滿足了最左前綴的要求

select * from rental where rental_date='2019-01-01' order by inventory_id desc

下面這個也可以，因為orderBy使用的也是最左前綴

select * from rental where rental_date>'2019-01-01' order by rental_date,inventory_id desc

下面這個不能用，因為orderBy的排序方向不同，索引列都是正向排序的

select * from rental where rental_date>'2019-01-01' order by inventory_id desc,customer_id ASC

這個也不行，用到了一個非索引的列

select * from rental where rental_date>'2019-01-01' order by inventory_id ,staff_id

這個也不行，無法組成最左前綴

select * from rental where rental_date>'2019-01-01' order by customer_id

這個也不行，第一列是范圍查詢

select * from rental where rental_date>'2019-01-01' order by inventory_id desc,customer_id

這個也不行，也是一種范圍查詢

select * from rental where rental_date='2019-01-01' and inventory_id in (1,2) order by inventory_id desc,customer_id

壓縮索引

MyIsam用前綴壓縮減小索引大小，從而讓更多索引放入內(nèi)存

冗余和重復(fù)索引

Mysql可以對相同列創(chuàng)建多個索引，主鍵、唯一鍵也是通過索引實現(xiàn)的。

例如(A,B)和A這種索引就是冗余索引，因為對于(A,B)只查A也走索引

另外一種情況是(A,ID)也是冗余的，因為InnoDB主鍵列在二級索引中。

索引和鎖

InnoDB只有在訪問行的時候會對其加鎖，但是這個只有在InnoDB在存儲引擎層能過濾掉所有不需要的行時才有效。在InnoDB檢索到數(shù)據(jù)行返回給服務(wù)層，MySQL服務(wù)器才能應(yīng)用Where子句。這時候很多行已經(jīng)被鎖住，直到服務(wù)器過濾掉行之后才釋放。

set autocommit=0;
begin;
select actor_id from actor where actor_id<5 and actor_id <>1 for update

只返回3行記錄，但是實際上會鎖住1-4這四行。通過explain可以看出Extra有"UsingWhere"，說明MysSQL是InnoDB將行返回再應(yīng)用where條件過濾。

set autocommit=0;
begin;
select actor_id from actor where actor_id=1 for update

這個程序會掛起，直到第一個事務(wù)釋放了鎖。

如果說查詢根本沒用到索引，情況會更糟糕，Innodb會全表掃描并鎖住所有的行，這個在生產(chǎn)環(huán)境是很危險的。所以寫for update語句一定要看看有沒有用到索引。

一個很少人知道的細(xì)節(jié)：InnoDB在二級索引上使用讀鎖，在訪問主鍵索引需要排他鎖，這消除了使用覆蓋索引的可能性，并且使得select for update 比 lock in share mode 慢很多

總結(jié)

Mysql大多數(shù)情況下都會使用B-Tree索引，其他索引都只是適用于特殊目的。

選擇索引和編寫索引的查詢時，有以下三條原則:

  • 單行訪問是很慢的，如果訪問數(shù)據(jù)頁的某一塊只為獲得某一行，那就浪費了很多IO的工作。
  • 順序訪問數(shù)據(jù)很快，1不需要多次磁盤尋道，所以比隨機(jī)IO快，尤其機(jī)械硬盤2其次是無需額外的排序操作。
  • 索引覆蓋查詢非?？欤苊饬藛涡性L問，減少了IO的次數(shù)。

祝大家編碼愉快，工作愉快，歡迎關(guān)注我的公眾號，一起分享交流


Java技術(shù)小棧
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容