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

行百里er

发布于 2020-12-02 14:38:40

8540

发布于 2020-12-02 14:38:40

文章被收录于专栏：JavaJourneyJavaJourney

承接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 Type
t1      range
t2      ref
t3      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 buffer
    if 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: 1
  select_type: SIMPLE
        table: itdragon_order_list
   partitions: NULL
         type: const
possible_keys: idx_order_transaID
          key: idx_order_transaID
      key_len: 453
          ref: const
         rows: 1
     filtered: 100.00
        Extra: 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: 1
  select_type: SIMPLE
        table: itdragon_order_list
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: 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: 1
  select_type: SIMPLE
        table: itdragon_order_list
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_order_levelDate
      key_len: 68
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: 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: 1
  select_type: SIMPLE
        table: itdragon_order_list
   partitions: NULL
         type: ref
possible_keys: idx_order_levelDate
          key: idx_order_levelDate
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition

这样搞，type能到ref级别，效果更好！