MySQL索引踩坑合集从入门到精通

2025-11-13 17:50
文章标签 mysql 合集 索引 入门 精通 database

本文主要是介绍MySQL索引踩坑合集从入门到精通，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL索引踩坑合集从入门到精通》本文详细介绍了MySQL索引的使用,包括索引的类型、创建、使用、优化技巧及最佳实践,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友...

MySQL索引完整教程：从入门到入土（附实战踩坑指南）

“没有索引的查询就像在图书馆里不用目录，一本本翻书找资料——等你找到，项目都上线了 ”

一、索引是什么？为什么需要它？

1.1 什么是索引？

索引（Index）就像书籍的目录，可以帮助数据库快速定位到数据，而不需要全表扫描。

想象一下：

  • 没有索引：就像在一本1000页的字典里，从第一页开始逐页查找"MySQL"这个词
  • 有索引：直接翻到"M"开头的部分，瞬间找到

1.2 为什么需要索引？

让我们看一个真实的China编程场景：

-- 假设有一个用户表，有1000万条数据
SELECT * FROM users WHERE phone = '13800138000';

没有索引的情况：

  • 需要扫描全表：1000万行
  • 时间复杂度：O(n)
  • 执行时间编程：可能几秒甚至几十秒
  • 用户体验： “这页面卡死了吗？”

有索引的情况：

  • 通过B+树快速定位：几层查找
  • 时间复杂度：O(log n)
  • 执行时间：几毫秒
  • 用户体验： “秒开，真快！”

二、索引的类型

2.1 按数据结构分类

1. B+树索引（最常用）

B+树索引是MySQL的默认索引类型，适用于大部分场景。

特点：

  • 所有数据都存储在叶子节点
  • 叶子节点之间用指针连接（便于范围查询）
  • 树的高度低，查询效率高
         [50]
        /    \
    [25]      [75]
   /   \      /   \
[10][30]  [60][80]
2. Hash索引

特点：

  • 等值查询极快，O(1)时间复杂度
  • 不支持范围查询
  • 不支持排序
  • 仅Memory存储引擎支持

适用场景：

  • 等值查询频繁
  • 不需要范围查询
-- Memory引擎的Hash索引
CREATE TABLE user_hash (
    id INT PRIMARY KEY,
    username VARCHAR(50)
) ENGINE=MEMORY;
CREATE INDEX idx_username ON user_hash(username) USING HASH;
3. 全文索引（FULLTEXT）

特点：

  • 用于全文搜索
  • 支持中文分词（MySQL 5.7+）
  • 仅MyISAM和InnoDB支持
-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);
-- 使用全文索引搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);

2.2 按字段数量分类

1. 单列索引
-- 在单个列上创建索引
CREATE INDEX idx_username ON users(username);
2. 复合索引（联合索引）
-- 在多个列上创建索引
CREATE INDEX idx_name_phone ON users(name, phone);

⚠️ 踩坑点1：复合索引的顺序很重要！

-- 假设有索引 idx_name_phone(name, phone)
-- ✅ 可以使用索引
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND phone = '13800138000';
-- ❌ 无法使用索引（违背最左前缀原则）
SELECT * FROM users WHERE phone = '13800138000';

为什么会这样？ 就像查字典，先按拼音首字母，再按第二个字母。你直接查第二个字母，目录就帮不上忙了！

2.3 按唯一性分类

1. 普通索引

允许重复值，最常用。

CREATE INDEX idx_email ON users(email);
2. 唯一索引

不允许重复值，但允许NULL（可以有多个NULL）。

CREATE UNIQUE INDEX idx_phone ON users(phone);
3. 主键索引

特殊的唯一索引，不允许NULL，每个表只能有一个。

-- 创建表时自动创建
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 主键索引
    username VARCHAR(50)
);

三、索引的创建和使用

3.1 创建索引的几种方式

方式1：创建表时创建
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    INDEX idx_username(username),         javascript  -- 普通索引
    UNIQUE INDEX idx_email(email),          -- 唯一索引
    INDEX idx_name_phone(username, phone)   -- 复合索引
);
方式2：使用ALTER TABLE
ALTER TABLE users ADD INDEX idx_username(username);
ALTER TABLE users ADD UNIQUE INDEX idx_email(email);
方式3：使用CREATE INDEX
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);

