MySQL的索引原理與查詢優化

一、MySQL 索引簡介

1、 MySQL索引的建立對于MySQL的高效運作是很重要的，索引可以大大提高MySQL的檢索速度。

打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。

2、索引分單列索引群組合索引。

• 單列索引，即一個索引隻包含單個列，一個表可以有多個單列索引，但這不是組合索引。
• 組合索引，即一個索引包含多個列。

3、索引的詳細分類：

1. 普通索引index :加速查找
2. 唯一索引
   • 主鍵索引：primary key ：加速查找+限制（不為空且唯一）
   • 唯一索引：unique：加速查找+限制 （唯一）
3. 聯合索引
   • primary key(id,name):聯合主鍵索引
   • unique(id,name):聯合唯一索引
   • index(id,name):聯合普通索引
4. 全文索引fulltext :用于搜尋很長一篇文章的時候，效果最好。
5. 空間索引spatial :了解就好，幾乎不用

4、建立索引時，你需要確定該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表的記錄。

5、索引的兩大類型hash與btree

1. 我們可以在建立上述索引的時候，為其指定索引類型，分兩類

• hash類型的索引：查詢單條快，範圍查詢慢
• btree類型的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb預設支援它）

2、不同的存儲引擎支援的索引類型也不一樣

• InnoDB 支援事務，支援行級别鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
• MyISAM 不支援事務，支援表級别鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
• Memory 不支援事務，支援表級别鎖定，支援 B-tree、Hash 等索引，不支援 Full-text 索引；
• NDB 支援事務，支援行級别鎖定，支援 Hash 索引，不支援 B-tree、Full-text 等索引；
• Archive 不支援事務，支援表級别鎖定，不支援 B-tree、Hash、Full-text 等索引；

6、濫用索引的缺點：

• 第一，建立索引和維護索引要耗費時間，這種時間随着資料量的增加而增加。
• 第二，索引需要占實體空間，除了資料表占資料空間之外，每一個索引還要占一定的實體空間，如果要建立聚簇索引，那麼需要的空間就會更大。
• 第三，當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，這樣就降低了資料的維護速度。

建立UNIQUE | FULLTEXT | SPATIAL 一樣的方法

方法一：建立表時建立索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
   [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY  [indexName] (username(length))   
);  
           

方法二：CREATE在已存在的表上建立索引

CREATE [UNIQUE | FULLTEXT | SPATIAL | INDEX ]  indexName ON mytable(username(length)); 
           

方式三：修改表結構(添加索引)

ALTER table tableName ADD  [UNIQUE | FULLTEXT | SPATIAL | INDEX ] indexName(columnName)
           

删除索引的文法

DROP INDEX [indexName] ON mytable; 
           

三、實測索引的功效

1. 前期準備工作

• 建立一個名為text的資料庫：

create database text charset utf8;
           

• 建立一張名為text的資料表

create table text(id int,name varchar(20))
           

• 通過建立存儲過程，實作批量插入記錄(大約需要半小時時間)

delimiter $$ #聲明存儲過程的結束符号為$$
create procedure insertinfo()
BEGIN
    declare i int default 1;
    while(i<1000000)do
        insert into text values(i,concat('wangjifei',i));
        set i=i+1;
    end while;
END $$ 
delimiter ; #重新聲明分号為結束符号為;
           

• 檢視存儲過程

show create procedure insertinfo\G 
           

• 調用存儲過程

call insertinfo();
           

2、在沒有索引的前提下測試查詢速度

mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.39 sec)
           

mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.53 sec)
           

3、加上索引

//1. 一定是為搜尋條件的字段建立索引，比如select * from t1 where age > 5;就需要為age加上索引
//2. 在表中已經有大量資料的情況下，建索引會很慢，且占用硬碟空間，插入删除更新都很慢，隻有查詢快
//比如create index myname on text(name);會掃描表中所有的資料，然後以name為資料項，
//建立索引結構，存放于硬碟的表中。建完以後，再查詢就會很快了

//給name加上普通索引
mysql> create index myname on text(name);
Query OK, 0 rows affected (18.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

//給id加上唯一索引
mysql> create unique index myid on text(id);
Query OK, 0 rows affected (10.83 sec)
Records: 0  Duplicates: 0  Warnings: 0
           

效果立竿見影,查詢速度翻了幾十倍

mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.00 sec)
           

mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.01 sec)
           

mysql> select * from text where name = 'wangjifei823458';
+--------+-----------------+
| id     | name            |
+--------+-----------------+
| 823458 | wangjifei823458 |
+--------+-----------------+
1 row in set (0.00 sec)
           

四、批量添加測試資料的方法：

• 通過存儲過程批量建立資料

1. 建立存儲過程
delimiter $$ #聲明存儲過程的結束符号為$$
create procedure insertinfo()
BEGIN
   declare i int default 1;
   while(i<1000000)do
       insert into text values(i,concat('wangjifei',i));
       set i=i+1;
   end while;
END $$ 
delimiter ; #重新聲明分号為結束符号為;

2. 檢視存儲過程
show create procedure insertinfo\G;

3. 調用存儲過程
call insertinfo();
           

五、正确使用索引

• 覆寫索引

  select * from text where name = 'wangjifei882345';

  該sql命中了索引，但未覆寫索引。利用name = 'wangjifei882345'到索引的資料結構中定位到該name在硬碟中的位置，或者說再資料表中的位置。

  但是我們select的字段為*，除了name以外還需要其他字段，這就意味着，我們通過索引結構取到name還不夠，還需要利用該name再去找到該name所在行的其他字段值，這是需要時間的，

  很明顯，如果我們隻select name，就減去了這份苦惱，如下select name from text where name = 'wangjifei882345';這條就是覆寫索引了，命中索引，且從索引的資料結構直接就取到了name在硬碟的位址，速度很快

mysql> select name from text where name = 'wangjifei882345';
+-----------------+
| name            |
+-----------------+
| wangjifei882345 |
+-----------------+
1 row in set (0.00 sec)
           

• 為了增加效果對比，在建立聯合索引前将之前建立的普通索引删除掉了

mysql> select * from text where id = 2435353252 and name = 'wangjifei123333';
Empty set (0.58 sec)  //普通查詢

mysql> create index idname on text(id,name); //建立聯合索引
Query OK, 0 rows affected (26.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from text where id = 2435353252 and name = 'wangjifei123333'; 
Empty set (0.00 sec)  // 聯合索引查詢