MySQL索引 - 当发网

什么是索引？

索引是数据库中用于快速查找数据的数据结构。类似于书籍的目录，索引可以帮助数据库引擎快速定位到所需的数据，而无需扫描整个表。

索引的工作原理

索引通过创建指向表中数据位置的指针来工作。当执行查询时，数据库首先检查索引，找到所需数据的位置，然后直接访问这些位置，而不是扫描整个表。

索引的数据结构

MySQL主要使用B+树作为索引的数据结构，这种结构适合磁盘存储，能够高效支持范围查询和等值查询。InnoDB存储引擎还支持自适应哈希索引，用于优化某些频繁访问的索引页。

索引的优点

大大加快数据的检索速度
通过创建唯一索引保证数据唯一性
加速表与表之间的连接
减少分组和排序的时间
提高数据库整体性能
使用覆盖索引可以避免回表，进一步提升查询性能

索引的缺点

创建和维护索引需要时间
索引需要占用物理空间
对表进行增删改时，索引也需要维护，降低数据维护速度
过多的索引会影响写入性能

索引类型

普通索引

最基本的索引，没有任何限制。允许在定义索引的列中插入重复值和空值。

                        SQL示例
                    

                        -- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建表时定义普通索引
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    INDEX idx_username (username)
);
                    

唯一索引

索引列的值必须唯一，但允许有空值。唯一索引可以确保列中不出现重复值。

                        SQL示例
                    

                        -- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建表时定义唯一索引
CREATE TABLE users (
    id INT,
    email VARCHAR(100),
    UNIQUE INDEX idx_email (email)
);
                    

主键索引

特殊的唯一索引，不允许有空值。每个表只能有一个主键，主键索引是表的主键自动创建的索引。

                        SQL示例
                    

                        -- 添加主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 创建表时定义主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);
                    

复合索引

基于多个列的索引。复合索引遵循最左前缀原则，查询时只有使用索引的最左前缀才会使用索引。

                        SQL示例
                    

                        -- 创建复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 复合索引使用场景
-- 以下查询会使用索引：
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;

-- 以下查询不会使用索引（不满足最左前缀原则）：
SELECT * FROM users WHERE first_name = 'John';
SELECT * FROM users WHERE age = 30;
                    

复合索引与排序

复合索引不仅能用于过滤，还能用于排序，避免额外的文件排序（filesort）。但排序顺序必须与索引列顺序一致。

                        SQL示例
                    

                        -- 索引 idx_name_age (last_name, first_name, age)
-- 可以利用索引排序的查询
SELECT * FROM users ORDER BY last_name, first_name;
SELECT * FROM users WHERE last_name = 'Smith' ORDER BY first_name;

-- 无法利用索引排序的查询（排序顺序与索引不一致）
SELECT * FROM users ORDER BY first_name, last_name;
SELECT * FROM users ORDER BY last_name DESC, first_name ASC;
                    

全文索引

用于全文搜索，仅适用于MyISAM和InnoDB存储引擎。全文索引可以对文本内容进行高效的搜索。

                        SQL示例
                    

                        -- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL database');

-- 布尔模式全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
                    

空间索引

用于地理空间数据类型，如POINT、LINESTRING、POLYGON等。

                        SQL示例
                    

                        -- 创建空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL,
    SPATIAL INDEX idx_position (position)
);
                    

高级索引特性

覆盖索引

当查询所需的数据全部包含在索引中时，MySQL可以直接从索引中返回结果，无需回表访问实际数据行，这称为覆盖索引。覆盖索引可以极大提升查询性能。

                        SQL示例
                    
                        -- 创建覆盖索引
CREATE INDEX idx_cover ON users(username, email);

-- 此查询只需要访问索引，不需要回表
SELECT username, email FROM users WHERE username = 'john';

索引下推

MySQL 5.6引入了索引条件下推（ICP），可以在索引遍历过程中直接过滤不符合条件的记录，减少回表次数。

索引合并

当查询条件涉及多个列，每个列都有独立的索引时，MySQL可能会使用索引合并策略，同时利用多个索引进行扫描并合并结果。

索引失效场景与避免策略

即使创建了索引，不正确的查询写法也可能导致索引无法使用。了解这些场景有助于编写高效的查询。

