MySQL 索引 - MySQL 教程 | 小傅哥技术教程

索引概述

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

                    索引的优势：
                    大幅提高查询速度
加快 WHERE 子句的过滤速度
提高 ORDER BY 和 GROUP BY 的性能
加速表连接操作
确保数据的唯一性

                

索引的劣势：

占用额外的存储空间
降低 INSERT、UPDATE、DELETE 的性能
维护索引需要额外的开销

索引类型

🔑 主键索引

PRIMARY KEY，唯一且不能为空，每个表只能有一个

🔒 唯一索引

UNIQUE，确保列值的唯一性，可以有多个

📋 普通索引

INDEX，最基本的索引类型，没有唯一性限制

🔍 复合索引

多列组合的索引，遵循最左前缀原则

📝 全文索引

FULLTEXT，用于全文搜索，支持自然语言搜索

🌐 空间索引

SPATIAL，用于地理空间数据类型

示例数据准备

-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    brand VARCHAR(50),
    description TEXT,
    stock_quantity INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入大量测试数据
INSERT INTO products (name, category, price, brand, description, stock_quantity) VALUES
('iPhone 14 Pro', '手机', 7999.00, 'Apple', 'Apple iPhone 14 Pro 128GB 深空黑色', 50),
('Samsung Galaxy S23', '手机', 5999.00, 'Samsung', 'Samsung Galaxy S23 256GB 幻影黑', 30),
('MacBook Air M2', '笔记本', 8999.00, 'Apple', 'Apple MacBook Air 13英寸 M2芯片', 20),
('Dell XPS 13', '笔记本', 6999.00, 'Dell', 'Dell XPS 13 9320 Intel i7', 15),
('iPad Pro 11', '平板', 6199.00, 'Apple', 'Apple iPad Pro 11英寸 第4代', 25),
('Surface Pro 9', '平板', 7888.00, 'Microsoft', 'Microsoft Surface Pro 9 Intel i5', 18),
('AirPods Pro 2', '耳机', 1899.00, 'Apple', 'Apple AirPods Pro 第2代', 100),
('Sony WH-1000XM5', '耳机', 2399.00, 'Sony', 'Sony WH-1000XM5 无线降噪耳机', 40);

-- 为了演示，我们需要更多数据
-- 使用存储过程生成大量测试数据
DELIMITER //
CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10000 DO
        INSERT INTO products (name, category, price, brand, description, stock_quantity) VALUES
        (CONCAT('Product ', i), 
         CASE (i % 4) 
             WHEN 0 THEN '手机'
             WHEN 1 THEN '笔记本'
             WHEN 2 THEN '平板'
             ELSE '耳机'
         END,
         ROUND(RAND() * 10000 + 100, 2),
         CASE (i % 5)
             WHEN 0 THEN 'Apple'
             WHEN 1 THEN 'Samsung'
             WHEN 2 THEN 'Dell'
             WHEN 3 THEN 'Sony'
             ELSE 'Microsoft'
         END,
         CONCAT('Description for product ', i),
         FLOOR(RAND() * 100) + 1
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程生成数据
CALL GenerateTestData();

-- 删除存储过程
DROP PROCEDURE GenerateTestData;
                

创建索引

基本语法

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2, column3);

-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

-- 使用 ALTER TABLE 创建索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);
                

实际示例

-- 为产品名称创建索引
CREATE INDEX idx_product_name ON products (name);

-- 为品牌创建索引
CREATE INDEX idx_brand ON products (brand);

-- 为价格创建索引
CREATE INDEX idx_price ON products (price);

-- 为分类和品牌创建复合索引
CREATE INDEX idx_category_brand ON products (category, brand);

-- 为价格范围查询创建索引
CREATE INDEX idx_price_range ON products (price, stock_quantity);

-- 为产品描述创建全文索引
CREATE FULLTEXT INDEX idx_description_fulltext ON products (description);

-- 为创建时间创建索引
CREATE INDEX idx_created_at ON products (created_at);

-- 查看表的所有索引
SHOW INDEX FROM products;
                

查看索引信息

-- 查看表的索引
SHOW INDEX FROM products;

-- 查看索引的详细信息
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE,
    INDEX_TYPE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 查看索引的大小
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS 'Size (MB)'
FROM mysql.innodb_index_stats 
WHERE TABLE_NAME = 'products' AND STAT_NAME = 'size';

-- 查看表的存储空间使用情况
SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 'Index Size (MB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total Size (MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products';
                

索引的使用和优化

查询执行计划

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM products WHERE name = 'iPhone 14 Pro';

EXPLAIN SELECT * FROM products WHERE brand = 'Apple';

EXPLAIN SELECT * FROM products WHERE category = '手机' AND brand = 'Apple';

EXPLAIN SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;

-- 使用 EXPLAIN FORMAT=JSON 获取详细信息
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category = '手机' AND price > 5000;

-- 分析索引使用情况
EXPLAIN SELECT * FROM products 
WHERE category = '手机' AND brand = 'Apple' AND price > 5000
ORDER BY created_at DESC;
                

复合索引的最左前缀原则

