Mysql优化

最新推荐文章于 2025-03-08 09:00:00 发布

七瑾年丶

最新推荐文章于 2025-03-08 09:00:00 发布

阅读量243

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_36994125/article/details/104492160

版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

一、连接

连接是客户端面对mysql服务端的第一步，这一步可能会出现连接数不够的问题。比如报错：Mysql:error1040:Too many connections，可以从服务端和客户端两个层面来解决连接造成的问题。

服务端

如果有多个应用或大量请求同时访问数据库，造成连接数不够的时候可以修改最大连接数或者修改释放不活动连接的时间（默认连接时间是28800秒）。

#查看mysql最大连接数
show varibles like 'max_connections';
#修改最大连接数，GLOBAL表示全局 SESSION表示当前会话重启mysql后失效
 set GLOBAL max_connections = [期望最大连接数];

#查看默认不活动连接超时时间
show global variables like 'wait_timeout';
#修改不活动连接超时时间
set GLOBAL wait_timeout = [期望不活动超时时间];

客户端

从客户端来说如果不希望每次执行sql都创建一个新的连接，可以引入连接池，正常情况下我们也这么做。ORM层面MyBatis自带了一个连接池。专业的连接池工具有阿里的Druid，Spring Boot2.0默认连接池Hikari，还有更老的DBCP和C3P0。

关于连接池的最大连接数设置，Druid的默认最大连接数是8，Hikari的默认最大连接数是10。在Hikari的GitHub文档https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing中，给出了一个PostgreSQL数据库设置连接池大小的建议是机器的CPU核心数乘以2加1。相当于4核的机器设置连接池的最大连接数为9最合适。

二、缓存

在应用系统的并发数大的情况下，如果没有缓存容易造成给数据库层带来压力过大，操作数据的速度受到影响两个问题。

可以使用第三方缓存来解决这个问题，例如Redis:https://blog.youkuaiyun.com/qq_36994125/article/details/104439133

三、主从复制

如果单台数据库服务不能满足访问需求，就需要对数据库做集群处理，为了让集群的不同节点之间的数据得到一致性的保证，需要采用复制技术（replication）。master/slave主从复制模式，由slave复制master节点的数据，而slave本身也可以作为其他节点的复制来源，这叫做级联复制。

主从复制的实现是根据mysql上记录的更新语句binlog来实现。slave节点从master节点获取binlog文件，然后解析里面的SQL语句，在slave节点中再执行一遍，从而保持主从节点之间的数据一致。这个过程涉及到三个线程。

slave连接到master获取binlog，并解析binlog写入到relay log，这个线程叫I/O线程。
master节点存在一个log dump线程，用于发送binlog给slave连接。
slave节点存在SQL线程，用于读取relay log，并把数据写入库中。

再做了主从复制以后，只把数据写入master节点，而读的请求只分担到slave节点，这种方案叫读写分离。读写分离可以一定程度上减轻数据库服务器的访问压力，但是需要特别注意的是主从数据库的数据一致性问题。

异步复制

在主从复制中，Mysql默认是异步复制的。异步复制指的是客户端请求到主节点，master节点处理请求，写入binlog，事务结束返回给客户端。对于master节点只需要知道slave节点接收到binglog，并不关心slave节点是否写入成功。

全同步复制

master节点等到全部的slave节点的事务执行完毕，才返回给客户端。这种方式叫做全同步复制，虽然这种方式保证了在客户端读数据之前就同步了数据，但是因为整个过程事务执行时间过长，会导致master节点性能下降。

半同步复制

半同步复制指的是主数据库在执行客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从数据库接受到binlog并写入到relay log中才返回给客户端。这种方式master节点不会等待过长时间，并且在返回给客户端的时候，数据就即将写入到从数据库了。因为从数据库只需要做读取relay log，写入数据库就完成了同步。

半同步复制需要安装插件

#插件目录
mysql/plugin/

主数据库执行内容

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
show variables like '%semi_sync%';

从数据库执行内容

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
show global variables like '%semi%';

GTID复制

官方介绍：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html

GTID复制指的是把可以在主数据库上并行执行的事务进行分组，并且给它们编号，这一个组的事务在从数据库上也可以并行执行。这个编号就叫做GTID（Global Transaction Identifiers）。

开启GTID模式

#查看GTID模式状态
show GLOBAL variables like 'gtid_mode';
#打开
set GLOBAL gtid_mode='ON';

四、分库分表

当单个master节点或者单张表存储的数据过大的时候，单表的查询性能还是会下降，这时可以对单台数据库节点的数据分型拆分，这就是分库分表。

垂直分库

垂直分库可以减少并发压力，它的做法是把一个数据库按照业务拆分成多个不同的数据库。

水平分库

水平分库可以解决数据库存储瓶颈问题，它的做法是把单张表的数据按照一定的规则分布到多个相同的数据库中。

高可用解决方案：https://dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html

五、SQL

1.慢查询

官方文档：https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

因为开启慢查询日志有性能代价，所以在mysql中它是默认关闭的。

#查看慢查询状态
show variables like 'slow_query_log';

#打开慢查询
set GLOBAL slow_query_log = ON;

#查询慢查询时间定义，默认10秒
show variables like 'long_query_time';

#查询慢查询日志文件存放路径
show variables like 'slow_query_log_file';

#查询慢查询内容
show global status like 'slow_queries';

慢查询日志

官方文档：https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

在mysql的bin目录下提供了mysqldumpslow工具。

#查看命令使用
mysqldumpslow --help
#查询用时最多的10条慢查询
mysqldumpslow -s t -t 10 -g 'select' ../localhost-slow.log

