Mysql索引失效问题demo

Mysql索引失效问题demo

# 1. 准备工作
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` INT DEFAULT '0',
  `name` VARCHAR(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` INT DEFAULT '0',
  `address` VARCHAR(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO test.user (id, CODE, age, NAME, height,address) VALUES (3, '103', 23, '苏东坡', 174,'北宋');

# 8.0.28
SELECT VERSION();


EXPLAIN SELECT * FROM USER WHERE id=1;
# 由于id字段是主键，该sql语句用到了主键索引。

# 2. 不满足最左匹配原则
EXPLAIN SELECT * FROM USER WHERE CODE='101';

EXPLAIN SELECT * FROM USER WHERE CODE='101' AND age=21 

EXPLAIN SELECT * FROM USER WHERE CODE='101' AND age=21 AND NAME='周星驰';
# 上面三种情况，sql都能正常走索引。

EXPLAIN SELECT * FROM USER WHERE CODE = '101'  AND NAME='周星驰';
# 查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引。
# 这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
# 最左匹配原则

## 以下未应用索引
EXPLAIN SELECT * FROM USER WHERE age=21;
EXPLAIN SELECT * FROM USER WHERE age=21 AND NAME='周星驰';
EXPLAIN SELECT * FROM USER WHERE NAME='周星驰';
# 说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。

# 3. 使用了select *
EXPLAIN SELECT * FROM USER WHERE NAME='苏东坡';
# 在该sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。

EXPLAIN 
SELECT CODE,NAME FROM USER 
WHERE NAME='苏东坡';
# 如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。
# 而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。

# 4. 索引列上有计算
EXPLAIN SELECT * FROM USER WHERE id=1;
# 由于id字段是主键，该sql语句用到了主键索引。

EXPLAIN SELECT * FROM USER WHERE id+1=2;
# 从上图中的执行结果，能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。

# 5. 索引列用了函数
EXPLAIN SELECT * FROM USER  WHERE height=17;

EXPLAIN SELECT * FROM USER  WHERE SUBSTR(height,1,2)=17;
# 这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。
# 在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了。

# 6. 字段类型不同
EXPLAIN 
SELECT * FROM USER WHERE CODE="101";
# 温馨提醒一下，查询字符字段时，用双引号“和单引号都可以。

# 但如果你在写sql时，不小心把引号弄掉了，把sql语句变成了：
EXPLAIN 
SELECT * FROM USER WHERE CODE=101;
# 该sql语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。
# 因为code字段的类型是varchar，而传参的类型是int，两种类型不同。

# 如果int类型的height字段，在查询时加了引号条件，却还可以走索引：
EXPLAIN SELECT * FROM USER 
WHERE height='175';
# 该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。

# mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。

SELECT 1 + '1';
# mysql自动把字符串1，转换成了int类型的1，然后变成了：1+1=2。

SELECT CONCAT(1,'1');
# 如果你确实想拼接字符串该怎么办？
# 答：可以使用concat关键字。

/**
关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？

答：根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?

感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
**/

# 7. like左边包含%
SELECT * FROM USER WHERE NAME LIKE '李%';
/**
目前like查询主要有三种情况：

like '%a'
like 'a%'
like '%a%'
**/

EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '10%';


EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '%1';
# 这种%在1左边时，code字段上索引失效了，该sql变成了全表扫描。

EXPLAIN SELECT * FROM USER
WHERE CODE LIKE '%1%';
# 该sql语句的索引也会失效。

/**
那么，为什么会出现这种现象呢？
答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。
通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。
但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母不现实。
**/

# 8. 列对比
EXPLAIN SELECT * FROM USER 
WHERE id=height
/**
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。
但如果把两个单独建了索引的列，用来做列对比时索引会失效。
**/

# 9. 使用or关键字
EXPLAIN SELECT * FROM USER 
WHERE id=1 OR height='175';


EXPLAIN SELECT * FROM USER 
WHERE id=1 OR height='175' OR address='北宋';
# 之前的索引都失效了。因为你最后加的address字段没有加索引，从而导致其他字段的索引都失效了。
# 注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

# 10. not in和not exists
/**
在我们日常工作中用得也比较多的，还有范围查询，常见的有：
in
exists
not in
not exists
between and
**/
EXPLAIN SELECT * FROM USER
WHERE height IN (173,174,175,176);
# sql语句中用in关键字是走了索引的。

EXPLAIN SELECT * FROM USER  t1
WHERE  EXISTS (SELECT 1 FROM USER t2 WHERE t2.height=173 AND t1.id=t2.id)
# 用exists关键字同样走了索引。

/**
上面演示的两个例子是正向的范围，即在某些范围之内。
那么反向的范围，即不在某些范围之内，能走索引不？
**/
EXPLAIN SELECT * FROM USER
WHERE height NOT IN (173,174,175,176);
# 索引失效了。

EXPLAIN SELECT * FROM USER
WHERE id  NOT IN (173,174,175,176);
# 主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

EXPLAIN SELECT * FROM USER  t1
WHERE  NOT EXISTS (SELECT 1 FROM USER t2 WHERE t2.height=173 AND t1.id=t2.id)
# sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。

# 11. order by的坑
# order by后面的条件，也要遵循联合索引的最左匹配原则。
EXPLAIN SELECT * FROM USER
ORDER BY `code` LIMIT 100;

EXPLAIN SELECT * FROM USER
ORDER BY `code`,age LIMIT 100;

EXPLAIN SELECT * FROM USER
ORDER BY `code`,age,NAME LIMIT 100;
# 索引失效了?? 
# 除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。

## 查找的是索引列，会适应索引。
EXPLAIN SELECT CODE,NAME FROM USER
ORDER BY `code`,age,NAME LIMIT 100;

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY age;
# order by还能配合where一起遵循最左匹配原则。

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY NAME;
# 虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序

EXPLAIN SELECT * FROM USER
ORDER BY CODE DESC,age DESC LIMIT 100;
# order by后面的code和age字段都用了降序，所以依然走了索引  失效了??

## 查找的是索引列，会适应索引。
EXPLAIN SELECT CODE,NAME FROM USER
ORDER BY CODE DESC,age DESC LIMIT 100;

EXPLAIN SELECT * FROM USER
WHERE CODE='101'
ORDER BY CODE, NAME;
# code字段在where和order by中都有，对于这种情况，从图中的结果看出，还是能走了索引的。

EXPLAIN SELECT * FROM USER
ORDER BY CODE, NAME;
# 如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。


EXPLAIN SELECT * FROM USER
ORDER BY CODE, height LIMIT 100;
# 如果对多个索引进行order by，索引也失效了。

EXPLAIN SELECT * FROM USER
ORDER BY NAME LIMIT 100;
# name字段是联合索引的第三个字段，从图中看出如果order by不满足最左匹配原则，确实不会走索引。

EXPLAIN SELECT * FROM USER
ORDER BY CODE ASC,age DESC LIMIT 100;
# 尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。