执行计划与慢查询定位

-- 查看执行计划(不实际执行)
EXPLAIN SELECT * FROM users WHERE age > 30;

-- MySQL 8.0+ 支持更详细的格式
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE age > 30;

-- 实际执行并返回详细统计信息(MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

EXPLAIN SELECT o.order_id, o.order_no, u.username 
FROM orders o 
JOIN users u ON o.user_id = u.user_id 
WHERE o.status = 'PAID' AND u.age > 25 
ORDER BY o.created_at DESC 
LIMIT 10;

+----+-------------+-------+------------+--------+---------------------+---------------------+---------+----------------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type   | possible_keys       | key                 | key_len | ref                  | rows  | filtered | Extra                                 |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+----------------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ref    | idx_user_status,idx_status | idx_status   | 10      | const                |  1520 |    10.00 | Using index condition; Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY,idx_age     | PRIMARY             | 4       | test.o.user_id       |     1 |    33.33 | Using where                           |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+----------------------+------+----------+---------------------------------------+

-- 关联查询:id 相同,从上往下执行
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 结果:id=1(table=orders), id=1(table=users) → 先查 orders,再查 users

-- 子查询:id 不同,大的先执行
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 30);
-- 结果:id=2(子查询,users表), id=1(主查询,orders表) → 先执行子查询

-- SIMPLE:简单查询
EXPLAIN SELECT * FROM users WHERE age > 30;

-- SUBQUERY:子查询
EXPLAIN SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'admin');

-- DERIVED:派生表
EXPLAIN SELECT * FROM (SELECT user_id, COUNT(*) as cnt FROM orders GROUP BY user_id) t WHERE cnt > 10;