-- 创建复合索引 (category, brand, price)
CREATE INDEX idx_category_brand_price ON products (category, brand, price);

-- 以下查询可以使用索引
EXPLAIN SELECT * FROM products WHERE category = '手机';  -- 使用索引
EXPLAIN SELECT * FROM products WHERE category = '手机' AND brand = 'Apple';  -- 使用索引
EXPLAIN SELECT * FROM products WHERE category = '手机' AND brand = 'Apple' AND price > 5000;  -- 使用索引

-- 以下查询不能完全使用索引
EXPLAIN SELECT * FROM products WHERE brand = 'Apple';  -- 不能使用索引
EXPLAIN SELECT * FROM products WHERE price > 5000;  -- 不能使用索引
EXPLAIN SELECT * FROM products WHERE brand = 'Apple' AND price > 5000;  -- 不能使用索引

-- 跳过中间列的查询
EXPLAIN SELECT * FROM products WHERE category = '手机' AND price > 5000;  -- 只能使用 category 部分
                

索引覆盖查询

-- 创建覆盖索引
CREATE INDEX idx_cover_query ON products (category, brand, price, name);

-- 覆盖查询（所有需要的列都在索引中）
EXPLAIN SELECT category, brand, price, name 
FROM products 
WHERE category = '手机' AND brand = 'Apple';

-- 查看 Extra 列是否显示 "Using index"
                

全文索引

创建和使用全文索引

-- 确保已创建全文索引
CREATE FULLTEXT INDEX idx_name_desc_fulltext ON products (name, description);

-- 自然语言模式搜索
SELECT name, description, 
       MATCH(name, description) AGAINST('iPhone Apple') AS relevance
FROM products 
WHERE MATCH(name, description) AGAINST('iPhone Apple')
ORDER BY relevance DESC;

-- 布尔模式搜索
SELECT name, description
FROM products 
WHERE MATCH(name, description) AGAINST('+iPhone -Samsung' IN BOOLEAN MODE);

-- 查询扩展模式
SELECT name, description
FROM products 
WHERE MATCH(name, description) AGAINST('Apple' WITH QUERY EXPANSION);

-- 短语搜索
SELECT name, description
FROM products 
WHERE MATCH(name, description) AGAINST('"iPhone 14"' IN BOOLEAN MODE);
                

全文索引配置

-- 查看全文索引相关配置
SHOW VARIABLES LIKE 'ft_%';

-- 设置最小词长度
SET GLOBAL ft_min_word_len = 2;

-- 查看停用词
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

-- 查看全文索引统计信息
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;
                

索引优化技巧

选择性分析

-- 分析列的选择性（唯一值的比例）
SELECT 
    COUNT(DISTINCT category) / COUNT(*) AS category_selectivity,
    COUNT(DISTINCT brand) / COUNT(*) AS brand_selectivity,
    COUNT(DISTINCT price) / COUNT(*) AS price_selectivity,
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity
FROM products;

-- 查看列的基数（唯一值的数量）
SELECT 
    'category' AS column_name, COUNT(DISTINCT category) AS cardinality
FROM products
UNION ALL
SELECT 
    'brand' AS column_name, COUNT(DISTINCT brand) AS cardinality
FROM products
UNION ALL
SELECT 
    'price' AS column_name, COUNT(DISTINCT price) AS cardinality
FROM products;

-- 分析数据分布
SELECT category, COUNT(*) as count, 
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 2) as percentage
FROM products 
GROUP BY category 
ORDER BY count DESC;
                

前缀索引

-- 分析字符串列的前缀选择性
SELECT 
    COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS prefix_5_selectivity,
    COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS prefix_10_selectivity,
    COUNT(DISTINCT LEFT(name, 15)) / COUNT(*) AS prefix_15_selectivity,
    COUNT(DISTINCT name) / COUNT(*) AS full_selectivity
FROM products;

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON products (name(10));

-- 测试前缀索引的效果
EXPLAIN SELECT * FROM products WHERE name LIKE 'iPhone%';
                

函数索引（MySQL 8.0+）

-- 创建函数索引
CREATE INDEX idx_upper_name ON products ((UPPER(name)));

-- 创建表达式索引
CREATE INDEX idx_price_category ON products ((price * 0.8), category);

-- 使用函数索引
EXPLAIN SELECT * FROM products WHERE UPPER(name) = 'IPHONE 14 PRO';
                

索引监控和维护

索引使用统计

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE() AND OBJECT_NAME = 'products';

-- 查找未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE() 
  AND OBJECT_NAME = 'products'
  AND INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0;
                

索引维护

-- 分析表和索引
ANALYZE TABLE products;

-- 优化表（重建索引）
OPTIMIZE TABLE products;

-- 检查表的完整性
CHECK TABLE products;

-- 修复表
REPAIR TABLE products;

-- 查看索引统计信息
SHOW TABLE STATUS LIKE 'products';
                

重建索引

-- 删除并重建索引
DROP INDEX idx_category_brand ON products;
CREATE INDEX idx_category_brand ON products (category, brand);

-- 使用 ALTER TABLE 重建索引
ALTER TABLE products DROP INDEX idx_price, ADD INDEX idx_price (price);
                

