Mysqls

引言 本文通过5个真实案例，综合运用索引优化知识，展示完整的优化过程。 案例1：订单列表查询优化 1.1 问题 -- 慢SQL SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; -- 执行时间：5秒 1.2 分析 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_id rows: 50000 Extra: Using filesort ← 文件排序 问题： 扫描5万行 需要额外排序（filesort） 1.3 优化 -- 创建联合索引 CREATE INDEX idx_user_created ON orders(user_id, created_at); -- 验证 EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20; 结果： type: ref key: idx_user_created rows: 20 Extra: Using index condition ← 无filesort 1.4 效果 执行时间：5秒 → 0.01秒 性能提升：500倍 扫描行数：50000 → 20 案例2：分页查询深分页优化 2.1 问题 -- 慢SQL（翻到第5000页） SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 执行时间：8秒 2.2 分析 EXPLAIN SELECT * FROM products ORDER BY id LIMIT 100000, 20; 问题： ...

一、什么是慢查询日志 1.1 定义 记录执行时间超过阈值的SQL语句。 1.2 作用 定位慢SQL 分析性能瓶颈 优化数据库性能 二、配置慢查询日志 2.1 查看当前配置 -- 是否启用 SHOW VARIABLES LIKE 'slow_query_log'; -- OFF（未启用） -- 慢查询阈值 SHOW VARIABLES LIKE 'long_query_time'; -- 10（默认10秒） -- 日志文件路径 SHOW VARIABLES LIKE 'slow_query_log_file'; 2.2 启用慢查询日志 -- 方式1：临时启用（重启失效） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 2秒 -- 方式2：配置文件永久启用 -- my.cnf [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = 1 -- 记录未使用索引的查询 2.3 重启生效 systemctl restart mysqld 三、慢查询日志格式 3.1 日志示例 # Time: 2024-11-21T22:30:00.123456Z # User@Host: root[root] @ localhost [] # Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000 SET timestamp=1700601000; SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at; 3.2 关键字段 Query_time：查询执行时间（秒） Lock_time：锁等待时间 Rows_sent：返回行数 Rows_examined：扫描行数 四、分析慢查询日志 4.1 查看慢查询日志 tail -f /var/log/mysql/slow.log 4.2 mysqldumpslow工具 # 查看出现次数最多的10条慢SQL mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 查看执行时间最长的10条慢SQL mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 查看返回记录最多的10条慢SQL mysqldumpslow -s r -t 10 /var/log/mysql/slow.log # 查看锁等待时间最长的10条慢SQL mysqldumpslow -s l -t 10 /var/log/mysql/slow.log 参数说明： ...

一、ref - 索引引用 1.1 含义 显示索引查找使用的列或常量。 -- 常量查询 EXPLAIN SELECT * FROM users WHERE id = 10; -- ref: const -- JOIN查询 EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- ref: database.o.user_id（orders表的user_id列） 二、rows - 扫描行数 2.1 预估行数 EXPLAIN SELECT * FROM users WHERE name = '张三'; -- rows: 100（预估扫描100行） 注意：是估算值，不是精确值。 2.2 优化目标 rows越小越好 - ✅ rows < 100：良好 - ⚠️ rows < 10000：可接受 - ❌ rows > 100000：需优化 三、filtered - 过滤百分比 3.1 含义 经过WHERE过滤后，剩余记录的百分比。 EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 20; -- rows: 1000 -- filtered: 10% -- 最终：1000 * 10% = 100行 3.2 计算 实际返回行数 ≈ rows * (filtered / 100) 四、Extra - 额外信息（重要） 4.1 Using index（覆盖索引） EXPLAIN SELECT id, name FROM users WHERE name = '张三'; -- Extra: Using index（覆盖索引，最优） 含义：只需扫描索引，无需回表。 ...

一、EXPLAIN基础 1.1 什么是EXPLAIN EXPLAIN SELECT * FROM users WHERE id = 10; 作用： 查看SQL执行计划 不实际执行查询 分析索引使用情况 优化查询性能 1.2 基本用法 -- 方式1：EXPLAIN EXPLAIN SELECT ...; -- 方式2：DESCRIBE（同义词） DESCRIBE SELECT ...; -- 方式3：查看实际执行（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT ...; 二、id - 查询标识 2.1 含义 查询的执行顺序标识。 EXPLAIN SELECT * FROM users WHERE id = 10; 结果： id: 1 ← 简单查询 2.2 子查询 EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); 结果： id | table ----+-------- 1 | users 2 | orders ← 先执行id=2（子查询） 规则： id大的先执行 id相同，从上往下执行 三、select_type - 查询类型 3.1 SIMPLE 简单查询，无子查询或UNION。 ...

