SQL 优化与诊断

Explain 诊断

Explain 各参数的含意如下：

select_type 常见类型及其含意

• SIMPLE：不蕴含子查问或者 UNION 操作的查问
• PRIMARY：查问中如果蕴含任何子查问，那么最外层的查问则被标记为 PRIMARY
• SUBQUERY：子查问中第一个 SELECT
• DEPENDENT SUBQUERY：子查问中的第一个 SELECT，取决于内部查问
• UNION：UNION 操作的第二个或者之后的查问
• DEPENDENT UNION：UNION 操作的第二个或者之后的查问, 取决于内部查问
• UNION RESULT：UNION 产生的后果集
• DERIVED：呈现在 FROM 字句中的子查问

type 常见类型及其含意

• system：这是 const 类型的一个特例，只会呈现在待查问的表只有一行数据的状况下
• consts：常呈现在主键或惟一索引与常量值进行比拟的场景下，此时查问性能是最优的
• eq_ref：当连贯应用的是残缺的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 时应用它
• ref：当连贯应用的是前缀索引或连贯条件不是 PRIMARY KEY 或 UNIQUE INDEX 时则应用它
• ref_or_null：相似于 ref 类型的查问，然而附加了对 NULL 值列的查问
• index_merge：该联接类型示意应用了索引进行合并优化
• range：应用索引进行范畴扫描，常见于 between、>、< 这样的查问条件
• index：索引连贯类型与 ALL 雷同，只是扫描的是索引树，通常呈现在索引是该查问的笼罩索引的状况
• ALL：全表扫描，效率最差的查找形式

阿里编码标准要求： 至多要达到 range 级别，要求是 ref 级别，如果能够是 consts 最好

key 列

理论在查问中是否应用到索引的标记字段

Extra 列

Extra 列次要用于显示额定的信息，常见信息及其含意如下：

• Using where：MySQL 服务器会在存储引擎检索行后再进行过滤
• Using filesort：通常呈现在 GROUP BY 或 ORDER BY 语句中，且排序或分组没有基于索引，此时须要应用文件在内存中进行排序，因为应用索引排序的性能好于应用文件排序，所以呈现这种状况能够思考通过增加索引进行优化
• Using index：应用了笼罩索引进行查问，此时不须要拜访表，从索引中就能够获取到所需的全副数据
• Using index condition：查找应用了索引，然而须要回表查问数据
• Using temporary：示意须要应用长期表来解决查问，常呈现在 GROUP BY 或 ORDER BY 语句中

如何查看 Mysql 优化器优化之后的 SQL

# 仅在服务器环境下或通过 Navicat 进入命令列界面
explain extended  SELECT * FROM `student` where `name` = 1 and `age` = 1;

# 再执行
show warnings;

# 后果如下：/* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))

为什么要做这个事呢？咱们晓得 Mysql 有一个最左匹配准则，那么如果我的索引建的是 age，name，那我以 name，age 这样的程序去查问是否应用到索引呢？实际上是能够的，就是因为 Mysql 查问优化器能够帮忙咱们主动对 SQL 的执行程序等进行优化，以选取代价最低的形式进行查问（留神是代价最低，不是工夫最短）

SQL 优化

超大分页场景解决方案

如表中数据须要进行深度分页，如何提高效率？在阿里出品的 Java 编程标准中写道：

利用提早关联或者子查问优化超多分页场景

阐明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，而后返回放弃前 offset 行，返回 N 行，那当 offset 特地大的时候，效率就十分的低下，要么管制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写

# 反例（耗时 129.570s）select * from task_result LIMIT 20000000, 10;

# 正例（耗时 5.114s）SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;

# 阐明
task_result 表为生产环境的一个表，总数据量为 3400 万，id 为主键，偏移量达到 2000 万 

获取一条数据时的 Limit 1

如果数据表的状况已知，某个业务须要获取合乎某个 Where 条件下的一条数据，留神应用 Limit

阐明：在很多状况下咱们已知数据仅存在一条，此时咱们应该告知数据库只用查一条，否则将会转化为全表扫描

# 反例（耗时 2424.612s）select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';

# 正例（耗时 1.036s）select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1;

# 阐明
task_result 表为生产环境的一个表，总数据量为 3400 万，where 条件非索引字段，数据所在行为第 19486 条记录 

批量插入

# 反例
INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)

# 正例
INSERT into person(name,age) values('A',24),('B',24),('C',24);

# 阐明
比拟惯例，就不多做阐明了 

like 语句的优化

