数据库优化

数据库优化可以从架构优化，硬件优化，DB优化，SQL优化四个维度入手 。

一、架构优化

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

1.分布式缓存  【在应用与数据库之间增加一个缓存服务，如Redis或Memcache】

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

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

2.读写分离

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

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

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

3、分库分表

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

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

 

二、硬件优化

 使用数据库，不管是读操作还是写操作，最终都是要访问磁盘，所以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是普通机械硬盘的几十倍不止。这里我们可以从吞吐率、IOPS两个维度看一下机械硬盘、普通固态硬盘、PCIE固态硬盘之间的性能指标。

吞吐率：单位时间内读写的数据量

机械硬盘：约100MB/s ～ 200MB/s
普通固态硬盘：200MB/s ～ 500MB/s
PCIE固态硬盘：900MB/s ～ 3GB/s
IOPS：每秒IO操作的次数

机械硬盘：100 ～200
普通固态硬盘：30000 ～ 50000
PCIE固态硬盘：数十万
通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大，当然性能更好的硬盘价格会更贵，在资金充足并且迫切需要提升数据库性能时，尝试更换一下数据库的硬盘不失为一个非常好的举措，你之前遇到SQL执行缓慢问题在你更换硬盘后很可能将不再是问题。

三、DB优化

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

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

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

先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写
加一层缓存结构Buffer，将单次写优化成顺序写
所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

优化设置页大小

1. ‌扇区‌：扇区是硬盘的最小物理存储单位，每个扇区的大小通常是512字节。虽然有些新型硬盘使用4096字节的扇区，但这是少数情况‌12。扇区是物理层面的概念，操作系统不直接与扇区交互，而是通过磁盘块进行操作‌2。

2. ‌磁盘块‌：磁盘块是操作系统管理的逻辑存储单位，通常由多个连续的扇区组成。每个磁盘块可以包括2、4、8、16、32或64个扇区，具体数量取决于操作系统和文件系统的设计‌12。磁盘块的大小通常是扇区大小的整数倍，这样可以提高数据读取的效率‌1。

3. ‌页‌：页是内存管理和虚拟存储系统中的基本单位，通常大小为4KB或8KB。页的大小与内存的页面大小相同，用于内存与磁盘之间的数据交换‌4。

‌扇区、磁盘块和页的具体大小关系如下‌：

• ‌扇区‌：通常为512字节或4096字节。
• ‌磁盘块‌：由多个扇区组成，大小通常是扇区大小的整数倍，例如4096字节。
• ‌页‌：一般为4KB或8KB，用于内存与磁盘之间的数据交换。

四、SQL优化

        SQL优化很容易理解，就是通过给查询字段添加索引或者改写SQL提高其执行效率

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

2.使用UNION ALL替代UNION
UNION ALL的执行效率比UNION高，因为UNION执行时需要排重；

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

4. JOIN字段建议建立索引
一般JOIN字段都提前加上索引

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

6. 避免where 1=1写法 and 避免order by rand()类似写法
RAND()导致数据列被多次扫描