假如你是一名DBA，或者一名开发者，让你去优化mysql数据库，你会想到怎么样优化逻辑思路？sql语句的explain查看是否使用索引？是不是查看表索引是否合理？还是cache查询结果？也许这些细节操作看起来都没有问题，但是思想哪里来？你为什么要这样做？理由是什么？我不想用高大上的术语来说明这些。我以我对数据库理解来陈述一下优化的思路。

了解客户端想服务器发送增删改查请求，下面以查询请求为例来展示大致过程：大致分为连接管理---解析与优化---存储引擎三个部分。如图

mysql的优化---思路篇_mysql

那么根据上图，先从mysql本身来做优化的思路是否可以？。优化mysql思路我们能不能从上面的流程环节入手？哪些环节我们作为开发人员或者DBA可以切入的？连接管理哪些可以优化？解析与优化涉及到哪些可以去做？存储引擎的选择（毕竟我们不可能修改存储引擎内部机制对于一般人来说，那样是mysql源码二次开发了。）？

其次在考虑从架构方面来做优化。

一、mysql本身优化思路

mysql的优化---思路篇_SQL_02

1、连接与配置优化：

Too many connections这样错误，也许我们经常遇到的问题。那么这个问题来源哪里？

服务端配置

1.1 增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个 ，下面就是我的数据库版本。

mysql> show variables like 'version';

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| version       | 8.0.31 |

+---------------+--------+

1 row in set (0.00 sec)

mysql的优化---思路篇_存储引擎_03

是不是我们修改max_connections越大越好呢？答案肯定不是，增加max_connections数量是可以缓解Too many connections出现概率，单台mysql服务器能力是有限，就算max_connections无限大，也在mysql服务器IO瓶颈，CPU瓶颈，内存瓶颈等。具体调整多大，根据自己业务与实际流量，服务器硬件条件来适当调整。没有标准答案。

修改max_connections操作最直接做法就是my.cnf去修改即可。

1.2  我们能不能把之前的连接用户释放不活动连接提供给新的用户使用呢？答案是可以的。默认28800秒，也就是8个小时。

mysql的优化---思路篇_mysql_04

mysql的优化---思路篇_存储引擎_05

mysql的优化---思路篇_存储引擎_06

connect_timeout在获取连接阶段（authenticate）起作用，

interactive_timeout和wait_timeout在连接空闲阶段（sleep）起作用，

net_read_timeout和net_write_timeout在连接繁忙阶段（query）起作用。

当然我觉得这些参数只能作为优化当中一些参考，很多参数都有默认值，也是mysql开发者经过思考与验证提供默认值。不是必要情况，不必考虑。

客户端连接优化

​ 客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。

1.3解决方案就是提供客户端连接池方案。

数据库连接池是个容器，负责分配、管理数据库连接(Connection)。它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个;

释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。

好处:  资源重用。 提升系统响应速度。避免数据库连接遗漏。

​常见的数据库连接池:DBCP、C3Po、Druid、Hikari。

连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？

对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。

Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的

具体使用参考其他资料进行。

2、sql分析与优化

SQL查询语句是解析器和优化器的作用了。

只要sql的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

2.1 慢查询

开启慢查询同时也会带来mysql性能代价，有一些代价是必须在权衡中来操作。show variables like 'slow_query%';查看是否开启，我本地服务器没有开启，slow_query_log_file表示慢查询日志的保存位置。

mysql的优化---思路篇_mysql_07

具体操作，在my.cnf做如下设置即可。

[mysqld]

slow_query_log=ON

long_query_time=2

slow_query_log_file=/var/log/mysql/slow.log

mysql的优化---思路篇_SQL_08

以下是我做动态修改。关闭慢日志。mysql重启后该设置就立刻失效。

mysql的优化---思路篇_存储引擎_09

我来做一下慢日志操作分析。

mysql的优化---思路篇_mysql_10

下面利用mysqldumpslow命令来分析慢日志。

mysqldumpslow --help;查看使用。

root@debian:/var/log/mysql# mysqldumpslow --help

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]


Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default

                al: average lock time

                ar: average rows sent

                at: average query time

                 c: count

                 l: lock time

                 r: rows sent

                 t: query time
  -r           reverse the sort order (largest last instead of first)

  -t NUM       just show the top n queries

  -a           don't abstract all numbers to N and strings to 'S'

  -n NUM       abstract numbers with at least n digits within names

  -g PATTERN   grep: only consider stmts that include this string

  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),

               default is '*', i.e. match all

  -i NAME      name of server instance (if using mysql.server startup script)

  -l           don't subtract lock time from total time

