MySQL（四）性能优化

于 2021-05-18 09:58:49 发布
阅读量302 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiayibingdong/article/details/116700833
版权

1、连接优化（配置）

第一个环节是客户端连接到服务端，这一部分可能出现的问题是服务端连接数不足，例如Mysql：error 1040：Too Many connections，这个问题可以通过客户端和服务端两个方面解决。

1.1、服务端

如果有多个应用或者大量请求涌入数据库导致连接数不足

  • 增加可用连接数，修改max_connections：
    show variables like 'max_connections' ; --修改最大连接数，当有多个应用连接时
  • 及时释放不活动的连接，默认超时时间为28800秒，8小时，修改wait_timeout：
    show global variables like 'wait_timeout' ; --及时释放不活动的连接

1.2、客户端

减少从服务端获取连接数，可以引入连接池，实现连接重用。
常见的数据库连接池有DBCP，C3P0，阿里的Druid，Hikari。
连接池的建议大小是：机器核数*2+1，也就是说双核机器的连接池大小应当是5。
Druid的默认最大连接池数是8，Hikari是10。

2、缓存优化（结构）

2.1、缓存

为了减轻查询缓慢的SQL语句执行压力，提高效率，可以将这些数据放到内存里缓存。
缓存适用于实时性不强的数据，更新不频繁但是查询频繁。

2.2、集群和主从复制

为了提升整个系统的可用性，可以采用集群模式。一旦使用集群，就会导致各个节点数据的一致性问题，为了解决问题，就要增加主从复制功能。
提供数据的节点被称为master（主机），得到数据的节点就是slave（从机），从master节点复制数据到slave节点就叫主从复制，从slave节点复制到slave节点叫做级联复制。
MySQL的主从复制实现，主要依赖binlog。
在MySQL所有更新语句都会记录到Server层的binlog，slave节点会不断获取master节点的binlog文件，然后解析语句，重新执行，保证数据的一致性。
在这里插入图片描述

  • I/O线程：连接到master获取binlog，并且解析binlog写入中继日志。
  • Log Dump线程：发送binlog给slave
  • slave的SQL线程：读取relay log，把数据写入数据库。

主从配置完成后，master节点负责写入数据，slave负责读取数据，实现读写分离，读写分离可以有效减轻主服务器的访问压力。

2.3、分库分表

分库分表是为了解决某一个库表的数据量过大造成查询性能下降问题的技术。
分库分表可以大致分为两类，垂直分库和水平分表。

  • 垂直分库：把一个数据库按照业务拆分成不同的数据库。
  • 水平分表：把单张表的数据按照一定的规则分布到多个数据库。

3、优化器

优化器的作用是对SQL语句进行优化分析，生成执行计划。在做针对性分析的时候，可以使用慢查询日志进行排查。

3.1、慢查询日志

3.1.1、打开慢查询日志

慢查询日志功能默认是关闭的，因为开启会影响它的性能。

show variables like 'slow_query%'

除了这个开关，还有一个参数，控制执行超过多久的SQL才记录到慢日志，默认是10秒，如果改成0秒就是记录所有的SQL。

show variables like 'long_query%'

参数的修改有两种方式：
1、使用set动态修改参数，重启后失效

set@@global.slow_query_log=1;--1开启，0关闭，重启后失效
set@global.slow_query_time=3;--默认10秒，另外开启窗口才能查询到最新值

2、修改配置文件my.cnf
以下配置定义了慢查询日志的开关，慢查询时间和日志文件的存放日志。

slow_query_log=ON
slow_query_time=2
slow_query_log_file=/var/lib/mysql/loaclhost-slow.log

3.1.2、慢查询分析

MySQL提供了MySQLdumpslow工具，在MySQL的bin目录下。

mysqldumpslow --help

例如查询用时最长的SQL

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/loaclhost-slow.log

在这里插入图片描述

  • count：SQL被执行次数
  • time：执行时间，括号里是累计
  • lock：锁定时间，括号里是累计
  • rows：返回的记录数，括号里是累计

有时候查询速度慢可能不是SQL的问题，也可能是服务器状态，存储引擎的问题。

3.2、其他系统命令

3.2.1、show processlist 运行线程

show full processlist;
select * from information_schema.processlist

此命令用于显示用户运行线程，可以根据ID来kill线程。
root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程，除非单独个这个用户赋予了PROCESS 权限。
在这里插入图片描述

