索引的使用

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=79

验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时。

SELECT * FROM tb_sku WHERE Sn = '100000003145001';

针对字段创建索引

create index idx_sku_sn on tb_sku(sn);

然后再次执行相同的SQL语句，再次查看SQL的耗时。

SELECT*FROM tb_sku WHERE sn = '100000003145001';

索引的使用规则

1、最左前缀法则
如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。跟顺序无关

2、范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效，所以一般使用（>=或 <=）

3、索引列运算
不要再索引列上进行运算操作，否则索引将失效

explain select * from tb_user where substring(phone,10,2)= '15';

4、字符串不加单引号
字符串类型字段使用时,不加引号，索引将失效。

explain select * from tb_user where profession='软件工程' and age = 31 and status = 0;

-- 不加单引号
explain select * from tb_user where phone = 17799990015;

5、模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

-- 模糊查询在尾部生效
explain select * from tb_user where profession like '软件%';

explain select * from tb_user where profession like '%工程';

explain select * from tb_user where profession like '%工%';

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

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

7、数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。

走不走索引取决于表中的分布的。如果查找的是表中大量的数据，则走全表扫描来的块，如果查找的数据在该表中比较稀有，则走索引扫描来的实在。

8、SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
user index:

explain select * from tb_user user index(idx_user_pro) where profession = '软件工程';

ignore index:

explain select * from tb_user ignore index(idex_use_pro) where profession = '软件工程';

force index：

explain select * from tb_user force index(index_user_pro) where profession = '软件工程';

9、覆盖索引【重点】
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select * 的使用。

explain select id.profession from tb_user where profession= '软件工程' and age =31 and status = '0';

explain select id.profession,age,status from tb_user where profession= '软件工程' and age=31 and status = '0';

explain select id.profession,age,status,name from tb_user where profession '软件工程' and age = 31 and status = '0';

explain select * from tb_user where profession='软件工程' and age = 31 and status = '0' ;

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

1、第一条语句:
select * from tb_user where id = 2;
解释：根据ID 查询数据，走的是聚集索引。因为查找的是 * （全部字段），所以返回索引下的整条数据；

2、第二条语句：
查询要返回的字段在辅助索引中都被找到了，这种就叫覆盖索引
select id,name from tb_user where name = ‘Arm’;
解释：根据name 查询数据，走的是辅助索引（二级索引）。通过name找到对应的叶子节点下的主键id。因为要返回的是id，name ，所以通过辅助索引可以直接进行返回。

3、第三条语句：
select id,name,gender from tb_user where name = ‘Arm’;
解释：根据name 查询数据，先走的是辅助索引（二级索引）。因为要返回的是id，name ,gender字段 ,在辅助索引里面找不到gender字段。所以要通过辅助索引下的id，进行回表查询，然后返回数据。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/71809.html