【MySQL索引的使用及索引失效情况】

1.索引结构

MySQL的索引是基于存储引擎实现的，不同的存储引擎有不同的索引结构

1. B+Tree索引 ，最常见的索引类型；
2. Hash索引 ，底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不 支持范围查询，无序的；

2.索引分类

1. 主键索引 ，针对于表中主键创建的索引，一个；
2. 唯一索引 ，避免同一个表中某数据列中的值重复，多个；
3. 常规索引 ，快速定位特定数据，多个；
4. 全文索引 ，全文索引查找的是文本中的关键词，而不是比较索引中的值，多个；

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

1. 聚集索引 ，将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，有且仅有一个；
2. 二级索引 ，将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个；

3.索引效率

表结构： 查看表中使用的索引：

#联合索引，最左前缀法则，可以运行一下sql脚本，查看是否用到了索引
EXPLAIN select * from tb_user where profession = 软件工程 and age > 23   and status = 6;
EXPLAIN select * from tb_user where profession = 软件工程 and status = 6;
EXPLAIN select * from tb_user where profession = 软件工程 and age >= 23   and status = 6;
#对索引字段进行运算，导致索引失效
EXPLAIN select * from tb_user where substring(phone,10,2) = 15;
#对字符串类型的索引，去掉单引号，导致索引失效
EXPLAIN select * from tb_user where phone = 17799990000;
EXPLAIN select * from tb_user where phone = 17799990000;
#使用索引进行模糊查询，如果模糊在前，导致索引失效
EXPLAIN select * from tb_user where profession like 软件%;
EXPLAIN select * from tb_user where profession like %软件;
#使用or关键字查询，如果or前后有字段没有索引，则索引全部失效
EXPLAIN select * from tb_user where id = 1 or phone = 17799990000;
EXPLAIN select * from tb_user where id = 1 or age = 21;
#如果mysql评估使用索引比全表扫描更慢的话，则索引失效
EXPLAIN select * from tb_user where phone >= 17799990020;
EXPLAIN select * from tb_user where phone >= 17799990000;
#用不用索引取决于profession字段在数据库中的分布情况
EXPLAIN select * from tb_user where profession is null;
EXPLAIN select * from tb_user where profession is not null;
#使用指定索引
create index index_profession on tb_user(profession);
EXPLAIN select * from tb_user use index(index_profession) where profession = 软件工程;
EXPLAIN select * from tb_user IGNORE index(index_profession) where profession = 软件工程;
#覆盖索引
#回表查询问题
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;
##针对覆盖索引说明：
因为，在tb_user表中有一个联合索引 idex_more，该索引关联了三个字段
profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主
键id。 
当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引
直接返回数据了。 
如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 
而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表
查询（除非是根据主键查询，此时只会扫描聚集索引）。