MySQL学习笔记--创建高性能索引

一.基础

索引在存储引擎实现，而不是服务器实现。没有统一的索引标准，支持同一索引的不同引擎，底层实现可能也是不同的。

B-Tree索引

大多数索引都是B-Tree索引。每一个叶子节点包含指向下一个页子节点的指针。使用B-Tree这个术语，是因为MySQL在CREATE TABLE和其他语句中使用此关键字。不同引擎底层实现是不同的，NDB使用的是T-Tree数据结构，InnoDB使用的是B+Tree数据结构。存储引擎以不同的方式使用B-Tree。MyISAM使用前缀压缩技术，使索引更小，通过数据物理位置索引行；InnoDB按照原数据格式进行存储，通过主键引用被索引行。B-Tree通常意味数据按顺序存储；对索引列是顺序组织存储的，所以适合查找范围数据；

适用的查询：全值匹配；匹配最左前缀；匹配列前缀；匹配范围值；精确匹配某一列并范围匹配另一列；只访问索引的查询；还可用于查询中的ORDER BY；

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

若在A、B、C三列上创建B-Tree索引，则A、AB、ABC、AC(效果同A)可以使用索引；若A为范围查询，则AB\AC\ABC中A右边的列无法使用索引；

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才会生效。对于每一行数据，搜索引擎会为其生成一个哈希码，索引将哈希码存储在索引中，同时在哈希表中保存指向每行数据的指针。只有Memory显式支持Hash索引；索引自身只存储对应的哈希值，所以结构十分紧凑，查找速度很快；

特点：哈希索引只包含哈希值和行指针，不存储字段值，不能用索引中值避免读取行；索引数据不是按照索引值排序的，无法用于排序；不支持部分索引匹配查找；只支持等值比较，=、in、<=>，不支持任何范围查询，>、<等；可能出现哈希冲突，需要遍历链表中所有行指针，导致查询速度变慢；若出现哈希冲突，索引维护成本较高；

若存储引擎不支持哈希索引，可创建伪哈希索引。例，若需要根据URL查找，而URL较长，可新建列URL_CRC=CRC32(URL)，可以使用触发器维护此列。为了避免哈希冲突，同时比较原值；

空间数据索引

MyISAM支持空间数据索引，可以用作存储地理数据。无需前缀查询，会从所有维度索引数据；可以使用任意维度来组合查询；

全文索引

适用于MATCH AGAINST操作

二.优点

• 减少服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机I/O变为顺序I/O

非常小的表，全表扫描更有效；中大型表索引更有效；特大型表，建立和使用索引的代价也增大，此时可以进行分区、分表。

三.索引策略

1. 独立的列：索引列不能是表达式的一部分，也不能是函数参数。
2. 前缀索引和索引选择性：若列为很长字符列，可以索引开始的部分字符，但会降低索引的选择性；索引的选择性指不重复的索引值和数据表总数的比值，选择性高的索引可以在查询时滤去更多行；唯一索引的选择性是1；无法使用前缀索引进行ORDER BY 和GROUP BY，也无法进行覆盖扫描；
3. 可以把字符串反转存储，从而使用后缀索引；
4. 多列索引：在多个列上创建独立的单独索引，并不能有效提升查询效率；若WHERE语句中有AND或OR或两者都有（条件中使用的均是独立索引），MySQL会使用索引合并。
5. 选择合适的索引列顺序：
6. 聚簇索引：不是单独的索引类型，而是数据存储方式。数据行和相邻的键值紧密地存储在一起；一个表只能有一个聚簇索引，因为无法把数据行同时放在两个地方；不是所有引擎都支持聚簇索引，InnoDB支持，通过主键聚集数据，若无主键选择一个唯一非空的索引代替，若无这样的索引会隐式定义一个主键；此索引无法单独创建，有引擎自动创建；
7. 覆盖索引：一个索引包含所有查询的字段值，则为覆盖索引；
8. 使用索引扫描来做排序：
   

在一个字段的前缀添加索引，5为前缀长度，此长度通过比较选择性值可得出

ALTER TABLE table1 ADD key(field1(5))

四.维护索引和表

检测表是否损坏

CHECK TABLE table_name

尝试修复

REPAIR TABLE table_name

更新索引统计信息

       查询优化器通过records_in_range()和info()两个API了解存储引擎的索引值分布信息，以决定如何使用索引。

 重新生成统计信息

ANALYZE TABLE table_name

减少索引和数据的碎片

• 行碎片:数据行被存储在多个地方的片段中。
• 行间碎片：逻辑上顺序的页，或行在磁盘上不是顺序存储的。对全表扫描和聚簇索引扫描之类的操作有很大影响。
• 剩余空间碎片：数据页中有大量的剩余空间。

对于MyISAM，三种碎片都可能出现。InnoDB不会出现短小的行碎片。可以通过OPTIMIZE TABLE或导出再导入的方式整理数据。

OPTIMIZE TABLE table_name

对于不支持OPTIMIZE TABLE的引擎，可以通过ALTER TABLE将表引擎修改为当前引擎来整理表。

ALTER TABLE table_name ENGINE=engine_name