MySQL数据库优化

概述

        本文主要介绍MySQL常用的数据库索引，理论与实践想结合的方式介绍MySQL数据库常用的优化方法，希望对你有所帮助。

数据库索引分类

        在介绍数据库索引分类之前，我们先来认识下什么是B树、B+树。

B树（B-tree）

        在计算机科学中，B树（英语：B-tree）不是减号-，是连接符-，是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。概括来说是一个一般化的二叉查找树（binary search tree）一个节点可以拥有2个以上的子节点。

        B树减少定位记录时所经历的中间过程，从而加快存取速度，这种数据结构可以用来描述外部存储，常被应用于文件系统及部分数据库索引上，如MongoDB。

如下图所示：一颗4阶的B树

如图所示：

• 根节点至少有两个子节点
• B树在每一个节点中都存储key和value（索引和数据），所有的节点有着相同的结构。
• 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
• 每个节点最多包含4个子节点（4为该B树的阶）
• 所有叶子节点都位于同一层

查找

        下面举一个典型案例说明查找需要消耗的IO，查找65

第一次磁盘IO

        在内存中定位，和57相比

第二次磁盘IO

        在内存中定位，和79相比

第三次磁盘IO

        在内存中定位，和（65，67）比较

        通过整个流程可以看出：B树在查询中的比较是在内存中完成的，相比磁盘IO的速度，内存中的比较耗时几乎可以忽略。所以只要树的高度足够低，IO次数足够少，就可以提升查找性能。这就是B树的优势之一。

插入

        B树插入新节点的过程比较复杂，分成很多种情况，我们只举一个最典型的案例。假如我们要插入的值是28。自顶向下查找28的节点位置，发现28应该插入到节点元素（27，30）之间。

        节点26，27，30已经是3元素节点，无法再增加，父节点13，25，31也是3元素节点，也无法再增加。根节点57是单元素节点，可以升级为2元素节点。于是拆分节点26，27，30节点13，25，31让根节点57升级为2元素节点28，57。节点31独立为根节点的第二个孩子。

        从图中可以看出：B树为了插入一个元素，多个节点发生了连锁改变，会有一定的性能损耗，但也正因为如此，B树能够始终维持多路平衡。这也是B树的另外一大优势：自平衡。

删除

        同样只举一个典型例子，删除元素81。

自顶向下查找元素81的位置。

        删除81后，节点为空，不符合B树规范，兄弟节点也不够借，向父节点借79，导致父节点79也不符合规范，再向其父节点借57，如下图：

        为了保障B树的平衡，合并节点：31，57，节点：65，67，79，最终结果如下图：

以上就是B树的搜索、插入、删除。

小结

一颗m阶的B树遵循以下规则：

注：m阶代表一颗树节点最多有多少个查找路径，m=m路,当m=2则是2叉树,m=3则是3叉

1. 每一个节点最多有m个子节点
2. 子节点数：除根节点外，非叶节点的子节点数<=m ，且m>=2，空树除外；
3. 关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于m-1个

  注：ceil()是个朝正无穷方向取整的函数 如ceil(2.5)结果为3。

1. 如果根节点不是叶子节点，那么它至少有两个子节点；
2. 所有的叶子节点都在同一层，叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null；
3. 排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则。

B+树（B+tree）

        B+ 树是一种树数据结构，通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。

        B+树是基于B-树的一种变体，有着比B-树更高的查询性能。

        B+ 树的创造者Rudolf Bayer没有解释B代表什么。最常见的观点是B代表平衡(balanced)，因为所有的叶子节点在树中都在相同的级别上。B也可能代表Bayer，或者是波音（Boeing），因为他曾经工作于波音科学研究实验室。

        B+树与B树最大的不同，B+树内部节点不保存数据，只用于索引，所有数据保存在叶子节点。我选择与B-树最接近的维基百科定义，如下图为一颗4阶B+树：

如图所示，一颗m阶B+树有以下特点：

