金沙官网线上MySql索引总结

索引概念

B+树索引分为聚集索引和非聚集索引(辅助索引),但是两者的数据结构都和B+树一样,区别是存放的内容。

可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时,一棵B+树就只能存储主键,如果检索的是非主键字段,则主键索引失去作用,又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。 这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储是指向真正数据行的指针,并且辅助B+树在叶子节点存储也是指向真正数据行的指针,而非主键B+树。

InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

金沙官网线上 1

InnoDB存储引擎是索引组织表,也就是说数据文件本身就是按照B+树方式存放数据的。Innodb 聚集索引是按照主键(primary key)进行聚集,被索引的列其实是主键列,如果没定义主键,Innodb会试着使用唯一非空索引Unique Index来代替,如果还找不到,Innodb就会自动创建一个6字节作为隐藏主键然后在上面进行索引聚集。除了主键的聚集索引,其他索引(辅助索引)中不会保存行的物理位置,而是保存主键的值,所以通过"二级索引"进行查找是先找到主键,再找到行,要进行二次索引查找。在InnoDB存储引擎中,将B+树索引分为聚集索引和非聚集索引(辅助索引),但是两者的数据结构都和B+树一样,区别是存放的内容。无论是何种索引,每个页的大小都是16KB,且不能改变。

MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

图示清晰的显示了聚簇索引和非聚簇索引的差异。

金沙官网线上 2

Cardinality

并不是所有在查询条件中出现的列都需要添加索引,对于什么时候添加B+树索引,一般的检验是,在访问表中很少一部分行是使用B+树索引才有意义。查看索引是否是高选择性的,可以通过SHOW INDEX语句中的Cardinality列来观察。Cardinality是一个估计值,在实际中,Cardinality/n_rows_in_table应尽可能接近1,如果非常小,那么需要考虑是否还要建这个索引。

InnoDB中如何统计Cardinality值,索引的更新可能非常平凡,如果每次更新操作时就统计Cardinality值,那么对数据库会带来很大负担。InnoDB存储引擎中,Cardinality统计发生在INSERT和UPDATE操作中,不过并不会每次都去统计,它的策略是:

  • 表中的1/16的数据已发生变化。
  • stat_modified_counter > 2000000000。

stat_modified_counter是数据库发生INSERT和UPDATE操作的计数器,如果每次对表中的一行数据更新操作,表中的数据几乎不会有变化,那么第一种策略就无法试用。所以计数器用来统计操作的次数,如果满足条件,也会统计Cardinality值。

InnoDB存储引擎只对8个叶节点进行采样。采样的过程为:

  1. 取得B+树索引中叶节点的数量,即为A。
  2. 随机取B+树索引中的8个节点。统计每页中不同的记录的个数,即为P1,P2,...,P8。
  3. 根据采样的信息给出Cardinality值预估值:Cardinality = (P1+P2+...+P8)* A / 8。

注意:如果表足够小,表的叶节点小于或等于8个,这时即使随机采样,也总是采取到这些页,因此每次的Cardinality值都是一样的。

B+树索引的使用

不同应用中B+树索引的使用

数据库存在两种类型的应用:OLTP和OLAP应用。OLTP是传统关系型数据库的主要应用,其主要面向基本的、日常的事务处理,例如银行交易。OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果。

在OLTP应用中,查询操作一般只从数据库中取得一小部分数据,在这种情况下,建立B+树索引后,优化器就会使用索引。对于OLAP应用,一般需要访问表中的大量数据,并根据这些数据来产生查询的结果,而这些查询多是分析的查询,目的是为决策者提供支持。但是对于LOAP中的复杂查询,需要涉及多表之间的联接操作,这是索引的添加是有意义的,但是联接操作使用Hash Join,那么索引可能又变得不是非常重要。不过在OLAP应用中,通常需要对时间字段进行索引,这是因为大多数统计需要根据时间维度来进行判断。

联合索引

联合索引是指对表上的多个列进行索引,又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。可以支持a | a,b | a,b,c 3种组合进行查找,但不支持b,c进行查找。当最左侧字段是常量引用时,索引就十分有效。查询使用索引的条件不同一般组合索引需要按照“最左前缀”来执行查询,并不是每个列都需要覆盖,只是从左边的列开始组合。在选择组合索引的时候,当前Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好。

例如有索引key(a,b,c)

  • where a=xx and b=xx and c=xxx 此语句可以用到索引
  • where b=xx and a=xx and c=xxx 同上,顺序没有关系,同样能用到索引
  • where a=xx and b=xx 可以用到索引
  • where a=xx and c=xx 可以用到索引
  • where b=xx and c=xx 用不到索引
  • where b=xx 用不到索引
  • where c=xx 用不到索引

