快捷搜索:
来自 网络数据库 2019-07-08 03:23 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

目录部分总括

高性能索引

mysql支持的索引

索引是在存储引擎层实现。而不是在mysql内实现

  • B-tree索引

index 普通索引 没有限制
primary 主键索引 必须唯一,且不能为空
unique key 唯一索引 必须唯一
index 前缀索引
当索引的字段长度很大,则使用前缀索引,使用更少的空间,并综合索引的选择性,达到平衡。

//如下面。根据字段列的长度,进行统计
select count(*) as cnt,left(city,7) as pref from city group by pref order by cnt desc limit 10
//计算索引选择性
select count(distinct left(city,7))/count(*) from city
//添加索引
alter table tablename add key (keyname(length))
alter table country add key(country(3))

1。索引基础:索引的作用类似'目录'帮助Query来快速定位数据行。

B-tree索引

1.1索引类型:

新京葡娱乐场网址 ,特点

  1. B-tree 索引是以B 树结构存储数据
  2. B-tree 索引能够加快数据的查询速度
  3. B-tree索引适合进行范围查找 索引是顺序存储的

1.1.1

在什么情况下使用到了b-tree索引

  • 全值匹配的查询
  • 匹配最左前缀的查询
  • 匹配列前缀查询
  • 匹配范围值查找
  • 精确匹配左前列并范围匹配另外一列
  • 只访问索引的查询

b-tree索引

B-tree索引的限制

  • 如果不是按照最左列开始查找,则无法使用索引
  • 使用索引时不能跳过索引中的列
  • not in 和<> 操作无法使用索引
  • 如果查询中有某个列的范围查询,则其右边所有的列都无法使用索引

b-tree(balance tree)索引:使用平衡树(非平衡二叉树)来保存索引值,叶子结点的到根节点的距离相差不超过1;对于不同的引擎,不同的索引类型叶子结点保存的值可以不同,同过索引找到数据行的方式也不同

Hash 索引

可以使用b-tree索引的查询类型:1.全值索引;2.最左前缀;3.前缀索引;4.匹配范围;5.精准匹配某一列,范围匹配另一列(可以在看作最左前缀的扩大化)6.覆盖查询(只访问索引的查询)

特点

  • 基于hash表实现的,只有查询条件精确匹配hash索引中所有列才能使用到hash索引
  • 对于hash索引中的所有列,存储引擎都会为每一行计算一个hash码,hash索引中存储的就是hash码

局限:范围查询对复合索引的截断作用。

限制

  • hash索引必须进行二次查询
  • hash索引无法用于排序
  • hash索引无法用于范围查找也不支持部分索引查找
  • hash索引中hash码的计算可能存在hash冲突

1.1.2

使用索引的好处

  • 索引可以减少存储引擎需要扫描数据量
  • 索引可以帮助排序,避免使用临时表
  • 索引可以把随机io变为顺序io

索引会增加写操作的成本
过多的索引会增加查询优化器的选择时间


hash索引

索引的优化策略

hash索引:hash索引使用索引字段的hash值(散列值)作为索引寻址的标识,找到对应hash值时即可通过hash表对应的指针找到表数据。

索引列上不能使用表达式或者函数

散列值通过散列函数获得:书中对一般例子中使用的MD5()与sha1()两个散列函数的劣势做了分析(这两个函数本来是两种加密函数),两者得到的散列值长度过长,浪费空间;

前缀索引和索引列的选择性

hash冲突:使用一个hash函数传入两个不同的值可能产生一样的散列值,这就会导致hash冲突;解决办法一般为修改散列函数,与再散列;以及增加where条件人工消除hash冲突的影响三种。

联合索引

列的顺序选择

  • 经常被使用的列优先
  • 列的选择性高的优先
  • 宽度小的列优先

文中提到:比较廉价的方式是截取md5()返回值的一部分作为散列值。

覆盖索引

在btree索引上直接获得查询中所需数据

  • 可以优化缓存,减少磁盘io操作
  • 可以减少随机io,变随机io为顺序io
  • 可以避免对innodb主键索引的二次查询
  • 可以避免myisam表进行系统调用

无法使用覆盖索引

  • 存储引擎不支持覆盖索引
  • 查询中使用了太多的列
  • 使用了双%的like查询 双%无法使用索引

应该注意到:myisam,innodb引擎并不支持hash索引。但是innodb有一个特殊的功能“自适应hash索引”,使其给予b-tree索引建立一个hash索引,具有一定hash索引性质。

