mysql索引汇总

索引时帮助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)上已有的索引进行覆盖。

阅读更多
文章标签: mysql 索引
个人分类: mysql
上一篇MySQL锁定机制
下一篇工厂模式与建造者模式
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