Skip to content

数据库优化

概述

数据库优化目的

  • 避免出现页面访问错误
  • 数据库连接超时造成的5xx错误
  • 慢查询造成页面无法加载
  • 阻塞造成数据无法提交

数据库优化的方面

(从上到下成本递增,优化效果递减)

  • SQL及索引
  • 数据库表结构
  • 系统配置
  • 硬件

SQL语句优化

慢查询日志

慢查询日志的相关变量和格式

  • show variables like 'slow_query_log'
  • set global slow_query_log_file='/...'
  • set global log_queries_not_using_indexes=on; # 是否把没有索引的慢查询记录到日志里
  • set global long_query_time=1; # 超过多长时间的查询记录到慢查询日志中

慢查询日志的格式如下

# Time: 2020-05-24T08:44:10.573201Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.411528  Lock_time: 0.000365 Rows_sent: 200  Rows_examined: 339784
SET timestamp=1590309850;
select * from actor_info;

慢查询日志的分析工具

  • mysqldumpslow:随带mysql-server一起安装的一个工具。一般加 -t n,查询前n条查询。
  • pt-query-digest:可以输出到文件,也可以存到表中
$ pt-query-digest /var/lib/mysql/ubuntu18-slow.log | less

explain分析sql的执行计划

先看下面的例子

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列含义:

  • table:属于哪张表
  • type:连接的类型,从好到差依次是const, eq_reg, ref, range, index和ALL
  • possible_keys:可能用到的索引
  • key:实际使用的索引
  • key_len:使用的索引的长度。在不损失精度的情况下,长度越短越好
  • ref:显示索引的哪一列被使用了
  • rows:MYSQL认为必须检查的用来返回请求的数据的行数
  • extra:如果出现using filesort或using temporary时需要优化,后者是用临时表来存放结果

对count()和max()的优化

以上面的explain为例。payment_date本来是没有索引的。我们先给这一列建立索引。

mysql> create index idx_payment_date on payment(payment_date);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)

可以看到rows和extra这两项的变化

对子查询进行优化

一般情况下,需要把子查询优化为JOIN查询