MySQL通过索引优化-这里可能有你不知道的索引优化细节（二）

承接

MySQL通过索引优化-这里可能有你不知道的索引优化细节（一）

。

直接开始。

索引优化细节

1. union all,in,or都能够使用索引，但是推荐使用in

还是用

sakila

这个数据库的表

mysql> explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     | actor | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  ||  2 | UNION       | actor | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from actor where actor_id in (1,2);+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | actor | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |    2 |   100.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql>  explain select * from actor where actor_id = 1 or actor_id =2;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | actor | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |    2 |   100.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

用执行计划分别测试一下

union all

、

in

和

or

，发现

union all

分两步执行，而

in

和

or

只用了一步，效率高一点。

但是用执行计划看不出

in

和

or

的差别，我们换做

show profiles

来看一下（先

set profiling=1;

）：

mysql> show profiles;+----------+------------+-------------------------------------------------------+| Query_ID | Duration   | Query                                                 |+----------+------------+-------------------------------------------------------+|        1 | 0.00081575 | select * from actor where actor_id in (1,2)           ||        2 | 0.02360075 | select * from actor where actor_id = 1 or actor_id =2 |+----------+------------+-------------------------------------------------------+2 rows in set, 1 warning (0.00 sec)

可以看到，用

or

的执行时间比

in

时间长。

因为使用or条件查询，会先判断一个条件进行筛选，再判断or中另外的条件再筛选，而in查询直接一次在in的集合里筛选。

所以，union all,in,or都能够使用索引，但是推荐使用in

2. 范围列可以用到索引

范围条件是：
```
<
```
、
```
<=
```
、
```
>
```
、
```
>=
```
、
```
between
```
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

关于范围列使用索引以及索引生效规则，索引优化细节（一）有提到。

3. 强制类型转换会全表扫描

比如有这样一个表，

phone

列上建了索引，数据类型是varchar类型，存储的是手机号码：

create table user(id int,name varchar(10),phone varchar(11));alter table user add index idx_1(phone);

用执行计划

explain

看一下，条件分别用

where phone=13800001234

和

phone=\'13800001234\'

：

可以看到，前者会触发全表扫描（type为

ALL

），后者用到了索引进行查询。

所以，这个细节提醒我们，在查询的时候虽然MySQL会帮助我们做一些数据类型的强制转换，但是如果有索引的话，索引也不会生效，因此，就老老实实的用定义的数据类型来查询吧。

4. 更新十分频繁，数据区分度不高的字段上不宜建立索引

数据更新操作会变更B+树，所以更新频繁的字段建立索引会大大降低数据库的性能。

比如类似于

性别

这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据。

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

5. 创建索引的列，不允许为null，可能会得到不符合预期的结果

如果一个列上创建了索引，最好不要让它为null。但是具体情况具体分析，毕竟实际业务场景中很多字段是允许为null的。

6. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

阿里规约里有这么一条：

【强制】超过三个表禁止 join 。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，
保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

被关联字段没有索引的话会大大降低MySQL的性能。

MySQL的join使用的是嵌套循环算法

Nested-Loop Join Algorithm

一种简单的嵌套循环联接（NLJ）算法，一次从一个循环中的第一个表中读取行，并将每行传递到一个嵌套循环中，该循环处理联接中的下一个表。重复此过程的次数与要连接的表的次数相同。

假定要使用以下联接类型执行三个表t1，t2和t3之间的联接：

Table   Join Typet1      ranget2      reft3      ALL

那么，使用NLJ算法，join的执行过程像这样：

for each row in t1 matching range {for each row in t2 matching reference key {for each row in t3 {if row satisfies join conditions, send to client}}}

因为NLJ算法一次将行从外循环传递到内循环，所以它通常会多次读取在内循环中处理的表。

Block Nested-Loop Join Algorithm

块嵌套循环（BNL）嵌套算法使用对在外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。

例如，如果将10行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。

这将内部表必须读取的次数减少了一个数量级。

for each row in t1 matching range {for each row in t2 matching reference key {store used columns from t1, t2 in join bufferif buffer is full {for each row in t3 {for each t1, t2 combination in join buffer {if row satisfies join conditions, send to client}}empty join buffer}}}if buffer is not empty {for each row in t3 {for each t1, t2 combination in join buffer {if row satisfies join conditions, send to client}}}

如果S是连接缓冲区中每个存储的t1，t2组合的大小，而C是缓冲区中组合的数量，则扫描表t3的次数：

(S * C)/join_buffer_size + 1
join_buffer_size
可以看一下多大：
mysql> show variables like \'%join_buffer%\';+------------------+--------+| Variable_name    | Value  |+------------------+--------+| join_buffer_size | 262144 |+------------------+--------+
默认情况下，
join_buffer_size
的大小为256K

7. 能使用limit的时候尽量使用limit

不要认为

limit

就是拿来做分页的哦，

limit

的含义是

限制输出

，分页只是它的一种基本应用。

对于一个查询，如果明确知道要取前x行，不使用

