MySQL 数据库优化

作者：五分钟学大数据

2022 年 7 月 18 日
本文字数：1880 字
阅读完需：约 6 分钟

1. 为什么要进行数据库优化

避免网站页面出现访问错误
由于数据库连接 timeout 产生页面 5xx 错误 , to many connection
由于慢查询造成页面无法加载 : 由于数据库无法及时的返回数据, 导致页面一直无法返回
由于阻塞造成数据无法提交 :
增加数据库的稳定性
很多数据库问题都是由于低效的查询引起的
优化用户体验
流畅页面的访问速度
良好的网站功能体验

2. MySql 数据库优化

可以从哪几个方面进行数据库的优化？如下图所示：

SQL 及索引优化
根据需求写出良好的 SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解 sql 优化
数据库表结构优化
根据数据库的范式，设计表结构，表结构设计的好直接关系到写 SQL 语句。
系统配置优化
大多数运行在 Linux 机器上，如 tcp 连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。
硬件配置优化
选择适合数据库服务的 cpu，更快的 IO，更高的内存；cpu 并不是越多越好，某些数据库版本有最大的限制，ＩＯ操作并不是减少阻塞。

注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。

3. SQL 及索引优化

3.1 准备数据

在 mysql 官网中, 给我们提供了一个实例数据库, 直接将这个实例数据库导入即可

页面地址: https://dev.mysql.com/doc/sakila/en/sakila-installation.html

点击实例数据下载地址, 下载即可

关于 sakila-db.zip 压缩包所包含的文件如下解释

sakila-schema.sql文件包含创建Sakila数据库结构所需的所有CREATE语句，包括表、视图、存储过程和触发器。sakila-data.sql文件包含填充sakila-schema创建的结构所需的INSERT语句。以及必须在初始数据加载之后创建的触发器的定义。sakila.mwb文件是一个MySQL工作台数据模型，您可以在MySQL Workbench中打开它来检查数据库结构。

复制代码

将 sakila-db.zip 上传到 linux, 并解压

使用 rz 命令上传即可: 如果使用 rz 显示没有此命令, 输入 yum -y install lrzsz 下载即可

由于是一个 zip 文件, 需要下载 unzip 才能解压压缩包: yum -y install unzip

解压压缩包: unzip sakila-db.zip

在数据库中创建对应表和库

连接数据库: shell> mysql -u root -p123456导入数据:  mysql> SOURCE /root/sakila-db/sakila-schema.sql;

复制代码

导入数据

mysql> SOURCE /root/sakila-db/sakila-data.sql;

复制代码

验证是否成功

USE sakila;  # 使用数据库SHOW TABLES; # 查看所有表

复制代码

SELECT COUNT(*) FROM film;

复制代码

SELECT COUNT(*) FROM film_text;

复制代码

3.2 查看表结构

可通过 sqlyog 查看基本结构关系

3.3 如何发现有问题的 SQL

mysql 提供了慢查询日志查看功能, 可以帮助查询某一条 SQL 执行的一些状态

查询 mysql 是否开启慢查询日志

show variables like 'slow_query_log';

复制代码

开启 mysql 的慢查询日志:

show variables like 'slow_query_log'  ;//查看是否开启慢查询日志
set global log_queries_not_using_indexes=on; // 将不使用索引的慢查询日志进行记录set global slow_query_log=on;  //开启慢查询日志
show variables like 'slow_query_log_file';//查看慢查询日志存储的位置
set global long_query_time=1;  //大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉

复制代码

检测是否已经开启:

Show databases;Use sakila;select * from store;  // 执行查询select * from staff;  // 执行查询

复制代码

监听日志文件，看是否写入 : 注意后面的日志文件的路径需要通过查询slow_query_log_file参数

tail -100f /var/lib/mysql/node01-slow.log

复制代码

mysql 慢查询日志存储的格式说明:

说明：  1、# Time: 190412 22:05:02    >>>      查询的执行时间  2、# User@Host: root[root] @ localhost []  Id:     2 >>> 执行sql的主机信息  3、# Query_time: 0.001274  Lock_time: 0.000164 Rows_sent: 7  Rows_examined: 7   >>>> SQL的执行信息：      Query_time：SQL的查询时间      Lock_time：锁定时间      Rows_sent：所发送的行数      Rows_examined：所扫描的行数  4、SET timestamp=1555077861; >>>> SQL执行时间  5、select * from shop;  >>>>> SQL的执行内容

复制代码

如何通过慢查日志发现有问题的 SQL

查询次数多且每次查询占用时间长的 sql
通常为 pt-query-digest 分析的前几个查询；该工具可以很清楚的看出每个 SQL 执行的次数及百分比等信息，执行的次数多，占比比较大的 SQL
IO 大的 sql
注意 pt-query-digest 分析中的 Rows examine 项。扫描的行数越多，IO 越大。
未命中的索引的 SQL
注意 pt-query-digest 分析中的 Rows examine 和 Rows Send 的对比。说明该 SQL 的索引命中率不高，对于这种 SQL，我们要重点进行关注。