关于数据库查询调优

慢查询日志

慢查询日志不仅记录select语句，update时间过长也包含在内。

mysql默认不开启，这个功能性能开销比较低，建议生产环境开启。

相关参数：建议都开启

slow_query_log 是否开启
slow_query_log_file 日志文件
long_query_time 定义查询时间阈值超过了就认为是慢查询，可根据业务调整。
log_show_admin_statements 是否记录数据库管理相关sql
log_queries_not_using_indexes 是否记录未使用索引的sql，很多慢sql的一个原因是没有使用索引，此项必然要开启。
buffer_pool 缓存大小，mysql数据库通常都单独机器运行的，通常我们会让这个值至少占主机的80%，特别是有大量报表类数据需要查询的时候，最好正式使用前先预热一下，第一次查询数据都在磁盘，会很慢。

辅助查询慢日志的工具有：

mysqldumpslow mysql自带的工具

elk架构也带慢日志查询的看板

查询优化器

查询优化器是mysql的一个核心模块。

我们执行的查询语句会先被查询优化器处理一次，这种策略类似于java代码执行前会新先经过JIT的重排序。

查询优化器执行的结果就把我们输入的语句变成了一条或者多条的执行计划，最终执行的是这些执行计划。

打个比方，一条语句有2个查询条件，我们分别不出先执行哪个会查询的更快，查询优化器会通过底层的一些方法计算出2种查询条件的成本，然后根据成品分成2条先后的执行计划。

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

相关命令：

explain [sql]

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

id：数值越大的计划越先执行，数值相同则前面的先执行。

select_type: primary主键查询，union联表查询等

type：查询类型，比如const代表常量查询，all全量查询等

key：索引类型

rows：越小代表磁盘操作会越少

select_type的语义：

type语义：
下列按性能排序依次降低

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

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

一些典型案例（mysql5.7）

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

1. like会不会查索引

1）explain selext * from table_a where email like ’aaa@aaa.com‘；
2）explain selext * from table_a where email like ’aaa@aaa.com%‘；
3）explain selext * from table_a where email like ’%aaa@aaa.com‘；
4）explain selext * from table_a where email like ’%aaa@aaa.com%‘；
查看执行计划发行1）和2）的type是range，走了索引，3）和4）是all没走索引。看来模糊匹配会直接全表扫描。

2. null查询会不会走索引

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

3. 函数会不会走索引

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

4. 类型不一致会不会走索引

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

1）select * from table_a where create_time >= '1591025358'; 会走索引,优化器会自动优化。

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

加入多列索引的定义为 key(colum_a, colum_b, colum_c); 满足最左前缀的情况下是会走索引的（range），不然就会全表扫描（all）。这个现象有点跟like相似，前面的字母或者值都不确定，后面的也没法索引。当然这里多列索引的前面指的是定义的第一列，where不一定非要第一个写colum_a，有就行。

如果一个where中有2个单列索引，次序不重要，优化器会把2个索引各自查一遍，再合并（union操作），执行计划会看到index_merge。

6. 比较union和or

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

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

8. 非等于!=、<>会不会走索引

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

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

9. 关于索引覆盖

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

10. 使用关联查询还是子查询

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

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

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

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

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

12. 分页相关问题

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

2）不要写select *

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

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

5）一些不通过id查询的分页，没法保证顺序的“无序查询”。
我们可以转化为通过id关联查询，查询id就可以使用索引覆盖技术了。
select * from table_a a, (
select 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;

其他原因

很多时候查询慢是因为开发不规范导致阻塞问题

1）数据库操作遇到表级锁

2）数据库操作遇到行级锁

3）事务执行时间太长，具体指数据库执行时间本身很短，业务代码耗时过长导致事务长时间不提交。

4）表数据量太大，没考虑优化。