MySQL InnoDB索引设计策略

一、索引设计原则

在MySQL中常用的B+树索引分为聚簇索引和辅助索引，关于辅助索引，虽然个数没有限制（聚簇索引一个表只能由一个），但可以想想一下，任何事物都是有两面性的：建立索引，是为了提高查询性能，但这是以降低写入性能为代价的。因为所有索引，在表需要写入数据时，都需要去维护索引数据以保证所有索引都是最新的、最准确的，所以可想而知，索引越多，写入性能越差，对索引上锁的时间也会越长（由于InnoDB是索引组织表，锁都是建立在索引之上）。更严重的是，如果有唯一索引，为了保证唯一索引的这个特性，每次修改或插入数据时都会去检查一下唯一性（产生共享锁），在RR隔离级别下，经常会造成死锁。所以在建立索引时一定要仔细权衡，建立出来的索引要个个为精，个个有用，这样才能保证在最大程度提高查询性能的情况下，最小程度地影响对表的修改。

1.1 索引创建原则

最左前缀匹配原则

非常重要的原则，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。所以这种情况在某些特定情况下加索引还是有效果的。

另外区分度低的字段在组合索引中使用是没有问题的，但一般区分度低的一般放组合索引的最右边。

计算列里无法使用索引

索引列不能参与计算，保持列“干净”。比如from_unixtime(create_time) = ’2017-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2017-05-29’)。

尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。这样可以节省没必要的索引创建。

多利用覆盖索引

MySQL利用B+树索引检索数据的时候，如果不是基于聚簇索引，或者说如果不是基于主键的检索，那么即便是SQL语句能够利用索引，但索引返回的信息也只是所需结果行的主键值，要取得全部数据，还需要通过这些主键值重新导数据文件里再做一次检索操作，这样就需要额外的IO，降低了查询效率。如果能优化SQL或索引，让MySQL只需要通过索引就可以返回所需的数据，而不必回表，从而提升效率，这就是覆盖索引。简单说就是返回字段就是where条件字段，比如select name from table where name=‘’;这样的。

组合索引等号条件字段在前

在创建组合索引时，比如“a>=xx and b=xx”查询，建立索引时应该为(b,a)，根据索引排序特性，这样扫描的数据就会越少。如果查询为“a=xx and b=xx”，建立组合索引时，谁的选择性高就把谁放在索引前面，这样扫描的数据也会少。

负向条件查询不能很好使用索引

负向条件查询默认是不能很好的使用索引，基本都是索引扫描。比如“!=”、“not in”、“not exists”都不是好习惯。

前导模糊查询不能使用索引

假如有这样一列code的值为’AAA’，’AAB’，’BAA’，’BAB’ ；如果where code like ‘%AB’条件，由于前面是模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like ‘A% ‘，就可以查找CODE中A开头的CODE的位置，当碰到B开头的数据时，就可以停止查找了，因为后面的数据一定不满足要求，这样就可以利用索引了。

表字段字符集不同时不能使用索引

当进行多表join时，如果进行比较的条件字段字符集不同则可能不会使用索引，因为不同的字符集类型其比较规则不同。这里为什么使用可能不会使用索引呢？因为就算字符集不同时要以驱动表为主，比如驱动表字符集为utf8，而被驱动表字符集为utf8mb4时，这时就无法使用索引。但是当驱动表字符集为utf8mb4时，被驱动表为utf8时，这时是可以使用索引的，因为utf8mb4字符集兼容utf8。

索引不存储NULL值

更准确的说，单列索引不存储NULL值，复合索引不存储全为NULL的值。如果允许为NULL，可能会得到“不符合预期”的结果集，比如“select count(*) from user where name != “dkey””的查询，如果允许name字段为NULL，因为索引不存储NULL值，所以结果集中不会统计这些记录。

另外由于索引不能存储NULL，所以对这列采用IS NULL条件时，因为索引上根本没NULL值，不能利用到索引，只能全表扫描。

为什么索引列不能存NULL值？

