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` = '鲁班'

示例截图：

上图中我们看到确实使用到了索引，注意上图中的key_len（索引长度）列的值是：63

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

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

示例截图：

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

③、使用到了复合索引中的3个（这里也就是 复合索引全部用上了，也被称为：全值匹配）

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

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

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

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

EXPLAIN SELECT * from employee WHERE dep_id = 1

示例截图：

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

EXPLAIN SELECT * from employee WHERE age = 10

示例截图：

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

示例截图：

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

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

示例截图：

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

二、索引的失效

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

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

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

示例截图：

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

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

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

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

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

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

示例截图：

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

EXPLAIN  SELECT * FROM employee WHERE name is not null

示例截图：

2.5、少用or，用or连接时会导致索引失效（or索引失效或不失效分多种情况）

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

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

示例截图：

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

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

示例截图：

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

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

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

示例截图：

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

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

上图中可以看到 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

示例截图：

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

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

什么是覆盖索引？

覆盖索引：查询的字段和建立索引的字段刚好吻合，这种我们称为覆盖索引。也就是说 查询的时候 不要使用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 '%鲁'

示例截图：

上图中看到了吧

四、索引的尾声

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消失。

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

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