【数据库】优化？索引？了解一下

实际数据库优化可以从架构优化，硬件优化，DB优化，SQL优化四个维度入手。位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

一、架构优化

一般来说在高并发的场景下对架构层进行优化其效果最为明显，常见的优化手段有：分布式缓存，读写分离，分库分表等，每种优化手段又适用于不同的应用场景。

1、分布式缓存

性能不够，缓存来凑。当需要在架构层进行优化时我们第一时间就会想到缓存这个神器，在应用与数据库之间增加一层缓存服务，如Redis或Memcache。

当接收到查询请求后，我们先查询缓存，判断缓存中是否有数据，有数据就直接返回给应用，如若没有再查询数据库，并加载到缓存中，这样就大大减少了对数据库的访问次数，自然而然也提高了数据库性能。

不过需要注意的是，引入分布式缓存后系统需要考虑如何应对缓存穿透、缓存击穿和缓存雪崩的问题。

• 缓存穿透：指当用户在查询一条数据的时候，而此时数据库和缓存都没有关于这条数据的任何记录。这条数据在缓存中没找到就会向数据库请求获取数据。它拿不到数据时，是会一直查询数据库，这样会对数据库的访问造成很大的压力。
• 缓存击穿：一个热点key刚好在某个时间点失效了，但是这时候突然来了大量对这个key的并发访问请求，导致大并发请求直接穿透缓存直达数据库，瞬间对数据库的访问压力增大。
• 缓存雪崩：某一个时间段内，缓存集中过期失效，如果这个时间段内有大量请求，而查询数据量巨大，所有的请求都会达到存储层，存储层的调用量会暴增，引起数据库压力过大甚至宕机。

2、读写分离

一主多从，读写分离，主动同步，是一种常见的数据库架构优化手段。

一般来说当你的应用是读多写少，数据库扛不住读压力的时候，采用读写分离，通过增加从库数量可以线性提升系统读性能。

主库，提供数据库写服务； 从库，提供数据库读能力； 主从之间同步数据问题。

当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从数据不一致性问题。

3、分库分表之水平切分

水平切分，也是一种常见的数据库架构优化手段。

当应用业务数据量很大，单库容量成为性能瓶颈后，采用水平切分可以降低数据库单库容量，提升数据库写性能。

实施水平切分时，需要结合实际业务选取合理的分片键（sharding-key），有时候为了解决非分片键查询问题还需要将数据写到单独的查询组件，如ElasticSearch。

4、架构优化小结

• 读写分离主要是用于解决 “数据库读性能问题”
• 水平切分主要是用于解决“数据库数据量大的问题”
• 分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

二、硬件优化

使用数据库，不管是读操作还是写操作，最终都是要访问磁盘，所以说磁盘的性能决定了数据库的性能。

不同规格的硬盘之间的性能差距非常大，当然性能更好的硬盘价格会更贵，在资金充足并且迫切需要提升数据库性能时，尝试更换一下数据库的硬盘不失为一个非常好的举措，你之前遇到SQL执行缓慢问题在你更换硬盘后很可能将不再是问题。

三、DB优化

SQL执行慢有时候不一定完全是SQL问题，手动安装一台数据库而不做任何参数调整，再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能，首先我们就得先优化数据库的实例参数。

数据库实例参数优化遵循三句口诀： 日志不能小、缓存足够大、连接要够用。

数据库事务提交后需要将事务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

• 先将事务写到日志文件redoLog（WAL），将随机写优化成顺序写
• 加一层缓存结构Buffer，将单次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

那么，数据库的参数该如何配置？

四、SQL优化

SQL优化很容易理解，就是通过给查询字段添加索引或者改写SQL提高其执行效率，一般而言，SQL编写有以下几个通用的技巧：

1）合理使用索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

2）使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序

3）避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

4）JOIN字段建议建立索引

一般JOIN字段都提前加上索引

5）避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

6）避免where 1=1写法

7）避免order by rand()类似写法

RAND()导致数据列被多次扫描

总结一下SQL优化的套路：

• 查看执行计划 explain sql
• 如果有告警信息，查看告警信息 show warnings;
• 查看SQL涉及的表结构和索引信息
• 根据执行计划，思考可能的优化点
• 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
• 查看优化后的执行时间和执行计划

1、什么是索引？

官方对于索引的定义是帮助 MySQL 高效获取数据的数据

索引如何实现高效的查找数据呢？

如何找到快速地找出一个序列中某元素的位置呢？从左至右遍历、排序…

2、数据结构的选择

常常用于排序的数据结构有哪些？

最能提高查询效率的就是 hash表和树

因此 MySQL 中正是利用了这两种数据结构来作为索引的底层结构支撑。

需要注意的是，hash 表因为将 key 作为 hash code 后存储，随机查询能力极强，但是不具备排序的能力，因为已经被 hash 一遍了，丢失了原本的数值。所以一般用于等值查询，而不能用于范围查询与排序

在 MySQL 中只有 memory 存储引擎支持 hash 表。而 InnoDB 和 MyIsam 都是使用的 B+ 树，hash 表的结构也比较简单，重点讲解 B+ 树结构，以及 MySQL 是如何一步步进化到 B+ 树的结构的。

3、树的演变

3.1 二叉搜索树 BST

