# 索引的使用 ## 1.准备环境 ```sql 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=utf8mb; 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); #创建联合索引name status address ``` ## 2.避免索引失效 ### 1). 全值匹配 ，对索引中所有列都指定具体值。 ~~~ mysql> explain select * from tb_seller where name='' and status='1' and address=''\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb_seller partitions: NULL type: ref possible_keys: idx_seller_name_sta_addr key: idx_seller_name_sta_addr key_len: 612 ref: const,const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR: No query specified ~~~ ### 1). 全值匹配 ，对索引中所有列都指定具体值。 改情况下，索引生效，执行效率高。 ```sql explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G; ```  ### 2). 最左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。 **2.1匹配最左前缀法则，走索引：**  索引全部覆盖时，索引长度比较长。 **2.2违法最左前缀法则 ， 索引失效：** ~~~ explain select * from tb_seller where status='0' and address='北京市'; ~~~  **2.3如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：：** ~~~ explain select * from tb_seller where name = '小米科技' and address='北京市'; ~~~  **2.4 范围查询右边的列，不能使用索引 。** ~~~ explain select * from tb_seller where status = '0' and address='北京市'; ~~~  **2.5 不要在索引列上进行运算操作， 索引将失效。** ~~~ explain select * from tb_seller where substring(name,3,2) = '科技'; ~~~  **2.6 字符串不加单引号，造成索引失效。** ~~~ mysql> explain select * from tb_seller where name = '小米科技' and status =0; ~~~ 由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。  此时只命中name一个索引，索引长度为单独命中name时的长度、 **2.6 尽量使用覆盖索引，避免select ** ~~~ mysql> explain select * from tb_seller where name = '小米科技' and status='0' and address='北京市'; ~~~  如果查询列，超出索引列，也会降低性能。 ~~~ mysql> explain select name,nickname from tb_seller where name = '小米科技' and status='0' and address='北京市'; ~~~  ``` TIP : using index ：使用覆盖索引的时候就会出现,完全使用索引 using where：在查找使用索引的情况下，需要回表去查询所需的数据 using index condition：查找使用了索引，但是需要回表查询数据 using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 ``` **2.7 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。** 示例，name字段是索引列 ， 而nikename不是索引列，中间是or进行连接是不走索引的 ： ```sql explain select * from tb_seller where name='黑马程序员' or nickname = '张三'; ```  **2.8 以%开头的Like模糊查询，索引失效。** 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 ~~~ mysql> explain select * from tb_seller where name like '%黑马程序员'; ~~~  **2.9 如果MySQL评估使用索引比全表更慢，则不使用索引。** address 是有索引的，但是查询时并不会走索引 address 的内容单一且全部都是重复的，mysql 判定走索引比全表更慢，所以查询不能使用索引。  其实这个字段不适合加索引，我们这只做测试做。 **2.10). is NULL ， is NOT NULL 有时索引失效** 将其中一个name 修改成NUll ，测试mysql索引优化选择器。  可以看到，并不是is NULL ， is NOT NULL完全不走索引，Mysql 内部会自动判断是否会要使用索引，mysql 判定走索引比全表更慢，就不会使用索引。 **2.11). in 走索引， not in 索引失效**  尽量使用复合索引，而少使用单列索引 。 创建复合索引 ``` 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); ``` 数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。