3.2 删除索引

-- 方式1
DROP INDEX idx_username ON users;
-- 方式2
ALTER TABLE users DROP INDEX idx_username;

3.3 查看索引

-- 查看表的索引
SHOW INDEX FROM users;
-- 查看创建索引的SQL
SHOW CREATE TABLE users;

四、工作中常见的踩坑点（血泪教训）

踩坑点1：索引不是越多越好

错误示例：

-- 新手：每个字段都加索引，美其名曰"优化"
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    age INT,
    gender TINYINT,
    city VARCHAR(50),
    INDEX idx_username(username),
    INDEX idx_email(email),
    INDEX idx_phone(phone),
    INDEX idx_age(age),
    INDEX idx_gender(gender),
    INDEX idx_city(cityxoYgRQD)
    -- ... 还有10个字段，每个都加索引
);

问题：

  1. 占用存储空间：每个索引都需要额外存储
  2. 降低写性能：INSERT/UPDATE/DELETE需要维护所有索引
  3. 查询优化器可能选错索引：MySQL会纠结用哪个索引

正确做法：

  • 只为经常用于查询条件的字段创建索引
  • 不要为数据分布均匀的字段创建索引（如性别、状态）

踩坑点2：字符串索引长度设置不当

错误示例：

-- 为很长的文本字段创建完整索引
CREATE INDEX idx_content ON articles(content);  -- content是TEXT类型，可能几KB

问题：

  • 索引文件巨大
  • 查询效率低
  • 浪费存储空间

正确做法：使用前缀索引

-- 只对前100个字符创建索引
CREATE INDEX idx_content ON articles(content(100));
-- 如何确定前缀长度？
-- 计算不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(content, 50)) / COUNT(*) AS sel50,
    COUNT(DISTINCT LEFT(content, 100)) / COUNT(*) AS sel100
FROM articles;
-- 选择性越接近1越好，但也要考虑索引大小

踩坑点3：在WHERE子句中对索引列使用函数

错误示例：

-- 有索引 idx_created_at(created_at)
SELECT * FROM orders 
WHERE DATE(created_at) = '2024-01-01';  -- ❌ 无法使用索引

问题：

  • MySQL无法使用索引，因为需要对每行数据执行函数
  • 导致全表扫描

正确做法：

-- ✅ 使用范围查询
SELECT * FROM orders 
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at < '2024-01-02 00:00:00';
-- ✅ 或者在函数计算列上创建索引（MySQL 5.7+）
ALTER TABLE orders ADD INDEX idx_created_date((DATE(created_at)));
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

踩坑点4：LIKE查询使用不当

错误示例：

-- 有索引 idx_username(username)
SELECT * FROM users WHERE username LIKE '%admin%';  -- ❌ 无法使用索引
SELECT * FROM users WHERE username LIKE '%admin';   -- ❌ 无法使用索引

问题：

  • 前导通配符 % 导致无法使用索引
  • 全表扫描，性能极差

正确做法：

-- ✅ 只有后缀通配符可以使用索引
SELECT * FROM users WHERE username LIKE 'admin%';  -- ✅ 可以使用索引
-- 如果必须使用前导通配符，考虑：
-- 1. 使用全文索引
-- 2. 使用搜索引擎（Elasticsearch）
-- 3. 反序存储（如：将'admin'存储为'nimda'，然后查询'%nimda'变成'admin%'）

踩坑点5：OR条件导致索引失效

错误示例：

-- 有索引 idx_username(username) 和 idx_email(email)
SELECT * FROM users 
WHERE username = 'admin' OR email = 'admin@example.com';  -- ❌ 可能无法使用索引

问题：

  • MySQL可能无法同时使用两个索引
  • 优化器可能选择全表扫描

正确做法：

-- ✅ 使用UNION
SELECT * FROM users WHERE username = 'admin'
UNION
SELECT * FROM users WHERE email = 'admin@example.com';
-- ✅ 或者创建复合索引
CREATE INDEX idx_username_email ON users(username, email);

踩坑点6：NULL值处理不当

错误示例：