-- DEPENDENT SUBQUERY:依赖外层的子查询(性能差)
EXPLAIN SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 分区表演示
CREATE TABLE order_partition (
    order_id INT,
    created_at DATE
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

EXPLAIN SELECT * FROM order_partition WHERE created_at >= '2023-01-01';
-- partitions: p2023,p2024

-- MyISAM 或 Memory 引擎的系统表
EXPLAIN SELECT * FROM mysql.proxies_priv WHERE 1=1;

-- 主键或唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE id = 100;  -- 主键
EXPLAIN SELECT * FROM users WHERE username = 'admin';  -- 唯一索引

-- JOIN 时使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- users 表的 type = eq_ref(使用主键)

-- 非唯一索引等值查询,可能返回多行
EXPLAIN SELECT * FROM orders WHERE status = 'PAID';  -- status 有索引但不是唯一

-- 索引范围扫描:>, <, >=, <=, BETWEEN, IN
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
EXPLAIN SELECT * FROM users WHERE age IN (20, 25, 30);

-- 全索引扫描:遍历整个索引树
EXPLAIN SELECT id FROM orders;  -- 如果 id 是主键(聚簇索引)
EXPLAIN SELECT status FROM orders;  -- 如果 status 有索引

-- 全表扫描:遍历整个表
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;  -- 索引失效
EXPLAIN SELECT * FROM orders;  -- 无 WHERE 条件

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- possible_keys: idx_user_id, idx_status
-- 表示这两个索引可能被使用

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- possible_keys: idx_user_id, idx_status
-- key: idx_user_id  (实际使用了 idx_user_id)

-- 创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 查询 1:只使用 user_id
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- key_len = 4 (INT 类型,user_id NOT NULL)

-- 查询 2:使用 user_id + status
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';
-- key_len = 4 + 10 (status VARCHAR(3), UTF8MB4: 3*3+1=10)

-- 查询 3:使用全部三个字段
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID' AND created_at > '2024-01-01';
-- key_len = 4 + 10 + 8 (DATETIME)

-- 联合索引:idx_user_status_created(user_id, status, created_at)
-- 最大 key_len = 4 + 10 + 8 = 22

-- 如果 key_len < 22,说明索引没有完全利用
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND created_at > '2024-01-01';
-- 跳过了 status,只用了 user_id
-- key_len = 4 (只用了第一列)

-- 常量比较
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- ref = const

-- 关联查询
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- orders 表:ref = test.u.id (使用 users 表的 id)
-- users 表:ref = test.o.user_id (使用 orders 表的 user_id)

-- 无索引:扫描全表
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- rows = 100000 (全表扫描)

-- 有索引:大幅减少
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- rows = 1520 (使用索引范围扫描)

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- rows = 1000, filtered = 10.00
-- 表示:扫描 1000 行,其中 10%(100 行)满足 status = 'PAID'

-- 覆盖索引:查询的所有列都在索引中
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;
-- Extra: Using index
-- 不需要回表,性能最优

-- WHERE 条件过滤
EXPLAIN SELECT * FROM users WHERE age > 30 AND username LIKE '%admin%';
-- Extra: Using where
-- MySQL 服务器层过滤,正常情况

-- 索引下推(ICP):在存储引擎层过滤
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status LIKE 'P%';
-- Extra: Using index condition
-- MySQL 5.6+ 优化,减少回表次数

-- 使用临时表
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary
-- 通常用于 GROUP BY, ORDER BY, 需要优化

-- 创建索引避免临时表
CREATE INDEX idx_status ON orders(status);
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using index (变成覆盖索引)

-- 文件排序:无法使用索引排序
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- Extra: Using filesort
-- 需要在内存或磁盘排序,性能差

-- 创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- Extra: NULL (使用索引排序)

-- 关联查询未使用索引,需要连接缓冲区
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_name = u.username;
-- Extra: Using join buffer (Block Nested Loop)
-- 表示 users 表没有使用索引,需要优化

-- 三重组合:WHERE 过滤 + 临时表 + 文件排序
EXPLAIN SELECT status, COUNT(*) FROM orders WHERE user_id = 100 GROUP BY status ORDER BY COUNT(*) DESC;
-- Extra: Using where; Using temporary; Using filesort
-- 性能很差,需要重点优化

-- WHERE 条件永远为 FALSE
EXPLAIN SELECT * FROM users WHERE id = 1 AND id = 2;
-- Extra: Impossible WHERE

-- OR 条件使用多个索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- Extra: Using union(idx_user_id, idx_status); Using where
-- MySQL 5.0+ 支持索引合并

-- Multi-Range Read 优化
EXPLAIN SELECT * FROM orders WHERE user_id IN (100, 200, 300);
-- Extra: Using index condition; Using MRR
-- MySQL 5.6+ 优化,减少随机 IO

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 查询执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- 分析:
-- type = ref:使用非唯一索引,较好
-- key = idx_user_id:确实使用了索引
-- rows = 100:扫描 100 行
-- Extra = NULL:无额外问题

-- 创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 查询 1:符合最左匹配
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- key = idx_user_status_created, key_len = 4

-- 查询 2:符合最左匹配(使用前两列)
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';
-- key = idx_user_status_created, key_len = 14

-- 查询 3:不符合最左匹配(跳过 user_id)
EXPLAIN SELECT * FROM orders WHERE status = 'PAID';
-- key = NULL, type = ALL(全表扫描)

-- 问题 SQL
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort (需要排序)

-- 优化:创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 再次查看
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: NULL (使用索引排序,无文件排序)

-- 查看慢查询日志开关
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看慢查询时间阈值(单位:秒)
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 查看是否记录未使用索引的 SQL
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 查看慢查询日志输出格式
SHOW VARIABLES LIKE 'log_output';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询时间阈值为 2 秒
SET GLOBAL long_query_time = 2;

-- 记录未使用索引的 SQL
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

[mysqld]
# 开启慢查询日志
slow_query_log = 1

# 慢查询时间阈值(秒)
long_query_time = 2

# 日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 记录未使用索引的 SQL
log_queries_not_using_indexes = 1

# 日志输出方式(FILE/TABLE)
log_output = FILE

# 扫描行数阈值(少于该值不记录)
min_examined_row_limit = 100

# Linux
systemctl restart mysqld

# 或
service mysql restart

-- 动态配置,立即生效,重启后失效
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 同时修改配置文件实现永久生效

-- 不要设置太大,否则无法捕获慢查询
SET GLOBAL long_query_time = 10;  -- × 太大

-- 也不要设置太小,否则日志爆炸
SET GLOBAL long_query_time = 0.1;  -- × 太小

-- 推荐值:1-3 秒
SET GLOBAL long_query_time = 2;  -- √ 合理

-- 开启后会记录所有未使用索引的 SQL,日志量可能很大
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 配合 min_examined_row_limit 过滤
SET GLOBAL min_examined_row_limit = 100;  -- 扫描少于 100 行不记录

-- FILE:记录到文件(推荐,性能好)
SET GLOBAL log_output = 'FILE';

-- TABLE:记录到 mysql.slow_log 表
SET GLOBAL log_output = 'TABLE';

-- 查询表日志
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- FILE,TABLE:同时记录到文件和表
SET GLOBAL log_output = 'FILE,TABLE';

# 查看最近的慢查询
tail -f /var/log/mysql/mysql-slow.log

# 查看慢查询数量
grep -c "Query_time" /var/log/mysql/mysql-slow.log

# 提取 SQL 语句
grep "Query_time" /var/log/mysql/mysql-slow.log -A 3

-- 查询最近的慢查询
SELECT 
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    db,
    sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 10;

-- 统计慢查询 TOP 10
SELECT 
    db,
    sql_text,
    COUNT(*) as exec_count,
    AVG(query_time) as avg_time,
    MAX(query_time) as max_time,
    SUM(rows_examined) as total_rows
FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY db, sql_text
ORDER BY avg_time DESC
LIMIT 10;

# 显示执行时间最长的 10 条 SQL
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 显示访问次数最多的 10 条 SQL
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 显示平均执行时间最长的 10 条 SQL
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

# 显示锁定时间最长的 10 条 SQL
mysqldumpslow -s l -t 10 /var/log/mysql/mysql-slow.log

# 显示返回记录数最多的 10 条 SQL
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 查找包含 "SELECT" 的慢查询 TOP 10
mysqldumpslow -s t -t 10 -g "SELECT" /var/log/mysql/mysql-slow.log

# 查询某个数据库的慢查询
mysqldumpslow -s t -t 10 -g "db_name" /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 152  Time=8.23s (1251s)  Lock=0.00s (0s)  Rows=100.0 (15200), root[root]@localhost
  SELECT * FROM orders WHERE status = 'S' ORDER BY created_at DESC LIMIT N

Count: 89  Time=5.12s (456s)  Lock=0.00s (0s)  Rows=50.0 (4450), root[root]@localhost
  SELECT * FROM users WHERE username LIKE 'S' ORDER BY id DESC LIMIT N

# Ubuntu/Debian
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

# macOS
brew install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/mysql-slow.log

# 分析最近 1 小时的慢查询
pt-query-digest --since '1h' /var/log/mysql/mysql-slow.log

# 分析某个时间段的慢查询
pt-query-digest --since '2024-01-01 00:00:00' --until '2024-01-02 00:00:00' /var/log/mysql/mysql-slow.log

# 过滤某个数据库
pt-query-digest --filter '$event->{db} =~ /mydb/' /var/log/mysql/mysql-slow.log

# 输出到文件
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

# 3600ms user time, 20ms system time, 24.73M rss, 204.84M vsz
# Current date: Mon Mar 30 12:00:00 2024
# Hostname: mysql-server
# Files: /var/log/mysql/mysql-slow.log
# Overall: 15.23k total, 152 unique, 45.23 QPS, 0.23x concurrency ________
# Time range: 2024-03-29T00:00:00 to 2024-03-30T00:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          3726s     1us   125s   245ms   100ms      3s    50ms
# Lock time            12s       0     1s   787us   159us    12ms    50us
# Rows sent         10.23M       0 100.00k  704.33   50.00   4.12k    0.99
# Rows examine     512.34M       0   1.50M  35.29k   1.50M 123.45k  100.00

# Profile
# Rank Query ID                            Response time   Calls R/Call V/M   Item
# ==== =================================== =============== ===== ====== ===== ===============
#    1 0x1234567890ABCDEF1234567890ABCDEF  1523.2345 40.9%  1523 1.0000  0.00 SELECT orders
#    2 0xFEDCBA0987654321FEDCBA0987654321   823.4567 22.1%   823 1.0000  0.00 SELECT users
#    3 0xABCDEF1234567890ABCDEF1234567890   512.3456 13.8%   512 1.0000  0.00 SELECT products

# Query 1: 45.23 QPS, 0.23x concurrency, ID 0x1234567890ABCDEF1234567890ABCDEF at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-03-29T00:00:00 to 2024-03-30T00:00:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10    1523
# Exec time     40   1523s     1s      5s      1s      3s   500ms      1s
# Lock time      0   152ms    50us   100us   100us   100us    10us   100us
# Rows sent      1 152.30k     100     100     100     100       0     100
# Rows examine  29 152.30M 100.00k 100.00k 100.00k 100.00k       0 100.00k
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################
#    1s  ################################################################
#   10s
#  100s
#    1s
#  10s
# Tables
#    SHOW TABLE STATUS LIKE 'orders'\G
#    SHOW CREATE TABLE `orders`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 100\G

-- 开启事件监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements_%';

-- 查询执行时间最长的 SQL
SELECT 
    DIGEST_TEXT as sql_text,
    COUNT_STAR as exec_count,
    SUM_TIMER_WAIT/1000000000 as total_time_sec,
    AVG_TIMER_WAIT/1000000000 as avg_time_sec,
    MAX_TIMER_WAIT/1000000000 as max_time_sec,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查询全表扫描的 SQL
SELECT 
    DIGEST_TEXT as sql_text,
    COUNT_STAR as exec_count,
    SUM_NO_INDEX_USED as no_index_count,
    SUM_ROWS_EXAMINED as rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

-- 查看执行时间最长的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 查看使用临时表的 SQL
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 查看最新的慢查询
SELECT * FROM sys.latest_file_io ORDER BY latency DESC LIMIT 10;

-- 创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- √ 命中索引(使用全部列)
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID' AND created_at > '2024-01-01';

-- √ 命中索引(使用前两列)
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';

-- √ 命中索引(只使用第一列)
SELECT * FROM orders WHERE user_id = 100;

-- × 不命中索引(跳过 user_id)
SELECT * FROM orders WHERE status = 'PAID';

-- × 不命中索引(跳过前两列)
SELECT * FROM orders WHERE created_at > '2024-01-01';

--  部分命中(只使用第一列,跳过 status)
SELECT * FROM orders WHERE user_id = 100 AND created_at > '2024-01-01';
-- key_len 显示只用了 user_id

-- 创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- × 需要回表
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- Extra: NULL (需要回表查询其他列)

-- √ 覆盖索引
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 100;
-- Extra: Using index (不需要回表)

-- √ 部分覆盖索引
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;
-- Extra: Using index (也不需要回表)

-- × 性能差
SELECT COUNT(*) FROM orders WHERE user_id = 100;

-- √ 使用覆盖索引
CREATE INDEX idx_user_id ON orders(user_id);
SELECT COUNT(user_id) FROM orders WHERE user_id = 100;
-- Extra: Using index

-- 选择性 = COUNT(DISTINCT column) / COUNT(*)
SELECT 
    COUNT(DISTINCT user_id) / COUNT(*) as user_id_selectivity,
    COUNT(DISTINCT status) / COUNT(*) as status_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) as created_at_selectivity
