索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。
在MySQL中,索引是在存储引擎层而不是服务器层实现的。所以没用统一的索引标准,不同存储引擎的索引工作方式并不相同。
B-Tree索引即使用B-Tree数据结构来存储数据。B-Tree通常意味着所有值都是按顺序存储的,并且每个叶子页到根的距离相同。存储引擎已不同的方式来使用B-Tree索引,性能也各不相同。
可以使用B-Tree索引的查询类型——全键值、键值范围和键前缀查找。其中键前缀查找只适用于根据最左前缀查找。
哈希索引基于哈希表实现,只有精确匹配索引的所有列的查询才有效。在MySQL中,只有Memory引擎显示支持哈希索引,这也是Memory引擎的默认索引类型。
对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,如果多个列的哈希码相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中 的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节,如 停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情,而不是简单 的WHERE条件匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突,全文索引适用于 MATCH AGAINST操作,而不是普通的WHERE条件操作。
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分 形树索引(fractal tree index),这是一类较新开发的数据结构,既有B-Tree的很多优点, 也避免了 B.Tree的一些缺点。如果通读完本章,可以看到很多关于InnoDB的主题,包 括聚簇索引、覆盖索引等。多数情况下,针对InnoDB的讨论也都适用于TokuDB。
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用,到目前 为止可以看到,根据创建索引的数据结构不同,索引也有一些其他的附加作用。总结下来索引的三大优点:
索引是最好的解决方案吗? 索引并不总是最好的工具。总的来说,只有当索引帮助存储引擎快速查找到记录带 来的好处大于其带来的额外工作时,索引才是有效的。对于非常小的表,大部分情 况下简单的全表扫描更高效。对于中到大型的表,索引就非常有效。但对于特大型的表,建立和使用索引的代价将随之增长。这种情况下,则需要一种技术可以直接区分出查询需要的一组数据,而不是一条记录一条记录地匹配。
索引列不能是表达式的一部分,也不能是函数的参数。
例如:SELECT actor_id FROM actor WHERE actor_id + 1 = 5; 或者:SELECT actor_id FROM actor WHERE f(actor_id) = 5;
有时候需要索引很长的字符列,这会让索引变得很大且很慢。此时可以有两个策略,一个是自定义哈希索引,另一个就是前缀索引。
为多列创建合适的索引
聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优点:
聚簇索引的缺点:
通常开发人员会根据查询的where条件来创建合适的索引,但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据,那么就不用回表查询了,也就是说这种索引包含(亦称覆盖)所有需要查询的字段的值,我们称这种索引为覆盖索引
MySQL有两种方式可以生成有序结果:通过排序操作;按照索引顺序扫描。
维护表有三个目的:找到并修复损坏的表;维护准确的索引统计信息;减少碎片
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息,已决定如何使用索引信息。
ALTER TABLE <table> ENGINE=<engine>;
索引是一个非常复杂的话题! MySQL和存储引擎访问数据的方式, 加上索引的特性,使得索引成为一个影响数据访问的有力而灵活的工作(无论数据是在 磁盘中还是在内存中)。
在MySQL中,大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊 的目的。如果在合适的场景中使用索引,将大大提高査询的响应时间。
如果一个査询无法从所有可能的索引中获益,则应该看看是否可以创建一个更合适的索 引来提升性能。如果不行,也可以看看是否可以重写该査询,将其转化成一个能够高效 利用现有索引或者新创建索引的査询。这也是下一章要介绍的内容。
参考:
《高性能 MySQL 第三版》