谈到树结构，首先想到的是较为简单的二叉树，但因为要具有排序的能力，于是引入了二叉搜索树（Binary Search Tree，BST）

二叉搜索树是一种节点值之间具有一定数量级次序的二叉树，对于树中每个节点：

• 若其左子树存在，则其左子树中每个节点的值都不大于该节点值；
• 若其右子树存在，则其右子树中每个节点的值都不小于该节点值。

但这样的结构有一个非常致命的问题，当数据本身有序时，就会退化为链表结构，其查询速度又变成了全表查询的速度

3.2 二叉平衡树 AVL

二叉平衡树（Balanced binary search trees，AVL），会通过旋转(左旋右旋 )自动调整树的结构，也就是说他会自动保证树结构的平衡，而不会出现二叉搜索树的问题。当然旋转操作也是要消耗性能的，是通过牺牲更新数据时的性能来换取查询数据时的性能，但是这样的牺牲显然是值得的。

但是二叉平衡树有一个限制：最短子树和最高子树之间的高度差不能超过1

这样的限制导致当数据越来越多，因为子树相差不超过1，最高子树高度增加，最低子树高度也要跟着增加，整个树的高度越来越高

树的高度增加会带来什么问题？

• 对于数据的查找，实际上是和高度相关的，高度越高，当我们要查询底下的数据时，消耗的时间就越久

• 数据的存储还有一个问题，数据在磁盘中存储如果物理空间是在一起的查询速度就快，如果不在一起查询速度就慢

所以也就意味着，树越高查询时需要消耗的IO就多，查询速度就慢。因此AVL也不能满足了

既然最低子树和最高子树高度差限制为1导致了这个问题，那么你把高度差限制取消掉不就好了吗？

想象一下如果把高度差限制取消掉，AVL 怎么判断是否要旋转来调整树结构呢？不旋转不就又退回到 BST 了吗？

那既然高度差限制不能取消，那就把限制调大一点

3.3 红黑树

红黑树（Red-Black Trees）通过左旋和右旋来调整树高的平衡，并且加入了变色的行为，要求根节点为黑色，其他节点都是红色，也因此得名，通过加入颜色值，作为二叉树平衡度的检查标准，只要插入节点的颜色满足要求，最短树高和最长树高之差不会相差太远。

红黑树将树高差限制放宽了，只要最高子树的树高不超过最矮子树的树高的2倍即可

红黑树在 jdk1.8 中也被作为 HashMap 的底层数据结构

随着业务量的持续增加，数据量变得越来越多，红黑树的树高还是会不可避免的越来越高，即使高度差拓展到了2倍，可还是不够～

因为红黑树也依然是二叉树，二叉！它只有两个分支，即使再旋转，它也只有两个分支，只要数据量达到一定地步，树的高度不可避免的还是要增加。

那为什么 HashMap 可以用红黑树而 MySQL 里就不能用了呢？

• 使用场景的不同了，MySQL 作为持久性的数据库存储，基于磁盘来实现，而 HashMap 可能作为代码运算或者本地缓存，并不作数据的持久化存储，基于内存来实现，其场景就已经限制磁盘存储的量一定比内存存储的量要大的多。

3.4 B 树

B 树是有序多叉树，每个节点中能够存储的数据变多了，同时每层的节点数也增加了

深入了解一下树中每个节点的结构，树中的每个节点称之为数据页，大小为16KB。也就是说每个节点只能装16KB的数据，装满就挪到下一个节点中。

B 树中每个节点，不仅要装索引值，而且要装该索引对应的数据。是 key-value 结构，如果是主键索引，key 就是主键值，value就是行数据

这样的结构意味着什么呢？

• 随着数据量的持续增加，因为数据页大小固定为16KB，又因为节点既要装索引值，又要装索引对应的数据，索引值一般很小，数据会占用大部分的空间，使得每个节点能装的索引其实不多，那么树的高度又会不断的变高。

• 树越高，IO越多，查询越慢。于是乎～性能瓶颈又来了。

怎么办呢？

• 既然因为 Data 占用了极大部分的空间，那么能不能不在节点放 Data 数据呢？答案是不行，因为我们的目的是要根据索引找到数据，根本目的是数据，而不是索引，所以去掉数据肯定不行。但是我们可以给数据换个位置

3.5 B+ 树

B+ 树的处理更加聪明了，我们只在叶子节点中存储数据，非叶子节点中只存储索引以及指向下个节点的指针。

这样的处理方案，是的非叶子节点能够存储的索引数据会大大增加，那么树整体的高度就会下降。从而提高查询效率

同时 B+ 树还在叶子节点之间加上了双向指针，使得分页查找的速度也大大提升。当需要进行遍历操作的时候直接通过叶子节点的链表查询

同时还需要注意的是，非主键索引中，data 存储的是主键数据，而在主键索引中，不同的存储引擎会存储不同的数据：

• InnoDB：主键索引的叶子节点，存储的是行数据，也体现出 InnoDB 存储方式采用的是聚簇索引，即 InnoDB 的索引和数据是放在同一个文件里存储的
• MyIsam：主键索引的叶子节点，存储的是指向行数据的内存地址，并没有真正存储行数据，体现出采用的是非聚簇索引，所以MyIsam 的数据和索引是放在两个文件里分开存储的。