索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

验证索引提升查询效率

主键， 有索引，查询会很快，当where 某字段查询过慢时，可将改字段设置为索引，设置方式参见：索引
创建完后，可explain，查看执行计划

索引的使用

演示数据：

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

避免索引失效

索引全值匹配

对索引中的列都指定具体值，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

最左前缀法则

（1）如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
匹配最左前缀法则，走索引：

explain select * from tb_seller where name = '小米科技';
explain select * from tb_seller where name = '小米科技' and status = '1';
explain select * from tb_seller where name = '小米科技' and status = '1' and address = '北京市';

执行顺序结果：

（2）违法最左前缀法则 ， 索引失效：

explain select * from tb_seller where status = '1';

explain select * from tb_seller where status = '1' and address = '北京市';

（3）如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

explain select * from tb_seller where name = '小米科技' and address = '北京市';

（4）范围查询，范围后的右边列的索引失效

explain select * from tb_seller where name = '小米科技' and status > '1' and address = '北京市';

即：字段name ， status 查询走索引， address 没有用到索引

• TIP:

当 > 或 < 调整为 >= 或 <= 时，name，status，address都走索引

（5）不要在索引列上进行运算操作， 否则索引将失效

explain select * from tb_seller where substring(name,3,2) = '科技';

（6）字符串不加单引号，会使得索引失效
原因：在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

注意：`status` varchar (1)

-- name 走索引,status 走索引
explain select * from tb_seller where name = '小米科技' and status = '1';

-- name 走索引,status 索引失效
explain select * from tb_seller where name = '小米科技' and status = 1;

执行顺序结果：

(7) 尽量使用覆盖索引，避免select
1）尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select 。

explain select * from tb_seller where name = '科技' and status = '1' and address = '北京市';
explain select name from tb_seller where name = '科技' and status = '1' and address = '北京市';
explain select name,status from tb_seller where name = '科技' and status = '1' and address = '北京市';
explain select name,status,address from tb_seller where name = '科技' and status = '1' and address = '北京市';

执行顺序结果：

2）如果查询列，超出索引列，也会降低性能。

explain select name,status,address,password from tb_seller where name = '科技' and status = '1' and address = '北京市';

• [x] TIP :

    using index ：使用覆盖索引时会出现<br />        using where：在查找使用索引的情况下，需要回表去查询所需的数据<br />        using index condition：查找使用了索引，但是需要回表查询数据<br />        using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
  

（8）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select name,status,address,password from tb_seller where name = '百度科技有限公司' and nickname = '百度小店';
explain select name,status,address,password from tb_seller where name = '百度科技有限公司' or nickname = '百度小店';

执行顺序结果：

（9）以%开头的Like模糊查询，索引失效。
如果是头部模糊匹配，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。

-- 索引失效
explain select * from tb_seller where name like '%科技%';
explain select * from tb_seller where name like '%科技';

-- 索引不失效
explain select * from tb_seller where name like '科技%';

执行顺序结果：

解决方案 ：
通过覆盖索引来解决，即用索引字段替换 select *

explain select name from tb_seller where name like '%科技%';
explain select name,status from tb_seller where name like '%科技%';
explain select name,status,address from tb_seller where name like '%科技%';

（10） 如果MySQL评估使用索引比全表更慢，则不使用索引。
创建另一个索引

create index idx_address on tb_seller(address);

查询1：未使用到索引，由于address列基本所有数据均为’北京市’，MySQL评估不使用索引查询会更快

explain select * from tb_seller where address = '北京市';

查询2：使用了索引，MySQL评估使用索引查询更快

explain select * from tb_seller where address = '西安市';

（11）is null，is not null 有时索引失效。

（12） in 走索引， not in 索引失效。
注：筛选字段要为索引字段，即：sellerid（主键），name，status，address

-- 走索引
explain select * from tb_seller where sellerid in('alibaba','baidu');

-- 索引失效
explain select * from tb_seller where sellerid not in('alibaba','baidu');

（13）单列索引和复合索引。
尽量使用复合索引，而少使用单列索引 。

创建复合索引

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 session status like 'Handler_read%';    -- session可省略

show global status like 'Handler_read%';