本文整理自网络,侵删。
目录
- 一、索引基础
- 1. 索引的类型
- 1.1 B-Tree 索引
- 1.2 哈希索引
- 1.3 空间数据索引(R-Tree)
- 1.4 全文索引
- 二、索引的优缺点
- 三、高性能索引策略
- 1. 独立的列
- 2. 前缀索引
- 3. 多列索引
- 4. 合适的索引列顺序
- 5. 聚簇索引
- 6. 覆盖索引
- 三、查询性能优化
- 1. Explain 性能分析
- 1.1 id:表的读取顺序
- 1.2 select_type:查询操作类型
- 1.3 table:表的来源
- 1.4 type:访问类型
- 1.5 possible_key:可能用到的索引
- 1.6 key:实际使用的索引
- 1.7 key_len:索引使用字节数
- 1.8 ref:显示被使用的索引的具体信息
- 1.9 rows:被查询的行数
- 1.10 Extra:额外重要信息
- 总结
一、索引基础
1. 索引的类型
1.1 B-Tree 索引
大多数MySQL存储引擎默认使用的是B+树的索引,不同的存储引擎用不同的方式使用B+树索引,MyISAM使用前缀压缩技术使得索引更小,但是InnoDB则按照元数据格式进行存储;MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。
B树 和 B+ 树
B树:
B+树:
区别:
- B树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中
- 在 B树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而 B+树中每个记录 的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看 B树的性能好像要比 B+树好,而在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据, 这样每个节点可容纳的元素个数比 B树多,树高比 B树小,这样带来的好处是减少磁盘访问次数。尽管 B+树找到 一个记录所需的比较次数要比 B树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中 B+树的性能可能还会好些,而且 B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有 文件,一个表中的所有记录等),这也是很多数据库和文件系统使用 B+树的缘故
为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引?
- B+树的磁盘读写代价更低
- B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了
- B+树的查询效率更加稳定
- 由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当
- 由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当
为什么不用红黑树?
- B+树更少的查找次数
- 平衡树查找操作的时间复杂度和树高 h 相关,O(h)=O(logdN),其中 d 为每个节点的出度。
- 红黑树的出度为 2,而 B+树 的出度一般都非常大,所以红黑树的树高 h 很明显比 B+树 大非常多,查找的次数也就更多。
- B+树利用磁盘预读特性
- 为了减少磁盘 I/O 操作,磁盘往往不是严格按需读取,而是每次都会预读。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的磁盘旋转时间,速度会非常快。
- 操作系统一般将内存和磁盘分割成固定大小的块,每一块称为一页,内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性,相邻的节点也能够被预先载入
1.2 哈希索引
哈希索引基于哈希表实现,对于每一行数据,存储引擎会对所有的索引列计算一个哈希码,通过哈希码能以 O(1) 时间进行查找,但是无法用于排序与分组,并且只支持精确查找,无法用于部分查找和范围查找。
在MySQL 中,只有Memory引擎显式支持哈希索引
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的一些优点,比如快速的哈希查找。
1.3 空间数据索引(R-Tree)
MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。
1.4 全文索引
MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。
查找条件使用 MATCH AGAINST,而不是普通的 WHERE。全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
二、索引的优缺点
优点
- 索引大大减少了服务器需要扫描的数据量
- 通过索引可以帮助服务器避免排序和临时表,降低CPU消耗
- 可以将随机IO变为顺序IO,加快IO速度
缺点
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
三、高性能索引策略
1. 独立的列
如果MySQL查询的列不是独立的,就不会使用索引,“独立的列”指的是,索引列不能是表达式的一部分,也不能是函数的参数
例如
mysql> SELECT id, name FROM t_user WHERE id + 1 = 5;
MySQL无法解析这个 id + 1 方程式,我们应该养成简化WHERE条件的习惯
2. 前缀索引
有时候需要索引很长的字符列,这会让索引变得大且慢
比如对于 BLOB、TEXT 和 VARCHAR 类型的列,必须使用前缀索引,只索引开始的部分字符。
前缀长度的选取需要根据索引选择性来确定
3. 多列索引
很多人对于多列索引的理解都不够,一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能,所以引入“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。
例如下面的语句中,最好把 username 和 password 设置为多列索引。
SELECT username, password FROM t_user WHERE username = 'Aiguodala' AND password = 'Aiguodala';
4. 合适的索引列顺序
让选择性最强的索引列放在前面。
索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,每个记录的区分度越高,查询效率也越高。
5. 聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
InnoDB 通过主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的非空索引来代替,如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚簇索引。
聚集的数据的优缺点
优点:
- 可以把相关的数据保存在一起
- 例如实现电子邮箱时,根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据就可以获取某个用户的全部邮件,如果没有聚簇索引,获取每封邮件都会导致一次磁盘IO
- 数据访问更快,聚簇索引将索引和数据保存在同一个B+树中,能更快的查找数据
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点:
- 聚簇数据最大限度提高了IO密集型应用的性能,但是如果数据全部放在内存中,则访问的顺序就不重要,聚簇索引也没有优势
- 插入速度严重依赖于插入顺序,如果不是按照主键的顺序加载数据,那么加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表,所以建议选择自增的主键
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
非聚簇索引
将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因
6. 覆盖索引
索引覆盖所有需要查询的字段的值
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » MySQL创建高性能索引的全步骤
相关推荐
评论
管理员已关闭评论功能...