mysql 索引优化

2022-07-15 22:42:35

背景

     首先是因为一个慢sql带来的思考与建议。

     随着2017双11即将来临，大家也都进入了备战状态。稳定性保障是我们双11核心目标的第一大目标，是其他一切目标的前提。大家必须带着危机感和使命感，进入备战状态了。

    今年业务量预计是去年的好几倍，仓配+村淘订单预计1000万单，我们如何应对这次战役呢，首先我们需要提效能，今年的核心保障人数比去年更少，业务降级比去年也少，平均每笔订单的机器成本还需要下降33.3%，单机QPS需要大幅度提升，意味着我们的压力是往年双11的好几倍，我们能否经得起考验呢！

 

     最近共配多级网络项目刚刚上线，发现线上有很多因为慢sql问题导致的性能问题。DB是整个系统的数据核心也是稳定性最底层的支持，如果这一层出现问题，将会是很严重的。说到DB问题，一个是数据可靠性，另一个是性能，前者在阿里的技术体系上保障，后者最容易出现问题的恐怕就是索引了。

通过这次的教训，呼吁各owner在双11前对重要表索引进行Review。

今天就通过具体的案例来和大家一起探讨下如何建索引，优化一个慢sql。

 

慢sql优化

线上案例

履行系统多级网络项目刚刚上线，为了支持多级作业，将运单和作业单做了分离，将原先的ct_order，拆分出nc_tms_order作业单。刚上线很多业务场景还没有加索引，刚上线几天随着业务不断深入，终于在今天爆发了。因为一个慢sql，导致DB服务器load上升，DB链接池耗尽，应用接口超时，甚至期间业务无法正常使用。

其中一个sql：

SELECT COUNT(1)
FROM`nc_tms_order_0340``nc_tms_order`
WHERE`nc_tms_order`.`wh_id`= 108884
AND`nc_tms_order`.`order_type`= 1
AND`nc_tms_order`.`distribution_order_id`='12059380340'
AND`nc_tms_order`.`is_deleted`= 0

 

 

从IDB上看到，平均响应时间5s，扫描303531行，流量一旦上来，基本就打满整个线程池，db性能急剧下降。

这个时候一并收到ctn报警，因为ctn用户地址转换也依赖这个数据库实例，系统间的耦合产生的连锁反应立即出现，所以有必要将两个重要应用做隔离，后续再讨论。

 

紧接着开发同学想到的是加索引，下午17.10分通过加上索引后，DB的各项性能数据恢复。

具体故障排查过程单独分析，这里主要说说，索引应该如何加。以下是DB性能指标图反映了从链接数耗尽、cpu接近100%，到恢复的过程。经历了有惊很险的过程。

 

 

 

 

 

如下图，新增

唯一索引:idx_whid_distributionorderid(wh_id,distribution_order_id)

通过这个索引解决线上故障，接下来分析下优化过程。

 

至少我第一眼看上去觉得索引有点多乱。

 

 

 

 

wh_id,总条目数是30万+，wh_id唯一值条目为2，按照公式count(distinct col)/count(*)

区分度基本等于0。

 

sql解析的额外信息，当出现using index时，表示sql使用覆盖索引，性能较好，而当出现using filesort、using temporary、using where时，查询需要优化。

 

我这里先总结几条建索引的几个原则，后面有机会再和大家一起深入下Mysql索引知识。

 

建索引的几大原则

对于索引我总结三大原则：

   1、最左前缀原则；2、不冗余原则；3、最大选择性原则。

基本掌握这三条，对于索引的优化理论上是没有问题了。

最左前缀原则

一般在where条件中两个及以上字段时，我们会建联合索引。

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1,a2,a3...an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数。另外，单列索引可以看成联合索引元素数为1的特例

 

  • 最左前缀是一个很重要的原则。

mysql会从左至右匹配，直到遇到范围查找(> < like between)就停止。

如：

select * from table1 where a=1 and b=2 and c<3 and d=9 ;

