Mysql索引学习笔记之使用_select id is not null 索引失效吗-CSDN博客

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);

5.2 索引失效情况

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
组合索引，不是使用第一列索引，索引失效。
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描
在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效
1. 联合查询参考
2. MySQL中IS NULL、IS NOT NULL、!= 问题参考
对索引字段进行计算操作、字段上使用函数
在索引字段上使用not，<>。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

5.3 避免索引失效

全值匹配，对索引中所有列都指定具体值
最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的
范围查询右边的列，不能使用索引
不要再索引列上进行运算操作
字符串不加单引号，造成索引失效
1. 由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效
2. 如varchar不加单引号的话可能会自动转换为int型
尽量使用覆盖索引，避免select *
1. 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *
2. 如果查询列，超出索引列（即查找的字段没有添加索引），也会降低性能
尽量使用符合索引

5.4 查看索引使用情况

show status like 'Handler_read%';
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：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。