1、数据库优化概览图

在数据库优化方面，从主到次的顺序：

以SQL优化、索引优化为主，解决慢SQL问题，最大程度地利用好索引 其次从数据库表结构入手、分库与分表，对数据量级进行处理 最大化利用机器配置，比如设置使用机器内存的大小 如果以上三点无法满足需求，那么再考虑硬件方面的问题，比如提升机器配置，再不行就多用几台服务器，这种成本较高，其性价比相对来说是最低的

2、软优化：

2.1、查询语句的优化 用EXPLAIN 分析一条查询语句

• 1.避免索引失效导致的全表扫描

• 2.SQL语句的规范

• 3.不要在列上进行运算

• 4.不使用NOT IN和<>操作

2.2、优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

使用连接（JOIN）来代替子查询(Sub-Queries)

2.3、添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

索引是提高数据库查询速度最重要的方法之一.

使用短索引

在建有索引的字段上尽量不要使用函数进行操作

应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用

索引失效的情况：mysql

• 1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

• 2.如果是组合索引的话，如果不按照索引的顺序进行查找，比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时，则会进行全表查询,组合索引，多列索引必须满足最左匹配.

• 3.条件中有or,OR关键字的两个字段必须都是用了索引,该查询才会使用索引

• 4.索引无法存储null值，所以where的判断条件如果对字段进行了null值判断，将导致数据库放弃索引而进行全表查询.

为什么索引列无法存储Null值？

a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。

• 5.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

• 6.in 和 not in 也要慎用，否则会导致全表扫描

• 7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

• 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

2.4、表的设计合理化 符合三大范式（3NF)

1NF、列不可分；

强调的是列的原子性，即列不能够再分成其他几列。

2NF、非主键列完全依赖主键，不存在部分依赖；

一是表必须有一个主键；

二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

3NF、非主键列必须直接依赖主键，不存在传递依赖。

不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表

中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时

使用联合(UNION)来代替手动创建的临时表

2.5、表字段设置合理

选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

数据字典，不存汉字查询

2.6、优化子查询

3、硬优化：

3.1、硬件三件套（cpu,内存，磁盘）

3.2、优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

• key_buffer_size:索引缓冲区大小

• table_cache:能同时打开表的个数

• query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.

• sort_buffer_size:排序缓冲区

3.3、数据库的分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

设置数据库主从复制状态下的读写分离，主库进行更新操作，从库进行查询操作。

3.4、缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

每天努力一点，每天都在进步。

目录

一、前言

二、架构优化

分布式缓存

读写分离

水平切分

架构优化小结

三、硬件优化

四、DB优化

五、SQL优化

执行计划

SQL优化小结

---

一、前言

首先，我们看一下，数据库优化可以从那些方面入手：

正如上图所示，数据库优化可以从架构优化，硬件优化，DB优化，SQL优化四个维度入手。

此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

接下来我们再看看每种优化该如何实施。

二、架构优化

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

分布式缓存

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

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

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

读写分离

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

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

 

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

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

水平切分

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

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

        当准备实施水平切分时，需要结合实际业务选取合理的分片键（sharding-key）。

架构优化小结

1. 读写分离主要是用于解决 “数据库读性能问题”

2. 水平切分主要是用于解决“数据库数据量大的问题”

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，将单次写优化成顺序写

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

五、SQL优化

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

• 合理使用索引

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

• 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，因为UNION执行时需要排重；

• 避免select * 写法

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

• JOIN字段建议建立索引

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

• 避免复杂SQL语句

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

• 避免where 1=1写法

• 避免order by rand()类似写法

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

执行计划

要想优化SQL必须要会看执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。通过explain sql 可以查看执行计划

SQL优化小结

这里给大家总结一下SQL优化的套路：

1. 查看执行计划 explain sql

2. 如果有告警信息，查看告警信息 show warnings;

3. 查看SQL涉及的表结构和索引信息

4. 根据执行计划，思考可能的优化点

5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

6. 查看优化后的执行时间和执行计划

7. 如果优化效果不明显，重复第四步操作