一、什么是索引下推（ICP） 1.1 问题场景 CREATE INDEX idx_a_b ON table(a, b); SELECT * FROM table WHERE a = 1 AND b LIKE '%abc%'; 传统执行（无ICP）： 使用索引找到a=1的所有记录 回表获取完整数据 在Server层过滤b LIKE ‘%abc%’ 问题：大量无效回表。 1.2 ICP优化 ICP执行（MySQL 5.6+）： 使用索引找到a=1的记录 在索引中直接过滤b LIKE ‘%abc%’ 只回表符合条件的记录 优势：减少回表次数。 二、ICP原理 2.1 传统流程 vs ICP流程 传统： 索引层：找到a=1的10条记录 ↓ 回表10次 存储引擎：获取10条完整数据 ↓ Server层：过滤b，最终2条符合 ICP： 索引层：找到a=1的10条记录 ↓ 在索引中过滤b 索引层：10条中2条符合b条件 ↓ 只回表2次 存储引擎：获取2条完整数据 2.2 关键 下推：将Server层的过滤条件下推到存储引擎层。 三、ICP的适用条件 3.1 启用条件 -- 查看ICP状态 SHOW VARIABLES LIKE 'optimizer_switch'; -- index_condition_pushdown=on -- 启用ICP SET optimizer_switch='index_condition_pushdown=on'; 3.2 使用场景 -- ✅ 可以使用ICP CREATE INDEX idx_a_b_c ON table(a, b, c); WHERE a = 1 AND b > 10 AND c = 3; -- a精确匹配 → 使用索引 -- b范围查询 → 使用索引 -- c过滤条件 → ICP下推到索引层 3.3 不适用场景 -- ❌ 主键索引（聚簇索引） -- 已经包含完整数据，无需下推 -- ❌ 覆盖索引 -- 不需要回表，无需下推 -- ❌ 全表扫描 -- 没有使用索引 四、EXPLAIN中的ICP 4.1 识别标志 EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status LIKE '%pending%'; 结果： ...

一、什么是覆盖索引 1.1 回表查询 -- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(100) ); CREATE INDEX idx_name ON users(name); -- 查询 SELECT * FROM users WHERE name = '张三'; 查询过程： 在idx_name索引找到name=‘张三’，获取id=10 回表：到主键索引查找id=10的完整数据 返回结果 IO次数：2次（索引1次 + 回表1次） 1.2 覆盖索引 -- 查询 SELECT id, name FROM users WHERE name = '张三'; 查询过程： 在idx_name索引找到name=‘张三’ 索引中已包含id和name 直接返回，无需回表 IO次数：1次（只查索引） 覆盖索引：查询的列都在索引中，无需回表。 二、覆盖索引的优势 2.1 减少IO -- 无覆盖索引 SELECT * FROM users WHERE name = '张三'; -- IO：索引 + 回表 = 2次 -- 覆盖索引 SELECT id, name FROM users WHERE name = '张三'; -- IO：索引 = 1次 2.2 提升性能 -- 测试 -- 100万数据，查询10000次 -- SELECT *（需要回表） -- 耗时：5秒 -- SELECT id, name（覆盖索引） -- 耗时：0.5秒 -- 性能提升：10倍 三、如何实现覆盖索引 3.1 主键索引天然覆盖 -- 主键索引是聚簇索引，包含完整数据 SELECT * FROM users WHERE id = 10; -- 覆盖索引 3.2 设计联合索引 -- 查询 SELECT id, name, age FROM users WHERE name = '张三'; -- 索引设计 CREATE INDEX idx_name_age ON users(name, age); -- 索引包含：name, age, id（主键自动包含） -- 覆盖查询所需列 3.3 添加冗余列 -- 查询 SELECT user_id, order_no, status FROM orders WHERE user_id = 123; -- 索引设计 CREATE INDEX idx_user_no_status ON orders(user_id, order_no, status); -- order_no和status可能不在WHERE中，但在SELECT中 -- 添加到索引实现覆盖 四、EXPLAIN中的覆盖索引 4.1 Using index EXPLAIN SELECT id, name FROM users WHERE name = '张三'; 结果： ...

