Jul 26 2018

MySQL 8.0数据库性能优化实战：索引优化、查询调优到分库分表，全面提升数据访问效率

作者： 绝缘体..   发布： 2018-07-26 11:21   分类： 未分类   阅读：   抢沙发  
 
更多

MySQL 8.0数据库性能优化实战：索引优化、查询调优到分库分表，全面提升数据访问效率

在现代Web应用和企业级系统中，数据库性能往往是决定整体系统响应速度和用户体验的关键因素。MySQL作为最流行的开源关系型数据库管理系统，在8.0版本中引入了许多新特性和性能改进。然而，即使是最先进的数据库系统，也需要正确的优化策略才能发挥其最大潜力。

本文将深入探讨MySQL 8.0的性能优化技术，从基础的索引优化到高级的分库分表策略，通过实际案例和代码示例，帮助您系统性地提升数据库访问效率。

1. MySQL 8.0性能优化基础

1.1 MySQL 8.0新特性对性能的影响

MySQL 8.0引入了多项重要的性能优化特性：

  • 数据字典优化：使用InnoDB存储引擎实现统一的数据字典，减少了元数据查询的开销
  • 直方图统计：提供更准确的查询优化器统计信息
  • 降序索引：支持真正的降序索引，避免排序操作
  • 窗口函数优化：提升复杂分析查询的性能

1.2 性能监控与分析工具

在进行优化之前，首先需要了解如何监控和分析数据库性能：

-- 查看当前数据库连接状态
SHOW PROCESSLIST;

-- 查看慢查询日志设置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志（建议在生产环境中谨慎使用）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看数据库性能指标
SHOW GLOBAL STATUS LIKE 'Com_%';
SHOW GLOBAL STATUS LIKE 'Handler_%';

2. 索引优化策略

2.1 索引基础知识

索引是数据库性能优化的核心。MySQL 8.0支持多种索引类型：

  • B+树索引：最常见的索引类型
  • 全文索引：用于文本搜索
  • 空间索引：用于地理空间数据
  • 哈希索引：仅在Memory存储引擎中支持

2.2 索引设计原则

2.2.1 选择合适的列作为索引

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 1,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_created_status (created_at, status)
);

-- 查看索引使用情况
SHOW INDEX FROM users;

2.2.2 复合索引的最左前缀原则

复合索引的顺序非常重要，应根据查询条件的使用频率来设计：

-- 创建复合索引示例
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    INDEX idx_user_date (user_id, order_date),
    INDEX idx_status_date (status, order_date)
);

-- 有效的查询（使用索引）
SELECT * FROM orders WHERE user_id = 123 AND order_date > '2023-01-01';
SELECT * FROM orders WHERE status = 'completed' AND order_date > '2023-01-01';

-- 无效的查询（不使用索引）
SELECT * FROM orders WHERE order_date > '2023-01-01';

2.3 索引优化工具

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

-- 分析查询执行计划
EXPLAIN SELECT u.username, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1 AND o.order_date > '2023-01-01';

-- 使用EXPLAIN ANALYZE获取实际执行信息（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE username LIKE 'john%';

2.3.2 索引优化建议

-- 使用索引优化器建议
ANALYZE TABLE users;
SHOW INDEX FROM users;

-- 查看表统计信息
SELECT 
    table_name,
    index_name,
    cardinality,
    (cardinality / table_rows) * 100 AS selectivity
FROM information_schema.statistics 
WHERE table_schema = 'your_database' 
AND table_name = 'users';

3. SQL查询优化

3.1 查询优化基本原则

3.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';

3.1.2 使用LIMIT限制结果集

-- 分页查询优化
SELECT * FROM users 
WHERE status = 1 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 100;

-- 使用游标分页（更高效）
SELECT * FROM users 
WHERE id > 1000 AND status = 1 
ORDER BY id 
LIMIT 20;

3.2 JOIN优化策略

3.2.1 选择合适的JOIN类型

-- INNER JOIN优化
SELECT u.username, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id, u.username;

-- 使用EXISTS替代IN（在某些情况下更高效）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

3.2.2 JOIN顺序优化

MySQL查询优化器通常能自动选择最优的JOIN顺序，但在复杂查询中，手动指定可能更有效：

-- 强制JOIN顺序（谨慎使用）
SELECT /*+ JOIN_ORDER(users, orders, products) */ 
u.username, p.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.status = 1;

3.3 子查询优化

3.3.1 将子查询转换为JOIN

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

-- 优化后：LEFT JOIN
SELECT u.username, 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 = 1;

4. 查询执行计划分析

4.1 EXPLAIN输出详解

-- 详细分析查询执行计划
EXPLAIN FORMAT=JSON 
SELECT u.username, o.amount, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.status = 1 AND o.order_date >= '2023-01-01';

4.1.1 关键字段解释

  • type：JOIN类型，从最好到最差：system > const > eq_ref > ref > range > index > ALL
  • key：实际使用的索引
  • rows：扫描的行数估算
  • Extra：额外信息，如”Using index”、”Using where”等

4.2 性能瓶颈识别

-- 识别性能问题查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 AS avg_sec,
    MAX_TIMER_WAIT/1000000000000 AS max_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

5. 读写分离架构

5.1 主从复制配置

5.1.1 主库配置

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

5.1.2 从库配置

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

5.2 应用层读写分离实现

# Python示例：读写分离连接池
import pymysql
from pymysql.connections import Connection

class ReadWriteSplitConnection:
    def __init__(self, master_config, slave_config):
        self.master = pymysql.connect(**master_config)
        self.slave = pymysql.connect(**slave_config)
    
    def execute_write(self, sql, params=None):
        """执行写操作"""
        with self.master.cursor() as cursor:
            cursor.execute(sql, params)
            self.master.commit()
            return cursor.lastrowid
    
    def execute_read(self, sql, params=None):
        """执行读操作"""
        with self.slave.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()

5.3 负载均衡策略

// Java示例：多从库负载均衡
public class LoadBalancedConnection {
    private List<Connection> slaves;
    private AtomicInteger counter = new AtomicInteger(0);
    
    public Connection getReadConnection() {
        int index = counter.getAndIncrement() % slaves.size();
        return slaves.get(index);
    }
}

6. 分库分表策略

6.1 垂直分库

垂直分库是按照业务模块将表分散到不同的数据库中：

-- 原始表结构
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    profile TEXT,
    created_at TIMESTAMP,
    last_login TIMESTAMP
);

