Home > Database > Mysql Tutorial > body text

Introducing the situation of mysql index failure

coldplay.xixi
Release: 2020-11-30 17:55:28
forward
3853 people have browsed it

# mysql video tutorial column index expires.

Introducing the situation of mysql index failure

#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

Introducing the situation of mysql index failure

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';
Copy after login

Introducing the situation of mysql index failure

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

Introducing the situation of mysql index failure

##1 , Violating the leftmost prefix rule

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

Introducing the situation of mysql index failure

2. Doing any operation on the index column

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

Introducing the situation of mysql index failure

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';
Copy after login

Introducing the situation of mysql index failure

4. Try to use covering index

只访问索引查询(索引列和查询列一致),减少select*
explain select name,age,pos,phone from user where age = 20;
Copy after login

Introducing the situation of mysql index failure

5. Use not equal to (! =,)

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

Introducing the situation of mysql index failure

Introducing the situation of mysql index failure

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

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

Introducing the situation of mysql index failure

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

Introducing the situation of mysql index failure

7. String index without single quotes is invalid

explain select * from user where name = 2000;
Copy after login

Introducing the situation of mysql index failure

8. or connection

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

Introducing the situation of mysql index failure

9、order by

正常(索引参与了排序)
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

Introducing the situation of mysql index failure

Introducing the situation of mysql index failure

Introducing the situation of mysql index failure##10、group by

正常(索引参与了排序)
explain select name,age from user where name = 'zhangsan' group by age;
备注:分组之前必排序(排序同order by)
Copy after login

导致产生临时表(会降低性能)
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;//含非索引字段
Copy after login
Introducing the situation of mysql index failure

Introducing the situation of mysql index failure

Introducing the situation of mysql index failure

##Sample data usedIntroducing the situation of mysql index failure

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
Copy after login

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!

Related labels:
source:learnku.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template