列含义
ID线程的唯一标识，可以用来kill线程
User启动这个线程的用户，普通用户只能看到自己的线程
Host发起链接的IP端口
db操作的数据库
Command线程正在执行的命令
State线程状态
Time操作持续时间，单位：秒
infoSQL语句的前100个字符，想要查看完整SQL语句，需要执行：show full processlist

Command的状态很多，用列表记录一下便于查看

命令含义
Binlog Dump主节点正在将binlog日志同步到从节点
Change User正在执行一个 change-user 的操作
Close Stmt正在关闭一个Prepared Statement 对象
Connect一个从节点连上了主节点
Connect Out一个从节点正在连主节点
Create DB正在执行一个create-database 的操作
Daemon服务器内部线程，而不是来自客户端的链接
Debug线程正在生成调试信息
Delayed Insert该线程是一个延迟插入的处理程序
Drop DB正在执行一个 drop-database 的操作
Execute正在执行一个 Prepared Statement
Fetch正在从Prepared Statement 中获取执行结果
Field List正在获取表的列信息
Init DB该线程正在选取一个默认的数据库
Kill正在执行 kill 语句，杀死指定线程
Long Data正在从Prepared Statement 中检索 long data
Ping正在处理 server-ping 的请求
Prepare该线程正在准备一个 Prepared Statement
ProcessList该线程正在生成服务器线程相关信息
Query该线程正在执行一个语句
Quit该线程正在退出
Refresh该线程正在刷表，日志或缓存；或者在重置状态变量；或者在复制服务器信息
Register Slave正在注册从节点
Reset Stmt正在重置 prepared statement
Set Option正在设置或重置客户端的 statement-execution 选项
Shutdown正在关闭服务器
Sleep正在等待客户端向它发送执行语句
Statistics该线程正在生成 server-status 信息
Table Dump正在发送表的内容到从服务器
Time未被使用

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
比较常用的KILL使用方式，查询出所有非睡眠状态且执行时间超过300秒的线程ID，拼接出KILL的语句，然后把拼接好的KILL语句执行一遍。

select concat('kill ', id, ';') from information_schema.processlist where Command <> 'Sleep' and Time > 300 order by Time desc;

3.2.2、show status 服务器运行状态

show global status

此命令用于查看MySQL服务器运行状态，重启后会被清空。具有两种作用域，session和global，格式：参数-值
可以通过通配符查询更具体的状态数值

show global status like 'com_select' --查询select的执行次数

3.2.3、show engine 存储引擎运行信息

show engine用来显示存储引擎当前的运行信息，包括事务持有的行锁与表锁信息，事务的锁等待情况，线程信号量等待，文件I/O请求，buffer pool统计信息。

show engine innodb status;

开启innodb监控

set global innodb_status_output=ON; --开启标准监控
set global innodb_status_output_locks=ON; --开启锁监控

4、执行计划

执行如下SQL作为演示示例

create table course
(
	cid int not null,
	cname varchar(20) null,
	tid int null
);

create table teacher
(
	tid int null,
	tname varchar(20) null,
	tcid int null
);

create table teacher_contact
(
	tcid int null,
	phone varchar(200) null
);

INSERT INTO test.course (cid, cname, tid) VALUES (1, 'mysql', 1);
INSERT INTO test.course (cid, cname, tid) VALUES (2, 'jvm', 1);
INSERT INTO test.course (cid, cname, tid) VALUES (3, 'juc', 2);
INSERT INTO test.course (cid, cname, tid) VALUES (4, 'spring', 3);

INSERT INTO test.teacher (tid, tname, tcid) VALUES (1, 'li', 1);
INSERT INTO test.teacher (tid, tname, tcid) VALUES (2, 'zhang', 2);
INSERT INTO test.teacher (tid, tname, tcid) VALUES (3, 'qin', 3);

INSERT INTO test.teacher_contact (tcid, phone) VALUES (1, '17777777777');
INSERT INTO test.teacher_contact (tcid, phone) VALUES (2, '18888888888');
INSERT INTO test.teacher_contact (tcid, phone) VALUES (3, '19999999999');

4.1、ID

id是查询序列编号，每张表都是单独访问的，一个select就有一个对应的序号。

  1. 在id值不一样的时候，先查询id值大的，后查询id值小的。
    在这里插入图片描述

  2. 在id值一样的时候，表的查询顺序是从上往下顺序执行，在连接查询中，先查的叫做驱动表，后查的叫做被驱动表，应该先查小表，后查达标，因为小表的中间结果更少，这就是小表驱动大表的思想。
    在这里插入图片描述

  3. 在id值既有相同，也有不同的情况下，就是id不同的按先大后小，id相同的按从上向下。

