浅谈MySQL索引优化分析

发布时间：2020-05-25 09:42:18 所属栏目：MsSql 来源：互联网

导读：为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从

为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。还在等啥子？撸起袖子就是干！

案例分析

我们先简单了解一下非关系型数据库和关系型数据库的区别。

MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL，非关系型数据库。它的特点是性能高，扩张性强，模式灵活，在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充，它在数据的一致性，数据的安全性，查询的复杂性问题上和关系型数据库还存在一定差距。

MySQL是关系性数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引。但性能方面稍逊与MongoDB，特别是百万级别以上的数据，很容易出现查询慢的现象。这时候需要分析查询慢的原因，一般情况下是程序员sql写的烂，或者是没有键索引，或者是索引失效等原因导致的。

公司ERP系统数据库主要是MongoDB（最接近关系型数据的NoSQL），其次是Redis，MySQL只占很少的部分。现在又重新使用MySQL，归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上，对MySQL的性能分析也就显得格外重要。

我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。

说明：需要用到的sql已经放在了github上了，喜欢的同学可以点一下star，哈哈。

https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/

场景一：订单导入，通过交易号避免重复导单

业务逻辑：订单导入时，为了避免重复导单，一般会通过交易号去数据库中查询，判断该订单是否已经存在。

最基础的sql语句

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| id  | transaction_id   | gross | net | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date     |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| 10000 | 81X97310V32236260E |  6.6 | 6.13 |    1 |      10 | ok    | ok        | auto    |      1 | itdragon  | 2017-08-18 17:01:49 |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |  33.33 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

查询的本身没有任何问题，在线下的测试环境也没有任何问题。可是，功能一旦上线，查询慢的问题就迎面而来。几百上千万的订单，用全表扫描？啊？哼!

怎么知道该sql是全表扫描呢？通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示：

id : 查询序列号为1。
select_type : 查询类型是简单查询，简单的select语句没有union和子查询。
table : 表是 itdragon_order_list。
partitions : 没有分区。
type : 连接类型，all表示采用全表扫描的方式。
possible_keys : 可能用到索引为null。
key : 实际用到索引是null。
key_len : 索引长度当然也是null。
ref : 没有哪个列或者参数和key一起被使用。
Extra : 使用了where查询。

因为数据库中只有三条数据，所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。

初步优化：为transaction_id创建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | NULL |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

这里创建的索引是唯一索引，而非普通索引。

唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。

普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。(这里没有贴出代码)
显而易见，const的性能要远高于ref。并且根据业务逻辑来判断，创建唯一索引是合情合理的。

再次优化：覆盖索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra    |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | Using index |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

这里将select * from 改为了 select transaction_id from 后 Extra 显示 Using index，表示该查询使用了覆盖索引，这是一个非常好的消息，说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。

根据业务逻辑来的，查询结构返回transaction_id 是可以满足业务逻辑要求的。

场景二，订单管理页面，通过订单级别和订单录入时间排序

业务逻辑：优先处理订单级别高，录入时间长的订单。
既然是排序，首先想到的应该是order by，还有一个可怕的 Using filesort 等着你。

最基础的sql语句

mysql> explain select * from itdragon_order_list order by order_level,input_date;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

首先，采用全表扫描就不合理，还使用了文件排序Using filesort，更加拖慢了性能。

MySQL在4.1版本之前文件排序是采用双路排序的算法，由于两次扫描磁盘，I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间，但如果数据量太大，buffer的空间不足，会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置，还不如自己乖乖地建索引。

初步优化：为order_level,input_date 创建复合索引

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
mysql> explain select * from itdragon_order_list order by order_level,input_date;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!