Mysql —sql优化

向右看齐 2024-05-05 22:08 51阅读 0赞

## 1、什么是慢 SQL ##

### 1.1慢 SQL 危害 ###

前天业务老师叫我过去查看系统日志，晚上11.00的一般都会卡一下原因！

*  当出现慢查询，DDL 操作都会被阻塞，**也就是说创建表、修改表、删除表、执行数据备份等操作都需要等待**，这对实时备份重要数据的系统来说是不可容忍的
 *  **慢查可能会占用 mysql 的大量内存**，严重的时候会导致服务器直接挂掉，整个系统直接瘫痪
 *  慢 SQL 的执行时间过长，**可能会导致应用的进程因超时被 kill**，无法返回结果给到客户端
 *  **严重影响用户体验**，SQL 的执行时间越长，页面加载数据耗时也就越长

### 1.2永久开启慢 SQL 监控 ###

> 1.打开`my.cnf`配置文件，添加如下配置变量
> 
>     [mysqld]
>     slow_query_log = ON
>     slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
>     long_query_time = 1
> 
> 2.重启 mysql 服务器  
> systemctl restart mysqld

> **运行时间较长的 SQL 语句**
> 
> **日志的配置属性有 文件一般在**/etc/my.conf
> 
>  **slow\_query\_log = ON 是否开启 \[off—不在记录慢查询日志，下列的文件也不会存在\]  
> long\_query\_time = 5 慢查询设置时间 单位秒/s  
> slow\_query\_log\_file = /opt/soft/mysql/log/slow.log 慢查询记录日志路径  
> log\_queries\_not\_using\_indexes=on 未使用索引是否开启日志 【off—**不记录没有使用索引的查询语句到慢查询日志中**】**
> 
> **命令查看**
> 
>     show VARIABLES like '%slow_query_log%';
>     show VARIABLES like '%slow_query_log_file%';
>     show VARIABLES like '%long_query_time%';
>     show VARIABLES like '%log_queries_not_using_indexes%';