将索引列值进行建树时，其中必然涉及到诸多的比较操作。NULL值的特殊性就在于参与的运算大多取值为NULL。这样的话，NULL值实际上是不能参与进建索引的过程。也就是说，NULL值不会像其他取值一样出现在索引树的叶子节点上。

强制类型转换不能使用索引

强制类型转换有一个名词叫“隐式转换”，简单来说就是查询条件值跟查询字段数据类型不匹配。比如：select * from user where phone = ‘xx’查询，条件查询值类型为字符型，但是phone字段数据类型为数值型，此时就发送了所谓的隐式转换，故不会使用索引。

索引并非越多越好

当建立索引之后，每一次的写入数据随之带来的是需要索引的变化，也就是增加写操作。

数据量不多不需要建立索引

由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。当然具体还要视情况而为。另外当要扫描的数据为总数据的大概30%左右时MySQL优化器可能也不会使用索引，会直接全表扫描。

1.2 索引创建误区

对于懂点数据库的人来说，基本都明白主键索引（primary key）、唯一索引（unique index）、普通索引（inde）与主键约束（primary key）、唯一键约束（unique key）的区别，这里说的索引都是Btree索引。

普通索引是MySQL最基本的B+树索引类型，它没有任何限制。而唯一索引它与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许为NULL。如果是联合唯一索引，则列值的组合必须唯一。

这里要说明一点，唯一键约束和主键约束创建时都会默认隐式创建同名的唯一索引，以键名为索引名。唯一键约束索引列的值必须唯一，但允许为NULL。而主键约束索引不允许有空值，且在一张表中只允许有一个主键约束，但允许存在复合主键索引，也就是两个字符共同组成一个主键索引，两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。

唯一键约束和主键约束与唯一索引的区别是，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。但你可以先创建唯一索引，后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。

所以经常会碰到研发同学写一些冗余索引，最常见的就是下面三种：

创建主键后，还对主键字段创建一个唯一索引。
创建唯一索引后，还对唯一索引字段创建一个普通索引。
创建组合索引a,b后，还对a字段创建单列索引。

冗余索引只有缺点没有优点，缺点如下：

占用磁盘空间，导致备份变慢。
导致DML操作缓慢，MySQL B+树索引是高度平衡的，每次DML操作都需要更新索引。
多余的唯一索引会大大增加死锁的产生。
会导致查询变慢，特定情况下会导致MySQL优化器选择错误的路径，无法有效利用好的索引。

二、索引使用规则

在以下几种情况下MySQL在查询中即使有索引也不会去使用（参照上面索引创建原则）：

1．在多列索引，查询条件中用的不是最左列，那么此时是不会使用索引。

2．like查询时%出现在第一位也不会使用索引。

3．where条件中有or、<>、not int、not exists可能也不会使用索引。

4．索引列类型是字符串，可在查询中省略了引号（有时字符串类型列也会存储数字），当做int型查询，此时不会使用索引。反之int型查询加引号也一样。

5．如果MySQL优化器估计使用全表扫描比使用索引快，它也不会使用索引。

6. 多表Join时，条件字符集不同也不会使用索引。

三、索引优化基准

合理的索引设计能够有效加速数据库的访问，提高查询的执行效率，减少用户查询对服务端的资源消耗。但是不合理的、低效的、冗余的甚至无效的索引不仅无法起到加速查询的效果，反而会影响数据库的插入、更新性能，甚至是数据库的高可用方案能否生效。

主键索引缺失

