某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > 【mysql】索引的优化,mysql索引

【mysql】索引的优化,mysql索引

时间:2019-12-06 08:48

1、B+树基本概念

原因就是为了减少磁盘io次数,因为b+树所有最终的子节点都能在叶子节点里找见,
所以非叶子节点只需要存`索引范围和指向下一级索引(或者叶子节点)的地址` 就行了,
不需要存整行的数据,所以占用空间非常小,直到找到叶子节点才加载进来整行的数据。

B树非叶子节点也会存数据,所以不适合mysql(以后研究下mongo为啥用b树 再补充)

数据库索引详解

【mysql】索引的优化,mysql索引

写在前面的话

查询容易,优化不易,且写且珍惜

  B+树的语言定义比较复杂,简单的说是为磁盘存取设计的平衡二叉树

B+树适合作为数据库的基础结构,完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问(随机访问即给出任意一个地址,要求返回这个地址存储的数据)但是容量较小。而硬盘的随机访问要经过机械动作(1磁头移动 2盘片转动),访问效率比内存低几个数量级,但是硬盘容量较大。典型的数据库容量大大超过可用内存大小,这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。如下图所示:通常向下读取一个节点的动作可能会是一次磁盘IO操作,不过非叶节点通常会在初始阶段载入内存以加快访问速度。同时为提高在节点间横向遍历速度,真实数据库中可能会将图中蓝色的CPU计算/内存读取优化成二叉搜索树(InnoDB中的page directory机制)。

索引

当我们在设计数据库的时候,对表的一些属性有时会加上索引,但索引为什么能提高检索速率呢?是不是用了索引就一定可以提高效率呢?不同索引之间有什么区别呢?搞懂这些问题是灵活运用索引的必备条件。接下来,我们将一 一进行讨论。

mysql结构

从MySQL逻辑架构来看,MySQL有三层架构,第一层连接,第二层查询解析、分析、优化、视图、缓存,第三层,存储引擎

必赢棋牌官网 1

 

必赢棋牌官网 2

必赢棋牌官网 3

一.索引的本质

索引也分为不同的种类,而且也有不同的分类方法,比较常用的是普通索引和聚集索引。

MySQL有哪些索引类型 ?

从数据结构角度

1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理

2、hash索引:
a 仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询
b 其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引

3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)

4、R-Tree索引(用于对GIS数据类型创建SPATIAL索引)

从物理存储角度

1、聚集索引(clustered index)

2、非聚集索引(non-clustered index)

从逻辑角度

1、主键索引:主键索引是一种特殊的唯一索引,不允许有空值

2、普通索引或者单列索引

3、多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
必赢棋牌官网,MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;
2、index和key为同义词,两者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
6、asc或desc指定升序或降序的索引值存储

  网上经典图,黄色p1 p2 p3代表指针,蓝色的代表磁盘,里面包含数据项,第一层17,35,p1就代表小于17的,p2就代表17-35之间的,p3就代表大于35的,可是需要注意的是,第三层才是真实的数据,17、35都不是真实数据,只是用来划分数据的!

image

1.普通索引

其实对某字段建立了索引就相当于是对该字段新建立了一个表,这个表里的元素是安照这个字段有序排列。这样有什么好处呢?好处就在于如果我们select的时候要搜索该字段,那就会在这个索引表中先查找,因为索引表是有序的,所以在检索该字段的时候就是二分搜索,速度自然会比在原表上快,然后如果我只需要这一个字段的话,查询就可以结束了,但如果还需要除索引字段的其他字段的话,那么就会根据这个索引表的字段对应到主表中,然后再获取。
看了上面讲的,是不是感觉有点迷茫?下面看一下图就会清晰很多。
必赢棋牌官网 4
(图片来源于百度)
大家可以看到这里我们以Col2建立索引之后右边有一颗二叉树,可能大家会问不是说好了是一张表吗,怎么又是二叉树了,好吧表本身就是一种树形的数据结构存储,虽然实际上很少会选取二叉树,但此处方便讲解。可以看到Col2单独的一棵树,然后每一个节点对过来是一条记录,如果我们执行 select Col2 from tablename where Col2=34;那么直接在右边的树中二叉搜索,找到了就可以返回了。如果我们执行 select * from tablename where Col2=34;那么可以看到需要的不仅仅是Col2这一个字段,那么还是先在二叉树中查找,然后找到了之后对应到主表中,然后返回整条记录。