4.2、select type 查询类型

  1. simple：简单查询，不包含子查询，不包含关联查询，union。
    在这里插入图片描述

  2. primary：子查询SQL语句中的主查询，也就是最外面的那层查询。

  3. subquery：子查询SQL语句中的内层查询都是subquery类型的。
    在这里插入图片描述

  4. derived：衍生查询，意味着在得到最终结果之前会用到临时表。对于关联查询，先执行右边的表（UNION），再执行左边的表。
    在这里插入图片描述

  5. union：同上
    在这里插入图片描述

  6. union result：显示哪些表存在union查询，<union2,3>，指的就是id=2，id=3的查询存在union，其他同上。

4.3、type 连接类型

在所有的连接类型中，上面的最好，越向下越差。
常用的连接类型中：system > const > eq_ref > ref > range > index > all
还有一些不常用的类型，fulltext，ref_or_null，index_merger，unique_subquery，index_subquery
以上除了all类型，都能使用索引。

  1. const：主键索引或唯一索引，只能查到一条数据的SQL，在上文中的course表增加主键后可以得到结果。
    在这里插入图片描述

  2. system：const的一种特例，只有一行满足条件，对于MyISAM，Memory的表，只查询到一条记录，也是system。

  3. eq_ref：通常出现在多表的join查询，被驱动表通过唯一性索引（unique或primary）进行访问，此时被驱动表的访问方式就是eq_ref。在上文中的teacher_contact表增加主键后可以得到结果。
    在这里插入图片描述

  4. ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
    在这里插入图片描述

  5. range：索引范围扫描，如果where后面是between and，>，<，>=，<=，in，type类型一定是range。
    在这里插入图片描述

  6. index：查询全部索引中的数据
    在这里插入图片描述

  7. all：如果没有索引或者没有用到索引，就会全表扫描。
    在这里插入图片描述

  8. null：不用访问表或索引就可以得到结果

4.4、possible_key，key 可能索引，实际索引

可能用到的索引和实际用到的索引，如果为null就代表没有用到索引。
possible_key可以有一个或者多个，可能用到索引不代表一定能用到索引。反之，possible_key为空，key不一定为空。

4.5、key_len 索引长度

索引的长度（使用的字节数），与索引字段本身的类型，长度有关。

4.6、rows 预估扫描行

MySQL预估要扫描多少行才能返回请求的数据，一般来说越小越好。

4.7、filtered 过滤百分比

代表存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的一个比例，因此它是一个百分比数值。
如果数值很低，意味着存储引擎返回到server层的数据需要大量的筛选，需要优化。

4.8、ref

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

4.9、extra

执行计划给出的额外的信息说明

  1. using index：用到了覆盖索引，不需要回表
  2. using where：使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤（和是否使用索引没有关系）
  3. Using index condition：索引条件下推，只适用于二级索引和单表查询，当查询结果中只有索引数据，那么就算where限定条件中没有用到索引，存储引擎也能根据where限定条件对结果进行一次过滤，减轻server层的筛选压力。
  4. using filesort：不能使用索引来排序，用到了额外的排序，需要进一步优化。
  5. using temporary：用到了临时表，需要进一步优化，如创建复合索引。发生情况如下：
    1、distinct 非索引列
    2、group by 非索引列
    3、使用join的时候，group任意列

如果需要具体的cost信息，可以用EXPLAIN FORMAT=JOSN
如果觉得EXPLAIN 不够详细，可以开启optimizer trace

show variables like 'opimizer_trace';
set optimizer_trace='enabled=on';
select * from information_schema.optimizer_trace\G

5、SQL与索引优化

当SQL比较复杂的时候，需要根据条件进行优化，比如用join on代替子查询，left join代替not exist等
比如：

select * from user limit 10000000,10;
select * from user where id > 10000000 limit 10;

这两个sql在id自增的情况下查询效果是一致的，但是后者的效率明显更高。

6、存储引擎

6.1、存储引擎的选择

存储引擎要根据业务场景进行选择，不同存储引擎的特性已在第一篇中提到，这里不做赘述。

6.2、分区或分表

日期历史表：可以根据月份分为12个。
渠道交易表：可以分为日表，月表，历史表，历史表可以继续区分。

6.3、字段定义

原则：使用可以正确存储数据的最小数据类型。

6.3.1、整数类型

整数有六种类型，不同类型的最大存储范围是不一样的，占用的存储空间也不同。

6.3.2、字符类型