由于MySQL默认存储引擎InnoDB（MySQL 5.6版本以上）使用的是聚簇索引表设计，这就要求所有的表必须包含一个主键，所有的数据记录按照主键次序构建B+树。如果用户在创建表时显式指定主键，则数据库会使用用户指定的主键构建B+树，但是如果用户没有显式指定主键，同时也没有创建任何唯一键索引，InnoDB为了确保每张表至少包含一个主键，则默认会为用户生成一个“隐含主键”，该主键对用户不可见，甚至对于MySQL Server层的binlog也不可见。binlog是连接MySQL主从复制节点的纽带，所有主节点的更新都是通过binlog传递给从节点的，一旦binlog中没有更新记录的主键ID，这就会导致基于Row格式的binlog在从节点执行时，无法唯一确定一条记录，只能通过全表扫描来进行匹配，大幅降低了从机的执行效率，造成复制延迟。如果是高可用故障切换的从节点，会导致切换的时间大幅增加，甚至会导致高可用机制失效。如果是实现读写分离的只读从节点，则会导致应用读到的数据可能是很久以前的旧数据。所以我们建议使用InnoDB存储引擎的MySQL用户在创建表时，必须显式指定主键。

主键索引与业务相关

如果用户在创建表时指定的主键与业务相关，可能会被频繁的更新，这样会引起MySQL数据库的InnoDB存储引擎进行频繁的节点合并和分裂，造成大量额外的系统IO开销，影响数据库的插入和更新性能。我们推荐开发者在创建表时指定与业务无关的自增字段作为主键，这样不仅会提高按时间序插入的性能（顺序写入硬盘），同时也可以提高按插入时间范围检索的查询效率。

冗余索引

如果一个索引涉及的字段属性包含另外一个索引涉及的字段属性，同时两个索引字段顺序一致，且两个索引的首字段属性相同，则可以认为涉及字段少的索引为冗余索引。在MySQL 5.7推出sys库之前，我们可以通过percona的工具pt-duplicate-key-checker来完成对冗余索引的检查，在MySQL 5.7中，我们可以通过sys库schema_redundant_indexes表来完成。

mysql> select * from sys.schema_redundant_indexes\G
*************************** 1. row ***************************
              table_schema: kupai
                table_name: bid_feed
      redundant_index_name: index_feed_id
   redundant_index_columns: feed_id
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: feed_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `kupai`.`bid_feed` DROP INDEX `index_feed_id`

mysql> select * from sys.schema_redundant_indexes\G

*************************** 1. row ***************************

table_schema: kupai

table_name: bid_feed

redundant_index_name: index_feed_id

redundant_index_columns: feed_id

redundant_index_non_unique: 1

dominant_index_name: PRIMARY

dominant_index_columns: feed_id

dominant_index_non_unique: 0

subpart_exists: 0

sql_drop_index: ALTER TABLE `kupai`.`bid_feed` DROP INDEX `index_feed_id`

低效索引

索引的作用在于通过索引，查询能够扫描更少的记录。数据库中的记录在索引字段区分度越高，扫描的记录数就越少，执行的效率就越高。如果数据库表中的记录在索引字段区分度不大，索引对记录的筛选结果就不明显，索引就无法起到加速查询的作用。通过数据库记录在索引字段的区分度，我们可以衡量索引的执行效率。MySQL系统库mysql库下，innodb_index_stats表的stat_value字段，记录了某张表在某个索引的不同取值的记录个数，innodb_table_stats表的n_rows字段记录了某张表的总记录数，二者相除，即可得到数据库记录在某个索引的区分度，越接近1，表示区分度越高，低于0.1，则说明区分度较差，开发者应该重新评估SQL语句涉及的字段，选择区分度高的多个字段创建索引，通过运行下面的SQL语句，就可以计算每张表的索引区分度。

SELECT i.database_name AS `db`,
       i.table_name AS `table`,
       i.index_name AS `index_name`,
       i.stat_description AS `cols`,
       i.stat_value AS `defferRows`,
       t.n_rows AS `ROWS`,
       ROUND(((i.stat_value / IFNULL(IF(t.n_rows < i.stat_value,i.stat_value,t.n_rows),0.01))),2) AS sel_persent
FROM mysql.innodb_index_stats i INNER JOIN mysql.innodb_table_stats t 
ON i.database_name = t.database_name AND i.table_name= t.table_name 
WHERE i.index_name != 'PRIMARY' AND i.stat_name LIKE '%n_diff_pfx%';