创建合理地索引能够提升数据库的查询效率，但是如果创建的索引不合理，不仅会降低数据库的插入、修改、删除的效率，而且会降低数据库的查询效率。其中最主要的是组合索引的创建和优化。

1、冗余和重复的索引

mysql允许在相同的列上创建多个索引，无论索引是否有实际的意义。且索引一旦被创建，就需要mysql单独维护重复的索引，且mysql优化器在优化查询的时候也需要对重复的索引重复进行计算，这样会影响mysql的性能。

重复的索引一般是指在相同的列上安装相同的顺序创建的相同类型的索引。

冗余的索引一般是因为存在多列索引使部分单列索引的存在没有了实际意义。如索引（A,B）与索引（A），那么索引（A)就是冗余的索引。以为索引（A,B）可以用来当做索引（A）使用。

一般不需要冗余索引，应该在尽量在已有索引的基础上进行扩展，而不创建新索引。但是有时候为了性能考虑需要创建新索引，如：在整数列上有一个索引，现在需要增加一个很长的varchar列来扩展所以，如果该索引是某个查询的覆盖索引或者它是MyISAM表且一个索引下有很多查询范围时，扩展该索引会是查询的效率大大降低-----因为新增的索引列是长varchar类型，它会是原有索引变得很大，这时再去遍历该索引时，就会比原来慢很多。

2、支持多过滤条件

一般我们会在where子句中使用比较频繁的列上创建索引，在列值差异比较大的情况下创建索引会使查询的效率更高。但是有些时候一些列的值比较固定，但查询中又经常用到该列进行筛选，如状态（status）、性别（sex）、年龄（age）、删除标识（is_del）等。这类信息查询使用的频率很高，在创建组合索引时，建议将这类列作为组合索引的前缀。也许你会觉得，根据过往经验，在这类选择性底的列上创建索引效率不高。下面是我们这么做的理由：

  1. 在这类使用频率很高，特别是像is_del（删除标识）在几乎所有的查询中都会使用的列。
  2. 组合索引加上这样的列并没有坏处，而对于没有用到该条件的查询，也可以用 “ is_del in （0,1）” 来让mysql选择该索引。这样查询就可以匹配最左前缀，并可以尽可能的使用覆盖索引。当前这样做仅限于字段值不太多，in () 列表不太长的情况。
  3. 上面提到的in（） 子句，可以将多个组合索引合并成一个组合索引。当然这仅限于两个组合索引相似，使用in() 子句的列的值的范围有限。如创建组合索引（is_del, sex, name）和 （is_del, set, age）两个索引中age和name两个字段有差异，这时可以将两个索引合并（is_del, sex,age,name）。根据索引的最左前缀原则，而且字段age的值有限，而name的值差异较大，这里就需要将age字段放在前面，在查询中如果没有使用age条件时，需要添加" age in (1,2,3....)" 子句。

注意：上面提到的用in子句来合并和使用组合索引的情况，有一定的局限性。因为没增加一条in子句，优化器需要做的组合都将以指数形式增加。如果in条件太多，或in的值过多都会降低查询性能。比如下面的查询

where name in ('a','b','c','d')
        and age in (20,21,22,23)
        and sex in (0,1)

这几个in条件的所有组合为：4*4*2 = 24种，执行计划需要检查where子句这24中组合。

3、避免多个范围条件查询

范围条件查询是指：age>10   、  created_time < 2020-12-1 ， 区别于等着查询：age = 20 、 name = 'zhou' 、color in ('white','blue','black') ,其中in() 条件相当于多个等值查询。

范围查询和多个等值查询，他们的查询效率不同： 

  • 范围查询，mysql无法再利用范围列后面的其他索引列
  • 多个等值查询，可以继续使用等值列后面的其他索引列

以下面的查询为例：

where color in ('white', 'black','blue')
        and created_time > '2020-12-1'
        and age between 20 and 30

对于这个查询，我们可以使用created_time 索引列或者age索引列，但不能同时使用他们。也许这里你会想到上面“支持多过滤条件”中提到的，使用in() 子句来同时使用两个索引列。但这里我们假设两个范围列都有无限个可能的值，即不能使用in() 子句代替。这时该怎么处理？？ 遗憾的是，mysql没有对于这种查询的优化处理方案。我们只能通过其他业务逻辑途径来改变其中一个范围查询条件来处理。 

4、索引维护

1、找到并修复损坏的表（corruption）

表损坏一般是因为系统崩溃或其他硬件问题、mysql本身缺陷或操作系统的问题导致表损坏和索引损坏。

索引损坏会导致查询但会错误的结果或莫名其妙的主键冲突问题，严重时可能导致数据库崩溃。使用 CHECK TABLE  命令通常能找出大多数的表和索引错误。使用 REPATR TABLE   命令，来修复损坏的表，但是并不是所有的引擎都支持该命令，这时可以考虑手动修改表的存储引擎。也可以借助一些存储引擎相关的离线工具， 如 myisamchk；或者将数据导出一份再重新导入，但如果损坏的是行数据而不是索引，这个方法就无效了。

2、减少索引和数据碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或无序的方式存储在磁盘上。尤其对于索引覆盖扫描效率影响极大。

表的数据存储也可能碎片化，表的数据存储碎片有三种类型：

  • 行碎片（Row fragmentation）: 数据行被存储在多个地方多个片段中
  • 行间碎片（Intra-row fragmentation）：逻辑上的顺序页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描、聚簇索引之类的操作影响较大，这类操作原本能从磁盘上顺序存储的数据中获益。
  • 剩余空间索引（Free space fragmentation）：数据页中有大量的空余空间，导致服务器读取大量不需要的数据。

对于MyISAM表，这三类碎片化都有可能；InnoDB表，不会出现短小的行碎片，它会移动短小的行碎片的并重写到一个片段中。

消除碎片空间的方法：

  1. OPTIMIZE TABLE 命令：对于大多数存储引擎都有效
  2. 导出再导入数据重新整理数据
  3. 对于MyISAM存储引擎，通过排序算法重建索引消除锁片
  4. 对于新版的InnoDB，可以通过先删除在创建索引的方式消除索引碎片
  5. 通过一个no-op操作，重建表： ALTER TABLE table_name ENGIN=engin_name , engin_name为当前表的存储引擎。这样虽然看似没有对表做任何操作，但可以消除索引碎片。且对于开启了expand_fast_index_creation参数的Percona Server，会同步消除表的碎片空间。

（内容摘自《高性能mysql》第5章 创建高性能索引）