对索引列使用函数或表达式：如 WHERE YEAR(date_col) = 2023，应改为 WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31'。
隐式类型转换：如果索引列是字符串类型，但WHERE条件中传入数字，如 WHERE phone = 123456，应改为字符串形式。
LIKE通配符以 '%' 开头：LIKE '%abc' 会导致全表扫描，而 LIKE 'abc%' 则可以利用索引。
使用 OR 连接条件：如果OR两边的列不是同一个索引，或者没有覆盖所有条件，可能不会使用索引。可以考虑使用 UNION 替代。
NOT IN, <>, != 操作：这些操作通常会导致索引失效，可以使用 NOT EXISTS 或改写查询。
IS NULL 或 IS NOT NULL：对于允许NULL的列，使用IS NULL可能无法有效利用索引。

最佳实践：经常使用 EXPLAIN 检查查询的执行计划，关注 `type` 列是否为 `ALL`（全表扫描），`key` 列是否使用了预期的索引。

创建索引的方法

创建表时创建索引

                        SQL示例
                    

                        CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    INDEX idx_username (username),
    INDEX idx_age (age),
    INDEX idx_username_age (username, age)
);
                    

修改表添加索引

                        SQL示例
                    

                        -- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_name_age (last_name, age);

-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);
                    

删除索引

                        SQL示例
                    
                        -- 删除索引
DROP INDEX idx_username ON users;

-- 或者使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_username;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

查看索引信息

                        SQL示例
                    

                        -- 查看表的索引信息
SHOW INDEX FROM users;

-- 查看索引创建语句
SHOW CREATE TABLE users;

-- 从information_schema查看索引信息
SELECT * 
FROM information_schema.STATISTICS 
WHERE table_name = 'users';
                    

索引使用原则

适合创建索引的情况

主键自动创建唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段
查询中排序的字段
查询中统计或分组的字段
具有高选择性的列（不同值多的列）
经常需要范围查询的列

不适合创建索引的情况

表记录太少
经常增删改的表
数据重复且分布均匀的字段
频繁更新的字段
WHERE条件中用不到的字段
数据量小的表
很少被查询的列

最佳实践：索引不是越多越好，过多的索引会影响写入性能。应该根据实际查询需求创建合适的索引。定期分析查询性能，删除不必要的索引。

索引选择策略

索引选择性

索引选择性是指不重复的索引值与总记录数的比例。选择性越高，索引效率越好。

                        SQL示例
                    

                        -- 计算索引选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;
                    

前缀索引

对于较长的字符串列，可以只对列的前缀创建索引，节省索引空间。

                        SQL示例
                    
                        -- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

索引维护与监控

索引需要定期维护以确保其效率和回收空间。

                        SQL示例
                    

                        -- 查看未使用的索引（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;

-- 重建或优化表以整理索引碎片
OPTIMIZE TABLE users;
-- 或使用 ALTER TABLE 重建（InnoDB）
ALTER TABLE users ENGINE=InnoDB;
                    

EXPLAIN分析查询

使用EXPLAIN命令可以分析SQL查询的执行计划，了解索引使用情况：

                        SQL示例
                    

                        -- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 获取更详细的JSON格式执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john';

-- 分析连接查询
EXPLAIN SELECT u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.country = 'China';
                    

EXPLAIN结果解读

列名	描述
id	查询标识符
select_type	查询类型（SIMPLE, PRIMARY, SUBQUERY等）
table	访问的表
type	连接类型（const, eq_ref, ref, range, index, ALL）
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
ref	与索引比较的列
rows	估计要检查的行数
Extra	额外信息（Using where, Using index等）

使用 EXPLAIN ANALYZE（MySQL 8.0.18+）可以获取实际执行时间和成本，比普通EXPLAIN更精确。

                        SQL示例
                    
                        EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';

索引优化案例

案例1：慢查询优化

                        SQL示例
                    

                        -- 优化前（没有合适的索引）
SELECT * FROM orders 
WHERE user_id = 123 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 优化后（创建复合索引）
CREATE INDEX idx_user_date ON orders(user_id, order_date);
                    

案例2：排序优化

                        SQL示例
                    

                        -- 优化前（需要文件排序）
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price DESC;

-- 优化后（使用索引排序）
CREATE INDEX idx_category_price ON products(category_id, price DESC);
                    

案例3：利用覆盖索引避免回表

                        SQL示例
                    

                        -- 查询只需要id和username，创建覆盖索引
CREATE INDEX idx_id_username ON users(id, username);
-- 查询直接从索引中获取数据，无需回表
SELECT id, username FROM users WHERE id BETWEEN 1 AND 1000;
                    

下一步学习

掌握了索引知识后，您可以继续学习：

视图 - 了解如何创建和使用数据库视图
存储过程 - 学习创建和使用存储过程
触发器 - 学习创建和使用触发器