索引优化

发布时间:2017-6-29 9:40:16编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"索引优化",主要涉及到索引优化方面的内容,对于索引优化感兴趣的同学可以参考一下。

索引基础

类型

B-TREE

InnoDB实际上用的是B+TREE

B-TREE和B+TREE的区别

  1. B+TREE中只有叶子节点会带有指向记录的指针(ROWID),而B树则所有节点都带有,在内部节点出现的索引项不会再出现在叶子节点中。

  2. B+树中所有叶子节点都是通过指针连接在一起,而B树不会。

B+TREE的优点
  1. 非叶子节点不会带上ROWID,这样,一个块中可以容纳更多的索引项,一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

  2. 叶子节点之间通过指针来连接,范围扫描将十分简单,而对于B树来说,则需要在叶子节点和内部节点不停的往返移动。

B-TREE的优点

对于在内部节点的数据,可直接得到,不必根据叶子节点来定位。

使用场景

由于BTREE是按顺序存储,所以很适合查询范围数据,B-TREE索引适用于全键值,键值范围或键前缀查找(只适用于根据最左前缀查找)
- 全值匹配,和索引中的所有列进行匹配
- 匹配最左前缀,只使用索引第一列
- 匹配列前缀,可以只匹配某一列的值的开头部分(只使用索引第一列)
- 匹配范围值,只使用索引的第一列
- 精确匹配某一列并范围匹配另一列,左列精确匹配,到范围匹配列停止
- 只访问索引的查询,查询只需要访问索引,不需要访问数据行

因为B-TREE中的节点是有序的,所以索引还可以用于ORDER BY操作

限制
  • 如果不按索引的最左列开始查找,则无法使用索引。
  • 不能跳过索引中的列
  • 如果查询中有范围查询,则其右边所有列都无法使用索引

哈希索引

  • 只有Memory引擎显示支持哈希索引
  • 不是按值顺序存储,无法用于排序
  • 不支持部分匹配查询,因为是使用索引列的全部内部计算哈希值的
  • 只支持等值比较查询,不支持任何范围查询

其它索引

空间数据索引(R-Tree)

MyISAM表支持空间索引,可以用作地理数据存储

全文索引

一种特殊类型的索引,它查找的是文本中的关键字

索引的优点

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

高性能索引策略

独立的列

  • 索引不能是表式的一总价,也不能是函数的参数
  • 简化WHERE条件,始终将索引列单独放在比较符号的一侧

前缀索引和索引选择性

  • 前缀索引应该足够长,以使前缀索引的选择性接近于索引的整个列
  • 前缀索引能使索引变得更小,更快,但MySQL无法使用前缀索引做ORDER BY和GROUP BY操作,也无法使用前缀索引做覆盖扫描

索引的选择性是指,不重复的索引值(cardinality)和数据表总数(#T)的比值,范围从I/#T到1之间,选择性越高,查询效率越高,唯一索引的选择性为1

多列索引

在每个列独立的建立索引,并在WHERE中使用了这些列的情况下,MySQL会使用一种叫“索引合并”的策略,查询是能够同时使用这两个单列索引进行扫描,并将结果合并。MySQL会使用这类技术优化复杂查询

索引合并策略有时候是一种优化的结果,但实际上更多时间说明了表上的索引建得糟糕
- 当服务器对多个索引做相关操作时(多个AND条件),通常说明需要一个包含所有相关列的多列索引,而不是多个独立的单列索引
- 当服务器需要对多个索引做联合时(多个OR条件),通常需要耗费大量的CPU和内存资源在算法的胡村、排序和合并操作上
- 如果在EXPLAIN中看到有索引合并,应该优化查询或表结构
- 可以通过参数optimizer_switch关闭索引合并功能

索引列顺序

  • 不考虑排序和分组时,将选择性最高的列放在前面

聚簇索引

聚簇索引是一种数据存储方式,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行,当表中有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。下表只能有一个聚簇索引(因为无法同时把数据行存在在两个不同的地方)
这里写图片描述

叶子页包含了行的全部数据,但是节点页只包含了索引列

  • MySQL内建的存储引擎不支持指聚簇索引,InnoDB默认通过主键聚集数据。
  • 如果没有定义主键,InnoDB会选择一个唯的非空索引代替,如果也没有,会隐式的定义一个主键作为聚簇索引
优点
  • 把相关的数据保存在一起,只需要从磁盘读取少数的数据页就可以获取全部数据
  • 数据访问更快,因为索引和数据保存在同一个B-Tree中
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点


  • 插入速度严重依赖于插入顺序
  • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动的新的位置
  • 插入新行或更新导致需要移动行的时候,可能会有页分裂(page split)的问题

当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会产该页分裂成两个页来容纳该行,这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间

  • 可能导致全表扫描变慢
  • 二级索引(非聚簇索引)可能比想象中大,因为二级索引的叶子节点中包含了引用行的主键列
  • 二级索引访问需要两次索引查找
    二级索引叶子节点保存的是行的主键值,而不是指向行的物理位置的指针

  • InnoDB中,聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。二级索引的每个叶子节点都包含了索引列和主键值

    这里写图片描述

    覆盖索引

    如果一个索引包含(或者说覆盖)所有需要查询的字段的值,就称之为“覆盖索引”。 - 索引条目通常远小于数据行大小,如果只需要读取索引,MySQL会极大的减小数据访问量 - 因为索引是按顺序存储的,所以对于I/O密集型的范围查询,会比随机从磁盘读取每一行数据的I/O要少得多 - 由于InnoDB中的聚簇索引,二级索引的叶子节点中保存了行的主键值,如果二级索引能够覆盖查询,就可以避免对主键索引的二次查询

    使用索引扫描来做排序

    MySQL有两种方式生成有序结果:通过排序操作或者按索引顺序扫描 - 只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,MySQL才能使用索引对结果做排序

    - 如果查询关联多张表,只有当ORDER BY子句引用的字段全部为第一个表时,才能够使用索引做排序

    索引和锁

    • 索引可以让查询锁定更少的行。InnoDB只在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量
    • InnoDB在二级索引上使用共享锁,但访问主键索引需要排他锁


    上一篇:Axon Framework消息、命令和事件
    下一篇:ocilib

    相关文章

    关键词: 索引优化

    相关评论

    本站评论功能暂时取消,后续此功能例行通知。

    一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

    二、互相尊重,对自己的言论和行为负责。

    好贷网好贷款