ITPub博客

首页 > 云计算 > 开源云工具 > 基于滴滴云 MySQL 验证索引优化简单查询

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

原创 开源云工具 作者:java06051515 时间:2019-01-28 10:48:53 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31559758/viewspace-2564759/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-10-26

  • 博文量
    81
  • 访问量
    38821