社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL 慢查询优化:索引失效的十大场景与解决方案

马哥Linux运维 • 11 月前 • 348 次点击  

MySQL 慢查询优化:索引失效的十大场景与解决方案

一、概述

1.1 背景介绍

MySQL慢查询是生产环境中最常见的性能瓶颈之一。虽然我们精心设计了索引,但在实际运行中却发现查询依然很慢,EXPLAIN分析显示索引并未生效。索引失效的原因复杂多样,理解这些常见场景并掌握优化方法,是每个后端工程师和DBA的必备技能。本文将系统性地剖析索引失效的十大典型场景,并提供可落地的解决方案。

1.2 技术特点

  • • 特点一:索引失效往往不是索引本身的问题,而是SQL写法、数据类型转换、函数使用等导致优化器无法使用索引
  • • 特点二:同一个查询在不同数据量、不同数据分布下,优化器可能做出完全不同的执行计划选择
  • • 特点三:理解MySQL优化器的cost-based选择机制,是预判和解决索引失效问题的关键

1.3 适用场景

  • • 场景一:生产环境出现慢查询告警,需要快速定位索引失效原因并优化
  • • 场景二:代码审查阶段发现潜在的索引失效风险,需要提前重构SQL
  • • 场景三:数据库架构设计阶段,需要规避常见的索引失效陷阱

1.4 环境要求

组件
版本要求
说明
MySQL
5.7+/8.0+
本文以MySQL 8.0为主
权限
SELECT/EXPLAIN权限
分析执行计划需要的最小权限
工具
pt-query-digest等
可选的慢查询分析工具
监控
Prometheus+Grafana
推荐的监控方案

二、详细步骤

2.1 准备工作

◆ 2.1.1 开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE'%slow_query%';
SHOW VARIABLES LIKE'long_query_time';

-- 开启慢查询日志(动态设置)
SETGLOBAL slow_query_log ='ON';
SETGLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SETGLOBAL long_query_time =1;  -- 超过1秒记录

-- 记录未使用索引的查询
SETGLOBAL log_queries_not_using_indexes ='ON';

配置文件永久配置(my.cnf):

[mysqld]
# 慢查询日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

◆ 2.1.2 准备测试数据

