1. MySQL 的索引
索引用于快速查找具有某一特定列值的行，如果不使用索引，MySQL 必须从表中的第一行开始读取整个表来找到相关的行，表越大，开销越大。如果表中有待查询列的索引，MySQL 可以在数据文件中快速定位所要查找的内容，而不用查询所有数据，速度大大优于顺序读取每一行数据。
本文将重点介绍基于滴滴云 MySQL 验证默认引擎 InnoDB 下索引对于简单查询的优化效果。
2. B+ 树与 InnoDB 引擎的索引
InnoDB 存储引擎支持 B+ 树索引、哈希索引、全文索引和空间索引，本文主要介绍 B+ 树索引。
B+ 树是由二叉树演变而来，关于二叉树和二分法在这里不再赘述，我们直接来看 B+ 树的结构。
在上图中，最下层被称为叶子节点，顺序保存了完整的数据记录，各叶子节点通过双向链表连接，上层节点储存着子节点的 key。B+ 树的高度通常为 2~3 层，因此一次查询一般只需要 2~3 次搜索，而由于叶子节点间的连接是通过双向链表，因此顺序遍历所有数据的效率大大提高。
InnoDB 的索引分为聚集索引（Clustered Index）和辅助索引（Secondary Indexes）。通常 InnoDB 表中的主键（primary key）即为聚集索引，而除了聚集所以之外所创建的其他索引均称作辅助索引。辅助索引所包含记录中必然会包含主键所在列，以这种方式将辅助索引与聚集索引关联起来。
接下来我们使用滴滴云的云主机与 MySQL 来验证索引对于搜索效率的提升。
3. 创建 MySQL 数据库
在滴滴云官网控制台创建 MySQL（主要步骤）：
本文选择的数据库规格为 2G 内存，20G 存储容量：
创建完成后可以看到数据库的链接信息：
后续详细使用步骤可点击以下链接参考滴滴云官网教程：https://help.didiyun.com/hc/kb/article/1143410/
通过滴滴云虚拟机 DC2 连接 MySQL，DC2 的创建可以参考以下连接：https://help.didiyun.com/hc/kb/article/1145869
本文操作系统以 Centos7.3 为例。
进入 DC2，安装 MariaDB 并启动：
|
1 2 3 4 |
[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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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 表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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万条数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 ; |
调用存储过程：
|
1 2 |
mysql> call myproc(); |
由于数据量较大，时间较长，完成后查看数据：
|
1 2 3 4 5 6 7 8 |
mysql> select count(*) from test_user; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (4.84 sec) |
出现以上结果，证明 1000 万条数据已插入成功。
4. 索引查询
接下来使用 primary key 也就是 ID 来查询符合条件的某一条记录，前面我们提到过表中的 primary key 就是 InnoDB 默认的聚集索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> explain select * from test_user where id=555555; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_user | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> select * from test_user where id=555555; +--------+-----------------+---------------+----------------------------------+--------+ | id | username | email | password | status | +--------+-----------------+---------------+----------------------------------+--------+ | 555555 | username_555555 | 555555@qq.com | 5b1b68a9abf4d2cd155c81a9225fd158 | 0 | +--------+-----------------+---------------+----------------------------------+--------+ 1 row in set (0.02 sec) |
我们可以看到 rows 字段显示 1，说明只读取了 1 行记录，而真正获取结果只需 0.02 秒。
接下来我们尝试用一个非索引字段 username 查询一条记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> explain select * from test_user where username='username_666666'; +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 9722473 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from test_user where username='username_666666'; +--------+-----------------+---------------+----------------------------------+--------+ | id | username | email | password | status | +--------+-----------------+---------------+----------------------------------+--------+ | 666666 | username_666666 | 666666@qq.com | f379eaf3c831b04de153469d1bec345e | 0 | +--------+-----------------+---------------+----------------------------------+--------+ 1 row in set (5.50 sec) |
我们可以看到执行这条数据需要检查 9722473 行，而得到结果需要 5.5 秒。
现在我们将 username 字段作为辅助索引，看是否能够提高查询效率：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> create index index_usernmae on test_user(username); Query OK, 0 rows affected (40.96 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test_user where username='username_7777777'; +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_user | NULL | ref | index_usernmae | index_usernmae | 153 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> select * from test_user where username='username_7777777'; +---------+------------------+----------------+----------------------------------+--------+ | id | username | email | password | status | +---------+------------------+----------------+----------------------------------+--------+ | 7777777 | username_7777777 | 7777777@qq.com | dc0fa7df3d07904a09288bd2d2bb5f40 | 0 | +---------+------------------+----------------+----------------------------------+--------+ 1 row in set (0.00 sec) |
我们可以看到在创建了 username 索引后，以 username 作为查询条件查询值为 7777777 的用户记录只需读取一条记录，返回结果只需不足 0.001 秒，大幅提升了查询效率。
本文作者：贺子一
