Mysql-sql优化
一、分析sql
1.查看SQL执行频率
分析当前数据库是查询频繁的,还是写入频繁的,一般都是查比较多咯。
MySQL 客户端连接成功后,通过show [session|global] status
命令可以提供服务器状态信息。
show [session|global] status
可以根据需要加上参数session
或者global
来显示对应级别的统计结果。
如果不写,默认使用参数是session。
-
session:当前连接的统计结果
-
global:自数据库上次启动至今的统计结果
查询结果中的Com_xxx 表示每个xxx语句执行的次数,我们通常比较关心的是以下几个统计参数。
参数 | 含义 |
---|---|
Com_select | 执行 select 操作的次数,一次查询只累加 1。 |
Com_insert | 执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。 |
Com_update | 执行 UPDATE 操作的次数。 |
Com_delete | 执行 DELETE 操作的次数。 |
Innodb_rows_read | select 查询返回的行数。 |
Innodb_rows_inserted | 执行 INSERT 操作插入的行数。 |
Innodb_rows_updated | 执行 UPDATE 操作更新的行数。 |
Innodb_rows_deleted | 执行 DELETE 操作删除的行数。 |
Connections | 试图连接 MySQL 服务器的次数。 |
Uptime | 服务器工作时间。 |
Slow_queries | 慢查询的次数。 |
-
Com_
这些参数对于所有存储引擎的表操作都会进行累计。
-
Innodb_
这几个参数只是针对InnoDB 存储引擎的,累加的算法也略有不同。
示例:
2.定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句。
-
慢查询日志
通过慢查询日志定位那些执行效率较低的 SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
-
show processlist
慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候,慢查询日志也许并不能方便的定位问题。
可以使用show processlist命令查看当前MySQL正在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。
-
id列
用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
-
user列
显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
-
host列
显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
-
db列
显示这个进程目前连接的是哪个数据库
-
command列
显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect)等
-
time列
显示这个状态持续的时间,单位是秒
-
state列
显示使用当前连接的sql语句的状态,很重要的列。
state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
-
info列
显示这个sql语句,是判断问题语句的一个重要依据
3.explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
查询SQL语句的执行计划 :
字段 | 含义 |
---|---|
id | select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。 |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等 |
table | 输出结果集的表 |
type | 表示表的连接类型,性能由好到差的连接类型为 system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all |
possible_keys | 表示查询时,可能使用的索引 |
key | 表示实际使用的索引 |
key_len | 索引字段的长度 |
rows | 扫描行的数量 |
extra | 执行情况的说明和描述 |
3.1 准备工作
新建一个t_user、t_role、user_role表。
t_user表存储用户信息。
t_role表存储角色信息。
关系表user_role
嗯,比较常见的场景。一个用户可能有多个角色,一个角色也可能对应着多个用户。
3.2 explain 之 id
id 字段是 select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序,id 情况有三种 。
-
id 相同:表示加载表的顺序是从上到下
-
id 不同:id值越大,优先级越高,越先被执行。
-
id 有相同,也有不同,同时存在。
id相同的可以认为是一组,从上往下顺序执行。
在所有的组中,id的值越大,优先级越高,越先执行。
3.2.1 id相同
3.2.2 id不同
3.2.3 id既有相同也有不同
3.3 explain 之 select_type
表示 SELECT 的类型
常见的取值,如下表所示,从上往下效率越来越低。
select_type | 含义 |
---|---|
SIMPLE | 简单的select查询,查询中不包含子查询或者UNION |
PRIMARY | 查询中若包含任何复杂的子查询,最外层查询标记为该标识 |
SUBQUERY | 在SELECT 或 WHERE 列表中包含了子查询 |
DERIVED | 在FROM 列表中包含的子查询,被标记为 DERIVED(衍生) MYSQL会递归执行这些子查询,把结果放在临时表中 |
UNION | 若第二个SELECT出现在UNION之后,则标记为UNION ; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为 : DERIVED |
UNION RESULT | 从UNION表获取结果的SELECT |
3.3.1 SIMPLE
单表操作,不包含子查询。
3.3.2 PRIMARY、SUBQUERY
复杂查询,可以看到,子查询的被标记为了SUBQUERY,子查询外面的查询被标记为PRIMARY。
3.3.3 DERIVED
临时表
3.4 explain 之 table
展示这一行的数据是关于哪一张表的
3.5 explain 之 type
type 显示的是访问类型,是较为重要的一个指标,可取值为:
type | 含义 |
---|---|
NULL | MySQL不访问任何表,索引,直接返回结果 |
system | 表只有一行记录(等于系统表),这是const类型的特例,我们的业务系统中一般不会出现 |
const | 单表场景 表示通过索引一次就找到了,而且只返回了一条记录。 通常是主键或者唯一索引。 因为只匹配一行数据,所以很快(如将主键置于where中)。 |
eq_ref | join场景(区分上方const,一个是强调单表一个是强调join) 使用唯一索引或者使用主键的关联查询,关联查询出的记录只有一条。 常见于主键或唯一索引扫描 |
ref | 非唯一性索引扫描,结果可能有多个,返回匹配某个单独值的所有行。 本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)。 |
range | 只检索给定返回的行,使用一个索引来选择行。 where 之后出现 between , < , > , in 等操作。 |
index | index 类型只是遍历了索引树,通常比ALL 快,all 是遍历数据文件(index 与 all 的区别)。 |
all | 将遍历全表以找到匹配的行 |
结果值从最好到最坏以此是:
一般来说, 我们需要保证查询至少达到 range 级别, 最好达到ref 。
3.6 explain 之 key
possible_keys、key、key_len三列的信息如下。
key | 含义 |
---|---|
possible_keys | 显示可能应用在这张表的索引, 一个或多个。 |
key | 实际使用的索引, 如果为NULL, 则没有使用索引。 |
key_len | 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。 |
3.7 explain 之 rows
扫描行的数量。
3.8 explain 之 filtered
表示过滤条件对于结果集的预计过滤比例,以百分比形式表示。具体来说,它表示通过索引或其他条件过滤掉的行的比例。
-
如果
filtered
值为 100%表示过滤条件将返回结果集中的所有行,没有进行额外的过滤操作。
-
如果
filtered
值小于 100%则表示过滤条件只过滤掉部分行
通常情况下,filtered
值越接近 100%,表示查询条件更加精确,可以更有效地筛选出所需的结果,从而提高查询性能。相反,如果 filtered
值较低,可能意味着查询条件不够准确,需要扫描更多的行来获取结果,可能会导致性能下降。
需要注意的是,filtered
列的值仅提供一个估计值,并不代表实际的过滤效果。实际的过滤效果还受到索引、统计信息、数据分布等因素的影响。因此,仅凭 filtered
值来判断查询性能并不全面,还需要综合考虑其他因素。
3.9 explain 之 extra
其他的额外的执行计划信息,在该列展示 。
需要优化是的前面两个,保持的是后面using index。
extra | 含义 |
---|---|
using filesort | 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取, 称为 “文件排序”, 效率低。 |
using temporary | 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by; 效率低 |
using index | 表示相应的select操作使用了覆盖索引, 避免访问表的数据行, 效率不错。 |
4.show profile分析SQL
Mysql在5.0.37版本增加了show profile
和show profiles
的语句支持。
show profile可以帮助我们具体的了解sql花费的时间都在哪里。
4.1 @@have_profiling
查看是否支持profile
4.2 @@profiling
是否开启profiling
1开启,0关闭。
4.3 实践
随便执行条sql语句
之后执行profiles查看
定位到刚才这个查询的id是176,查看详情。
Sending data:
表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回个客户端。由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询中耗时最长的状态。
在获取到最消耗时间的线程状态后,MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。
例如,选择查看CPU的耗费时间。
5.trace分析优化器执行计划
MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
5.1 开关
打开trace , 设置格式为 JSON,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。
1.optimizer_trace
该变量用于启用或禁用优化器跟踪功能。通过将其设置为 "enabled=on"
,可以开启优化器跟踪。
优化器跟踪可以记录查询优化器的详细执行过程,包括优化器如何选择执行计划和执行操作的顺序。这对于分析和调优查询性能非常有用。
2.end_markers_in_json=on
表示在优化器跟踪结果中使用 JSON 格式,并在结果中包含标记信息,以便更清晰地区分不同的阶段和操作。
3.optimizer_trace_max_mem_size
该变量用于限制优化器跟踪信息的内存使用。通过设置合适的值,可以控制优化器跟踪所占用的内存大小。在上述示例中,将其设置为 1000000,表示最大使用 1000000 字节的内存空间。
通过设置这些变量,您可以启用优化器跟踪,并控制跟踪信息的内存使用。
请注意,启用优化器跟踪可能会对系统性能产生一定影响,因此在生产环境中应谨慎使用,并及时关闭跟踪功能以避免额外的资源消耗。
5.2 实例
执行一个sql语句
检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的。
二、优化sql
1.索引的使用
2.sql优化实例
__EOF__

本文链接:https://www.cnblogs.com/yang37/p/17501417.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库