高性能mysql学习笔记之----第五章：创建高性能索引

1、索引是对查询性能优化最有效的手段之一

2、如果没有特别指明类型，那多半说的是B-Tree索引，大多数mysql引擎都支持这种索引

3、MyISAM使用前缀压缩技术使得索引更小，但InnoDB按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行

4、B-Tree对索引是顺序组织存储的，适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据左前缀的查找

5、B-Tree索引的限制：如果不是按照索引的最左列开始查找，则无法使用索引；多列组成的索引不能跳过索引中的列；如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

6、哈希索引：基于哈希表实现，访问哈希索引的数据非常快；只有精确匹配索引所有列的查询才有效，即不支持部分索引列查找；因为不是按照索引值顺序存储的，所以也就无法用于排序，只支持等值比较查询，也不支持任何范围查询；

7、如果存储引擎不支持哈希索引可以通过新增一个列来存对应列的哈希值来做自定义哈希索引，这样实现的缺陷是需要维护哈希值，可以手动维护也可以使用触发器实现

8、还有全文索引、空间数据索引（R-Tree）MyISAM支持空间数据索引

9、索引的优点：索引大大减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；索引可以将随机I/O变为顺序I/O

10、对于非常小得表大部分情况下简单的全表扫描更高效；对于中到大型的表索引就非常有效；但对于特大型的表，建立和使用索引的代价就随之增长，这种情况下可以使用分区技术

11、始终将索引列单独放到比较符的一侧

12、当服务器对多个索引做相交操作时（通常有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单独索引

13、多列索引的顺序至关重要，当不需要考虑排序和分组时将选择性最高的列放在前面通常是很好的选择

14、InnoDB的聚簇索引实际上在同一个结构中报错了B-Tree索引和数据行，InnoDB将通过主键聚集数据，主键的大小会影响整个表索引的大小，如果没有定义主键则会选择一个唯一的非空索引代替，如果没有这样的索引会隐式定义一个主键来作为聚簇索引

15、聚簇索引最大限度的提高了I/O密集型应用的性能；插入速度严重依赖于插入顺序，按照主键顺序插入式加载数据到InnoDB表中速度最快的方式；更新聚簇索引列的代价很高；聚餐索引可能会导致全表扫描变慢，尤其是行比较稀疏的时候

16、 最好避免随机的聚簇索引，特别是对于I/O密集型应用：这样会导致大量的随机I/O，因为是乱象的最终数据会有碎片，使用InnoDB时应该尽可能高按主键岁序插入数据

17、顺序的主键什么时候会造成更坏的结果：对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用

18、覆盖索引：如果一个索引包含或者说覆盖所有需要查询字段的值，我们就称之为覆盖索引，覆盖索引是非常有用的，能够极大的提高吸能，因为只需要扫描索引无需回表查询；explain出来extra列值为using index 则说明使用到了覆盖索引；mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖；由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用，但是只有B-Tree可以左覆盖索引

19、有的情况如果无法做覆盖索引可以利用延迟关联通过连表在子查询中用覆盖索引把主键取出来，再主查询中在取对应列的值，延迟关联适用于子查询可以查出比较少的数据的情况，但随着mysql5.6版本的出现，延迟关联这种技巧就没这么必要了

20、使用索引扫描排序：如果explain出来的type列值为‘index’,则说明mysql使用了索引扫描来做排序；扫描索引本身是很快的，但是如果不能使用覆盖索引读取数据的速度通常要比全表扫描慢。。。尤其是在I/O密集型的工作负载时；使用索引做排序的一个最重要的用法是当查询同时又order by和limit子句的时候

21、冗余索引：如果创建了（A,B）索引再创建索引（A）就是冗余索引，如果再创建（B,A）则不是冗余索引；大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引，因为增加新索引将会导致insert、update、delete等操作的速度变慢

22、尽可能将要做范围查询的列放到索引的最后面，以便优化器能使用尽可能多的索引列

23、应该避免多个范围条件

24、维护表有三个主要目的：找到并修复损坏的表；维护准确的索引统计信息；减少碎片

25、可以用check table来检查表是否发生损坏，repair table命令可以用来修复损坏的表

26、mysql的查询优化器会通过两个api来了解存储引擎的索引值的分布信息以决定如何使用索引，第一个api是records_in_range()，该api通过像存储引擎传入两个边界来获取范围大概有多少记录；第二个是info()，该接口返回各种类型的数据，包括索引的基数

27、B-Tree索引可能会碎片化，这会降低查询效率，碎片化的索引可能会以很差或者无序的方式存储在磁盘上，可以通过执行optimize table或者导出再导入的方式来重新整理数据

28、在选择索引和编写利用这些索引查询时，有如下三个原则始终需要记住：

1、当行访问时很慢的，最后读取的会中能包含尽可能多所需要的行

2、按顺序访问访问数据是很快的

3、索引覆盖查询时很快的