mysql索引之九：重复索引和冗余索引，索引和锁
索引的创建对数据库的查询性能有很好的优化，但是不恰当的索引使用也会给数据库带来性能上的影响。
一、重复索引和冗余索引
SQL索引创建需要注意的情况：
- 重复索引：一个字段上建立多个索引
- 冗余索引：指多个索引的前缀列相同，或者在联合索引中包含了主键的索引 。
mysql允许在相同列上创建多个索引，无论是有意还是无意，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复所以，发现以后也应该立即删除。但，在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。
示例：
下面的test表中创建了3个重复索引，在ID列上建了主键、唯一索引、普通索引。
create table test( id int not null PRIMARY KEY, A int not null, B int not null, UNIQUE(ID), INDEX(ID) )engine=innodb;
示例：冗余索引：这个和重复索引有一些不同，如果创建了索引（a,b），再创建索引（a）就是冗余索引，因为这只是前面一个索引的前缀索引，因此（a,b）也可以当作(a)来使用，但是（b,a）就不是冗余索引，索引(b)也不是，因为b不是索引（a,b）的最左前缀列，另外，其他不同类型的索引在相同列上创建（如哈希索引和全文索引）不会是btree索引的冗余索引。
另外：对于二级索引（a,id），id是主键，对于innodb来说，主键列已经包含在二级索引中了，所以这个也是冗余索引。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引，但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。如：如果在整数列上有一个索引，现在需要额外增加一个很长的varchar列来扩展该索引，那么性可能会急剧下降，特别是有查询把这个索引当作覆盖索引，或者这是myisam表并且有很多范围查询的时候(由于myisam的前缀压缩)。
如：表userinfo，myisam引擎，有100W行记录，每个state_id值大概2W行，在state_id列有一个索引对下面的查询有用：如：select count(*) from userinfo where state_id=5;测试每秒115次QPS
对于下面的查询这个state_id列的索引就不太顶用了，每秒QPS是10次
select state_id,city,address from userinfo where state_id=5;
如果把state_id索引扩展为(state_id,city,address)，那么第二个查询的性能更快了，但是第一个查询却变慢了，如果要两个查询都快，那么就必须要把state_id列索引进行冗余了。但如果是innodb表，不冗余state_id列索引对第一个查询的影响并不明显，因为innodb没有使用索引压缩，myisam和innmodb表使用不同的索引策略的select查询的qps测试结果（以下测试数据仅供参考）：
只有state_id列索引 只有state_id_2索引 同时有两个索引
myisam,第一个查询 114.96 25.40 112.19
myisam,第二个查询 9.97 16.34 16.37
innodb,第一个查询 108.55 100.33 107.97
innodb,第二个查询 12.12 28.04 28.06
从上图中可以看出，两个索引都有的时候，缺点是成本更高，下面是在不同的索引策略时插入innodb和myisam表100W行数据的速度（以下测试数据仅供参考）：
只有state_id列索引 同时有两个索引
innodb,对有两个索引都有足够的内容的时候 80秒 136秒
myisam,只有一个索引有足够的内容的时候 72秒 470秒
可以看到，不论什么引擎，索引越多，插入速度越慢，特别是新增索引后导致达到了内存瓶颈的时候。解决冗余索引和重复索引的方法很简单，删除这些索引就可以了，但首先要做的是找出这样的索引，可以通过一些复杂的访问information_schema表的查询来找，不过还有两个更简单的方法，使用：shlomi noach的common_schema中的一些视图来定位，也可以使用percona toolkit中的pt-dupulicate-key-checker工具，该工具通过分析表结构来找出冗余和重复的索引，对于大型服务器来说，使用外部的工具更合适，如果服务器上有大量的数据或者大量的表，查询information_schema表可能会导致性能问题。建议使用pt-dupulicate-key-checker工具。
在删除索引的时候要非常小心：
如果在innodb引擎表上有where a=5 order by id 这样的查询，那么索引（a）就会很有用，索引(a,b)实际上是（a,b,id）索引，这个索引对于where a=5 order by id 这样的查询就无法使用索引做排序，而只能使用文件排序了。所以，建议使用percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。
2. 查找“未使用的索引”的方法
除了冗余索引和重复索引，可能还会有一些服务器永远不使用的索引，这样的索引完全是累赘，建议考虑删除，有两个工具可以帮助定位未使用的索引：
A：在percona server或者mariadb中先打开userstat=ON服务器变量，默认是关闭的，然后让服务器运行一段时间，再通过查询information_schema.index_statistics就能查到每个索引的使用频率。
B：使用percona toolkit中的pt-index-usage工具，该工具可以读取查询日志，并对日志中的每个查询进行explain操作，然后打印出关羽索引和查询的报告，这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划，如：在某些情况下有些类似的查询的执行方式不一样，这可以帮助定位到那些偶尔服务器质量差的查询，该工具也可以将结果写入到mysql的表中，方便查询结果。
3. MySQL索引和锁
索引和锁可以让查询锁定更少的行。如果你的查询从不访问那些不需要访问的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然innodb的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销，其次，锁定超过需要的行会增加锁竞争，并减少并发性。
innodb只有在访问行的时候才会对其加锁，而索引能够减少innodb访问的行数，从而减少锁的数量。但只有当innodb在存储引擎能够过滤掉不需要的行时才有效。如果索引无法过滤掉无效的行，那么在innodb检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时候，已经无法避免锁定行了：inno代表可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，innodb只有在事务提交后才能释放锁。
通过下面的例子，很好的解释了这些情况
SELECT actor_id FROM actor WHERE actor_id < 5 AND actor_id<>1 FOR UPDATE;
这些表仅仅会返回2-4之间的行，但是实际上获取了1-4之间行的排他锁，innodb会锁住第一行，这是因为MySQL为该查询选择执行计划是索引范围扫描；换句话说，底层存储引擎操作的是“从索引的开头开始获取满足条件的actor_id<5的记录”服务器并没有告诉innodb可以过滤掉第一行的where条件。注意到explain的Extra列出现了“Using Where ” 这表示MySQL服务器将存储引擎返回行以后再应用where过滤条件。
下面的第二个查询就可以证明第一行确实已经被锁定，尽管第一个查询的结果中并没有这个第一行。保持第一个连接打开，然后开启第二个连接并执行如下语句：
SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE.
这个查询就会被挂起，直到第一个事务释放第一行的锁。这个行为对于基于语句的复制的正常运行来说是必要的。
就像这个例子显示的，即使使用了索引，innodb可能也会锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会很糟糕，MySQL会做全表扫描并锁定所有的行，而不管是不是需要。
关于innodb，索引和锁有一些很少有人知道的细节：innodb在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写），这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或非锁定查询要慢得多。