查询结果中：

Count表示这个SQL执行次数。
Time表示执行时间，括号里是累计时间。
Lock表示锁定的时间，括号是累计时间。
Rows 表示返回的记录数，括号是累计返回记录数。

2.SHOW PROFILE

官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

SHOW PROFILE工具用于查看SQL语句执行时占用的资源消耗。

#查看开启状态
select @@profiling;
#打开show profile
set @@profiling=1;

#查看profile统计
show profiles;

#根据show profiles;得到的queryID查询SQL执行详情
show profile for query [queryID];

查看线程命令

官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

#查看MySQL用户线程，其中查询结果id是线程id，可以用户kill线程
show processlist;

#查询表的方式查询用户线程
select * from information_schema.processlist;

关于线程命令：https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
关于线程状态：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
关于服务器运行状态：https://dev.mysql.com/doc/refman/5.7/en/show-status.html
关于存储引擎运行状态(包含锁信息)：https://dev.mysql.com/doc/refman/5.7/en/show-engine.html

3.EXPLAIN SQL语句分析

在查询SQL前加上EXPLAIN关键字，对将要查询的SQL语句进行分析，如果查询过慢，可从对应方向进行优化。

#例如：
EXPLAIN select * t1.name,t2.message from t1, t2 where t1.id = t2.t1_id;

explain id

根据执行计划，查询结果id值不同时，先查询id值小的再查询id值小的。
根据执行计划，查询结果id值相同时，从上往下执行。

explain select type

表查询常见类型包括：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT等。

SIMPLE：最简单的单表查询，不包含子查询，不包含关联查询。
PRIMARY：包含子查询时，最外面一层的查询。
SUBQUERY：包含子查询时，所有的内层查询。
DERIVED：衍生查询，关联查询时最终得到结果前，用到临时表的查询。
UNION：关联查询，用到了UNION。
UNION RESULT：关联查询时，显示表之间的关联关系。

explain type

表查询的连接类型，常见的连接类型查询性能从优到差为：system > const > eq_ref > ref > range > index > all 。还有并不常见的连接类型(fulltext 、 ref_or_null 、 index_merger、unique_subquery、index_subquery)，其中只有all连接类型不会用到索引。

const：主键索引或者唯一索引，并且只能查询一条数据的SQL。
system：system是const的一种特例，当数据表只有一行数据时的const。例如：只有一条数据的系统表。
eq_ref：通常出现在join查询，表示对前表的每一个结果，都只能后表的一行数据。一般是唯一索引的查询（UNIQUE 或者 PRIMARY KEY）。
ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
range：索引范围扫描，如果where条件后面跟了between and、>、>= 、< 、<= 、in这种类型的查询，连接类型就为range。
index：Full Index Scan，查询索引中的全部数据。
all：Full Table Scan，没有索引或者没使用到索引的查询，代表全表扫描。
null：不用访问表或者索引就能得到结果。比如从dual工具表得到数据。

一般来说需要查询至少能够达到range级别，最好优化到ref级别。all（全表扫描）和 index（查询全部索引）是需要优化的。

explain possible_key

可能用到的索引，possible_key有0个至多个，其中的索引代表可能用到索引却不一定真的用到了索引。如果通过分析发现没有用到索引就要检查SQL或者创建索引。

explain key

实际用到的索引。

explain key_len

索引的长度（使用的字节数），和索引字段的类型、长度相关。

explain rows

Mysql认为需要扫描多少行才能返回请求数据，是一个预估值。一般来说行数越少越好。

explain filtered

表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量百分比。

explain ref

使用哪个列或者常数和索引一起从表中筛选数据。

explain Extra

执行计划给出的额外说明，一般会给出提示，部分提示可以对应分析优化方案。

using index：表示用到了覆盖索引，不需要回表。
using where：表示使用了where条件，存储引擎返回的记录并不是所有的都满足查询条件，需要在server 层进行过滤。
using index condition：表示使用到了索引条件下推https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html。
using filesort：表示不能使用索引来排序，用到了额外的排序，需要优化。
using temporary：表示使用到了临时表，一般是distinct了非索引列、group by了非索引列或者使用 join 的时候，group by 了任意列造成，需要优化，比如可以创建复合索引。

官方SQL优化建议：https://dev.mysql.com/doc/refman/5.7/en/optimization.html

六、存储引擎

1.存储引擎选择

根据不同业务选择不同的存储引擎。

常规的并发大更新多的表用InnoDB。
临时数据表用Memeroy。
查询插入操作多的表用MyISAM。

2.字段定义

使用可以正确存储数据的最小数据类型，为每一列选择合适的字段类型。

整数类型：INT一共有8中类型，不同类型的最大存储范围不一样，比如性别这类固定值用TINYINT。
字符类型：变长情况下，varchar更节省空间，但是对于varchar字段需要一个字节用于记录长度，所以固定长度的用char变长用varchar。
非空：非空字段尽量定义成NOT NULL，提供默认值，或者使用特殊值，或者控字符串来代替null。因为null类型的存储、优化和使用都可能出现问题。
不使用外键、触发器、视图：使用这些特性会降低可读性，影响数据库性能。应该把计算的事情交给程序，数据库只关心存储即可。
大文件存储：不要使用Mysql数据库存储图片（比如Base64编码）或者大文件。文件应该放在对象存储系统，数据库需要存储对应的URI即可。
表拆分：将不常用的字段拆分出去，避免列数过多和数据量过大。比报文数据可以用blob或者text字段单独创建一张表来存储。