mysql联表查询优化

对于B端系统，因为其业务复杂性而导致的SQL复杂性往往不太引起重视，觉得并发少，也就是个别功能慢影响用户体验而已，但是慢SQL不进行及时治理，会引发量变到质变，春节前，就有两个兄弟团队的系统，因为慢SQL太多导致MySQL服务器出现OOM而重新。近期团队对所负责系统进行专项慢SQL治理，对SQL优化原则又重新梳理一遍并附以执行计划结果的案例分析，最大的体会：对市面上各种用于面试的SQL优化规则，不用死记硬背，了解MySQL优化器和掌握执行计划才是以不变应万变之策。

查询优化器

查询优化器是mysql的核心模块之一。我们执行的查询语句会先被查询优化器处理一次，这种策略类似于java代码执行前会先经过JIT的重排序。查询优化器执行的结果把我们输入的语句变成了一条或者多条的执行计划，最终sql会根据这些执行计划执行。打个比方，一条语句有2个查询条件，查询优化器会通过底层的一些方法计算出2个查询条件的成本，然后根据成本分成2条先后的执行计划。

执行计划

分析慢查询的sql的时候就需要先查看这些执行计划：

相关命令：explain [sql]

执行计划信息中比较重要的字段：

id：数值越大的计划越先执行，数值相同则前面的先执行select_type: primary主键查询，union联表查询等type：查询类型，比如const代表常量查询，all全量查询等key：索引类型rows：越小代表磁盘操作会越少

其中,select_type的语义：

Column

JSONName

Meaning

SIMPLE

None

简单查询（不使用UNION或子查询）

PRIMARY

None

外层查询，主查询

UNION

None

UNION中第二个语句或后面的语句

DEPENDENT UNION

dependent（true）

UNION中第二个语句或后面的语句，独立于外部查询

UNION RESULT

union_result

UNION的结果

SUBQUERY

None

子查询中第一个ｓｅｌｅｃｔ

DEPENDENT SUBQUERY

dependent(true)

子查询中第一个ｓｅｌｅｃｔ,独立于外部查询

DERIVED

None

子查询在FROM子句中

MATERIALIZED

materialized_from_subquery

物化子查询

UNCAHEABLE SUBQUERY

cacheable(false)

结果集不能被缓存的子查询

UNCACHEABLE UNION

cacheable(false)

UNION中第二个语句或后面的语句不属于可缓存的子查询

type语义(按性能排序依次降低):

null：不访问任何一个表。explain ｓｅｌｅｃｔ 1 from dual;system: 根据主键查询系统表并且这个表只有一条记录（特殊的const场景）const：通常业务中速度最快的查询，主键或者唯一索引的const查询表中只有一条数据满足需求。explain ｓｅｌｅｃｔ * from user where uid=1;eq_ref: 使用主键或者唯一键和之前查询的结果集匹配。explain ｓｅｌｅｃｔ * from table_a a, table_b b where b.id = a.id;这里会出现2条执行计划，具体先查a还是先查b不一定，执行计划有自己的优化算法。ref：使用非聚集索引的常量查询。explain ｓｅｌｅｃｔ * from table_a a where email= ‘abc@aaa.com‘;fulltext: 全文索引查询，实际使用较少。fulltext索引的index存在于innoDB中，innoDB版本5.6以后支持。全文索引必须使用指定的语法。explain ｓｅｌｅｃｔ * from table where match(‘colum‘) against(‘abc‘);ref_or_null: 相当于在ref的基础上再加一条null匹配。explain ｓｅｌｅｃｔ * from table_a a where email= ‘abc@aaa.com‘ or a.emailis null;index_merge: 分别查2个索引的结果，再合并。explain ｓｅｌｅｃｔ * from table_a where email= ‘abc@aaa.com‘ or id = 1;unique_subquery: in子查询的结果被聚簇索引或者唯一索引覆盖。只针对 in 子查询并且子查询使用聚集索引或者唯一索引的情况。通常这种查询会被sql引擎优化，不容易出现。因为子查询的结果是聚簇索引，所以也不会出现回表。explain ｓｅｌｅｃｔ * from table_a where a.id not in (ｓｅｌｅｃｔ id from table_a where email like ‘%@aaa.com%‘ );index_subquery: 与上述索引覆盖类似，只不过这里使用的是二级索引，也不会出现回表。下面email加了二级索引explain ｓｅｌｅｃｔ * from table_a where email not in (ｓｅｌｅｃｔ email from table_a where email like ‘%@aaa.com%‘ );range: 范围查询[ =, >, =, 5;index: 纯索引查询，直接从索引中取出结果，这个也是不会发生回表。explain ｓｅｌｅｃｔ id from table_a;all：全表扫描，这是效率最低的查询方式。explain ｓｅｌｅｃｔ pay from table_a;

我们写sql的时候首先要避免的就是全表扫描（比较常见的是未使用索引，然后尽量使用索引，减少回表操作。至于复杂的查询效率问题，就得借助慢日志+执行计划来分析了，同样的语句，不同的结果集，不同的数据库版本都会影响到执行计划结果。通过查看执行计划，我们就可以分析出导致查询慢的因素到底在哪里。再根据实际情况去调整sql或者查询的方式。

典型案例（mysql5.7）

有些例子并不是绝对的，具体还得依赖优化器分析的结果，系统上线前最好每条sql都手动检查一遍比较稳妥。甚至有可能运行一段时间后，同一条语句的执行计划会不一样。

like会不会查索引?

1) explain selext * from table_a where email like ’aaa@bbb.com‘；2) explain selext * from table_a where email like ’aaa@bbb.com%‘；3) explain selext * from table_a where email like ’%aaa@bbb.com‘；4) explain selext * from table_a where email like ’%aaa@bbb.com%‘；

