数据库编程（九）| MySQL优化

已于 2023-09-07 17:06:38 修改
阅读量48 收藏
点赞数
分类专栏： 数据库编程 文章标签： 数据库 mysql
于 2023-09-04 19:01:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_65180200/article/details/132661639
版权

SQL性能下降的原因

在程序的运行过程中，我们会发现这样的一个现象，随着程序运行时间的不断推移以及数据量越来越大，程序响应的时间逐渐变慢，程序变得卡顿，但最开始的时候并不是这样的，那是什么原因导致的呢？

性能下降的原因

  • 外部原因
    计算机磁盘空间占用较大，服务器cpu性能及内存不足
  • 内部原因
    程序员编写的sql语句有问题
    索引失效
    表关联太多
    服务器参数设置不合理

Sql读取顺序

代码编写的顺序

select->distinct->from->join->on->where->group by->having->order by->limit

Mysql读取顺序

from->on->join->where->group by->having->select->distinct->order by->limit

整体过程

  1. 先对多表进行关联，根据条件找出符合的记录
  2. 在符合的记录基础上进行where条件过滤
  3. 对筛选出的记录进行分组操作
  4. 分组完成后再进行having操作，过滤出满足条件的数据
  5. 对取出的记录进行排序
  6. 再按照分页条件取出要显示的数据

explain执行计划

Mysql 使用 explain 关键字可以模拟优化器执行 sql 语句，我们就能够知道 Mysql 会如何处理sql，可以根据 explain 的分析结果和Mysql 底层数据结构优化 sql。不同Mysql 版本可能有差别，但差别不会很大。

文章中需要的SQL文件 ：SQL文件下载

执行示例：

EXPLAIN SELECT hco.co_id,hco.patient_name 
FROM his_care_order hco LEFT JOIN his_care_order_item hcoi 
ON hco.co_id = hcoi.co_id

在这里插入图片描述

字段名含义
id编号
select_type查询类型
table表名
partitions分区
type类型
possible_keys可能用到的索引
key实际使用的索引
key_len实际使用索引的长度
ref表之间的引用
rows扫描的行数
filtered过滤
Extra额外的信息

id

id：select查询的序号，表示执行select操作时表的顺序。

id相同按顺序走

EXPLAIN SELECT * 
FROM
	`his_care_order` o,
	`his_care_order_item` i 
WHERE
	o.co_id = i.co_id

在这里插入图片描述
就是先执行表 “o” ，再执行表 “i”

id不同，序号大的先执行

EXPLAIN SELECT
	* 
FROM
	`his_care_order` o 
WHERE
	co_id =(
	SELECT
		co_id 
	FROM
	( SELECT co_id FROM `his_care_order_item` WHERE item_id = 'ITEM1465223982444838912' ) T 
	)

在这里插入图片描述
因为id=2 所以先执行表 “his_care_order_item” ，再执行表 “o”

id相同，不同同时存在

EXPLAIN SELECT * 
FROM sys_dept d,( SELECT dept_id FROM sys_user GROUP BY dept_id ) t 
WHERE
	t.dept_id = d.dept_id

在这里插入图片描述

因为id=2 所以先执行表 “sys_user” ，再根据 id相同按顺序走 执行表 “d”，最后执行子查询出来的表“t”

select_type

查询类型，主要用于区别普通查询、联合查询、子查询

simple：

简单select查询，查询中不包含子查询或union；

EXPLAIN SELECT * FROM `his_care_order`

在这里插入图片描述

PRIMARY

主键查询

EXPLAIN SELECT * 
FROM `his_care_order`
WHERE co_id =( SELECT co_id FROM `his_care_order_item` WHERE item_id = 'ITEM1465859053631700992' )

在这里插入图片描述

SUBQUERY

where条件包含了子查询

EXPLAIN SELECT * 
FROM `his_care_order`
WHERE co_id =( SELECT co_id FROM `his_care_order_item` WHERE item_id = 'ITEM1465859053631700992' )

在这里插入图片描述

DERIVED

from的表中包含子查询，被标记为derived（衍生），把子查询的结果放在临时表中

EXPLAIN SELECT * FROM `his_care_order` a,( SELECT co_id FROM `his_care_order_item` GROUP BY co_id ) b 
WHERE
	a.co_id = b.co_id

在这里插入图片描述

table

显示这一行的数据是来自于哪张表的

partitions

