令人头大的慢查询分析

令人头大的慢查询分析

前言

建表语句

create table student(
  `id`  BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age`  INT(11)    NOT NULL,
  `height` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`),
  KEY `multi_index` (`age`,`height`)  
);

explain命名使用方法

explain命令，主要可以对select语句进行分析，并输出select执行的详细信息，以供开发人员针对性优化。explian的使用十分简单，在select语句前面加上explain关键字即可，如下图,其中order_id是主键

输出的各个属性含义

id	select 查询的标识符,是唯一的
select_type	select查询类型
table	查询指定的表
partitions	匹配的分区
type	访问类型，ALL指的是全表扫描
possible_keys	查询中可能使用的索引
key	查询中确切使用到的索引
ref	哪个字段或常数与 key 一起被使用
rows	显示此查询一共扫描的行数,是一个估计值.
filtered	查询条件所过滤的数据的百分比
extra	额外信息

索引失效情况

• 最佳左前缀法则

  如果索引是复合索引，要遵循最佳左前缀法则，查询从索引的最前列开始，并且不跳过索引中的列

  下面是违反最佳左前缀法则的情况，针对复合索引(age, height), 直接根据height查询，则复合索引失效

  

• 在索引上做操作(计算、函数、类型转换)，均会导致索引失效

  如下图，对主键id，进行 id+1操作，会使索引失效

  

• 索引字段上使用(is null, not null)判断时，会使索引失效

  

• 索引字段使用like以通配符%开头时，会导致索引失效

  

• 索引字段中使用or

  

慢查询分析

msql通过指定long_query_time参数，设置慢查询的时间阈值，一旦查询语句的执行时间超过此参数，其就会被系统记录到慢查询日志中。通过对慢查询日志进行分析，可以对相应的慢查询语句或者数据库进行优化，从而提升数据库性能。下面详细讲述几种造成慢查询的原因和优化手段。

• 上文提到的索引失效

• limit分页语句偏移量过大

  limit语句的查询时间与偏移量成正比，所以一旦偏移量过大会造成查询速度巨慢

  使用子查询优化

  原查询语句

  select * from student limit 200000, 20;

  优化后查询语句(id是主键索引)

  select * from student where id >=(select id from student limit 200000, 1) limit 20;

• 数据库在刷脏页

  数据库的每一次修改(插入、删除、更新)操作，都会记录在redo log日志，redo log日志一般不会直接刷新到磁盘中，而是保存在缓冲区(os buffer),等操作系统空闲的时候，刷新进磁盘。但是缓冲区容量是有限的，如果数据库更新过于频繁，导致缓冲区满了,就不会等系统空闲，而是直接暂停其他操作，将缓冲区刷新进磁盘中。所以此时执行查询语句就会特别慢。

• 锁冲突

  数据库一般支持表锁，或者行锁。拿表锁举例，如果一个sql语句，获取表的写锁后，在执行更新操作，之后的查询(select)语句,需要等待表写锁释放，才能获取读锁。

  所以一旦查询语句执行涉及到的锁，被其他sql语句占有，无法获取锁，需要等待其释放锁，所以会造成查询慢。

参考文章

腾讯面试：一条SQL语句执行得很慢的原因有哪些？—不看后悔系列