一、索引
一种数据结构（通常是B+树），存储字段值和对应记录的物理地址
（一）索引类型
普通索引： 最基本的索引类型
唯一索引： 索引列值必须唯一
主键索引： 特殊的唯一索引，不允许NULL值
复合索引： 多列组合的索引
全文索引： 用于全文搜索，MyISAM和InnoDB都支持
空间索引： 用于地理空间数据类型
B-Tree索引： 最常用的索引类型，适合全键值、键值范围或键前缀查找
哈希索引： 基于哈希表实现，只支持精确匹配，不支持范围查询
（二）索引创建原则
1、WHERE子句中的列：频繁作为查询条件的列
2、JOIN关联列：经常用于表连接的列
3、ORDER BY/GROUP BY列：排序或分组操作的列
4、高选择性的列：区分度高（不同值多）的列
（三）避免过度索引
1、每个索引都会增加写入操作的开销
2、索引会占用额外的存储空间
3、维护索引需要CPU资源
（四）多列索引（复合索引）
1、最左前缀原则： 查询必须使用索引的最左列才能利用索引,索引(a,b,c)可以用于查询条件(a)、(a,b)、(a,b,c)，但不能用于(b,c)
2、列顺序：
a、将选择性高的列放在前面
b、等值查询列放在范围查询列前面
c、常用列放在前面
3、列数限制： 通常不超过5列
二、优化策略
（一）使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = 'john';
重点关注
关注type列： 从优到差 system > const > eq_ref > ref > range > index > ALL， 最好达到const、eq_ref、ref、range级别
Extra列： Using index(覆盖索引)、Using filesort(需要优化)等
key列： 显示实际使用的索引
rows列： 估计需要检查的行数
（二）覆盖索引优化
查询的列都包含在索引中，避免回表操作
回表是指MySQL在使用二级索引（非聚簇索引）查询数据时，需要根据索引中存储的主键值再次查询聚簇索引（主键索引）以获取完整数据行的过程。
回表操作会导致额外的磁盘I/O，当回表次数多时（如大量数据满足条件），查询性能会显著下降。
回表过程：
二级索引(age)
| age | id(PK) |
|---|---|
| 18 | 3 |
| 20 | 1 |
| 22 | 2 |
| 20 | 4 |
聚簇索引(id)
| id(PK) | name | age |
|---|---|---|
| 1 | Tom | 20 |
| 2 | Jack | 22 |
| 3 | Lucy | 18 |
| 4 | Lily | 20 |
回表查询步骤：
1、查询二级索引：首先在age索引中查找age=20的记录，找到两条记录：(20,1)和(20,4)
2、获取主键值：从二级索引中提取主键值1和4
3、回表查询：用主键值1和4去聚簇索引中查找完整记录
查找id=1的记录：Tom,20
查找id=4的记录：Lily,20
4、返回结果：将两条完整记录返回给客户端
发生回表情况：
a、查询的列不完全包含在二级索引中
b、使用了SELECT *查询
c、查询包含非索引列的WHERE条件
示例：
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);
-- 优化后的查询（不需要访问数据行）
SELECT customer_id, order_date FROM orders WHERE customer_id = 123;
案例：优化慢查询
-- 原始查询（慢）
SELECT * FROM orders
WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC
LIMIT 100;
优化方案
1、创建复合索引
ALTER TABLE orders ADD INDEX idx_customer_date_amount (customer_id, order_date, total_amount DESC);
2、优化查询（覆盖索引）
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 123 AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY total_amount DESC
LIMIT 100;
（三）避免索引失效的情况
1、不要在索引列上使用函数
1）字符串函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM products WHERE SUBSTRING(product_code, 1, 3) = 'ABC';
替代方案
存储计算后的值：在表中添加一个计算列并建立索引
ALTER TABLE users ADD COLUMN name_upper VARCHAR(255);
UPDATE users SET name_upper = UPPER(name);
CREATE INDEX idx_name_upper ON users(name_upper);
SELECT * FROM users WHERE name_upper = 'JOHN';
使用生成列（MySQL 5.7+）
ALTER TABLE users ADD COLUMN name_upper VARCHAR(255) GENERATED ALWAYS AS (UPPER(name)) STORED;
CREATE INDEX idx_name_upper ON users(name_upper);
在应用层处理：将数据转换为小写/大写后再查询
2）日期/时间函数
问题示例
SELECT * FROM orders WHERE DATE(order_date) = '2023-01-01';
SELECT * FROM logs WHERE YEAR(log_time) = 2023;
替代方案：范围查询
SELECT * FROM orders
WHERE order_date >= '2023-01-01 00:00:00'
AND order_date < '2023-01-02 00:00:00';
添加日期分区列
ALTER TABLE orders ADD COLUMN order_day DATE;
UPDATE orders SET order_day = DATE(order_date);
CREATE INDEX idx_order_day ON orders(order_day);
SELECT * FROM orders WHERE order_day = '2023-01-01';
使用生成列
ALTER TABLE orders ADD COLUMN order_day DATE GENERATED ALWAYS AS (DATE(order_date)) STORED;
CREATE INDEX idx_order_day ON orders(order_day);
错误示例（索引失效）
SELECT * FROM users WHERE YEAR(create_time) = 2023;
正确做法
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
3）数学运算
问题示例
SELECT * FROM products WHERE price * 1.1 > 100;
SELECT * FROM inventory WHERE quantity + 10 < 100;
替代方案：重写查询
SELECT * FROM products WHERE price > 100 / 1.1;
SELECT * FROM inventory WHERE quantity < 100 - 10;
存储计算后的值
ALTER TABLE products ADD COLUMN price_with_tax DECIMAL(10,2);
UPDATE products SET price_with_tax = price * 1.1;
CREATE INDEX idx_price_with_tax ON products(price_with_tax);
SELECT * FROM products WHERE price_with_tax > 100;
4）类型转换
问题示例
SELECT * FROM users WHERE CAST(id AS CHAR) = '123';
SELECT * FROM products WHERE CONCAT(product_id, '') = '456';
替代方案：确保比较类型一致
SELECT * FROM users WHERE id = 123; -- 如果id是数字类型
在应用层处理类型转换
2、避免隐式类型转换
问题示例
-- 如果email列是VARCHAR但用数字比较
SELECT * FROM users WHERE email = 12345;
替代方案：显式转换或修正查询
SELECT * FROM users WHERE email = '12345';
3、避免使用NOT、!=、<>、NOT IN（某些情况下）
问题示例
SELECT * FROM users WHERE status <> 'active';
SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);
替代方案
确保列有索引（这些操作通常可以使用索引，但大数据集时可能效率不高）
考虑使用 NOT EXISTS 替代 NOT IN（对于子查询情况）
4、避免使用OR条件（除非所有列都有索引）
问题示例
SELECT * FROM users WHERE name = 'John' OR age = 30;
替代方案：使用 UNION ALL
SELECT * FROM users WHERE name = 'John'
UNION ALL
SELECT * FROM users WHERE age = 30 AND name <> 'John';
5、避免使用LIKE前导通配符
问题示例
SELECT * FROM products WHERE name LIKE '%phone';
替代方案：使用全文索引
ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone' IN BOOLEAN MODE);
考虑使用专门的搜索引擎（如Elasticsearch）
（四）使用索引排序优化
当ORDER BY的列与索引顺序一致时，可避免文件排序(Using filesort)
-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_customer_status_date (customer_id, status, order_date);
-- 优化后的查询（利用索引排序）
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'completed'
ORDER BY order_date DESC;
（五）分页查询优化
--低效
SELECT * FROM users ORDER BY id LIMIT 10000, 20;
--优化
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
（六）JOIN优化
确保关联字段有索引
小表驱动大表原则
（七）升级到 MySQL 8.0+
利用函数索引功能（MySQL 8.0 支持函数索引），在 MySQL 8.0 及以上版本，可以直接创建函数索引
-- 创建函数索引
CREATE INDEX idx_name_upper ON users((UPPER(name)));
-- 使用函数索引的查询
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
三、索引管理
1、定期分析表
--更新索引统计信息
ANALYZE TABLE users;
--查看索引基数(Cardinality)
SHOW INDEX FROM table;
2、优化表（重建表，减少碎片）
OPTIMIZE TABLE users;
3、监控索引使用情况
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看索引统计信息
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;
4、删除无用索引
根据监控结果删除长期未使用的索引
四、高级技巧
1、前缀索引
对字符串列的前N个字符建立索引，节省空间，但会降低选择性
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));
2、倒排索引
对于全文搜索需求，考虑使用专门的搜索引擎如 Elasticsearch
3、索引合并优化
MySQL可能使用多个索引合并结果（type列为index_merge）,对多个单列索引进行合并使用,通过EXPLAIN查看type为index_merge时表示使用了索引合并
4、自适应哈希索引
InnoDB自动为热点页创建哈希索引，无需手动配置
5、索引下推(ICP)
MySQL 5.6+特性，将WHERE条件部分下推到存储引擎层
减少回表次数，提高查询效率
6、MRR优化(Multi-Range Read)
将随机I/O转为顺序I/O，提高范围查询效率
需要设置 optimizer_switch='mrr=on'