如果查询是基于分区的话，会显示查询访问的分区

type

访问类型，按性能从低到高依次排列为

  • ALL：全表扫描，一定要优化
EXPLAIN SELECT * FROM his_care_order

在这里插入图片描述

  • index：它和All都是扫描全表，但index是从索引中读取表，All是从硬盘中读取
EXPLAIN SELECT co_id  FROM his_care_order

在这里插入图片描述

  • range：只检索给定范围的行，key列显示使用了哪个索引,between and或in等查询
EXPLAIN SELECT co_id  FROM his_care_order where co_id > '2'

在这里插入图片描述

  • ref：非唯一性索引扫描，本质上也是一种索引访问
EXPLAIN SELECT * FROM his_care_order o,his_care_order_item i WHERE o.co_id=i.co_id

在这里插入图片描述

  • eq-ref：唯一性索引扫描，对于每个索引键只有一条记录与之匹配
EXPLAIN SELECT u.dept_id ,d.dept_id FROM  sys_user u, sys_dept d WHERE u.dept_id = d.dept_id

在这里插入图片描述

  • const：通过索引一次就找到了，常见于primary或unique索引查找
EXPLAIN SELECT * FROM his_care_order WHERE co_id = '2'

在这里插入图片描述

  • system：表中只有一行记录（系统表），很少出现
  • NULL：不需要访问表

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及的字段上若建立了索引则会列出来，但不一定被使用

key

它和possible_keys的关系，理论上应该用到哪些索引，实际上用到了哪些索引

EXPLAIN SELECT * FROM his_care_order WHERE co_id = '2'

在这里插入图片描述

因为查询的是主键 所以实际应用的是主键索引

EXPLAIN SELECT co_id FROM his_care_order

在这里插入图片描述

什么索引都没用到

EXPLAIN SELECT * FROM `his_care_order`

在这里插入图片描述

key_len

索引使用的字节数，key_len显示的值为索引字段的最大可能长度

测试
对 his_care_order 表创建一个复合索引
在这里插入图片描述
在这里插入图片描述

EXPLAIN SELECT * FROM his_care_order WHERE user_id =2 and patient_id = 'HZ1263653571396763648' AND patient_name = '唐太宗'

在这里插入图片描述

EXPLAIN SELECT * FROM his_care_order WHERE user_id =2 and patient_id = 'HZ1263653571396763648'

在这里插入图片描述

rows

根据表统计信息及索引使用情况，估算所需读取的记录行数

filtered

满足查询条件记录数量的比例，是百分比，不是具体的记录数，这个值越大越好，它依赖于统计信息，并不是很准确（这个值越大，过滤的数据就越多，扫描的行数就越少）

Extra

  • Using filesort（文件排序，表示mysql无法利用索引完成排序操作，这种情况需要优化）
  • Using temporary（使用了临时表保存中间结果，常见于order by和group by ，这种情况需要优化）
    当 extra 字段的值为 NULL 时，表示该查询没有额外的特殊信息需要显示或说明。换句话说，没有特定的附加操作或特殊的执行计划细节需要在 extra 中显示。

在查询执行计划中，extra 字段通常提供了关于查询优化器决策、查询执行方式、使用的索引、连接类型等信息。但并非每个查询都会产生非空的 extra 值，有些查询可能没有特殊的情况需要额外说明。

当 extra 值为 NULL 时，你可以认为查询没有引起特别注意的情况或优化器决策。这并不意味着查询有问题或性能不佳，只是表示没有额外的信息需要显示在 extra 字段中。

EXPLAIN SELECT * FROM sys_dept d,sys_user u WHERE d.dept_id = u.dept_id ORDER BY d.dept_id

在这里插入图片描述

对这种情况进行优化
在sys_user中添加dept_id索引
在这里插入图片描述

优化后的结果
在这里插入图片描述

Using index（如果同时出现Using where，表明索引用来执行索引键值的查找，如果没有出现Using where，表明索引用来读取数据而非执行查找）
Using where（使用了where过滤）
Using join buffer（使用了连接缓存）
impossible where（where子句的值总是false，不能用来获取记录，说明我们的查询条件是有问题的，也是需要改进）

优化技巧

Sql及索引优化

排序字段加索引

使用explain命令分析sql时得到type的值为index，表示该查询sql使用了索引Z
在这里插入图片描述
如果order by的字段没有索引，type的值变为ALL，即全表扫描
在这里插入图片描述

