MySQL索引的使用

准备环境

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. 全值匹配，对索引列中所有的列都指定具体指。该情况下，索引生效，执行效率高
   eg:
   select * from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’\G;
   
2. 最左前缀法则
   如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。
   eg:
   select * from tb_seller where name=‘小米科技’ ;
   select * from tb_seller where name=‘小米科技’ and status=‘1’;
   select * from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’;
   匹配最左前缀法则，走索引：
   

select * from tb_seller where status=‘1’ ;
select * from tb_seller where and status=‘1’ and address=‘北京市’;
违反最左前缀法则，索引失效

select * from tb_seller where name=‘小米科技’ and address=‘北京市’;
符合最左法则，但是出现跳跃都一列，只有最左列索引生效：

3. 范围查询右边的列，不能使用索引
select * from tb_seller where name=‘小米科技’ and status>‘1’ and address=‘北京市’;

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

4. 不要在索引列上进行运算操作，索引将失效
   select * from tb_seller where substring(name,3,2) =‘科技’ ;
   

5. 字符串不加单引号，造成索引失效
   select * from tb_seller where name=‘小米科技’ and status = 0 ;
   
   由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

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

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

如果查询列，超出索引列，也会降低性能。
eg:
select status,address,password from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’;
(password不在索引列)

TIP :

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

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

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

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

7. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
   eg： name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的
   select * from tb_seller where name=‘黑马程序员’ or createtime = ‘2088-01-01 12:00:00’\G;
   
8. 以%开头的like模糊查询，索引失效
   如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。
   
   解决方案：
   通过覆盖索引来解决
   select sellerid from tb_seller where name like ‘%科技%’ ;
   select sellerid,name from tb_seller where name like ‘%科技%’ ;
   select sellerid ,name ,status ,adress from tb_seller where name 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);

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