-- 有索引 idx_email(email)，但email字段允许NULL
SELECT * FROM users WHERE email IS NULL;  -- ⚠️ 可能无法使用索引
SELECT * FROM users WHERE email IS NOT NULL;  -- ⚠️ 可能无法使用索引

问题：

  • NULL值在索引中的处理比较复杂
  • 可能导致索引使用效率低下

正确做法：

-- ✅ 尽量避免NULL，使用默认值
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL DEFAULT '',  -- 使用空字符串而不是NULL
    INDEX idx_email(email)
);
-- ✅ 如果必须使用NULL，考虑覆盖索引
CREATE INDEX idx_email_id ON users(email, id);
SELECT id FROM users WHERE email IS NULL;  -- 可以使用覆盖索引

踩坑点7：隐式类型转换

错误示例：

-- phone字段是VARCHAR类型，有索引 idx_phoxoYgRQDne(phone)
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ 类型不匹配，无法使用索引

问题：

  • MySQL会进行隐式类型转换
  • 导致无法使用索引

正确做法：

-- ✅ 确保类型匹配
SELECT * FROM users WHERE phone = '13800138000';  -- ✅ 使用字符串

踩坑点8：ORDER BY和索引

错误示例：

-- 有索引 idx_username(username)，但没有包含age
SELECT * FROM users 
WHERE username = 'admin' 
ORDER BY age;  -- ❌ 需要额外的排序操作（filesort）

问题：

  • 如果ORDER BY的字段不在索引中，需要额外的排序
  • 如果数据量大，排序会很慢

正确做法：

-- ✅ 创建包含ORDER BY字段的索引
CREATE INDEX idx_username_age ON users(username, age);
SELECT * FROM users 
WHERE username = 'admin' 
ORDER BY age;  -- ✅ 可以使用索引，避免filesort

五、索引优化技巧

5.1 使用EXPLAIN分析查询

EXPLAIN是优化查询的神器！

EXPLAIN SELECT * FROM users WHERE username = 'admin';

关键字段：

  • type：访问类型
    • ALL：全表扫描（最差）
    • index：全索引扫描
    • range：范围扫描
    • ref：非唯一索引扫描
    • const：常量查询（最好）
  • key：使用的索引
  • rows：扫描的行数（越少越好）
  • Extra：额外信息
    • Using index：使用了覆盖索引（很好）✨
    • Using filesort：需要额外排序（不好）⚠️
    • Using temporary：需要临时表（很不好）

5.2 覆盖索引（Covering Index）

覆盖索引：索引包含了查询所需的所有字段

-- 普通索引
CREATE INDEX idx_username ON users(username);
-- 查询需要回表
SELECT id, username, email FROM users WHERE username = 'admin';
-- 1. 通过索引找到username = 'admin'的行
-- 2. 根据主键回表查询email（额外IO）
-- 覆盖索引
CREATE INDEX idx_username_email ON users(username, email);
-- 查询不需要回表
SELECT username, email FROM users WHERE username = 'admin';
-- 1. 通过索引找到username = 'admin'的行
-- 2. 索引中已经有email，直接返回（不需要回表）✨

优势：

  • 减少IO操作
  • 提高查询性能
  • 特别是在InnoDB中，可以减少随机IO

5.3 索引下推（Index Condition Pushdown，ICP）

MySQL 5.6+支持索引下推优化

-- 有索引 idx_name_phone(name, phone)
SELECT * FROM users WHERE name LIKE '张%' AND phone = '13800138000';
-- 没有ICP（MySQL 5.6之前）：
-- 1. 通过索引找到name LIKE '张%'的所有行
-- 2. 回表查询
-- 3. 过滤phone = '13800138000'
-- 有ICP（MySQL 5.6+）：
-- 1. 通过索引找到name LIKE '张%'的所有行
-- 2. 在索引中直接过滤phone = '13800138000'（索引下推）
-- 3. 只回表查询匹配的行
-- 减少了回表次数！✨

5.4 索引选择性（Cardinality）

索引选择性 = 不同值的数量 / 总行数

选择性越高，索引效果越好。

-- 查看索引选择性
SHOW INDEX FROM users;
-- 或者
SELECT 
    COUNT(DISTINCT username) / COUNT(*) AS username_sel,
    COUNT(DISTINCT gender) / COUNT(*) AS gender_sel