结论： 对查询进行优化，要尽量避免全表扫描，首先应考虑在where 及 order by 涉 及的列上建立索引

where条件中or两边的字段没有索引时尽量少用or

or两边的字段中，如果有一个不是索引字段会造成查询不走索引的情况

item_id字段建了索引，type的值为const，查询效率极高
在这里插入图片描述
co_id字段没有建索引，type的值为ALL，变成全表扫描
在这里插入图片描述

co_id字段建索引，type的值为index_merge，extra变成using union(primary,co_id);using where
在这里插入图片描述
index_merge 是一种 MySQL 查询优化器的策略，用于在查询涉及多个条件且没有单个索引能够满足全部条件时，通过合并多个索引的结果得到最终的查询结果。

extra 中的 using union(primary,co_id);using where 表示查询使用了 UNION 操作符将 primary 和 co_id 的结果集联合，并在联合结果上应用了 WHERE 子句进行进一步的筛选操作。

区分in与exists

什么时候用in，什么时候用exists？

当in里面子查询的数据少可以用in，例如：

SELECT * FROM sys_user u 
WHERE u.dept_id IN (SELECT dept_id FROM `sys_dept` WHERE STATUS=0)

如果外层查询的数据少，而内层查询的数据量大，则用exists。
注意exists返回的是true或false

SELECT * FROM `his_patient` a 
WHERE EXISTS (SELECT * FROM `his_care_order` b WHERE b.patient_id=a.patient_id)

总结： 外大内小 选 IN ；外小内大 选 EXISTS

不建议使用%前缀模糊查询

“%name"或者LIKE”%name%"，这种查询会导致索引失效而进行全表扫描，如下图

EXPLAIN SELECT * FROM	sys_dept WHERE dept_name LIKE '%内%'

在这里插入图片描述
查询的结果是全表扫描 ，现在对dept_name 添加索引 在继续测试
在这里插入图片描述
在这里插入图片描述
结果发现并没有变化

接下俩使用LIKE “name%”，发现type的值变为range

EXPLAIN SELECT * FROM	sys_dept WHERE dept_name LIKE '内%'

在这里插入图片描述

避免在where子句中对字段进行表达式操作

where条件中对all_amount字段进行计算，造成索引失效

已经对all_amount建立的普通索引

EXPLAIN SELECT * FROM his_care_order WHERE all_amount*2=200

在这里插入图片描述
建议改成：

EXPLAIN SELECT * FROM his_care_order WHERE all_amount=200/2

在这里插入图片描述

注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、< 等条件时，会造成后面的索引字段失效

使用JOIN优化

A left join B ，A表为驱动表，A right join B，B表为驱动表，而INNER JOIN，MySQL会自动找出数据少的表作用驱动表。

  1. 合理利用索引
    被驱动表的索引字段作为on的限制字段；
  2. 利用小表去驱动大表
    减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数

系统参数调优

公共参数

max_connections = 1000
#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%
左右  
sort_buffer_size = 2M
#查询排序时缓冲区大小，只对order by和group by起作用，
可增大此值为16M
open_files_limit = 10240
#MySQL打开的文件描述符限制，默认最小1024
connect-timeout = 10
#连接超时之前的最大秒数,在 Linux 平台上，该超时也用作等
待服务器首次回应的时间
wait-timeout = 28800
#等待关闭连接的时间
max_allowed_packet = 64M
# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB 字段一起工作时相当必要)， 避免超长SQL的执行有问题,默认值为16M
table_cache = 512
# 所有线程所打开表的数量. 增加此值就增加了mysqld所需要的文件描述符的数量
thread_stack = 192K
# 线程使用的堆大小. 此容量的内存在每次连接时被预留.MySQL 本身常不会需要超过 64K
thread_cache_size = 20
# 我们在 cache 中保留多少线程用于重用.当一个客户端断开连接后,如果 cache 中的线程还少于 thread_cache_size,则客户端线程被放入 cache 中.这可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能。通过比较Connections 和Threads_created 状态的变量，可以看到这个变量的作用根据物理内存设置规则如下：
# 1G —> 8
# 2G —> 16
# 3G —> 32
thread_concurrency = 8

