日常工作中如何做MySQL优化？

发布时间：2024-11-24 09:56

厨房设计中如何实现工作流优化 #生活技巧# #家居技巧# #家居设计灵感#

前提：当面试官问你在日常工作中怎么做MySQL优化的？很多同学第一反应想到的就是SQL优化，如何创建索引，如何改写SQL，他们把数据库优化与SQL优化划上了等号。当然这不能算是完全错误的回答，只不过思考的角度稍微片面了些，太“程序员思维”化了，没有站在更高层次来思考回答。那今天我们就将视角拔高，站在架构的角度来聊聊这一问题，数据库优化可以从哪些维度入手？

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

一、架构优化

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

1.1、分布式缓存

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

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

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

缓存穿透：它是指当用户在查询一条数据的时候，而此时数据库和缓存都没有关于这条数据的任何记录。这条数据在缓存中没找到就会向数据库请求获取数据。它拿不到数据时，是会一直查询数据库，这样会对数据库的访问造成很大的压力。

缓存击穿：一个热点key刚好在某个时间点失效了，但是这时候突然来了大量对这个key的并发访问请求，导致大并发请求直接穿透缓存直达数据库，瞬间对数据库的访问压力增大。

缓存雪崩：某一个时间段内，缓存集中过期失效，如果这个时间段内有大量请求，而查询数据量巨大，所有的请求都会达到存储层，存储层的调用量会暴增，引起数据库压力过大甚至宕机。

1.2、读写分离

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

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

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

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

1.3、分库分表

分库分表，也是一种常见的数据库架构优化手段。当你的应用业务数据量很大，单表容量或单库连接数成为性能瓶颈后，采用水平切分，可以降低数据库单表和单库容量，提升数据库读写性能。

适当分表和分库策略

分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。

分表：从单张表拆分成多张表的过程，将数据散落在多张表内。

为什么要分库分表？

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

怎么进行分库分表？

一般我们可以先分表，解决单表的容量和读写性能问题，随着业务发展，单库也遇到瓶颈了再考虑分库。分表主要看容量，很多经验表明上千万后性能会有显著下降，因此，我们可以把表容量定在一半多一点，600w。分库主要看的是连接数，我们以阿里对外售卖的云存储来大致估计，单库的连接数定在4000左右。

分库分表工具

sharding-jdbc（当当）

TSharding（蘑菇街）

Atlas（奇虎360）

Cobar（阿里巴巴）

MyCAT（基于Cobar）

Oceanus（58同城）

Vitess（谷歌）各种工具的利弊自查

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

1.4、架构优化小结

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

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

分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

二、硬件优化

2.1、磁盘

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

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

机械硬盘：约100MB/s ～ 200MB/s

普通固态硬盘：200MB/s ～ 500MB/s

PCIE固态硬盘：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次数

机械硬盘：100 ～200

普通固态硬盘：30000 ～ 50000

PCIE固态硬盘：数十万

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

2.2、网络

保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率

2.3、内存

MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率

三、DB优化

3.1、数据库参数设置

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

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

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

先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写

加一层缓存结构Buffer，将单次写优化成顺序写

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

接下来我们以MySQL（InnoDB）看看每种数据库的参数该如何配置。

3.2、使用数据库连接池

数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对连接池中的连接进行申请，使用，释放。

数据库连接池允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库读写的性能。

常见数据库连接池：

1）DBCP

　　DBCP是一个依赖Jakarta commons-pool对象池机制的数据库连接池.DBCP可以直接的在应用程序中使用，Tomcat的数据源使用的就是DBCP。

2）c3p0

　　c3p0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象。

3）Druid

　　阿里出品，淘宝和支付宝专用数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个ProxyDriver，一系列内置的JDBC组件库，一个 SQL Parser。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等等。

4)HikariCP

