MySQL索引原则

索引是帮助数据库高效获取数据的数据结构。MySQL默认使用Innodb存储引擎，Innodb使用B+Tree。

一.索引的优缺点

        优点：索引可以提高检索速度，减少扫描的数据量，避免排序和临时表，可以将随机IO变成顺序IO。

        缺点：降低表的更新速度（insert,update,delete），创建索引需占用磁盘空间，需维护成本。

二.索引类型

1. 普通索引   CREATE INDEX ....
2. 主键索引   PRIMARY KEY 特殊的唯一索引，不允许有空值。
3. 唯一索引   UNIQUE 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
4. 组合索引   联合索引的组成列的长度和不能大于3072bytes。
5. 全文索引   MySQL5.6及以后版本，Innodb支持全文索引。fulltext index  ；match(col)  against ('aaa');Innodb对全文索引的要求：默认长度大于等于3。
6. 前缀索引   index(col(10)) ;如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
                     例如communication.com_push_message_ps表的字段上requestId的索引，如果设置index(requestId(20))，可明确区分大部分该字段的值，这样既节省了磁盘空间，又达到了通过索引快速检索字段的目的。

三.索引的命名规则

1. 普通索引命名规则为IDX_表名_字段名，如果索引名称过长，列名可用首字母代替。
2. 唯一索引命名规则为UQ_表名_列名，如果索引名称过长，列名可用首字母代替。

四.创建索引的原则

1. 区分度高的列。
   区分度的计算公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1。
2. 优先使用短的列创建索引。
3. 覆盖索引：一颗索引树上能获取所有所需列的数据，无需回表，速度更快。
                     在user表创建了联合索引(name,tel),select name,tel from user where name = 'a';
                     应尽量减少查询字段，尽可能使用到覆盖索引查询，减少IO，提升性能。
4. 前缀索引： 对于超过20个字节长度的列上创建索引，应考虑前缀索引。（缺点：对这个列进行order by或group by时无法使用前缀索引）
5. 最左前缀匹配原则。  
   创建(a,b,c,d)顺序的组合索引 index_abcd ， 在查询时存储引擎会利用index_abcd 索引从左向右匹配，直到遇到范围查询(>、<、between、like)就停止匹配。比如where条件是  a = 1 and b = 2 and c > 3  and d = 4，mysql引擎会使用index_abcd 索引，先用a索引，再用b索引，停止在c索引，就无法利用d索引。优化方法是  建立(a,b,d,c)顺序的组合索引 （a,b,d的顺序可以任意调整）， 建索引时把范围查询c放在最后面。

五.适合使用索引的场景

1. where条件里的列。
2. 频繁进行排序或分组的列。
3. 频繁使用distinct查询的列。
4. 多表关联查询时，进行表连接的列。（join的列需字段类型相同，表的字符集也需相同）
5. 当sql语句返回的行数占整张表总行数<=5%,适合使用索引。

六.不适合使用索引的场景

1. 对于更新量非常大的表，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求，此时应尽量减少索引。
2. 对于表数据较少，通过索引检索速度比不上全表扫描的，不宜创建索引。
3. 区分度较低的列。
4. 扫描范围占整张表范围比例较大。
5. 不应对BLOB,TEXT等大字段创建索引，否则会占用过多的存储空间。

七.创建了索引无法使用索引的场景

1. 查询条件最左以通配符开始，例如where t_name like '%xxx'。
2. 在字段上进行数据运算，函数运算（包括隐式数据转换）。应把计算放到业务层而不是数据库层。
   比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，因为B+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)
3. 查询条件使用is null ，is not null。
4. 查询条件使用负向查询条件not，<>,!<,!>,!=,not in ，not like等。
   member_id 有索引，下面语句执行计划也不走索引。
   select * from fc_friend_circle_comment where member_id <> 1342374 
5. 在查询条件中or连接多个条件会导致索引失效，除非or的每个条件上都有索引。此时应改为两次查询，然后用union all连接。
6. 索引最多用于一个范围列，如果查询条件中有两个范围列，则无法全走索引。范围条件有<,<=,>,>=,between,in等。
7. 不能使用索引做排序的查询
   a.使用两种不同排序方向。但执行计划中会出现using filesort
   （member_id ，member_name 有索引）select * from fc_friend_circle_comment where member_id > 1342374 order by member_id asc,member_name desc；
   b.排序字段有不在索引中的列
      select * from fc_friend_circle_comment where member_id > 1342374 order by member_id ,update_time;
   c.where和order by中的列无法组合成索引的最左前缀
   MySQL8.0版本对此部分会有更好的支持。

八.索引相关名词解释

1. 索引下推（index condition pushdown 简称ICP）
   MySQL5.6推出索引下推的概念，用于优化查询。
   对于user_table表，我们现在有（username,age）联合索引  ，   select * from user_table where username like '张%' and age > 10
   如果没有索引下推，会忽略age这个字段，根据username进行查找，找到对应的结果后通过id一次次进行回表查询。
2. 索引合并优化（index_merge）
   MySQL5.0之后的版本引入索引合并优化。
   索引合并是把几个索引的范围扫描合并成一个。需要合并的索引必须是同一个表的，不能是多表的索引。
   对or语句求并集 SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"
   对and语句求交集 SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"

九.冗余索引

1. 主键字段不应再建索引。
2. 不宜过度使用索引，不应再针对当前索引覆盖的前导字段再创建索引。

十.存储引擎清理碎片方法

       碎片清理尽量选择在业务不繁忙阶段操作，清理的频率无需过高，例如一个月清理一次即可。

1. InnoDB存储引擎：alter table table_name engine = innodb;
2. Myisam存储引擎：optimize table table_name;