乐趣区

关于优化:技术分享-优化案例

jiezi

作者：杨奇龙
网名“北在北方”，目前任职于杭州有赞科技 DBA，次要负责数据库架构设计和运维平台开发工作，善于数据库性能调优、故障诊断。
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

前言

在数据库表构造变更公布之前，咱们会和开发沟通索引设计是否正当，发现局部开发同学对于索引设计还是有一些常识盲区。本文把常见的案例记录下来，做个剖析，抛砖引玉。

区分度和过滤性

先看一段和开发同学日常 sql review 的对话：

下面的对话其实在工作中比拟常见（同时也阐明咱们培训没有到位 T_T），这样的想法会导致开发疏忽选择性比拟低的字段，sql 的执行打算应用 using where 匹配更多的数据行，后果执行耗时比拟，性能就不现实。大部分开发会理解这样的《开发标准》：创立索引要抉择区分度高的字段 。他们会认为 区分度低的字段 不适宜创立索引或者不适宜增加到组合索引外面。然而这样的操作会导致很多慢查。举例来说：

select  * from  tab where a=1 and b=2;

场景 1 

合乎 a=1 的记录数有 10w 条记录，b=2 有 1000 条记录。如果只是创立 idx_a(a)，sql 申请通过索引 idx_a 拜访 10w 条件记录，而后还要逐个匹配 10w 条记录中的 status，找到合乎 b=2 的记录。这个动作会导致慢查。如果创立组合索引 idx_ab(a,b)，sql 申请通过索引 idx_ab 能够间接定位到 1000 条记录，无需额定的过滤。这样缩小拜访 9900 条记录的工夫，晋升查问速度。

场景 2 

合乎 a=1 的有 100 条记录，status=2 有 10 条记录。其实场景 2 因为数据量比拟少，间接拜访 100 条记录和定位到 10 条记录的工夫耗费相差不大，质变不足以引发量变，能够疏忽了。

Tips：

  1. 创立索引的目标是通过索引尽可能找到匹配 where 条件的行，缩小不必要的回表，进步查问效率；
  2. 须要辩证地对待区分度比拟低的字段在组合索引中的作用。在组合索引的状况下，咱们不能只是单纯地看字段的区分度，而是要看符合条件的记录数是多少。符合条件的记录越少，性能越好。

索引的有序性

在优化业务 sql 的过程中，常常发现开发将 order by 的字段增加到组合索引外面，然而仍然有 file sort 产生，导致慢查。这是为什么呢？索引自身是有序的 ，之所以产生 file sort 阐明组合索引中存在字段在索引中存储的程序和 order by 字段的程序不统一，不是严格正相干导致 MySQL 依据后果从新排序。order by 语句利用索引的有序性是有比拟高要求的， 组合索引中 order by 之前的字段必须是等值查问，不能是 in、between、<、> 等范畴查问，explain 的 type 是 range 的 sql 都会导致 order by 不能失常利用索引的有序性。入手实际一下，初始化一张表 x

create table x(id int not null auto_increment primary key, a int ,b int,key idx(a,b));
insert into x(a,b) values(1,8),(1,6),(1,3),(2,1),(2,2),(2,4),(3,7),(3,9);

索引中存储的 (a,b) 程序如下

mysql>  select * from x order  by a, b; 
+----+------+------+
| id | a    | b    |
+----+------+------+
|  3 |    1 |    3 |
|  2 |    1 |    6 |
|  1 |    1 |    8 |
|  4 |    2 |    1 |
|  5 |    2 |    2 |
|  6 |    2 |    4 |
|  7 |    3 |    7 |
|  8 |    3 |    9 |
+----+------+------+
8 rows in set (0.00 sec)

对于组合索引 (a,b) 在 where 条件中 a= 2 应用等值查问，explain 的 extra 字段中提醒 using index，并无额定的排序。

mysql> select * from x where a=2 order  by b;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  4 |    2 |    1 |
|  5 |    2 |    2 |
|  6 |    2 |    4 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> desc select * from x where a=2 order  by b \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
   partitions: NULL
         type: ref
possible_keys: idx
          key: idx
      key_len: 5
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

对于组合索引 (a,b) 在 where 条件中 a 应用范畴查问，执行打算中呈现 using filesort 排序。阐明 order by b 并未利用索引的有序性，进行了额定的排序。

mysql> select * from x where a>=1 and a<3 order  by b;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  4 |    2 |    1 |
|  5 |    2 |    2 |
|  3 |    1 |    3 |
|  6 |    2 |    4 |
|  2 |    1 |    6 |
|  1 |    1 |    8 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> desc select * from x where a>=1 and a<3 order  by b \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: x
   partitions: NULL
         type: range
possible_keys: idx
          key: idx
      key_len: 5
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.01 sec)

数据 a b 在索引中的程序和理论拜访的后果程序不统一，须要额定的排序就产生了 file sort。(a,b) 字段在索引中存储的程序如下图，显著和下面的查问条件的后果程序不统一，就导致 sql 执行打算呈现额定的排序，数据量比拟大的状况（比方 5000 以上）下就呈现慢查。

小结

本文总结两种呈现频率比拟高的场景。心愿开发同学看完本文之后，能设计出更正当的索引。

退出移动版