s:是表示按照何种方式排序

​c:访问次数

i：锁定时间

r：返回记录

t：查询时间

al:平均锁定时间

ar：平均返回记录数

at：平均查询时间

t：即为返回前面多少条数据

g：后边搭配一个正则匹配模式，大小写不敏感

mysqldumpslow -s t -t 1 -g 'select' /var/log/mysql/slow.log  查看

Reading mysql slow query log from /var/log/mysql/slow.log

Count: 1  Time=20.00s (20s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost

  select sleep(N) from record_demo

mysql的优化---思路篇_mysql_11

Count：表示这个SQL执行的次数

Time：表示执行的时间，括号中的是累积时间

Locks：表示锁定的时间，括号中的是累积时间

Rows：表示返回的记录数，括号中的是累积数

2.2 查看运行中的线程 show processlist

mysql的优化---思路篇_mysql_12

由于我本地服务器没有应用程序运行。从网上找来一张图。

mysql的优化---思路篇_SQL_13

其中，

  • Id：线程的唯一标志，可以使用Id杀死指定线程
  • User：启动这个线程的用户，普通账户只能查看自己的线程
  • Host：哪个ip和端口发起的连接
  • db：线程操作的数据库
  • Command：线程的命令
  • Time：操作持续时间，单位秒
  • State：线程的状态
  • Info：SQL语句的前100个字符

查看到哪个线程异常的，直接kill。

3.3 查看mysql服务器状态

​使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

mysql的优化---思路篇_mysql_14

show global status; 由于变量太多，仅仅截图局部信息。

mysql的优化---思路篇_存储引擎_15

mysql的优化---思路篇_SQL_16

show global status like 'com_select';根据需求来查看不同状态。

mysql的优化---思路篇_SQL_17

3.4查看存储引擎运行信息  show engine innodb status;

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。

例如：

SHOW ENGINE INNODB STATUS;

上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

mysql的优化---思路篇_mysql_18

3.5 explain 查看执行计划

​通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？

MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。

​EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。

​这里不详细说明EXPLAIN的细节

3.6 SQL与索引优化  关于这个技术话题，细节比较多。这里不做细讲了。

​为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。


4、存储引擎与表结构

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。

4.1选择mysql存储引擎

建议根据不同的业务选择不同的存储引擎，例如：

  • 查询操作、插入操作多的业务表，推荐使用MyISAM；
  • 临时表使用Memory；
  • 并发数量大、更新多的业务选择使用InnoDB；
  • 不知道选啥直接默认。

4.2 字段优化

​字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

整型

MySQL提供了6种整数类型，分别是

  • tinyint
  • smallint
  • mediumint
  • int
  • integer
  • bigint

字符串

如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。

非空

​​非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。

因为NULL类型的存储和优化都会存在性能不佳的问题。

不要用外键、触发器和视图功能

​这也是「阿里巴巴开发手册」中提到的原则。原因有三个：

  • 降低了可读性，检查代码的同时还得查看数据库的代码；
  • 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
  • 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。

大字段拆分和数据冗余

我在工作中，特别喜欢数据冗余来解决union问题（字段冗余原则上不符合数据库设计范式）。当然这个需要根据业务需求来确定。

大字段尽量在设计数据结构中避免。把常用的字段单独拆分出来成为一个表，也就是垂直分表方式。例如商品id和商品price，商品title，商品缩略图pic_path。平时商品列表经常用到这些数据，单独拿出来做一个商品基础表，那些商品描述，商品图片集，商品的细节参数。单独拿出来做表，关联为商品id。

当然一些图片，文件大二进制文件，就不要用数据库存储了，存储它路径即可。

以上我们谈都是mysql本身做一些优化。下面我们从另一个角度来做优化。

二、mysql架构方面出发

1、缓存机制。从引入第三方缓存来保存查询结果，这样极大提高mysql响应速度。我本人喜欢有redis来解决这个问题。别的缓存也是可以的。个人爱好，用自己熟悉的技术来做就可以了的。

2、mysql集群方式。

2.1 读写分离。主从设计。（具体实现这里不细谈，网上大把资料，这里主要谈思路，大方向。每个一个环节考虑）

2.2 分库分表

​分库分表分成垂直分库、垂直分表、水平分库和水平分表。水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。

这里也不细说了。

3、信息队列引入---削峰


mysql的优化---思路篇_存储引擎_19

                           网络图

三、业务优化来达到mysql优化查询

​严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：

以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；

双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；

双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。

​