#该参数取值为服务器逻辑CPU数量×2，在本例中，服务器有 2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8.设置 thread_concurrency的值的正确与否,对 mysql 的性能影响很大, 在多个 cpu(或多核)的情况下，错误设置了 thread_concurrency 的值, 会导致 mysql 不能充分利用多 cpu(或多核),出现同一时刻只能一个 cpu(或核)在工作的情况。 thread_concurrency 应设为 CPU 核数的 2 倍.比如有一个双核的 CPU, 那么thread_concurrency 的应该为 4; 2 个双核的cpu,thread_concurrency 的值应为 8,属重点优化参数

MyISAM引擎参数

MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。

key_buffer_size = 256M
#指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的key_buffer_size 可以设置较小，8MB 已足够 如果是以MyISAM引擎为主，可设置较大，但不能超过4G. 在这里，强烈建议不使用MyISAM引擎，默认都是用InnoDB引擎.注意：该参数值设置的过大反而会使服务器整体效率降低！
sort_buffer_size = 2M
#查询排序时所能使用的缓冲区大小。排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 2 ＝200MB，所以，对于内存在 4GB 左右的服务器推荐设置为 6-8M。
read_buffer_size = 1M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！如果机器内存是16G，可分配1MB用来做MyISAM表全表扫描的缓冲大小。当全表扫描需要时,在对应线程中分配.
join_buffer_size = 512
#联合查询操作所能使用的缓冲区大小，默认大小512KB，该参数对应的分配内存也是每连接独享。多表关联查询时，当JOINkey无索引时会用到join_buffer_size，此属性是在没有索引的情况下减少过多的表扫描而设计的。
read_rnd_buffer_size = 8M
#MyISAM 以索引扫描(Random Scan)方式扫描数据的buffer大小，如果程序中有很多order by语句，增大此属性值能够提升性能。
bulk_insert_buffer_size = 64M
#用来缓存批量插入数据的时候临时缓存写入数据。如果有大量数据插入可提高此参数值，默认是8M
myisam_sort_buffer_size = 64M
#MyISAM设置恢复表之时使用的缓冲区的尺寸，默认为8M，最小值4k。当在REPAIR TABLE 或用 CREATE INDEX 创建索引或ALTER TABLE过程中对MyISAM索引排序时分配的缓冲区
myisam_max_sort_file_size = 10G
#mysql重建索引时允许使用的临时文件最大大小，在windows系统中默认值为2G
myisam_repair_threads = 1
#如果该值大于1，在 Repair by sorting 过程中并行创建MyISAM 表索引(每个索引在自己的线程内).如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.这对于拥有多个 CPU 以及大量内存情况的用户,是一个很好的选择.
myisam_recover = DEFAULT
#Myisam_revocer控制了Myisam查找和修复错误的方式，DEFAULT--Mysql会尝试修复被标记为崩溃会没有干净关闭的表，除了修复，不会做任何事情;Backup--Mysql将数据文件备份到一个BAK文件中;FORCE--即使.MYD文件丢失的数据多余一行，恢复也会继续.
transaction_isolation = REPEATABLE-READ
# 设定默认的事务隔离级别.可用的级别如下:READUNCOMMITTED, READ-COMMITTED, REPEATABLEREAD,SERIALIZABLE，1.READ UNCOMMITTED-读未提交 2.READ COMMITTE-读已提交 3.REPEATABLE READ -可重复读 4.SERIALIZABLE -串行

InnoDB引擎参数

innodb_file_per_table = 1
# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
# 独立表空间优点：
# 1．每个表都有自已独立的表空间。
# 2．每个表的数据和索引都会存在自已的表空间中。
# 3．可以实现单表在不同的数据库中移动。
# 4．空间可以回收（除drop table操作外，表空间不能自已回收）
# 缺点：
# 1.单表增加过大，如超过100G
# 结论：
# 共享表空间在Insert操作上稍有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files

innodb_status_file = 1
#启用InnoDB的status file，便于管理员查看以及监控等

innodb_open_files = 2048
# 限制Innodb能打开的文件数量，如果库里的表特别多的情况，请增加这个。这个值默认是300

innodb_additional_mem_pool_size = 128M
#设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，默认是8M，数据库中表越多这个参数应设置得越大。所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

innodb_buffer_pool_size = 128M
#包括数据页、索引页、插入缓存、锁信息、自适应哈希索引、数据字典信息.InnoDB 使用一个缓冲池来保存索引和原始数据，不像MyISAM，这里你设置越大，你在存取表里面数据时所需要的磁盘 I/O 越少。在一个独立使用的数据库服务器上，你可以设置这个变量到服务器物理内存大小的80%，但不要设置过大，否则，由于物理内存的竞争可能导致操作系统的换页颠簸

innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64

innodb_data_home_dir = /usr/local/mysql/var/
#设置此选项如果你希望 InnoDB 表空间文件被保存在其他分区.默认保存在 MySQL 的 datadir 中.

innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend
#InnoDB将数据保存在一个或者多个数据文件中成为表空间.如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.其他情况下.每个设备一个文件一般都是个好的选择.你也可以配置InnoDB来使用裸盘分区

innodb_file_io_threads = 4
#用来同步 IO 操作的 IO 线程的数量. 此值在 Unix 下被硬编码为 4,但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.

innodb_thread_concurrency = 0
#在 InnoDb 核心内的允许线程数量,InnoDB 试着在 InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制,最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_flush_log_at_trx_commit = 1
#可以通过修改此参数的默认值，即批量提交事务IO的方式来提高数据库性能。但是当数据库crash的时候可能会丢事务。如设置为1，只要事务一提交，就会对Log buffer进行刷盘。

innodb_log_buffer_size = 8M
#用来缓冲日志数据的缓冲区的大小.当此值快满时, InnoDB将必须刷新数据到磁盘上.由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)

innodb_log_file_size = 500M
#事务日志大小.在日志组中每个日志文件的大小，你应该设置日志文件总合大小到你缓冲池大小的5%~100%，来避免在日志文件覆写上不必要的缓冲池刷新行为.不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.

innodb_log_files_in_group = 2
#在日志组中的文件总数，通常来说2~3是比较好的.

innodb_log_group_home_dir = /usr/local/mysql/var/
# InnoDB的日志文件所在位置. 默认是与innodb_data_home_dir参数值相同。

innodb_lock_wait_timeout = 50
#设置锁等待的时间默认是50s，一旦数据库锁超过这个时间就会报错，避免在资源有限的情况下产生太多的锁等待。

innodb_flush_method = O_DSYNC
#这个参数控制着innodb数据文件及redo log的打开、刷写模式.默认值是 “fdatasync”, 调用fsync()去刷数据文件与redo log的buffer；另一个是 “O_DSYNC”，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件；设为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

innodb_force_recovery=1
# 如果你发现 InnoDB 表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.从1 开始并且增加此值知道你能够成功的导出表。这个参数只能修改my.cnf配置文件，然后重启数据库实例，不能用命令行修改。

innodb_fast_shutdown
# 0，代表当MYSQL关闭时，Innodb需要完成所有full purge和merge insert buffer操作，这需要花费时间来完成；
# 1，是默认值，不需要完成full purge和merge insertbuffer操作，但是在缓冲池的一些数据脏页还是会刷新到磁盘；
# 2，表示不需要完成full purge和merge insert buffer操作 ，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务丢失，但是mysql在下次启动时，会执行恢复操作（recovery）

慢查询日志

Mysql的慢查询日志是Mysql提供的一种日志记录，它用来记录在Mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的sql语句会被记录到慢查询日志中。

开启日志

默认情况下，Mysql并不会开启慢查询日志，需要手动设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会降低服务器的性能。

进入mysql命令终端，执行下面的命令查看是否开启了慢日志，默认是OFF

show variables like '%slow_query_log%';

在这里插入图片描述
执行下面的命令开启慢查询日志

set global slow_query_log=1;

再执行show variables like ‘%slow_query_log%’;，看到value值变为ON
在这里插入图片描述
这样修改只对当前数据库有效，重启之后将恢复默认值，如果要永久生效必须修改Mysql配置文件。在my.cnf文件中添加以下内容

slow_query_log=1
slow_query_log_file=日志存放路径

开启了慢查询日志后，什么样的sql语句才会被记录到日志中？
执行下面的命令查看默认阈值

show variables like '%long_query_time%';

在这里插入图片描述
执行下面的命令设置慢查询阈值

set global long_query_time=5;

设置完成后，需要断开连接后才能生效。
使用下面的命令，查看记录了多少慢查询语句

show global status like '%slow_queries%';

在这里插入图片描述

分析日志

mysqldumpslow

使用mysqldumpslow命令对慢查询日志进行分析，命令参数及含义如下表

参数含义
-s按照哪种方式排序（al, at, ar, c, l, r, t), 默认按at排序
c访问计数
l锁定时间
r返回记录
t查询时间
al平均锁定时间
ar平均访问记录数
at平均查询时间
-t是top n 的意思，即返回多少条数据
-g可以跟正则表达式，大小写不敏感

