Introducing the situation of mysql index failure

                                                       

# mysql video tutorial column 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';

##1 , Violating the leftmost prefix rule

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

2. Doing any operation on the index column

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

3. Index The column on the right side of the range condition

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

4. Try to use covering index

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

5. Use not equal to (! =,)

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

6, like starts with a wildcard character ('�c')

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

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

7. String index without single quotes is invalid

explain select * from user where name = 2000;

8. or connection

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

9、order by

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

导致额外的文件排序（会降低性能）
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;//含非索引字段

##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!