1. 主键索引（PRIMARY KEY）

特点：
– 唯一标识每一行
– 不能为NULL
– 每个表只能有一个主键
– 自动创建聚簇索引

创建主键：
mysql> CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (id)
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

添加主键：
mysql> ALTER TABLE users ADD PRIMARY KEY (id);

输出示例：
Query OK, 0 rows affected (0.03 sec)

2. 唯一索引（UNIQUE）

特点：
– 确保列值唯一
– 允许NULL值（但只能有一个）
– 可用于避免数据重复

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

输出示例：
Query OK, 0 rows affected (0.05 sec)

添加唯一约束：
mysql> ALTER TABLE users ADD UNIQUE KEY uk_email (email);

输出示例：
Query OK, 0 rows affected (0.04 sec)

3. 普通索引（INDEX）

特点：
– 最基本的索引类型
– 允许重复值和NULL
– 用于加速查询

创建普通索引：
mysql> CREATE INDEX idx_name ON users(name);

输出示例：
Query OK, 0 rows affected (0.03 sec)

查看索引：
mysql> SHOW INDEX FROM users;

输出示例：
+——-+————+———-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——-+————+———-+————–+————-+
| users | 0 | PRIMARY | 1 | id |
| users | 1 | idx_name | 1 | name |
+——-+————+———-+————–+————-+

4. 复合索引（Composite Index）

特点：
– 多列组合索引
– 遵循最左前缀原则
– 可覆盖多个查询条件

创建复合索引：
mysql> CREATE INDEX idx_status_created ON orders(status, created_at);

输出示例：
Query OK, 0 rows affected (0.08 sec)

查看复合索引：
mysql> SHOW INDEX FROM orders WHERE Key_name = ‘idx_status_created’;

输出示例：
+——–+————+——————-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——–+————+——————-+————–+————-+
| orders | 1 | idx_status_created| 1 | status |
| orders | 1 | idx_status_created| 2 | created_at |
+——–+————+——————-+————–+————-+

5. 全文索引（FULLTEXT）

特点：
– 用于全文搜索
– 支持中文分词（MySQL 5.7.6+）
– 仅支持CHAR、VARCHAR、TEXT类型

创建全文索引：
mysql> CREATE FULLTEXT INDEX idx_content ON articles(title, content);

输出示例：
Query OK, 0 rows affected (0.15 sec)

使用全文搜索：
mysql> SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(‘MySQL优化’);

输出示例：
+—-+—————-+———————+
| id | title | content |
+—-+—————-+———————+
| 1 | MySQL优化指南 | 本文介绍MySQL优化…|
+—-+—————-+———————+

6. 空间索引（SPATIAL）

特点：
– 用于地理空间数据
– 支持GEOMETRY类型
– 仅MyISAM和InnoDB支持

创建空间索引：
mysql> CREATE SPATIAL INDEX idx_location ON stores(location);

输出示例：
Query OK, 0 rows affected (0.10 sec)

使用空间查询：
mysql> SELECT * FROM stores
WHERE ST_Contains(ST_GeomFromText(‘POLYGON(…)’), location);

输出示例：
+—-+———-+———————+
| id | name | location |
+—-+———-+———————+
| 1 | Store A | POINT(116.4 39.9) |
+—-+———-+———————+

7. 函数索引（MySQL 8.0+）

特点：
– 基于函数表达式创建索引
– 解决函数导致索引失效问题

创建函数索引：
mysql> CREATE INDEX idx_lower_email ON users((LOWER(email)));

输出示例：
Query OK, 0 rows affected (0.05 sec)

使用函数索引：
mysql> SELECT * FROM users WHERE LOWER(email) = ‘test@example.com’;

输出示例：
+—-+——+——————+
| id | name | email |
+—-+——+——————+
| 1 | test | TEST@example.com |
+—-+——+——————+

1. B+Tree索引

结构特点：
– 多路平衡搜索树
– 非叶子节点只存储键值
– 叶子节点存储所有数据
– 叶子节点通过指针连接

B+Tree结构示意图：
[根节点]
/ | \
[中间节点] [中间节点] [中间节点]
/ | \
[叶子节点] -> [叶子节点] -> [叶子节点]
(数据) (数据) (数据)

查看索引高度：
mysql> SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE AS height
FROM mysql.innodb_index_stats
WHERE STAT_NAME = ‘n_leaf_pages’
AND TABLE_NAME = ‘orders’;