FROM orders;

-- 假设 orders 表有 100 万行数据
-- user_id: 10 万个不同值,选择性 = 100000/1000000 = 0.1 (高)
-- status: 5 个不同值,选择性 = 5/1000000 = 0.000005 (低)
-- created_at: 365 个不同值,选择性 = 365/1000000 = 0.000365 (中)

-- 索引设计:选择性高的列放前面
CREATE INDEX idx_user_created_status ON orders(user_id, created_at, status);

-- 查看某列的选择性
SELECT 
    COUNT(*) as total_rows,
    COUNT(DISTINCT status) as distinct_values,
    COUNT(DISTINCT status) / COUNT(*) as selectivity
FROM orders;

-- 选择性 < 0.1:索引效果差
-- 选择性 > 0.5:索引效果好
-- 选择性 > 0.8:索引效果很好

-- × 索引失效:使用函数
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type = ALL (全表扫描)

-- √ 索引生效:范围查询
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type = range

-- × 使用函数
WHERE DATE(created_at) = '2024-03-30'
WHERE UPPER(username) = 'ADMIN'
WHERE SUBSTRING(order_no, 1, 4) = '2024'

-- √ 优化
WHERE created_at >= '2024-03-30 00:00:00' AND created_at < '2024-03-31 00:00:00'
WHERE username = 'ADMIN' OR username = 'admin'
WHERE order_no LIKE '2024%'

