最近在学习深入MySQL,记录一下学习历程和一些问题。
如何分析SQL的查询效率
在日常操作中,总会有一些查询得比较慢的SQL,但是造成查询慢的原因有很多,那么我们要怎么样才能准确的找到问题所在呢?
先创建一张表
drop table if exists index_test01;
create table `index_test01`(
`id` int(11) NOT NULL AUTO_INCREMENT,/*AUTO_INCREMENT表示自增*/
`a` int(11) ,
`b` int(11) ,
`c` int(11) ,
`d` int(11) ,
PRIMARY KEY (`id`),/*主键*/
KEY `idx_a` (`a`),/*为a字段创建索引*/
KEY `idx_b_c` (`b`,`c`)/*为b,c字段创建联合索引*/
);
drop procedure if exists insert_index_test01; /*如果存储过程insert_index_test01存在就删除它*/
delimiter $$ /*改变结束符 MYSQL的默认结束符为";"*/
create procedure insert_index_test01() /*创建存储过程insert_index_test01*/
begin
declare i int;/*声明变量i*/
set i=1;/*设置i的初始值为1*/
while (i<100000)do /*循环10万次*/
insert into index_test01(a,b,c,d) values(i,i,i,i); /*自定义SQL 这里是为index_test01添加10万条数据*/
set i=i+1;
end while;
end $$
delimiter ;/*还原结束符*/
call insert_index_test01(); /* 运行存储过程insert_t9_1 */
想要看到明显的差距的话需要加表的数据
反复执行
创建完毕后 执行SQL
insert into index_test01(a,b,c,d)
select a,b,c,d from index_test01;
比如我们想看看下面SQL是否走了索引
select * from index_test01 where c=9000
只需要在SQL前 加上explain
explain select * from index_test01 where c=9000
下面是对这些字段的一些解释
explain相关字段解释
列名 | 注释 |
---|---|
id | 查询编号 |
select_type | 查询类型 |
table | 涉及到的表格 |
partitions | 匹配的分区:查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表,该值为 NULL。 |
type | 本次查询的表连接类型 |
possible_keys | 可能选择的索引 |
key | 实际选择的索引 |
key_len | 被选择索引的长度 |
ref | 与索引比较的列 |
rows | 预计需要扫描的行数,对 InnoDB 来说,这个值是估值,并不一定准确 |
filtered | 涉及到按条件筛选的行的百分比表格 |
Extra | 附加信息 |
select_type常见参数
value | 解释 |
---|---|
SIMPLE | 简单查询 (不使用关联查询或子查询) |
PRIMARY | 如果包含子查询或者关联查询,则最外层的查询部分标记为 primary |
UNION | 联合查询中第二个及后面的查询 |
DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
UNION RESULT | 联合查询的结果 |
SUBQUERY | 子查询中的第一个查询 |
DEPENDENT SUBQUERY | 子查询中的第一个查询,并且依赖外部查询 |
DERIVED | 用到派生表的查询 |
MATERIALIZED | 被物化的子查询 |
UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行 |
UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
type常见参数
value | 解释 |
---|---|
system | 查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表,这是最好的情况 |
const | 基于主键或唯一索引查询,最多返回一条结果 |
eq_ref | 表连接时基于主键或非 NULL 的唯一索引完成扫描 |
ref | 基于普通索引的等值查询,或者表间等值连接 |
fulltext | 全文检索 |
ref_or_null | 表连接类型是 ref,但进行扫描的索引列中可能包含 NULL 值 |
index_merge | 利用多个索引 |
unique_subquery | 子查询中使用唯一索引 |
index_subquery | 子查询中使用普通索引 |
range | 利用索引进行范围查询 |
index | 全索引扫描 |
ALL | 全表扫描 |
Extra常见参数
value | 解释 |
---|---|
Using filesort | 将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序 |
Using temporary | 需要创建一个临时表来存储结构,通常发生对没有索引的列进行 GROUP BY 时 |
Using index | 使用覆盖索引 |
Using where | 使用 where 语句来处理结果 |
Impossible WHERE | 对 where 子句判断的结果总是 false 而不能选择任何数据 |
Using join buffer (Block Nested Loop) | 关联查询中,被驱动表的关联字段没索引 |
Using index condition | 先条件过滤索引,再查数据 |
Select tables optimized away | 使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时 |
如有补充,欢迎指点。