输出示例：
+————+——————-+——–+
| TABLE_NAME | INDEX_NAME | height |
+————+——————-+——–+
| orders | PRIMARY | 3 |
| orders | idx_status | 2 |
+————+——————-+——–+

2. 聚簇索引

特点：
– 主键索引即聚簇索引
– 数据按主键顺序存储
– 叶子节点包含完整行数据

聚簇索引结构：
+——————-+
| 非叶子节点 | 存储主键值
+——————-+
|
v
+——————-+
| 叶子节点 | 存储完整行数据
| id=1, name=… |
+——————-+

查看聚簇索引：
mysql> SHOW INDEX FROM orders WHERE Key_name = ‘PRIMARY’;

输出示例：
+——–+————+———-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——–+————+———-+————–+————-+
| orders | 0 | PRIMARY | 1 | id |
+——–+————+———-+————–+————-+

3. 二级索引

特点：
– 非主键索引
– 叶子节点存储主键值
– 需要回表查询完整数据

二级索引结构：
+——————-+
| 非叶子节点 | 存储索引列值
+——————-+
|
v
+——————-+
| 叶子节点 | 存储索引列值 + 主键值
| status=’pending’ |
| id=1 |
+——————-+

回表查询过程：
1. 在二级索引中找到主键值
2. 在聚簇索引中查找完整数据

示例：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;

输出示例：
+—-+————-+——–+————+——+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————+
| 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status |
+—-+————-+——–+————+——+—————+————+

4. 自适应哈希索引

特点：
– InnoDB自动创建
– 针对热点数据
– 提高等值查询速度

查看自适应哈希索引状态：
mysql> SHOW VARIABLES LIKE ‘innodb_adaptive_hash_index’;

输出示例：
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_adaptive_hash_index | ON |
+—————————-+——-+

查看AHI使用情况：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
———————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
———————-
Ibuf: size 1, free list len 0, seg size 2, 0 merges
Hash table size 34679, used cells 100, node heap has 1 buffer(s)

1. 选择合适的列

适合创建索引的列：
– WHERE条件列
– JOIN连接列
– ORDER BY排序列
– GROUP BY分组列
– 高选择性列（区分度高）

查看列选择性：
mysql> SELECT
COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;

输出示例：
+————-+
| selectivity |
+————-+
| 0.0001 |
+————-+

高选择性示例：
mysql> SELECT
COUNT(DISTINCT email) / COUNT(*) AS selectivity
FROM users;

输出示例：
+————-+
| selectivity |
+————-+
| 0.9999 |
+————-+

2. 最左前缀原则

复合索引使用规则：
– 从索引最左列开始
– 不能跳过中间列
– 范围查询后的列不使用索引

创建复合索引：
mysql> CREATE INDEX idx_a_b_c ON test(a, b, c);

输出示例：
Query OK, 0 rows affected (0.05 sec)

有效使用：
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)

无效使用：
WHERE b = 2 — 不使用索引
WHERE c = 3 — 不使用索引
WHERE a = 1 AND b > 2 AND c = 3 — 使用索引(a, b)，c不使用

3. 避免冗余索引

冗余索引示例：
INDEX idx_a (a)
INDEX idx_a_b (a, b) — idx_a是冗余的

查找冗余索引：
mysql> SELECT * FROM sys.schema_redundant_indexes\G

输出示例：
*************************** 1. row ***************************
table_schema: production_db
table_name: orders
redundant_index_name: idx_a
redundant_index_columns: a
dominant_index_name: idx_a_b
dominant_index_columns: a, b

4. 控制索引数量

索引数量建议：
– 单表索引数量：建议<5个 - 复合索引列数：建议<5列 - 索引总大小：建议<表大小的30% 查看索引统计： mysql> SELECT
TABLE_NAME,
COUNT(*) AS index_count,
SUM(STAT_VALUE) * @@innodb_page_size / 1024 / 1024 AS size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = ‘production_db’
GROUP BY TABLE_NAME;

输出示例：
+————+————-+———-+
| TABLE_NAME | index_count | size_mb |
+————+————-+———-+
| orders | 4 | 512.00 |
| users | 3 | 128.00 |
+————+————-+———-+

5. 索引列顺序

排序原则：
– 等值条件列放前面
– 范围条件列放后面
– 排序列考虑放后面

示例：
— 查询条件：status = ‘pending’ AND created_at > ‘2026-01-01’ ORDER BY amount
— 建议索引：idx_status_created_amount(status, created_at, amount)

