Mysql 优化从基础到落地一节

最新推荐文章于 2025-07-17 22:58:22 发布

原创最新推荐文章于 2025-07-17 22:58:22 发布 · 367 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql 优化从基础到落地一节

数据库同时被 2 个专栏收录

33 篇文章

订阅专栏

Mysql

28 篇文章

订阅专栏

本文深入探讨Mysql慢查询的定义与作用，详细介绍慢查询日志的配置与分析，包括启动、配置参数、记录规则及分析工具mysqldumpslow的使用。同时，文章覆盖了数据库设计的三大范式及其在减少冗余中的应用，并提供了多种慢查询优化策略，如服务器硬件升级、Mysql服务器优化、SQL语句优化、反范式设计和索引优化等。

Mysql 优化从基础到落地一节

● 慢查询，找到值得优化的 SQL

慢查询的定义以及作用：

慢查询日志，顾名思义   就是查询慢的日志，是指 Mysql 记录所有执行超过 long_query_time 参数设定的时间的阀值的 SQL 语句的日志。该日志能为 SQL 语句的优化带来很好的帮助。默认情况下慢查询日志是关闭的，要使用慢查询日志功能首先要开启慢查询的日志功能

启动慢查询：

● 常用配置

show_query_log                             启动停止慢查询
show_query_log_file                      指定慢查询日志的存储路径及文件（默认和数据文件放在一起）
long_query_time                            指定记录慢查询日志 SQL 执行时间的阀值（单位秒，默认 10秒）
log_queries_not_using_indexes    是否记录未使用的索引的 SQL
log_output                                      日志存放的地方 [table]   [file]    [file, table]

● 记录符合条件的 SQL

查询语句
数据修改语句
已经回滚的 SQL

下面是其中的几条慢查询日志

（单位毫秒）

query_time           :查询时间
lock_time              :执行获取锁的时间
row_sent              :获得结果时间
row_examined     :扫描的行数
set_ timestamp    :sql 执行的具体时间

慢查询分析工具 mysqldumpslow

● 常用的慢查询日志分析工具 mysqldumpslow

汇总除查询条件以外完全相同的 SQL, 并将分析结果按照参数中指定的顺序输出

● 语法

mysqldumpslow -s r -t 10 slow-mysql.log

●  -s   排序参数 ： c,t,l,r,at,ai,ar
   c            :总次数
   t            :总时间
   l            :总次数
   r            :总数行数
   at, al, ar / t,l,r   :平均数     at = 总时间 / 总次数

●  -t   指定取前面几天 作为结果输入：

这个需要安装 perl 来运行 mysqldumpslow.pl

perl 下载地址

#以总时间排序，取出前5条

C:\Program Files\MySQL\MySQL Server 8.0\bin>perl mysqldumpslow.pl -s t -t 5 C:\WIN-7781FQVPL6U-slow.log

慢的 SQL 语句已经找到出来了，接下来我们来优化慢的 SQL

数据库设计的三大范式

三大范式的主要作用：减少冗余

● 第一大范式

数据库表中的所有字段都只具有单一属性

单一属性的列是由基本数据类型所构成的

设计出来的表都是简单的二维表

● 第二大范式

要求表中只有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系； （每一行数据需要有一个主键来区分）

● 第三大范式

指每一个非主键属性即不能部分依赖也不传递依赖与主键。（属性之间不要有关联）

常用的慢查询优化方式：

● 服务器硬件

SSD 比 HDD 高 10倍左右

● Mysql 服务器优化

linux 随着时间推移会比 window 稳定而且系统效能更高

● SQL 本身优化

子查询优化为关联查询

● 反范式设计优化

反范式 是针对范式而言的

所谓的 反范式优化 就是为了 性能和读取效率考量而违反数据库设计范式进行的优化

允许存在 少量的冗余 来换取 缩短查询时间。换句话说使用空间来换取时间

● 索引优化

索引的定义：

索引（index）是帮助 Mysql 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构

索引的分类：

普通索引：一个索引只包含单个列，一个表可以包含多个单列索引                                idnex
唯一索引：索引列的值必须唯一，     但是允许为 null                                                      unique
主键索引：主键索引是在创建主键的时候，自动添加的索引唯一且不能为 null            pramery key
复合索引：一个索引包含多个列

执行计划：

执行计划是什么：

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句；从而知道是如何处理你的 MYSQL 语句的。分析你的查询语句或者表的结构性瓶颈

语法：

Explain + SQL语句

● Explain 看是否用到了索引； key 为 Null 则没有使用到索引， ley_len 索引算法
    key_len       算法

    类型                           varchar                      char

    字符编码                      utf8                           3

    字符本身长度         
● 索引是否充分使用， ley_len 的大小，数值越大说明索引利用的月充分

● Type 字段： type system > const > eq_ref > ref > range > index > ALL
 ●  ALL        全表扫描                     性能最低 
 ●  Index      全表扫描（索引表）