mysql索引汇总

原创 2018年04月17日 11:23:21

索引时帮助mysql高效获取数据的数据结构。它对于高效能非常关键,是是现实中性能问题的首要原因。

索引来自表的某一列或多个列的值,如果索引了多列数据,那么顺序很重要,因为mysql只能高效的搜索索引的最左前缀。

最左前缀注意一下几点:

1.使用索引时作为条件查询时,索引的顺序很重要,必须从索引定义的最左边开始的连续索引才可以使索引生效。

            解决方式:a、建立辅助索引 b、通过填坑的方式,自己补全顺序中缺少的索引。

2.理论上索引对顺序是敏感的,但是musql查询优化器会自动调整where字句的顺序以使用合适的索引。

3.如果通配符%不出现在开头(%file),则可以使用到索引。

4.索引最多只能用于一个范围列,如此如果条件中有多个范围查询则无法全用到索引。

            区分多值匹配和范围匹配:

                                       范围匹配:BETWEEN '1986-01-01' AND '1986-12-31'

                                        多值精确匹配 BETWEEN '10001' AND '10010'

索引类型

B- 树

   

B+树


由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引,而B树则常用于文件索引。

但是为了保持平衡,对于新插入的键值可能需要做大量的拆分页(split)操作,而B+树主要用于磁盘,因此页的拆分意味着磁盘的操作,应该在可能的情况下尽量减少页的拆分。因此,B+树提供了旋转(rotation)的功能。
旋转发生在Leaf Page已经满了、但是其左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作,而是将记录移到所在页的兄弟节点上

一棵m阶的B+树和m阶的B树的差异在于:
      1.有n棵子树的结点中含有n个关键字; (而B 树是n棵子树有n-1个关键字)
      2.所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)
      3.所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)

哈希索引

select lname FROM tableName WHERE fname= 'peter';

mysql首先会计算peter的哈希值并且使用它去查找索引的指针。最后用peter和计算出来的指针行数据进行比较,确定他是不是正确的数据行。

注意:

    1、哈希索引不支持部分键匹配,因为他们是由被索引的全部值计算出来的

    2、当发生的碰撞时,则会一行一行的进行比较,或者定义两个条件。


    3、哈希索引只支持使用了 ‘=’‘ IN()‘ 和 ‘<=>’的相同比较。他们不能加快范围查询。

Innodb的自适应韩系索引

当innodb注意一些索引值被很频繁的访问的时候,他就会在Tree的顶端为这些值建立起内存的索引,这使得Btree索引有一些哈希索引的特性。这个过程是全自动的,既不能控制,也不能配置。

如果存储引擎不支持哈希索引,可以模拟innodb使用的方式模拟自己的哈希索引。


高性能索引策略

一、隔离列。

如果在查询中没有隔离索引列,MySQL通常不会使用索引。


二、前缀索引和索引选择性

索引选择性:是不重复的索引值(基数)与表中所有行的比值。选择率为1 为最佳。

需要解决的问题:怎样才能找到长度合适的索引值?

方法:1、逐个验证不同索引长度所产生的结果,选择最优。

2、计算全列的选择性,并使前缀的选择性接近于它。


三、聚集索引。

聚集索引不是一种单独的索引类型,而是一种存储数据的方式。


聚集索引优点:提升获取数据的速度。

缺点:1、插入速度严重依赖插入顺序。这与树的存储结构有关,不按顺序插入会带来分页等操作。

2、更新聚集索引列是昂贵的

3、插入新行可能会导致分页,因而占用更多的空间。

4、聚集表可能会比全表扫描的慢,尤其在表存储的比较稀疏或因为分页而没有顺序存储的时候。


非聚集索引,第一索引和第二索引存储结构基本一致。

聚集索引,第一索引存储表的全部信息,第二索引存储第二索引和第一索引列值主索引。

注意:  如果正在使用的innodb并不需要特定的聚集,就可以定义代理建,其值与应用程序无关。最好避免随机(乱序)聚集键。例如UUID。

覆盖索引

用mysql也能使用索引来接收列的数据,这样就可以不用读行的数据了。

innodb的第二索引的存储结构是覆盖索引最佳的表现。

压缩索引

myisam使用前缀压缩以减少索引大小,运行更多索引被装入内存,以及在某些情况下极大地提升性能。

例如:第一个值是interest,并且第二个值是interesting,第二个值就可以存储为‘8,ing’。

压缩后的块占用的空间较小,但是某些动作变慢了。由于每个压缩的前缀需要依赖前面的值,所以顺序向前的操作性能尚可,但是反向扫描就不会很好的工作。

多余和重复索引

重复索引: MySQL允许你在同一列创建多个索引,他不会注意到你的错误,但是MySQL不得不单独维护每一个索引,并且查询优化器在优化查询时会逐个考虑他们。这会严重的影响性能。索引实现了UNIQUE约束和PRIMARY KEY约束。

多余索引:(A,B)上的索引能被当成A上的索引。有人也许会在(A,B)上添加索引而不是对(A)上已有的索引进行覆盖。

Linux基础课程汇总

-
  • 1970年01月01日 08:00

MySQL索引类型大汇总

  • 2010年08月28日 07:53
  • 17KB
  • 下载

MySQL索引背后的数据结构及算法原理

  • 2012年09月05日 10:20
  • 113KB
  • 下载

MySQL索引使用方法及实例学

索引用于快速找到特定一些值的记录。如果没有索引,MySQL就必须从第一行记录开始读取整个表来检索记录。表越大,资源消耗越大。如果在字段上有索引的话,MySQL就能很快决定该从数据文件的哪个位置开始搜索...
  • hi_jess
  • hi_jess
  • 2009-10-08 11:30:00
  • 2738

mysql索引的设计和使用

  • 2012年03月26日 17:24
  • 541KB
  • 下载

提高vtigercrm速度的MYSQL索引命令

  • 2011年02月22日 11:39
  • 6KB
  • 下载

mysql索引、触发器、事务、存储过程说明

  • 2012年09月13日 15:06
  • 344KB
  • 下载

MySQL索引实战汇总

Java实战技术 2017-09-06 21:22 MySQL索引对数据检索的性能至关重要,盲目的增加索引不仅不能带来性能的提升,反而会消耗更多的额外资源,本篇总结了一些MySQL索引实...
  • u011277123
  • u011277123
  • 2017-09-07 09:07:05
  • 115

Mysql索引失效情况汇总

    1、where子句的查询条件里有where不等号,例如:(column!=XXX),MySQL将无法使用索引    2、where子句的查询条件中使用了函数...
  • ming654987
  • ming654987
  • 2018-03-27 11:42:15
  • 23

MYSQL索引详解(学习记录)

一:什么是索引 1、索引   索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。 2. 索引,是数据库中专门用于帮助用户快速查询数...
  • w670268787
  • w670268787
  • 2016-08-08 09:12:32
  • 998
收藏助手
不良信息举报
您举报文章:mysql索引汇总
举报原因:
原因补充:

(最多只允许输入30个字)