索引是否可使用简单介绍
我们创建的索引,就例如我们修一栋楼房一样,我们修了一楼修了二楼也修了三楼,正例如我们索引创建了c1c2c3一样,
- 然而你使用的时候,不能直接使用c1和c3,也就是你不能直接从一楼到三楼,而不经过二楼
- 同样,我们在使用范围索引的时候,我们若没有指定某一楼的具体指,索引也会失效,因为你没有指定,就是在这层楼的每一户去查找,看是否有楼上三楼,那这样还怎么能叫索引呢?
- 具体的见后面分析
EXPLAIN介绍
explain可以模拟优化器执行SQL语句,从而知道mysql是如何执行处理sql语句的,以便于分析查询语句的一个性能
那么explain到底能做什么呢?
- 表的读取顺序
- 数据读取操作类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被查询
表头信息如下:
-
ID:表示表的执行顺序,如果ID数字相等,则从上往下执行,若ID不等,则先执行ID较大的那张表
就如同这图一样,msql会优先加载demo2表,然后再加载demo1 -
select_type:表示该条sql语句的类型是什么,常用的值有如下:
- simple:表示这查询不包含union或者子查询
- primary:表示这查询是最外层的查询
- union:表示这查询是union的第二或随后的查询
- suquery:子查询的第一个select
EXPLAIN select * from demo1 where ID = (select id from demo2 where id=1)
例如这个实例一样,首先加载demo2表,因为ID更大,同时它的查询类型是“suquery”,因为是子查询,然后demo1的查询是“primary”,因为是最外层的查询
-
table:就是表名
10.type:很重要的一个属性,后文会详细讲解 -
possible_keys:有可能会使用到的索引,具体后文会详解
-
key:实际使用的索引,后文详解
-
key_len:索引的长度,因为我们的索引也是占据空间的
-
ref:索引指向的那一列
-
rows:查询这行数据,查询了多少行
-
Extra:附加信息
- Using filesort:当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
- Using index:“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
- Using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
表展示,以及索引展示
select * from demo1
SELECT * from demo2
show INDEX from demo1
type属性具体讲解
-
All:全表查询
EXPLAIN select * from demo1
-
index:利用全索引查询
EXPLAIN select * from demo1,demo2 where demo1.c1=demo1.c1
-
range:使用索引范围查询,一般用于>、<、<=之类
EXPLAIN select * from demo1 where c1='a1' and c2>'a2' and c3='a3'
-
ref:非唯一性索引扫描,返回匹配某个单独值的所有行
EXPLAIN select * from demo1 where c1='a1'
-
eq_ref:此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果
-
const:针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
EXPLAIN select * from demo1 where ID =1
因为这里where条件对应的记录只有一条,而且ID也是主键 -
system:表中只有一条数据. 这个类型是特殊的 const 类型.一般用于系统表中
EXPLAIN select * from (select * from demo2 where id=1)d2
-
然而这几种type的效率也是有性能排序的:
ALL < index < range < ref < eq_ref < const < system
-
同时,我们进行查询优化,并不是一定要优化到最好,例如优化到system、const之类的,而是应该尽量去避免ALL,以及更多能使性能达到range、ref级别,就可以了,因为范围查询在很多场景是无法避免的
possible_keys与keys详解
这是这里很好理解,前者表示认为会使用的索引,后者表示实际使用的索引;正例如,我们平时生活中,你父母认为你是喜欢吃苹果,然而你喜欢吃香蕉,是一个道理,我们看几个例子
EXPLAIN select id,c1,c2,c3 from demo1 where c1='a1' and c3='a3' and id>1
- 这里我们的查询条件中用到了c1,c3,以及ID主键
- 以为我们会用到我们自己创建的索引以及主键索引
- 然而,我们只用到了自定义的索引,同时也只访问了一个索引键
- 因为我们c1和c3中间断开了,没有连接所以索引无法连续
key_len详解
-
字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
-
数值类型:
-
TINYINT: 1字节
-
SMALLINT: 2字节
-
MEDIUMINT: 3字节
-
INT: 4字节
-
BIGINT: 8字节
-
-
时间类型
-
DATE: 3字节
-
TIMESTAMP: 4字节
-
DATETIME: 8字节
-
-
字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
EXPLAIN select * from demo1 where c1='a1' and c3='a3' GROUP BY c2
EXPLAIN select * from demo1 where c1='a1' and c2='a2' GROUP BY c3
- 很明显看到这两句sql其实都差不多,只是顺序不一样而已,然而我们的key_len却不一样
- 那就是因为我们钱一句的索引到达c3的时候寄失效了,也就是只有c1使用了索引的
- 而第二句是c1与c2都使用了索引,同时我们的c3也是使用了索引,因为我们的索引就是未来查询和排序
小总结
- 其实以上介绍的就基本上是explain的一些基本参数使用了
- 这样可以对explain有一个直观的理解,为我们后面的索引优化做铺垫
- 接下来通过一些实例了解查询优化的策略吧
关于索引的优化,请看另一篇博客
总结
- 以上就是针对explain的全部讲解以及针对索引优化的分析了
- 希望这些查询例子可以帮助到你,
- 如果大家有补充欢迎评论区留言交流