• B+树包含2种类型的节点：内部节点（也称索引节点）和叶子节点。根节点本身即可以是内部节点，也可以是叶子节点。根节点的关键字个数最少可以只有1个。
• B+树与B树最大的不同是索引节点不保存数据，只用于索引，所有记录都保存在叶子节点中，索引节点的元素在子节点元素中是最小（或最大）元素。
• m阶B+树表示了内部节点最多有m-1个关键字，叶子节点最多存储m-1个记录。
• 索引节点中的key都按照从小到大的顺序排列，对于索引节点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子节点中的记录也按照key的大小排列。
• 叶子节点中包含了全部元素的信息，及指向这些元素记录的指针，叶子节点本身依关键字的大小自小而大顺序链接。

查找

查找元素5，B+树自顶向下查找

第一次磁盘IO

第二次磁盘IO

第三次磁盘IO

和B-树的不同：

• 索引节点不存放数据，因此同样大小的磁盘页可以容纳更多的节点元素，这也意味着，数据相同的情况下，B+树的结构比B-树更加“矮胖”，因此查询时IO次数也更少。
• B+树的查询必须最终查找到叶子节点，而B-树只要找到匹配元素即可，无论匹配元素是在中间节点还是叶子节点，因此B-树查询性能不稳定（最好情况只查跟节点，最坏情况查到叶子节点），B+树查询性能稳定（每次查询都要到叶子节点）

再来举一个范围查找的例子，查找范围为5到25的元素。

B-树范围查找过程

自顶向下，查找到范围的下限（5）：

中序遍历到元素15

中序遍历到元素17，18

中序遍历到元素20

中序遍历到元素25，遍历结束。

可以看到B-树的范围查找非常繁琐。

B+树范围查找过程

自顶向下查找到范围的下限（5）

通过链表指针，遍历到元素15，17

通过链表指针，遍历元素25，遍历结束。

有图可知：B+树范围查找比B-树简单很多。综合来看，B+树相比B-树优势如下：

1. IO次数少
2. 查询性能稳定
3. 范围查询简便

插入

下面是一颗4阶B树插入过程，空树中插入1、3、5

        插入13后超过了关键字个数限制，所以要进行分裂，分裂成2个节点，左节点2个记录，右节点2个记录，中位数5进位到父节点（索引节点），分裂后当前节点指向父节点。如下图：

当然还有另外一种分裂方式，选择3作为中位数，此时父节点中key就变为3

依次插入：15、17、25、26、27、30、31。索引节点5、15、25、27超过关键字个数限制，分裂成2个节点，左节点2个key，右节点1个key，中位数25进入到父节点，当前节点指向父节点。如下图所示：

当前节点关键字个数满足条件，插入结束。

删除

删除元素25、25

自顶向下，查找元素25的位置

删除25后，不符合B+树特性，合并节点，如下图：

小结

B+树较B-树的优势

1.单一节点存储更多的元素，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。

3.所有叶子节点形成有序链表，便于范围查询。

相信通过上面的介绍，你对B+树已经有了一定的了解，下面我们来学习数据库索引。

索引分类

索引的本质是数据库用于快速找到记录的一种数据结构。从数据结构维度、物理存储维度、逻辑维度上来看，索引分类如下

数据结构维度

B+树索引

        这个是MySQL索引的基本实现方式。除了全文索引、hash索引，Innodb、MyISAM的索引都是通过B+树实现的。

哈希索引

        MySQL并没有显式支持Hash索引，而是作为内部的一种优化。具体在Innodb存储引擎里，会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立hash索引。因此，在MySQL的Innodb里，对于热点的数据会自动生成Hash索引。这种hash索引，根据其使用的场景特点，也叫自适应Hash索引。

全文索引

        在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎。在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引。在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词。主要用来利用关键词查询文本，不是MySQL的主要面向场景，使用较少，这里就不展开讨论了。全文索引可以在VARCHAR或者TEXT类型的列上创建。