创建优化索引：
mysql> CREATE INDEX idx_status_created_amount
ON orders(status, created_at, amount);

输出示例：
Query OK, 0 rows affected (0.10 sec)

1. 分析查询模式

收集查询统计：
mysql> SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE ‘%orders%’
ORDER BY COUNT_STAR DESC
LIMIT 10;

输出示例：
+——————————–+————+——————+
| DIGEST_TEXT | COUNT_STAR | SUM_ROWS_EXAMINED|
+——————————–+————+——————+
| SELECT * FROM `orders` WHERE…| 12345 | 1234567890|
+——————————–+————+——————+

分析慢查询：
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

输出示例：
Count: 100 Time=5.00s (500s) Lock=0.00s (0s) Rows=1000.0
SELECT * FROM orders WHERE status = ‘S’

2. 确定索引优先级

优先级排序：
1. 主键索引（必须有）
2. 外键索引（关联查询）
3. 高频查询索引
4. 排序/分组索引
5. 其他查询索引

评估索引收益：
mysql> SELECT
TABLE_NAME,
INDEX_NAME,
ROWS_READ,
ROWS_INSERTED
FROM sys.schema_index_statistics
WHERE TABLE_SCHEMA = ‘production_db’
ORDER BY ROWS_READ DESC;

输出示例：
+————+————+———–+—————+
| TABLE_NAME | INDEX_NAME | ROWS_READ | ROWS_INSERTED |
+————+————+———–+—————+
| orders | PRIMARY | 1234567 | 123456 |
| orders | idx_status | 234567 | 0 |
+————+————+———–+—————+

3. 设计复合索引

复合索引设计步骤：
1. 分析WHERE条件
2. 确定列的选择性
3. 考虑排序需求
4. 遵循最左前缀

示例：
— 查询：SELECT * FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC

— 分析：
— customer_id: 选择性高
— status: 选择性低
— created_at: 排序列

— 建议索引：
mysql> CREATE INDEX idx_customer_status_created
ON orders(customer_id, status, created_at);

输出示例：
Query OK, 0 rows affected (0.12 sec)

4. 考虑覆盖索引

覆盖索引优势：
– 避免回表查询
– 减少I/O操作
– 提高查询速度

设计覆盖索引：
— 查询：SELECT status, COUNT(*) FROM orders WHERE customer_id = ? GROUP BY status

— 覆盖索引：
mysql> CREATE INDEX idx_customer_status_cover
ON orders(customer_id, status);

输出示例：
Query OK, 0 rows affected (0.08 sec)

验证覆盖索引：
mysql> EXPLAIN SELECT status, COUNT(*) FROM orders
WHERE customer_id = 100 GROUP BY status;

输出示例：
+—-+————-+——–+————+——+————————–+
| id | select_type | table | partitions | type | possible_keys |
+—-+————-+——–+————+——+————————–+
| 1 | SIMPLE | orders | NULL | ref | idx_customer_status_cover|
+—-+————-+——–+————+——+————————–+
| Extra: Using index |
+—————————————————————–+

5. 索引命名规范

命名规范：
– 主键索引：PRIMARY
– 唯一索引：uk_列名
– 普通索引：idx_列名
– 复合索引：idx_列名1_列名2
– 函数索引：idx_func_列名

示例：
mysql> ALTER TABLE orders
ADD PRIMARY KEY (id),
ADD UNIQUE KEY uk_order_no (order_no),
ADD INDEX idx_customer_created (customer_id, created_at);

输出示例：
Query OK, 0 rows affected (0.15 sec)

1. 索引统计信息

更新统计信息：
mysql> ANALYZE TABLE orders;

输出示例：
+——————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————-+———+———-+———-+
| production_db.orders | analyze | status | OK |
+——————-+———+———-+———-+

查看索引基数：
mysql> SHOW INDEX FROM orders;

输出示例：
+——–+————+——————-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Cardinality |
+——–+————+——————-+————–+————-+
| orders | 0 | PRIMARY | 1 | 1000000 |
| orders | 1 | idx_status | 1 | 10 |
| orders | 1 | idx_customer | 1 | 100000 |
+——–+————+——————-+————–+————-+

2. 索引选择机制

MySQL选择索引考虑因素：
– 索引基数（Cardinality）
– 查询条件选择性
– 索引覆盖情况
– 排序需求