建立的联合索引为：(a,b,c,d)  实际使用的索引为(a,b,c)。因为遇到了c<3就停止了，d列就没有用上。

前面讲过联合索引是有序元组，

则mysql实际建的索引为：(a) (a,b)  (a,b,c) (a,b,c,d)。

举个例子：where b=2 and c=3 and d=9 ；按照最左匹配原则，这个条件就没法走索引了，首先必须有a。

 

  • =,in可以乱序，查询优化器会帮你优化成索引可以识别的形式。也就是说，where b=2 and a=1 and c<3

使用的索引任然为(a,b,c)组合。

 

  • 回到线上案例：

索引:idx_whid_distributionorderid(wh_id,distribution_order_id)

索引组合 (wh_id) ,(wh_id,distribution_order_id)

相当于建了一个wh_id 的单列索引，也就是说当你要根据wh_id查询时，是不需要再新建索引了。

 

不冗余原则

  • 尽量扩展索引、不要新建索引

mysql目前主要索引有：FULLTEXT,HASH,BTREE

好的索引可以提高我们的查询效率，不好的索引不但不会起作用，反而给DB带来负担，基于BTREE结构，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时DB系统也要消耗资源去维护。

基于刚才的最左匹配原则，尽量在原有基础上扩展索引，不要新增索引。

 

  • 能用单索引，不用联合索引；能用窄索引，不用宽索引；能复用索引，不新建索引。

回到线上案例：

nc_tms_order、ct_order看看分别有哪些索引

 

 

看到这里我开始凌乱，好像什么字段都可以加索引。

为此专门针对ct_order表两个具有比较性的索引做了性能测试，ct_order_code，lc_order_code区分度都是非常高的字段，前者是好于后者（联合station_id并没有起到太多优化作用）。

idx_ct_order_code(ct_order_code),

idx_ct_order_lc_order_code(station_id,lc_order_code)

 

那么接下来我们说说那些字段适合建索引。

最大选择性原则

  • 选择区分度高列做索引

    什么是区分度高的字段呢？

一般两种情况不建议建索引：

 1、一两千条甚至几百条，没必要建索引，让查询做全表扫描就好了。

因为不是你建了就一定会走索引，执行计划会选择一个最优的方式，msql辅助索引的叶子节点并不直接存储实际数据，只是主建ID，再通过主键索引二次查找。这么一来全表可能很有可能效率更高。

 2、索引选择性较低的情况。

所谓选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值。


Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

 

  • 回到线上案例 wh_id 最好不用做索引字段，这个和性别男、女作为索引字段没区别：

SELECT count(DISTINCT(wh_id))/count(*) AS Selectivity
FROM nc_tms_order_0340 nc_tms_order;

 

0

 

选择性不足0.0001（精确值为0.00000666），按Selectivity值越大价值越大原则，实在没有什么必要为其单独建索引。

再看下distribution_order_id 单列索引。

SELECT count(DISTINCT(distribution_order_id))/count(*) AS Selectivity FROM nc_tms_order_0340 nc_tms_order;

0.0030

Selectivity = 0.0030 ，比之前有所优化，但其实不不是特别理想。

 

联合索引

SELECT count(DISTINCT(concat(wh_id,distribution_order_id)))/count(*) AS Selectivity FROM nc_tms_order_0340 nc_tms_order;

0.0030

Selectivity = 0.0030

 

从值来看，这里建联合索引的价值并不是特别大。一个distrubution_id 搞定。

 

那么我们在建一个索引或联合索引的时候拿不准的时候可以先计算下选择性值以及通过explain测试。

一般情况，status、is_deleted列不建议建索引。

 

  • 创建复合索引，需要注意把区分度最大的放到最前面。也就是值越大的放前面，当然需根据时间场景和sql通过执行计划进行优化。

 

  • 前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