-- 假设 user_id 是 INT 类型,order_no 是 VARCHAR 类型

-- × 隐式转换:字符串转数字
EXPLAIN SELECT * FROM orders WHERE user_id = '100';
-- 虽然可以工作,但性能稍差

-- × 隐式转换:数字转字符串,索引失效
EXPLAIN SELECT * FROM orders WHERE order_no = 20240330001;
-- type = ALL (全表扫描)

-- √ 类型匹配
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
EXPLAIN SELECT * FROM orders WHERE order_no = '20240330001';

-- × 隐式转换
WHERE user_id = '100'  -- INT 列用字符串
WHERE order_no = 123   -- VARCHAR 列用数字
WHERE created_at = '2024-03-30'  -- DATETIME 列用 DATE

-- √ 类型匹配
WHERE user_id = 100
WHERE order_no = '123'
WHERE created_at = '2024-03-30 00:00:00'

-- × 索引可能失效
EXPLAIN SELECT * FROM orders WHERE status != 'CANCELLED';
-- type = ALL 或 range(取决于数据分布)

-- √ 改用 IN
EXPLAIN SELECT * FROM orders WHERE status IN ('PAID', 'SHIPPED', 'COMPLETED');
-- type = range

-- × 索引失效(MySQL 5.6 之前)
EXPLAIN SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- type = ALL