查看优化器选择：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’ AND customer_id = 100;

输出示例：
+—-+————-+——–+————+——+———————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+———————+————-+
| 1 | SIMPLE | orders | NULL | ref | idx_status,idx_cust | idx_customer|
+—-+————-+——–+————+——+———————+————-+

3. 索引提示

强制使用索引：
mysql> SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = ‘pending’;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+———+——–+———————+

建议使用索引：
mysql> SELECT * FROM orders USE INDEX (idx_status)
WHERE status = ‘pending’;

忽略索引：
mysql> SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = ‘pending’;

4. 索引合并

查看索引合并：
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ OR customer_id = 100;

输出示例：
+—-+————-+——–+————+————-+———————+
| id | select_type | table | partitions | type | possible_keys |
+—-+————-+——–+————+————-+———————+
| 1 | SIMPLE | orders | NULL | index_merge | idx_status,idx_cust |
+—-+————-+——–+————+————-+———————+
| Extra: Using union(idx_status,idx_customer) |
+—————————————————————–+

索引合并类型：
– Using union：OR条件
– Using sort_union：OR条件排序
– Using intersection：AND条件

5. 优化器开关

控制索引合并：
mysql> SET SESSION optimizer_switch=’index_merge=on,index_merge_union=on’;

输出示例：
Query OK, 0 rows affected (0.00 sec)

查看当前设置：
mysql> SELECT @@optimizer_switch;

输出示例：
+———————————————+
| @@optimizer_switch |
+———————————————+
| index_merge=on,…,index_merge_union=on,… |
+———————————————+

1. 索引碎片整理

查看表碎片：
mysql> SELECT TABLE_NAME,
DATA_LENGTH / 1024 / 1024 AS data_mb,
INDEX_LENGTH / 1024 / 1024 AS index_mb,
DATA_FREE / 1024 / 1024 AS free_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘production_db’;

输出示例：
+————+———-+———-+———-+
| TABLE_NAME | data_mb | index_mb | free_mb |
+————+———-+———-+———-+
| orders | 512.00 | 256.00 | 128.00 |
+————+———-+———-+———-+

整理碎片：
mysql> OPTIMIZE TABLE orders;

输出示例：
+——————-+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————-+———-+———-+———-+
| production_db.orders | optimize | status | OK |
+——————-+———-+———-+———-+

在线重建表：
mysql> ALTER TABLE orders ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (30.12 sec)

2. 索引统计更新

手动更新统计：
mysql> ANALYZE TABLE orders;

输出示例：
+——————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————-+———+———-+———-+
| production_db.orders | analyze | status | OK |
+——————-+———+———-+———-+

自动更新配置：
mysql> SHOW VARIABLES LIKE ‘innodb_stats%’;

输出示例：
+————————————–+————-+
| Variable_name | Value |
+————————————–+————-+
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_transient_sample_pages | 8 |
+————————————–+————-+

3. 索引使用监控

查看索引使用情况：
mysql> SELECT * FROM sys.schema_index_statistics
WHERE table_schema = ‘production_db’;

输出示例：
+————+————+———–+—————+————+
| table_name | index_name | rows_read | rows_inserted | rows_updated|
+————+————+———–+—————+————+
| orders | PRIMARY | 1234567 | 123456 | 12345 |
| orders | idx_status | 234567 | 0 | 0 |
+————+————+———–+—————+————+

查找未使用索引：
mysql> SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = ‘production_db’;

输出示例：
+—————+————-+————+
| object_schema | object_name | index_name |
+—————+————-+————+
| production_db | orders | idx_old |
+—————+————-+————+

4. 索引删除

删除未使用索引：
mysql> DROP INDEX idx_old ON orders;

输出示例：
Query OK, 0 rows affected (0.05 sec)

检查索引依赖：
mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘production_db’ AND TABLE_NAME = ‘orders’;

输出示例：
+—————+————+————+————–+————-+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME |
+—————+————+————+————–+————-+
| production_db | orders | PRIMARY | 1 | id |
+—————+————+————+————–+————-+

5. 索引重建

重建索引：
mysql> ALTER TABLE orders DROP INDEX idx_status;
mysql> ALTER TABLE orders ADD INDEX idx_status (status);

输出示例：
Query OK, 0 rows affected (0.10 sec)

在线添加索引（MySQL 8.0+）：
mysql> ALTER TABLE orders ADD INDEX idx_new (column_name), ALGORITHM=INPLACE, LOCK=NONE;

输出示例：
Query OK, 0 rows affected (5.12 sec)

1. 创建前评估

评估步骤：
1. 分析查询模式
2. 评估索引收益
3. 评估索引成本
4. 制定创建计划

评估脚本：
#!/bin/bash
TABLE=$1
COLUMN=$2

mysql -u root -p -e ”
SELECT
COUNT(DISTINCT $COLUMN) AS distinct_values,
COUNT(*) AS total_rows,
COUNT(DISTINCT $COLUMN) / COUNT(*) AS selectivity
FROM $TABLE;
”

执行评估：
./evaluate_index.sh orders status

输出示例：
+—————-+————+————-+
| distinct_values| total_rows | selectivity |
+—————-+————+————-+
| 10 | 1000000 | 0.00001|
+—————-+————+————-+

2. 创建索引

在线创建索引：
mysql> ALTER TABLE orders
ADD INDEX idx_status_created (status, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;

输出示例：
Query OK, 0 rows affected (15.23 sec)

使用pt-online-schema-change：
pt-online-schema-change \
–alter “ADD INDEX idx_status (status)” \
D=production_db,t=orders \
–execute

输出示例：
Successfully altered `production_db`.`orders`.

3. 创建后验证

验证索引创建：
mysql> SHOW INDEX FROM orders WHERE Key_name = ‘idx_status_created’;

输出示例：
+——–+————+——————-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——–+————+——————-+————–+————-+
| orders | 1 | idx_status_created| 1 | status |
| orders | 1 | idx_status_created| 2 | created_at |
+——–+————+——————-+————–+————-+

验证索引使用：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;

输出示例：
+—-+————-+——–+————+——+——————-+
| id | select_type | table | partitions | type | possible_keys |
+—-+————-+——–+————+——+——————-+
| 1 | SIMPLE | orders | NULL | ref | idx_status_created|
+—-+————-+——–+————+——+——————-+

4. 性能对比

对比执行时间：
mysql> SELECT COUNT(*) FROM orders WHERE status = ‘pending’;

输出示例：
+———-+
| COUNT(*) |
+———-+
| 100000 |
+———-+
1 row in set (0.05 sec) — 创建索引后

对比执行计划：
mysql> EXPLAIN ANALYZE SELECT * FROM orders WHERE status = ‘pending’;

输出示例：
-> Index lookup on orders using idx_status (status=’pending’)
(cost=100.50 rows=100000) (actual time=0.100..50.000 rows=100000 loops=1)

5. 文档记录

记录索引变更：
CREATE TABLE index_change_log (
id INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(100),
index_name VARCHAR(100),
action VARCHAR(20),
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入记录：
mysql> INSERT INTO index_change_log (table_name, index_name, action, reason)
VALUES (‘orders’, ‘idx_status_created’, ‘CREATE’, ‘优化订单状态查询’);

输出示例：
Query OK, 1 row affected (0.01 sec)

1. 监控指标

关键监控指标：
– 索引使用频率
– 索引命中率
– 索引大小
– 索引碎片率

监控脚本：
vim /opt/mysql/scripts/index_monitor.sh

脚本内容：
#!/bin/bash
mysql -u monitor -p -N -e ”
SELECT
TABLE_NAME,
INDEX_NAME,
ROWS_READ,
ROWS_FETCHED
FROM sys.schema_index_statistics
WHERE table_schema = ‘production_db’
ORDER BY ROWS_READ DESC;
” > /var/log/mysql/index_usage_$(date +%Y%m%d).txt

设置定时任务：
crontab -e

添加内容：
0 * * * * /opt/mysql/scripts/index_monitor.sh

输出示例：
crontab: installing new crontab

2. 索引使用分析

分析索引使用率：
mysql> SELECT
TABLE_NAME,
INDEX_NAME,
ROWS_READ,
ROWS_FETCHED,
ROUND(ROWS_FETCHED / NULLIF(ROWS_READ, 0) * 100, 2) AS hit_rate
FROM sys.schema_index_statistics
WHERE table_schema = ‘production_db’;

输出示例：
+————+————+———–+—————+———-+
| TABLE_NAME | INDEX_NAME | ROWS_READ | ROWS_FETCHED | hit_rate |
+————+————+———–+—————+———-+
| orders | PRIMARY | 1234567 | 1234567 | 100.00 |
| orders | idx_status | 234567 | 234567 | 100.00 |
+————+————+———–+—————+———-+

3. 未使用索引检测

检测未使用索引：
mysql> SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = ‘production_db’;

输出示例：
+—————+————-+————+
| object_schema | object_name | index_name |
+—————+————-+————+
| production_db | orders | idx_old |
| production_db | users | idx_test |
+—————+————-+————+

冗余索引检测：
mysql> SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = ‘production_db’;

输出示例：
*************************** 1. row ***************************
table_schema: production_db
table_name: orders
redundant_index_name: idx_a
redundant_index_columns: a
dominant_index_name: idx_a_b
dominant_index_columns: a, b

4. 索引大小监控

监控索引大小：
mysql> SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = ‘production_db’
AND STAT_NAME = ‘size’
ORDER BY STAT_VALUE DESC;

输出示例：
+————+————+———-+
| TABLE_NAME | INDEX_NAME | size_mb |
+————+————+———-+
| orders | PRIMARY | 512.00 |
| orders | idx_status | 128.00 |
+————+————+———-+

5. 告警配置

配置索引告警：
vim /opt/mysql/scripts/index_alert.sh

脚本内容：
#!/bin/bash

# 检查未使用索引
UNUSED=$(mysql -u monitor -p -N -e ”
SELECT COUNT(*) FROM sys.schema_unused_indexes
WHERE object_schema = ‘production_db’
“)

if [ $UNUSED -gt 0 ]; then
echo “发现 $UNUSED 个未使用索引” | mail -s “MySQL索引告警” dba@company.com
fi

输出示例：
Alert check completed.

1. 问题索引识别

识别问题索引：
mysql> SELECT
TABLE_NAME,
INDEX_NAME,
CASE
WHEN ROWS_READ = 0 THEN ‘未使用’
WHEN ROWS_READ < 100 THEN '低使用' ELSE '正常' END AS status FROM sys.schema_index_statistics WHERE table_schema = 'production_db'; 输出示例： +------------+------------+----------+ | TABLE_NAME | INDEX_NAME | status | +------------+------------+----------+ | orders | PRIMARY | 正常 | | orders | idx_old | 未使用 | | orders | idx_test | 低使用 | +------------+------------+----------+ 2. 优化方案制定 优化方案类型： - 删除未使用索引 - 合并冗余索引 - 调整索引顺序 - 添加缺失索引 生成优化建议： mysql> SELECT
CONCAT(‘DROP INDEX ‘, index_name, ‘ ON ‘, table_name, ‘;’) AS drop_sql
FROM sys.schema_unused_indexes
WHERE object_schema = ‘production_db’;

输出示例：
+——————————————+
| drop_sql |
+——————————————+
| DROP INDEX idx_old ON orders; |
+——————————————+

3. 优化实施

删除未使用索引：
mysql> DROP INDEX idx_old ON orders;

输出示例：
Query OK, 0 rows affected (0.05 sec)

合并冗余索引：
mysql> DROP INDEX idx_a ON orders;
mysql> CREATE INDEX idx_a_b ON orders(a, b);

输出示例：
Query OK, 0 rows affected (0.10 sec)

4. 效果验证

验证优化效果：
mysql> SELECT
TABLE_NAME,
COUNT(*) AS index_count,
SUM(STAT_VALUE * @@innodb_page_size / 1024 / 1024) AS total_size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = ‘production_db’
AND STAT_NAME = ‘size’
GROUP BY TABLE_NAME;

输出示例：
+————+————-+—————+
| TABLE_NAME | index_count | total_size_mb |
+————+————-+—————+
| orders | 3 | 640.00 |
+————+————-+—————+

5. 持续优化

建立优化流程：
1. 每周检查索引使用情况
2. 每月分析慢查询
3. 每季度评估索引效果
4. 持续监控和优化

定期优化脚本：
vim /opt/mysql/scripts/index_weekly_check.sh

脚本内容：
#!/bin/bash
DATE=$(date +%Y%m%d)
REPORT=”/var/log/mysql/index_report_$DATE.txt”

echo “=== MySQL索引周报 ===” > $REPORT
echo “日期: $(date)” >> $REPORT
echo “” >> $REPORT

echo “=== 未使用索引 ===” >> $REPORT
mysql -u monitor -p -N -e ”
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = ‘production_db’
” >> $REPORT

echo “” >> $REPORT
echo “=== 冗余索引 ===” >> $REPORT
mysql -u monitor -p -N -e ”
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = ‘production_db’
” >> $REPORT

mail -s “MySQL索引周报” dba@company.com < $REPORT 输出示例： Weekly report sent.

# 案例：订单状态查询优化

# 问题描述：
# 订单状态查询响应慢

# 步骤1：分析查询
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;

# 输出示例：
# +—-+————-+——–+————+——+—————+——+
# | id | select_type | table | partitions | type | possible_keys | key |
# +—-+————-+——–+————+——+—————+——+
# | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
# +—-+————-+——–+————+——+—————+——+
# | rows: 1000000 |
# +—————————————————————–+

# 问题分析：
# – type=ALL表示全表扫描
# – 扫描100万行

# 步骤2：评估索引选择性
mysql> SELECT
COUNT(DISTINCT status) AS distinct_values,
COUNT(*) AS total_rows,
COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;

# 输出示例：
# +——————+————+————-+
# | distinct_values | total_rows | selectivity |
# +——————+————+————-+
# | 10 | 1000000 | 0.00001|
# +——————+————+————-+

# 选择性低，但查询频繁，仍建议创建索引

# 步骤3：创建索引
mysql> CREATE INDEX idx_status ON orders(status);

# 输出示例：
# Query OK, 0 rows affected (5.23 sec)

# 步骤4：验证优化效果
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;

# 输出示例：
# +—-+————-+——–+————+——+—————+————+
# | id | select_type | table | partitions | type | possible_keys | key |
# +—-+————-+——–+————+——+—————+————+
# | 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status |
# +—-+————-+——–+————+——+—————+————+
# | rows: 100000 |
# +—————————————————————–+

# 性能提升：扫描行数从100万降到10万

# 案例：订单查询优化

# 问题描述：
# 多条件查询性能差

# 查询SQL：
mysql> SELECT * FROM orders
WHERE customer_id = 100
AND status = ‘pending’
ORDER BY created_at DESC
LIMIT 10;

# 执行时间：5秒

# 步骤1：分析执行计划
mysql> EXPLAIN SELECT … \G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: orders
# type: ALL
# possible_keys: NULL
# key: NULL
# rows: 1000000
# filtered: 1.00
# Extra: Using where; Using filesort

# 问题分析：
# – 全表扫描
# – 使用filesort排序

# 步骤2：设计复合索引
# 分析查询条件：
# – customer_id: 等值条件，选择性高
# – status: 等值条件，选择性低
# – created_at: 排序列

# 索引设计原则：
# 1. 等值条件列放前面
# 2. 排序列放后面
# 3. 高选择性列优先

# 建议索引：idx_customer_status_created(customer_id, status, created_at)

# 步骤3：创建复合索引
mysql> CREATE INDEX idx_customer_status_created
ON orders(customer_id, status, created_at);

# 输出示例：
# Query OK, 0 rows affected (10.45 sec)

# 步骤4：验证优化效果
mysql> EXPLAIN SELECT * FROM orders
WHERE customer_id = 100
AND status = ‘pending’
ORDER BY created_at DESC
LIMIT 10\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: orders
# type: ref
# possible_keys: idx_customer_status_created
# key: idx_customer_status_created
# rows: 100
# filtered: 100.00
# Extra: Using index condition; Backward index scan

# 执行时间：0.01秒

# 性能提升：从5秒降到0.01秒，提升500倍

# 案例1：全文索引优化

# 问题描述：
# 商品名称模糊搜索慢

# 原始查询：
mysql> SELECT * FROM products WHERE name LIKE ‘%手机%’;

# 输出示例：
# +—-+—————-+——–+
# | id | name | price |
# +—-+—————-+——–+
# | 1 | 智能手机A | 2999.00|
# +—-+—————-+——–+
# 执行时间：10秒

# 问题分析：
# – 前导通配符导致索引失效
# – 全表扫描

# 解决方案：使用全文索引
mysql> CREATE FULLTEXT INDEX idx_name_ft ON products(name);

# 输出示例：
# Query OK, 0 rows affected (2.34 sec)

# 优化查询：
mysql> SELECT * FROM products
WHERE MATCH(name) AGAINST(‘手机’ IN NATURAL LANGUAGE MODE);

# 输出示例：
# +—-+—————-+——–+
# | id | name | price |
# +—-+—————-+——–+
# | 1 | 智能手机A | 2999.00|
# +—-+—————-+——–+
# 执行时间：0.01秒

# 案例2：函数索引优化

# 问题描述：
# 按日期查询时索引失效

# 原始查询：
mysql> SELECT * FROM orders WHERE DATE(created_at) = ‘2026-04-01’;

# 问题分析：
# – DATE函数导致索引失效

# 解决方案：使用函数索引（MySQL 8.0+）
mysql> CREATE INDEX idx_date_created ON orders((DATE(created_at)));

# 输出示例：
# Query OK, 0 rows affected (3.45 sec)

# 验证优化：
mysql> EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = ‘2026-04-01’;

# 输出示例：
# +—-+————-+——–+————+——+——————+
# | id | select_type | table | partitions | type | possible_keys |
# +—-+————-+——–+————+——+——————+
# | 1 | SIMPLE | orders | NULL | ref | idx_date_created |
# +—-+————-+——–+————+——+——————+

# 案例3：前缀索引优化

# 问题描述：
# URL列索引太大

# 原始索引：
mysql> CREATE INDEX idx_url ON pages(url);

# 问题分析：
# – URL很长，索引占用空间大

# 解决方案：使用前缀索引
mysql> DROP INDEX idx_url ON pages;
mysql> CREATE INDEX idx_url_prefix ON pages(url(50));

# 输出示例：
# Query OK, 0 rows affected (1.23 sec)

# 验证选择性：
mysql> SELECT
COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) AS prefix_selectivity,
COUNT(DISTINCT url) / COUNT(*) AS full_selectivity
FROM pages;

# 输出示例：
# +——————+—————-+
# | prefix_selectivity| full_selectivity|
# +——————+—————-+
# | 0.95 | 0.99 |
# +——————+—————-+

# 前缀选择性接近完整索引，可以使用

1. 索引设计原则

– 为高频查询创建索引
– 选择高选择性列
– 遵循最左前缀原则
– 控制索引数量
– 避免冗余索引

2. 索引创建原则

– 在低峰期创建索引
– 使用在线DDL
– 监控创建进度
– 验证创建效果

3. 索引维护原则

– 定期更新统计信息
– 定期整理碎片
– 监控索引使用
– 清理未使用索引

4. 索引监控原则

– 监控索引使用率
– 监控索引大小
– 监控索引命中率
– 设置告警阈值

5. 索引文档原则

– 记录索引变更
– 记录优化原因
– 记录优化效果
– 建立索引知识库

1. 避免过度索引

反模式：
– 为每个列创建索引
– 创建过多复合索引
– 索引占用空间过大

正确做法：
– 只为需要的列创建索引
– 合理设计复合索引
– 控制索引总大小

2. 避免索引失效

反模式：
WHERE YEAR(created_at) = 2026
WHERE name LIKE ‘%keyword%’
WHERE id + 1 = 100
WHERE status != ‘active’

正确做法：
WHERE created_at >= ‘2026-01-01’ AND created_at < '2027-01-01' WHERE name LIKE 'keyword%' WHERE id = 99 WHERE status IN ('pending', 'processing') 3. 避免冗余索引 反模式： INDEX idx_a (a) INDEX idx_a_b (a, b) -- idx_a冗余 正确做法： 只保留idx_a_b 4. 避免低选择性索引 反模式： 为性别、状态等低选择性列单独建索引 正确做法： 与其他列组合成复合索引 5. 避免忽视索引维护 反模式： 创建索引后不维护 正确做法： 定期分析、优化、更新统计信息

1. 设计阶段检查

[ ] 是否分析了查询模式
[ ] 是否评估了索引选择性
[ ] 是否遵循了最左前缀原则
[ ] 是否避免了冗余索引
[ ] 是否控制了索引数量

2. 创建阶段检查

[ ] 是否选择了合适的创建时间
[ ] 是否使用了在线DDL
[ ] 是否监控了创建进度
[ ] 是否验证了创建成功

3. 使用阶段检查

[ ] 是否验证了索引使用
[ ] 是否测试了查询性能
[ ] 是否检查了执行计划
[ ] 是否对比了优化效果

4. 维护阶段检查

[ ] 是否定期更新统计信息
[ ] 是否定期检查索引碎片
[ ] 是否监控索引使用情况
[ ] 是否清理未使用索引

5. 监控阶段检查

[ ] 是否配置了索引监控
[ ] 是否设置了告警阈值
[ ] 是否定期生成报告
[ ] 是否建立了优化流程