mysql 数据库索引在什么场景下会失效？实战篇

 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`), -- 建立 idx_code_age_name 索引
   KEY `idx_height` (`height`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

 INSERT INTO sue.user (id, code, age, name, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
-- INSERT INTO sue.user (id, code, age, name, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
 INSERT INTO sue.user (id, code, age, name, height,address) VALUES (3, '203', 23, '苏三', 174,'成都');

select version() as 'mysql版本号';

--  where 后面是主键，使用了主键索引
explain select * from user where id = 1;

-- 这5条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
explain select * from user where code = '101';
explain select * from user where code = '101' and name = '周星驰'
explain select * from user where code = '101' and age = 21
explain select * from user where code = '101' and age = 21 and  name = '周星驰'
explain select * from user where code = '101' and address = '香港'

--  不满足最左匹配原则,没有包含给定字段最左边的索引字段，即字段code。
explain select * from user where age =21
explain select * from user where name = '周星驰'
explain select * from user where age = 21 and name = '周星驰'

-- 使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
explain select * from user where name = '周星驰'
-- select语句中的查询列，都是索引列，那么这些列被称为覆盖索引,查询的相关字段都能走索引.
explain select code,name from user where name = '周星驰'
explain select id,code,name from user where name = '周星驰'

-- id字段的主键索引，在有计算的情况下失效。id+1=2-->id = 2
explain select * from user where id+1=3

-- height 走普通索引
explain select * from user where height = 173
-- 在使用该函数 SUBSTR() 之后，走全表扫描，索引失效
explain select * from user where SUBSTR(height,1,3) = 173

-- 走联合索引 idx_code_age_name ，查询字符字段时，用双引号"和单引号'都可以。
explain select * from user where code = "101"
-- code字段的类型是varchar，而传参的类型是int，两种类型不同,索引失效。
explain select * from user where code = 101
-- 走普通索引：idx_height。int类型的参数，不管在查询时加没加引号，都能走索引。
explain select * from user where height = '175'
-- 如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。
select 1+'1'  -- 执行结果是2

-- 想拼接字符串该怎么办？使用concat关键字。
select concat(1,'1')  -- 执行结果是 11

-- 查出所有code是10开头的用户：%在参数右边时走索引
explain select * from user where code like '10%'
-- 查出所有code是1结尾的用户：%在1左边，code字段索引失效
explain select * from user where code like '%1'
-- 查出所有code包含1结尾的用户：当like语句中的%，出现在查询条件的左边时，索引会失效。
select * from user where code like '%1%'
 
-- 如果把两个单独建了索引的列，用来做列对比时索引会失效
explain select * from user where id = height
-- myisam:如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效。
-- innodb：在myisam的基础上：数据量少不走索引，数据量大的前提下，比如100条数据查出符合条件的数据为少量，即：10条左右走索引，查出的数据为多量，即：70条左右则不走索引
-- 结论：or、in 通常是走索引的，当in后面的数据在数据表中超过30%（匹配数据大约6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系。
explain select * from user where id = 2 or height= '173';
-- 走索引，结论如上
explain select * from user
where height in (173,174,175,176);
-- 内层走索引，外层结论如上
explain select * from user  t1
where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
-- 而普通索引字段使用了not in关键字查询数据范围，索引会失效。结论如上
explain select * from user
where height not in (173,174,175,176);
-- 主键字段中使用not in关键字查询数据范围，任然可以走索引。
explain select * from user
where id  not in (173,174,175,176);
-- t1不走索引，t2走索引
explain select * from user  t1
where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)