Sep 03 2018

MySQL 8.0查询性能优化实战：索引优化、执行计划分析和慢查询调优技巧

作者： 绝缘体..   发布： 2018-09-03 11:13   分类： 未分类   阅读：   抢沙发  
 
更多

MySQL 8.0查询性能优化实战：索引优化、执行计划分析和慢查询调优技巧

引言

在现代互联网应用中，数据库性能直接影响着用户体验和系统稳定性。MySQL作为最受欢迎的关系型数据库之一，在处理高并发读写场景时，查询性能优化显得尤为重要。MySQL 8.0版本带来了许多新特性，包括改进的查询优化器、新的存储引擎特性以及更强大的性能监控工具，为开发者提供了更多优化手段。

本文将深入探讨MySQL 8.0环境下的查询性能优化实战，从索引设计原则到执行计划分析，再到慢查询优化技巧，通过真实案例展示如何将查询性能提升数倍，为高并发业务场景提供切实可行的解决方案。

一、索引优化基础理论与实践

1.1 索引类型与选择原则

在MySQL 8.0中，索引是提升查询性能的核心机制。合理设计索引能够将查询时间从秒级降低到毫秒级。

主要索引类型

-- B-tree索引（默认索引类型）
CREATE INDEX idx_user_email ON users(email);

-- 哈希索引（InnoDB存储引擎支持）
CREATE TABLE test_hash (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name USING HASH (name)
) ENGINE=InnoDB;

-- 全文索引（用于文本搜索）
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 空间索引（用于地理数据）
CREATE SPATIAL INDEX idx_location ON locations(location);

索引选择原则

  1. 选择性原则：高选择性的列更适合建立索引
  2. 覆盖原则：尽量让索引覆盖查询需求
  3. 前缀原则：对于长字符串，使用前缀索引
  4. 复合原则：多列查询时考虑复合索引

1.2 复合索引设计技巧

复合索引的设计需要遵循最左前缀原则：

-- 假设有一个用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    status TINYINT,
    created_at DATETIME,
    INDEX idx_username_status (username, status),
    INDEX idx_email_created (email, created_at)
);

-- 正确的查询使用方式
SELECT * FROM users WHERE username = 'john' AND status = 1;
SELECT * FROM users WHERE email = 'john@example.com' AND created_at > '2023-01-01';

-- 错误的查询使用方式（无法使用复合索引）
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE username = 'john' AND created_at > '2023-01-01';

1.3 前缀索引优化

对于长字符串字段，使用前缀索引可以显著减少索引空间占用：

-- 创建前缀索引
CREATE INDEX idx_long_string ON products(description(100));

-- 分析前缀长度的选择
SELECT 
    COUNT(DISTINCT LEFT(description, 50)) as unique_50,
    COUNT(DISTINCT LEFT(description, 100)) as unique_100,
    COUNT(*) as total
FROM products;

二、执行计划分析详解

2.1 EXPLAIN命令深度解析

EXPLAIN是分析SQL执行计划的重要工具，MySQL 8.0对其进行了增强：

-- 示例查询
EXPLAIN SELECT u.id, u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1 AND o.order_date >= '2023-01-01';

-- 输出结果分析
/*
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ref  | idx_status    | idx_status | 1       | const             |   10 |   100.00 | NULL  |
|  1 | SIMPLE      | o     | NULL       | ref  | idx_user_date | idx_user_date | 9       | test.u.id         |   50 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
*/

2.2 执行计划关键字段解读

type字段详解

-- 各种访问类型示例
-- ALL - 全表扫描
SELECT * FROM users WHERE age > 25;

-- index - 索引扫描
SELECT id FROM users;

-- range - 范围扫描
SELECT * FROM users WHERE id BETWEEN 100 AND 200;

-- ref - 等值查询
SELECT * FROM users WHERE username = 'john';

-- const - 常量查询
SELECT * FROM users WHERE id = 1;

key_len字段分析

-- 分析不同索引的key_len
CREATE TABLE test_index (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    status TINYINT,
    INDEX idx_name_email (name(50), email(50)),
    INDEX idx_status (status)
);

-- key_len计算规则
-- id: 4字节 (INT)
-- name(50): 50*3 + 1 = 151字节 (UTF8MB4)
-- email(50): 50*3 + 1 = 151字节
-- status: 1字节 (TINYINT)

2.3 MySQL 8.0执行计划增强功能

MySQL 8.0引入了更多的执行计划信息：

-- 使用EXPLAIN FORMAT=JSON查看详细执行计划
EXPLAIN FORMAT=JSON 
SELECT u.username, o.total_amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1 
ORDER BY o.created_at DESC 
LIMIT 10;

-- 查看优化器成本估算
SHOW VARIABLES LIKE 'optimizer_trace%';
SET optimizer_trace="enabled=on";
-- 执行查询
SELECT * FROM users WHERE email = 'test@example.com';
-- 查看跟踪结果
SELECT * FROM information_schema.OPTIMIZER_TRACE;

三、慢查询诊断与优化策略

3.1 慢查询日志配置

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 记录超过2秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

3.2 慢查询分析工具

-- 使用pt-query-digest分析慢查询日志
-- 安装percona-toolkit后执行：
pt-query-digest /var/log/mysql/slow.log

-- 或者使用mysqltuner.pl进行整体性能分析

3.3 实际慢查询优化案例

案例1：JOIN查询优化

-- 优化前的慢查询
SELECT u.username, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.status = 1 
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND p.category = 'electronics';

-- 优化后的查询
-- 1. 添加必要的索引
CREATE INDEX idx_users_status_id ON users(status, id);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
CREATE INDEX idx_products_category_id ON products(category, id);

-- 2. 重写查询逻辑
SELECT u.username, o.order_date, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.status = 1 
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND p.category = 'electronics'
ORDER BY o.order_date DESC;

案例2：子查询优化

-- 优化前的子查询
SELECT u.username, u.email
FROM users u
WHERE u.id IN (
    SELECT user_id 
    FROM orders 
    WHERE total_amount > 1000
    AND created_at > '2023-01-01'
);

-- 优化后的JOIN查询
SELECT DISTINCT u.username, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 1000
AND o.created_at > '2023-01-01';

四、高级优化技巧与最佳实践

4.1 查询缓存与预取优化

-- MySQL 8.0移除了查询缓存，但可以使用其他方式实现类似效果
-- 使用内存表缓存热点数据
CREATE TABLE hot_data_cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    data JSON,
    expire_time TIMESTAMP,
    INDEX idx_expire_time (expire_time)
) ENGINE=MEMORY;

-- 预取策略示例
SELECT u.*, o.* 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5) -- 预先知道需要的数据
ORDER BY u.id;

4.2 分区表优化

-- 按时间分区的订单表
CREATE TABLE orders_partitioned (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    status TINYINT
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 分区裁剪优化
SELECT * FROM orders_partitioned 
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
-- 只扫描2023年分区，提高查询效率

4.3 事务与锁优化

-- 优化事务中的查询顺序
-- 避免死锁的查询顺序
BEGIN;
-- 先查询再更新，保持一致的锁定顺序
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 使用合适的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

五、监控与持续优化

5.1 性能监控指标

-- 关键性能指标监控
SELECT 
    VARIABLE_NAME, 
    VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME IN (
    'Threads_connected',
    'Threads_running',
    'Innodb_buffer_pool_hit_rate',
    'Handler_read_rnd_next',
    'Handler_read_key',
    'Select_full_join',
    'Sort_merge_passes'
);

-- 监控慢查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 AS avg_time_ms,
    SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT LIKE '%SELECT%' 
ORDER BY AVG_TIMER_WAIT DESC 
LIMIT 10;

5.2 自动化优化脚本

#!/bin/bash
# 性能优化监控脚本

# 检查慢查询数量
SLOW_QUERIES=$(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | awk 'NR>1 {print $2}')
echo "慢查询数量: $SLOW_QUERIES"

# 检查索引使用情况
mysql -e "
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    i.INDEX_NAME,
    i.COLUMN_NAME,
    i.SEQ_IN_INDEX,
    i.INDEX_TYPE
FROM information_schema.TABLES t
JOIN information_schema.STATISTICS i ON t.TABLE_SCHEMA = i.TABLE_SCHEMA AND t.TABLE_NAME = i.TABLE_NAME
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, i.INDEX_NAME;
" > index_analysis.txt

echo "索引分析已保存到 index_analysis.txt"

六、实战案例分享

6.1 电商平台商品搜索优化

-- 原始商品搜索查询（性能差）
SELECT p.id, p.name, p.price, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.name LIKE '%手机%'
AND p.status = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- 优化方案1：添加全文索引
ALTER TABLE products ADD FULLTEXT(name, description);
SELECT p.id, p.name, p.price, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE MATCH(p.name, p.description) AGAINST('手机' IN NATURAL LANGUAGE MODE)
AND p.status = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- 优化方案2：复合索引优化
CREATE INDEX idx_products_search ON products(category_id, status, created_at);
SELECT p.id, p.name, p.price, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.name LIKE '手机%'
AND p.status = 1
ORDER BY p.created_at DESC
LIMIT 20;

6.2 社交平台用户动态流优化

-- 用户动态流查询优化
-- 原始查询（复杂且慢）
SELECT d.*, u.username, u.avatar
FROM dynamic_feed d
JOIN users u ON d.user_id = u.id
WHERE d.user_id IN (
    SELECT followed_id FROM user_follows WHERE follower_id = 12345
)
ORDER BY d.created_at DESC
LIMIT 50;

-- 优化后的查询
-- 1. 预计算关注列表
CREATE TABLE user_follow_cache (
    user_id BIGINT,
    followed_ids JSON,
    updated_at TIMESTAMP,
    PRIMARY KEY (user_id)
);

-- 2. 优化后的查询
SELECT d.*, u.username, u.avatar
FROM dynamic_feed d
JOIN users u ON d.user_id = u.id
WHERE d.user_id IN (/* 预计算的关注列表 */)
ORDER BY d.created_at DESC
LIMIT 50;

七、常见问题与解决方案

7.1 索引失效问题排查

-- 索引失效常见场景
-- 1. 函数使用导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 索引失效
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'; -- 索引有效

-- 2. 范围查询后使用等值条件
SELECT * FROM users WHERE status = 1 AND age BETWEEN 20 AND 30 AND email = 'test@example.com';
-- 最好调整索引顺序为 (status, age, email)

7.2 内存优化策略

-- 调整缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 2G; -- 根据服务器内存调整

-- 调整查询缓存（MySQL 8.0已移除）
-- 使用其他缓存机制替代

-- 优化临时表
SET SESSION tmp_table_size = 256M;
SET SESSION max_heap_table_size = 256M;

结论

MySQL 8.0的性能优化是一个系统工程，需要从索引设计、执行计划分析、慢查询优化等多个维度综合考虑。通过本文介绍的各种技术和方法，我们可以有效地提升数据库查询性能，特别是在高并发业务场景下。

关键要点总结：

  1. 索引优化：合理设计索引，遵循最左前缀原则，使用前缀索引优化长字符串
  2. 执行计划分析：熟练掌握EXPLAIN命令，理解各种访问类型和优化器行为
  3. 慢查询优化：建立完善的慢查询监控机制，针对性地优化慢查询语句
  4. 高级技巧：利用分区表、缓存、事务优化等高级特性
  5. 持续监控：建立性能监控体系，定期分析和优化

通过系统的性能优化实践，我们可以在MySQL 8.0环境下实现查询性能的显著提升，为业务发展提供坚实的技术支撑。记住，性能优化是一个持续的过程，需要不断地监控、分析和改进。

本文来自极简博客，作者：梦幻之翼，转载请注明原文链接：MySQL 8.0查询性能优化实战：索引优化、执行计划分析和慢查询调优技巧

打赏

本文固定链接: https://www.cxy163.net/archives/9184 | 绝缘体

该日志由 绝缘体.. 于 2018年09月03日 发表在 未分类 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: MySQL 8.0查询性能优化实战：索引优化、执行计划分析和慢查询调优技巧 | 绝缘体
关键字: , , , ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

  1. CVE-2021-35042
  2. go语言编译过程概述
  3. golang内存对齐分析(转载)
  4. DOS攻击(一)
  5. python黑帽子(第二章)
  6. sqlmap Tamper脚本编写
  7. python安全脚本
  8. docker进阶_dockerswarm
  9. CDN绕过
  10. docker基础_网络模式
  11. mysql中int(1)是长度为1的int型吗？
  12. 百万数据查询优化技巧
  13. php的password_hash方法详解
  14. 使用php编程的一些经验总结
  15. mysql 配置ssl ，java应用连接
  16. (转载)Go channel 的妙用

MySQL 8.0查询性能优化实战：索引优化、执行计划分析和慢查询调优技巧：等您坐沙发呢！

发表评论


快捷键：Ctrl+Enter