本篇博文的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消失。
外界事物纷至沓来 我敞开所有的感观接纳 来者全是客 -->人间食粮
声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。