Aug 22 2021

MySQL 8.0高性能调优实战：索引优化、查询重写与分区策略详解

作者： 绝缘体..   发布： 2021-08-22 17:29   分类： 未分类   阅读：   抢沙发  
 
更多

MySQL 8.0高性能调优实战：索引优化、查询重写与分区策略详解

引言

随着数据量的不断增长和业务复杂度的提升，MySQL数据库的性能优化已成为每个DBA和开发工程师必须掌握的核心技能。MySQL 8.0作为当前最主流的关系型数据库版本，在性能优化方面提供了更多强大的功能和工具。本文将深入探讨MySQL 8.0的高性能调优技术，通过系统性的分析和实战案例，帮助读者掌握索引优化、查询重写和分区策略等关键技术。

MySQL 8.0性能优化概述

新特性与优化点

MySQL 8.0在性能优化方面引入了多项重要改进：

  1. 查询优化器增强：支持直方图统计信息，提供更准确的执行计划选择
  2. 索引优化：引入不可见索引、降序索引等新特性
  3. 资源管理：新增资源组功能，可以控制CPU资源分配
  4. 并行查询：支持并行执行某些查询操作
  5. 窗口函数优化：提供更高效的窗口函数执行

性能优化的基本原则

在进行MySQL性能优化时，需要遵循以下基本原则：

  • 预防优于治疗：在设计阶段就考虑性能因素
  • 量化分析：基于实际数据和指标进行优化决策
  • 逐步优化：从整体到局部，逐步细化优化策略
  • 持续监控：建立长期的性能监控机制

索引优化详解

索引设计原则

1. 选择合适的索引类型

MySQL 8.0支持多种索引类型，每种都有其适用场景：

-- B-Tree索引（默认）
CREATE INDEX idx_user_name ON users(name);

-- 前缀索引
CREATE INDEX idx_user_email_prefix ON users(email(10));

-- 全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(content);

-- 空间索引
CREATE SPATIAL INDEX idx_location_point ON locations(point);

2. 复合索引的最佳实践

复合索引的列顺序至关重要，应遵循最左前缀原则：

-- 正确的复合索引设计
CREATE INDEX idx_order_status_date ON orders(status, order_date, customer_id);

-- 查询示例
SELECT * FROM orders WHERE status = 'completed' AND order_date > '2023-01-01';
SELECT * FROM orders WHERE status = 'completed' AND order_date > '2023-01-01' AND customer_id = 123;

3. 索引覆盖优化

尽量让查询只需要访问索引而不需要回表：

-- 创建覆盖索引
CREATE INDEX idx_user_cover ON users(status, created_at, id, name);

-- 覆盖索引查询
SELECT id, name FROM users WHERE status = 'active' AND created_at > '2023-01-01';

MySQL 8.0索引新特性

不可见索引

不可见索引允许我们在不影响查询执行的情况下测试索引效果：

-- 创建不可见索引
CREATE INDEX idx_test_invisible ON products(category_id) INVISIBLE;

-- 查看索引状态
SHOW INDEX FROM products;

-- 使索引可见
ALTER INDEX idx_test_invisible VISIBLE ON products;

降序索引

MySQL 8.0支持真正的降序索引：

-- 创建降序索引
CREATE INDEX idx_product_price_desc ON products(price DESC, category_id ASC);

-- 利用降序索引的查询
SELECT * FROM products WHERE category_id = 5 ORDER BY price DESC LIMIT 10;

索引优化工具

使用EXPLAIN分析查询

EXPLAIN FORMAT=JSON 
SELECT p.name, p.price, c.name as category 
FROM products p 
JOIN categories c ON p.category_id = c.id 
WHERE p.price > 100 
ORDER BY p.price DESC 
LIMIT 20;

索引使用统计

-- 查看索引使用情况
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema = 'your_database';

查询优化与重写

查询优化器基础

执行计划分析

MySQL 8.0提供了更详细的执行计划信息：

-- 详细执行计划
EXPLAIN ANALYZE 
SELECT u.name, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2023-01-01' 
GROUP BY u.id, u.name 
HAVING order_count > 5;