例如

  1. 得到返回记录数最多的10条sql
mysqldumpslow -s r -t 10 /var/lib/mysql/192-slow.log

在Linux命令行输入指令
在这里插入图片描述
2. 得到访问次数最多的10条sql

mysqldumpslow -s c -t 10 /var/lib/mysql/192-slow.log
  1. 得到日志里含有’left join’的慢sql语句
mysqldumpslow -s t -g 'left join' /var/lib/mysql/192-slow.log

如果日志中sql语句太多，可结合|more使用，例如

mysqldumpslow -s c /var/lib/mysql/192-slow.log|more

show profiles

show profiles是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的参考。同样和慢日志查询一样，默认是处于关闭状态，并保存最近15次的运行结果。

查看当前版本是否支持

show variables like '%profiling%';

在这里插入图片描述
打开profile

set profiling = on;

随便运行几条sql语句，查看结果

show profiles;

在这里插入图片描述
执行下面的命令诊断sql

show profile cpu,block io for query 5;

在这里插入图片描述
当出现下面的数据时就要进行优化了

converting HEAP to MyISAM  #查询结果太大，内存放不下了，需要往磁盘上写

creating tmp tables #创建临时表，先copy数据到临时表，用完再删除

copying to tmp table on disk #把内存中的临时表复制到磁盘上

