概述

MySQL的性能优化，包括了服务器硬件优化、操作系统的优化、MySQL数据库配置优化、数据库表设计的优化、SQL语句优化等5个方面的优化。在进行优化之前，需要先掌握性能分析的思路和方法，找出问题，接下来才是优化。

服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。 关于CPU的选择，考虑以下两个场景去做决策：

• 对于数据库并发比较高的场景，CPU的数量比频率重要
• 对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好

操作系统优化

主要是操作系统针对MySQL的参数优化。以下以Centos操作系统来举例。

内核相关参数

修改相关参数配置文件（/etc/sysctl.conf），以达到优化目的。主要参数包括

1.增加监听队列上限

net.core.somaxconn = 65535 
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535

2.加快TCP连接的回收

net.ipv4.tcp_fin_timeout = 10 
net.ipv4.tcp_tw_reuse = 1 
net.ipv4.tcp_tw_recycle = 1

3.TCP连接接收和发送缓冲区大小的默认值和最大值

net.core.wmem_default = 87380 
net.core.wmem_max = 16777216 
net.core.rmem_default = 87380 
net.core.rmem_max = 16777216

4.减少失效连接所占用的TCP资源的数量，加快资源回收的效率

net.ipv4.tcp_keepalive_time = 120 
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3

5.单个共享内存段的最大值

这个参数应该设置的足够大，以便能在一个共享内存段下容纳整个的Innodb缓冲池的大小。 对于64位linux系统，可取的最大值为(物理内存值-1)byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。

kernel.shmmax = 4294967295

6.控制换出运行时内存的相对权重

这个参数当内存不足时会对性能产生比较明显的影响。（设置为0，表示Linux内核虚拟内存完全被占
用，才会要使用交换区。）

vm.swappiness = 0

增加资源限制

修改相关参数配置文件（/etc/security/limits.conf ），适当增加可以打开的文件数。* 表示对所有用户生效，修改后需要重启系统。

* soft nofile 65535
* hard nofile 65535

磁盘调度策略

1.cfq (完全公平队列策略)

Linux2.6.18之后内核的系统默认策略。该模式按进程创建多个队列，各个进程发来的IO请求会被cfq以轮循方式处理，对每个IO请求都是公平的。该策略适合离散读的应用。

2.deadline (截止时间调度策略)
deadline，包含读和写两个队列，确保在一个截止时间内服务请求（截止时间是可调整的），而默认读
期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象，deadline对数据库类应用是最好
的选择

3.noop (电梯式调度策略)

noop只实现一个简单的FIFO队列，倾向饿死读而利于写，因此noop对于闪存设备、RAM及嵌入式系统
是最好的选择。

4.anticipatory (预料I/O调度策略)

本质上与deadline策略一样，但在最后一次读操作之后，要等待6ms，才能继续进行对其它I/O请求进
行调度。它会在每个6ms中插入新的I/O操作，合并写入流，用写入延时换取最大的写入吞吐量。anticipatory适合于写入较多的环境，比如文件服务器。该策略对数据库环境表现很差。

可以使用以下命令查看调度策略的方法

cat /sys/block/磁盘编号（如sda）/queue/scheduler

修改调度策略

echo cfq | deadline | noop | anticipatory > /sys/block/sda/queue/scheduler 

MySQL数据库配置优化

• 缓冲池大小innodb_buffer_pool_size

推荐值为物理内存的50%~80%，比如10G的服务器内存， 可以设置为8G大小

innodb_buffer_pool_size=

• 控制redo log刷新到磁盘的策略

innodb_flush_log_at_trx_commit=1

• 每提交1次事务同步写到磁盘中，可以设置为n

sync_binlog=1

• 脏页占innodb_buffer_pool_size的比例

当脏页占缓冲池大小的达到设置的比例时，触发将脏页的数据刷到磁盘。 推荐值为25%~50%。

innodb_max_dirty_pages_pct=30 

• 后台进程最大IO性能指标

默认200，如果SSD，调整为5000~20000。