数据库优化大体分为四个方面，一是数据库服务器的优化，二是架构的设计层面的优化，三是my.cnf配置的优化，四是sql语句的优化。

一. 数据库服务器的优化

1. 服务器硬件方面：影响Mysql性能因素主要有 CPU、可用内存大小、磁盘读写速度、网络带宽等；

2. 操作系统方面：影响Mysql性能因素主要有 应用文件句柄数、系统的网络配置等；

这部分的优化一般由DBA和运维工程师去完成，在硬件资源的优化中，我们重点关注的应该是服务本身所承载的体量，然后提出合理的指标要求避免出现资源浪费的想象。

二. 架构的设计层面的优化

Mysql是一个磁盘IO访问特别频繁的关系型数据库，在高并发和高性能的场景中，Mysql必然会承受巨大的并发压力，优化方式如下：

1. 搭建Mysql主从集群：单个Mysql服务容易导致单点故障，一旦服务宕机将会导致依赖Mysql数接库的应用全部无法响应，主从集群可以保证服务的高可用性；

2. 读写分离设计：在读多写少的场景中，通过读写分离的方案可以去避免读写冲突，导致性能问题；

3. 分库分表机制：通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量从而提升sql的查询效率。

三. my.cnf配置的优化

四. sql语句的优化

常见提问：你有没有sql优化的实际经验，或者是你对sql优化有没有自己的见解？

答：后期在项目维护的时候，对sql进行过优化，(实际优化例子阐述)，通过对sql的优化，也有一些自己的理解，主要是如下几点：

1. 使用缓存优化查询：

(1) 原理：进行多次相同的查询，结果就会放入缓存中，后续在进行同样的查询，就会从缓存中查询，不会到表中提取数据了。

(2) 避免缓存失效：查询的sql不能变，也就是说sql语句的大小写不同、多空格、少空格、不确定数据等都会算做不同的sql，会导致缓存失效。

为什么？

MySql在实现缓存方面也采用用了Key-Value的方式，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。

(3) 缓存数据失效时机：

1) 表的结构或数据发生改变：在表的结构或数据发生改变时，查询缓存中的数据不再有效，有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效，所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用；

2) 写入数据时缓存失效：当某个表正在写入数据，则这个表的缓存(命中检查,缓存写入等)将会处于失效状态，在Innodb中，如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

4) Mysql会出现内存泄漏问题：

Mysql一般是不会出现这中情况的，如果出现了，一般都是自己的参数设置问题，需要在参数设置和设计上尽量合理，或者去官方bug里memory leak查找，是否存在修复的版本等等，本人暂时未遇到过该情况，如需了解可自行百度。

(3) 优点：提高sql的查询效率

(4) 缺点：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，因此很可能费劲地把结果存起来，还没使用，就被一个更新全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低，除非你的业务就是有一张静态表，很长时间才会更新一次，所以对于某些写频繁的系统，开启Query Cache功能可能并不能让系统性能有提升，有时反而会有下降。

所以需要根据实际情况选择使用mysql自带缓存功能还是使用像redis等第三方缓存。

查询缓存的使用方法可以查看 Mysql之查询缓存介绍_BestandW1shEs_lsy的博客-CSDN博客

2. explain查看sql的执行计划：

用法：explain select ......；

explain的详细使用方法可以查看 Mysql之快速上手sql优化_BestandW1shEs_lsy的博客-CSDN博客

3. 给搜索的字段建立索引：

给哪些字段：

where后面一些常用字段建立索引，但是并不是所有的字段都适合建立索引，如果sql筛选出来的结果集只占表的少部分，就适合建立索引，如果查询出来的数据太大，超过了表的50%以上，就不适合建立索引，因为当数据量达到很大的时候，查询的速度就很慢，这是因为当数据量达到一定量的时候就会走全表扫描，这是因为mysql的内部的一个优化器进行了最优策略。

4. limit X 的使用：

在明确知道只有一条数据的时候，可以使用limit 1，明确知道有五行数据，使用limit 5等，如下图所示：

5. 永久连接：