建立索引的原则

1、基数很低的字段不创建索引,更新非常频繁的字段不适合创建索引

2、MySQL不支持bitmap索引

3、采用第三方系统实现 Text/Blob 的全文索引(Sphinx、Coreseek、Lucene、ElashSearch)

4、常用的 where、ORDER BY 、GROUP BY 、DISTINCT 字段要建立索引

5、索引不能太多,会有负作用

6、多使用联合索引、少使用独立索引

7、字符型可创建前缀索引(如 username 字段 80% 的数据都小于18个字符,那么可以创建18个字符的前缀索引

8、字段的顺序对组合索引效率有至关重要的作用,过滤效果越好的字段需要更靠前

最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

9、MySQL只对以下操作符才使用索引

  • <,<=,=,>,>=,between,
  • 某些时候的like(不以通配符%或_开头的情形)
  • 若已对名为col_name的列建了索引,则形如"col_name is null"的SQL会用到索引
  • 若sql语句中的where条件不只1个条件,则MySQL会进行Index Merge优化来缩小候选集范围

10、不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

2、为什么使用B+树

真实数据库中的B+树应该是非常扁平的,可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。我们通过如下图的CREATE语句建立一个只有简单字段的测试表,然后不断添加数据来填充这个表。通过下图的统计数据(来源见参考文献1)可以分析出几个直观的结论,这几个结论宏观的展现了数据库里B+树的尺度。

1.索引的数据结构

通过上面的图我们可以看到,索引的本质其实就是新建了一张表,而表本质上的数据结构就是树形结构,所以索引也是树形结构。但实际运用中并没有谁用红黑树,avl树这种数据结构,一般是b+树,接下来给大家大致介绍一下b+树的构成。
必赢棋牌官网 5
(图片来源于百度)
b+树在构建时和我们之前提到的二三树很像,只是有一些改进,b+树的非叶子节点不包含value的信息,也就是说非叶子结点只起到一个导航的作用,所有的value放在了叶子结点里,这样由于B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。 数据存放的更加紧密,具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。通常会将b+树进行优化,增加顺序访问指针。
必赢棋牌官网 6
(图片来源于百度0)
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。
可以看到b+树对于表的存储是一种很方便的数据结构。那么为什么不用红黑树呢,因为数据量大的时候,会导致这种二叉树深度太深,io次数会很多,层数很少的b+树可以有效降低io次数。

无法使用索引的场景

1、通过索引扫描的记录数超过30%会进行全表扫描

2、第一个索引列使用范围查询不能使用索引

3、内存表使用Hash进行全表扫描

4、ORDER BY 、GROUP BY Hash索引只能进行等于/不等于的检索

5、SELECT … WHERE key1 = ? ORDER BY key2 ASC 对于key1和key2上的索引,查询优化器会自己判断用哪个(只能用到一个)

6、表关联字段类型要一样(包括长度),否则会有类型转换

7、使用函数时不能用到索引( WHERE func(key1) = ? 不能用到)( WHERE key1 + 1 = ? 不能用到)(WHERE key1 = ? + ? 可以用到)

  B+树有什么好处我们非要使用它呢?那就先要来看看mysql的索引

1 每个叶子节点存储了468行数据,每个非叶子节点存储了大约1200个键值,这是一棵平衡的1200路搜索树!

聚集索引

聚集索引和普通索引是不一样的,聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况。意思就是说上面的普通索引我们可以看到是另建了一个表,然后当查询到了索引没有覆盖到的字段的时候是将这个字段映射到了主表中然后进行查询的。而聚集索引建立后主表本身就会按照这个索引的结构来存储,意思就是说主表直接就按这个来存了。这也是为什么聚集索引一定是唯一的原因,因为一张表中只能有一种存储方式。

索引有哪些“副作用”

1、增,删,改都需要修订索引,索引存在额外的维护成本

2、查找翻阅索引系统需要消耗时间,索引存在额外的访问成本

3、索引系统需要一个地方来存放,索引存在额外的空间成本

 

 

2 对于一个22.1G容量的表,也只需要高度为3的B+树就能存储了,这个容量大概能满足很多应用的需要了。如果把高度增大到4,则B+树的存储容量立刻增大到25.9T之巨!

聚集索引与普通索引

两种索引谁更快呢?这当然是没有悬念的,聚集索引更快咯,因为普通索引查到没有覆盖的字段的时候需要向主表中映射过去,然后再获取,而聚集索引因为其本身就包含了所有数据,所以一次就好~

索引工具

mysqlidxchx/pt-index-usage/userstat/check-unused-keys

1、mysqlidxchx工具很长时间没有更新,但主要用来分析general log、slow.log,来判断实例中那个索引是可以删除,但这个工具没有经过实战,风险很大。

2、pt-index-usage原理来类似mysqlidxchx,执行过程中性能消耗比较严重,如果要在生产库上部署,最好在凌晨业务低锋时使用,pt-index-usage只支持slow.log格式的文件,如果要全面分析整个实例索引使用情况,需要long_query_time设置成0,才能把所以的sql记录下来,但同时会对磁盘空间造成压力,同时pt-index-usage对大文件分析就是件痛苦的事。当然pt-index-usage可以考虑部分表索引使用情况的确认。

3、最看好的userstat,收集信息性能优越,成本低。这个patch是google贡献的(userstat_running),percona把它改名成userstat,默认是不开启的,开启是会收集客户端、索引、表、线程信息存储在CLIENT_STATISTICS、INDEX_STATISTICS、TABLE_STATISTICS、THREAD_STATISTICS。Userstat的bug导致的问题太严重,直接导致mysql crash,到目前淘宝生产环境还没有使用。

4、Ryan Lowe的check-unused-keys脚本基于userstat,能够比较方便输出需要删除的索引。

参考地址



写在前面的话 查询容易,优化不易,且写且珍惜 mysql结构 从MySQL逻辑架构来看,MySQL有三层架构,第一层连...

  2.1mysql索引

3 对于一个22.1G容量的表,B+树的高度是3,如果要把非叶节点全部加载到内存也只需要少于18.8M的内存(如何得出的这个结论?因为对于高度为2的树,1203个叶子节点也只需要18.8M空间,而22.1G从良表的高度是3,非叶节点1204个。同时我们假设叶子节点的尺寸是大于非叶节点的,因为叶子节点存储了行数据而非叶节点只有键和少量数据。),只使用如此少的内存就可以保证只需要一次磁盘IO操作就检索出所需的数据,效率是非常之高的。

主键与聚集索引

在我们新建一个表时,如果没有定义主键,那么表格的数据是顺序线性存储的,在定义的主键之后,因为主键默认有索引,并且在很多平台上默认是聚集索引,所以在主键定义的时候就会把整个表变为一个树形结构(如果主键是聚集索引),但要知道的是主键不一定是聚集索引,也可以是普通索引,只是很多平台默认为聚集,不要盲目划等号。

    试想一下在mysql中有200万条数据,在没有建立索引的情况下,会全部进行扫描读取,这个时间消耗是非常恐怖的,而对于大型一点的网站来说,达到这个数据量很容易,不可能这样去设计

必赢棋牌官网 7

索引的利弊

那么索引既然这么快是不是越多越好呢?不存在的,因为索引本身是一个数据表,那么在插入或删除的时候就涉及到了索引表的改变,b+树的插入删除涉及到很多节点操作,或许会消耗很多时间。所以我们对于常改变的字段不宜建索引,而对于改动较少的字段就很合适,在设计表的时候我们要灵活选取,才能高效。

    在我们创建数据库表的时候,大家都知道一个东西叫做主键,一般来讲数据库会自动在主键上创建索引,这叫做主键索引,来看看索引的分类吧

image

    a.主键索引:int优于varchar

    b.普通索引(INDEX):最基本的索引,没有限制,加速查找

    c.唯一索引(UNUQUE):听名字就知道,要求所有类的值是唯一的,但是允许有空值

    d.组合索引: