数据库索引失效

记录一下,方便自己记忆.

前提:参考

1.建表:

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`),
    ->   KEY `idx_height` (`height`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

2.插入数据

INSERT INTO user (id, code, age, name, height , address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO user (id, code, age, name, height , address) VALUES (2, '102', 23, '周杰伦', 171,'台湾');
INSERT INTO user (id, code, age, name, height , address) VALUES (3, '103', 25, '周晓', 173,'成都');

3.mysql版本

肯定失效:

2.or连接的所有字段只要有一个没有建立索引则不能命中索引--->所有字段都建立索引，索引有效．

3.不符合最左匹配原则

4.like "%**"或like "%**%"不能命中索引.--->like "**%"索引有效．

5.字符串类型没有加 ''， 如 id_card = 1345 ,其中字段id_card为char类型，会索引失效-->id_card = '1345'

6. 索引列上有计算/函数,会索引失效.

7.如果把两个单独建了索引的列，用来做列对比时索引会失效.

可能失效:

1.使用select *

不失效，id为主键索引

explain select * from user where id = 1;

 失效，name字段上没有建立索引

explain select * from user where name = '周晓';

 但如果上句sql改为查询索引字段，如

explain select code , age, name from user where name = '周晓';

则不会去主键索引树进行全表扫描，会去联合索引树上进行全索引扫描．联合索引树叶子节点只存储所有联合索引字段和主键数据,但主键索引树的叶子节点存储所有字段数据信息,因此全索引扫描(index)比全表扫描(ALL)稍微快点.

2.使用not in

不失效，主键字段中使用not in关键字查询数据范围．

注意：这里我用mysqlv5.7.39测试,主键字段中使用not in关键字查询数据范围也失效了，但在这篇文章中该作者使用mysqlv8.0.21，索引没有失效．

失效，普通索引字段使用了not in关键字查询数据范围．

 3.使用order by

此情况也因版本不一样，索引是否失效也不同．

不失效，配合where使用．

 注意，此时where后面的字段和order by 后面的字段要满足最左匹配原则,否则失效.具体实例见失效情况分析.

失效，不满足最左匹配原则．

 失效，没加where．

  失效，对不同的索引做order by．