Feb 23 2021

MySQL 8.0数据库性能调优最佳实践：索引优化、查询执行计划分析与读写分离架构

作者： 绝缘体..   发布： 2021-02-23 15:46   分类： 未分类   阅读：   抢沙发  
 
更多

MySQL 8.0数据库性能调优最佳实践：索引优化、查询执行计划分析与读写分离架构

引言：MySQL 8.0性能优化的时代背景

随着企业级应用对数据处理能力的要求日益提高，数据库系统作为核心基础设施，其性能表现直接决定了业务系统的响应速度与用户体验。MySQL 8.0自发布以来，凭借其在性能、安全性和功能上的显著提升，已成为众多高并发、大数据量场景下的首选关系型数据库。

相较于早期版本，MySQL 8.0引入了多项关键改进：

  • 窗口函数（Window Functions） 支持复杂统计分析；
  • 通用表表达式（CTE） 提升SQL可读性；
  • 不可见索引（Invisible Indexes） 用于安全测试索引影响；
  • 原子DDL操作 确保元数据一致性；
  • JSON增强支持 满足半结构化数据需求；
  • 更高效的查询优化器（CBO） 基于成本的优化模型大幅提升执行效率。

然而，即使拥有这些先进特性，若缺乏合理的性能调优策略，仍可能面临慢查询、锁竞争、连接耗尽等问题。本文将围绕 索引优化、查询执行计划分析、分区表设计 三大核心技术，并结合 主从复制与读写分离架构，系统性地介绍MySQL 8.0环境下的性能调优完整解决方案。

一、索引优化：构建高效访问路径的核心策略

1.1 索引的基本原理与类型

索引是数据库中加速数据检索的关键机制。在MySQL中，常见的索引类型包括：

类型 说明
B-Tree索引 默认索引类型，适用于等值查询、范围查询、排序
Hash索引 仅支持等值比较，适用于内存表（如Memory引擎）
Full-Text索引 支持文本搜索，适合关键词匹配
Spatial索引 用于地理空间数据（如GIS）

在MySQL 8.0中，B-Tree索引仍是主流选择，尤其适用于InnoDB存储引擎。

1.2 索引设计原则

✅ 正确使用复合索引（Composite Index）

复合索引遵循“最左前缀匹配”原则。例如，对于查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2024-01-01';

应建立如下复合索引：

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

⚠️ 错误示例：CREATE INDEX idx_date_customer ON orders (order_date, customer_id);
此索引无法有效支持 customer_id = ? 的查询条件。

✅ 避免冗余索引

重复或冗余索引会增加写入开销（INSERT/UPDATE/DELETE），并占用额外磁盘空间。例如：

-- 冗余索引示例
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_ab ON t(a, b); -- 已包含 a，无需单独索引

建议定期使用 information_schema.statistics 检查索引冗余情况：

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db_name'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

✅ 选择合适的索引字段长度

对于字符串字段，避免对过长字段创建全列索引。可通过前缀索引减少存储开销：

-- 对VARCHAR(255)字段创建前10字符索引
CREATE INDEX idx_name_prefix ON users (name(10));

💡 评估前缀长度是否足够：可通过以下语句估算唯一性比例：

SELECT 
    COUNT(*) AS total,
    COUNT(DISTINCT LEFT(name, 10)) AS unique_prefix_10,
    COUNT(DISTINCT LEFT(name, 15)) AS unique_prefix_15
FROM users;

若 unique_prefix_10 / total > 0.95，则10字符已足够。

1.3 利用不可见索引进行安全测试

MySQL 8.0引入了 不可见索引（Invisible Indexes） 功能，允许在不删除索引的前提下让优化器忽略它，从而安全测试索引移除的影响。

-- 创建一个不可见索引
CREATE INDEX idx_invisible ON orders (status) INVISIBLE;

-- 查看当前可见性
SHOW CREATE TABLE orders;

-- 启用该索引（使其重新被优化器使用）
ALTER TABLE orders ALTER INDEX idx_invisible VISIBLE;

此功能特别适用于生产环境中的索引变更风险控制。

1.4 使用覆盖索引减少回表开销

覆盖索引（Covering Index）是指查询所需的所有字段都包含在索引中，从而避免回表查找主键记录。

例如，假设表结构如下：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20),
    order_date DATETIME,
    amount DECIMAL(10,2)
);

如果经常执行：

SELECT customer_id, status, order_date 
FROM orders 
WHERE customer_id = 123;

应建立覆盖索引：

CREATE INDEX idx_covering ON orders (customer_id) INCLUDE (status, order_date);

📌 注意：MySQL 8.0支持 INCLUDE 子句（仅限InnoDB），但需注意：INCLUDE 字段不会参与索引排序，仅作为附加数据存储。

1.5 实战案例：基于慢查询日志分析索引缺失

假设通过 slow_query_log 发现以下慢查询：

SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY status DESC;

执行计划显示 Using filesort，说明未使用索引排序。

解决方案：

-- 创建复合索引，覆盖查询和排序需求
CREATE INDEX idx_date_status ON orders (order_date, status) INCLUDE (id, customer_id, amount);

再次运行 EXPLAIN，确认 Using index 和 Using index condition 出现，表明已优化成功。

二、查询执行计划分析：深入理解SQL执行路径

2.1 使用 EXPLAIN 分析查询执行计划

EXPLAIN 是分析SQL执行效率的核心工具。MySQL 8.0中，EXPLAIN FORMAT=JSON 提供更详细的输出信息。

示例：基础EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

返回结果包含以下关键字段：

字段 说明
id 查询编号，表示执行顺序
select_type 查询类型（SIMPLE、PRIMARY、SUBQUERY等）
table 表名
type 访问类型（ALL, index, range, ref, eq_ref, const）
possible_keys 可能使用的索引
key 实际使用的索引
key_len 使用的索引长度（字节数）
ref 与索引比较的列或常量
rows 估计扫描行数
filtered 过滤后剩余行数百分比
Extra 附加信息（如 Using where, Using index, Using filesort）

关键访问类型解读

类型 性能等级 说明
const 最佳 主键或唯一索引等值匹配
eq_ref 优秀 多表连接中使用主键或唯一索引
ref 良好 非唯一索引部分匹配
range 中等 范围查询（如 BETWEEN, >, <）
index 较差 全索引扫描（比全表扫描略快）
ALL 最差 全表扫描，必须避免

❗ 若出现 Using filesort 或 Using temporary，说明存在排序或临时表开销，需优化。

2.2 使用 EXPLAIN FORMAT=JSON 获取详细执行信息

EXPLAIN FORMAT=JSON 
SELECT o.id, o.amount, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.order_date >= '2024-01-01'
ORDER BY o.amount DESC;

输出JSON中包含：

  • query_block：查询块结构
  • table：每个表的访问方式
  • access_type：访问类型
  • condition_rejected_by_optimizer：优化器拒绝的条件
  • rows_examined_per_scan：每轮扫描行数

这有助于发现潜在的优化点，如过滤条件未被下推至存储引擎。

2.3 识别并解决常见执行问题

问题1：Using filesort 导致性能下降

原因：排序未利用索引。

修复方法：

  • 添加合适排序字段的复合索引。
-- 原始查询
SELECT * FROM orders ORDER BY order_date DESC;

-- 优化方案
CREATE INDEX idx_order_date_desc ON orders (order_date DESC);

✅ MySQL 8.0支持降序索引（DESC），提升排序效率。

问题2：Using temporary 表示使用了临时表

常见于 GROUP BY 或 DISTINCT 操作。

优化建议：

  • 尽量使用覆盖索引避免回表。
  • 避免不必要的聚合操作。
-- 低效写法
SELECT DISTINCT customer_id FROM orders WHERE order_date > '2024-01-01';

-- 优化写法：添加索引 + 排序优化
CREATE INDEX idx_date_cust ON orders (order_date, customer_id);

2.4 结合 Performance Schema 监控执行细节

MySQL 8.0内置了强大的性能监控系统——Performance Schema（P_S），可用于追踪SQL执行详情。

启用P_S并查询慢查询执行时间：

-- 查看所有执行超过1秒的SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1000000000 AS avg_ms,
    MAX_TIMER_WAIT / 1000000000 AS max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1000000000 -- 1秒
ORDER BY avg_ms DESC;

此命令可帮助定位高频慢查询，进而针对性优化。

三、分区表设计：应对海量数据的分治之道

3.1 分区表的优势与适用场景

当单表数据量超过千万级别时，传统表结构将导致查询变慢、备份困难、维护成本高。MySQL 8.0支持多种分区策略，适用于以下场景：

  • 日志表按时间滚动（如按月分区）
  • 订单表按客户ID哈希分区
  • 大量历史数据归档管理

3.2 MySQL 8.0支持的分区类型

类型 说明 适用场景
Range分区 按范围划分（如日期、数值） 时间序列数据
List分区 显式指定值列表 枚举型字段（如状态码）
Hash分区 基于哈希算法分配 均匀分布数据
Key分区 类似Hash，但使用MySQL内部哈希 无显式key字段时
Composite分区 组合分区（如Range+Hash） 复杂业务模型

3.3 实战：按月创建Range分区表

