By IceMimosa

更新日期:

文章目录
  1. Mysql性能优化
  2. 1. 优化的目的
  3. 2. 优化的思路
    1. 2.1 SQL语句优化
      1. 2.1.1 慢查询日志配置
      2. 2.1.2 慢查询日志组成
      3. 2.1.3 慢查询日志分析工具
      4. 2.1.4 explain语句
    2. 2.2 索引优化
    3. 2.3 数据库表结构的优化
    4. 2.4 系统配置的优化
    5. 2.5 服务器硬件优化

[TOC]

Mysql性能优化

1. 优化的目的

  • 避免页面出现5xx访问错误
1
2
3
1. 数据库连接可能timeout了
2. 慢查询导致页面无法加载
3. 事物阻塞造成数据无法提交
  • 增加数据库稳定性
1
很多时候的问题都是由于低效的sql查询引起的
  • 优化用户体验
1
2
1. 提升页面访问速度
2. 提升网站功能体验

2. 优化的思路

我们可以从SQL及索引，数据库表结构，系统配置，硬件的思路进行数据库优化。这个思路在金钱成本上是越来越高的，但是在优化效果上是越来越差的。

2.1 SQL语句优化

2.1.1 慢查询日志配置

查看是否开启慢查询，并打开

1
2
3
4
5
6
7
8
9
10
11
12
-- 是否打开慢查询日志
show variables like 'slow_query_log';
show variables like 'slow_query_log_file';
-- 记录慢查询的sql执行时间，单位为秒，默认10s
show variables like 'long_query_time'; 
-- 是否记录没有索引的查询
show variables like 'log_queries_not_using_indexes';

-- 打开相应设置
set global slow_query_log=on;
set global log_queries_not_using_indexes=on;
set long_query_time=0; -- 0表示都记录，这里仅用于测试

2.1.2 慢查询日志组成

查看slow_query_log_file配置对应的文件，比如如下：

1
2
3
4
5
# Time: 2018-03-26T15:19:37.449160Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    58
# Query_time: 0.003934  Lock_time: 0.000384 Rows_sent: 1  Rows_examined: 1020
SET timestamp=1522077577;
show variables like 'long_query_time';

对应的字段分别是：

  • 执行时间点
  • sql执行的主机信息
  • sql执行信息（时间，锁等待时间等）
  • 执行时间点和执行的sql语句

2.1.3 慢查询日志分析工具

当慢查询日志文件日积月累变得很大的时候，就需要分析工具来帮助我们进行慢查询的分析。

  • mysqldumpslow工具

mysqldumpslow是mysql官方提供的分析工具，详细可参考帮助文档mysqldumpslow -h。如执行mysqldumpslow -t 3 IceMimosa-slow.log

1
2
3
4
5
6
7
8
9
Reading mysql slow query log from IceMimosa-slow.log
Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  show variables like 'S'

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

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global log_queries_not_using_indexes=on
  • pt-query-digest工具

安装：可以在mysql/bin目录中执行如下命令：

1
curl -o pt-query-digest https://www.percona.com/get/pt-query-digest

可以查询pt-query-digest --help进行使用。如使用命令pt-query-digest -t 3 IceMimosa-slow.log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 250ms user time, 50ms system time, 27.19M rss, 4.10G vsz
# Current date: Mon Mar 26 23:52:44 2018
# Hostname: IceMimosa.local
# Files: IceMimosa-slow.log
# Overall: 10 total, 4 unique, 0.12 QPS, 0.00x concurrency _______________
# Time range: 2018-03-26T15:19:35 to 2018-03-26T15:20:56
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           14ms    27us     4ms     1ms     2ms     1ms     2ms
# Lock time            1ms       0   384us   111us   194us   112us   108us
# Rows sent              6       0       1    0.60    0.99    0.49    0.99
# Rows examine       5.98k       0    1020     612 1012.63  496.09 1012.63
# Query size           650      21      89      65   88.31   29.00   84.10

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ==============
#    1 0x7DD5F6760F2D2EBB  0.0136 98.3%     6 0.0023  0.00 SHOW VARIABLES
# MISC 0xMISC              0.0002  1.7%     4 0.0001   0.0 <3 ITEMS>

