mysql中的索引使用以及索引失效及索引常见面试题

本篇博文的mysql版本:5.7.26

准备相关数据表和测试数据

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dep_id` int(11) NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `cus_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_ndc`(`name`, `dep_id`, `cus_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 插入测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 10, 1000.00, 1);
INSERT INTO `employee` VALUES (2, '后裔', 1, 20, 2000.00, 1);
INSERT INTO `employee` VALUES (3, '孙尚香', 1, 20, 2500.00, 1);
INSERT INTO `employee` VALUES (4, '凯', 4, 20, 3000.00, 1);
INSERT INTO `employee` VALUES (5, '典韦', 4, 40, 3500.00, 2);
INSERT INTO `employee` VALUES (6, '貂蝉', 6, 20, 5000.00, 1);
INSERT INTO `employee` VALUES (7, '孙膑', 6, 50, 5000.00, 1);
INSERT INTO `employee` VALUES (8, '蔡文姬', 30, 35, 4000.00, 1);

-- 给salary字段添加一个普通索引
ALTER TABLE employee ADD INDEX `idx_salary`(`salary`) USING BTREE;

一、索引的使用

1.1、复合索引(又被称为:联合索引)

复合索引的描述:对多个字段同时建立的索引(有顺序,ABC,ACB是完全不同的两种复合索引)


给 name、dep_id、age 这三个字段建立一个复合索引

-- 创建复合索引
ALTER TABLE employee ADD INDEX `idx_name_dep_age`(`name`, `dep_id`, `age`) USING BTREE;


①、使用到了复合索引中的1个

EXPLAIN SELECT * from employee WHERE `name` = '鲁班'

示例截图:

m1.png

上图中我们看到确实使用到了索引,注意上图中的key_len(索引长度)列的值是:63


②、使用到了复合索引中的2个

EXPLAIN SELECT * from employee WHERE `name` = '鲁班' AND dep_id = 1

示例截图:

m2.png

上图中我们看到key_len(索引长度)列的值已经变成了是:68,由此可见我们的复合索引 使用到了不止1列


③、使用到了复合索引中的3个(这里也就是 复合索引全部用上了,也被称为:全值匹配)

m3.png

上图中我们看到key_len(索引长度)列的值已经变成了是:73,比前面2个显示的key_len都多,所以这里可以肯定是复合索引中的全部索引字段都用上了,实际开发过程中,我们最好最理想的结果也是用上 全值匹配

1.2、复合索引中的最佳左前缀法则

最佳左前缀法则的描述:如果索引中有多个列,要遵守最左前缀法则,指的就是从索引中的最左列开始,并且不跳过索引中的列。比如说:跳过索引中的第1列,使用第2列,索引直接失效。跳过索引中的第1列,使用第2列和第3列,索引直接失效。跳过索引中的第1列和第2列,使用第3列,索引直接失效等。下面直接看举的例子吧。


①、跳过索引中的第一列,直接导致索引失效

EXPLAIN SELECT * from employee WHERE dep_id = 1

示例截图:

m4.png

②、跳过索引中的前2列,索引直接失效

EXPLAIN SELECT * from employee WHERE age = 10

示例截图:

m5.png


③、跳过索引中的中间索引列,则只有第1列生效

示例截图:

m6.png

④、复合索引中的使用顺序可以乱

EXPLAIN SELECT * from employee WHERE age = 10 AND name = '鲁班' AND dep_id = 1

示例截图:

m7.png


复合索引中的最佳左前缀法则总结:所谓最佳左前缀法则就是在使用复合索引的时候,尽量优先匹配复合索引中的最左边的那一列索引字段。比如说:上面我们的复合索引中,最左边的索引字段是name字段,所以我们在使用任何条件查询数据的时候,只要用上了该复合索引中的任何一个字段,都要优先使用最左边的name列,这样才不会导致该复合索引失效。所以创建复合索引的时候,复合索引中的最左边的字段尤为重要,深思熟虑后再加复合索引。所以说,复合索引遵循“带头大哥不能灭,中间兄弟不能断”的原则。

二、索引的失效

2.1、不在索引列上做任何操作(无论复合索引还是普通索引等)

计算、函数、类型转换等会导致索引失效而转向全表扫描。

EXPLAIN SELECT * from employee WHERE trim(name) = '鲁班'

示例截图:

m8.png

上图中可以看到 name列用上了trim()函数直接导致索引失效。无论是我们上面创建的复合索引还是单独给name列添加一个普通索引都会失效。

2.2、复合索引中的范围条件右边的索引列失效

EXPLAIN  SELECT * FROM employee WHERE name = '鲁班' AND dep_id > 1 AND age = 10;

m9.png

上图中可以看到最右边的age这个索引列失效了,哪里能看出来?可以通过key_len(索引长度)这一列的值进行判断。

2.3、使用不等于(!=或<>)的时候无法使用索引会导致全表扫描(无论复合索引还是普通索引等)

