MySQL索引的使用

2020-07-12  本文已影响0人  李白开水

一、索引对查询效率的提升

这是一个拥有300万行数据的表格：


image.png

表的大概结构：


image.png

查询id:


image.png

用了很短的执行时间，如果查询title字段：


image.png

用了4.6秒的时间，查询效率比较低，原因是id字段是主键，有主键索引，而title字段没有索引，所以查询效率比较低。

要解决这个问题，可以对title字段建立索引。
create index idx_item_title on tb_item(title);

创建成功：


image.png

大概花费了一分钟的时间。

再次查询：


image.png

查询效率大大提升了。

二、避免索引失效

创建一张新的表：

image.png
对这个表的三个字段创建联合索引：
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

1.全值匹配

对索引中所有列都指定具体值。


image.png

三个查询的值都为联合索引中的字段。

这时候用explain来查看它的执行计划：


image.png

走了索引。

2.最左前缀法则

创建索引的时候的语句是：
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

这个联合索引对name、status、address三个字段建立了索引，按照最左前缀法则，查询的时候，如果想使用这个索引，查询条件中要包含name字段，并且不能够跳过status字段，也就是如果想使用这个索引查询name和address，那么在查询条件中，也必须包含status字段，不能跳过它。

所以只要查询条件中包含索引的最左字段（name），不跳过字段，那么这个索引就没有失效，可以使用。

3.范围查询右边的列，不能使用索引 。

通过一组对比：


image.png

第二次查询的status字段是范围查找，可以看到两次查找索引的长度发生了变化，也就是第一个和第二个字段使用了索引，范围查询右边的列的索引失效了。

4.在索引列上进行运算操作， 索引将失效

image.png

可以看到，进行的是全表扫描，索引失效了。

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

如果是字段是varchar类型，不加单引号也可以查询出来：


image.png

加不加单引号索引长度发生了变化，没加单引号的时候，只有name字段走了索引，因为数据库检测到status是varchar类型，会自动进行隐式类型转换，这时候索引就失效了。

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

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

也就是说，如果要查询的字段是name，status和address，（select name,status,address from ....）这时候就尽量不使用select * from：

image.png

可以看到如果是select * from也使用了索引，但是extra给出的信息不同，在select * from中，using index condition是指：查找使用了索引，但是需要回表查询数据，所以select * from 相对于select name,status,address from ....查询效率还是慢了的。

7.用or分割开的条件

如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
索引失效，走的是全表扫描：


image.png

如果换成and：


image.png

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

以%开头，索引失效：


image.png

要解决这个问题可以使用覆盖索引（sellerid有索引）：


image.png
password没有索引：
image.png

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

对address字段创建单列索引：


image.png

这时候查询，查询北京市没有走索引，而查询西安市走了索引：


image.png
查看一下表：
image.png

在表中，共有12条数据，11条都包含了北京市，查询的又是select * from，需要回表，所以走索引还不如全表扫描快，所以索引就失效了。

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

image.png

在表中，address字段所有的行都是非空的，所以is null的情况没有或基本没有，这时候就会走索引。

而每个字段都是is not null的，所以就不走索引了。
同理（name字段有索引）：


image.png

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

image.png

12.单列索引和复合索引

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

create index idx_name_sta_address on tb_seller(name, status, address);
创建一个这样的复合索引相当于创建了三个索引：
name
name + status
name + status + address

如果创建单列索引：
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);
如果数据库中只有这三个单列索引，那么对name、status、address字段进行联合查询的时候，数据库只会选这三个单列索引中的一个最优索引（辨识度最高的索引），不会使用全部索引。
使用复合索引：


image.png

使用三个单列索引，只是用了一个索引：


image.png
image.png

三、查看索引的使用情况

show status like 'Handler_read%';
show global status like 'Handler_read%';

当前会话的索引使用情况：


image.png

全局的索引使用情况：


image.png
参数含义：
上一篇 下一篇

猜你喜欢

热点阅读