• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

MYSQL学习(三) –索引详解

开发技术 开发技术 1周前 (11-20) 10次浏览

 创建高性能索引

(一)索引简介

  1. 索引的定义

  索引,在数据结构的查找那部分知识中有专门的定义。就是把关键字和它对应的记录关联起来的过程。索引由若干个索引项组成。每个索引项至少包含两部分内容。关键字和关键字对应记录在存储器位置信息。索引是组织磁盘文件的一种重要的技术。

数据库的数据量通常比较大,都是存储在磁盘上。通过存储引擎对磁盘文件的数据进行管理。而索引是存储引擎御用快速找到记录的一种数据结构。

  2.索引的优点

  (1)大大减少服务器需要扫描的数据量

  (2)索引可以帮助服务器避免排序和临时表

  (3)索引可以将随机IO转换成顺序IO

  3.索引三星系统原则

  (1)第一星:索引将相关的记录放在一起。即在一系列必要的列上建立索引,不必为where条件里的所有得列建立索引。

  (2)第二星:索引中数据的顺序和排序要求的数据的顺序一致。通常将选择性更高的列放在索引列的最前面。

  (3)第三星:索引中的列包含查询所需要的所有列。因为索引中已经包含查询所需的全部字段,所以不需要在进行行查询(覆盖索引的定义)。

(二)索引类型

  其中,索引是在存储引擎层实现的。因此,不同的执行引擎,自己选择使用实现自己的索引类型。

  1.B-Tree 索引

  (1)B+树数据结构

 

  B+树的定义是在B树的基础之上定义的。相比B+树,区别部分有两点。

  A.非叶子结点只存key,不存储指向数据的指针(ROWID)

  B.叶子结点都保存一个指向相邻节点的指针。

  区别A.非叶子节点不存储ROWID,索引项更小。一块可以存储更多的索引项。所以,可以存储更多的非叶子索引项。一个索引项能定位更多的叶子结点。

  区别B.叶子节点根据指针链接,范围查询非常简单(如果是B树结构的话,范围查找需要 叶子节点和内部结点之间不停的往返)。

 

  (2)使用B+树索引的存储引擎

  InnoDBMyISAM

  B+树索引是逻辑结构是B+树、物理存储结构是链式存储

  (3)B+树索引适合的查询类型

  1. 键值范围
  2. 键前缀查找(只使用最左前缀查找)
  3. 只访问索引的查询(覆盖索引)
  4. 全键值

  其实是覆盖索引。即索引中的字段能覆盖查询语句中的全部字段(包括分组、排序字段)。

 

  2. 哈希索引

  哈希索引是线性索引。是基于哈希表实现。只有精确匹配索引的所有列的查询才有效。

  哈希索引存储在哈希表中。逻辑结构是线性表,物理存储结构是顺序存储。

  不同的key通过哈希函数计算,可能产生相同的结果。即冲突。哈希索引用的冲突解决算法链地址法(索引相同的记录指针放在一个链表中)

  索引表是线性表的顺序存储。存储在连续的存储空间中。

  (1).哈希索引的索引项包含哈希值行指针

   哈希值:由索引列按照哈希函数计算,获得的哈希值。

   行指针:当前哈希值对应的行的指针

  (2).哈希索引特点

  1. 哈希索引只包含哈希值和行指针。不包含其它列信息。因此,无法避免读取行(无法实现覆盖索引)
  2. 哈希索引的索引项不是按照索引值顺序存储的。所以,无法避免排序
  3. 哈希索引不支持索引列的匹配查找

  3. 全文索引

  全文索引和哈希索引一样,也是一种线程索引。本质是倒序索引。

  4.B+树索引和哈希索引的区别

  1. 哈希索引的特殊性,索引的检索可以一次定位。而B树索引的检索需要工根节点到树枝结点,最后再到叶子结节点。这样多次IO访问。所以,哈希索引的效率远高于B树索引。
  2. 哈希索引无法避免排序按照哈希码顺序存储的,不是按照索引列进行顺序存储的)。B树索引在特殊情况下是可以避免排序操作的(索引列作为索引key)。
  3. 哈希索引只能使用全部索引键来查询,不能用部分索引键来查询。(哈希函数是一种对应关系,所以,必须要所有的参数才能得到哈希码,部分索引建是不可以的)

