一.基础
索引在存储引擎实现,而不是服务器实现。没有统一的索引标准,支持同一索引的不同引擎,底层实现可能也是不同的。
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;无法使用前缀索引进行ORDER BY 和GROUP BY,也无法进行覆盖扫描;
- 可以把字符串反转存储,从而使用后缀索引;
- 多列索引:在多个列上创建独立的单独索引,并不能有效提升查询效率;若WHERE语句中有AND或OR或两者都有(条件中使用的均是独立索引),MySQL会使用索引合并。
- 选择合适的索引列顺序:
- 聚簇索引:不是单独的索引类型,而是数据存储方式。数据行和相邻的键值紧密地存储在一起;一个表只能有一个聚簇索引,因为无法把数据行同时放在两个地方;不是所有引擎都支持聚簇索引,InnoDB支持,通过主键聚集数据,若无主键选择一个唯一非空的索引代替,若无这样的索引会隐式定义一个主键;此索引无法单独创建,有引擎自动创建;
- 覆盖索引:一个索引包含所有查询的字段值,则为覆盖索引;
- 使用索引扫描来做排序:
ALTER TABLE table1 ADD key(field1(5))
四.维护索引和表
检测表是否损坏
CHECK TABLE table_name
尝试修复
REPAIR TABLE table_name
更新索引统计信息
查询优化器通过records_in_range()和info()两个API了解存储引擎的索引值分布信息,以决定如何使用索引。
重新生成统计信息
ANALYZE TABLE table_name
减少索引和数据的碎片
- 行碎片:数据行被存储在多个地方的片段中。
- 行间碎片:逻辑上顺序的页,或行在磁盘上不是顺序存储的。对全表扫描和聚簇索引扫描之类的操作有很大影响。
- 剩余空间碎片:数据页中有大量的剩余空间。
OPTIMIZE TABLE table_name
对于不支持OPTIMIZE TABLE的引擎,可以通过ALTER TABLE将表引擎修改为当前引擎来整理表。
ALTER TABLE table_name ENGINE=engine_name