基礎知識
一張資料表中具有百萬級的資料時，如何精确且快速的拿出其中某一條或多條記錄成為了人們思考的問題。
InnoDB
存儲引擎的出現讓這個問題得到了很好的解決，
InnoDB
存儲引擎是以索引來進行資料的組織，而索引在
MySQL
中也被稱之為鍵，是以
UNIQUE KEY
，
PRIMARY KEY
限制字段會作為索引字段。
當沒有明确指出
PRIMAY KEY
時，
InnoDB
存儲引擎會自動的建立一個6位元組的隐藏主鍵用于組織資料，但是由于該主鍵是隐藏的是以對查詢沒有任何幫助。
索引相當于一本大字典的目錄，有了目錄來找想要的内容就快很多，否則就隻能進行一頁一頁的周遊查詢
查找過程
索引的查找過程是依照
B+
樹算法進行查找的，而每一張資料表都會有一個且隻能有一個與之對應的樹
隻有最下面一層節點中存儲一整行記錄
第二層及第一層中黃色部分為指針
如圖所示，如果要查找資料項29，那麼首先會把磁盤塊1由磁盤加載到記憶體，此時發生一次IO，在記憶體中用二分查找确定29在17和35之間，鎖定磁盤塊1的P2指針，記憶體時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤位址把磁盤塊3由磁盤加載到記憶體，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到記憶體，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO。真實的情況是，3層的
B+
樹可以表示上百萬的資料，如果上百萬的資料查找隻需要三次IO，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。
索引分類
索引分為聚集索引與輔助索引
聚集索引
聚集索引是會直接按照
B+
樹進行查詢，由于
B+
樹的底層葉子節點是一整行記錄，是以聚集索引能夠十分快速的拿到一整行記錄。
值得注意的是，一張資料表中隻能有一個聚集索引。
輔助索引
輔助索引的樹最底層的葉子節點并不會存儲一整行記錄，而是隻存儲單列索引的資料，并且還存儲了聚集索引的資訊。
通過輔助索引進行查詢時，先拿到自身索引字段的資料，再通過聚集索引拿到整行記錄，也就是說輔助索引拿一整行記錄而言需要最少兩次查詢。
而一張資料表中可以有多個輔助索引。
建立索引
索引類型
| 索引名 | 類型 |
|---|---|
| INDEX(field) | 普通索引，隻加速查找，無限制條件 |
| PRIMARY KEY(field) | 主鍵索引，加速查找，非空且唯一限制 |
| UNIQUE(field) | 唯一索引，加速查找，唯一限制 |
| INDEX(field1,field2) | 聯合普通索引 |
| PRIMARY KEY(field1,field2) | 聯合主鍵索引 |
| UNIQUE(field1,field2) | 聯合唯一索引 |
| FULLTEXT(field) | 全文索引 |
| SPATIAL(field) | 空間索引 |
舉個例子來說，比如你在為某商場做一個會員卡的系統。
這個系統有一個會員表
有下列字段：
會員編号 INT
會員姓名 VARCHAR(10)
會員身份證号碼 VARCHAR(18)
會員電話 VARCHAR(10)
會員住址 VARCHAR(50)
會員備注資訊 TEXT
那麼這個 會員編号，作為主鍵，使用 PRIMARY
會員姓名 如果要建索引的話，那麼就是普通的 INDEX
會員身份證号碼 如果要建索引的話，那麼可以選擇 UNIQUE （唯一的，不允許重複）
# 除此之外還有全文索引，即FULLTEXT
會員備注資訊如果需要建索引的話，可以選擇全文搜尋。
用于搜尋很長一篇文章的時候，效果最好。
用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。
但其實對于全文搜尋，我們并不會使用MySQL自帶的該索引，而是會選擇第三方軟體如Sphinx，專門來做全文搜尋。
# 其他的如空間索引SPATIAL，了解即可，幾乎不用
各個索引的應用場景 使用場景
文法介紹
索引應當再建立表時就進行建立，如果表中已有大量資料，再進行建立索引會花費大量的時間。
-- 方法一：建立表時
CREATE TABLE 表名 (
字段名1 資料類型 [完整性限制條件…],
字段名2 資料類型 [完整性限制條件…],
[UNIQUE | FULLTEXT | SPATIAL | PK] INDEX
[索引名] (字段名[(長度)] [ASC |DESC])
);
-- 方法二：CREATE在已存在的表上建立索引
CREATE [UNIQUE | FULLTEXT | SPATIAL | PK ] INDEX 索引名
ON 表名 (字段名[(長度)] [ASC |DESC]) ;
-- 方法三：ALTER TABLE在已存在的表上建立索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL | PK ] INDEX
索引名 (字段名[(長度)] [ASC |DESC]) ;
-- 删除索引：DROP INDEX 索引名 ON 表名字;
功能測試
-- 準備表，注意此時表沒有設定任何類型的索引
create table s1(
id int,
number varchar(20)
);
-- 建立存儲過程，實作批量插入記錄
delimiter $$ -- 聲明存儲過程的結束符号為$$
create procedure auto_insert1()
BEGIN
declare i int default 1; -- 聲明定義變量
while(i < 1000000) do
insert into s1 values
(i,concat('第', i, '條記錄'));
set i = i + 1;
end while;
END $$ -- 存儲過程建立完畢
delimiter ;
-- 調用存儲過程，自動插入一百萬條資料
call auto_insert1();
在無索引的情況下，查找
id
為
567891
的這條記錄，耗時
0.03s
mysql> select * from s1 where id = 567891;
+--------+--------------------+
| id | number |
+--------+--------------------+
| 567891 | 第567891條記錄 |
+--------+--------------------+
1 row in set (0.33 sec) 接下來為
id
字段建立主鍵索引後再進行查找，耗時為
0.00s
mysql> ALTER TABLE s1 MODIFY id int PRIMARY KEY;
Query OK, 0 rows affected (4.76 sec) -- 建立索引花費寺廟
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from s1 where id = 567891;
+--------+--------------------+
| id | number |
+--------+--------------------+
| 567891 | 第567891條記錄 |
+--------+--------------------+
1 row in set (0.00 sec) 再次查找則快了很多
索引名詞
索引結構
如果想更加深刻的了解索引，則需要更底層的認識索引的結構。
它其實是這個樣子的。
每一列建立一個索引，可以将它了解為單獨的建立了一個索引表，包含目前索引字段與值，不同的是聚集索引包含一整行資料表的真實記錄，而輔助索引隻包含目前的值與聚集索引的位址
回表查詢
使用輔助索引進行查詢時，如果
select
需要的字段沒有存在于索引中，則會根據聚集索引再查詢一次，這個過程稱之為回表查詢。
按上圖所示，以下查詢語句會執行回表查詢：
selcet age from tb1 where name = "name1";
# name是輔助索引列，并不存在age字段資訊，需要通過聚集索引列回表進行查詢
# 查兩次
如果使用聚集索引列進行查詢，就不會進行回表二次查詢：
select age from tb1 where id = 1;
# 聚集索引包含目前一整行的資訊，不用再回表進行查詢
# 查一次
覆寫索引
覆寫索引的意思是
select
需要的字段正是索引字段，不必再進行回表查詢，如下所示：
select name from tb1 where name = "name1";
如果
(name,age)
字段為聯合索引，則下面這種查詢也屬于覆寫索引：
select age from tb1 where name = "name1";
反之，如果查詢未在聯合索引中的資料，則不屬于覆寫索引：
select gender from tb1 where name = "name1";
# gender未在聯合索引中
有一個特點就是通過主鍵進行查詢時，都是覆寫索引，因為不用再進行回表查詢：
select name, age, gender from tb1 where id = 1;
索引合并
使用多個輔助索引（單列，非聯合）進行查詢時，被稱之為索引合并。
索引合并的查詢速度小于聯合索引，并且不會有最左字首比對的限制：
select id from tb1 where name = "name1" and age = 18;
最左字首比對特性
這個主要是在聯合索引中展現，如下所示，
(name,age)
字段為聯合索引，必須從左邊查詢才會走索引：
select gender from tb1 where name = "name1" and age = 18 ;
# 聯合索引是name在前，age在後
# name在前，age在後，走索引
如果像下面這樣使用，就不會走索引：
select gender from tb1 where age = 18 and name = "name1";
# 聯合索引是name在前，age在後
# 查詢是age在前，name在後，不走索引
短索引
如果一個字段中，字首或者字尾都相同的情況下，如：
字段：name(char)
記錄1：user01203023
記錄2：user92392023
記錄3：user92328823
記錄4：user02388322
将整條記錄完整的做索引顯然很浪費空間，隻從第四個字元開始向後做索引是最明智的選擇，這種索引被稱之為短索引。
建立或修改短索引，詳見建立索引中的文法。
使用索引
索引未命中
以下的查詢都會造成索引未命中的情況：
- 使用
like
select * from tb1 where name like "%yu"
- 使用函數進行查詢：
select * from tb1 where reverse(name) = "yunya"
-
or
or
select * from tb1 where id = 1 or email = "[email protected]" # email為建立索引 - 類型不一緻，如果
name
select * from tb1 where name = 1234; # 不走索引 select * from tb1 where name = "1234"; # 走索引 -
!=
select * from tb1 where name != "yunya"; # 不走索引 select * from tb1 where id != 1; # 走索引 -
>
select * from tb1 where name > "yunya"; # 不走索引 select * from tb1 where id > 10; # 走索引 select * from tb1 where age > 10; # 走索引 -
ordery by
select * from tb1 ordery by gender desc; # gender不是索引，不走索引 select * from tb1 ordery by id desc; # 主鍵，走索引 - 最左字首比對特性，如果
(name,age)
select * from tb1 where name = "yunya" and email = "[email protected]" # 走索引 select * from tb1 where name = "yunya"; # 走索引 select * from tb1 where age = "[email protected]"; # 不走索引
其他注意事項
- 避免使用
select *
- 使用
count(1)
count(列)
count(*)
- 建立表時盡量時
char
varchar
- 表的字段順序固定長度的字段優先
- 組合索引代替多個單列索引（經常使用多個條件查詢時）
- 盡量使用短索引
- 使用連接配接（
JOIN
- 連表時注意條件類型需一緻
- 索引散列值（重複少）不适合建索引，例：性别不适合
- 查詢一條資料時，使用limit 1來結尾，如select id,name,gender form tb1 where name = "yunya" limit 1; 否則會查詢整張表