覆盖索引

InnoDB存储引擎支持覆盖索引,即从辅助索引就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减小大量的IO操作。

对于InnoDb存储引擎的辅助索引而言,由于其中包含了主键信息,因此其叶子节点放的数据为(primary key1, primary key2,..., key1, key2,...)。对于下面的可以仅用一次辅助联合索引来完成查询。

  • SELECT key2 FROM table key1 = xxx;
  • SELECT primary key1, key2 FROM table WHERE key1 = xxx;
  • SELECT primary key1, primary key2, key2 FROM table WHERE key1 = xxx;

简单的说,如果查询的列,正好是我们设置的主键或者键(建立聚合索引的键),如上图展示的,辅助索引的叶子节点的值就是所要查询的内容,就不必取到键值后再去聚合索引进行二次查找。可以想到如果想要更多的使用覆盖索引这一特性,需要将我们需要的列,都建立联合索引。

联合索引和覆盖索引有很大的区别:

覆盖索引是查询的列可以直接通过索引提取,比如只查询主键的列!或者查询联合索引的所有列或者左边开始的部分列(注意有顺序的)!

而联合索引并不一定只从索引中能获取到所有的数据,这个取决于你所查询的列。比如select * from table where ××××××;的方式就不太可能是覆盖索引。因此如果你查询的列能用到联合索引,且你查询的列都能通过联合索引获取,比如你只查询联合索引所在的列或者左边开始的部分列,这就相当于覆盖索引了。通常为了让查询能用到覆盖索引,就将要查询的多列数据设置成联合索引。

优化器不使用(辅助)索引的情况

在某些情况下,优化器并没有选择辅助索引去查找数据,而是通过扫描聚集索引,也就是直接进行全表的扫描来得到数据。

SELECT * FROM orderdetails WHERE orderid>10000 and orderid<102000;

通过SHOW INDEX FROM orderdetails可以看到可以使用的索引包括PRIMARY、OrderID、OrderOrder_Details三个索引,正常情况下,应该选择OrderID辅助索引开始查询,但是优化器直接选择PRIMARY聚集索引。

金沙官网线上 3

原因在于我们要选取的数据是整行信息,而OrderID索引不能覆盖到我们要查询的信息(覆盖索引),因此在对OrderID索引进行查询到指定数据的操作后,还需要进行一次书签访问来查找整行信息。虽然在辅助索引中数据是顺序存放的,但是再一次的书签查找数据是无序的,因此变为了磁盘上的离散读取操作。如果要访问的数据量很小,那么优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的很大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。

INDEX HINT

MYSQL数据库支持INDEX HINT(索引提示),显示的告诉优化器使用哪个索引,在以下情况下可能需要用到INDEX HINT:

  • MYSQL数据库的优化器错误的选择了某个索引,导致MYSQL语句运行很慢,情况少数。
  • 某SQL语句可以选择的索引非常多,这时优化器选择执行计划时间的开销就可能会大于SQL语言本身。通过INDEX HINT可以强制优化器使用某个索引,直接执行选择指定的索引来完成索引。
USE INDEX

SELECT * FROM t USE INDEX(a) WHERE a=1 AND b=2;

使用USE INDEX可以告诉优化器可以选择该索引,但是不是强制使用该索引。实际的使用优化器还是会根据自己的判断进行选择。

FORCE INDEX

SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b=2;

使用FORCE INDEX可以指定优化器选择某个索引进行查询。

T树索引

对于MYSQL数据库的NDB CLuster内存存储引擎,在使用它时可将其视为内存数据库。在内存数据库中,一般使用T树作为其索引的数据结构。T树是有平衡二叉树和B树发展而来。T树的好处是节点不存放数据,只存放指针,这样能减少内存的使用,这对内存数据库来说是很重要的。同时T树也是一棵平衡二叉树,以此保证查找的性能。

哈希索引

当前MYSQL数据库中,Memory存储引擎支持哈希索引。InnoDB存储引擎支持自适应哈希索引,用户仅能开启该特性,不能对其进行人工干预。通过参数innodb_adaptive_hash_index来禁用或启动此特性,默认为开启。

转载请注明出处。
作者:wuxiwei
出处:http://www.cnblogs.com/wxw16/p/6131132.html

本文由金沙官网线上发布于数据库,转载请注明出处:金沙官网线上MySql索引总结

您可能还会对下面的文章感兴趣: