01.MySQL慢查询

1.1 什么是mysql慢查询

  • MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。
  • 运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。
  • long_query_time的默认值为10，意思是记录运行10秒以上的语句。

1.2 诊断慢查询

  • 开启慢查询日志
1
2
3
4
5
6
7
8
9
-- 查看当前配置
-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIPS LIKE 'long_query_time';

-- 动态开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 阈值设为1秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
  • 分析工具
    • mysqldumpslow（MySQL自带）
    • Percona Toolkit（高阶分析）

1.2 查找那些语句慢

1
2
3
4
5
6
7
8
9
10
11
# 比如，得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

1.3 explain分析慢查询原因

1.3.1 explain执行计划分析

  • 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
  • EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
  • explain的执行效果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from subject where id = 1 \G
******************************************************
           id: 1
  select_type: SIMPLE
        table: user             # 表示访问那个表
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY           # 使用了主键索引，如果为null代表没有使用索引
      key_len: 4                # 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
          ref: const
         rows: 1
     filtered: 100.00             # 查询的表行占表的百分比
        Extra: NULL
******************************************************
  • 关注type列： index/rang优于全表扫描
  • 检查key列： 是否民中索引
  • rows列： 扫描行数越少越好

1.3.2 索引设计原则

1.3.2.1 覆盖索引： 索引包含查询锁需要的字段

1
2
3
ALTER TABLE orders ADD INDEX idx_user_product (user_id, product_id);
-- 查询时直接使用索引
SELECT user_id, product_id FROM orders WHERE user_id = 100;

1.3.2.2 最左前缀原则： 复合索引按字段顺序生效

1
-- 索引 (A,B,C) 对 WHERE A=1 AND B>2 有效，但对 WHERE B>2 无效

1.3.2.3 避免冗余索引：删除未使用的索引

1
2

SELECT * FROM sys.schema_unused_indexes;  -- 需启用performance_schema

1.3.3 索引失效的场景

  • 对索引字段进行运算：
  • 使用LIKE “%keyword” 左模糊查询
  • 隐式类型转换： where id = “100” (id为整型)

1.4 SQL语句优化

1.4.1 避免全表扫描

  • **禁用SELECT * **， 明确指定字段
  • 分页优化
1
2
3
4
5
6
7

-- 低效写法（偏移量过大）
SELECT * FROM logs LIMIT 10000,12;

-- 高效写法（基于ID游标）
SELECT * FROM logs  where  id >100000 LIMIT 10000,12;

1.4.2 JOIN优化

  • 小表驱动大表
1
2
3
4

SELECT * from users u 
join orders o 
on  u.id=o.user_id
  • 避免笛卡尔积： 确保JOIN条件准确, ON后面一定要有条件！！

1.4.3 子查询改写: 将IN子查询改为JOIN

1
2
3
4
5
--- 低效
select * from users where id in (select user_id in orders);

--- 高效
select * from users u join orders o ON u.id = o.user_id;

1.4.4 批量操作

1
INSERT INTO logs (message) VALUES ('msg1'), ('msg2'), ('msg3');

1.5 配置调优

1.5.1 InnoDB缓冲池

1
2
# 配置为物理内存的70%-80%
innodb_buffer_pool_size = 16G

1.5.2 连接池管理

1
2
3
max_connections = 500        # 最大连接数
thread_cache_size = 50       # 线程缓存
wait_timeout = 600           # 空闲连接超时

1.5.3 临时表优化

1
2
tmp_table_size = 64M         # 内存临时表上限
max_heap_table_size = 64M    # 内存表大小限制

__END__