变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长度。
定长情况下，char更节省空间。

6.3.3、非空

非空字段尽量定义成not null，提供默认值，或者使用特殊值，空串来代替null。
null类型的存储，优化，使用都会存在问题。

6.3.4、不要用外键，触发器，视图

降低可读性，影响数据库性能，数据的完整性校验，计算都要放在程序中进行。

6.3.5、大文件存储

大文件（如图片，视频）尽量放在NAS服务器上，数据库不要存放这样的数据。

6.3.6、表拆分

将不常用的字段拆分出去，避免列数过多和数据量过大。

6.3.6、字段冗余

可以通过其他表获取到或者无用的字段应该去掉。

7、优化总结

数据库优化，可以从几个方面来考虑：

  1. SQL与索引
  2. 存储引擎与表结构
  3. 数据库架构
  4. MySQL配置
  5. 硬件与操作系统

7.1、服务端状态

如果连接变慢，查询阻塞，无法获得连接

  1. 重启！重启！重启！
  2. show processlist查看对应的线程状态，连接数量，连接时间，状态等。
  3. 查看锁的状态
  4. kill有问题的线程

7.2、问题SQL

对于查询缓慢的SQl，涉及到的表结构，字段的索引情况，每张表的数据量，查询的业务含义，只有分析清楚，才能写出正确的SQL。
除此之外，还要找到缓慢的原因，有以下几条思路：

  1. 查看执行计划，分析运行情况，了解访问顺序，访问类型，索引扫描行数等信息。
  2. 如果总体的时间很长，无法确定，那么去掉一部分条件，逐渐筛选出有问题的部分，不断尝试

分析到原因后，可以采用对应的方法，如：

  1. 创建索引或联合索引
  2. 小表驱动大表
  3. join代替子查询
  4. left join is null代替not exist
  5. or改成union
  6. 如果结果集允许重复，使用union all而非union
  7. 大偏移的limit，先过滤，后排序
  8. 拆分，去冗余，数值非空，架构优化
  9. 业务层需要考虑优化。
