基礎(chǔ)知識

一張數(shù)據(jù)表中具有百萬級的數(shù)據(jù)時，如何精確且快速的拿出其中某一條或多條記錄成為了人們思考的問題。

InnoDB 存儲引擎的出現(xiàn)讓這個問題得到了很好的解決， InnoDB 存儲引擎是以索引來進(jìn)行數(shù)據(jù)的組織，而索引在 MySQL 中也被稱之為鍵，因此 UNIQUE KEY ， PRIMARY KEY 約束字段會作為索引字段。

當(dāng)沒有明確指出 PRIMAY KEY 時， InnoDB 存儲引擎會自動的創(chuàng)建一個6字節(jié)的隱藏主鍵用于組織數(shù)據(jù)，但是由于該主鍵是隱藏的所以對查詢沒有任何幫助。

索引相當(dāng)于一本大字典的目錄，有了目錄來找想要的內(nèi)容就快很多，否則就只能進(jìn)行一頁一頁的遍歷查詢

查找過程

索引的查找過程是依照 B+ 樹算法進(jìn)行查找的，而每一張數(shù)據(jù)表都會有一個且只能有一個與之對應(yīng)的樹

只有最下面一層節(jié)點(diǎn)中存儲一整行記錄

第二層及第一層中黃色部分為指針

如圖所示，如果要查找數(shù)據(jù)項(xiàng)29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時間因?yàn)榉浅６?相比磁盤的IO)可以忽略不計(jì)，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。真實(shí)的情況是，3層的 B+ 樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

索引分類

索引分為聚集索引與輔助索引

聚集索引

聚集索引是會直接按照 B+ 樹進(jìn)行查詢，由于 B+ 樹的底層葉子節(jié)點(diǎn)是一整行記錄，所以聚集索引能夠十分快速的拿到一整行記錄。

值得注意的是，一張數(shù)據(jù)表中只能有一個聚集索引。

輔助索引

輔助索引的樹最底層的葉子節(jié)點(diǎn)并不會存儲一整行記錄，而是只存儲單列索引的數(shù)據(jù)，并且還存儲了聚集索引的信息。

通過輔助索引進(jìn)行查詢時，先拿到自身索引字段的數(shù)據(jù)，再通過聚集索引拿到整行記錄，也就是說輔助索引拿一整行記錄而言需要最少兩次查詢。

而一張數(shù)據(jù)表中可以有多個輔助索引。

創(chuàng)建索引

索引類型

索引名類型INDEX(field)普通索引，只加速查找，無約束條件PRIMARY KEY(field)主鍵索引，加速查找，非空且唯一約束UNIQUE(field)唯一索引，加速查找，唯一約束INDEX(field1,field2)聯(lián)合普通索引PRIMARY KEY(field1,field2)聯(lián)合主鍵索引UNIQUE(field1,field2)聯(lián)合唯一索引FULLTEXT(field)全文索引SPATIAL(field)空間索引

舉個例子來說，比如你在為某商場做一個會員卡的系統(tǒng)。這個系統(tǒng)有一個會員表有下列字段：會員編號INT會員姓名VARCHAR(10)會員身份證號碼VARCHAR(18)會員電話VARCHAR(10)會員住址VARCHAR(50)會員備注信息TEXT那么這個 會員編號，作為主鍵，使用PRIMARY會員姓名 如果要建索引的話，那么就是普通的INDEX會員身份證號碼 如果要建索引的話，那么可以選擇UNIQUE(唯一的，不允許重復(fù))# 除此之外還有全文索引，即FULLTEXT會員備注信息如果需要建索引的話，可以選擇全文搜索。用于搜索很長一篇文章的時候，效果最好。用在比較短的文本，如果就一兩行字的，普通的INDEX也可以。但其實(shí)對于全文搜索，我們并不會使用MySQL自帶的該索引，而是會選擇第三方軟件如Sphinx，專門來做全文搜索。# 其他的如空間索引SPATIAL，了解即可，幾乎不用各個索引的應(yīng)用場景索引定義

語法介紹

索引應(yīng)當(dāng)再建立表時就進(jìn)行創(chuàng)建，如果表中已有大量數(shù)據(jù)，再進(jìn)行創(chuàng)建索引會花費(fèi)大量的時間。

-- 方法一：創(chuàng)建表時CREATETABLE表名 (? ? ? ? ? ? ? ? 字段名1數(shù)據(jù)類型 [完整性約束條件…],? ? ? ? ? ? ? ? 字段名2數(shù)據(jù)類型 [完整性約束條件…],? ? ? ? ? ? ? ? [UNIQUE| FULLTEXT | SPATIAL ]INDEX|KEY[索引名]? (字段名[(長度)]? [ASC|DESC])? ? ? ? ? ? ? ? );-- 方法二：CREATE在已存在的表上創(chuàng)建索引CREATE[UNIQUE| FULLTEXT | SPATIAL ]INDEX索引名ON表名 (字段名[(長度)]? [ASC|DESC]) ;-- 方法三：ALTER TABLE在已存在的表上創(chuàng)建索引ALTERTABLE表名ADD[UNIQUE| FULLTEXT | SPATIAL ]INDEX索引名 (字段名[(長度)]? [ASC|DESC]) ;-- 刪除索引：DROP INDEX 索引名 ON 表名字;

功能測試

--? 準(zhǔn)備表，注意此時表沒有設(shè)置任何類型的索引createtables1(? ? ? ? id int,? ? ? ? number varchar(20));-- 創(chuàng)建存儲過程，實(shí)現(xiàn)批量插入記錄delimiter $$-- 聲明存儲過程的結(jié)束符號為$$createprocedure auto_insert1()? ? ? ? BEGIN? ? ? ? ? ? ? ? declare i int default1;-- 聲明定義變量while(i <1000000)doinsertinto s1 values? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (i,concat('第', i,'條記錄'));? ? ? ? ? ? ? ? set i = i +1;endwhile;? ? ? ? END $$-- 存儲過程創(chuàng)建完畢delimiter ;-- 調(diào)用存儲過程，自動插入一百萬條數(shù)據(jù)call auto_insert1();

在無索引的情況下，查找 id 為 567891 的這條記錄，耗時 0.03s

mysql>select* from s1 where id =567891;+--------+--------------------+| id? ? | number? ? ? ? ? ? |+--------+--------------------+|567891| 第567891條記錄? ? |+--------+--------------------+1rowinset (0.33sec)

接下來為 id 字段建立主鍵索引后再進(jìn)行查找，耗時為 0.00s

mysql> ALTER TABLE s1 MODIFY idintPRIMARY KEY;Query OK,0rowsaffected(4.76sec)? -- 創(chuàng)建索引花費(fèi)寺廟Records: 0? Duplicates: 0? Warnings: 0mysql>select*froms1whereid=567891;+--------+--------------------+| id? ? | number? ? ? ? ? ? |+--------+--------------------+|567891| 第567891條記錄? ? |+--------+--------------------+1rowinset(0.00sec) 再次查找則快了很多

總結(jié)

以上是生活随笔為你收集整理的了解mysql文章_一篇文章带你深入了解MySQL 索引相关的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。