聚簇索引

聚簇索引是一种数据的存储方式。聚簇表示数据行和相邻的键值紧凑地存储在一起。innodb是通过主键聚集数据

1.1.3空间数据索引(R-tree)

优点

  • 可以把相关的数据保存在一起
  • 数据访问更快
  • 使用索引覆盖扫描查询时,可以直接使用页节点中的主键值

MYISAM支持R-tree,但是mysql数据库在空间索引方面是弱势的,pgsql的postGIS做的比较好。

缺点

  • 聚簇索引最大限度地提升了io密集型的性能,但是如果数据都放入内存,则访问的顺序并没有那么重要
  • 插入的速度严重依赖于插入的顺序
  • 更新的代价很高,会强制innodb把被更新的行移动到新位置
  • 基于聚簇索引的表,在插入新行,或者主键被更新导致需要移动行时,可能会导致页分裂
  • 聚簇索引可能导致全表扫描变慢
  • 二级索引会比想象的大,因为在二级索引中含有主键
  • 二级索引需要两次访问,而不是一次

1.1.4全文索引

全文索引与b-tree索引不冲突,适用于match,against操作。第七章会详细讨论

1.1.5其他索引

这里提到了tokudb引擎使用的分型树索引(fractal  tree  index);以及后面将要讲解的聚簇索引以及覆盖索引。

 

5.2

索引优点:

1.大大减少了服务器需要扫描的数据量;

2.帮助服务器避免了排序和临时表;

3.将随机io变为顺序io;

 

5.3高性能索引

5.3.1

左值才是索引列

索引不能使用表达式的一部分例:where a_id 1=2

5.3.2

前缀索引与索引的选择性

这一节详细讨论了前缀索引对于选择多常记录作为索引字段合适的问题:

提出了一个‘基数’的概念,即前缀对应唯一条目的数量与总数的笔直。前缀的基数应该接近完整列

例:count(distinct  tt(city,3))/count(*)

这个值可以称为选择性

注:部分字段后缀索引有时候更加有效

5.3.3

索引合并

5.0以后会有一新增了索引合并这一优化策略:

问题:   对于一条sql中有多个单列索引可以用到,但是由于优化器对where子句的解析原因,只用到一个(一个索引即可过滤大量数据)或者干脆直接全表扫描(5.0以前优化器的缺陷)。

解决1:使用union all等保留字对数据进行sql进行拆分;

解决2:5.0mysql以上提供了一种索引合并策略,explain以后再会有类似extra:using union(primary,id_key)的提示项。

说明:索引合并大多数时候说明索引不太符合实际运用,需要优化更新索引。

 

 5.3.4 合适顺序的索引

(1)索引项先后顺序对效率的影响

   索引项先后顺序对效率的影响:多列索引中索引顺序的考量,1.可以由前缀索引的知识了解到,索引的顺序对索引的适用情景有较大的限制,这里讨论的是索引顺序对索引效率的影响,可以通过选择性计算的方式,将选择性高的数据放在前面(因为可以一次性过滤掉更多的数据)。    需要注意的是多列索引的索引项顺序是需要整体考量的,因为一条query的查询索引优化可能是另一条query的负优化。

 

(2)聚簇索引

(1)中讨论的索引项的顺序对索引的效率的影响。这里讨论索引中数据顺序对磁盘io的影响:

    对于聚簇索引我们需要明白,它是innodb中的索引类型,在前面的章节我们了解innodb与myisam引擎索引结构的区别的时候说明了,两者对表数据的存放有一些区别,innodb不同于myisam将数据与索引分卡存放的方式,而是将数据行与索引存放在一起,存放了数据行的索引就是聚簇索引,数据行保存在顺序的索引叶子页中,这是io是顺序的,相比随即io能节省很多的io时间。 所以innodb表需要一个主键,没有显示定义的话,innodb也会隐式的定义一个主键索引顺序保存数据。   

聚簇索引的缺点

聚簇索引要求索引与数据行一起顺序保存就使得非顺序插入,修改等需要需要操作原有数据的操作需要对数据的移位(这样才能保证数据的顺序性),非顺序数据插入后最好使用optimize table重新组织一下表。

数据加入的过程中往往可能需要对数据也进行也分裂与也合并,导致数据页中的数据并不是紧靠的,会占用跟多空间,微微的降低全表扫描的效率。、

二级索引(innodb中的非主键索引)除索引项以外需要保存主键值。

 

5.3.5innodb与myisam数据分布对比、

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:目录部分总括

关键词: