文章主要介绍mysql性能下降的原因，索引的简介，索引常见的原则，explain命令的使用，以及explain输出字段的意义
我们先简单了解一下非关系型数据库和关系型数据库的区别
MongoDB是nosql中的一种。nosql的全称是not onle sql 非关系型数据库。
它的特点是性能高，扩张性强，模式灵活，在高并发场景表现非常优秀
但目前它还只是关系型数据库的补充，它在数据的一致性，数据的安全性，查询的复杂性问题上和关系型数据库还存在一定差距
mysql是关系型数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引，但其性能方面稍逊MongoDB，特别是百万级别以上的数据，很容易出现查询慢的现象。
查询慢的原因，一般情况下是程序员sql写的不好，或者是没有建索引，或者是索引失效等原因造成的。
我们来看两个场景：
1.订单导入，通过订单编号避免重复导单
具体业务逻辑是：订单导入时，为了避免重复导单，一般会通过订单号去数据库查询，判断该订单是否已经存在
select * from t_order where order_sn = "2001154648454";
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
| id | order_sn | stock_id | order_status | descript | create_type | order_level | input_date |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
| 1 | 2001154648454| 1 | 10 | ok | auto | 1 |2019-05-18 12:21:45 |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+
explain select * from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
上面这个查询自身没有任何问题，在测试环境中也没发现任何的问题。但如果一旦放到正式环境，放到一个有几百上千万的订单上，用全表扫描，那个酸爽，哈哈哈
通过explain命令我们可以清楚mysql是如何处理sql语句的：打印的内容分别表
-id：查询的序列号为1
-select_type:查询类型是简单查询，简单的select语句没有union和子查询
-table：表是t_order。
-partitions：没有分区。
-type：连接类型，all表示采用全表扫描方式。
-possible_keys：可能用到的索引为null。
-key：实际用到的索引是null。
-key_len：索引长度为null。
-ref：没有哪个列或者参数和key一起被使用。
-Extra：使用了where查询。
-rows：扫描出的行数
-filtered：执行情况的描述和说明
因为测试数据库中的数据量不大，所以rows和filtered提供的信息作用不大。
这里需要重点了解的是type为ALL,全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常慢
我们先来第一步优化下：为order_sn创建索引
create unique index idx_order_sn on t_order (order_sn);
explain select * from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| 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 |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
上面我们针对order_sn创建的索引是唯一索引，不是普通索引。
唯一索引打印的type值是const,表示通过索引一次就可以找到了。即找到值就结束扫描返回查询结果。
普通索引打印的type值如果是ref的话。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。
通过上面描述：显而易见，const的性能要远高于ref。并且根据业务逻辑来判断，创建唯一索引是比较合理的。
我们继续再优化：覆盖索引
explain select order_sn from t_order where order_sn = '2001154648454';
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| 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 改为了 order_sn 后 Extra 显示Using index，表示该查询使用了覆盖索引
这是一个非常好的消息，说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序) 和Using temporary(使用临时表)则表明sql需要立即优化了。
根据业务逻辑来，查询结果返回order_sn也可以满足业务逻辑要求。
2.订单管理页面，通过订单级别和订单录入时间排序
具体业务逻辑：优先处理订单级别高，录入时间长的订单。
说到排序，我们首先会想到的应该是order by,这里面还有一个可怕的Using filesort（使用内部排序）等着你。
explain select * from t_order 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的情况。其效果反而更差。
1.初步优化：为order_level,input_date创建复合索引
create index idx_order_leveldate on t_order (order_level,input_date);
explain select * from t_order 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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
创建复合索引后你会惊奇的发现，和没创建索引一样？？？？？？都是全表扫描，都用到了文件排序。那是索引失效？还是索引创建失败呢？
我们试着看看下面打印情况
explain select order_level,input_date from t_order 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 | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | Using index |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
上面我们将 * 换成了order_level,input_date后。type从all升级为index,表示全索引文件扫描，Extra也显示使用了索引。
但里面还是有问题，检索虽然快了，但返回的内容只有order_level和input_date两个字段，让业务同事怎么用？而且我们也不能把每个用到的字段都建一个复合索引吧？
不过这里好在mysql没有这么笨，可以使用force index 强制指定索引。在原来的sql语句上修改force index(idx_order_leveldate )就可以了。
explain select * from t_order force index(idx_order_leveldate) 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 | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | NULL |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
根据业务逻辑我们再次优化一下：就是订单级别真的有必要排序么
这里面order_level的值可能只有低、中、高、加急、超时这5种。对于这种重复且分布平均的字段，排序和加索引的作用不大。
那我们能否先固定order_level的值，然后再给input_date排序呢？
explain select * from t_order where order_level = 3 order by input_date;
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | itdragon_order_list | NULL | ref | idx_order_levelDate | idx_order_levelDate | 5 | const | 1 | 100 | Using index condition |
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
和之前的sql比起来，type从index升级为ref（非唯一性索引扫描）。索引的长度从68变成了5，说明之用了一个索引，ref也是一个常量。
Extra 为Using index condition 表示自动根据临界值，选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。
通过上面的案例我们需严谨一点：优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。
网友评论