最近在学习深入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）来访问存在索引的某个字段时 |
如有补充，欢迎指点。