一、联合索引基础 1.1 什么是联合索引 -- 单列索引 CREATE INDEX idx_a ON table(a); CREATE INDEX idx_b ON table(b); -- 联合索引（多列组合） CREATE INDEX idx_a_b ON table(a, b); CREATE INDEX idx_a_b_c ON table(a, b, c); 1.2 存储结构 联合索引 idx_a_b_c(a, b, c)的B+树： [(1,1,1), (1,1,2), (1,2,1), (2,1,1)] ↓ 先按a排序 [(1,1,1), (1,1,2), (1,2,1)] ← a相同 ↓ 再按b排序 [(1,1,1), (1,1,2)] ← a、b相同 ↓ 最后按c排序 二、最左前缀原则 2.1 核心规则 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ✅ 可以使用索引 WHERE a = 1; -- 使用(a) WHERE a = 1 AND b = 2; -- 使用(a,b) WHERE a = 1 AND b = 2 AND c = 3; -- 使用(a,b,c) WHERE a = 1 AND c = 3; -- 使用(a)，c失效 -- ❌ 不能使用索引 WHERE b = 2; -- 缺少a WHERE c = 3; -- 缺少a WHERE b = 2 AND c = 3; -- 缺少a 2.2 原理 联合索引按从左到右的顺序排序： ...

引言 创建了索引不一定会使用。理解索引失效的场景，才能写出高效的SQL。 一、违反最左前缀原则 1.1 联合索引规则 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ✅ 使用索引 WHERE a = 1; WHERE a = 1 AND b = 2; WHERE a = 1 AND b = 2 AND c = 3; -- ❌ 不使用索引 WHERE b = 2; -- 缺少a WHERE c = 3; -- 缺少a和b WHERE b = 2 AND c = 3; -- 缺少a 1.2 跳跃列 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ⚠️ 部分使用索引 WHERE a = 1 AND c = 3; -- 只使用a，c失效 二、索引列上使用函数 2.1 函数导致失效 -- ❌ 失效：YEAR函数 SELECT * FROM orders WHERE YEAR(created_at) = 2024; -- ✅ 改进：使用范围查询 SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'; 2.2 常见函数失效案例 -- ❌ 失效 WHERE DATE(created_at) = '2024-11-21'; WHERE LEFT(name, 3) = 'abc'; WHERE UPPER(email) = 'USER@EXAMPLE.COM'; WHERE price * 0.8 > 100; -- ✅ 改进 WHERE created_at >= '2024-11-21' AND created_at < '2024-11-22'; WHERE name LIKE 'abc%'; WHERE email = 'user@example.com'; -- 应用层转小写 WHERE price > 125; -- 100 / 0.8 三、隐式类型转换 3.1 字符串与数字 -- 表结构 CREATE TABLE users ( id INT, phone VARCHAR(20) -- 字符串类型 ); CREATE INDEX idx_phone ON users(phone); -- ❌ 失效：数字查询字符串列 SELECT * FROM users WHERE phone = 13800138000; -- 数字 -- MySQL转换为：WHERE CAST(phone AS SIGNED) = 13800138000 -- 索引失效 -- ✅ 使用索引 SELECT * FROM users WHERE phone = '13800138000'; -- 字符串 3.2 反向情况 -- 表结构 CREATE TABLE users ( id INT, age INT -- 数字类型 ); CREATE INDEX idx_age ON users(age); -- ✅ 使用索引：字符串查询数字列 SELECT * FROM users WHERE age = '20'; -- MySQL转换为：WHERE age = CAST('20' AS SIGNED) -- 索引仍然有效 规则：字符串列查询时必须用字符串类型。 ...

