Introducing the situation of mysql index failure

# mysql video tutorialcolumn index expires.

#Indices are a very important chapter for MySQL. There are also a lot of index knowledge points. If you want to master them thoroughly, you need to break down each knowledge point one by one. Today, let’s first talk about the situations that will cause index failure.

Picture summary version

Related free learning recommendations:mysql video tutorial

Full value matching (best index)

explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy' and phone = '18730658760';

和索引顺序无关，MySQL底层的优化器会进行优化，调整索引的顺序 explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy' and phone = '18730658760';

如果索引有多列，要遵守最左前缀法则 即查询从索引的最左前列开始并且不跳过索引中的列 explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';

Copy after login

如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描 explain select * from user where left(name,5) = 'zhangsan' and age = 20 and phone = '18730658760';

Copy after login

索引范围条件右边的索引列会失效 explain select * from user where name = 'zhangsan' and age > 20 and pos = 'cxy';

Copy after login

只访问索引查询（索引列和查询列一致），减少select* explain select name,age,pos,phone from user where age = 20;

Copy after login

mysql在使用不等于（!=、）的时候无法使用索引会导致全表扫描（除覆盖索引外） explain select * from user where age != 20; explain select * from user where age 20;

Copy after login

索引失效 explain select * from user where name like '%zhangsan';

Copy after login

索引生效 explain select * from user where name like 'zhangsan%';

explain select * from user where name = 2000;

Copy after login

少用or explain select * from user where name = '2000' or age = 20 or pos ='cxy';

Copy after login

正常（索引参与了排序） explain select * from user where name = 'zhangsan' and age = 20 order by age,pos; 备注：索引有两个作用：排序和查找

Copy after login

导致额外的文件排序（会降低性能） explain select name,age from user where name = 'zhangsan' order by pos;//违反最左前缀法则 explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则 explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段

Copy after login

##10、group by

正常（索引参与了排序） explain select name,age from user where name = 'zhangsan' group by age; 备注：分组之前必排序（排序同order by）

导致产生临时表（会降低性能） explain select name,pos from user where name = 'zhangsan' group by pos;//违反最左前缀法则 explain select name,age from user where name = 'zhangsan' group by pos,age;//违反最左前缀法则 explain select name,age from user where name = 'zhangsan' group by age,created_time;//含非索引字段

##Sample data used

mysql> show create table user \G ****************************************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(10) DEFAULT '0', `pos` varchar(30) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, `created_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name_age_pos_phone` (`name`,`age`,`pos`,`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The above is the detailed content of Introducing the situation of mysql index failure. For more information, please follow other related articles on the PHP Chinese website!