-- √ 使用 UNION
EXPLAIN 
SELECT * FROM orders WHERE user_id = 100
UNION
SELECT * FROM orders WHERE status = 'PAID';
-- 各自使用索引

-- √ MySQL 5.6+ 支持索引合并
EXPLAIN SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- type = index_merge (使用两个索引)

-- × 索引失效
EXPLAIN SELECT * FROM users WHERE username LIKE '%admin%';
-- type = ALL

-- × 索引失效
EXPLAIN SELECT * FROM users WHERE username LIKE '%admin';
-- type = ALL

-- √ 索引生效
EXPLAIN SELECT * FROM users WHERE username LIKE 'admin%';
-- type = range

-- 解决方案:使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX idx_username(username);
SELECT * FROM users WHERE MATCH(username) AGAINST('admin');

-- × 可能不使用索引(取决于数据分布)
EXPLAIN SELECT * FROM orders WHERE status IS NULL;
-- type = ref 或 ALL

-- √ 优化:设置默认值
ALTER TABLE orders MODIFY status VARCHAR(20) NOT NULL DEFAULT 'PENDING';
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';
-- type = ref

-- 创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- × 范围查询后的列索引失效
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status > 'A' AND created_at > '2024-01-01';
-- key_len 显示只用了 user_id + status,created_at 索引失效

-- √ 优化:调整索引顺序
CREATE INDEX idx_user_created_status ON orders(user_id, created_at, status);
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND created_at > '2024-01-01' AND status > 'A';
-- 三个字段都能使用索引

-- 问题 SQL
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort (文件排序)

-- 优化:创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 再次查看
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: NULL (使用索引排序)

-- 进一步优化:覆盖索引
EXPLAIN SELECT order_id, user_id, created_at FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: Using index (覆盖索引,最优)

-- 问题 SQL
EXPLAIN SELECT status, COUNT(*) FROM orders WHERE user_id = 100 GROUP BY status;
-- Extra: Using temporary; Using filesort (临时表 + 排序)

-- 优化:创建联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 再次查看
EXPLAIN SELECT status, COUNT(*) FROM orders WHERE user_id = 100 GROUP BY status;
-- Extra: Using index (覆盖索引,无需临时表)

-- 问题 SQL:深度分页
EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- rows = 100010 (扫描大量数据)

-- 优化 1:使用子查询
EXPLAIN SELECT * FROM orders o 
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t 
ON o.id = t.id;
-- 先查询主键(覆盖索引),再关联查询,性能更好

-- 优化 2:记录上次的最大 ID
EXPLAIN SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- rows = 10 (使用范围查询)

-- 案例:按用户名查询
EXPLAIN SELECT * FROM users WHERE username = 'admin';
-- type = ALL

-- 优化:创建索引
CREATE INDEX idx_username ON users(username);

EXPLAIN SELECT * FROM users WHERE username = 'admin';
-- type = ref

-- 案例:查询用户 ID 和状态
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 100;
-- Extra: NULL (需要回表)

-- 优化:创建覆盖索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 100;
-- Extra: Using index (覆盖索引)

-- 案例:按创建时间排序
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- Extra: Using filesort

-- 优化:创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- Extra: NULL

-- 案例:按状态分组统计
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary

-- 优化:创建索引
CREATE INDEX idx_status ON orders(status);

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using index

-- 案例:MySQL 选择了错误的索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';
-- possible_keys: idx_user_id, idx_status
-- key: idx_status (选择了错误的索引)

-- 优化 1:使用 FORCE INDEX 强制使用索引
EXPLAIN SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100 AND status = 'PAID';

-- 优化 2:使用 USE INDEX 建议 MySQL 使用索引
EXPLAIN SELECT * FROM orders USE INDEX(idx_user_id) WHERE user_id = 100 AND status = 'PAID';

-- 优化 3:创建更合适的联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 案例:关联查询
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- users 表: type = eq_ref (使用主键,好)
-- orders 表: type = ALL (无索引,差)

-- 优化:为关联字段创建索引
CREATE INDEX idx_user_id ON orders(user_id);

EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- orders 表: type = ref

-- 问题 SQL:深度分页
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;
-- 扫描 100010 行

-- 优化 1:使用子查询
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 10) t
ON o.id = t.id;
-- 子查询使用覆盖索引,性能更好

-- 优化 2:记录上次的最大值
SELECT * FROM orders 
WHERE created_at < '2024-03-30 12:00:00' 
ORDER BY created_at DESC 
LIMIT 10;
-- 使用范围查询,无需扫描大量数据

-- 优化 3:业务限制(禁止深度分页)
-- 只允许查看前 100 页
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000;

-- 问题 SQL
SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- MySQL 5.6 之前:全表扫描
-- MySQL 5.6+:索引合并(index_merge),性能中等

-- 优化 1:使用 UNION
SELECT * FROM orders WHERE user_id = 100
UNION
SELECT * FROM orders WHERE status = 'PAID';
-- 各自使用索引,性能好

-- 优化 2:创建联合索引(如果条件固定)
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- 仍然可以使用索引合并,性能更好

-- 查看是否启用
SHOW VARIABLES LIKE 'profiling';

-- 启用 profiling
SET profiling = 1;

-- 或设置保留的历史记录数
SET profiling_history_size = 100;

-- 执行 SQL
SELECT * FROM orders WHERE user_id = 100;

-- 查看最近的 SQL 列表
SHOW PROFILES;

-- 查看指定 SQL 的详细耗时(Query_ID 来自 SHOW PROFILES)
SHOW PROFILE FOR QUERY 1;

-- 查看所有性能指标
SHOW PROFILE ALL FOR QUERY 1;

-- 查看特定指标
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000100 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000020 |
| init                 | 0.000030 |
| System lock          | 0.000010 |
| optimizing           | 0.000010 |
| statistics           | 0.000050 |
| preparing            | 0.000020 |
| executing            | 0.000005 |
| Sending data         | 0.500000 |  ← 主要耗时
| end                  | 0.000010 |
| query end            | 0.000005 |
| closing tables       | 0.000010 |
| freeing items        | 0.000020 |
| cleaning up          | 0.000010 |
+----------------------+----------+

-- 启用等待事件监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%wait/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_waits_%';

-- 查看等待事件
SELECT 
    EVENT_NAME,
    COUNT_STAR as count,
    SUM_TIMER_WAIT/1000000000 as total_sec,
    AVG_TIMER_WAIT/1000000000 as avg_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE SUM_TIMER_WAIT > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看 SQL 执行统计
SELECT 
    DIGEST_TEXT as sql_text,
    COUNT_STAR as exec_count,
    SUM_TIMER_WAIT/1000000000 as total_sec,
    AVG_TIMER_WAIT/1000000000 as avg_sec,
    MAX_TIMER_WAIT/1000000000 as max_sec,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看文件 IO 统计
SELECT 
    FILE_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_NUMBER_OF_BYTES_READ/1024/1024 as read_mb,
    SUM_NUMBER_OF_BYTES_WRITE/1024/1024 as write_mb
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 10;

-- 查看执行时间最长的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 查看使用临时表或文件排序的 SQL
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

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

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

-- 查看 IO 统计
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

pt-index-usage /var/log/mysql/mysql-slow.log --host=localhost --user=root --password=123456

pt-mysql-summary --host=localhost --user=root --password=123456

SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY created_at DESC 
LIMIT 0, 20;

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 0, 20;

-- 结果:
-- type: ref (使用索引 idx_user_id)
-- key: idx_user_id
-- rows: 1520
-- Extra: Using filesort (文件排序)

-- 方案 1:创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 再次查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 0, 20;
-- type: ref
-- key: idx_user_created
-- Extra: NULL (无文件排序)

-- 方案 2:使用覆盖索引(如果只需要部分字段)
EXPLAIN SELECT order_id, user_id, created_at, status 
FROM orders 
WHERE user_id = 1001 
ORDER BY created_at DESC 
LIMIT 0, 20;
-- Extra: Using index (覆盖索引)

SELECT d.* 
FROM dynamics d
JOIN user_follows f ON d.user_id = f.follow_user_id
WHERE f.user_id = 1001
ORDER BY d.created_at DESC
LIMIT 0, 20;

EXPLAIN SELECT d.* FROM dynamics d JOIN user_follows f ON d.user_id = f.follow_user_id WHERE f.user_id = 1001 ORDER BY d.created_at DESC LIMIT 0, 20;

-- 结果:
-- dynamics 表: type: ALL (全表扫描)
-- Extra: Using temporary; Using filesort (临时表 + 文件排序)

-- 方案 1:创建索引
CREATE INDEX idx_user_created ON dynamics(user_id, created_at);
CREATE INDEX idx_user_id ON user_follows(user_id);

-- 再次查看执行计划
EXPLAIN SELECT d.* FROM dynamics d JOIN user_follows f ON d.user_id = f.follow_user_id WHERE f.user_id = 1001 ORDER BY d.created_at DESC LIMIT 0, 20;
-- dynamics 表: type: ref
-- Extra: Using filesort (仍有文件排序)

-- 方案 2:改写 SQL,先查关注列表
SELECT d.* FROM dynamics d
WHERE d.user_id IN (
    SELECT follow_user_id FROM user_follows WHERE user_id = 1001
)
ORDER BY d.created_at DESC
LIMIT 0, 20;
-- 子查询使用索引,但需要创建临时表

-- 方案 3:使用冗余字段(推荐)
-- 在 dynamics 表中添加冗余字段:is_followed(是否被关注)
-- 或使用消息队列推送动态到用户的 feed 表

SELECT 
    status,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;

EXPLAIN SELECT status, COUNT(*) as order_count, SUM(amount) as total_amount FROM orders WHERE created_at >= '2024-01-01' GROUP BY status;

-- 结果:
-- type: ALL (全表扫描)
-- Extra: Using temporary; Using filesort

-- 方案 1:创建联合索引
CREATE INDEX idx_created_status ON orders(created_at, status, amount);

-- 再次查看执行计划
EXPLAIN SELECT status, COUNT(*) as order_count, SUM(amount) as total_amount FROM orders WHERE created_at >= '2024-01-01' GROUP BY status;
-- type: range
-- Extra: Using index; Using temporary (使用覆盖索引,但仍有临时表)

-- 方案 2:使用物化视图(MySQL 不支持,可用定时任务替代)
CREATE TABLE order_stats_daily (
    stat_date DATE,
    status VARCHAR(20),
    order_count INT,
    total_amount DECIMAL(10,2),
    PRIMARY KEY(stat_date, status)
);

-- 定时任务每天统计一次
INSERT INTO order_stats_daily
SELECT 
    CURDATE(),
    status,
    COUNT(*),
    SUM(amount)
FROM orders
WHERE created_at >= CURDATE()
GROUP BY status
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount);

-- 查询时直接从统计表读取
SELECT * FROM order_stats_daily WHERE stat_date = '2024-03-30';

SELECT * FROM products WHERE name LIKE '%手机%';

EXPLAIN SELECT * FROM products WHERE name LIKE '%手机%';

-- 结果:
-- type: ALL (全表扫描)
-- Extra: Using where

-- 方案 1:使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX idx_name(name);

SELECT * FROM products WHERE MATCH(name) AGAINST('手机');
-- 使用全文索引,性能好

-- 方案 2:使用 Elasticsearch
-- 将商品数据同步到 ES,使用 ES 进行搜索
-- 适合大规模数据搜索