> \# Time: 2023-12-25T04:43:50.190877
> 
> \# User@Host: zwfwroot\[zwfwroot\] @ 19.15.0.30 \[19.15.0.30\] Id: 67508  
> \# Schema: gdqlk Last\_errno: 0 Killed: 0  
> \# Query\_time: 14.344185 Lock\_time: 0.000028 Rows\_sent: 237064 Rows\_examined: 237064 Rows\_affected: 0  
> \# Bytes\_sent: 96078006  
> SET timestamp=1534348804;  
> SELECT \* FROM \`test\_qry\`;
> 
> \-----------------------------------------------------------
> 
>  **日志内容详解说明：**
> 
>  *  Time：表示**客户端查询时间**
>  *  root\[root\]：表示客户端查询用户和IP
> 
>  *  Query\_time：sql执行的时间，**越长则代表越慢**
>  *  Lock\_time：mysql服务器阶段（不是在存储引擎阶段）**等待表锁的时间**
>  *  Rows\_sent：查询**返回的行数(结果集)**
>  *  Rows\_examined：查询**检查的行数 、扫描的行数**
>  *  SET timestamp=1534348804： sql的**执行起始时间** / 表示查询执行的时间戳

### 1.3我先从CPU查看的原因 当时升到60%多点 ###

在 MySQL 执行过程中，优化器可能会对我们即将要执行的 SQL 进行改造

*  1.根据搜索条件，找出 SQL 中所有可能使用的索引
 *  2.然后计算全表扫描的成本开销
 *  3.接着计算使用不同索引执行查询的成本开销
 *  4.最后会对比各种执行方案的成本开销，找出开销值最小的那一个

其中影响成本开销值的计算，**主要是`I/O成本`和`CPU成本`这两个指标**。

从**`I/O成本`视角**看：

*  当表的**数据量越**大，需要的 **I/O 次数也就越多**
 *  从**磁盘读取**数据比从缓存读取数据，I/O 消耗的**时间更多**
 *  **全表扫描比通过索引快速查找，I/O 消耗的时间和**次数更多

从**`CPU成本`视角**看：

*  当 SQL 中有排序、子查询等复杂的操作时，CPU 需要先把数据存到**临时表**中，再对数据**进行加工**，需要的 CPU 资源更多
 *  **全表扫描相比于通过索引快速查找，需要的 CPU 资源也更多**

## 2、慢查询日志分析 ##

### 2.1使用mysqldumpslow工具 ###

mysqldumpslow -s t -t 10 -g 'select' /home/data/log/slow.log
    
    -s t：表示按照总的执行时间进行排序，将执行时间较长的SQL语句排在前面。
    -t 10：表示只显示前10条慢查询语句。
    -g 'select'：表示只显示包含select关键字的慢查询语句。
    /home/data/log/slow.log ：指定慢查询日志文件的路径
    
    
    #####
    -s         表示按何种方式排序，支持的参数如下
                al: 平均锁定时间
                ar: 平均返回记录数
                at: 平均查询时间
                c: 访问次数
                l: 锁定时间
                r: 返回记录
                t: 查询时间
    -t NUM       返回前面多少条的数据
    -g PATTERN   后边搭配一个正则匹配模式，大小写不敏感

下述为截图展示！![dd16e7fda93447dd8eb807065e9f0f52.png][]

显示结果中含有字段及含义

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>Count:2583</td> 
   <td>Time=16.85s(265896s)</td> 
   <td>Lock=2s(343s)</td> 
   <td>Row=100(1265324)</td> 
  </tr> 
  <tr> 
   <td>查询返回的行数</td> 
   <td>执行的时间，括号是累计时间</td> 
   <td>锁定的时间，括号是累计时间</td> 
   <td>平均扫描行数，括号是累计总扫描行数</td> 
  </tr> 
  <tr> 
   <td colspan="4">一般类似代码review，代码/sql优化时期查看哪些sql查询速度较慢</td> 
  </tr> 
 </tbody> 
</table>

### 2.2开发中常用——Explain关键字 \[执行计划\] ###

> select \* from user\_remind\_record where wei\_xin\_unionid='jkdj34nfcjfj44fxcf'  
> ![b8227603c706484d8cc830ada43e8e07.png][]
> 
> 特别关注以下几个重要指标
> 
>  *  `type`：表示表的访问方式，常见的有 `ALL`（全表扫描）、`index`（索引扫描）等。尽量避免全表扫描，优先考虑使用索引等其他方式。
>  *  `key`：表示使用的索引列，如果为 `NULL` 则表示未使用索引。
>  *  `rows`：表示估计的结果集行数，可以用来评估查询的开销和效率。
>  *  `Extra`：表示额外的信息，如是否使用了临时表、是否进行了文件排序等。
> 
> [explain分析sql语句性能详解\_explain sql-CSDN博客][explain_sql_explain sql-CSDN]—— 可看详情
> 
> [查询（五）索引长度和索引区分度-CSDN博客][-CSDN] 理解 key\_len-索引长度，观察索引是否完全使用 ！针对复合索引 建议看看

## 3、经过sql查询、批次查询、排除后最终发现晚上的数据库备份时间是11：00 ##

>  **练习linux脚本：**
> 
> **1.写入命令 在文件test.txt 写入下列命令 后改名文件为test.sh**
> 
> **\#!/bin/bash  
> \# 获取前一天的日期  
> yesterday=$(date -d "yesterday" +"%Y-%m-%d")** 
> 
> **\# `-d` 参数用于指定日期，`yesterday` 表示昨天。`+%Y-%m-%d` 是指定输出的日期格式  
> \# 设置将要提取的日志文件路径  
> log\_file="/\*\*\*\*/\*\*\*\*/node.log"**
> 
> **\# 使用引号将路径字符串赋值给变量 `log_file`。引号的作用是将字符串作为一个整体进行处理，防止其中的特殊字符被解释或分割。  
> output\_file="/\*\*\*\*/out/$\{yesterday\}.log"  
> \# 提取包含 "test" 的整条日志到新文件中  
> grep '"test"' $log\_file | grep "$yesterday" > $output\_file**
> 
> \# 从 `$log_file` 指定的文件中查找包含 `"test"` 字符串并且日期为 `$yesterday` 的行，并将结果输出到 `$output_file` 文件中。
> 
>  *  第一个 `grep` 命令中使用了双引号将 `"test"` 字符串括起来，表示精确匹配该字符串；
>  *  第二个 `grep` 命令中使用了变量 `$yesterday`，用于匹配昨天的日期；
>  *  `>` 符号用于将命令的标准输出重定向到指定的文件 `$output_file` 中。
> 
> 注意，在 Shell 脚本中，变量赋值时等号两边不能有空格，否则会导致语法错误。
> 
> **2.运行**
> 
> **chmod +x test.sh \#添加权限**  
> \# `chmod +x text.sh` 会添加所有用户的执行权限，而 `chmod 1 text.sh` 只会给所有者添加执行权限。
> 
> `u`（所有者）、`g`（所属组）、`o`（其他用户）和 `a`（所有用户）
> 
> 每个权限对应一个数字（r 读取权限为 `4`，w 写入权限为 `2`，x 执行权限为 `1`）
> 
> **sed -i 's/\\r$//' test.sh**  
> \# 文件中的 Windows 换行符（`\r\n`）替换为 Unix 换行符（`\n`） `-i` 选项表示直接修改原文件，而不是输出到标准输出,`s` 表示进行替换操作，`\r$` 匹配行尾的 `\r` 字符，用空字符串替换即可去除该字符
> 
> **./test.sh \#** 在运行之前，你已经给该文件添加了可执行权限（使用 `chmod +x filename.sh` 命令）。
> 
> sh filename.sh 与 bash filename.sh 不需要提前给文件添加可执行权限
> 
> 后台运行脚本【终端关闭后仍然运行】 `nohup ./filename.sh &`。这将在后台运行脚本，并将输出重定向到当前目录下的 `nohup.out` 文件中  
> ==========================
> 
> **`sed` 是一种流编辑器**，用于对文本进行逐行处理。它主要用于**查找、替换和编辑文件中的文本**。以下是 `sed` 的简单示例：
> 
> `sed 's/pattern/replacement/g' file.txt`
> 
> 将在 `file.txt` 文件中查找所有匹配 `pattern` 的文本，并将其替换为 `replacement`。
> 
> `awk` 是一种强大的**文本处理工具**，它可以根据指定的规则对**文本进行分析和操作**。以下是 `awk` 的简单示例：
> 
> `awk '/pattern/ { print $1 }' file.txt`  
> `file.txt` 文件中查找包含 `pattern` 的行，并打印每行的第一个字段
> 
> ==================================
> 
> **练习定时任务**
> 
> Crontab 是 Linux 下的一个工具，它能够让用户根据**一定的时间周期去执行指定的任务或命令。**使用 Crontab 定时执行 Shell 脚本是常见的需求。  
> 1. Crontab 常用命令  
> crontab -e : 编辑当前用户的 Crontab。  
> crontab -l : 查看当前用户的 Crontab。  
> crontab -r : 删除当前用户的 Crontab。  
> service crond start : 启动 Cron 服务。  
> service crond stop : 停止 Cron 服务。  
> service crond restart : 重启 Cron 服务。
> 
> service crond reload //重新载入配置
> 
> service crond status //查看服务状
> 
> 2. Crontab 时间格式  
> 共五个星号，分别代表分钟、小时、日、月、周。其中 \* 表示任意值，也可以指定具体数值，例如：
> 
> \* ：每个对应的时间周期都执行。  
> 7 ：第七个时间周期执行。  
> \*/5 ：每 5 个时间周期执行一次。  
> 0-23 ：时间周期范围内的所有值都执行。
> 
> **使用以下特殊字符：**
> 
> 星号（\*）：代表所有可能的值，例如month字段如果是星号，则表示在满足其它字段的制约条件后每月都执行该命令操作。
> 
> 逗号（,）：可以用逗号隔开的值指定一个列表范围，例如，“1,2,5,7,8,9”
> 
> 中杠（-）：可以用整数之间的中杠表示一个整数范围，例如“2-6”表示“2,3,4,5,6”
> 
> 正斜线（/）：可以用正斜线指定时间的间隔频率，例如“0-23/2”表示每两小时执行一次。同时正斜线可以和星号一起使用，例如\*/10，如果用在minute字段，表示每十分钟执行一次
> 
> `若在第一个字段/第一列：0-23/2` 表达式表示每隔两分钟执行一次任务，包括 0 分、2 分、4 分等等，直到 23 分
> 
> [linux通过crontab命令定时执行shell脚本\_crontab执行shell脚本-CSDN博客][linux_crontab_shell_crontab_shell_-CSDN]
> 
> [【Linux】在服务器上创建Crontab（定时任务），自动执行shell脚本\_用shell脚本设定crontab命令-CSDN博客][Linux_Crontab_shell_shell_crontab_-CSDN]  
> [Linux下数据库备份 - 一拾三夏 - 博客园 (cnblogs.com)][Linux_ - _ - _ _cnblogs.com]

## 4、备份数据库 ##

mysqldump备份执行时**是会锁表的**，建议在凌晨没什么人操作数据库的时候执行备份操作。

#!/bin/sh
    
    # 数据库账号信息
    DB_USER="root"
    DB_PWD="root"
    DB_HOST="127.0.0.1"
    DB_PORT="3306"
    
    # MYSQL所在目录
    MYSQL_DIR="/home/mysql"
    # 备份文件存放目录
    BAK_DIR="/home/db_backup"
    # 时间格式化，如 20200902
    DATE=`date +%Y%m%d`
    # 备份脚本保存的天数
    DEL_DAY=7
    
    # 要备份的数据库，空格分隔
    DATABASES=("apolloconfigdb" "apolloportaldb" "apolloconfigdb-uat" "frame-assistor" "frame-authorization" )
    
    # 创建日期目录
    mkdir $BAK_DIR/$DATE
    
    echo "-------------------$(date +%F_%T) start ---------------" >>${BAK_DIR}/db_backup.log
    for database in "${DATABASES[@]}"
    do
      # 执行备份命令
      $MYSQL_DIR/bin/mysqldump --opt -u$DB_USER -p$DB_PWD -h$DB_HOST -P$DB_PORT ${database} > $BAK_DIR/$DATE/${database}.sql
    done
    
    echo "--- backup file created: $BAK_DIR/db_backup_$DATE.tar.gz" >>${BAK_DIR}/db_backup.log
    
    # 将备份好的sql脚本压缩到db_backup_yyyyMMdd.tar.gz
    tar -czf $BAK_DIR/db_backup_$DATE.tar.gz $BAK_DIR/$DATE
    
    # 压缩后，删除压缩前的备份文件和目录
    rm -f $BAK_DIR/$DATE/*
    rmdir $BAK_DIR/$DATE
    
    # 遍历备份目录下的压缩文件
    LIST=$(ls ${BAK_DIR}/db_backup_*)
    # 获取截止时间，早于该时间的文件将删除
    SECONDS=$(date -d "$(date +%F) -${DEL_DAY} days" +%s)
    
    for index in ${LIST}
    do
      # 对文件名进行格式化，取命名末尾的时间，格式如 20200902
      timeString=$(echo ${index} | egrep -o "?[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]")
      if [ -n "$timeString" ]
      then
        indexDate=${timeString//./-}
        indexSecond=$( date -d ${indexDate} +%s )
        # 与当天的时间做对比，把早于7天的备份文件删除
        if [ $(( $SECONDS- $indexSecond )) -gt 0 ]
        then
          rm -f $index
          echo "---- deleted old backup file : $index " >>${BAK_DIR}/db_backup.log
        fi
      fi
    done
    
    echo "-------------------$(date +%F_%T) end ---------------" >>${BAK_DIR}/db_backup.log

如果不希望备份时锁表，可以在mysqldump命令中添加参数--skip-lock-tables；

如果想备份所有数据库，则无需指定数据库，在mysqldump命令中添加参数--all-databases即可

## 5、MySql优化 ##

> 1.  **查询写明字段，不用\*代替**   
>     原因：1.节省资源，减少网络开销 2.\*可能造成回表查询，不再使用覆盖索引
> 2.  **查询结果只有一条或最大/最小一条记录，建议加上limit 1**  
>     原因：limit 1 在找到对应的记录后，就不再往下扫描，提高效率。但是where 条件后的 字段是唯一索引，可不加 limit 1。ep: where a='sdf' 这里a有唯一索引
> 3.  **避免使用 or 连接条件**  
>     原因：or 可能使索引失效。OR 运算符连接多个条件时，数据库需要对每个条件进行逻辑判断，并合并结果集。如果其中一个条件无法利用索引进行优化，整个查询可能会退化为全表扫描，导致性能下降  
>     **考虑**：1.改为union all 或子查询 2.建立合适的索引【 分析查询的字段和条件，评估创建合适的索引来支持查询优化。有时候创建复合索引、覆盖索引或者函数索引等可以提高查询性能】 3.索引提示使用 【确定某个查询需要使用特定的索引，可以使用索引提示来强制数据库使用指定的索引。但是要评估整体性能是否影响】  
>     select xx ,dd from tab1 use\_index(idx\_k1) where k='小白'; idx\_k1—索引名称，k—字段名/列名
> 4.  **优化limit分页**  
>     原因：偏移量大时，查询效率降低。  
>     考虑： 1. 排个序+索引 select id,name from tab1 order by **id** limit 10000,100; 2.上次查询最大记录/偏移量 where id >10000 limit 100; 3.按照业务讨论，比如时间倒叙，会看这么靠后的分页吗。绝大用户不会翻太多页码
> 5.  **优化like**  
>     原因：索引有个最左原则。 where a like '%123' 改为 where a like '213%'
> 6.  **where 条件限定查询的数据，避免返回多余的行数**  
>     原因：ep：是否有权限， 查询角色的对应人员主键集合，在判断。不如直接根据人员主键+角色看是否存在
> 7.  **尽量避免在索引字段上使用Mysql内置函数**  
>     原因：where date\_add(**log\_time** , interval 7 day) >=now(); 改为 where **log\_time>=**date\_add(now() , interval -7 day); //-7 代表减去7天
> 8.  **where 条件 字段尽量不表达式操作**。  
>     原因：造成放弃索引，改为全表扫描  
>     ep： where age -1 =10; 改为 where age =1;
> 9.  **使用联合索引时，注意索引列的顺序，一般遵循最左原则** 【假设索引idx\_id\_age(user\_id,age)】  
>     反例：select \*from user where age=10;  
>     正例：select\* from user where user\_id=34;
> 10. **对查询条件优化，考虑在where 和 order by 涉及的字段上建立索引，避免全表扫描**  
>     反例： select \*from user where address='铜锣湾' order by age;  
>     建立索引 alter table user add index idx\_add\_age(address,age);
> 11. **插入数据较多时，批量 或 分批次插入**  
>     原因：for循环中插入，每次一条数据，多次循环。效率低  
>     改造：1万次循环，改为每次插入1500条，7次就可插入完毕。而不是直接插入1万次。  
>     注意：inert into user (address,age) values (\#\{address\},\#\{age\}); 变为 values(address,age) values ('c1',12),('c2',34),('c5',46);
> 12. **慎用 distinct 关键字**  
>     原因：一般过滤重复数据，返回不重复的记录。查询字段少还行，多了 尝试 group by代替。
> 13. **删除冗余、重复的索引**  
>     一般针对联合索引，idx\_1(user\_id,age) ,索引idx\_2(user\_id),此时idx\_1 本质上包含idx\_2，故idx\_2可删除  
>     注意：组合索引 idx\_1(user\_id,age) 相当于 索引(user\_id) 与 索引 （user\_id,age）两个索引
> 14. **数据量较大，优化修改/删除语句**  
>     原因：避免同时修改/删除过多数据，造成因cpu利用率过高，影响他人对数的访问。还会提示 lock wait timeout exceed \[锁等待超时已经超过了阈值。这个错误通常是由数据库事务在等待锁的时间超过了数据库设置的最大等待时间而引起的\]  
>     提示：分批删除——分成多个批次，逐个批次删除； 优化删除语句——where 子句限制删除的行。 记住批量操作也会减少事务的大小
> 15. **表中某列考虑使用默认值替代null**  
>     原因：select \* from tab where age is not null; 不走索引，后者说mysql优化器解释走索引比不走索引成本还要高，就不走了

[dd16e7fda93447dd8eb807065e9f0f52.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/2f99cab8359941e08d6f928945c89f99.png
[b8227603c706484d8cc830ada43e8e07.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/05/f78063f0fa6e49888d9ddfbbde4bd093.png
[explain_sql_explain sql-CSDN]: https://blog.csdn.net/weixin_43811057/article/details/124883790
[-CSDN]: https://blog.csdn.net/w_t_y_y/article/details/111691128
[linux_crontab_shell_crontab_shell_-CSDN]: https://blog.csdn.net/qq_45473377/article/details/125504367
[Linux_Crontab_shell_shell_crontab_-CSDN]: https://blog.csdn.net/qq_48424581/article/details/132089325
[Linux_ - _ - _ _cnblogs.com]: https://www.cnblogs.com/jiachong/p/9441728.html