数据库优化

最新推荐文章于 2024-09-10 20:22:21 发布

原创最新推荐文章于 2024-09-10 20:22:21 发布 · 173 阅读

0 ·

CC 4.0 BY-SA版权

1. 优化的目的：

避免页面出现访问错误
- 数据库连接timeout产生5xx错误
- 慢查询导致页面无法加载（就是查询操作消耗了比较多的时间）
- 数据库阻塞导致数据无法提交
增加数据库的稳定性
- 很多的数据库问题都是低效的查询引起的
优化用户体验
- 提升浏览速度

2. 数据准备

准本一个数据库：https://blog.youkuaiyun.com/forever19911314/article/details/73527610

3. 优化一：慢查询日志

使用慢查询监控有效率的问题

显示是否开启了慢查询日志
show variables like 'slow_query_log'；
指定慢查询文件位置在哪里
set global show_query_log_file = '/home/mysql/sql_log/mysql-slow.log'
是否要把没有使用索引的sql记录到慢查询日志中
set global log_queries_not_using_indexes = on;
把大于1秒的查询记录在日志中
set global long_query_time  = 1

show variables like '%log%';
查看配置项
set global log_queries_not_using_indexes = on;
把没有使用索引的sql记录到慢查询日志中
show variables like 'long_query_time';
查看long_query_time的配置，并将它设置为0.000000

10秒

设置为0
开启慢查询
set global slow_query_log = on;

show variables like 'slow%';
查看日志存储目录

目录
日志格式

时间
# Time: 2018-12-06T08:13:23.876056Z
主机信息
# User@Host: root[root] @ localhost [::1]  Id:     7
sql执行信息
# Query_time: 0.003867  Lock_time: 0.003738 Rows_sent: 2  Rows_examined: 2
sql执行时间
SET timestamp=1544084003;
sql内容
select * from store limit 10;

慢查询分析工具

有问题的sql有哪些：

- 每次查询占用时间长的sql
    通常为pt-query-digest的前几个查询
- IO大的sql
    注意pt-query-digest分析的rows examine项
- 未命中索引的sql
    注意pt-query-digest分析的rows examine项和Rows send的对比

mysqldumpslow
mysql自带，linux可以直接使用，windows不行，需要安装个别的东西，

-t 3:显示top3的记录，后面跟着的是日志的目录位置
mysqldumpslow -t 3 /var/lib/mysql/zl-virtual-machine-slow.log | more

查询后的结果

pt-query-digest
效果比mysqldumpslow好一些

我使用的时候遇到了bug，不能用：
The pipeline caused an error: Pipeline process 5 (iteration) caused an error: Redundant argument in sprintf at /usr/bin/pt-query-digest line 2556.
网上说安装高版本的就好了，没有试。

sudo pt-query-digest /var/lib/mysql/zl-virtual-machine-slow.log | more

4.优化二：通过explain查询和分析sql的执行计划

就是在运行sql之前，运行一下explain sql，看一下这个sql好不好使

示例

explain返回各列的含义

table：显示这一行的数据是关于那张表的
type：重要，显示连接使用了何种类型，从最好到最差的连接类型为：const、eq_reg、ref、range、index、all
possible_keys：显示可能应用在这张表中的索引。空表示没有可能的索引。
key：实际使用的索引。null表示没有索引。
key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好。
ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数。
rows：MYSQL认为必须检查的用来返回请求数据的行数。

5 语句优化

5.1 max（利用索引）

比如查询一个max：

select max(payment_date) from payment

用explain分析一下：

explain select max(payment_date) from payment \G

分析结果：

rows达到了一万6
建立索引：

create index idx_paydate on payment (payment_date)

建立索引

再次explain分析一下：

rows变成了null

这时候再select max(payment_date) from payment就比原来优化了