CREATE TABLE sales_monthly (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE NOT NULL,
    product_id INT,
    amount DECIMAL(10,2),
    region VARCHAR(50),
    INDEX idx_sale_date (sale_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date) * 100 + MONTH(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    ...
    PARTITION p202412 VALUES LESS THAN (202501)
);

✅ 优势：

  • 查询只扫描相关分区（Partition Pruning）
  • 可独立维护某个月份数据（如删除旧分区）

3.4 动态分区管理：自动化维护脚本

编写存储过程自动添加新分区：

DELIMITER $$

CREATE PROCEDURE AddMonthlyPartition()
BEGIN
    DECLARE year_month INT DEFAULT 0;
    DECLARE next_partition VARCHAR(20) DEFAULT '';
    DECLARE done INT DEFAULT FALSE;

    -- 获取当前年月
    SET year_month = YEAR(CURDATE()) * 100 + MONTH(CURDATE());

    -- 构造下一个月分区名
    SET next_partition = CONCAT('p', LPAD(year_month + 1, 6, '0'));

    -- 检查是否存在该分区
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.partitions 
        WHERE table_schema = DATABASE() 
          AND table_name = 'sales_monthly'
          AND partition_name = next_partition
    ) THEN
        SET @sql = CONCAT(
            'ALTER TABLE sales_monthly ADD PARTITION ',
            'PARTITION ', next_partition,
            ' VALUES LESS THAN (', year_month + 1, ')'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT 'Partition added successfully.' AS message;
    ELSE
        SELECT 'Partition already exists.' AS message;
    END IF;
END$$

DELIMITER ;

可设置定时任务（如cron）每日执行一次，确保分区持续更新。

3.5 分区表的注意事项

  • 分区键必须是表达式的一部分：如 YEAR(sale_date) 必须出现在 PARTITION BY 中。
  • 避免跨分区查询：若查询条件不涉及分区键，可能触发全表扫描。
  • 分区数量不宜过多：建议每张表不超过100个分区，否则影响元数据管理。
  • 备份与恢复：可单独备份/恢复特定分区。

四、读写分离架构：提升高并发场景下的系统吞吐量

4.1 为什么需要读写分离？

在高并发Web应用中，读操作远多于写操作（通常比例为 8:2）。若所有请求均访问主库，会导致：

  • 主库CPU、IO压力剧增
  • 主库成为瓶颈
  • 写锁阻塞读操作
  • 故障恢复困难

读写分离通过将读请求路由到从库，写请求保持在主库，实现负载均衡与容灾。

4.2 主从复制基础架构

MySQL 8.0支持基于GTID（全局事务ID）的主从复制，具有更强的一致性和可维护性。

步骤1：配置主库（Master）

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON

重启后授权从库连接：

CREATE USER 'replica'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

查看主库状态：

SHOW MASTER STATUS;
-- 输出类似：
-- File: mysql-bin.000001, Position: 154, GTID: 0-1-123

步骤2：配置从库（Slave）

# my.cnf
[mysqld]
server-id = 2
relay-log = relay-bin
log-slave-updates = ON
read-only = ON
gtid-mode = ON
enforce-gtid-consistency = ON

启动复制：

CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'replica',
    MASTER_PASSWORD = 'secure_password',
    MASTER_AUTO_POSITION = 1; -- 使用GTID自动定位

START SLAVE;
SHOW SLAVE STATUS\G

检查 Slave_IO_Running 和 Slave_SQL_Running 是否均为 Yes。

4.3 应用层读写分离实现方案

方案一：使用中间件（推荐）

1. MyCat（国产开源中间件）

配置 schema.xml：

<schema name="app_db" checkSQLSchema="false" sqlMaxLimit="100">
    <table name="orders" dataNode="dn1" rule="sharding-by-month"/>
</schema>

<dataNode name="dn1" dataHost="host1" database="test_db"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="1">
    <writeHost host="master" url="master_ip:3306" user="root" password="pass"/>
    <readHost host="slave1" url="slave_ip:3306" user="root" password="pass"/>
</dataHost>
  • balance="1"：读写分离模式（读请求分发到从库）
  • 自动故障转移、连接池管理
2. ProxySQL

ProxySQL是一个高性能MySQL代理，支持动态路由、查询缓存、连接池等。

配置示例：

-- 添加后端服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'master_ip', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave_ip', 3306);

-- 设置读写规则
INSERT INTO mysql_query_rules (
    rule_id, active, match_pattern, destination_hostgroup, apply
) VALUES (
    1, 1, '^SELECT.*FOR UPDATE$', 0, 1
), (
    2, 1, '^SELECT', 1, 1
);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
  • 所有 SELECT 请求路由到从库
  • SELECT ... FOR UPDATE 路由到主库（保证一致性）

方案二：应用代码层面实现

使用Java Spring Boot + JDBC模板：

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource routingDataSource() {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", masterDataSource());
        dataSourceMap.put("slave", slaveDataSource());

        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());

        return routingDataSource;
    }
}

// 自定义注解标记读操作
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ReadOnly {}

// AOP切面判断是否为读操作
@Aspect
@Component
public class ReadWriteAspect {

    @Pointcut("@annotation(ReadOnly)")
    public void readOnlyMethod() {}

    @Around("@within(ReadOnly) || readOnlyMethod()")
    public Object intercept(ProceedingJoinPoint pjp) throws Throwable {
        DataSourceContextHolder.setRead(); // 标记为读
        try {
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clear();
        }
    }
}

配合 DynamicDataSource 实现动态切换数据源。

4.4 读写分离的高级优化技巧

✅ 从库延迟监控

使用 SHOW SLAVE STATUS 检查延迟：

SELECT 
    Seconds_Behind_Master,
    Slave_IO_Running,
    Slave_SQL_Running
FROM slave_status;

若 Seconds_Behind_Master > 10，建议：

  • 优化从库硬件
  • 减少大事务
  • 使用半同步复制（semi-sync replication）

✅ 半同步复制配置

在主库和从库均启用半同步：

# 主库
plugin-load-add = "semisync_master.so"
rpl_semi_sync_master_enabled = ON

# 从库
plugin-load-add = "semisync_slave.so"
rpl_semi_sync_slave_enabled = ON

确保至少一个从库收到并确认写入后，主库才返回成功，提升数据安全性。

