本文共 4679 字,大约阅读时间需要 15 分钟。
关于Explain的介绍可前往查看
现有如下表:
CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时 间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
根据这张表的查询来介绍一些优化原则(下面介绍的是一些常用的优化原则,但是不是100%覆盖所有的场景,有些特殊场景是不适用的)
有以下三条SQL:
均使用到了索引如下索引:EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';这条SQL语句使用了索引idx_name_age_position中的 name字段。 ken_len的计算方式: 1)name的类型为varchar 2)name字段的长度为24 3)name字段是not null 在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为:varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2 所以,这条SQL语句的ken_len为:3*24+2=74
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;这条SQL语句使用了索引idx_name_age_position中的 name字段和age字段。 ken_len的计算方式: 1)在1.1中计算name的ken_len为74 2)age字段的类型是int 3)age字段是not null 在上篇MySQL Explain执行计划详解中介绍了int的计算方式为:int:4字节 所以,这条SQL语句的ken_len为:74+4=78
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';这条SQL语句使用了索引idx_name_age_position中的 name字段、age字段和position字段。 ken_len的计算方式: 1)在1.2中计算name的ken_len为78 2)position字段的类型是varchar 3)position字段的长度是20 4)position字段是not null 在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为:varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2 所以,这条SQL语句的ken_len为:78+20*3+2=140
ps:如果计算出的key_len的长度多了1,看下是否哪个字段是允许为空的,MySQL中如果字段允许为 NULL,需要1字节记录是否为 NULL
综上所述:
全值匹配针对联合索引来说就是尽可能多的使用索引包含的字段,字段使用的越多,效率会更高,扫描行数会更少 通过计算key_len可以判断是否索引使用到了哪些字段 所以,全值匹配优化就是尽可能多的使用索引的字段如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
比如以下索引: 为什么age字段和position字段明明是在索引里面的,但是没有走索引,最终全表扫描??? 因为联合索引在查询的时候会先去比对第一个字段,第一个字段比对完成之后再去比对第二个字段…,以此类推。 现在联合索引的第一个字段是name,但是上述SQL中直接是从第二个字段开始查询,根据后面的字段,无法精确定位到索引树的某个节点,导致无法根据索引树查找。 现在相当于不知道第一个字段是什么,那么就需要把索引树中所有的节点都过一遍,其实就相当于是全表扫描了,索引用了和没用没有太大作用。 所以,如果使用了联合索引,查找条件必须要按照联合索引字段出现的顺序排列,否则会导致索引失效。ps:如果查询条件是:以下SQL,那么会不会走索引呢?
SELECT * FROM employees WHERE age = 22 and name = 'HanMeimei' and position = 'manager';
可以执行看下结果。
那么为什么第一个字段不是name也走了索引呢? 因为mysql 在真正执行SQL之前,内部会进行优化(可以使用explain EXTENDED + show warnings查看),真正执行的时候会按照联合索引的顺序去执行。比如有如下两个SQL:
直接根据name查询是会走索引的 将name字段left之后,不走索引了。 为什么会出现这种情况呢? 因为SQL中对取了name字段的前3位,索引树中没有执行函数之后对应的值,所以根本就走不了索引。 可以看下联合索引的底层存储结构,如果是从name字段中截取一部分来查询,整棵树就不再是有序的,是没有办法走索引的。 所以就需要考虑使用函数的结果是否可以在索引树中定位,而且还可以保持索引树依然有序。SQL走了索引全值匹配,ken_len为140,是name字段、age字段、position字段之和
SQL走了部分字段,key_len为78是name字段和age字段之和,position字段并没有走索引。 为什么不走position字段的索引呢?? SQL在查询name字段的时候是等值,走索引;查询age的时候是查询大于,范围查找也是走索引的。 在第一个字段相等的情况下,第二个字段肯定还是有序的。 SQL查询的时候其实是先根据第一个条件name=“LiLei”,把结果集加载出来,这个时候数据还是有序的,然后根据第二个条件age>22查询,数据就不一定有序了,所以在查询第三个条件的时候,没办法利用索引的有序性,所以就不会走索引了所以总的来说:存储引擎不能使用索引中范围条件右边的列(范围后面的字段都不会再走索引)
查询尽量不要用select *,建议指明具体的字段,而且这些字段尽量能被联合索引覆盖(如果不能全部覆盖,尽量多覆盖),要是实在覆盖不到,数据量有非常大,对效率要求又很高,这个时候就需要使用到搜索引擎 。
看上去name列是应该要走索引的(possible_keys是有值的),但是其实效率和全表扫描没有什么太大的区别,所以就直接走了全表扫描(key列是空的)
不等于的结果集有可能太多了,走索引总的来说没有什么太大的用处问题:上面说过的left(name,3)其实是和name%是类似的,为什么后者走了索引,而前者没有走呢?
因为mysql底层中会进行判断,如果对字段加了函数,就不会走索引,尽管可以很好的利用索引,也不会走。 问题:解决like’%字符串%'索引不被使用的方法? a)使用覆盖索引,查询字段必须是建立覆盖索引字段 b)如果不能使用覆盖索引则可能需要借助搜索引擎给年龄添加单值索引
ALTER TABLE `employees` 2 ADD INDEX `idx_age` (`age`) USING BTREE ;没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。比如这个例子,可能是由于单次数据量查询过大导致优化器最终选择不走索引 优化方法:可以将大的范围拆分成多个小范围
假设index(a,b,c)
where语句 | 索引是否使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b= 5 | Y,使用到a,b |
where a = 3 and b= 5 and c= 4 | Y,使用到a,b,c |
where b = 3 或者 where b= 3 and c= 4 或者 where c=4 | N |
where a = 3 and c= 5 | Y,使用到a,但是不可以使用c,b中间断了 |
where a = 3 and b>4 and c= 5 | Y,使用到a和b,c不能用在范围之后,b断了 |
where a = 3 and b like ‘kk%’ and c= 4 | Y,使用到a,b,c |
where a = 3 and b like ‘%kk’ and c= 4 | Y,只用到a |
where a = 3 and b like ‘%kk%’ and c= 4 | Y,只用到a |
where a = 3 and b like ‘k%kk%’ and c= 4 | Y,使用到a,b,c |
更多内容可前往查看。
转载地址:http://doyai.baihongyu.com/