FROM users;
-- username_sel接近1，适合创建索引
-- gender_sel接近0.5（只有男/女），不适合创建索引

经验法则：

  • 选择性 > 0.1：适合创建索引
  • 选择性 < 0.1：不适合创建索引（如性别、状态等）

5.5 索引合并（Index Merge）

MySQL可以将多个索引合并使用

-- 有索引 idx_username(username) 和 idx_email(email)
SELECT * FROM users 
WHERE username = 'admin' OR email = 'admin@example.com';
-- MySQL可能使用索引合并：
-- 1. 使用idx_username查找username = 'admin'的行
-- 2. 使用idx_email查找email = 'admin@example.com'的行
-- 3. 合并结果

但要注意：

  • 索引合并的效率通常不如单个复合索引
  • 如果经常这样查询，考虑创建复合索引

六、索引设计最佳实践

6.1 索引设计原则

  • 最左前缀原则
    • 复合索引要遵循最左前缀原则
    • 将选择性高的字段放在前面
  • 避免冗余索引
-- ❌ 冗余：idx_username已经包含在idx_username_email中
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_username_email ON users(username, email);
-- ✅ 正确：只需要复合索引
CREATE INDEX idx_username_email ON users(username, email);
  • 考虑查询模式
  • 根据实际查询场景设计索引
  • 不要盲目创建索引
  • 定期分析和优化
-- 分析表，更新索引统计信息
ANALYZE TABLE users;
-- 查看未使用的索引（MySQL 5.7+）
SELECT * FROM sys.schema_unused_indexes;

6.2 索引命名规范

-- 推荐命名方式
CREATE INDEX idx_username ON users(username);              -- 单列索引
CREATE INDEX idx_username_email ON users(username, email); -- 复合索引
CREATE UNIQUE INDEX uk_email ON users(email);              -- 唯一索引
CREATE INDEX idx_created_at ON orders(created_at);         -- 时间索引

6.3 索引维护

-- 重建索引（InnoDB）
ALTER TABLE users DROP INDEX idx_username;
ALTER TABLE users ADD INDEX idx_username(username);
-- 或者使用OPTIMIZE TABLE
OPTIMIZE TABLE users;

总结

索引使用 checklist ✅

  • 为经常用于WHERE条件的字段创建索引
  • 为经常用于ORDER BY的字段创建索引
  • 遵循最左前缀原则设计复合索引
  • 使用EXPLAIN分析查询性能
  • 避免在索引列上使用函数
  • 避免前导通配符的LIKE查询
  • 定期分析和优化索引
  • 删除未使用的索引
  • 监控索引使用情况

还是那句话

索引是一把双刃剑：

  • 用得好：查询速度飞起，用户体验up up up
  • 用得不好：写性能下降，存储空间浪费，还可能选错索引

记住：

  1. 不要过度索引：索引不是越多越好
  2. 根据实际场景设计：不要盲目创建索引
  3. 定期监控和优化：索引需要持续维护
  4. 使用EXPLAIN分析：不要凭感觉优化

希望这篇文章能帮到你，避免在工作中踩坑。大家都踩过什么坑呢，欢迎留言讨论！

参考资料：

  • MySQL官方文档 - 索引
  • 高性能MySQL
  • MySQL索引背后的数据结构及算法原理

到此这篇关于MySQL索引踩坑合集从入门到精通的文章就介绍到这了,更多相关mysql索引内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！

这篇关于MySQL索引踩坑合集从入门到精通的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1156149。

相关文章

MySQL字符串转数值的方法全解析

MySQL字符串转数值的方法全解析

《MySQL字符串转数值的方法全解析》在MySQL开发中,字符串与数值的转换是高频操作,本文从隐式转换原理、显式转换方法、典型场景案例、风险防控四个维度系统梳理,助您精准掌握这一核心技能,需要的朋友可... 目录一、隐式转换：自动但需警惕的&ld编程quo;双刃剑”二、显式转换：三大核心方法详解三、典型场景
阅读更多...
MySQL中between and的基本用法、范围查询示例详解

MySQL中between and的基本用法、范围查询示例详解

