索引的使用
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;
--插入多条数据
--创建联合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
2. 避免索引失效的方式
(1) 全值匹配
对索引中所有列都指定具体值
(2) 最左前缀法则
查询从索引的最左列开始,并且不跳过索引中的列
-
遵守最左法则的情况
-
违反最左法则的情况
-
跳跃索引列的情况
只有跳跃列之前的索引生效
(3) 覆盖索引
尽量使用覆盖索引,减少select*
覆盖索引指的是只访问索引的查询(索引列完全包含查询列)
如果查询列超出索引列,也会降低性能:
Extra列取值:
using index :使用覆盖索引的时候就会出现
using where :在使用索引的情况下,需要回表查询所需的数据(回表查询:获取到了索引的数据,还要拿 着这些数据去表中获取一整行的数据)
using index condition :查找使用了索引,但是需要回表查询数据
using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表 查询数据
(4) 复合索引
尽量使用复合索引,减少使用单列索引
原因:使用复合索引,多列查询时数据库会选择一个最优的索引来使用,并不会使用多个索引;如果使用单列索引,查询多个列时会使用多个索引
3. 索引失效的情况
-
范围查询之后的条件,索引将失效
根据前面的两个字段name , status 查询是走索引的, 但是最后一个条件 address 没有用到索引
-
在索引列上进行运算操作, 索引将失效
-
字符串不加单引号,索引将失效
- 注意:varchar类型加不加单引号都可以查询出数据
原因:没有对字符串加单引号,MySQL的查询优化器会自动的进行类型转换,进行了运算操作,造成索引失效
-
or中条件有非索引列,全部索引将失效
and无此规定
下述示例中,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的
-
以%开头的Like模糊查询全列,索引将失效
如果仅仅是尾部模糊匹配,索引不会失效
解决方案:
通过覆盖索引来解决,不使用select*
-
如果MySQL评估使用索引比全表更慢,索引将失效
比如某一列的值全是重复的,但也创建了索引,此时查询索引比查询表更慢
-
is null、is not null 有时索引失效
-
in 走索引,not in 索引失效
查看索引使用情况
show status like 'Handler_read%'; --当前Session
show global status like 'Handler_read%'; --全局
Handler_read_first :索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。
Handler_read_key :如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)。
Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。
Handler_read_prev :按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。
Handler_read_rnd_next :在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。