-- 方案 3:使用 LIKE '手机%' (如果业务允许)
SELECT * FROM products WHERE name LIKE '手机%';
-- 可以使用索引

UPDATE orders SET status = 'CANCELLED' WHERE created_at < '2023-01-01';

-- 方案 1:分批更新
-- 每次更新 1000 条
UPDATE orders SET status = 'CANCELLED' 
WHERE created_at < '2023-01-01' AND status != 'CANCELLED'
LIMIT 1000;

-- 循环执行,直到影响行数为 0
-- 脚本示例(Python):
"""
affected_rows = 1
while affected_rows > 0:
    cursor.execute('''
        UPDATE orders SET status = 'CANCELLED' 
        WHERE created_at < '2023-01-01' AND status != 'CANCELLED'
        LIMIT 1000
    ''')
    affected_rows = cursor.rowcount
    connection.commit()
    time.sleep(1)  # 休息 1 秒,降低影响
"""

-- 方案 2:使用存储过程
DELIMITER //
CREATE PROCEDURE batch_update_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE affected_rows INT DEFAULT 1;
    
    WHILE affected_rows > 0 DO
        UPDATE orders SET status = 'CANCELLED' 
        WHERE created_at < '2023-01-01' AND status != 'CANCELLED'
        LIMIT 1000;
        
        SET affected_rows = ROW_COUNT();
        DO SLEEP(1);
    END WHILE;
END //
DELIMITER ;

CALL batch_update_orders();

-- 方案 3:归档历史数据
-- 创建归档表
CREATE TABLE orders_archive LIKE orders;

-- 迁移数据
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2023-01-01';

-- 删除原表数据
DELETE FROM orders WHERE created_at < '2023-01-01';

-- 查看 possible_keys 和 key
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- possible_keys: idx_user_id
-- key: NULL (未使用索引)

-- 原因 1:选择性太低
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders;
-- 结果 < 0.1,选择性太低

-- 原因 2:隐式转换
SHOW CREATE TABLE orders;
-- user_id INT
SELECT * FROM orders WHERE user_id = '100';  -- 字符串

-- 原因 3:使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2024-03-30';

-- 原因 4:数据量小
SELECT COUNT(*) FROM orders;  -- 数据量很小

-- 检查配置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 测试慢查询
SELECT SLEEP(3);  -- 执行 3 秒
-- 查看是否记录到日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 检查日志文件权限
-- Linux:
-- ls -l /var/log/mysql/mysql-slow.log
-- chown mysql:mysql /var/log/mysql/mysql-slow.log
-- chmod 644 /var/log/mysql/mysql-slow.log

-- 查看索引数量
SHOW INDEX FROM orders;

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders';

-- 分析表(更新统计信息)
ANALYZE TABLE orders;

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE ...;

-- 第 1 页:扫描 10 行
SELECT * FROM orders ORDER BY id LIMIT 0, 10;

-- 第 10000 页:扫描 100010 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 方案 1:使用子查询
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;

-- 方案 2:记录上次的最大 ID
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 方案 3:限制分页深度(业务层面)
-- 只允许查看前 100 页

-- 方案 1:使用覆盖索引
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(status) FROM orders;  -- 使用覆盖索引

-- 方案 2:缓存计数
CREATE TABLE table_counts (
    table_name VARCHAR(50) PRIMARY KEY,
    row_count BIGINT
);

INSERT INTO table_counts VALUES ('orders', 0);

-- 每次插入/删除时更新
INSERT INTO orders (...) VALUES (...);
UPDATE table_counts SET row_count = row_count + 1 WHERE table_name = 'orders';

-- 查询时直接读取
SELECT row_count FROM table_counts WHERE table_name = 'orders';

-- 方案 3:使用 Redis 缓存计数
-- 每次插入/删除时更新 Redis 计数器

发现问题 → 定位问题 → 分析原因 → 制定方案 → 实施优化 → 验证效果 → 监控改进
    ↓          ↓          ↓          ↓          ↓          ↓          ↓
慢查询日志   EXPLAIN   索引/锁/SQL  创建索引    验证计划   对比性能   持续监控

-- 查看执行计划
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0.18+

-- 慢查询配置
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看索引
SHOW INDEX FROM table_name;

-- 分析表(更新统计信息)
ANALYZE TABLE table_name;

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

-- 查看性能统计
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Profile 分析
SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;