在Sping Boot 2.0之后默认使用HikariCP数据库连接池，您或许不再一定要用druid、c3p0等连接池了。在Spring Boot的官方文档中，其更推荐使用HiKariCP数据库连接池，因其高效的性能和并发性。

在很多朋友的感受中，HiKariCP是数据库连接池的一个后起之秀，号称性能最好，可以完美地PK掉其他连接池，是一个高性能的JDBC连接池，基于BoneCP做了不少的改进和优化。其作者还有另外一个开源作品——高性能的JSON解析器HikariJSON。

3.3、字段类型选择和设计

尽量避免使用NULL

NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化。应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0

最小数据长度

越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

使用最简单数据类型

简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高

尽量少定义 text 类型

text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

整数类型宽度设置

MySQL可以为整数类型指定宽度，例如int(11)，实际上并没有意义，它并不会限制值的范围，对于存储和计算来说，int(1)和int(20)是相同的

VARCHAR和CHAR类型

char类型是定长的，而varchar存储可变字符串，比定长更省空间，但是varchar需要额外1或2个字节记录字符串长度，更新时也容易产生碎片

需要结合使用场景来选择：如果字符串列最大长度比平均长度大很多，或者列的更新很少，选择varchar较合适；如果要存很短的字符串，或者字符串值长度都相同，比如MD5值，或者列数据经常变更，选择使用char类型

DATETIME和TIMESTAMP类型

datetime的范围更大，能表示从1001到9999年，timestamp只能表示从1970年到2038年。datetime与时区无关，timestamp显示值依赖于时区。在大多数场景下，这两种类型都能良好地工作，但是建议使用timestamp，因为datetime占用8个字节，timestamp只占用了4个字节，timestamp空间效率更高

BLOB和TEXT类型

blob和text都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储

在实际使用中，要慎用这两种类型，它们的查询效率很低，如果字段必须要使用这两种类型，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

四、SQL优化

此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率，这里给大家总结一下SQL优化的套路：

开启慢查询日志

查看执行计划 explain sql

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

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

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

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

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

如果优化效果不明显，重复第五步操作

4.1、开启慢查询日志

出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理。

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过long_query_time值的 SQL 将会被记录在日志中，我们可以通过设置“slow_query_log=1”来开启慢查询。

需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能。

4.2、explain 分析SQL的执行计划

要想优化SQL必须要会看执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，来认识一下执行计划。

通过explain sql 可以查看执行计划，如：

4.3、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语句

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

避免where 1=1写法

避免order by rand()类似写法

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

4.4 、索引优化，正确使用索引

(1) 正确使用索引

假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能。

建立覆盖索引，不用回表查询

InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引。

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

在 MySQL 5.0 之前的版本尽量避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并

索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，因此就不会导致索引失效的问题了

如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询

避免在 where 查询条件中使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃索引索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引

我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

查询具体的字段而非全部字段

要尽量避免使用select *，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where id in (select id from B);

不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;

select * from test where month(updateTime) = 7;

一个很容易踩的坑：隐式类型转换：

select * from test where skuId=123456

skuId这个字段上有索引，但是explain的结果却显示这条语句会全表扫描

原因在于skuId的字符类型是varchar(32)，比较值却是整型，故需要做类型转换

(2) 适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

(3)正确使用联合索引

使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据的，因此在创建联合索引的时候需要注意索引创建的顺序

例如，我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询；然而如果非最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引

参考链接：

数据库优化之四大心法

面试题：在日常工作中怎么做MySQL优化的？

什么是分库分表，为什么要分库分表？

网址：日常工作中如何做MySQL优化？ https://www.yuejiaxmz.com/news/view/232297

上一篇: 一文详谈20多种RAG优化方法

下一篇: 多目标优化问题与解决技巧的综述

日常工作中如何做MySQL优化？

一、架构优化

二、硬件优化

三、DB优化

四、SQL优化

相关内容

随便看看

最新动态分享

热点动态分享

专题

推荐动态分享