limit

的话，MySQL将会一行一行的将全部结果按顺序查找，最后返回结果，借助于

limit

如果找到了指定行数，将直接返回查询结果，效率会有提升。

8. 单表索引建议控制在5个以内

并不是索引越多越好，索引也是要占空间的！

9. 组合索引的字段数不允许超过5个

10. 创建索引的时候应该避免以下错误概念

索引越多越好
过早优化，在不了解系统的情况下进行优化

索引监控

索引使用状态：

mysql> show status like \'Handler_read%\';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     || Handler_read_key      | 6     || Handler_read_last     | 0     || Handler_read_next     | 0     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 1117  |+-----------------------+-------+7 rows in set (0.05 sec)

各个

Variable

的含义：

Handler_read_first
读取索引第一个条目的次数
Handler_read_key
通过index获取数据的次数
Handler_read_last
读取索引最后一个条目的次数
Handler_read_next
通过索引读取下一条数据的次数
Handler_read_prev
通过索引读取上一条数据的次数
Handler_read_rnd
从固定位置读取数据的次数
Handler_read_rnd_next
从数据节点读取下一条数据的次数

通常我们只关注一下

Handler_read_key

和

Handler_read_rnd_next

就行了。如果它们的值比较大，说明用到索引的次数比较多，索引利用率高；反之如果都是0或者数值很小，这个时候就该慌了，说明索引没有起到作用，该检查SQL语句了！

看两个索引优化的案例

准备表

SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `itdragon_order_list`;CREATE TABLE `itdragon_order_list` (`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT \'主键id，默认自增长\',`transaction_id` varchar(150) DEFAULT NULL COMMENT \'交易号\',`gross` double DEFAULT NULL COMMENT \'毛收入(RMB)\',`net` double DEFAULT NULL COMMENT \'净收入(RMB)\',`stock_id` int(11) DEFAULT NULL COMMENT \'发货仓库\',`order_status` int(11) DEFAULT NULL COMMENT \'订单状态\',`descript` varchar(255) DEFAULT NULL COMMENT \'客服备注\',`finance_descript` varchar(255) DEFAULT NULL COMMENT \'财务备注\',`create_type` varchar(100) DEFAULT NULL COMMENT \'创建类型\',`order_level` int(11) DEFAULT NULL COMMENT \'订单级别\',`input_user` varchar(20) DEFAULT NULL COMMENT \'录入人\',`input_date` varchar(20) DEFAULT NULL COMMENT \'录入时间\',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;INSERT INTO itdragon_order_list VALUES (\'10000\', \'81X97310V32236260E\', \'6.6\', \'6.13\', \'1\', \'10\', \'ok\', \'ok\', \'auto\', \'1\', \'itdragon\', \'2017-08-28 17:01:49\');INSERT INTO itdragon_order_list VALUES (\'10001\', \'61525478BB371361Q\', \'18.88\', \'18.79\', \'1\', \'10\', \'ok\', \'ok\', \'auto\', \'1\', \'itdragon\', \'2017-08-18 17:01:50\');INSERT INTO itdragon_order_list VALUES (\'10002\', \'5RT64180WE555861V\', \'20.18\', \'20.17\', \'1\', \'10\', \'ok\', \'ok\', \'auto\', \'1\', \'itdragon\', \'2017-09-08 17:01:49\');

案例一：

select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

通过执行计划查看

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 |+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

发现

type=ALL

，需要进行全表扫描。

优化1：为

transaction_id

创建唯一索引：

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.00 | NULL  |+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

当创建索引之后，唯一索引对应的type是

const

，通过索引一次就可以找到结果，普通索引对应的type是

ref

，表示非唯一性索引扫描，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，

const

的性能要高于

ref

。

优化2：使用覆盖索引，查询的结果变成

select transaction_id

，而不是

select *

，当extra出现

using index

，表示使用了覆盖索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = \"81X97310V32236260E\"\\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: itdragon_order_listpartitions: NULLtype: constpossible_keys: idx_order_transaIDkey: idx_order_transaIDkey_len: 453ref: constrows: 1filtered: 100.00Extra: Using index

案例二：

创建组合索引

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\\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: itdragon_order_listpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3filtered: 100.00Extra: Using filesort

type: ALL

，

Extra: Using filesort

，说明创建索引之后跟没有创建索引一样，都是全表扫描，都是文件排序。

可以使用force index强制指定索引

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date\\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: itdragon_order_listpartitions: NULLtype: indexpossible_keys: NULLkey: idx_order_levelDatekey_len: 68ref: NULLrows: 3filtered: 100.00Extra: NULL

这样，type就到了

index

级别，效率略有提升了。

其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序

mysql> explain select * from itdragon_order_list where order_level=3 order by input_date\\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: itdragon_order_listpartitions: NULLtype: refpossible_keys: idx_order_levelDatekey: idx_order_levelDatekey_len: 5ref: constrows: 1filtered: 100.00Extra: Using index condition

这样搞，type能到

ref

级别，效果更好！