其他

  • 索引列不能参与计算

    比如from_unixtime(create_time) = ’2017-11-11’就不能使用到索引，语句应该写成create_time = unix_timestamp(’2017-11-11’);

  • 主键最好使用自增型，保证数据连续性（mysql innodb 主键默认采用b+tree，索引和数据放在同一个btree中），不要使用uuid、hash、md5等

  •  

  • 不要使用前匹配的like查询，会导致索引失效。可以使用后匹配like，如"xxx%"。

  • 在字符串列上创建索引，尽量使用前缀索引。前缀基数根据具体业务，在匹配度和存储量（索引的存储量）之前做一个平衡。

  • 不要使用 not inlike，会导致索引失效。not in可以用not exists替换。in和or所在列最好有索引

 

说了这么多留action吧，大家回去看看ct_order,nc_tms_order看看如何优化吧。

 

其实数据库索引调优，光靠理论是不行的，需要结合实际情况。MySQL机制复杂，如查询优化策略和各种引擎的实现差异等都会使情况变复杂。我们在了解这些原则和基础之上，要不断的实践和总结，从而真正达到高效使用MySQL索引的目的。

 

执行计划explain命令

explain 是sql优化神奇。

公司IDB的执行计划被包装过了，除了被动查慢sql调优上看到，一直没找主动做执行计划的地方，有知道的可以告诉我下。

 

以下均为工具介绍，可留做备查。

explain用法

EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options

举例

  1. mysql> explain select * from event;  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
1 row in set (0.00 sec)

各个属性的含义

  • id：select查询的序列号

  • select_type：select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

  • table：输出的行所引用的表。

  • type：联合查询所使用的类型。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

就type进行详细的介绍：all : 即全表扫描

index : 按索引次序扫描，就type进行详细的介绍：

System,const,eq_ref,ref,range,index,all

all : 即全表扫描

 

index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。

range:以范围的形式扫描。

explain select * from a where a_id > 1\G

ref:非唯一索引访问(只有普通索引)

create table a(a_id int not null, key(a_id));

insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

mysql> explain select * from a where a_id=1\G

eq_ref:使用唯一索引查找(主键或唯一索引)

const:常量查询

当出现using index时，表示sql使用覆盖索引，性能较好，而当出现using filesort、using temporary、using where时，查询需要优化。

先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。

range:以范围的形式扫描。

explain select * from a where a_id > 1\G

ref:非唯一索引访问(只有普通索引)

create table a(a_id int not null, key(a_id));

insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

mysql> explain select * from a where a_id=1\G

eq_ref:使用唯一索引查找(主键或唯一索引)

const:常量查询

当出现using index时，表示sql使用覆盖索引，性能较好，而当出现using filesort、using temporary、using where时，查询需要优化。

possible_keys：指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

  • key：显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

  • key_len：显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

  • ref：显示哪个字段或常数与key一起被使用。

  • rows：这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

  • Extra：如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是where used，就是使用上了where限制。

如果是impossible where 表示用不着where，一般就是没查出来啥。

如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

 

 

 

 

 

MySQL索引原理

未完待续 。。。

可以参考：王小东《高性能MySQL》

索引价值

索引基本原理

索引数据结构

b+tree介绍

磁盘IO原理

 

 

索引优化实践

     sql优化实践：https://lark.alipay.com/nongcun/fc/glqvng

 

 

相关书籍介绍

[1] Baron Scbwartz等 著，王小东等 译；高性能MySQL（High Performance MySQL）；电子工业出版社，2010

[2] Michael Kofler 著，杨晓云等 译；MySQL5权威指南（The Definitive Guide to MySQL5）；人民邮电出版社，2006

[3] 姜承尧 著；MySQL技术内幕-InnoDB存储引擎；机械工业出版社，2011

[4] D Comer, Ubiquitous B-tree; ACM Computing Surveys (CSUR), 1979

[5] Codd, E. F. (1970). “A relational model of data for large shared data banks”. Communications of the ACM, , Vol. 13, No. 6, pp. 377-387

上一篇：finereport入门教程：如何在FineReport中添加 离线/在线地图


下一篇：高德离线地图TMS服务教程