✅ 读写分离的容错机制

  • 当所有从库宕机时，自动降级为只读主库（需应用层逻辑支持）
  • 使用心跳检测机制动态剔除不可用节点
  • 结合ZooKeeper或Consul实现服务发现与配置同步

五、综合性能调优流程与最佳实践总结

5.1 完整性能调优工作流

graph TD
    A[收集慢查询日志] --> B[分析执行计划 EXPLAIN]
    B --> C{是否存在索引缺失?}
    C -->|是| D[创建或调整索引]
    C -->|否| E[检查是否有文件排序/临时表]
    E --> F[优化SQL或添加覆盖索引]
    F --> G[评估是否需分区表]
    G --> H[实施分区策略]
    H --> I[部署主从复制 + 读写分离]
    I --> J[使用中间件/应用层路由]
    J --> K[持续监控 P_S + Prometheus/Grafana]
    K --> L[周期性调优与告警]

5.2 最佳实践清单

类别 最佳实践
索引 使用复合索引，遵循最左前缀；避免冗余；善用不可见索引测试
SQL 避免 SELECT *；合理使用 LIMIT；避免函数封装字段
执行计划 优先消除 Using filesort 和 Using temporary
分区 按时间或业务维度分区；合理设置分区数量
读写分离 使用GTID主从复制；借助MyCat/ProxySQL；开启半同步
监控 启用 slow_query_log、performance_schema；集成Prometheus

5.3 推荐工具链组合

  • 慢查询分析：pt-query-digest（Percona Toolkit）
  • 执行计划可视化：MySQL Workbench 或 DBeaver
  • 性能监控：Prometheus + Grafana + MySQL Exporter
  • 备份恢复：XtraBackup（Percona）或 MySQL Enterprise Backup

结语

MySQL 8.0为数据库性能优化提供了前所未有的能力。通过科学的索引设计、精准的执行计划分析、合理的分区策略，以及成熟的读写分离架构，我们完全可以在不牺牲稳定性的前提下，构建出高性能、高可用的企业级数据库系统。

本文所涵盖的技术细节与实战案例，均来自真实生产环境经验。建议团队建立定期性能巡检机制，结合自动化工具，持续优化数据库性能，真正实现“数据驱动业务”的高效运转。

🔧 行动建议：立即执行 SHOW VARIABLES LIKE 'slow_query_log'; 并开启慢查询日志，开始你的性能调优之旅！

标签：MySQL, 性能优化, 数据库, 索引优化, 读写分离

本文来自极简博客，作者：狂野之心，转载请注明原文链接：MySQL 8.0数据库性能调优最佳实践：索引优化、查询执行计划分析与读写分离架构

打赏

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

该日志由 绝缘体.. 于 2021年02月23日 发表在 未分类 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: MySQL 8.0数据库性能调优最佳实践：索引优化、查询执行计划分析与读写分离架构 | 绝缘体
关键字: , , , ,
【上一篇】
【下一篇】

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

  1. sqlmap Tamper脚本编写
  2. mysql中int(1)是长度为1的int型吗？
  3. 百万数据查询优化技巧
  4. 使用php编程的一些经验总结
  5. BUUCTF-[WebLogic]CVE-2017-10271（反
  6. mysql 配置ssl ，java应用连接
  7. 蜜罐学习
  8. MariaDB/MySQL配置文件my.cnf解读
  9. 开发者应该知道的12条Linux系统管理命令
  10. Windows下DOS命令查看占用××端口的程序
  11. utf8和utf8mb4的区别
  12. MySQL的语句执行顺序
  13. Java中的IO操作与流处理
  14. Mybatis、Mybatis Generator、Mybatis-Plus、
  15. 使用Spring Boot Actuator实现应用监控
  16. 代码中常见的Binary Search Error”异常如何处

MySQL 8.0数据库性能调优最佳实践：索引优化、查询执行计划分析与读写分离架构：等您坐沙发呢！

发表评论


快捷键：Ctrl+Enter