MySQL性能优化和慢查询日志

news/2024/4/30 3:40:48/文章来源:https://blog.csdn.net/yiqieruni/article/details/127617102

目标

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

讲解

1. 优化方案

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

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

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

1.2 如何优化数据库性能

在这里插入图片描述

优化性能的四个维护

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

四个维度对比：

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

要使用SQL及索引优化：

要先定位到执行慢的SQL语句：可以使用慢查询日志来定位
优化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()
BEGINDECLARE i INT DEFAULT 1;START TRANSACTION; -- 开启事务WHILE(i<=10000000)DOINSERT 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%';

在这里插入图片描述

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，进行针对性的优化