MySQL 9.5 性能优化终极指南：从 10s 到 10ms 的 5 个核心心法

-- 范式化设计示例
CREATETABLEusers (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

CREATETABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
FOREIGNKEY (user_id) REFERENCESusers(user_id)
);

CREATETABLE order_items (
  item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
FOREIGNKEY (order_id) REFERENCES orders(order_id)
);

-- 反范式化设计示例：订单表包含用户信息
CREATETABLE orders_denormalized (
  order_id INT PRIMARY KEY,
  user_id INT,
  username VARCHAR(50), -- 反范式化字段
  email VARCHAR(100),   -- 反范式化字段
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

-- 添加冗余的统计字段
CREATETABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10, 2),
  stock_quantity INT,
  total_sold INTDEFAULT0-- 反范式化：预计算字段
);

-- 创建不同类型的索引
-- 1. B-Tree索引（默认）：适用于全值匹配、范围查询、排序
CREATEINDEX idx_order_date ON orders(order_date);

-- 2. 哈希索引：适用于等值查询，Memory引擎支持
CREATEINDEX idx_hash_user ONusers(user_id) USINGHASH;

-- 3. 全文索引：适用于文本搜索
CREATE FULLTEXT INDEX idx_product_desc ON products(description);

-- 4. 空间索引：适用于地理空间数据
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- 5. 多列索引（复合索引）
CREATEINDEX idx_user_status_date ON orders(user_id, status, order_date);

-- 6. 前缀索引：为字符串列前N个字符创建索引
CREATEINDEX idx_email_prefix ONusers(email(20));

-- 7. 函数索引（MySQL 8.0+）：为表达式结果创建索引
CREATEINDEX idx_lower_username ONusers((LOWER(username)));

-- 1. 选择性高的列放在索引前面
-- user_id的选择性高于status，因此user_id在前
CREATEINDEX idx_user_status ON orders(user_id, status);

-- 2. 考虑覆盖索引
-- 索引包含查询所需的所有列，避免回表
CREATEINDEX idx_covering ON orders(order_id, user_id, order_date, total_amount);

-- 查询可以使用覆盖索引
SELECT order_id, user_id, order_date
FROM orders
WHERE user_id = 100AND order_date > '2024-01-01';

-- 3. 避免重复索引
-- 以下索引是冗余的，因为idx_a_b可以用于a列的查询
CREATEINDEX idx_a ON table1(a); -- 冗余索引
CREATEINDEX idx_a_b ON table1(a, b);

-- 4. 定期分析索引使用情况
SELECT
  object_schema,
  object_name,
  index_name,
  count_star,
  count_read,
  count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name ISNOTNULL
ORDERBY count_star DESC;

-- 5. 删除未使用的索引
-- 使用sys schema查看索引使用统计
SELECT * FROM sys.schema_unused_indexes;

ALTER TABLE user ADD INDEX idx_email (email); -- 创建
ALTER TABLE user ALTER INDEX idx_email INVISIBLE; -- 隐身！

-- 1. 确保JOIN列上有索引
-- 优化前：没有索引的JOIN
SELECT * FROM orders o
JOINusers u ON o.user_id = u.user_id; -- user_id没有索引

-- 优化后：为JOIN列添加索引
CREATEINDEX idx_user_id ON orders(user_id);
CREATEINDEX idx_user_id ONusers(user_id);

-- 2. 小表驱动大表
-- 优化前：大表驱动小表
SELECT * FROM large_table l
JOIN small_table s ON l.key = s.key;

-- 优化后：小表驱动大表（通过STRAIGHT_JOIN提示）
SELECTSTRAIGHT_JOIN s.*, l.*
FROM small_table s
JOIN large_table l ON s.key = l.key;

-- 3. 避免笛卡尔积
-- 确保所有JOIN都有连接条件
SELECT * FROM table1, table2 WHERE table1.id = table2.table1_id; -- 正确

-- 4. 使用合适的JOIN类型
-- 根据数据分布选择INNER JOIN、LEFT JOIN等

-- 5. 分阶段JOIN处理大数据量
-- 第一步：获取筛选后的数据
CREATETEMPORARYTABLE filtered_orders
SELECT order_id, user_id
FROM orders
WHERE order_date > '2024-01-01';

-- 第二步：与其他表JOIN
SELECT fo.*, u.username
FROM filtered_orders fo
JOINusers u ON fo.user_id = u.user_id;

CREATE INDEX idx_time_desc ON article (create_time DESC);

-- 1. 使用索引避免排序
-- 优化前：没有索引支持排序
SELECT * FROM orders ORDERBY order_date DESC;

-- 优化后：为排序字段添加索引
CREATEINDEX idx_order_date_desc ON orders(order_date DESC);

-- 2. 减少排序数据量
-- 优化前：排序所有列
SELECT * FROM orders ORDERBY order_date LIMIT100;

-- 优化后：只排序和选择必要列
SELECT order_id, order_date, total_amount
FROM orders
ORDERBY order_date
LIMIT100;

-- 3. 优化GROUP BY查询
-- 优化前：GROUP BY没有索引
SELECT user_id, COUNT(*)
FROM orders
GROUPBY user_id;

-- 优化后：为GROUP BY列添加索引
CREATEINDEX idx_user_id ON orders(user_id);

-- 4. 使用派生表优化复杂分组
-- 优化前：复杂GROUP BY
SELECT
  o.user_id,
COUNT(*) as order_count,
SUM(oi.quantity) as total_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUPBY o.user_id;

