Mysql数据库优化

第一章 数据库优化简介

1-1 数据库优化的目的

避免出现页面访问错误

• 由于数据库连接timeout产生页面5xx错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据无法提交

增加数据库的稳定性

• 很多数据库问题都是由于低效的查询引起的

优化用户体验

• 流畅页面的访问速度
• 良好的网站功能体验

第二章 SQL语句优化

2-1 数据准备

以下演示使用MySQL提供的sakia数据库，可通过如下URL获取这个演示数据库：
获取演示数据库sql文件

2-2 Mysql慢查日志的开启方式和存储格式

1.通过如下命令查看是否开启了慢查日志

2.确认没有使用索引的sql记录到慢查日志中

show variables like '%log%';

3.记录未使用索引的查询，将值改为on

set global log_queries_not_using_indexes=on;

4.查看慢查询日志记录设置的查询时间

show variables like 'long_query_time';

上图说明超过10ms的sql会被记录到查询日志中
出于演示，我们将它改为0，所有sql都记录到查询日志中！

执行如下命令，修改记录的查询时间：

set global long_query_time=0;

5.开启慢查日志

set global slow_query_log=on;

6.测试查询是否记录到慢查日志中

use sakila; //打开测试的数据库
show tables;//查看所有表
select * from store;//执行一条查询
show variables like '%slow%';//查看一下慢查日志记录的存储位置
+-----------------------------+--------------------------------------+
| Variable_name               | Value                                |
+-----------------------------+--------------------------------------+
| log_slow_admin_statements   | OFF                                  |
| log_slow_extra              | OFF                                  |
| log_slow_replica_statements | OFF                                  |
| log_slow_slave_statements   | OFF                                  |
| slow_launch_time            | 2                                    |
| slow_query_log              | ON                                   |
| slow_query_log_file         |/var/lib/mysql/8ed58be4faed-slow.log  |
+-----------------------------+--------------------------------------+
7 rows in set (0.00 sec)

7.打开慢查日志文件

exit//退出mysql
tail -50 /var/lib/mysql/8ed58be4faed-slow.log//打开指定目录的文件
# Time: 2021-09-23T07:32:06.044022Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000139  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:06.044402Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000274  Lock_time: 0.000053 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:06.044786Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000305  Lock_time: 0.000051 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:06.044924Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000059  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:06.045311Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000260  Lock_time: 0.000050 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:06.045464Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000031  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1632382326;
;
# Time: 2021-09-23T07:32:09.897381Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000838  Lock_time: 0.000460 Rows_sent: 23  Rows_examined: 76
SET timestamp=1632382329;
show tables;
# Time: 2021-09-23T07:33:07.012721Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000210  Lock_time: 0.000087 Rows_sent: 2  Rows_examined: 2
SET timestamp=1632382387;
select * from store;
# Time: 2021-09-23T07:35:01.654249Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.001417  Lock_time: 0.000140 Rows_sent: 7  Rows_examined: 7
SET timestamp=1632382501;
show variables like '%slow%';
# Time: 2021-09-23T07:37:14.932809Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 0.000027  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 7
SET timestamp=1632382634;
# administrator command: Quit;

从上面可以看出刚刚执行的sql语句和一些命令都被记录下来了
慢查日志所包含的内容：

1. 执行SQL的主机信息
   # User@Host: root[root] @ localhost [] Id: 17
2. SQL的执行信息
   # Query_time: 0.001417 Lock_time: 0.000140 Rows_sent: 7 Rows_examined: 7
3. SQL 执行时间
   SET timestamp=1632382387;
4. SQL内容
   select * from store;

2-3 MySql慢查日志分析工具之mysqldumpslow

1.在终端使用如下命令，查看命令的帮助信息

mysqldumpslow -h

Option h requires an argument
ERROR: bad option

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

2.筛选出慢查日志的前三条记录

mysqldumpslow -t 3 /var/lib/mysql/8ed58be4faed-slow.log

下面是分析结果：

Reading mysql slow query log from /var/lib/mysql/8ed58be4faed-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global slow_query_log=on

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=7.0 (7), root[root]@localhost
  show variables like 'S' 

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=6.0 (6), root[root]@localhost
  show databases

分析结果的参数解释：

参数	解释
Count	SQL的执行次数
Time	SQL的执行时间
Lock	SQL的锁定时间
Rows	SQL发送的行数
root[root]@localhost	SQL的执行者及服务器地址
sql语句	sql的执行内容

2-4 如果通过慢查日志发现有问题的sql

1.查询次数多且每次查询占用时间长的SQL

第三章 索引优化

第四章 数据库结构优化

第五章 系统配置优化