《MySQL中betweenand的基本用法、范围查询示例详解》BETWEENAND操作符在MySQL中用于选择在两个值之间的数据,包括边界值,它支持数值和日期类型,示例展示了如何使用BETWEEN... 目录一、between and语法二、使用示例2.1、betwphpeen and数值查询2.2、be
阅读更多...
MySQL快速复制一张表的四种核心方法(包括表结构和数据)

MySQL快速复制一张表的四种核心方法(包括表结构和数据)

《MySQL快速复制一张表的四种核心方法(包括表结构和数据)》本文详细介绍了四种复制MySQL表（结构+数据）的方法,并对每种方法进行了对比分析,适用于不同场景和数据量的复制需求,特别是针对超大表（1... 目录一、mysql 复制表（结构+数据）的 4 种核心方法（面试结构化回答）方法 1：CREATE
阅读更多...
SQL Server中行转列方法详细讲解

SQL Server中行转列方法详细讲解

《SQLServer中行转列方法详细讲解》SQL行转列、列转行可以帮助我们更方便地处理数据,生成需要的报表和结果集,：本文主要介绍SQLServer中行转列方法的相关资料,需要的朋友可以参考下... 目录前言一、为什么需要行转列二、行转列的基本概念三、使用PIVOT运算符进行行转列1.创建示例数据表并插入数
阅读更多...
MySQL MHA集群详解(数据库高可用)

MySQL MHA集群详解(数据库高可用)

《MySQLMHA集群详解(数据库高可用)》MHA（MasterHighAvailability）是开源MySQL高可用管理工具,用于自动故障检测与转移,支持异步或半同步复制的MySQL主从架构,本... 目录mysql 高可用方案：MHA 详解与实战1. MHA 简介2. MHA 的组件组成（1）MHA
阅读更多...
SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

《SQL注入攻击(SQLInjection)原理、利用方式与防御策略深度解析》本文将从SQL注入的基本原理、攻击方式、常见利用手法,到企业级防御方案进行全面讲解,以帮助开发者和安全人员更系统地理解... 目录一、前言二、SQL 注入攻击的基本概念三、SQL 注入常见类型分析1. 基于错误回显的注入（Erro
阅读更多...
MySQL基本表查询操作汇总之单表查询+多表操作大全

MySQL基本表查询操作汇总之单表查询+多表操作大全

《MySQL基本表查询操作汇总之单表查询+多表操作大全》本文全面介绍了MySQL单表查询与多表操作的关键技术,包括基本语法、高级查询、表别名使用、多表连接及子查询等,并提供了丰富的实例,感兴趣的朋友跟... 目录一、单表查询整合（一）通用模版展示（二）举例说明（三）注意事项（四）Mapper简单举例简单查询
阅读更多...
SpringCloud Stream 快速入门实例教程

SpringCloud Stream 快速入门实例教程

《SpringCloudStream快速入门实例教程》本文介绍了SpringCloudStream（SCS）组件在分布式系统中的作用,以及如何集成到SpringBoot项目中,通过SCS,可... 目录1.SCS 组件的出现的背景和作用2.SCS 集成srping Boot项目3.Yml 配置4.Sprin
阅读更多...
MySQL中的DELETE删除数据及注意事项

MySQL中的DELETE删除数据及注意事项

《MySQL中的DELETE删除数据及注意事项》MySQL的DELETE语句是数据库操作中不可或缺的一部分,通过合理使用索引、批量删除、避免全表删除、使用TRUNCATE、使用ORDERBY和LIMI... 目录1. 基本语法单表删除2. 高级用法使用子查询删除删除多表3. 性能优化策略使用索引批量删除避免
阅读更多...
MySQL 数据库进阶之SQL 数据操作与子查询操作大全

MySQL 数据库进阶之SQL 数据操作与子查询操作大全

《MySQL数据库进阶之SQL数据操作与子查询操作大全》本文详细介绍了SQL中的子查询、数据添加（INSERT）、数据修改（UPDATE）和数据删除（DELETE、TRUNCATE、DROP）操作... 目录一、子查询：嵌套在查询中的查询1.1 子查询的基本语法1.2 子查询的实战示例二、数据添加：INSE
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2