直方图统计信息

MySQL 8.0支持直方图来提供更准确的数据分布信息：

-- 为列创建直方图
ANALYZE TABLE users UPDATE HISTOGRAM ON age, status;

-- 查看直方图信息
SELECT * FROM information_schema.column_statistics 
WHERE table_name = 'users';

查询重写技巧

1. 子查询优化

将相关子查询转换为JOIN操作：

-- 优化前：相关子查询
SELECT u.name, 
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u 
WHERE u.status = 'active';

-- 优化后：LEFT JOIN
SELECT u.name, COALESCE(oc.order_count, 0) as order_count
FROM users u 
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count 
    FROM orders 
    GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

2. LIMIT优化

对于大偏移量的LIMIT查询，使用延迟关联：

-- 优化前：大偏移量查询
SELECT * FROM articles 
WHERE status = 'published' 
ORDER BY created_at DESC 
LIMIT 10000, 20;

-- 优化后：延迟关联
SELECT a.* FROM articles a 
INNER JOIN (
    SELECT id FROM articles 
    WHERE status = 'published' 
    ORDER BY created_at DESC 
    LIMIT 10000, 20
) AS limited_articles ON a.id = limited_articles.id;

3. 范围查询优化

合理使用范围查询条件：

-- 使用时间范围优化
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
  AND order_date < '2023-02-01'
  AND status IN ('completed', 'shipped');

查询缓存优化

虽然MySQL 8.0移除了查询缓存，但可以通过其他方式实现类似效果：

-- 使用应用层缓存
-- Redis缓存示例
SETEX user_orders_123 3600 "serialized_order_data"

-- 使用结果表缓存
CREATE TABLE order_summary_cache (
    user_id INT PRIMARY KEY,
    total_orders INT,
    total_amount DECIMAL(10,2),
    last_updated TIMESTAMP,
    INDEX idx_last_updated (last_updated)
);

分区策略详解

分区类型选择

1. RANGE分区

适用于按时间范围或数值范围分区：

-- 按时间范围分区
CREATE TABLE sales_data (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    customer_id INT,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    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
);

2. LIST分区

适用于按预定义值列表分区：

-- 按地区分区
CREATE TABLE customer_data (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    region VARCHAR(20),
    created_at TIMESTAMP,
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

3. HASH分区

适用于均匀分布数据：

-- 按HASH分区
CREATE TABLE user_logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    log_type VARCHAR(20),
    created_at TIMESTAMP,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

分区管理操作

添加新分区

-- 为RANGE分区添加新分区
ALTER TABLE sales_data ADD PARTITION (
    PARTITION p2024 VALUES LESS THAN (2025)
);

删除旧分区

-- 删除历史分区
ALTER TABLE sales_data DROP PARTITION p2020;

重新组织分区

-- 合并分区
ALTER TABLE sales_data REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p_2021_2022 VALUES LESS THAN (2023)
);

分区查询优化

分区剪枝

确保查询能够利用分区剪枝：

-- 能够利用分区剪枝的查询
SELECT SUM(amount) FROM sales_data 
WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31';

-- 不能利用分区剪枝的查询（避免）
SELECT * FROM sales_data WHERE YEAR(sale_date) = 2022;

跨分区查询优化

-- 使用UNION优化跨分区查询
(SELECT * FROM sales_data PARTITION (p2022) WHERE amount > 1000 LIMIT 10)
UNION ALL
(SELECT * FROM sales_data PARTITION (p2023) WHERE amount > 1000 LIMIT 10)
ORDER BY amount DESC LIMIT 10;

读写分离与负载均衡

主从复制配置

主库配置

# my.cnf (主库)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

从库配置

# my.cnf (从库)
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1

读写分离实现

应用层读写分离

# Python示例
class DatabaseRouter:
    def __init__(self):
        self.master_conn = self.connect_master()
        self.slave_conn = self.connect_slave()
    
    def execute_write(self, sql, params=None):
        cursor = self.master_conn.cursor()
        cursor.execute(sql, params)
        self.master_conn.commit()
        return cursor
    
    def execute_read(self, sql, params=None):
        cursor = self.slave_conn.cursor()
        cursor.execute(sql, params)
        return cursor.fetchall()

中间件读写分离

使用ProxySQL实现读写分离：

-- ProxySQL配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (1, 'master.db.local', 3306), (2, 'slave1.db.local', 3306);

INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1), (2, 1, '^SELECT', 2);

性能监控与诊断

关键性能指标

1. 查询性能指标

-- 慢查询统计
SELECT * FROM performance_schema.events_statements_summary_by_digest 
WHERE avg_timer_wait > 1000000000000  -- 1秒以上
ORDER BY avg_timer_wait DESC;

-- 查询执行次数统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_time_ms,
    SUM_ROWS_EXAMINED/COUNT_STAR as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY COUNT_STAR DESC 
LIMIT 10;

2. 索引使用效率

-- 索引未使用统计
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_read = 0;

-- 索引效率分析
SELECT 
    s.table_schema,
    s.table_name,
    s.index_name,
    s.rows_selected,
    s.selectivity
FROM information_schema.statistics s
JOIN (
    SELECT table_schema, table_name, index_name, 
           SUM(rows_selected) as rows_selected
    FROM performance_schema.table_io_waits_summary_by_index_usage
    GROUP BY table_schema, table_name, index_name
) io ON s.table_schema = io.table_schema 
    AND s.table_name = io.table_name 
    AND s.index_name = io.index_name;

性能诊断工具

1. Performance Schema

-- 实时监控活跃会话
SELECT 
    th.processlist_id,
    th.processlist_user,
    th.processlist_host,
    th.processlist_db,
    th.processlist_command,
    th.processlist_time,
    th.processlist_info
FROM performance_schema.threads th
WHERE th.type = 'FOREGROUND' 
AND th.processlist_command != 'Sleep';

2. sys Schema

-- 查看最耗时的语句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile 
ORDER BY avg_latency DESC;

-- 查看IO等待最高的表
SELECT * FROM sys.schema_table_statistics 
ORDER BY io_total_latency DESC;

实战案例分析

案例一：电商订单查询优化

问题描述

某电商平台订单表包含5000万条记录，查询最近一个月的订单经常超时：

-- 原始查询（性能差）
SELECT o.*, u.name as user_name, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY o.created_at DESC
LIMIT 50;

优化方案

  1. 索引优化：
-- 创建复合索引
CREATE INDEX idx_orders_created_user_product ON orders(created_at DESC, user_id, product_id);

-- 优化JOIN条件的索引
CREATE INDEX idx_users_id_name ON users(id, name);
CREATE INDEX idx_products_id_name ON products(id, name);
  1. 查询重写：
-- 使用延迟关联优化
SELECT o.*, u.name as user_name, p.name as product_name
FROM (
    SELECT id, user_id, product_id, created_at
    FROM orders 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    ORDER BY created_at DESC 
    LIMIT 50
) latest_orders
JOIN orders o ON latest_orders.id = o.id
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
ORDER BY o.created_at DESC;
  1. 分区策略：
-- 按月分区
ALTER TABLE orders 
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    -- ... 更多分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

优化效果

  • 查询时间从15秒降低到0.8秒
  • 性能提升约18倍
  • CPU使用率降低60%

案例二：用户行为分析系统优化

问题描述

用户行为日志表每天新增1000万条记录，复杂的聚合查询经常超时：

-- 复杂聚合查询
SELECT 
    DATE(created_at) as date,
    action_type,
    COUNT(*) as action_count,
    COUNT(DISTINCT user_id) as unique_users
FROM user_actions 
WHERE created_at >= '2023-01-01' 
  AND created_at < '2023-02-01'
  AND action_type IN ('click', 'view', 'purchase')
GROUP BY DATE(created_at), action_type
ORDER BY date, action_type;

优化方案

  1. 预聚合表：
