Young Kbt blog
Young Kbt
2021-10-24
目录

MySQL - 索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

# 验证索引提升查询效率

在我们准备的表结构 tb_item 中，一共存储了 300 万记录；

1). 根据 ID 查询

select * from tb_item where id = 1999\G;
1

查询速度很快，接近 0s，主要的原因是因为 ID 为主键，自带索引；

2). 根据 title 进行精确查询

select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;
1

查看 SQL 语句的执行计划 ：

处理方案，针对 title 字段，创建索引：

create index idx_item_title on tb_item(title);
1

索引创建完成之后，再次进行查询：

通过 explain，查看执行计划，执行 SQL 时使用了刚才创建的索引

# 准备环境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

create index idx_seller_name_sta_addr on tb_seller(name,status,address);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

创建了三个索引，分别对应 name、status、address 三个字段。

# 避免索引失效

1). 全值匹配，对索引中所有列都指定具体值

该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
1

2). 最左前缀法则

如果索引了多列（复合索引），要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

explain select * from tb_seller where name='小米科技';
explain select * from tb_seller where name='小米科技' and status='1';
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
1
2
3

违法最左前缀法则，索引失效：

explain select * from tb_seller where status='1';
explain select * from tb_seller where and status='1' and address='北京市';
1
2

name 是最左边的索引，因为没有先用到，导致索引失效，走全局扫描 ALL。

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

explain select * from tb_seller where name='小米科技' and address='北京市';
1

中间跳过了 status，所以相对于 address，违反了最左法则，仅有 name 的索引生效。

3). 范围查询，不能使用索引

explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';
1

前面的两个字段 name，status 查询是走索引的，但是最后一个条件 address 没有用到索引。

4). 不要在索引列上进行运算操作，否则索引将失效

explain select * from tb_seller where substring(name,3,2) = '科技';
1

5). 如果字符串不加单引号，造成索引失效

explain select * from tb_seller where name = '科技' and status = 0;
1

在查询时，没有对字符串加单引号，MySQL 的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免 select *

尽量使用覆盖索引（只访问索引的查询，如索引列完全包含查询列），减少 select *。

explain select * from tb_seller where name='科技' and status='0' and address='西安市';
1

如果查询列，超出索引列，也会降低性能。

explain select status,address,password from tb_seller where name='科技' and status='0' and address='西安市';
1

Extra 说明：

  • using index：使用覆盖索引的时候就会出现

  • using where：在查找使用索引的情况下，需要去表里查询所需的数据

  • using index condition：查找使用了索引，但是需要去表里查询数据

  • using index; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7). 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

示例，name 字段是索引列，而 createtime 不是索引列，中间是 or 进行连接是不走索引的：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';
1

8). 以 % 开头的 Like 模糊查询，索引失效

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_seller where name like "%黑马程序员";
1

解决方案 ：

通过覆盖索引来解决 (不用 select *)

9). 如果 MySQL 评估使用索引比全表更慢，则不使用索引

我们先给 address 创建索引

create index idx_seller_address on tb_seller(address);
1

在我们表 tb_seller 中，12 条地区数据其中 11 个是北京市

查北京地区的走全表扫描：

explain select * from tb_seller where address like "北京市";
1

使用覆盖查询会走索引

explain select address from tb_seller where address='北京市';
1

10). is NULL，is NOT NULL 有时 索引失效

和上一条（9）差不多。

11). in 走索引，not in 索引失效

在 MySQL 5.6 中

个人理解：not in 判断不存在的，需要对表进行大部分数据扫描，类似于第九条

MySQL 5.7 中都不失效：

12). 单列索引和复合索引

尽量使用复合索引，而少使用单列索引。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

--就相当于创建了三个索引 ： 
--	name
--	name + status
--	name + status + address
1
2
3
4
5
6

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
1
2
3

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

# 查看索引使用情况

show status like 'Handler_read%';  -- 当前会话级别

show global status like 'Handler_read%'; -- 全局级别
1
2
3

  • Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）

  • Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）

  • Handler_read_next：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加

  • Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC

  • Handler_read_rnd：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要 MySQL 扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救

  • Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引

# 练习

假设 index(a,b,c)

Where 语句 索引是否被使用
where a = 3 Y，使用到 a
where a = 3 and b = 5 Y，使用到 a，b
where a = 3 and b = 5 and c = 4 Y，使用到 a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N（第二条，左前缀法则）
where a = 3 and c = 5 使用到 a，但是 c 不可以，b 中间断了
where a = 3 and b > 4 and c = 5 使用到 a 和 b，c 不能用在范围之后，b 断了
where a is null and b is not null is null 支持索引，但是 is not null 不支持
where a <> 3 不能使用索引
where abs(a) =3 不能使用索引
where a = 3 and b like 'kk%' and c = 4 Y，使用到 a，b，c
where a = 3 and b like '%kk' and c = 4 Y，只用到 a
where a = 3 and b like '%kk%' and c = 4 Y，只用到 a
where a = 3 and b like 'k%kk%' and c = 4 Y，使用到 a，b，c
编辑此页 (opens new window)
#关系数据库
更新时间: 2023/10/23, 10:58:52
MySQL - 检测优化SQL
MySQL - SQL语句优化

← MySQL - SQL语句优化→

最近更新
01
技术随笔 - Element Plus 修改包名 原创
11-02
02
Reactor - 扩展性
11-02
03
Reactor - 最佳实践
11-02
更多文章>
Theme by Vdoing | Copyright © 2021-2024 Young Kbt | blog
桂ICP备2021009994号