EXPLAIN  SELECT * FROM employee WHERE name <> '鲁班'

示例截图:

m10.png

2.4、is not null无法使用索引(无论复合索引还是普通索引等)

EXPLAIN  SELECT * FROM employee WHERE name is not null

示例截图:

m11.png

2.5、少用or,用or连接时会导致索引失效(or索引失效或不失效分多种情况)

①、复合索引or不失效的情况如下

EXPLAIN  SELECT * FROM employee WHERE name = '鲁班' OR name = '孙尚香'

示例截图:

m12.png

②、复合索引or失效的情况如下

EXPLAIN  SELECT * FROM employee WHERE name = '鲁班' OR age = 10

示例截图:

m13.png

上图中 复合索引or导致索引失效了,主要是因为or后面的字段不是同一个(这里也就是name)导致的。



③、普通索引使用or直接导致索引失效

EXPLAIN  SELECT * FROM employee WHERE salary = 1000 OR salary = 5000

示例截图:

m14.png

2.6、like以通配符开头(%字符)会导致索引失效变成全表扫描

EXPLAIN  SELECT * FROM employee WHERE name LIKE '%鲁'

m15.png

上图中可以看到 like %开头的字符会导致索引失效,为什么?因为mysql看到 like %开头的语句 不知道到底要查什么,所以当然会选择放弃使用索引,直接走全表扫描了。


下面这2种情况的 like 不会导致索引失效:

示例1:

EXPLAIN  SELECT * FROM employee WHERE name LIKE '鲁%'

为什么这种情况下的 like 不会失效?因为 mysql 知道要搜索“鲁”字符开头的数据,所以知道要查什么,就会使用索引。就不截图了。


示例2:

EXPLAIN  SELECT name FROM employee WHERE name LIKE '%鲁%'

-- 该条语句的作用为什么能让索引生效?使用覆盖索引解决 like 两边的% 让索引生效

2.7、字段类型是字符串 如果不加引号会导致索引失效

EXPLAIN  SELECT * FROM employee WHERE name = 200

示例截图:

m16.png


索引失效尾声:以上列举的一些情况都会导致索引失效。如何解决上面这些索引失效?可以使用覆盖索引解决上面列举的这些情况导致索引失效的问题。

三、尽量使用覆盖索引,大部分索引失效都能解决

什么是覆盖索引?

覆盖索引:查询的字段和建立索引的字段刚好吻合,这种我们称为覆盖索引。也就是说 查询的时候 不要使用select * 而是使用select column1,column2,column3..........


什么是覆盖索引的更多种解释:

解释一: 就是说select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。


解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。


解释三:是非聚集组合索引的一种形式,它包括在查询里的select、join和where子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)。


不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以mysql只能使用B-Tree索引做覆盖索引。


当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时,在explain的Extra列可以看到“Using index”的信息。


说了这么多上个demo看看?

好的。

3.1、使用覆盖索引解决上面提到的 like %字符 导致索引失效的问题

EXPLAIN  SELECT name,age FROM employee WHERE name like '%鲁'

示例截图:

m17.png

上图中看到了吧


四、索引的尾声

sql优化小口诀:

全值匹配我最爱,最左前缀要遵守;

带头大哥不能灭,中间兄弟不能断;

索引列上少计算,范围之后全失效;

LIKE百分写最右,覆盖索引不写星;

不等空值还有or,索引失效要少用;

VAR引号不可丢,SQL高级也不难!

五、索引常见的面试题

假设建立复合索引(a,b,c),请说出以下条件是否使用到了索引及使用情况

5.1、where a = 4    答:使用到了索引a

5.2、where a = 4 and b = 6    答:使用到了索引a,b

5.3、where a = 4 and c = 5 and b = 6    答:使用到了a,b,c

5.4、where b = 4 or b=5    答:没有使用索引

5.5、where a = 4 and c = 6    答:使用到了索引a

5.6、where a  = 4 and b > 5 and c=6    答:使用到了索引a,b,c不会用到,因为前面的b是范围查询

5.7、where a = 4 and b like 'test%' and c=4    答:使用到了a,b   test%相当于范围查询,所以后面的c不会用到索引

5.8、where a = 4 order by b,c    答:使用到了索引a,不会有filesort

5.9、where b = 5 order by a    答:没用到索引,会有filesort。因为没用到最佳左前缀原则 直接跳过了a索引列了

5.10、where b = 5 order by c    答:没用到索引,会有filesort

5.11、where a = 5 group by  c,b    答:使用到了索引a,会造成Using temporary;同时也会造成Using filesort;因为分组的时候,特别强调索引的顺序,所以这里改成group by b,c 即可让 Using temporary和Using filesort消失。



外界事物纷至沓来  我敞开所有的感观接纳  来者全是客    -->人间食粮



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

小周博客
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复 0人评论 7,777人参与

loading