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 并启动:
[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
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 表:
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 默认的聚集索引。
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 查询一条记录:
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 字段作为辅助索引,看是否能够提高查询效率:
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 秒,大幅提升了查询效率。