一、何时创建索引 1.1 适合创建索引的场景 -- 1. WHERE条件列 SELECT * FROM orders WHERE user_id = 123; CREATE INDEX idx_user_id ON orders(user_id); -- 2. ORDER BY排序列 SELECT * FROM products ORDER BY price DESC; CREATE INDEX idx_price ON products(price); -- 3. GROUP BY分组列 SELECT category, COUNT(*) FROM products GROUP BY category; CREATE INDEX idx_category ON products(category); -- 4. JOIN连接列 SELECT * FROM orders o JOIN users u ON o.user_id = u.id; CREATE INDEX idx_user_id ON orders(user_id); -- 5. DISTINCT去重列 SELECT DISTINCT category FROM products; CREATE INDEX idx_category ON products(category); 1.2 不适合创建索引的场景 -- 1. 小表（< 1000行） -- 全表扫描更快 -- 2. 频繁更新的列 -- 维护索引代价高 -- 3. 区分度低的列（选择性 < 0.01） SELECT * FROM users WHERE gender = '男'; -- 50%数据 -- 不如全表扫描 -- 4. 不在WHERE/ORDER BY/JOIN中使用的列 -- 纯粹浪费空间 二、选择索引列的原则 2.1 选择性高的列 -- 计算列的选择性 SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table_name; -- 选择性 > 0.1：适合索引 -- 选择性 < 0.01：不适合索引 示例： ...

引言 MySQL支持多种索引类型，每种有不同的特点和适用场景。 一、主键索引（PRIMARY KEY） 1.1 特点 唯一且非空 聚簇索引：数据和索引存在一起 每个表只能有一个主键 1.2 创建 -- 方式1：建表时指定 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ); -- 方式2：后续添加 ALTER TABLE users ADD PRIMARY KEY (id); 1.3 聚簇索引（Clustered Index） InnoDB的主键索引是聚簇索引： 叶子节点存储完整的数据行 表数据按主键顺序存储 B+树结构： [10, 20] / | \ [1→row] [10→row] [20→row] ← 叶子节点存完整数据 优势： 主键查询快（一次IO获取完整数据） 范围查询快（数据有序） 劣势： 主键不能太长（影响所有二级索引） 插入乱序会导致页分裂 二、唯一索引（UNIQUE INDEX） 2.1 特点 值必须唯一 允许NULL（但只能有一个NULL） 可以有多个唯一索引 2.2 创建 -- 方式1：建表时 CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE ); -- 方式2：单独创建 CREATE UNIQUE INDEX idx_email ON users(email); -- 方式3：ALTER TABLE ALTER TABLE users ADD UNIQUE INDEX idx_email (email); 2.3 使用场景 -- 用户名、邮箱、手机号等唯一字段 CREATE UNIQUE INDEX idx_username ON users(username); CREATE UNIQUE INDEX idx_phone ON users(phone); 三、普通索引（NORMAL INDEX） 3.1 特点 最基本的索引 值可以重复 也称为二级索引（Secondary Index） 3.2 创建 -- 方式1：CREATE INDEX CREATE INDEX idx_name ON users(name); -- 方式2：ALTER TABLE ALTER TABLE users ADD INDEX idx_age (age); -- 方式3：建表时 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), INDEX idx_name (name) ); 3.3 二级索引（非聚簇索引） 存储结构： ...