MySQL语句优化

前言

在项目业务中，SQL优化是性能瓶颈最为关键的一环，本文主要记录了工作中常用的MySQL的语句优化点。

1. explain关键字

MySQL数据库中，通过使用explain关键字可以解析出SQL语句的执行计划，分析查询语句或是结构的性能瓶颈，其中主要包括以下几个参数

1. id

SELECT识别符，每个表的加载和读取顺序，原则是：id值越大越先被执行。id值相同的按从上到下的顺序执行。id为NULL的最后执行。

2. select_type

SELECT类型：simple，primary，union。

3. table

输出的行所用的表。

4. type 访问类型

表示MySQL是如何访问数据的，是全表扫描还是通过索引，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

• system：表里只有一行记录，这个属于const类型的特例，一行数据平时很少出现，可以忽略不计。
• const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。
• rq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
• ref:不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
• range：只检索给定范围的行，使用一个索引来选择行。
• index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
• all：全表扫描，意味MySQL需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

5.possible_keys

这一列显示查询可能使用哪些索引来查找。

6.key

这一列显示MySQL实际采用哪个索引来优化对该表的访问。

7.key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。

8.ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名。

9.rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

10.Extra

• Using filesort：MySQL对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。
• Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
• Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 如果同时出现Using where，表明索引被用来执行索引键值的查找 如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作。

2.什么情况下不会走索引

1. 使用了!=或者<>时
2. 数据类型不一致导致索引失效
3. 函数导致索引失效
4. 运算符导致索引失效
5. OR 导致索引失效
6. 模糊搜索导致索引失效
7. not in，not exists导致索引失效
8. 联合索引，没有使用第一列索引

3.表设计

要根据查询速度来设计，尽量做到建立索引后，查询匹配的结构集数据量少。

三大范式

第一范式：每一列都不可以再拆分，保持原子性。
第二范式：在第一范式的基础上，非主键列完全依赖于主键列，而不只依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键列，而不依赖于其他非主键列。

4.分库分表

数据库瓶颈

1. IO瓶颈
   第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。
   第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。
2. CPU瓶颈
   第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。
   第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

分表分库方式

水平分库

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
结果：每个库的结构都一样；每个库的数据都不一样，没有交集；所有库的并集是全量数据。
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：每个表的结构都一样；每个表的数据都不一样，没有交集；所有表的并集是全量数据。
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。推荐：一次SQL查询优化原理分析。
分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
结果：每个库的结构都不一样；每个库的数据也不一样，没有交集；所有库的并集是全量数据。
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

垂直分表

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
结果：每个表的结构都不一样；每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；所有表的并集是全量数据。
场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。

分库分表步骤

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）。

5.分库分表问题

1、非partition key的查询问题
端上除了partition key只有一个非partition key作为条件查询
映射法
基因法

端上除了partition key只有一个非partition key作为条件查询
映射法
冗余法

2、非partition key跨库跨表分页查询问题
问题原因：跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页

解决方案：

全局视野法：

（1）将order by time offset X limit Y，改写成order by time offset 0 limit X+Y
（2）服务层将改写后的SQL语句发往各个分库：即例子中的各取3页数据
（3）假设共分为N个库，服务层将得到N*(X+Y)条数据：即例子中的6页数据
（4）服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录，就是全局视野所需的一页数据

业务折衷法：

业务折衷一：禁止跳页查询

业务折衷二：允许数据精度损失