索引的使用
- 一、准备环境
- 二、避免索引失效
- 三、查看索引使用情况
索引是数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
一、准备环境
--1.建表
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;
-- 2.创建索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
--3.然后插入部分测试数据,此处不展示insert sql
二、避免索引失效
- 1).全值匹配,对索引中所有列都指定具体值。改情况下,索引生效,执行效率高。
explain select*from tb_seller where name='小米科技' and status='1' and address = '北京市'\G;
- 2).最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列
匹配最左前缀法则,走索引:
违法最左前缀法则,索引失效:
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效: - 3).范围查询右边的列,不能使用索引。
根据前面的两个字段name,status查询是走索引的,但是最后一个条件address没有用到索引。 - 4).不要在索引列上进行运算操作,索引将失效。
- 5).字符串不加单引号,造成索引失效。
由于,在查询是,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。 - 6).尽量使用覆盖索引,避免select *
尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select *。
如果查询列,超出索引列,也会降低性能。
TIP:
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'\G;
- 8).以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
解决方案:
通过覆盖索引来解决 - 9).如果MySQL评估使用索引比全表更慢,则不使用索引。
- 10). is NULL,is NOT NULL有时索引失效。
- 11). in走索引,not in索引失效。
- 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);
数据库会选择一个最优的索引(辨识度最高索引)来使用,并不会使用全部索引。
三、查看索引使用情况
show status like'Handler_read%';
show global status like'Handler_read%';