like 语句个别业务要求都是 '% 关键字 %' 这种模式，然而仍然要思考是否思考应用右含糊的形式去代替产品的要求，其中阿里的编码标准提到：

页面搜寻严禁左含糊或者全含糊，如果须要请走搜索引擎来解决

# 反例（耗时 78.843s）EXPLAIN select * from task_result where taskid LIKE '%tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1;

# 正例（耗时 0.986s）select * from task_result where taskid LIKE 'tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1

##########################################################################
# 对正例的 Explain
1    SIMPLE    task_result        range    adapt_id    adapt_id    98        99    100.00    Using index condition

# 对反例的 Explain
1    SIMPLE    task_result        ALL                                        33628554    11.11    Using where

# 阐明
task_result 表为生产环境的一个表，总数据量为 3400 万，taskid 是一个一般索引列，可见 %% 这种匹配形式齐全无奈应用索引，从而进行全表扫描导致效率极低，而正例通过索引查找数据只须要扫描 99 条数据即可 

防止 SQL 中对 where 字段进行函数转换或表达式计算

# 反例
select * from task_result where id + 1 = 15551;

# 正例
select * from task_result where id = 15550;

##########################################################################
# 对正例的 Explain
1    SIMPLE    task_result        const    PRIMARY    PRIMARY    8    const    1    100.00    

# 对反例的 Explain
1    SIMPLE    task_result        ALL                                    33631512  100.00    Using where

# 阐明
其实在晓得了有 SQL 优化器之后，我个人感觉这种一般的表达式转换应该能够提前进行解决再进行查问，这样一来就能够用到索引了，然而问题又来了，如果 mysql 优化器能够提前计算出后果，那么写 sql 语句的人也肯定能够提前计算出后果，所以矛盾点在这个中央，导致 5.7 版本以前的此种状况都无奈应用索引吧，将来可能会对其进行优化 

应用 ISNULL() 来判断是否为 NULL 值

阐明：NULL 与任何值的间接比拟都为 NULL

# 1）NULL<>NULL 的返回后果是 NULL，而不是 false。# 2）NULL=NULL 的返回后果是 NULL，而不是 true。# 3）NULL<>1 的返回后果是 NULL，而不是 true。

多表查问

我所在的公司根本禁止了多表查问，那如果必须应用到的话，咱们能够一起参考一下阿里的编码标准

Eg：超过三个表禁止 join。须要 join 的字段，数据类型必须相对统一；多表关联查问时，保障被关联的字段须要有索引

明明有索引为什么还走全表扫描

之前答复一些面试问题的时候，对某一个点的了解呈现了偏差，即我认为只有查问的列有索引则肯定会应用索引去 Push 数据

然而实际上不仅仅是这样，真正应该是： 针对查问的数据行占总数据量过多时会转化成全表查问

那么这个过多指代的是多少呢？

我的测试后果是 50%，但集体认为 MySQL 优化器不会齐全纠结于行数辨别是否全表，而是有很多其余因素综合思考发现全表扫描的效率更低等等，所以充分认识到该问题即可

count(*) 还是 count(id)

阿里的 Java 编码标准中有以下内容：

【强制】不要应用 count(列名) 或 count(常量) 来代替 count(*)

count(*) 是 SQL92 定义的规范统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

阐明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行

字段类型不同导致索引生效

阿里的 Java 编码标准中有以下内容：

【举荐】避免因字段类型不同造成的隐式转换，导致索引生效

实际上数据库在查问的时候会作一层隐式的转换，比方 varchar 类型字段通过 数字去查问

# 正例
EXPLAIN SELECT * FROM `user_coll` where pid = '1';
type：ref
ref：const    
rows:1    
Extra:Using index condition

# 反例
EXPLAIN SELECT * FROM `user_coll` where pid = 1;
type：index
ref：NULL    
rows:3(总记录数)
Extra:Using where; Using index

# 阐明
pid 字段有相应索引，且格局为 varchar 

对于

感激以下博文及其作者：

干货！SQL 性能优化，书写高质量 SQL 语句

干货！SQL 性能优化，书写高质量 SQL 语句（二）

MySQL 官网文档

Tips

自建数据表进行测试

CREATE TABLE `student` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '状态：0 失常，1 解冻，2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

插入数据

DELIMITER ;;
    CREATE PROCEDURE insertData()
    BEGIN
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO student(`name`,class,`page`,`status`)
                VALUES(CONCAT('class_', i),
                    CONCAT('class_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit;
        END;;
CALL insertData();