R-Tree索引

        R树是用来做空间数据存储的树状数据结构。例如给地理位置，矩形和多边形这类多维数据创建索引。R树是由Antonin Guttman于1984年提出的。 在现实生活中，R树可以用来存储地图上的空间信息，例如餐馆地址，或者地图上用来构造街道，建筑，湖泊边缘和海岸线的多边形。然后可以用它来回答“查找距离我2千米以内的博物馆”，“检索距离我2千米以内的所有路段”（然后显示在导航系统中）或者“查找（直线距离）最近的加油站”这类问题。R树还可以用来加速使用包括大圆距离在内的各种距离度量方式的最邻近搜索。主要用来解决空间数据检索问题，是B+Tree的多维版。

物理存储维度

聚集索引

        聚集索引也叫做聚簇索引或者簇类索引，索引的存储顺序和数据的存储顺序有关系。MySQL中主键索引从物理存储纬度看也是聚集索引。

非聚集索引

        非聚集索引也叫做非聚簇索引，索引的存储顺序和数据的存储顺序无关系。

逻辑维度

主键索引

        MySQL中是根据主键来组织数据的，所以每张表都必须有主键索引，主键索引只能有一个，不能为null同时必须保证唯一性。建表时如果没有指定主键索引，则会自动生成一个隐藏的字段作为主键索引。

普通索引

        也可以称之为辅助索引或者二级索引。主键索引的叶子节点存储了完整的数据行，而非主键索引的叶子节点存储的则是主键索引值，通过非主键索引查询数据时，会先查找到主键索引，然后再到主键索引上去查找对应的数据。

联合索引

        也称为复合索引或者组合索引亦或者多值索引，由多个索引字段组成。

唯一索引

        顾名思义，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查，如果有重复的值，则会操作失败，抛出异常。可以为null值但不可以为空字符串。

        需要注意的是，主键索引一定是唯一索引，而唯一索引不一定是主键索引。唯一索引可以理解为仅仅是将索引设置一个唯一性的属性。

空间索引

        空间索引通过四叉树、R树等数据结构，还有 GeoHash 算法将二维数据转化为一维使用普通B树索引来实现，它们都能实现对空间范围内的快速搜索。如：查询(116.27636, 40.041285)附近的点。

小结

        以上我们介绍了索引的数据结构、索引分类，相信通过上面的学习，你从数据库原理上已能够理解建好索引、用好索引的必要性，索引也是数据库最常用的优化手段。下面我们从实践角度来告诉你常用的数据库优化技巧。

数据库优化技巧

        我们来看下在使用MySQL数据库时，有哪些实用优化技巧。Query语句占用的资源可以划为：CPU资源、IO资源、内存空间、网络带宽、锁。

        从监控数据来看，对大家比较有用的性能指标如下：

性能指标	含义
innodb_row_read	行读，点查峰值大约在800W左右，列表查大约在1200W左右。
innodb_buffer_pool_read_requests	逻辑读，峰值800W左右。
innodb_bp_hit	innodb bp缓存命中率，好的命中率一般在99.8%+。
innodb_bp_dirty_pct	脏页百分比，写入大的应用达到相应阈值就会有不同的刷脏策略
thread_running	活跃线程，反映用户并发状态，通常情况下会建议指标小于核数。范围限制：如开启线程池的情况下，受限于thread_pool_size和thread_pool_oversubscribe。反之，则受限于max_user_connections。

常用的优化方法

• 降低单条记录长度
• 将访问频率低、大字段拆分，用主键对应
• 适当冗余，不要使用多表join查询
• 选择过滤性高的字段建立索引
• Join查询中连接字段建立索引
• 尽量使用覆盖索引
• 利用前缀索引
• 避免建重复索引，提高索引使用率
• 建议读写都采用主键索引
• 尽量利用索引排序，避免产生临时表
• 避免使用select *
• 避免使用全模糊查询 like '%xxx%’
• 多SQL综合考虑，保证核心SQL