-- 垂直分库后
-- 用户基础信息库
CREATE TABLE user_basic (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

-- 用户扩展信息库
CREATE TABLE user_profile (
    id INT PRIMARY KEY,
    profile TEXT,
    last_login TIMESTAMP,
    FOREIGN KEY (id) REFERENCES user_basic(id)
);

6.2 水平分表

水平分表是将同一张表的数据按照某种规则分散到多个表中：

6.2.1 按时间分表

-- 按月分表示例
CREATE TABLE orders_202301 (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    INDEX idx_user_date (user_id, order_date)
);

CREATE TABLE orders_202302 (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    INDEX idx_user_date (user_id, order_date)
);

6.2.2 按哈希分表

-- 按用户ID哈希分表
CREATE TABLE users_0 (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE users_1 (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 应用层分表逻辑
public class HashSharding {
    private static final int SHARD_COUNT = 4;
    
    public String getTableName(int userId) {
        int shardIndex = userId % SHARD_COUNT;
        return "users_" + shardIndex;
    }
}

6.3 分库分表中间件

6.3.1 ShardingSphere配置示例

# sharding.yaml
dataSources:
  ds0:
    url: jdbc:mysql://localhost:3306/db0
    username: root
    password: password
  ds1:
    url: jdbc:mysql://localhost:3306/db1
    username: root
    password: password

rules:
- !SHARDING
  tables:
    orders:
      actualDataNodes: ds${0..1}.orders_${0..3}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: orders_inline
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
  shardingAlgorithms:
    orders_inline:
      type: INLINE
      props:
        algorithm-expression: orders_${user_id % 4}
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds${user_id % 2}

7. 缓存优化策略

7.1 Redis缓存集成

import redis
import json
from typing import Optional

class MySQLCache:
    def __init__(self, redis_host='localhost', redis_port=6379):
        self.redis_client = redis.Redis(host=redis_host, port=redis_port)
        self.mysql_conn = None  # MySQL连接
    
    def get_user_with_cache(self, user_id: int) -> Optional[dict]:
        # 先查缓存
        cache_key = f"user:{user_id}"
        cached_data = self.redis_client.get(cache_key)
        
        if cached_data:
            return json.loads(cached_data)
        
        # 缓存未命中，查询数据库
        cursor = self.mysql_conn.cursor()
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        user_data = cursor.fetchone()
        
        if user_data:
            # 写入缓存，设置过期时间
            self.redis_client.setex(
                cache_key, 
                3600,  # 1小时过期
                json.dumps(user_data)
            )
        
        return user_data

7.2 查询缓存优化

-- MySQL查询缓存配置（注意：MySQL 8.0已移除查询缓存）
-- 但可以使用应用层缓存

-- 创建缓存友好的查询
SELECT SQL_CACHE * FROM users WHERE status = 1 LIMIT 100;
SELECT SQL_NO_CACHE * FROM users WHERE status = 1 LIMIT 100;

8. 实际案例分析

8.1 电商系统订单查询优化

8.1.1 问题描述

某电商平台订单表包含数千万条记录，用户查询订单时响应缓慢。

-- 原始表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

8.1.2 优化方案

-- 1. 添加合适的索引
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);

-- 2. 分表策略
-- 按月分表
CREATE TABLE orders_202301 LIKE orders;
CREATE TABLE orders_202302 LIKE orders;

-- 3. 优化查询语句
-- 优化前
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

-- 优化后
SELECT * FROM orders_202302 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

8.2 社交平台用户关系查询优化

8.2.1 问题分析

用户关注关系表数据量巨大，查询关注列表和粉丝列表性能较差。

-- 用户关系表
CREATE TABLE user_follows (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_follower_followee (follower_id, followee_id),
    KEY idx_follower (follower_id),
    KEY idx_followee (followee_id)
);

8.2.2 优化实现

-- 1. 添加复合索引优化
ALTER TABLE user_follows ADD INDEX idx_follower_created (follower_id, created_at DESC);
ALTER TABLE user_follows ADD INDEX idx_followee_created (followee_id, created_at DESC);

-- 2. 使用覆盖索引
SELECT followee_id, created_at 
FROM user_follows 
WHERE follower_id = 123 
ORDER BY created_at DESC 
LIMIT 20;

-- 3. 引入缓存层
-- 关注列表缓存
-- Key: user:123:following
-- Value: [456, 789, 101, ...]

-- 粉丝列表缓存  
-- Key: user:123:followers
-- Value: [234, 567, 890, ...]

9. 性能监控与调优工具

9.1 Performance Schema使用

-- 启用Performance Schema
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%statements%';

-- 监控慢查询
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 > 1000000000000  -- 平均执行时间超过1秒
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

9.2 系统级监控

# 使用mysqladmin监控
mysqladmin -u root -p extended-status | grep -E "Threads_connected|Threads_running|Questions|Slow_queries"

# 监控InnoDB状态
SHOW ENGINE INNODB STATUS\G

9.3 第三方监控工具

9.3.1 Prometheus + Grafana配置

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

9.3.2 MySQL Exporter配置

# .my.cnf
[client]
user=exporter
password=secure_password

10. 最佳实践总结

10.1 索引优化最佳实践

  1. 定期分析表统计信息

    ANALYZE TABLE users, orders;

  2. 删除未使用的索引

    -- 查找未使用的索引
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0;

  3. 使用前缀索引优化大文本字段

    ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

10.2 查询优化最佳实践

  1. **避免SELECT ***

    -- 好的做法
SELECT id, username, email FROM users WHERE status = 1;

-- 避免的做法
SELECT * FROM users WHERE status = 1;

  2. 合理使用LIMIT

    -- 分页查询优化
SELECT * FROM users 
WHERE id > 10000 
ORDER BY id 
LIMIT 20;

  3. 使用EXPLAIN定期检查查询计划

    EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john';

10.3 架构优化最佳实践

  1. 读写分离配置

    • 主库处理写操作
    • 从库处理读操作
    • 合理配置复制延迟容忍度

  2. 分库分表策略

    • 根据业务特点选择分片键
    • 避免跨分片查询
    • 考虑数据迁移和扩容方案

  3. 缓存策略

    • 热点数据缓存
    • 缓存失效策略
    • 缓存穿透防护

结论

MySQL 8.0的性能优化是一个系统性的工程，需要从多个维度综合考虑。通过合理的索引设计、SQL查询优化、读写分离架构以及分库分表策略，可以显著提升数据库的访问效率。

在实际应用中，建议：

  1. 建立完善的监控体系，及时发现性能瓶颈
  2. 定期进行性能分析，持续优化数据库配置
  3. 结合业务特点，选择合适的优化策略
  4. 重视测试验证，确保优化措施的有效性

通过本文介绍的各种优化技术和最佳实践，相信您能够在MySQL 8.0环境中构建高性能的数据库系统，为您的应用提供稳定可靠的数据服务。

本文来自极简博客，作者：智慧探索者，转载请注明原文链接：MySQL 8.0数据库性能优化实战：索引优化、查询调优到分库分表，全面提升数据访问效率

打赏

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

该日志由 绝缘体.. 于 2018年07月26日 发表在 未分类 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: MySQL 8.0数据库性能优化实战：索引优化、查询调优到分库分表，全面提升数据访问效率 | 绝缘体
关键字: , , , ,
【上一篇】
【下一篇】

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

  1. CVE-2022-0543
  2. golang内存对齐分析(转载)
  3. DOS攻击(一)
  4. docker进阶_dockerswarm
  5. docker进阶_docker-compose
  6. docker基础_网络模式
  7. mysql中int(1)是长度为1的int型吗？
  8. CDN绕过
  9. python安全脚本
  10. php的password_hash方法详解
  11. laravel 缓存系统的使用
  12. python黑帽子(第二章)
  13. 使用php编程的一些经验总结
  14. sqlmap Tamper脚本编写
  15. BUUCTF-[WebLogic]CVE-2017-10271（反
  16. mysql 配置ssl ，java应用连接

MySQL 8.0数据库性能优化实战：索引优化、查询调优到分库分表，全面提升数据访问效率：等您坐沙发呢！

发表评论


快捷键：Ctrl+Enter