# 1. MySQL 的索引 #

索引用於快速查詢具有某一特定列值的行，如果不使用索引，MySQL 必須從表中的第一行開始讀取整個表來找到相關的行，表越大，開銷越大。如果表中有待查詢列的索引，MySQL 可以在資料檔案中快速定位所要查詢的內容，而不用查詢所有資料，速度大大優於順序讀取每一行資料。

本文將重點介紹基於[滴滴雲](https://blog.didiyun.com) MySQL 驗證預設引擎 InnoDB 下索引對於簡單查詢的最佳化效果。

# 2. B+ 樹與 InnoDB 引擎的索引#

InnoDB 儲存引擎支援 B+ 樹索引、雜湊索引、全文索引和空間索引，本文主要介紹 B+ 樹索引。

B+ 樹是由二叉樹演變而來，關於二叉樹和二分法在這裡不再贅述，我們直接來看 B+ 樹的結構。

![](https://i.imgur.com/oBQlos2.png)

在上圖中，最下層被稱為葉子節點，順序儲存了完整的資料記錄，各葉子節點透過雙向連結串列連線，上層節點儲存著子節點的 key。B+ 樹的高度通常為 2~3 層，因此一次查詢一般只需要 2~3 次搜尋，而由於葉子節點間的連線是透過雙向連結串列，因此順序遍歷所有資料的效率大大提高。

InnoDB 的索引分為聚集索引（Clustered Index）和輔助索引（Secondary Indexes）。通常 InnoDB 表中的主鍵（primary key）即為聚集索引，而除了聚集所以之外所建立的其他索引均稱作輔助索引。輔助索引所包含記錄中必然會包含主鍵所在列，以這種方式將輔助索引與聚集索引關聯起來。

接下來我們使用滴滴雲的雲主機與 MySQL 來驗證索引對於搜尋效率的提升。

# 3. 建立 MySQL 資料庫#

在滴滴雲官網控制檯建立 MySQL（主要步驟）：

![](https://i.imgur.com/HOcOrZv.png)

本文選擇的資料庫規格為 2G 記憶體，20G 儲存容量：

![](https://i.imgur.com/fLkL95q.png)

建立完成後可以看到資料庫的連結資訊：

![](https://i.imgur.com/UxKWPQN.png)

後續詳細使用步驟可點選以下連結參考滴滴雲官網教程：[https://help.didiyun.com/hc/kb/article/1143410/](https://help.didiyun.com/hc/kb/article/1143410/ "https://help.didiyun.com/hc/kb/article/1143410/")

透過滴滴雲虛擬機器 DC2 連線 MySQL，DC2 的建立可以參考以下連線：[https://help.didiyun.com/hc/kb/article/1145869](https://help.didiyun.com/hc/kb/article/1145869 "https://help.didiyun.com/hc/kb/article/1145869")

本文作業系統以 Centos7.3 為例。

進入 DC2，安裝 MariaDB 並啟動：
```
[dc2-user@10-254-17-175 ~]$ sudo su
[dc2-user@10-254-17-175 ~]$ yum install mariadb-server mariadb
[dc2-user@10-254-17-175 ~]$ systemctl start mariadb    
```

連線 MySQL：
```
[root@10-254-17-175 dc2-user]# mysql -h 10.254.148.233 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11870
Server version: 5.7.21-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

```

建立 test 資料庫並建立 test_user 表：
```
mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS `test_user`; 
tus`  tinyint(1) NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test_user` (  
    ->     `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,  
    ->     `username` varchar(50) DEFAULT NULL,  
    ->     `email` varchar(30) DEFAULT NULL,  
    ->     `password` varchar(32) DEFAULT NULL,
    ->     `status`  tinyint(1) NULL DEFAULT 0
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
```

建立一個儲存過程，插入1000萬條資料：
```
mysql> delimiter //
mysql> create procedure myproc()
    -> begin   
    ->     declare num int;   
    ->     set num=1;   
    ->     while num <= 10000000 do   
    ->         insert into test_user(username,email,password) values(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));   
    ->         set num=num+1;  
    ->     end while;  
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
```

呼叫儲存過程：
```
mysql> call myproc();
```

由於資料量較大，時間較長，完成後檢視資料：
```
mysql> select count(*) from test_user;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.84 sec)
```

出現以上結果，證明 1000 萬條資料已插入成功。

# 4. 索引查詢#
接下來使用 primary key 也就是 ID 來查詢符合條件的某一條記錄，前面我們提到過表中的 primary key 就是 InnoDB 預設的聚集索引。

```

我們可以看到 rows 欄位顯示 1，說明只讀取了 1 行記錄，而真正獲取結果只需 0.02 秒。

我們可以看到執行這條資料需要檢查 9722473 行，而得到結果需要 5.5 秒。

```

我們可以看到在建立了 username 索引後，以 username 作為查詢條件查詢值為 7777777 的使用者記錄只需讀取一條記錄，返回結果只需不足 0.001 秒，大幅提升了查詢效率。

基於滴滴雲 MySQL 驗證索引最佳化簡單查詢

相關文章