【Mysql】索引类型优化策略
1. 索引类型
1.1 普通索引
这是一个最基本的索引类型，基于普通字段建立索引，没有任何限制。
1.2 唯一索引
普通索引允许被索引的数据列包含重复的值
唯一索引就是刚好相反，不允许索引的数据列里面有重复的值。比如说身份证号，那么一定是一个唯一索引。因为不可能有两个人的身份证号一模一样。
1.3 主键索引
主键索引，只要声明一个主键，系统会为主键自动建立索引
1.4 复合索引
相对于单一索引来说，单一索引是索引只有一列，复合索引是在多列上面建立索引。
最左前缀匹配原则：当我的查询条件大于等于两个的时候，我需要给两个字段都加上索引，并且索引的顺序需要和查询条件的顺序一致。
1.5 覆盖索引
- 如果索引的叶子节点中已经包含了要查询的数据，那么就没有必要回表了，如果一个索引包含所有需要查询的字段的值，我们就称为“覆盖索引”。
- 由于innodb的聚簇索引，覆盖索引对innodb表特别有用，innodb的二级索引在叶子节点存放了行的主键值，所以如果耳机主键能够覆盖查询，则可以避免对主键索引的二次查询。
1.6 全文索引
- 全文索引查找的是文本中的关键词，查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。
select * from user where match(name) against('AAA')
1.7 索引下推
- user（name，age）
- select * from user where name like ‘张%’ and age=10；
- 在Mysql5.6之前，他的查找是这样的，因为我的查询条件是“张%”，那么我只能先用name = “张”查所有符合条件的主键，然后通过主键去查找age=10符合条件的行。
- Mysql5.6之后，使用了索引下推，也就是说，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
- 对于这个查询，他先根据name=“张”找到符合条件的数据，再根据age=10再过滤。这就是回表下推。
1.8 聚簇索引
- 聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引
- 如果索引值和行记录分开存放就属于非聚簇索引。
- 聚簇索引的优点是：访问速度更快，此外使用覆盖索引扫描查询的时候可以直接使用叶子节点中的主键值。
- 聚簇索引的缺点是：更新聚簇索引的代价很高
- 一般使用自增id作为主键，是因为在插入操作的时候，主键的值是顺序的，会吧每一个行记录存储在上一条记录的后面，他是一种按照顺序的方式存储。如果使用uuid作为主键，无法简单的总把新的行记录插入到索引后面，而是要为新的行记录寻找合适的位置，会增加很多额外的工作。会产生大量的随机io。并且写入是乱序的，innodb需要不停的做页分裂操作，以便为新的行分配空间，页分裂会导致大量的数据。所以我们应该让主键按照自增的顺序插入数据。
1.9 辅助索引
- 辅助索引其实就是普通索引
- B+Tree的叶子节点存放的是主键字段值就属于主键索引
- 如果存放的是非主键值
就属于辅助索引 - 当我们创建了一个非主键的列位索引的时候，会根据索引的列创建B+结构，叶子节点中存放的是索引列以及主键的信息。
1.10 前缀索引
- 一般用于给字符串加索引，比如说给字符串前6个字母加索引。但是使用前缀索引，我们需要定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
1. 索引原理
2.1 B+树索引
- 非叶子节点不存储data数据，只存储索引值
- 叶子节点包含了所有的所引值和data数据
- 叶子节点用指针连接，提高区间访问效率。在进行范围查找的时候，只需要遍历范围内所有的节点和数据。
2.2 hash索引
- 比如说memory存储引擎，他的索引类型是hash索引
- 他会根据Where查询条件，通过hash函数，寻找指针，那么指针指向的就是所要查找的行记录。
- hash索引的缺点是：不能用户排序，不能用于范围查找，只能用户等值查询。并且，hash索引会存在hash冲突，出现hash冲突的时候，需要遍历连标中的所有行指针。优点是查询非常快。
2.3 索引的优点
- 索引大大减少了服务器需要扫描的数据行
- 索引可以帮助我们服务器避免排序和创建临时表
- 索引可以将随机io变成顺序io
3. 索引优化策略
- 不要在字段开头使用模糊查询，比如 like “%aaa”，一定走全盘扫描，不要吧%放在前面，如果真的要吧%放在前面，就需要考虑数据量，几千条那没事，不用花里胡哨的，可以吧%放前面。但是数据量特别大，使用instr来匹配。
- 避免使用非等值查询，比如 in <> 这种情况，不会走索引。可以用between
- 避免使用or，可以使用union all
- 每一个字段尽可能设置默认值0，不要对null进行判断，不会走索引
- where 后面字段的类型需要与db一致，比如db里面是varchar，where后面是int，会有一个隐式转换，有开销。
- 最左前缀匹配原则
- order by 条件要与where中的条件一致
- where 条件子句需要建立索引