(三)高性能索引策略

  1.独立的列

  索引列不能是表达式,也不能是函数。

  例如:SELECT actor_id FROM actor WHERE actor_id+1=5  这种写法,就算在actor_id上建立了索引,也不起效。索引列actor_id需要是独立的列才可以。

  2.多列索引

  多列索引也叫符合索引。即同时对多个列建立索引。比如(A,B,C)。

  (1). 需要使用多列索引的场景

  1. 服务器需要对多个索引进行相交运算(通常是AND条件)。
  2. 服务器需要对多个索引进行联合操作(通常是OR条件)。

  (2).多列索引的生效规则

  比如(a,b,c),a,b,c都是排好序的。在任何一段a的下面b都是排好序的。在任何一段b的下面c都是排好序的。多列索引生效原则是从前向后依次生效。如果中间索引列没有起作用,则该索引列之前的索引列起作用。

  例如 (1)select * from mytable where a=3 and b>7 and c=3;  –a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引

  3.聚簇索引

  聚簇索引:不是一种索引类型,是一种数据存储方式

  InnoDB的聚簇索引,在同一个结构中保存了B-Tree索引和数据行。

  当表有聚簇索引的时候,它的数据实际上是存储在索引的叶子页(leaf page)中。数据只有一份,所以,一个表只有一个聚簇索引。聚簇是指键值相邻的数据行紧簇的保存在一起。

  聚簇索引的叶子节点也是数据节点。而非聚簇索引的叶子结点仍然是索引节点。只不过,它有指向对应数据块的指针。

  (1).聚簇索引和二级索引

  1. 聚簇索引的定义

  在InnoDB中,聚簇索引就是主键索引。如果表中没有定义主键,则InnoDB选择一个唯一的非空索引作为主键。如果没有这样的索引,InnoDB会隐式的定义一个主键来做聚簇索引。

  2.聚簇索引特征

  数据存储和索引放在一起,找到索引,就找到数据。

  由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有

  二级索引定义

  非主键索引就是二级索引。

  二级索引特征

  将数据存储和索引存储分开的结构,索引结构的叶子节点指向数据的对应行。

  在InnoDB存储引擎中,在聚簇索引之上创建的索引称之为辅助索引。即二级索引。辅助索引总是需要二次查找的。辅助索引叶子结点存储的不是行的物理位置,而是主键值。(然后根据主键值去聚簇索引中查询数据)

  (2).MyISAMInnoDB的主键索引、二级索引对照图

 MYSQL学习(三) --索引详解

 

 

 MYSQL学习(三) --索引详解

 

 

 

 

  熟悉两种引擎的数据和索引分布,就真正理解了MYSQL的存储和索引查询。

  1. MyISAM数据分布

如上图所示,MyISAM的数据和索引分开储存的。MyISAM是按照插入的顺序,顺序的存储在磁盘上。类似数组那样顺序存储。

   2. MyISAM索引

它对应的不论主键索引还是二级索引,都是典型的B+树索引形式。叶子结点对应的是行指针(行在磁盘中的具体位置)

  3. InnoDB数据分布

如上图所示,InnoDB支持聚簇索引。其中,聚簇索引就是数据。在聚簇索引的叶子节点上,除了主键外,还有事务ID,回滚指针和其余非主键字段。所以,通过主键索引可以直接找到数据.

  4. InnoDB主键索引

InnoDB的主键索引和数据分布内容一样。主键索引就是其数据分布。

  5.InnoDB二级索引

  二级索引也是标准的B树索引。只是叶子结点指的不是行指针,而是主键值。所以,整个叶子结点的索引项是[key+主键]。

  6. 聚簇索引的选择和重建

  聚簇索引默认是按照主键建立的主键索引。如果没有定义主键,InnoDB会选择一个唯一的非空索引替代。

  InnoDB的数据插入是按照主键顺序插入行的

  4.覆盖索引

  如果一个索引包含(或者说覆盖)查询的所有字段(查询字段和where条件字段)的值,我们称之为覆盖索引。【由此可见,覆盖索引是索引的一个分类而已】

  覆盖索引效率高的几个原因

  1. 覆盖索引,只需要查找索引,不需要二次查找数据行。少一次操作。
  2. 覆盖索引是按照索引字段顺序存储。因而,支持范围查找。

  5.使用索引扫描来排序

  核心思路是:因为索引是有序的。如果排序要求的顺序和索引的顺序一致,就可以直接使用索引的顺序。从而减少对排序的操作

  ORDER BY和查找型查询的限制是一样的:需要满足索引的最左前缀原则,否则,MySQL无法使用索引排序。但有一个特殊情况:就是前导列为常量。例如,有一个索引为(A,B,C),那么这样的SQL语句也会用索引排序。

  select id from table where A=2 order by B,C;

  第一个索引列A为常量22后面对应的BC也是有序的。所以,这个查出的数据是有序的。

  select id from table where A>2 order by B,C; 这个不可以。

(四)维护索引和表

  索引如此重要,所以需要对索引和表进行实时维护。确保索引正常工作。

  1.找到并修复损坏的表

  检查:通过CHECK TABLE来检查引擎是否发生表损坏。

  维修:使用 ALTER TABLE innoDB_tbl ENGINE = INNODB;

  2.更新索引统计信息

  ANALYZE TABLE

  3.减少索引和数据碎片

  数据存储的碎片有

  (1).行碎片

  数据行被存在多个地方的多个片段中。

  (2).行间碎片

  逻辑上顺序的也,或者行在磁盘上不是顺序存储的。行间碎片对全表扫描和聚簇索引扫描影响较大。

  (3).剩余空间碎片

  数据页中有大量的剩余空间。从而导致服务器读取大量不需要的数据。

 

  修复: OPTIMIZE TABLE ;或者 ALTER TABLE innoDB_tbl ENGINE= <engine>;

(五)为什么 绝大多数索引选择B+树?

  MYSQL查询的本质是在一个数据集合中的查找数据。查找常见方式以及场景如下

  1.顺序查找,场景:无序,数据量较小。

  2.折半(二分)查找,场景:顺序线性表存储。数据量较小。

  3.二叉树查找(AVL):场景:二叉树或者平衡二叉树存储(有序链式存储),数据量中等或者较小。

  4.多路查找树(B树或B+):场景:B+树存储(有序存储),可以处理数据量很大的数据。

因为B树存储可以让树的阶(深度)控制在较小的分为内。阶(深度)每少一层,查询就会少一次索引的获取。因而,B树这个类型的存储是MYSQL选择索引数据结构的一个很好的方案。

  为什么选择B+树而不是B

  1. B+树非叶子结点不存数据,所以,可以存储更多的结点,因而,树的阶就越小。从树形上来看越矮胖。可以较少IO操作。
  2. 叶子结点都添加一个指向相邻叶子的指针。范围扫描更容易。

喜欢 (0)