MySQL｜性能调优 & SQL 优化

SmellyCat1024
170 阅读6分钟

MySQL 优化

MySQL数据库优化模型是个金字塔结构，优化维度有四个：硬件、系统配置、数据库表结构、SQL及索引。

从优化成本上: 硬件 > 系统配置 > 数据库表结构 > SQL及索引
从优化效果上: 硬件 < 系统配置 < 数据库表结构 < SQL及索引
对于SQL语句优化，是开发工程师最常见，也是效果最好、成本最低的一种优化方式。文章重点聚焦于SQL语句的优化。
优化思路

  • 定位慢查等有性能问题的SQL
  • 使用 explain , profile等工具分析性能SQL
  • 优化SQL

1 定位性能SQL

1.1 通过慢查询日志定位性能SQL

1.1.1 开启慢查询

配置 /etc/my.cnf 文件开启

[mysqld]
slow_query_log = 1  #1开启慢查询，0关闭
slow_query_log_file = /var/lib/mysql/slow-query.log   # 指定慢查询日志路径，默认名字为hostname_slow.log  
long_query_time = 1  # 查询时间>=1秒才记录日志，-   默认10s
log_queries_not_using_indexes = 1  # 1表示记录没有使用索引的 SQL 语句

** 重启MySQL服务 **

sudo service mysqld restart

检查配置生效

mysql> show variables like 'slow_query%';

1.1.2 慢查询日志内容

执行下列SQL

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

查询日志文件中记录SQL语句

# Query_time: 1.000246  // 执行时间，精确到ms
# Lock_time: 0.000000    // 使用锁执时间，精确到ms
# Rows_sent: 1     // 返回的数据行数
# Rows_examined: 0  // 扫描的数据行数
SET timestamp=1529485986;   // 执行SQL的时间戳
select sleep(1);  // SQL语句

1.1.3 慢查询日志分析工具

(1) mysqldumpslow

mysqldumpslow是MySQL官方自带的慢查询日志分析工具，但是所提供的功能较为基础

常用参数如下

-s：排序方式，值如下
c：查询次数
t：查询时间
l：锁定时间
r：返回记录
ac：平均查询次数
al：平均锁定时间
ar：平均返回记录书
at：平均查询时间
-t：top N查询
-g：正则表达式

获取访问次数最多的5个SQL语句

$ mysqldumpslow -s c -t 5 /var/lib/mysql/slow-query.log
Reading mysql slow query log from /var/lib/mysql/slow-query.log
Count: 15  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), apsara[apsara]@dc1487859883577.et2sqa
# Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
SET timestamp=N;
select * from db_user where name like 'S'
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), apsara[apsara]@dc1487859883577.et2sqa
# Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
use test;
SET timestamp=N;
select * from db_user where name like 'S'

按照时间排的top 5个SQL语句

mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log

按照时间排序且含有’like’的top 5个SQL语句

 mysqldumpslow -s t -t 3 -g "like" /var/lib/mysql/slow-query.log

(2) pt-query-digest

pt-query-digest是Percona公司制作的mysql慢查询分析工具，不仅可以分析slow log，还可以用来分析general log，binlog。

pt-query-digest slow.log

详细使用请参考 pt-query-digest操作指南。

1.2 PROCESSLIST 实时定位性能SQL

为了更加及时的发现当前的性能问题，可以利用 MySQL information_schema 数据库下的 PROCESSLIST 表来实现实时的发现性能问题 SQL。

1.2.1 processlist参数

输出示例

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  2 | root | localhost | qian | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

相关参数及含义

参数含义
Id连接标识符，可以使用函数connection_id()查看
User当前用户
Host发出语句的客户端主机名
db当前连接的数据库
Command当前连接执行的命令
State使用当前连接的sql语句的状态
Time当前状态持续的时间，单位是秒
Info执行的sql语句

1.2.1 常用processlist命令

按客户端 IP 分组，看哪个客户端的链接数最多

select client_ip, count(client_ip) as client_num 
from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info 
group by client_ip 
order by client_num desc;

查看耗时最长的10个正在执行的线程

select * from information_schema.processlist 
where Command != 'Sleep'
order by Time desc 
limit 10 ;

kill掉有问题的线

kill 45900 # 45900为show full processlist输出的Id列

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

select concat('kill ', id, ';')

from information_schema.processlist

where command != 'Sleep'

and time > 3*60

order by time desc;

2 分析SQL

2.1 使用explain分析SQL

2.2 使用profile分析SQL

对于一个存在性能问题的 SQL，profile命令可以度量查询处理各个阶段所消耗的时间，详情请见 PROFILE命令详解 。

3 优化SQL

3.1 基本原则

用小结果集驱动大的结果集 尽可能在索引中完成排序

只取出自己需要的列

用 exists 代替 in

select num from a where num in(select num from b)
-- 用下面的语句替换
select num from a where exists(select 1 from b where num=a.num)

使用连接查询（join）代替子查询
连接查询不需要像子查询一样在内存中创建临时表，再从临时表中过滤数据，从而加快查询速度.

3.2 索引

3.2.1 索引的利弊

  • 提高检索速率，降低IO成本。
  • 降低数据排序成本（B-Tree的有序性）
  • 提高更新操作的IO成本
  • 浪费空间。

3.2.2 什么时候用索引&怎么用索引

  • 较频繁的查询字段

  • 唯一性较强的字段

  • 更新频繁的字段不适合做索引

  • 应考虑在 where 及 order by 涉及的列上建立索引

  • 尽量让一个索引被多个query语句所利用，减少同一个表上索引的数量

  • 选择组合索引时离散程度大的列在前

3.1.3 索引失效的场景

  • 联合索引违反最左前缀原则
    如果索引了多列，要遵守最左前缀原则。最左前缀原则指的是查询要从索引的最左前列开始并且不跳过索引中的列。

  • 在索引列上做计算，函数，类型转换等操作

  • 错误的Like使用
    只有类似like ‘abc%’ 的like查询会走索引

  • 字符串不加单引号索引会失效

  • 如果条件中有or，只要有条件列没有索引，索引就会失效

  • or两边为>和<范围查询时，索引失效

  • 当查询条件为字符串时，使用<>或!=作为条件查询，有可能不走索引

  • 查询条件使用is null或is not null时，有可能不走索引

  • 查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效
    使用比较多的范围查询有in、exists、not in、not exists、between 等都会走索引

  • 查询条件使用not exists时，索引失效

  • order by 索引失效情况
    （1）order by 后面的条件，即使遵循联合索引的最左匹配原则，但是不加limit，索引失效（加了limit关键字， 会走索引）。
    （2）对多个索引进行order by，索引失效
    （3）不同的排序，索引失效（如果order by后面有一个联合索引的多个字段，它们具有相同排序规则，那么会走索引）

  • 如果mysql使用全表扫描要比使用索引快，则不会使用到索引

4 其他优化

4.1 数据库结构优化

使用可存下数据的最小的数据类型

使用简单数据类型，int在mysql的处理比varchar简单

尽可能使用not null定义字段

用int来存储日期时间以节省空间

  • 插入时使用UNIX_TIMESTAMP()将datetime转成int
  • 查询时使用FROM_UNIXTIME()将int转成datetime

用bigint来存储ip地址

  • 插入时使用INET_ATON()来将ip地址转成bigint
  • 查询时使用INET_NTOA()来将bigint转成ip地址

主键选择

  • 主键字段类型尽可能小
  • 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能

通常推荐采用数值类型做主键并采用auto_increment属性让其自动增长。

avatar
文章
阅读
粉丝