接上文: 第五章 创建高性能的索引(上)
覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为: 覆盖索引. 覆盖索引的好处有：
- 索引条目远小于数据行大小，能够极大地提高性能，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量
- 因为索引是按照值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少的多
- 如果不覆盖索引，则会产生回表查询， 先定位主键值，再定位行记录，它的性能较低
当发起一个被索引覆盖的查询时, 在EXPLAIN的Extra列可以看到'Using index'的信息.
小技巧: 延迟关联
select * from products where actor='SEAN CARREY' and title like '%APOLLO%';
上面的select * 包含了所有的列, 因此没办法使用覆盖索引, 回表需要扫描很多不满足条件的行. 但它的where条件是可以有索引可以覆盖的, 利用延迟关联(deferred join)的技巧, 建立(actor, title, prod_id)索引, 利用子查询的覆盖索引只过滤出满足条件的行:
select * from products join (select prod_id from products where actor='SEAN CARREY' and title like '%APOLLO%')as t1 on (t1.prod_id=products.prod_id);
分页查询时这个技巧常常被使用:
-- 索引：(threa_id, deleted)
select * from t join (select id
from t where thread_id = 5616385 and deleted = 0
order by id limit 50000, 10) t1 on t.id=t1.id;
使用索引扫描来排序
MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描
如果EXPLAIN出来的type列的值为index，则说明使用了索引扫描排序
只有当索引的列顺序和order by子句的顺序完全一致, 且所有列的排序方向(倒序或正序)都一样时, MySQL才能使用索引来对结果排序. 当关联多张表, 则只有当Order by子句引用的字段全部为第一个表时, 才能使用索引来排序. 且Order By子句要满足索引的最左前缀的要求.
下面这个例子, where子句的前缀列是范围时, 也无法使用索引扫描排序:
-- 有索引(rental_date, inventory_id, customer_id)
... where rental_date='2005-05-25' and inventory_id in (1,2) order by customer_id.
冗余和重复索引
重复索引：
MySQL允许在相同列上创建多个索引，但这样需要单独维护重复的索引，并且优化查询的时候也需要逐个进行考虑，会影响性能，应该避免这么做.
冗余索引
如果已经创建了索引（A, B），在创建索引（A），那么就是冗余索引，因为它只是前一个索引的前缀, 如果再创建(B, A), 则不是冗余索引.
冗余索引通常发生在表添加新索引的时候。如增加一个新的索引(A, B)，而没有扩展已有索引(A)，导致(A)成为冗余索引。或者将索引扩展为(A, 主键ID)，对InnoDB来说，主键已经包含在二级索引中了，因此也是冗余的.
解决方法是: drop掉重复和冗余索引即可.
索引和锁
索引可以让查询锁定更少的行. nnoDB只有在访问行的时候才会对其加锁, 而索引能够减少InnoDB访问的行数, 从而减少锁的数量.
但这只有当InnoDB在存储引擎能够过滤掉不需要的行时才有效，如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层之后，MySQL服务器才能应用Where子句，这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。
Explain时Extra列的'using where'的意思是: MySQL服务器将存储引擎返回行以后再应用where过滤条件.
InnoDB的行锁是建立在索引的基础之上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引.
索引案例学习
-
索引排序和索引查询经常有矛盾:
如果使用某个索引进行范围查询, 就无法再使用另一个索引(或该索引的后续字段)进行排序了. -
范围条件查询和等值条件查询有区别:
对于范围条件查询, MySQL无法再使用范围列后面的其他索引了, 而对于"多个等值条件查询"则没有这个限制.
select actor_id from actor where actor_id>45; -- 范围查询
select actor_id from actor where actor_id in (1, 4, 99); -- 等值查询
优化排序
-- 索引(sex, rating)
select <cols> from profiles where sex='M' order by rating limit 10;
上面的排序用到了索引, 速度是很快的. 但是当翻页时, 靠后的查询仍然会很慢:
-- 索引(sex, rating)
select <cols> from profiles where sex='M' order by rating limit 100000, 10;
原因是: MySQL需要每个满足条件的都回表取到行数据, 然后丢弃. 这样会丢弃前面大量不需要的行.
这时可以使用延迟关联的技巧, 通过覆盖索引查询返回需要的主键, 再根据这些主键关联原表获得所需要的行:
-- 索引(sex, rating)
select <cols> from profiles inner join
(select id from profiles where sex='M' order by rating limit 100000, 10)
as x using(id);
维护索引和表
即使用正确的类型创建了表并加上了合适的索引后，还需要维护表和索引来确保它们正常工作，目的如下：
- 找到并修复损坏的表
- 维护准确的索引统计信息
- 减少碎片
找到并修复损坏的表
可以通过CHECK TABLE检查是否发生了表错误
可以用REPAIR TABLE或者一个不作任何操作的ALTER操作来修复表
更新索引统计信息
MySQL的查询优化器会通过2个API来了解存储引擎的索引值的分布信息, 以决定如何使用索引:
- records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录
- info()，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）
InnoDB会在表首次打开, 或者执行analyze table, 抑或表的大小发生非常大的变化时,计算索引的统计信息.
查看索引或表统计信息sql语句:
Select * from information_schema.statistics where table_name='actor' and table_schema='sakila’;
show index from sakila.actor;
show table status from sakila where name='actor';
注意: 查看索引统计信息可能会导致统计信息的更新, 造成性能问题.
减少索引和数据的碎片
B-Tree索引可能导致碎片化，会导致查询效率降低。有三类数据碎片
- 行碎片：数据行被存储到多个地方的多个片段中
- 行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的
- 剩余空间碎片化：数据也中有大量的空余空间
对于MyISAM表，三类碎片都可能发生，InnoDB不会出现短小的行碎片.
下面三种方式都可以消除碎片化:
- OPTIMIZE TABLE
- 导入导出数据
- 不做任何操作的ALTER TABLE(标准版MySQL该方法只会消除聚簇索引的碎片化, 可以先删除所有索引, 再alter table, 再重建索引来消除索引的碎片化)
总结
选择索引以及利用索引查询时的三个原则：
- 单行访问是很慢的. 最好读取的块中包含尽可能多需要的行，使用索引可以创建位置引用以提升效率.
- 按顺序访问范围数据是很快的，原因如下：
- 顺序I/O不需要多次磁盘寻道，比随机I/O快
- 如果服务器能够按顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了
- 索引覆盖查询是很快的, 若一个索引包含了查询需要的所有列, 那就不需要再回表查询, 这就避免了大量的单行访问, 而第1点已经写明单行访问是很慢的.
现实使用中，很难做到每一个查询都有完美的索引，这时候需要根据需求有所取舍地创建合适的索引，而非根据惯例一刀切.
如何判断系统中的索引是否合理? 按响应时间对查询做分析, 找出消耗最长时间的查询或给服务器带来最大压力的查询, 然后检查这些查询的schema, SQL和索引结构.
网友评论