# 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 秒，大幅提升了查询效率。

ITPub博客

基于滴滴云 MySQL 验证索引优化简单查询

最新文章