数据库索引设计与优化07-多索引访问

简介

许多数据库管理系统支持从一张表的多个索引处收集制作,或是从单个索引的几个索引片收集,然后比较这些指针集并访问满足WHERE语句中所有谓词条件的数据行。这一能力被称为多索引访问,或被称为索引与(索引交集)和索引或(索引并集)。

索引与

假设表TX上有索引A和索引B,如何使用如下的索引的来完成SQL 10.1的查询

SELECT B, C
FROM TX
WHERE A = :A
      AND
      B BETWEEN :B1 AND :B2
ORDER BY B

在高昂的磁盘空间成本导致宽索引不太普遍时,索引与是很重要的。对与必须在A和索引B之前作出选择,而后读取表行以校验其余谓词条件的场景,该特性提供了另一种替代方案。如下

  • 从索引片①和索引片②上手机所有满足响应谓词条件的索引行指针
  • 按页号的顺序对两个指针集合进行排序③
  • 合并这两个已排序的指针集合③
  • 只针对那些同时满足两个WHERE子句的结果行进行表访问④
    每个结果行进行一次表访问;这些表访问将会比传统随机读的方式访问得更快,因为指针已经按照页号进行了排序,所以对表页的扫描将通过跳跃式顺序读的方式完成。

那么这一访问路径与单个组合索引相比哪个更好?相比多索引访问我们更倾向于后者,因为使用后者对索引的访问次数更少。即便如此,优化器仍然可能会选择跳跃式顺序读的方式,如果这么做看起来性能不错的话。当然,对该查询来说,一个宽索引(A,B,C)是更高效的方案。

在现今的操作型应用中,索引并不是一种普遍的访问路径。如果通过EXPLAIN发现了索引与,则应当考虑使用一个宽索引将其替代,因为使用上述机制进行索引与操作有三个严重的缺陷 :

  1. 当一个简单谓词有一个较高的过滤因子时,顺序访问的量可能会过多
  2. 即便多个索引片都是从一个符合查询排序的索引上读取的,ORDER BY子句仍会引起一次排序,因为对指针集的排序操作破坏了从索引继承而来的原始顺序。
  3. 如果从索引片上仅仅手机指向表行的指针,那么一个只需要访问索引的访问路径是无法实现的。这一数据库管理系统的实现相关的问题将在后面讨论。

假设有一张表CUST,表上有三个单列索引(SEX, WEIGHT, HEIGHT),表上总共有100万行数据,SQL 10.2如下。如果优化器决定从这三个索引上收集匹配的指针,那么数据库管理系统就必须访问50万的SEX索引行。并且,针对每一位身材高大的男士都将进行一次表访问。若数据库管理系统依照上述的方式进行多索引访问,那么即使SEX索引变宽也无法避免表访问。
SQL 10.2

SELECT LNAME, FNAME, CNO
FROM CUST
WHERE SEX = 'M'
      AND
      (WEIGHT > 90 OR HEIGHT > 190)
ORDER BY LNAME, FNAME

与查询表一同使用索引与

如果应用系统会对一个表生成SELECT语句,且这些SELECT语句带有许多不同且不可预测的WHERE子句,那么此时索引与是一个很有用的功能。

查询表是对查询进行了优化了的事实数据表。他们在表的行数不是特别多的时候是可行的,比如几百万行数据。正是由于对查询进行了优化,所以不再需要进行表连接,所有查询语句所需的列都在一个表中了。

多索引访问和事实数据表

也许在一个大的事实数据表上创建多个窄索引,主键索引及外键索引的方案看起来不错。这样将会比宽索引耗费更少的磁盘空间。然而,如果该表有10亿行数据,那么基于B树索引的多索引扫描,两桶大量的指针扫描和排序一起,在当前的硬件条件下可能会运行的太慢。

用位图索引进行多索引访问

略...

索引或

索引或是比索引与更重要的一个特性。
在多索引访问功能被引入作为优化器的一部分之前,对于SQL 10.3中所示的SELECT语句,优化器将会选择哪种访问路径呢?如果该表有一个单列索引A和一个单列索引B,那么优化器只有一个合理的选择,即全部扫描,因为这两个谓词都是非布尔的,数据库管理系统并不能因为其中一个谓词被检查出不满足条件而排除该行。
SQL 10.3

SELECT B, C
FROM TX                          1000000 rows
WHERE A = :A                     FF = 0...0.01%
      OR
      B BETWEEN :B1 AND :B2      FF = 0...0.05%

即使使用一个宽索引(A, B, C)也不会有太大的帮助,因为数据库管理系统必须扫描整个索引,这涉及一百万次的顺序访问。

在使用索引或时,会进行如下操作

  • 采集两个指针集①和②---100次顺序访问A,500次顺序访问B,在最差输入条件下合计共600次访问。
  • 对指针进行排序③---非常快。
  • 去除重复的指针③---非常快。
  • 读取满足条件的表行④---在最差输入下且没有重复指针的情况下,共600次随机访问,100 x 10ms = 6s;相较之下,全索引扫描花费的时长为 1000000 x 0.01ms = 10s

在快速EXPLAIN复查的过程中,我们应当对SELECT语句进行多索引访问检查;不合适的索引或者有害的OR可能会被识别出来,这些识别出的问题必须用UNION来替代,或者对游标进行拆分。

索引连接

略。。。

标签: mysql, mysql索引

添加新评论