MySQL索引优化(自己收藏)

作者: 靠还是你 | 来源:发表于2019-09-25 15:21 被阅读0次

MySQL索引优化(自己收藏)
MySQL索引知多少
mysql 查询优化
MySQL(4)应用优化
Mysql 相关
MySQL，必须掌握的6个知识点
MySQL相关文章索引（2）
17.MySQL优化
mysql性能优化-慢查询分析、优化索引和配置
MySQL优化原理

InnoDB 索引的这种结构，产生了一些限制：

如果不是按照索引的最左列开始查找，则无法使用索引；
不能跳过联合索引中的某些列；
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找；

以上几点也基本上代表常听到的“最左前缀”，我们通过几个例子来解释一下这个问题，可能有的情况举的例子不太恰当，但希望能说明白想说出的问题。假设我们有一个 employees 表，表结构如下：

Column	Type	Usage	Index
id	bigint	Primary Key	primary_index
employee_id	varchar(10)	员工编号	employee_id_index
name	varchar(20)	姓名	name_age_gender_index
age	int	年龄	name_age_gender_index
gender	int	性别	name_age_gender_index

这个表中我们有 (name, age, gender) 这个联合索引，这个索引的结构大概如下图所示：

image

三星索引

在《高性能 MySQL》书中提到了一本书叫《Relational Database index design and the optimizers》，书中有一个概念是“三星索引”，它是这样定义的：

满足第一颗星：取出 WHERE 语句后的相关的列，将这些列作为索引最开始的列，这样可以利用索引来尽可能的过滤不必要的数据，减少数据处理的规模；
满足第二颗星：将 ORDER BY 列加入到索引中，不改变这些列的顺序，不考虑第一颗星已经出现的列，利用索引进行排序；
满足第三颗星：将查询语句中剩余的列加到索引中去，达到覆盖索引的效果。

但是三星索引往往是理想中的情况，现实状况下往往会同时有范围查询和排序的需求出现，这样就很难同时满足第一颗星和第二颗星，比如下列语句：

SELECT name, age FROM employees 
WHERE age BETWEEN 15 AND 30 
AND gender=1 ORDER BY name;

根据以上 SQL 建立索引，会出现两种情况：

第一种情况的索引为 (age, gender, name)：
满足第一颗星：将 age 和 gender 放入索引中，这样满足 WHERE 后有一个索引列和一个过滤列；
无法满足第二颗星：age 是范围查询，此时的 gender 并不是有序的；
满足第三颗星：将查询列 name 放入索引中；

第二种情况的索引为 (gender, name, age):
不满足第一颗星：只能匹配到 gender 索引列；
满足第二颗星：gender 相等的前提下，name 是有序排列的；
满足第三颗星：将查询列 age 放入索引中；

ORDER BY 索引优化技巧

初步优化：为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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

创建复合索引后你会惊奇的发现，和没创建索引一样？？？都是全表扫描，都用到了文件排序。是索引失效？还是索引创建失败？我们试着看看下面打印情况

mysql> explain select order_level,input_date 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       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |      100 | Using index |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

将select * from 换成了 select order_level,input_date from 后。type从all升级为index，表示（full index scan）全索引文件扫描，Extra也显示使用了覆盖索引。可是不对啊！！！！检索虽然快了，但返回的内容只有order_level和input_date 两个字段，让业务同事怎么用？难道把每个字段都建一个复合索引？
MySQL没有这么笨，可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

mysql> explain select * from itdragon_order_list 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的值可能只有，低，中，高，加急，这四种。对于这种重复且分布平均的字段，排序和加索引的作用不大。
我们能否先固定 order_level 的值，然后再给 input_date 排序？如果查询效果明显，是可以推荐业务同事使用该查询方式。

mysql> explain select * from itdragon_order_list 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。