-- 优化后：分阶段处理
WITH order_summary AS (
SELECT
    o.order_id,
    o.user_id,
    SUM(oi.quantity) as order_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUPBY o.order_id, o.user_id
)
SELECT
  user_id,
COUNT(*) as order_count,
SUM(order_quantity) as total_quantity
FROM order_summary
GROUPBY user_id;

-- 1. 传统分页的性能问题
-- 偏移量越大，性能越差
SELECT * FROM orders ORDERBY order_date LIMIT100000, 20;

-- 2. 基于游标的分页（Keyset Pagination）
-- 记住最后一行的值，而不是偏移量
SELECT * FROM orders
WHERE order_date > '2024-01-01'
ORDERBY order_date
LIMIT20;

-- 下一页
SELECT * FROM orders
WHERE order_date > '最后一行日期'
ORDERBY order_date
LIMIT20;

-- 3. 使用覆盖索引优化
-- 先通过覆盖索引获取ID，再获取数据
SELECT o.*
FROM orders o
JOIN (
SELECT order_id
FROM orders
WHERE order_date > '2024-01-01'
ORDERBY order_date
LIMIT100000, 20
) tmp ON o.order_id = tmp.order_id;

-- 4. 分区表分页优化
-- 使用分区减少扫描范围
SELECT * FROM orders PARTITION (p2024_q1)
ORDERBY order_date
LIMIT100000, 20;

-- 使用EXPLAIN分析查询
EXPLAINFORMAT=JSON
SELECT o.order_id, o.order_date, u.username,
       SUM(oi.quantity * p.price) as total_amount
FROM orders o
JOINusers u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
GROUPBY o.order_id
HAVING total_amount > 1000
ORDERBY o.order_date DESC
LIMIT10;

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);
-- 或者更直接的JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- 1. 避免使用SELECT *，只选择需要的列
-- 优化前
SELECT * FROMusersWHERE user_id = 100;

-- 优化后
SELECT user_id, username, email FROMusersWHERE user_id = 100;

-- 2. 使用EXISTS代替IN（当子查询返回大量数据时）
-- 优化前
SELECT * FROM orders
WHERE user_id IN (SELECT user_id FROMusersWHEREstatus = 'active');

-- 优化后
SELECT * FROM orders o
WHEREEXISTS (SELECT1FROMusers u WHERE u.user_id = o.user_id AND u.status = 'active');

-- 3. 将复杂查询拆分为多个简单查询
-- 优化前：一个复杂的多表JOIN查询
-- 优化后：拆分为多个查询，在应用层组合数据

-- 4. 合理使用LIMIT
-- 优化前：不必要的大量数据检索
SELECT * FROM orders WHERE order_date > '2024-01-01';

-- 优化后：添加LIMIT限制
SELECT * FROM orders WHERE order_date > '2024-01-01'LIMIT100;

-- 5. 避免在WHERE子句中对列进行函数操作
-- 优化前
SELECT * FROM orders WHEREYEAR(order_date) = 2024;

-- 优化后
SELECT * FROM orders WHERE order_date >= '2024-01-01'AND order_date < '2025-01-01';

SHOW STATUS LIKE ‘innodb_buffer_pool_read%’;

# InnoDB缓冲池配置（通常设置为系统内存的70-80%）
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8  # 减少争用，通常设为CPU核心数

# 日志文件配置
innodb_log_file_size = 2G          # 更大的日志文件减少检查点
innodb_log_buffer_size = 64M       # 日志缓冲区大小

# 连接管理
max_connections = 500              # 根据应用需求调整
thread_cache_size = 100            # 线程缓存大小

# 查询缓存（MySQL 8.0+已移除，注意替代方案）
# 使用应用层缓存或ProxySQL查询缓存

# 临时表和排序优化
tmp_table_size = 256M              # 内存临时表最大大小
max_heap_table_size = 256M         # 内存表最大大小
sort_buffer_size = 4M              # 排序缓冲区大小

# InnoDB I/O优化
innodb_flush_method = O_DIRECT     # Linux系统推荐
innodb_io_capacity = 2000          # 根据磁盘性能调整
innodb_io_capacity_max = 4000      # 最大I/O容量

# 事务隔离与锁
transaction_isolation = READ-COMMITTED  # 大多数应用推荐
innodb_lock_wait_timeout = 50      # 锁等待超时时间

-- 1. 使用Performance Schema监控性能
-- 查看最耗时的SQL语句
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT/1000000000as total_sec,
  AVG_TIMER_WAIT/1000000000as avg_sec,
  MAX_TIMER_WAIT/1000000000as max_sec
FROM performance_schema.events_statements_summary_by_digest
ORDERBY SUM_TIMER_WAIT DESC
LIMIT10;

-- 2. 使用sys schema快速诊断
-- 查看锁等待情况
SELECT * FROM sys.innodb_lock_waits;

-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看表统计信息
SELECT * FROM sys.schema_table_statistics;

-- 3. 使用慢查询日志
-- 启用慢查询日志
SETGLOBAL slow_query_log = ON;
SETGLOBAL long_query_time = 1;  # 超过1秒的查询

-- 分析慢查询日志
# 使用mysqldumpslow工具
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 使用pt-query-digest工具
pt-query-digest /var/log/mysql/mysql-slow.log

-- 4. 实时监控
-- 查看当前连接和执行语句
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Sleep'
ORDERBYTIMEDESC;

-- 查看InnoDB状态
SHOWENGINEINNODBSTATUS;

-- 5. 使用MySQL Shell性能报告
-- MySQL Shell提供了高级诊断功能
\sql
\performance report