烟锁迷城
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL8.0版本新特性-全局变量修改
YoFog的博客
08-24 4926
MySQL8之前的版本，例如5.7，在修改全局变量时候，并不会影响到配置文件，重启之后就会恢复到默认值。今天看mysql参考手册时发现了新版本对于修改变量的新方法----set persist，可以使全局系统变量变为永久性。 https://dev.mysql.com/doc/refman/8.0/en/using-system-variables.html  以修改最大连接数----max_...
mysql set类型 查询,mysql 中set 类型详解
weixin_29461699的博客
03-17 1384
【SET类型】SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。所以SET成员值本身不能包含逗号。例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：'''one''two''one,two'SET最多可以有64个不同的成员，当创建表时，SET成员值的尾部空格将自动...
mysql 去重复查询 子查询
一个草根敲键盘的故事
06-27 2853
SELECT e.id, e.on_line, e.name, (SELECT d.longitude FROM sys_equipment_data d WHERE d.equipment_id = e.id ORDER BY d.transmission_time DESC LIMIT 1) AS longitude, (SELECT d.latitude FROM sys_equipment...
MySQL基础之流程控制（十一）
qq_43752469的博客
07-02 182
流程控制结构 顺序结构：程序从上往下依次执行。 分支结构：程序从两条或多条路径中选择一条去执行。 循环结构：程序在满足一定条件的基础上，重复执行一段代码。 一、分支结构 1.if函数 功能：实现简单的双分支。 语法： select if(表达式1，表达式2，表达式3) 执行顺序：如果表达式1成立，则if返回表达式2的值，反之返回表达式3的值。 应用：任何地方 2.case结构 情况1：类似于java中的switch语句，一般用于实现等值判断。 语法： case 变量|表达式|字段 when 要判断
MySQL engine层到server层字段过滤优化
雪辉博客
05-29 685
1.1 问题描述   执行计划的不同肯定会带来效率的不同，但是在本例中执行计划完全一致，都是全表扫描，不同的只有字段个数而已。其次，测试中都使用了where条件进行过滤（Using where），过滤后没有数据返回，常说的where过滤实际上是在server层，当然某些情况下使用ICP会提前在Innodb层过滤数据，这里不考虑ICP。   对于大数据量访问来讲可能涉及到物理IO，首次访问和随后的访问因为Innodb buffer的关系，效率不同是正常，需要多测试几次。   通过上面的测试，可以发现随着
MySQL 性能优化
瘦到被风吹走的博客
09-16 109
MySQL 性能优化前言表结构设计字段设计基本字段业务字段 前言 一开始标题命名的sql优化，后来改成了性能优化，之所以这样，是因为我们需要优化sql，是为了提高数据库系统性能，而如果仅仅着眼于sql，不去看表结构，业务逻辑，不评估数据量，不控制增删改查的复杂度与频繁程度，恐怕性能优化只能是隔靴搔痒 表结构设计 我们拿到一个需求，需要存储数据时，就会涉及到表结构设计 提到表结构设计，首先都会考虑，怎样以尽量少的表，尽量少的字段实现业务需求，同时也想保证一定的可扩展性，控制表的数据量级 然后就是具体的字段设
MySql5.6性能优化1
08-08
MySQL 5.6性能优化是数据库管理中至关重要的一环，其目标在于提升系统运行效率，减少资源消耗，增强系统的响应速度。优化不仅涉及查询层面，还包括数据库结构的优化以及MySQL服务器本身的调整。以下将详细讨论这些...
MySQL性能优化方向
12-14
MySQL性能优化是一个复杂而重要的任务，它涉及到多个层面，包括连接管理、线程处理、缓存策略等。本文将按照从大到小、从主要到次要的顺序，逐一解析MySQL性能优化的关键点。 首先，我们要关注的是**连接 ...
MySQL性能优化
01-31
此外，除了数据库索引之外，在LAMP结果如此流行的今天，数据库（尤其是MySQL）性能优化也是海量数据处理的一个热点。下面就结合自己的经验，聊一聊MySQL数据库优化的几个方面。 首先，在数据库设计的时候，要能够...
Mysql高级性能优化思维导图
最新发布
07-28
MySQL高级性能优化的关键点： 数据库设计和规范化：确保数据库表结构合理、字段类型正确，并进行适当的规范化，以减少数据冗余和提高查询效率。 索引优化：根据查询需求创建适当的索引，避免过多或不必要的索引，...
set global show_compatibility_56 = on；永久生效MySQL重启
weixin_34336526的博客
01-08 1553
1. 找到MySQL的配置文件，一般在MySQL的安装目录下，例如我的： C:\Program Files\MySQL\MySQL Server 5.7 ，打开下面的一个配置文件： my-default.ini ，在最后面添加一行配置： show_compatibility_56 = 1 。 2. 重启MySQL让设置生效。...
MySQL小误区：关于set global sql_slave_skip_counter=N 命令的一些点
lidan3959的专栏
07-18 1064
背景知识1：     在主从库维护中，有时候需要跳过某个无法执行的命令，需要在slave处于stop状态下，执行 set global sql_slave_skip_counter=N以跳过命令。常用的且不易用错的是N=1的情况，但N>1时，则不那么顾名思义，本文详细介绍N的意义，及使用注意事项。       背景知识2：     MySQL从库从主库上复制binlog文件内容到本地执行。
sql boolean类型_MySQL数据类型测试：集合类型SET测试数据总结（第七节）
weixin_39759918的博客
12-05 144
MySQL数据库提供针对字符串存储的一种特殊数据类型：集合类型SET，这种数据类型可以给予我们更多提高性能、降低存储容量和降低程序代码理解的技巧，前面介绍了首先介绍了四种数据类型的特性总结，其后又分别介绍了布尔类型BOOL或称布尔类型BOOLEAN、枚举类型ENUM（点这里查看之前的文章），本文我们详细介绍集合类型set测试过程与总结，加深对mysql数据库集合类型set的理解记忆。集合类型 SE...
Mysql SET（集合类型） 数据类型
热门推荐
wrh_csdn的博客
03-29 3万+
SET（集合类型） SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。set的容纳范围为64个不同的成员..set其实和枚举差不多，set指定了一个集合范围，在我们插入数据的时候，需要插入在set范围之内的元素，如果插入了未被包含的元素，那么就会发出警告..CREAT...
mysql的set类型很简单
牛儿吃草的博客
11-20 6576
创建set表 CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); 插入数据 INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,c,d'); 全局查询 SELECT col FROM myset; 表示第三个值即：c SELECT *
MySQL性能优化之配置优化（二）
延瓒
11-03 931
Mysql参数优化对于不同的网站，及在线量等，以及机器硬件的配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能的得到最佳的效果 为了测试我们创建一个test1的库创建一个tb1的表，然后导入2万行数据，脚本如下： #!/bin/bash HOSTNAME="localhost" PORT="3306" USERNAME="root" PASSWO...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值