# Query 1: 0.38 QPS, 0.00x concurrency, ID 0x7DD5F6760F2D2EBB at byte 1264
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-03-26T15:19:36 to 2018-03-26T15:19:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         60       6
# Exec time     98    14ms     2ms     4ms     2ms     4ms   758us     2ms
# Lock time    100     1ms    97us   384us   186us   366us    90us   185us
# Rows sent    100       6       1       1       1       1       0       1
# Rows examine 100   5.98k    1020    1020    1020    1020       0    1020
# Query size    81     532      88      89   88.67   88.31    0.50   88.31
# String:
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
show variables like 'long_query_time'\G

我们需要重点关注SQL执行次数多，SQL执行时间长，未命中索引的SQL等。

2.1.4 explain语句

使用explain语句可以列出sql语句的执行计划，如语句如下：

1
explain select id, name from parana_items where id=1;
  • 输出

字段的意义：

  • table：执行的表
  • type：连接使用的类型。最好到最差的连接类型为：const，eq_reg，ref，range，index和ALL。
  • possible_keys：显示可能应用在这张表的索引，NULL表示没有可能的索引
  • key：实际使用的索引，NULL表示没有使用索引
  • ref：显示索引的哪一列被用了，可能是一个常数
  • rows：Mysql认为必须检查的用来返回请求数据的行数
  • extra：注意Using filesort和Using temporary，此时sql可能需要优化

示例

1
2
3
4
explain select max(pay_date) from payments;

* 如果pay_date没有索引的话，那么需要全表扫描
* 如果pay_date有索引的话，那么直接索引定位就能找出

2.2 索引优化

选择合适的列建立索引：

  • 在where从句，group by从句，order by从句，on从句中出现的列
  • 索引字段越小越好
  • 离散度大的列放到联合索引的前面

避免重复或冗余的索引，比如不用给主键ID，又加上了唯一索引。可以使用pt-duplicate-key-checker来校验是否存在重复索引。

2.3 数据库表结构的优化

1、 选择合适的数据类型

  • 使用可以存下你的数据的最小的数据类型
  • 使用简单的数据类型，如Int比Varchar在处理上更简单
  • 尽可能使用not null定义字段
  • 尽量少使用text类型，非用不可时最好考虑分表。

2、考虑数据库表的范式化和反范式化。

3、表的垂直拆分（将原来一个有很多列的表拆分成多个表）：

  • 把不常用的字段单独存放到一个表中。
  • 把大字段独立存放到一个表中。
  • 把经常一起使用的字段放在一起。

4、表的水平拆分（解决单表数据量过大的问题 ）

2.4 系统配置的优化

1、操作系统的配置优化

比如增加TCP支持的队列数、增加/etc/security/limits.conf的打开文件数限制、关闭防火墙等。

2、Mysql配置优化

  • 查找配置文件的顺序
1
2
3
4
mysqld --verbose --help | grep -A 1 'Default options'

# 输出
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

可以借助 http://tools.percona.com/wizard (需要登录)进行详细的配置和参考。

2.5 服务器硬件优化

可以从cpu，内存，磁盘，网络IO等方面考虑。

文章目录
  1. Mysql性能优化
  2. 1. 优化的目的
  3. 2. 优化的思路
    1. 2.1 SQL语句优化
      1. 2.1.1 慢查询日志配置
      2. 2.1.2 慢查询日志组成
      3. 2.1.3 慢查询日志分析工具
      4. 2.1.4 explain语句
    2. 2.2 索引优化
    3. 2.3 数据库表结构的优化
    4. 2.4 系统配置的优化
    5. 2.5 服务器硬件优化