索引设计最佳实践

                    索引设计原则：
                    为经常出现在 WHERE 子句中的列创建索引
为经常用于 JOIN 的列创建索引
为经常用于 ORDER BY 的列创建索引
复合索引的列顺序很重要，选择性高的列放在前面
避免在小表上创建过多索引
定期监控和维护索引

                

索引设计示例

-- 针对常见查询模式设计索引

-- 1. 单列查询
-- 查询：SELECT * FROM products WHERE brand = 'Apple'
CREATE INDEX idx_brand ON products (brand);

-- 2. 范围查询
-- 查询：SELECT * FROM products WHERE price BETWEEN 1000 AND 5000
CREATE INDEX idx_price ON products (price);

-- 3. 多条件查询
-- 查询：SELECT * FROM products WHERE category = '手机' AND brand = 'Apple'
CREATE INDEX idx_category_brand ON products (category, brand);

-- 4. 排序查询
-- 查询：SELECT * FROM products WHERE category = '手机' ORDER BY price DESC
CREATE INDEX idx_category_price ON products (category, price);

-- 5. 分组查询
-- 查询：SELECT category, COUNT(*) FROM products GROUP BY category
CREATE INDEX idx_category ON products (category);

-- 6. 覆盖查询
-- 查询：SELECT id, name, price FROM products WHERE category = '手机'
CREATE INDEX idx_category_cover ON products (category, id, name, price);
                

避免的索引设计

-- 避免的索引设计模式

-- 1. 避免在低选择性列上创建索引
-- 不好：性别列只有 M/F 两个值
-- CREATE INDEX idx_gender ON users (gender);  -- 避免

-- 2. 避免在经常更新的列上创建过多索引
-- 不好：频繁更新的状态列
-- CREATE INDEX idx_status ON orders (status);  -- 谨慎使用

-- 3. 避免创建冗余索引
-- 如果已有 (a, b, c) 索引，就不需要 (a) 和 (a, b) 索引
-- CREATE INDEX idx_abc ON table (a, b, c);     -- 主索引
-- CREATE INDEX idx_a ON table (a);             -- 冗余
-- CREATE INDEX idx_ab ON table (a, b);         -- 冗余

-- 4. 避免在小表上创建索引
-- 对于行数很少的表，全表扫描可能比索引查找更快
                

性能测试和比较

-- 性能测试示例

-- 测试无索引的查询性能
SET profiling = 1;
SELECT * FROM products WHERE brand = 'Apple' AND category = '手机';
SHOW PROFILES;

-- 创建索引后测试
CREATE INDEX idx_brand_category ON products (brand, category);
SELECT * FROM products WHERE brand = 'Apple' AND category = '手机';
SHOW PROFILES;

-- 比较不同索引顺序的性能
DROP INDEX idx_brand_category ON products;
CREATE INDEX idx_category_brand ON products (category, brand);
SELECT * FROM products WHERE brand = 'Apple' AND category = '手机';
SHOW PROFILES;

-- 关闭性能分析
SET profiling = 0;
                

慢查询分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1秒以上的查询记录为慢查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 使用 mysqldumpslow 分析慢查询日志（命令行工具）
-- mysqldumpslow -s c -t 10 /path/to/slow-query.log
                

删除索引

-- 删除索引的语法
DROP INDEX index_name ON table_name;

-- 使用 ALTER TABLE 删除索引
ALTER TABLE table_name DROP INDEX index_name;

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

-- 删除示例
DROP INDEX idx_brand ON products;
DROP INDEX idx_category_brand ON products;
DROP INDEX idx_description_fulltext ON products;

-- 批量删除索引
ALTER TABLE products 
DROP INDEX idx_price,
DROP INDEX idx_created_at,
DROP INDEX idx_cover_query;
                

注意：删除索引前请确保该索引不被重要查询使用，可以先通过性能监控确认索引的使用情况。

索引故障排除

问题	可能原因	解决方案
查询仍然很慢	索引未被使用	检查查询条件，确保符合索引使用规则
索引不生效	数据类型不匹配	确保查询条件的数据类型与索引列一致
复合索引无效	违反最左前缀原则	调整查询条件或重新设计索引
插入性能下降	索引过多	删除不必要的索引
索引占用空间大	索引设计不合理	使用前缀索引或重新设计

-- 诊断索引问题

-- 1. 检查查询是否使用了索引
EXPLAIN SELECT * FROM products WHERE name = 'iPhone';
-- 查看 type 列：const > eq_ref > ref > range > index > ALL
-- 查看 key 列：显示使用的索引名称
-- 查看 rows 列：扫描的行数

-- 2. 检查索引的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 3. 检查是否存在函数或表达式
-- 错误：WHERE UPPER(name) = 'IPHONE'  -- 无法使用普通索引
-- 正确：WHERE name = 'iPhone'         -- 可以使用索引

-- 4. 检查数据类型转换
-- 错误：WHERE id = '123'    -- 如果 id 是 INT 类型
-- 正确：WHERE id = 123      -- 避免隐式类型转换
                

🚀 MySQL 索引