MySQL性能优化和慢查询日志

柔情只为你懂 2024-04-01 12:54 27阅读 0赞

#### 目标 ####

*  了解性能优化的方案
 *  能够使用慢日志定位慢SQL

#### 讲解 ####

##### 1. 优化方案 #####

###### 1.1 为什么要优化数据库性能 ######

MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。可以看到Google，Facebook，Twitter，百度，新浪，腾讯，淘宝，网易，久游等绝大多数互联网公司数据库都是用的MySQL数据库，甚至将其作为核心应用的数据库系统。

虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课。我们知道一般的应用系统，**读写比例在10:1左右**，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

###### 1.2 如何优化数据库性能 ######

![在这里插入图片描述][a2385fb6c8964229b0ddb91d7c161a0b.png]

优化性能的四个维护

*  硬件优化：更好的CUP、磁盘、网络、内存
 *  系统配置：优化操作系统的设置、数据库的配置优化
 *  数据库表结构：有良好的数据库设计，分库、分表、数据库集群、读写分离、冷热分离等等
 *  SQL及索引：编写良好的SQL，使用索引优化性能

四个维度对比：

*  效果：`SQL及索引效果最好 <-- 数据库表结构 <-- 系统配置 <-- 硬件优化最差`
 *  成本：`SQL及索引成本最低 --> 数据库表结构 --> 系统配置 --> 硬件优化成本最高`

要使用SQL及索引优化：

1.  要先定位到执行慢的SQL语句：可以使用慢查询日志来定位
2.  优化SQL，给相关表添加索引

##### 2. 慢查询日志的使用 #####

###### 2.1 准备千万级数据 ######

数据量太小的话，索引的作用体现的不明显。我们执行以下脚本，准备千万级数据,电脑性能不好的谨慎操作，可能会卡死，但是这种查询慢SQL以及优化速度等等，都要从大数据入手。

create database if not exists demo;
    use demo;
    
    -- 1. 准备表
    CREATE TABLE `user`(
    	id INT,
    	username VARCHAR(32),
    	`password` VARCHAR(32),
    	sex VARCHAR(6),
    	email VARCHAR(50)
    );
    
    -- 2. 创建存储过程，实现批量插入记录
    DELIMITER $$ -- 声明存储过程的结束符号为$$
    CREATE PROCEDURE auto_insert()
    BEGIN
        DECLARE i INT DEFAULT 1;
    	START TRANSACTION; -- 开启事务
        WHILE(i<=10000000)DO
            INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'com.demo'));
            SET i=i+1;
        END WHILE;
    	COMMIT; -- 提交
    END$$ -- 声明结束
    DELIMITER ; -- 重新声明分号为结束符号
    
    -- 3. 调用存储过程
    CALL auto_insert();

执行一下这条语句，看需要花费多少时间

SELECT * FROM USER WHERE id = 9999999;

###### 2.2 开启慢查询日志 ######

###### 1. 查看慢查询日志的配置 ######

show variables like '%query%';

![在这里插入图片描述][649de57b0e114ee5a3618c0908db3645.png]

###### 2. 开启慢查询日志 ######

# 1.开启慢查询日志；
    SHOW VARIABLES LIKE '%query%';
    
    #   1.1 设置慢查询的时间阀值，单位是：秒
    SET long_query_time = 1;
    #   1.2 开启慢查询日志的开关。1是开启，0是关闭
    SET GLOBAL slow_query_log = 1;
    #   1.2 慢SQL会被保存到slow_query_log_file指定的位置
    
    # 2.执行SQL，如果SQL执行慢，这个SQL语句会被记录到日志文件里
    SELECT * FROM USER WHERE id = 9999999;
    
    # 3.从日志文件里找到这个慢SQL，进行针对性的优化

###### 3. 查看慢SQL ######

打开慢查询日志文件，即可看到，执行慢的SQL语句被记录下来了

![在这里插入图片描述][83f3d0ff22ce4ab68cd2becddbe3e0c3.png]

###### 2.3 注意事项 ######

*  开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源、影响mysql的性能；但是可以阶段性/临时性开启，用来定位性能瓶颈。
 *  性能优化相关的sql语句，要全部**在cmd窗口里执行**，而不要在SQLyog里，因为SQLyog会修改我们的sql语句，在最后加上`limit 0, 1000`

#### 小结 ####

数据库优化：

1.  先使用慢查询日志，定位执行慢的SQL语句
    
    1.  设置慢SQL的时间阀值
    2.  开启慢查询日志的开关
    3.  执行SQL的时候，如果执行时间超过了时间阀值，这条SQL就会被记录到日志文件里
2.  针对执行慢的SQL，添加索引做优化

[a2385fb6c8964229b0ddb91d7c161a0b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c365aa768dde49469d7d20b53b90cbf2.png
[649de57b0e114ee5a3618c0908db3645.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/9ab18bd7c0bd4bd6a38ce6b78ac76e3e.png
[83f3d0ff22ce4ab68cd2becddbe3e0c3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5dce4e4901bb4457b6933157a042efa6.png