locked #被锁定
惊鸿化雨
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL
dvlinker的技术专栏
10-08 4万+
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL。
数据库编程（九）- MySQL优化
09-04
在数据库编程领域，MySQL是一个广泛使用的开源关系型数据库管理系统，尤其在Web应用程序中。本文将深入探讨MySQL的优化策略，以提升系统性能和效率。在实际应用中，数据表的设计、查询优化、索引策略、存储引擎的...
MySQL数据库编程
Hlr0726的博客
06-25 806
MySQL数据库编程主要涉及SQL语言的使用，以及如何通过编程接口（如JDBC、Python的MySQL Connector等）与数据库进行交互。本文将详细介绍MySQL数据库编程的各个方面，帮助读者掌握MySQL数据库编程的基本知识和技巧。本文详细介绍了MySQL数据库编程的各个方面，包括MySQL数据库基础、编程基础知识、高级特性等。通过本文的学习，读者可以掌握MySQL数据库编程的基本知识和技巧，并能够在实际项目中进行应用。同时，还需要注意不断学习和实践，以提高自己的编程能力和数据库管理水平。
数据库MySQL详解
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
07-24 18万+
全网最详细MySQL教程，2023持续更新中
面试不再尬聊的Mysql数据库优化方案
路漫漫其修远兮，吾将上下而求索
03-23 3万+
点赞是一种积极的生活态度！有支持才有动力！微信搜索公众号【达摩克利斯之笔】获取更多资源，文末有二维码！ 前言 数据库优化是一个老生常谈的问题，刚入门的小白或者工作N年的光头对这个问题应该都不陌生，你要面试一个中高级工程师那么他就想"哥俩好"一样那么粘，面试官肯定会问这个问题，这篇文章我们就和它哥俩好！而且这个问题就是一个送分题，数据库的优化方案基本就是那些，答案也都是固定的，大家只要好好...
MySQL数据库面试题（2020最新版）
热门推荐
ThinkWon的博客
03-10 121万+
文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别？InnoDB引擎的4大特性存储引擎选择索引什么是索引？索引有哪些优缺点？索引使用场景（重点）............
头歌MySQL数据库实训答案 有目录
个人博客地址https://sun98k.github.io/
12-04 25万+
计算机系统综合实训 头歌MySQL数据库实训平台作业，内容比较全比较多，内容仅供参考，如有错误部分希望联系我跟正，觉得有用记得点赞收藏。数据库1-MySQL数据定义与操作实战 数据库2-MySQL数据管理技术实战 数据库3-MySQL数据库系统设计实战 数据库4-层次、网状、关系模型实战
MySQL | JDBC连接数据库详细教程【全程干货】
做技术人 · 产优质博客 · 找好工作
05-28 2万+
如何使用JDBC连接MySQL数据库详细教程
mysql数据库介绍
柳小葱的博客
03-15 1万+
💜今天对mysql的一些基础概念进行讲诶横扫，如概念、特点、优势、发展历史等等。对之前内容感兴趣的同学可以参考👇：🎾让我们开始今日份的学习吧！
从零到上亿用户，我是如何一步步优化MySQL数据库的？（建议收藏）
冰河的专栏
08-19 3万+
冰河亲历的亿级流量下的MySQL优化实战，强烈建议收藏！！
【MySQL数据库 | 第一篇】 概述
fckbb的博客
05-30 1281
面向对象数据库模型的基本单位是对象，而不是表格，它能够处理更加复杂的数据类型，并且具有更高的灵活性和可扩展性。为基础的数据库管理系统。数据被组织成一个类似于树状结构的层次，只有子节点有一个及以上的父节点，根节点则没有父节点，叶子节点没有子节点。面向对象数据库是在关系型数据库的基础上发展而来的一种数据库模型，它使用对象、类和继承等概念来管理数据，并且支持面对对象的编程语言。网状型数据库是在层次型数据库的基础上发展而来，它解决了层次型数据库中父子节点必须严格相连的问题，采用节点互不干扰的方式，
PHP数据库编程之MySQL优化策略概述
12-19
在PHP数据库编程中，MySQL优化策略对于提升应用性能至关重要。MySQL作为PHP开发中常见的数据库管理系统，其性能优化能够显著改善应用程序的响应时间和资源消耗。以下是一些常用的MySQL优化技巧： 首先，规范SQL语句...
Linux下的MySQL数据库编程
07-23
Linux下的MySQL数据库编程内容简介：本书全面、深入地讲解Linux下的数据库开发技术，既涵盖理论基础，又囊括案例开发，既包括Linux操作系统和数据库技术的基础知识，又详细介绍MySQL数据库的安装/配置、查询优化、...
数据库01MySQL数据库开发基础篇-mysql数据库编程开发实训(基础篇)
06-28
- **SQL**（Structured Query Language）：操作关系型数据库的标准编程语言，用于创建、修改、查询数据库。 #### 四、数据模型与关系型数据库 - **关系型数据库**（Relational Database Management System, RDBMS）...
java笔记 mybatis 基本增删改
qq_63095129的博客
08-05 733
在pom.xml文件中添加依赖。
Python爬虫与MongoDB的完美结合
weixin_52392194的博客
08-03 820
我们掌握了在Windows和Linux系统下安装MongoDB，设计适合爬虫存储数据的写入规范，并通过实际爬虫示例展示了如何将爬取到的数据存储到MongoDB中。此外，还进行了MongoDB性能优化，提高了数据存储和查询的效率。
【MySQL】慢sql优化全流程解析
k123456kah的博客
08-04 1117
explain 是 MySQL 提供的一种用于分析和调试 SQL 查询的工具。 通过使用 explain，可以了解 MySQL 在执行查询时采用的具体执行计划，包括访问数据表的方式、使用的索引、连接表的顺序等信息。这些信息对于优化查询性能至关重要。
SpringBoot+Vue图书(图书借阅)管理系统-附项目源码与配套文档
m0_74283290的博客
08-03 1332
本论文阐述了一套先进的图书管理系统的设计与实现，该系统采用Java语言，结合现代Web开发框架和技术，旨在为图书馆提供高效、灵活且用户友好的资源管理解决方案。系统利用Spring Boot框架为核心，整合MyBatis ORM工具，以及MySQL数据库，构建了一个高性能、可扩展的后端服务。前端界面则采用了Vue.js框架，以提供流畅的用户交互体验。论文首先进行了详尽的需求分析，确定了系统的核心功能，包括图书的添加、编辑、删除、查询，读者的注册、登录、个人信息管理，以及图书的借阅、归还、续借流程。
Linux安全与高级应用（四）深入探索MySQL数据库：安装、管理与安全实践
最新发布
洛秋的博客
08-07 1132
Linux：作为操作系统，提供稳定的运行环境。Apache：作为Web服务器，处理HTTP请求。MySQL：作为数据库管理系统，存储和管理数据。：作为脚本语言，生成动态网页。LAMP平台的优势在于其成本低廉、可定制性强、易于开发、方便易用且安全稳定。这使得LAMP成为许多企业和开发者的首选平台。通过以上步骤，我们完成了LAMP平台的部署及其主要组件的配置和测试。LAMP平台的搭建不仅为Web开发提供了一个强大的环境，同时也展现了其在成本和效率上的优势。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值