-- 创建每日聚合表
CREATE TABLE user_actions_daily_summary (
    date DATE,
    action_type VARCHAR(20),
    action_count INT,
    unique_users INT,
    PRIMARY KEY (date, action_type),
    INDEX idx_date (date)
);

-- 定时任务更新聚合数据
INSERT INTO user_actions_daily_summary (date, action_type, action_count, unique_users)
SELECT 
    DATE(created_at) as date,
    action_type,
    COUNT(*) as action_count,
    COUNT(DISTINCT user_id) as unique_users
FROM user_actions 
WHERE created_at >= CURDATE() - INTERVAL 1 DAY
  AND created_at < CURDATE()
GROUP BY DATE(created_at), action_type
ON DUPLICATE KEY UPDATE 
    action_count = VALUES(action_count),
    unique_users = VALUES(unique_users);
  1. 查询优化：
-- 查询聚合表
SELECT date, action_type, action_count, unique_users
FROM user_actions_daily_summary 
WHERE date >= '2023-01-01' AND date < '2023-02-01'
  AND action_type IN ('click', 'view', 'purchase')
ORDER BY date, action_type;

优化效果

  • 查询时间从30秒降低到0.2秒
  • 性能提升约150倍
  • 系统资源占用降低80%

最佳实践总结

索引优化最佳实践

  1. 定期审查索引使用情况：删除未使用的索引，避免维护开销
  2. 合理使用复合索引：遵循最左前缀原则，考虑查询模式
  3. 利用MySQL 8.0新特性：如不可见索引、降序索引等
  4. 监控索引统计信息：定期更新表统计信息

查询优化最佳实践

  1. **避免SELECT ***：只查询需要的列
  2. 合理使用LIMIT：避免大偏移量查询
  3. 优化JOIN操作：确保JOIN条件上有索引
  4. 使用EXPLAIN分析：理解查询执行计划

分区策略最佳实践

  1. 选择合适的分区键：考虑查询模式和数据分布
  2. 定期维护分区：添加新分区，删除旧分区
  3. 监控分区性能：确保分区剪枝有效工作
  4. 避免过多分区：一般不超过1000个分区

监控与维护最佳实践

  1. 建立完善的监控体系：实时监控关键性能指标
  2. 定期性能分析：识别性能瓶颈和优化机会
  3. 制定维护计划：定期更新统计信息，优化表结构
  4. 备份与恢复策略：确保数据安全和业务连续性

结论

MySQL 8.0为数据库性能优化提供了丰富的工具和特性。通过合理的索引设计、查询优化、分区策略以及读写分离等技术手段，可以显著提升数据库性能。在实际应用中，需要根据具体的业务场景和数据特点，选择合适的优化策略，并建立完善的监控和维护机制。

性能优化是一个持续的过程，需要不断地分析、测试和调整。通过本文介绍的技术和方法，相信读者能够在MySQL 8.0环境下构建高性能的数据库应用系统。记住，最好的优化方案是那些基于实际数据和业务需求制定的方案，而不是通用的”最佳实践”。

本文来自极简博客，作者：雨中漫步，转载请注明原文链接：MySQL 8.0高性能调优实战：索引优化、查询重写与分区策略详解

打赏

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

该日志由 绝缘体.. 于 2021年08月22日 发表在 未分类 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: MySQL 8.0高性能调优实战：索引优化、查询重写与分区策略详解 | 绝缘体
关键字: , , , ,
【上一篇】
【下一篇】

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

  1. 蜜罐学习
  2. go ants源码分析
  3. golang数组类型系列
  4. CVE-2021-35042
  5. golang并发扫描
  6. (转载)Go channel 的妙用
  7. CVE-2022-0543
  8. python黑帽子(第四章)
  9. python黑帽子(第三章)
  10. go语言编译过程概述
  11. golang内存对齐分析(转载)
  12. DOS攻击(一)
  13. 身份认证种类及攻击
  14. python黑帽子(第五章)
  15. python黑帽子(第二章)
  16. 生成社工字典

MySQL 8.0高性能调优实战：索引优化、查询重写与分区策略详解：等您坐沙发呢！

发表评论


快捷键：Ctrl+Enter