数据库再创建完一个连接之后，会永久处于连接状态，apache在极端的环境中，它进行的每一个http请求都会创建子进程去请求数据库，到时候如果连接太多后，会增加数接库的负担，该问题是属于架构的问题，比如大型网站的访问，或者是恶意频繁请求数据等情况。

6. 选择正确的数据库引擎：

mysql中有两个数据库引擎的，myISAM 和 innoBD。

(1) myISAM适合大数据量的查询，但是对于一些插入的操作并不友好，因为在进行update的时候会进行表锁，此时如果还有用户在读表，查询是无效的，必须等到插入操作完成后，才可以去读数据；

(2) innoDB对于插入操作比较友好，但是对查询操作不是特别友好，它是一个行锁。

7. 在进行大量数据的delete或insert的时候：

该情况也是会经常遇到，该问题可能会导致数接库服务器宕机，因为在进行大量数据的delete或insert的时候，会将整个表锁起来，此时外部的一些web请求再来，就请求不到该表，当请求达到足够多的时候后，并且此时的执行语句完成，大量的请求就会把数据库搞宕机，并且如过数据量过大，在执行途中中止了该语句的运行，数据库就会回滚操作，可能导致数据库负载变高等影响。

优化方案：需要使用limit去做限制，不要一次性处理全部数据。

8. 数据类型，尽量使用小的：

如果一个硬盘的数据量特别多，不同的数据类型暂用的硬盘空间是不一样的，如果占用硬盘的空间特别小，特别紧凑，那么硬盘的读取速度就会特别快，在大型的公司里，数据库字段类型的选择是有明确的规定的，这样不仅可以节约硬盘空间，而且对数据读取的性能提升也很大。

9. 固定的字段长度：

(1) 优点：从速度来说，处理固定长度字符比可变长度字符的速度快；

用房子打比方简单的说，偏移量是门牌号，数据记录长度是房间数，根据起始位置加上偏移量定位到记录的位置，再从定位到的位置读取记录长度的这么多数据，就把这条记录完整读出来了。

(2) 缺点：从存储空间来说，可变长度字符比固定长度字符节省空间。

10. 尽量不要赋null：

可以用' '，不然容易报空指针的错误。

11. 明确的固定的字段上使用enum：

比如性别、国家、市，这些都是固定的，就可以使用enum，但是有人会选择使用varchar，最好别这么用，因为enum的速度比varchar的速度更快。

enum类型是非常快和紧凑的，实际上，其保存的是 tinyint，但其外表上显示为字符串，这样一来，用这个字段来做一些选项列表变得相当的完美，就比如一个表中存储性别，很多人喜欢存成：男，‘m’，女，'f' ，这时候还不如ENUM性能高，还有一些状态值，例如：新建，审批中，结束，当然中间应该还有其他状态，反正这些用ENUM是最高效的，而且显示的时候，看起来也特别的直观，虽然真实存储tinyint，但是表面是字符呀，这个看的时候特别直观，查询的时候特别高效；

因为无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长，内部都是用整型表示，在内存中产生的数据大小不变，而varchar型，却在内存中产生的数据越来越多。

12. id主键每张表都需要建立：

对于查询性能来说是非常重要的，因为不管通过任何方式去查询表，最终都会通过主键去定位到数据，所有建立主键是可以有效的提高性能的，而且在建主键的时候能不使用varchar，最好不用varchar，因为varchar的查询性能是比不上int的，严谨一点都是使用int，但是大多数都是使用varchar。

13. 避免使用select * ：

原因就是，查询的约多，速度就越慢，数据越多，对网络的传输也会负载过重。

14. 避免使用某些运算函数：

例如，rand() 函数，它的计算是在CPU上进行的，尽量避免使用它，实在必须使用也没办法。

15. 连接两表的时候，join尽量保持两个字段的类型要一致：

因为两个字段的类型不一样，比如一个是int，一个是varchar，join后会导致索引失效。

16. 垂直分割：

将表按列的形式分割成表，如果表中的字段特别多，可以将一张表分为两张表，可以提高访问效率。