-- 创建测试表
CREATE TABLE `users` (
  `id` bigintNOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `age` intDEFAULTNULL,
  `status` tinyint DEFAULT'1',
  `created_at` datetime DEFAULTCURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`),
  KEY `idx_age` (`age`),
  KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据(100万条)
DELIMITER $$
CREATEPROCEDURE generate_test_data()
BEGIN
DECLARE i INTDEFAULT1;
  WHILE i <=1000000 DO
INSERT INTO users (username, email, age, status, created_at)
VALUES (
      CONCAT('user', i),
      CONCAT('user', i, '@example.com'),
FLOOR(RAND() *60) +18,
FLOOR(RAND() *3),
      DATE_ADD('2020-01-01', INTERVALFLOOR(RAND() *1460) DAY)
    );
SET i = i +1;
END WHILE;
END$$
DELIMITER ;

CALL generate_test_data();

◆ 2.1.3 EXPLAIN基础知识

-- EXPLAIN基本用法
EXPLAIN SELECT*FROM users WHERE age =25;

-- EXPLAIN输出关键字段说明:
-- type: 连接类型 (ALL < index < range < ref < eq_ref < const)
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 使用的索引长度
-- rows: 扫描的行数
-- Extra: 额外信息 (Using where, Using index, Using filesort等)

2.2 十大索引失效场景

◆ 2.2.1 场景一:在索引列上使用函数

问题示例:




    
-- 索引失效:在索引列上使用函数
EXPLAIN SELECT*FROM users WHEREYEAR(created_at) =2023;

EXPLAIN结果:

type: ALL
key: NULL
rows: 1000000
Extra: Using where

原因分析: 当对索引列使用函数时,MySQL无法使用索引,因为索引存储的是原始值,而不是函数计算后的值。

解决方案:

-- 方案1:改写查询条件,避免函数
EXPLAIN SELECT*FROM users
WHERE created_at >='2023-01-01'AND created_at <'2024-01-01';

-- 方案2:创建函数索引(MySQL 8.0+)
ALTER TABLE users ADD INDEX idx_created_year ((YEAR(created_at)));
EXPLAIN SELECT*FROM users WHEREYEAR(created_at) =2023;

其他常见函数导致索引失效的例子:

-- ❌ 索引失效
SELECT*FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') ='2023-01';
SELECT*FROM users WHERESUBSTRING(username, 1, 3) ='adm';
SELECT*FROM users WHERE CONCAT(username, email) ='adminadmin@test.com';

-- ✅ 优化后
SELECT*FROM users WHERE created_at >='2023-01-01'AND created_at <'2023-02-01';
SELECT*FROM users WHERE username LIKE'adm%';
-- 对于CONCAT,考虑分别查询或创建虚拟列索引

◆ 2.2.2 场景二:隐式类型转换

问题示例:

-- 表结构:username varchar(50), 有索引
-- 索引失效:字符串类型的字段使用数字查询
EXPLAIN SELECT*FROM users WHERE username =123456;

-- type: ALL, key: NULL, 全表扫描

原因分析: MySQL会将字符串转换为数字进行比较,相当于 CAST(username AS SIGNED) = 123456,触发了函数计算导致索引失效。

解决方案:

-- 正确写法:使用字符串类型
EXPLAIN SELECT*FROM users WHERE username ='123456';
-- type: ref, key: idx_username, 使用索引

其他类型转换场景:

-- ❌ 数字字段使用字符串(通常能用索引,但不推荐)
SELECT*FROM users WHERE age ='25';  -- age是int类型

-- ❌ 日期类型使用字符串
SELECT *FROM users WHERE created_at =20230101;  -- created_at是datetime

-- ✅ 正确写法
SELECT*FROM users WHERE age =25;
SELECT*FROM users WHERE created_at ='2023-01-01';

◆ 2.2.3 场景三:LIKE查询以通配符开头

问题示例:

-- 索引失效:前缀通配符
EXPLAIN SELECT*FROM users WHERE username LIKE'%admin%';
EXPLAIN SELECT*FROM users WHERE username LIKE'%admin';

-- type: ALL, key: NULL

原因分析: B+树索引是按照索引列的值从左到右顺序存储的,前缀通配符无法利用索引的有序性进行快速定位。

解决方案:

-- ✅ 后缀通配符可以使用索引
EXPLAIN SELECT*FROM users WHERE username LIKE'admin%';
-- type: range, key: idx_username

-- 对于必须使用前缀模糊查询的场景:
-- 方案1:使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);
SELECT*FROM users WHEREMATCH(username) AGAINST('admin'INNATURALLANGUAGE MODE);

-- 方案2:使用ElasticSearch等专业全文检索引擎

-- 方案3:反向索引(将字符串反转存储)
ALTER TABLE users ADDCOLUMN username_reverse VARCHAR(50);
UPDATE users SET username_reverse = REVERSE(username);
ALTER TABLE users ADD INDEX idx_username_reverse (username_reverse);

-- 查询时反转搜索词
SELECT*FROM users WHERE username_reverse LIKE REVERSE('admin') ||'%';

◆ 2.2.4 场景四:OR条件中有未建索引的列

问题示例:

-- status有索引,age有索引,但OR连接
EXPLAIN SELECT*FROM users WHERE status =1OR age =25;
-- 可能使用index_merge,也可能全表扫描

-- status有索引,但created_at无独立索引
EXPLAIN SELECT*FROM users WHERE status =1OR created_at >'2023-01-01';
-- type: ALL, 全表扫描

原因分析: OR条件要求两侧的条件都能使用索引,MySQL才会考虑使用index_merge。如果任一条件无法使用索引,则可能放弃所有索引。

解决方案:

-- 方案1:改写为UNION(推荐)
EXPLAIN
SELECT*FROM users WHERE status =1
UNION
SELECT*FROM users WHERE age =25;

-- 方案2:确保OR两侧都有索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);
EXPLAIN SELECT*FROM users WHERE status = 1OR created_at >'2023-01-01';

-- 方案3:根据业务逻辑改写为IN(适用于枚举值)
EXPLAIN SELECT*FROM users WHERE status IN (0, 1) AND age =25;

◆ 2.2.5 场景五:联合索引不满足最左前缀原则

问题示例:

-- 联合索引: KEY idx_status_created (status, created_at)

-- ✅ 使用索引:满足最左前缀
EXPLAIN SELECT*FROM users WHERE status =1;
EXPLAIN SELECT*FROM users WHERE status =1AND created_at >'2023-01-01';

-- ❌ 不使用索引:跳过status直接使用created_at
EXPLAIN SELECT*FROM users WHERE created_at >'2023-01-01';
-- type: ALL, key: NULL

原因分析: 联合索引是按照索引列的顺序构建B+树,必须从最左侧的列开始才能有效利用索引结构。

解决方案:

-- 方案1:调整WHERE条件顺序(如果业务允许)
EXPLAIN SELECT*FROM users WHERE status =1AND created_at >'2023-01-01';

-- 方案2:创建单独的索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- 方案3:调整联合索引顺序(根据查询模式决定)
ALTER TABLE users DROP INDEX idx_status_created;
ALTER TABLE users ADD INDEX idx_created_status (created_at, status);

联合索引使用规则总结:

-- 假设联合索引: INDEX idx_abc (a, b, c)

-- ✅ 使用索引
WHERE a =1
WHERE a =1AND b =2
WHERE a =1AND b =2AND c =3
WHERE a =1AND c =3-- 只使用a部分

-- ❌ 不使用索引
WHERE b =2
WHERE c =3
WHERE b =2AND c =3

-- ⚠️ 部分使用索引
WHERE a =1AND b >2AND c =3-- 使用a,b,但c无法使用(范围查询后的列无法使用)

◆ 2.2.6 场景六:不等于操作符(!=、<>)

问题示例:

-- 可能索引失效
EXPLAIN SELECT*FROM users WHERE status !=1;
EXPLAIN SELECT*FROM users WHERE status <>1;

-- type: ALL 或 range,取决于数据分布

原因分析: 不等于操作需要扫描大量数据,MySQL优化器会评估成本,如果不等于条件过滤后的数据量过大(如超过30%),优化器可能认为全表扫描更高效。

解决方案:

-- 方案1:改写为IN或OR(适用于已知的取值范围)
-- 假设status只有0,1,2三个值
EXPLAIN SELECT*FROM users WHERE status IN (0, 2);

-- 方案2:使用覆盖索引减少回表
ALTER TABLE users ADD INDEX idx_status_id (status, id);
EXPLAIN SELECT id FROM users WHERE status !=1;
-- type: range, Extra: Using where; Using index

-- 方案3:如果!=1的数据很少,考虑单独建表或分区存储

验证优化器选择:

-- 强制使用索引
EXPLAIN SELECT*FROM users FORCE INDEX(idx_status) WHERE status !=1;

-- 查看优化器成本估算
EXPLAIN FORMAT=JSON SELECT*FROM users WHERE status !=1\G

◆ 2.2.7 场景七:IS NULL 和 IS NOT NULL

问题示例:

-- 可能索引失效(取决于NULL值的比例)
EXPLAIN SELECT*FROM users WHERE age ISNULL;
EXPLAIN SELECT*FROM users WHERE age ISNOT NULL;

原因分析:

  • • MySQL可以在索引中存储NULL值
  • • IS NULL通常能使用索引(如果NULL值较少)
  • • IS NOT NULL可能不使用索引(如果NULL值较少,意味着NOT NULL数据很多)

解决方案:

-- 方案1:设计表时避免NULL
ALTER TABLE users MODIFY age INTNOT NULLDEFAULT0;

-- 方案2:使用特殊值代替NULL
UPDATE users SET age =-1WHERE age ISNULL;
SELECT*FROM users WHERE age =-1;

-- 方案3:使用覆盖索引
CREATE INDEX idx_age_id ON users(age, id);
SELECT id, age FROM users WHERE age ISNOT NULL;
-- Extra: Using where; Using index

NULL值处理建议:

-- 表设计时的最佳实践
CREATE TABLE users_optimized (
  id BIGINTNOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULLDEFAULT'',
  age INTNOT NULLDEFAULT0,
  status TINYINT NOT NULLDEFAULT1,
PRIMARY KEY (id)
) ENGINE=InnoDB;

◆ 2.2.8 场景八:范围查询后的列无法使用索引

问题示例:

-- 联合索引: KEY idx_status_age_created (status, age, created_at)

EXPLAIN SELECT*FROM users
WHERE status =1AND age >25AND created_at >'2023-01-01';

-- key: idx_status_age_created
-- 但只使用了status和age,created_at未使用索引

原因分析: 在联合索引中,一旦出现范围查询(>、

解决方案:

-- 方案1:调整索引顺序,将范围查询的列放在最后
ALTER TABLE users DROP INDEX idx_status_age_created;
ALTER TABLE users ADD INDEX idx_status_created_age (status, created_at, age);

-- 此时查询改为:
SELECT*FROM users
WHERE status =1AND created_at >'2023-01-01'AND age >25;

-- 方案2:分别创建索引,让优化器选择
CREATE INDEX idx_status_age ON users(status, age);
CREATE INDEX idx_status_created ON users(status, created_at);

-- 方案3:改写为精确匹配
SELECT*FROM users
WHERE status =1AND age IN (26, 27, 28, 29, 30) AND created_at >'2023-01-01';

◆ 2.2.9 场景九:数据分布导致优化器放弃索引

问题示例:

-- 假设表中99%的用户status都是1
EXPLAIN SELECT*FROM users WHERE status =1;
-- type: ALL, key: NULL, 优化器选择全表扫描

原因分析: MySQL优化器是基于成本(Cost-Based)的,如果条件过滤后的数据占比过高(通常>20-30%),优化器会认为全表扫描比走索引再回表更快。

解决方案:

-- 方案1:使用覆盖索引避免回表
CREATE INDEX idx_status_cover ON users(status, id, username, email);
SELECT id, username, email FROM users WHERE status =1;
-- Extra: Using where; Using index

-- 方案2:强制使用索引(谨慎使用)
SELECT*FROM users FORCE INDEX(idx_status) WHERE status =1;

-- 方案3:分区表设计
ALTER TABLE users PARTITIONBY LIST(status) (
PARTITION p0 VALUESIN (0),
PARTITION p1 VALUESIN (1),
PARTITION p2 VALUESIN (2)
);

-- 方案4:调整数据模型,增加过滤条件
SELECT*FROM users
WHERE status =1AND created_at >'2024-01-01';  -- 增加时间过滤

分析优化器成本:

-- 查看索引统计信息
SHOW INDEX FROM users;

-- 更新统计信息
ANALYZE TABLE users;

-- 查看优化器trace
SET optimizer_trace='enabled=on';
SELECT*FROM users WHERE status =1;
SELECT*FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

◆ 2.2.10 场景十:字符集和排序规则不一致

问题示例:

-- 表字符集: utf8mb4, 排序规则: utf8mb4_general_ci
-- 查询使用不同的排序规则
EXPLAIN SELECT*FROM users
WHERE username ='test'COLLATE utf8mb4_bin;

-- 可能导致索引失效或无法使用索引优化排序

原因分析: 当查询条件或ORDER BY指定的排序规则与索引的排序规则不一致时,MySQL可能无法有效使用索引。

解决方案:

-- 方案1:统一字符集和排序规则
ALTER TABLE users CONVERTTOCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 方案2:创建特定排序规则的索引
ALTER TABLE users ADD INDEX idx_username_bin (username) COLLATE utf8mb4_bin;

-- 方案3:查询时不指定COLLATE
SELECT*FROM users WHERE username ='test';

三、示例代码和配置

3.1 慢查询分析脚本

◆ 3.1.1 自动化分析脚本

#!/bin/bash
# 慢查询分析脚本: slow_query_analyzer.sh

SLOW_LOG="/var/log/mysql/slow.log"
REPORT_FILE="/tmp/slow_query_report_$(date +%Y%m%d).txt"

echo"========== MySQL慢查询分析报告 ==========" > "$REPORT_FILE"
echo"生成时间: $(date '+%Y-%m-%d %H:%M:%S')" >> "$REPORT_FILE"
echo"" >> "$REPORT_FILE"

# 使用pt-query-digest分析慢查询
ifcommand -v pt-query-digest &> /dev/null; then
echo"【TOP 10 慢查询】" >> "$REPORT_FILE"
    pt-query-digest "$SLOW_LOG" --limit 10 >> "$REPORT_FILE"
else
echo"pt-query-digest未安装,使用mysqldumpslow分析" >> "$REPORT_FILE"
    mysqldumpslow -s t -t 10 "$SLOW_LOG" >> "$REPORT_FILE"
fi

# 检查未使用索引的查询
echo -e "\n【未使用索引的查询】" >> "$REPORT_FILE"
grep "Query_time""$SLOW_LOG" | head -20 >> "$REPORT_FILE"

echo"分析完成,报告保存至: $REPORT_FILE"

◆ 3.1.2 索引使用情况监控

-- 查看索引使用情况
SELECT
    object_schema AS database_name,
    object_name AS table_name,
    index_name,
    COUNT_STAR AS index_usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name ISNOT NULL
ORDERBY COUNT_STAR DESC
LIMIT 20;

-- 查找未使用的索引
SELECT
    object_schema AS database_name,
    object_name AS table_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name ISNOT NULL
AND index_name !='PRIMARY'
AND COUNT_STAR =0
ORDERBY object_schema, object_name;

3.2 SQL优化实战案例

◆ 案例一:订单查询优化

场景描述:电商订单表,需要查询指定用户在某个时间范围内的订单,并按创建时间倒序排列。

原始SQL(慢):

-- 表结构
CREATE TABLE orders (
  id BIGINTPRIMARY KEY AUTO_INCREMENT,
  user_id BIGINTNOT NULL,
  order_no VARCHAR(32) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 慢查询SQL
SELECT*FROM orders
WHERE user_id =123456
AND created_at >='2024-01-01'
AND created_at <'2024-02-01'
ORDERBY created_at DESC
LIMIT 20;

-- EXPLAIN结果:
-- type: ref, key: idx_user_id, rows: 50000, Extra: Using where; Using filesort

优化方案:

-- 创建联合索引,覆盖查询条件和排序
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);

-- 优化后的查询
SELECT*FROM orders
WHERE user_id =123456
AND created_at >='2024-01-01'
AND created_at <'2024-02-01'
ORDERBY created_at DESC
LIMIT 20;

-- EXPLAIN结果:
-- type: range, key: idx_user_created, rows: 100, Extra: Using where

性能对比:

-- 测试脚本
SET profiling =1;

-- 原始查询
SELECT*FROM orders WHERE user_id =123456AND created_at >='2024-01-01'AND created_at <'2024-02-01'ORDERBY created_at DESC LIMIT 20;

-- 优化后查询
SELECT*FROM orders WHERE user_id =123456AND created_at >= '2024-01-01'AND created_at <'2024-02-01'ORDERBY created_at DESC LIMIT 20;

SHOW PROFILES;
-- 优化前: 2.5s -> 优化后: 0.02s, 性能提升125倍

◆ 案例二:分页查询深度翻页优化

场景描述:用户列表分页查询,当翻页到很后面时(如第10000页),查询变得非常慢。

原始SQL(慢):

-- 深度翻页查询
SELECT*FROM users
WHERE status =1
ORDERBY id
LIMIT 200000, 20;

-- 执行时间: 5.2s
-- rows examined: 200020

优化方案:

-- 方案1:使用子查询,先定位ID再JOIN
SELECT u.*FROM users u
INNERJOIN (
SELECT id FROM users
WHERE status =1
ORDERBY id
  LIMIT 200000, 20
) AS t ON u.id = t.id;

-- 执行时间: 0.8s

-- 方案2:记录上次查询的最大ID(推荐)
SELECT*FROM users
WHERE status =1AND id >1234567-- 上一页的最大ID
ORDERBY id
LIMIT 20;

-- 执行时间: 0.01s

-- 方案3:使用游标(适用于后台任务)
DECLARE done INTDEFAULTFALSE;
DECLARE cur CURSORFORSELECT*FROM users WHERE status =1ORDERBY id;
DECLARE CONTINUE HANDLER FORNOT FOUND SET done =TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO ...;
  IF done THEN
    LEAVE read_loop;
END IF;
-- 处理数据
END LOOP;
CLOSE cur;

四、最佳实践和注意事项

4.1 最佳实践

◆ 4.1.1 索引设计原则

  • • 原则一:选择性高的列优先建索引
    -- 计算列的选择性(唯一值比例)
    SELECT
    COUNT(DISTINCT username) /COUNT(*) AS username_selectivity,
    COUNT(DISTINCT status) /COUNT(*) AS status_selectivity,
    COUNT(DISTINCT age) /COUNT(*) AS age_selectivity
    FROM users;

    -- username: 0.98 (高选择性,适合索引)
    -- status: 0.03 (低选择性,不适合单独索引)
    -- age: 0.05 (低选择性)
  • • 原则二:联合索引顺序: 等值 > 范围 > 排序
    -- 查询: WHERE status = 1 AND created_at > xxx ORDER BY id
    -- 推荐索引: INDEX idx_opt (status, created_at, id)

    -- 查询: WHERE age > 25 AND status = 1 ORDER BY created_at
    -- 推荐索引: INDEX idx_opt (status, age, created_at)  -- status等值在前
  • • 原则三:避免冗余索引
    -- ❌ 冗余索引
    INDEX idx_a (a)
    INDEX idx_ab (a, b)  -- idx_a是冗余的

    -- ✅ 优化后
    INDEX idx_ab (a, b)  -- 删除idx_a

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

◆ 4.1.2 SQL编写规范

  • • **规范一:SELECT指定列名,避免SELECT ***
    -- ❌ 不推荐
    SELECT*FROM users WHERE id =1;

    -- ✅ 推荐(便于使用覆盖索引)
    SELECT id, username, email FROM users WHERE id =1;
  • • 规范二:避免在WHERE子句中对字段进行计算
    -- ❌ 不推荐
    WHEREYEAR(created_at) =2024
    WHERE amount *0.8>100

    -- ✅ 推荐
    WHERE created_at >='2024-01-01'AND created_at <'2025-01-01'
    WHERE amount >125-- 100 / 0.8
  • • 规范三:尽量使用UNION ALL代替UNION
    -- ❌ UNION会去重,性能较差
    SELECT id FROM orders WHERE status =1
    UNION
    SELECT id FROM orders WHERE status =2;

    -- ✅ 如果确定无重复,使用UNION ALL
    SELECT id FROM orders WHERE status =1
    UNIONALL
    SELECT id FROM orders WHERE status =2;

◆ 4.1.3 定期维护

  • • 维护任务一:更新统计信息
    -- 手动更新统计信息
    ANALYZE TABLE users;

    -- 批量更新所有表
    SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
    FROM information_schema.tables
    WHERE table_schema ='your_database';
  • • 维护任务二:清理碎片
    -- 查看表碎片
    SELECT
      table_name,
      ROUND((data_length + index_length) /1024/1024, 2) AS total_mb,
      ROUND(data_free /1024/1024, 2) AS data_free_mb,
      ROUND(data_free / (data_length + index_length) *100, 2) AS fragment_pct
    FROM information_schema.tables
    WHERE table_schema ='your_database'
    AND data_free >0
    ORDERBY fragment_pct DESC;

    -- 优化表(重建索引)
    OPTIMIZE TABLE users;
  • • 维护任务三:监控慢查询趋势
    -- 使用Prometheus监控慢查询数
    SELECT
      VARIABLE_VALUE AS slow_queries
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME ='Slow_queries';

4.2 注意事项

◆ 4.2.1 索引使用误区

⚠️ 警告:索引不是越多越好,过多索引会严重影响写入性能和占用存储空间

  • • ❗ 误区一:为每个查询条件都创建索引
    • • 索引维护开销:INSERT/UPDATE/DELETE都需要更新索引
    • • 建议:一张表的索引数量控制在5个以内
  • • ❗ 误区二:索引列顺序无所谓
    • • 联合索引的列顺序直接影响索引能否被使用
    • • 建议:根据查询频率和过滤性排序
  • • ❗ 误区三:数据量小不需要索引
    • • 虽然小表全表扫描很快,但养成良好习惯很重要
    • • 随着数据增长,提前规划索引可避免后期重构

◆ 4.2.2 常见错误

错误现象
原因分析
解决方案
索引创建后仍然不使用
统计信息过期
执行ANALYZE TABLE更新统计
FORCE INDEX后查询更慢
强制索引不是最优选择
移除FORCE INDEX,优化查询条件
覆盖索引未生效
SELECT包含了索引外的列
调整SELECT列或扩展索引列
联合索引只使用第一列
后续列是范围查询
调整索引列顺序

◆ 4.2.3 性能测试注意事项

  • • 注意事项一:测试环境要接近生产环境
    • • 数据量:至少100万行测试数据
    • • 数据分布:模拟真实的数据分布和热点数据
    • • 硬件配置:磁盘IO、内存、CPU尽量一致
  • • 注意事项二:清理缓存影响
    -- 清理查询缓存(MySQL 5.7及以下)
    RESET QUERY CACHE;

    -- 清理InnoDB缓冲池(重启MySQL或刷新缓冲池)
    SETGLOBAL innodb_buffer_pool_dump_at_shutdown =1;
    SETGLOBAL innodb_buffer_pool_load_at_startup =1;
  • • 注意事项三:使用性能分析工具
    -- 启用profiling
    SET profiling =1;
    -- 执行查询
    SELECT*FROM users WHERE username ='test';
    -- 查看详细耗时
    SHOW PROFILE FOR QUERY 1;

五、故障排查和监控

5.1 故障排查

◆ 5.1.1 慢查询诊断流程

#!/bin/bash
# 慢查询诊断流程脚本

# 1. 确认慢查询是否开启
mysql -e "SHOW VARIABLES LIKE '%slow_query%';"

# 2. 查看当前正在执行的慢查询
mysql -e "SELECT * FROM information_schema.processlist WHERE time > 5 ORDER BY time DESC;"

# 3. 分析EXPLAIN执行计划
mysql -e "EXPLAIN SELECT * FROM users WHERE username = 'test'\G"

# 4. 查看表索引情况
mysql -e "SHOW INDEX FROM users;"

# 5. 检查表统计信息
mysql -e "SHOW TABLE STATUS LIKE 'users'\G"

◆ 5.1.2 常见问题排查

问题一:为什么EXPLAIN显示使用了索引,但查询仍然很慢?

-- 可能原因1:回表次数过多
EXPLAIN SELECT*FROM users WHERE age =25;
-- type: ref, rows: 50000, Extra: Using where
-- 虽然使用了索引,但需要回表50000次

-- 解决方案:使用覆盖索引
CREATE INDEX idx_age_cover ON users(age, id, username, email);
SELECT id, username, email FROM users WHERE age =25;
-- Extra: Using where; Using index (无需回表)

-- 可能原因2:索引选择性太低
SELECTCOUNT(DISTINCT age) /COUNT(*) FROM users;
-- 0.05, 选择性很低

-- 解决方案:增加联合索引提高选择性
CREATE INDEX idx_age_status ON users(age, status);
SELECT*FROM users WHERE age =25AND status =1;

问题二:同样的SQL,在测试环境走索引,在生产环境不走索引

-- 原因:数据分布不同,导致优化器选择不同

-- 诊断步骤1:对比统计信息
SHOW INDEX FROM users;  -- 查看Cardinality

-- 诊断步骤2:更新生产环境统计信息
ANALYZE TABLE users;

-- 诊断步骤3:查看优化器成本估算
EXPLAIN FORMAT =JSON SELECT*FROM users WHERE status =1\G

-- 临时解决:强制使用索引(不推荐长期使用)
SELECT*FROM users FORCE INDEX(idx_status) WHERE status =1;

问题三:加了索引后写入变慢

-- 诊断:查看索引数量和大小
SELECT
  table_name,
COUNT(*) AS index_count,
  ROUND(SUM(stat_value) * @@innodb_page_size/1024/1024, 2) AS index_size_mb
FROM mysql.innodb_index_stats
WHERE database_name ='your_database'
GROUPBY table_name;

-- 解决方案1:合并冗余索引
-- 删除使用频率低的索引
ALTER TABLE users DROP INDEX idx_rarely_used;

-- 解决方案2:延迟索引创建(插入时无索引,批量插入后再创建)
ALTER TABLE users DROP INDEX idx_created_at;
-- 批量插入数据...
ALTER TABLE users ADD INDEX idx_created_at (created_at);

◆ 5.1.3 实时监控慢查询

-- 创建慢查询监控视图
CREATEOR REPLACE VIEW v_slow_queries AS
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT /1000000000, 2) AS avg_time_ms,
  ROUND(MAX_TIMER_WAIT /1000000000, 2) AS max_time_ms,
  ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME ='your_database'
AND AVG_TIMER_WAIT >1000000000-- 超过1秒
ORDERBY AVG_TIMER_WAIT DESC
LIMIT 20;

-- 查询慢查询
SELECT*FROM v_slow_queries;

5.2 性能监控

◆ 5.2.1 关键指标监控

-- 1. 查询吞吐量
SHOWGLOBAL STATUS LIKE'Questions';
SHOWGLOBAL STATUS LIKE'Queries';

-- 2. 慢查询数量
SHOWGLOBAL STATUS LIKE'Slow_queries';

-- 3. 索引使用率
SELECT
  ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Handler_read_rnd_next') /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Handler_read_key') *100, 2) AS index_usage_pct;

-- 4. 表锁等待
SHOWGLOBAL STATUS LIKE'Table_locks_waited';

-- 5. InnoDB行锁等待
SHOWGLOBAL STATUS LIKE'Innodb_row_lock%';

◆ 5.2.2 Prometheus监控配置

# prometheus.yml - MySQL exporter配置

scrape_configs:
-job_name:'mysql'
static_configs:
-targets: ['localhost:9104']
relabel_configs:
-source_labels: [__address__]
target_label:instance

Grafana告警规则:

# mysql_alerts.yml

groups:
-name:mysql_performance
interval:30s
rules:

# 慢查询告警
-alert:HighSlowQueries
expr:rate(mysql_global_status_slow_queries[5m])>10
for:5m
labels:
severity:warning
annotations:
summary:"MySQL慢查询过多"
description:"实例 {{ $labels.instance }} 慢查询速率: {{ $value }}/s"

# 索引未命中率高
-alert:HighTableScanRate
expr:rate(mysql_global_status_handler_read_rnd_next[5m])/rate(mysql_global_status_handler_read_key[5m])>100
for:10m
labels:
severity:warning
annotations:
summary:"全表扫描比例过高"
description:"实例 {{ $labels.instance }} 可能存在索引失效"

◆ 5.2.3 监控指标说明

指标名称
正常范围
告警阈值
说明
慢查询数(Slow_queries)
< 总查询数的1%
> 5%
持续高比例需要优化SQL
QPS(Queries Per Second)
根据业务和硬件
突然下降50%
可能存在慢查询阻塞
索引命中率
> 95%
< 90%
低于90%需要检查索引设计
InnoDB行锁等待
< 10次/秒
> 100次/秒
可能存在锁冲突

5.3 性能测试

◆ 5.3.1 基准测试脚本

#!/bin/bash
# MySQL性能基准测试:benchmark_test.sh

# 使用sysbench进行压力测试
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=testdb \
  --tables=10 \
  --table-size=1000000 \
  prepare

# 运行测试
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=testdb \
  --tables=10 \
  --table-size=1000000 \
  --threads=16 \
  --time=300 \
  --report-interval=10 \
  run

# 清理
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=testdb \
  --tables=10 \
  cleanup

◆ 5.3.2 SQL性能对比测试

-- 对比测试框架
DELIMITER $$

CREATEPROCEDURE compare_query_performance(
IN sql_original TEXT,
IN sql_optimized TEXT,
IN iterations INT
)
BEGIN
DECLARE i INTDEFAULT0;
DECLARE start_time BIGINT;
DECLARE end_time BIGINT;
DECLARE original_time BIGINTDEFAULT0;
DECLARE optimized_time BIGINTDEFAULT0;

-- 清理缓存
SET@dummy= (SELECT SLEEP(0.1));

-- 测试原始SQL
  WHILE i < iterations DO
SET start_time = UNIX_TIMESTAMP(NOW(6));
SET@sql= sql_original;
PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
SET end_time = UNIX_TIMESTAMP(NOW(6));
SET original_time = original_time + (end_time - start_time);
SET i = i +1;
END WHILE;

SET i =0;

-- 测试优化后SQL
  WHILE i < iterations DO
SET start_time = UNIX_TIMESTAMP(NOW(6));
SET@sql= sql_optimized;
PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
SET end_time = UNIX_TIMESTAMP(NOW(6));
SET optimized_time = optimized_time + (end_time - start_time);
SET i = i +1;
END WHILE;

-- 输出结果
SELECT
    ROUND(original_time / iterations, 4) AS avg_original_time_sec,
    ROUND(optimized_time / iterations, 4) AS avg_optimized_time_sec,
    ROUND((original_time - optimized_time) / original_time *100, 2) AS improvement_pct;
END$$

DELIMITER ;

-- 使用示例
CALL compare_query_performance(
'SELECT * FROM users WHERE username LIKE "%test%"',
'SELECT * FROM users WHERE username LIKE "test%"',
100
);

六、总结

6.1 技术要点回顾

  • • ✅ 要点一:索引失效的本质是MySQL优化器无法利用索引结构进行快速定位,常见原因包括函数计算、类型转换、不合理的查询条件等
  • • ✅ 要点二:联合索引的最左前缀原则和索引列顺序设计,直接决定了索引的使用效率,需要根据查询模式合理设计
  • • ✅ 要点三:MySQL优化器是基于成本的,数据分布、统计信息、硬件配置都会影响执行计划,需要定期维护统计信息
  • • ✅ 要点四:索引不是万能的,需要在查询性能和写入性能之间权衡,避免过度索引

6.2 进阶学习方向

  1. 1. MySQL内核与优化器原理
  • • 学习资源:《MySQL技术内幕:InnoDB存储引擎》《高性能MySQL》
  • • 实践建议:阅读MySQL源码,深入理解B+树索引结构和优化器cost模型
  • 2. 分库分表与读写分离
    • • 学习资源:ShardingSphere、Vitess、ProxySQL官方文档
    • • 实践建议:在单表优化到极限后,考虑水平拆分和垂直拆分方案
  • 3. NewSQL数据库
    • • 学习资源:TiDB、CockroachDB、YugabyteDB
    • • 实践建议:了解分布式数据库的索引设计差异和查询优化策略

    6.3 参考资料

    • • MySQL官方文档 - Optimization - MySQL优化指南
    • • 《高性能MySQL》 - MySQL性能优化经典书籍
    • • MySQL索引背后的数据结构及算法原理 - 深入理解B+树
    • • Percona Blog - MySQL性能优化博客
    • • pt-query-digest - 慢查询分析工具

    附录

    A. 索引优化决策树

    查询慢?
    ├─ 是否走索引?
    │  ├─ 否
    │  │  ├─ WHERE条件有索引? → 否 → 创建索引
    │  │  ├─ 使用函数/计算? → 是 → 改写SQL或创建函数索引
    │  │  ├─ 类型转换? → 是 → 统一数据类型
    │  │  ├─ LIKE '%xx%'? → 是 → 使用全文索引或搜索引擎
    │  │  └─ 数据分布问题? → 是 → 增加过滤条件或分区
    │  └─ 是
    │     ├─ 回表次数多? → 创建覆盖索引
    │     ├─ Using filesort? → 调整索引列顺序,包含排序列
    │     ├─ 扫描行数多? → 提高索引选择性
    │     └─ JOIN慢? → 确保关联列都有索引
    └─ 否 → 检查业务逻辑或硬件资源

    B. EXPLAIN输出字段详解

    字段
    说明
    id
    SELECT查询的序列号
    select_type
    查询类型:SIMPLE、PRIMARY、UNION、SUBQUERY等
    table
    输出行所引用的表
    type
    连接类型(性能从差到好):ALL < index < range < ref < eq_ref < const < system
    possible_keys
    可能使用的索引
    key
    实际使用的索引
    key_len
    使用的索引长度(字节),越短越好
    ref
    与索引比较的列
    rows
    估算要检查的行数
    filtered
    按条件过滤的行百分比
    Extra
    额外信息:Using index(覆盖索引)、Using where、Using filesort(需优化)、Using temporary(需优化)

    C. 常用SQL优化命令速查

    -- 查看执行计划
    EXPLAIN SELECT*FROM users WHERE username ='test';
    EXPLAIN FORMAT=JSON SELECT*FROM users WHERE username ='test'\G

    -- 查看索引使用情况
    SHOW INDEX FROM users;
    SHOWTABLE STATUS LIKE'users'\G

    -- 更新统计信息
    ANALYZE TABLE users;

    -- 查看表结构
    SHOWCREATE TABLE users\G

    -- 查看当前正在执行的查询
    SHOWFULL PROCESSLIST;

    -- 杀死慢查询
    KILL QUERY <process_id>;

    -- 查看慢查询配置
    SHOW VARIABLES LIKE'%slow_query%';

    -- 查看索引统计信息
    SELECT*FROM mysql.innodb_index_stats WHERE database_name ='your_db'AND table_name ='users';

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

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

    -- 启用profiling
    SET profiling =1;
    SHOW PROFILES;
    SHOW PROFILE FOR QUERY 1;

    文末福利


    网络监控是保障网络系统和数据安全的重要手段,能够帮助运维人员及时发现并应对各种问题,及时发现并解决,从而确保网络的顺畅运行。

    谢谢一路支持,给大家分享6款开源免费的网络监控工具,并准备了对应的资料文档,建议运维工程师收藏(文末一键领取)。

    图片
    备注:【监控合集】

    图片

    100%免费领取


    一、zabbix

    图片
    图片

    二、Prometheus


    图片

    内容较多,6款常用网络监控工具(zabbix、Prometheus、Cacti、Grafana、OpenNMS、Nagios)不再一一介绍, 需要的朋友扫码备注【监控合集】,即可100%免费领取。

    图片

     以上所有资料获取请扫码

    备注:【监控合集】

    图片

    100%免费领取

    (后台不再回复,扫码一键领取)


    Python社区是高质量的Python/Django开发社区
    本文地址:http://www.python88.com/topic/189738