查看执行计划发现1）和2）的type是range，走了索引，3）和4）是all没走索引。看来模糊匹配会直接全表扫描。

null查询会不会走索引?

这里的意思是加了二级索引的列中有值为null的情况，这其实比较依赖mysql数据库的优化器，不是绝对的。1）explain selext * from table_a where email is null；会走索引2）explain selext * from table_a where email = ’aaa@bbb.com‘；会走索引3）explain selext * from table_a where email is not null；基数太大，全表扫描4）explain selext * from table_a where id is null；不会执行查询，优化器发现id不可能为null，此查询条件无意义。

函数会不会走索引?

也不一定，还得具体来看执行计划，比如count(*)会走索引，但是type是index，全索引扫描，性能也不高，如果where条件加了范围，则变成了range。

类型不一致会不会走索引?

某些场景优化器是可以自动调整的。但是开发的时候还是要尽量遵守类型匹配。

ｓｅｌｅｃｔ * from table_a where create_time >= ‘1591025358‘; 会走索引,优化器会自动优化。

单列、多列索引，where条件怎么写?

联合索引的定义为 key(colum_a, colum_b, colum_c); 满足最左前缀的情况下是会走索引的（range），不然就会全表扫描（all）。这个现象有点跟like相似，前面的字母或者值都不确定，后面的也没法索引。当然这里多列索引的前面指的是定义的第一列，where不一定非要第一个写colum_a，有就行。如果一个where中有2个单列索引，次序不重要，优化器会把2个索引各自查一遍，再合并（union操作），执行计划会看到index_merge。

比较union和or

这个也没有定论，生产环境必须要实际explain看一下。执行计划看似一样，就得看执行计划source里的成本（cost）。通常union的成本要比or低一些，但是写法麻烦一点。

exists和in的比较

exiests 的原理是先查一条主查询的记录，然后拿着这条记录去做子查询，然后这样把主查询表循环一遍。in 的原理是先子查询和主查询各自都查出来，一起放到innoDB内存中去比较。如果子查询记录较多，建议使用exists，尽量减少内存占用，反之可以使用in。如果in的条件都是明确的常量，则经过优化器优化，效率会比exists高很多。

非等于!=、会不会走索引

这也得分情况：1）条件是主键肯定会走索引explain ｓｅｌｅｃｔ * from table_a where id != 999;2）count()会走索引explain ｓｅｌｅｃｔ count() from table_a where name != ‘abc‘;3）如果的查询条件如果基数很大，则会走全表扫描。explain ｓｅｌｅｃｔ * from table_a where name != ‘abc‘;

关于基数是数据库引擎自动对表数据的同步信息，优化器优化的时候会参考这个基数计算成本。例如性别这个字段只有2种，基数就很小。昵称这个字段每条记录都不一样基数自然就很大。

关于索引覆盖

从优化的角度来看，尽量不要用“ｓｅｌｅｃｔ *” ，业务需要什么字段，就查什么字段，同样查询条件下索引覆盖的查询效率要远远高于“ｓｅｌｅｃｔ *”。

使用关联查询还是子查询

通常绝大多数情况下建议使用关联查询。

1）mysql优化器对关联查询的优化效果要比子查询容易。

2）有些情况下非常复杂的查询逼迫我们写出了子查询，有些查询条件本身没有加索引，就会导致全表查询，优化器可能也没法对当前的写法做优化。

3）如果业务允许，我们也可考虑把复杂查询拆开，先存到中间表，间隔一天同步一次，然后在复杂报表查询的时候就会快很多。

表关联查询的时候，小表，大表的关联顺序？

优化器会帮我们自动优化，不需要纠结。

分页相关问题

1）count(colum)、count(1)、count()建议永远使用count()，官方推荐的查询数目语法。count(colum) 有个问题是不会统计null字段，某些特殊场景可以利用。count(1) 不关心查询内容，查一次就加1。

2）尽量不写ｓｅｌｅｃｔ *

3）如果能保证id是连续不会中断，使用between的效率会很高。自增id在实际场景中会因为事务回滚的问题中断，想保证连续还是很难的。

4）id不连续分页查询的一种解决方案，简单说就是利用索引覆盖获取起点,效率能提高一些。ｓｅｌｅｃｔ id, colum_a from table_a where id >= (ｓｅｌｅｃｔ id from table_a order by id limit 4000000,1) limit 100;

5）一些不通过id查询的分页，没法保证顺序地“无序查询”。我们可以转化为通过id关联查询，查询id就可以使用索引覆盖技术了。ｓｅｌｅｃｔ * from table_a a, (ｓｅｌｅｃｔ id from table_a a where a.login_time >= 1590076800 order by a.login_time, a.id limit 4000000, 10 ) a1 where a1.id = a.id order by a.id;

上面是对近期慢SQL治理工作中的一些SQL知识点总结，后续还会分享遗留系统数据库表结构无法改变情况下的查询优化体会。