一、索引设计原则
在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。
详见本人简书文章：https://www.jianshu.com/p/bd9b53dbd497
索引不存储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）、普通索引（index）与主键约束（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 in、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系统库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%';
也可以在这个WHERE条件后面跟上t.database_name=’库名’ AND t.table_name=’表名’用于精确查找某个表的索引区分度。
无效索引
如果一个索引始终无法被查询使用，它的存在只会增加数据库的维护开销，开发者应该及时删除这些索引。通过MySQL 5.7 sys库schema_unused_indexes视图，可以查看当前实例哪些索引从没有被使用。
参考：http://www.ywnds.com/?p=7792
网友评论