innodb_io_capacity=200

指定innodb共享表空间文件的大小

innodb_data_file_path

慢查询日志的阈值设置，单位秒

long_qurey_time=0.3

• mysql复制的形式

row为MySQL8.0的默认形式。

binlog_format=row

• 最大连接数

调高该参数则应降低interactive_timeout、wait_timeout的值

max_connections=200

• 日志文件缓冲大小innodb_log_file_size

此参数过大，实例恢复时间长；过小，造成日志切换频繁。推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size。可以配置适当的innodb_flush_log_at_trx_commit，降低磁盘的写入次数。

innodb_log_file_size=

全量日志建议关闭。 默认关闭。

general_log=0

另外，对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志。

数据库表设计优化

主要面向懂技术并了解需求的开发人员。具体优化方案如下：

• 设计中间表

一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

• 创建合理的冗余字段

为减少关联查询，创建合理的冗余字段。考虑数据库的三范式和查询性能的取舍，创建冗余字段还需要注意数据一致性问题。

• 拆表

对于字段太多的大表，考虑拆表，比如一个表有100多个字段。另外，对于表中经常不被使用的字段或者存储数据比较多的字段，也可考虑拆表，比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联。

• 创建主键

每张表建议都要有一个主键，以创建主键索引，而且主键类型最好是int类型，建议自增主键（不考虑
分布式系统的情况下）。

SQL语句优化

主要面向开发人员。可以通过开启慢查询日志找出执行比较慢的语句，然后查看SQL语句的执行计划分析，然后进行优化。SQL语句的优化，主要包括索引优化、limit优化、其它优化。

索引优化

• 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引

要考虑数据的业务场景，查询多还是增删多，然后去创建合适的索引。

• 尽量建立组合索引

注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。

• 尽量使用覆盖索引

SELECT语句中尽量不要使用*

• order by、group by语句要尽量使用到索引

• 索引长度尽量短

短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引

• 索引更新不能频繁

更新非常频繁的数据不适宜建索引，因为维护索引的成本。

• order by的索引生效

order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

limit优化

limit（limit offset,size）的优化问题，其实是offset的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。

• 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描

SELECT * FROM user WHERE username=’李四川’; -- username没有建立唯一索引 
SELECT * FROM user WHERE username=’李四川’ limit 1; 

• 优化分页偏移量非常大的情况

分页会使用到 limit ，当翻页到非常靠后的页面的时候，偏移量offset会非常大，这时limit的效率会非常差。

解决方案1：使用order by 和索引覆盖

# 原SQL
SELECT id,name FROM user limit 10000, 20;

# 优化后的SQL
SELECT id,name FROM user ORDER BY title limit 20;

解决方案2：使用子查询

解决方案3：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只
写rows（size）

其它优化

• 小表驱动大表

使用left join时，以小表关联大表，因为join的时候，第一张表必须全表扫描，以少关联多可以减少扫描次数。

• 避免全表扫描

不等于号不能使用索引

• 避免放弃索引查询

如果mysql估计（优化器）使用全表扫描比使用索引快的，则不使用索引。（最典型的场景就是数量少的时候）。

• 尽量使用count(主键)，不要使用count(*)

count（*）、count(1)、count(列/主键)，从索引使用情况来说，是一样的。如果count（非索引列），那么MySQL会选择该表中，最小的那棵索引树，去进行统计。目前性能效果已经差不多。

count(*)以行为统计，最终的结果是包含null值；count(1)，会过滤null值；count(列)，会过滤null值，如果允许null的列的话。

• 两张表的连接字段最好都建立索引

join两张表的连接字段最好都建立索引，而且字段类型最好是一样的（不一样会进行隐式转换，类似使用了函数）。

• where条件中尽量不要使用1=1、not in语句（建议使用not exists）

• 不要用MySQL内置的函数，因为函数不会建立查询缓存

• 合理利用慢查询日志、explain执行计划查询、show profile 查看SQL执行时的资源使用情况

#DBA# #java开发工程师# #架构师#