数据库索引操作

1. 索引概述

1.1 什么是索引

索引（Index）是数据库中一种数据结构，它是对数据库表中一列或多列的值进行排序的一种结构。索引就好比一本书的目录，它会让你更快地找到内容。

核心概念：

索引是数据结构，不是数据本身
索引存储在磁盘上，占用存储空间
索引可以显著提高查询速度
索引需要维护，会影响写入性能

1.2 索引的类比理解

书籍目录类比：

没有目录：需要逐页查找，耗时很长
有目录：直接定位到章节，快速找到内容
目录过多：如果一本书1000页，有500页是目录，效率反而低
目录占纸张：索引占磁盘空间

数据库索引同样如此：

索引不是越多越好
索引需要占用磁盘空间
索引需要维护成本
合理使用索引才能提升性能

1.3 索引的工作原理

B-Tree索引结构（InnoDB默认）

          [50]
         /    \
    [25]        [75]
   /   \       /    \
[10]  [30]  [60]  [90]

查找过程：

从根节点开始比较
根据比较结果决定向左或向右查找
在叶子节点找到数据指针
通过指针访问实际数据行

时间复杂度：O(log n)，远优于全表扫描的O(n)

1.4 索引的优势

1. 提高查询速度

避免全表扫描
快速定位数据
支持排序和分组操作

2. 加速表连接

提高JOIN操作效率
优化外键关联查询

3. 保证数据唯一性

唯一索引保证列值唯一
主键索引保证记录唯一

4. 优化排序

索引本身就是有序的
避免临时排序操作

1.5 索引的代价

1. 存储空间

索引需要额外的磁盘空间
索引文件可能比数据文件还大

2. 维护成本

INSERT操作需要更新索引
UPDATE操作可能需要重建索引
DELETE操作需要维护索引结构

3. 写入性能影响

索引越多，写入越慢
需要平衡读写性能

1.6 索引使用原则

何时应该创建索引

频繁作为查询条件的字段
外键关联的字段
经常需要排序的字段
经常需要分组的字段
高选择性字段（唯一值多）

何时不应该创建索引

很少查询的字段
数据量很小的表
频繁更新的字段（需要权衡）
低选择性字段（如性别、状态等）
大文本字段（使用全文索引）

2. 索引分类

2.1 按功能分类

1. 普通索引（INDEX）

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

特点：

最基本的索引类型
没有任何限制
允许重复值和NULL值
最常用的索引类型

适用场景：

经常作为查询条件的字段
需要提高查询性能的列

2. 唯一索引（UNIQUE）

与普通索引类似，但索引列的值必须唯一，允许有空值。

特点：

索引列的值必须唯一
允许NULL值（NULL可以出现多次）
自动创建唯一约束
提高查询性能的同时保证数据唯一性

适用场景：

需要保证唯一性的字段（非主键）
邮箱、用户名等唯一标识字段

3. 全文索引（FULLTEXT）

仅可用于MyISAM表（MySQL 5.6+ InnoDB也支持），针对较大的数据，生成全文索引很耗时和空间。

特点：

用于全文搜索
仅支持CHAR、VARCHAR、TEXT类型
生成索引耗时且占用空间大
MySQL 5.6+ InnoDB支持全文索引

适用场景：

文章内容搜索
产品描述搜索
需要模糊匹配的文本字段

4. 主键索引（PRIMARY KEY）

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

特点：

特殊的唯一索引
不允许NULL值
一个表只能有一个主键
自动创建聚簇索引（InnoDB）

适用场景：

表的主键字段
唯一标识记录

2.2 按数据结构分类

1. B-Tree索引（B+Tree）

MySQL默认的索引类型，适用于大多数场景。

特点：

平衡树结构
支持范围查询
支持排序
InnoDB使用B+Tree

2. Hash索引

基于哈希表实现，只支持等值查询。

特点：

等值查询极快
不支持范围查询
不支持排序
Memory引擎支持

3. 全文索引

基于倒排索引实现，用于全文搜索。

2.3 按列数分类

1. 单列索引

只包含一个列的索引。

1	CREATE INDEX idx_name ON table(name);

2. 多列索引（复合索引）

包含多个列的索引，也称为联合索引。

1	CREATE INDEX idx_name_age ON table(name, age);

最左前缀原则：

索引按照最左列开始匹配
(name, age) 索引可以用于：
- WHERE name = ?
- WHERE name = ? AND age = ?
- 但不能用于 WHERE age = ?

3. 索引环境准备

3.1 准备测试表

创建测试表用于索引性能测试：

-- 创建测试表
mysql> CREATE TABLE t5 (
    ->     id INT,
    ->     name VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构
mysql> DESC t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.2 使用存储过程批量插入数据

为了测试索引效果，需要插入大量数据。使用存储过程可以高效地批量插入数据。

1. 创建存储过程

-- 修改语句结束符
mysql> DELIMITER $$

-- 创建存储过程
mysql> CREATE PROCEDURE autoinsert()
    -> BEGIN
    ->     DECLARE i INT DEFAULT 1;
    ->     WHILE (i < 200000) DO
    ->         INSERT INTO bgx.t5 VALUES(i, 'bgx');
    ->         SET i = i + 1;
    ->     END WHILE;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

-- 恢复语句结束符
mysql> DELIMITER ;

存储过程说明：

DELIMITER $$：修改语句结束符为$$，避免存储过程中的;被误解析
DECLARE i INT DEFAULT 1：声明变量i，初始值为1
WHILE ... DO ... END WHILE：循环结构
SET i = i + 1：变量自增

2. 查看存储过程

-- 查看所有存储过程
mysql> SHOW PROCEDURE STATUS\G

-- 查看存储过程定义
mysql> SHOW CREATE PROCEDURE autoinsert\G
*************************** 1. row ***************************
           Procedure: autoinsert
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i < 200000) DO
INSERT INTO bgx.t5 VALUES(i, 'bgx');
SET i = i + 1;
END WHILE;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

3. 调用存储过程执行

1
2
3

-- 调用存储过程，插入199999条数据
mysql> CALL autoinsert();
Query OK, 1 row affected (2 min 15.32 sec)

执行时间说明：

插入近20万条数据需要一定时间
时间取决于硬件性能
可以通过批量插入优化性能

4. 优化存储过程（批量插入）

-- 优化版本：批量插入
DELIMITER $$
CREATE PROCEDURE autoinsert_optimized()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000;
    
    START TRANSACTION;
    WHILE (i < 200000) DO
        INSERT INTO bgx.t5 VALUES
            (i, 'bgx'),
            (i+1, 'bgx'),
            (i+2, 'bgx'),
            -- ... 批量插入
            (i+999, 'bgx');
        SET i = i + 1000;
        
        -- 每10000条提交一次
        IF i % 10000 = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;
    COMMIT;
END $$
DELIMITER ;

3.3 验证数据

-- 查看数据量
mysql> SELECT COUNT(*) FROM t5;
+----------+
| COUNT(*) |
+----------+
|   199999 |
+----------+
1 row in set (0.05 sec)

-- 查看部分数据
mysql> SELECT * FROM t5 LIMIT 5;
+------+------+
| id   | name |
+------+------+
|    1 | bgx  |
|    2 | bgx  |
|    3 | bgx  |
|    4 | bgx  |
|    5 | bgx  |
+------+------+
5 rows in set (0.00 sec)

4. 索引创建

4.1 创建表时创建索引

基本语法

CREATE TABLE 表名 (
    字段名 数据类型 [完整性约束条件…],
    [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名[(长度)] [ASC | DESC])
);

语法说明：

INDEX 或 KEY：创建索引的关键字
UNIQUE：唯一索引
FULLTEXT：全文索引
SPATIAL：空间索引
索引名：可选，不指定则自动生成
字段名[(长度)]：索引字段，可指定长度
ASC | DESC：排序方向，默认ASC

1. 创建普通索引示例

CREATE TABLE tt (
    id INT,
    name VARCHAR(30),
    comment VARCHAR(50),
    INDEX index_tt_name (name)
);

验证索引：

-- 查看表结构
mysql> SHOW CREATE TABLE tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `comment` varchar(50) DEFAULT NULL,
  KEY `index_tt_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2. 创建唯一索引示例

CREATE TABLE tt (
    id INT,
    name VARCHAR(30),
    comment VARCHAR(50),
    UNIQUE INDEX index_tt_name (name)
);

唯一索引特点：

自动创建唯一约束
插入重复值会报错
允许NULL值（多个NULL值）

3. 创建全文索引示例

-- 注意：InnoDB在MySQL 5.6+支持全文索引
CREATE TABLE tt (
    id INT,
    name VARCHAR(30),
    comment VARCHAR(50),
    log TEXT,
    FULLTEXT INDEX index_tt_log (log)
) ENGINE=InnoDB;

全文索引使用：

1
2
3

-- 全文搜索
SELECT * FROM tt 
WHERE MATCH(log) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

4. 创建多列索引示例

CREATE TABLE tt (
    id INT,
    name VARCHAR(30),
    comment VARCHAR(50),
    INDEX index_tt_name_comment (name, comment)
);

多列索引说明：

按照列的顺序创建索引
遵循最左前缀原则
可以用于多列查询

4.2 在已存在的表上创建索引

基本语法

1 2	CREATE [UNIQUE \| FULLTEXT \| SPATIAL] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC \| DESC]);

1. 创建普通索引示例

1 2	-- 在product表的name字段上创建普通索引 CREATE INDEX index_name ON product(name);

执行示例：

1
2
3

mysql> CREATE INDEX index_name ON product(name);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. 创建唯一索引示例

1 2	-- 在product表的name字段上创建唯一索引 CREATE UNIQUE INDEX index_name ON product(name);

注意事项：

如果表中已有重复值，创建唯一索引会失败
需要先清理重复数据

3. 创建全文索引示例

1 2	-- 在product表的name字段上创建全文索引 CREATE FULLTEXT INDEX index_dept_name ON product(name);

全文索引限制：

仅支持CHAR、VARCHAR、TEXT类型
MyISAM和InnoDB（5.6+）支持
需要指定最小词长度

4. 创建多列索引示例

1 2	-- 在product表的name和id字段上创建多列索引 CREATE INDEX index_dept_name_comment ON product(name, id);

多列索引顺序：

索引列的顺序很重要
常用查询条件放在前面
选择性高的列放在前面

4.3 使用ALTER TABLE创建索引

-- 使用ALTER TABLE添加索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 (字段名);

-- 示例
ALTER TABLE product ADD INDEX index_name (name);
ALTER TABLE product ADD UNIQUE INDEX index_name_unique (name);

4.4 索引创建最佳实践

1. 索引命名规范

-- 推荐命名方式
CREATE INDEX idx_table_column ON table(column);        -- 普通索引
CREATE UNIQUE INDEX uk_table_column ON table(column);  -- 唯一索引
CREATE INDEX idx_table_col1_col2 ON table(col1, col2); -- 多列索引

2. 索引字段选择

-- 推荐：选择性高的字段
CREATE INDEX idx_user_email ON users(email);  -- 邮箱唯一性高

-- 不推荐：选择性低的字段
-- CREATE INDEX idx_user_gender ON users(gender);  -- 性别只有几个值

3. 索引长度优化

-- 对于长字符串，可以只索引前N个字符
CREATE INDEX idx_name_prefix ON users(name(10));

-- 适用于：VARCHAR(255)等长字段
-- 可以节省索引空间

4. 覆盖索引设计

-- 设计覆盖索引，查询只需访问索引
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- 查询可以完全从索引获取数据
SELECT user_id, status, created_at FROM orders WHERE user_id = 1;

5. 索引测试

5.1 未建立索引的查询性能

1. 执行查询并记录时间

-- 查询id为199999的记录
mysql> SELECT * FROM t5 WHERE id = 199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.08 sec)  -- 耗时0.08秒

性能分析：

需要扫描近20万条记录
全表扫描，性能较差
数据量越大，查询越慢

2. 使用EXPLAIN分析执行计划

-- 查看查询优化器如何决定执行查询
mysql> EXPLAIN SELECT * FROM t5 WHERE id = 199999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ALL              -- 全表扫描
possible_keys: NULL             -- 没有可用的索引
          key: NULL              -- 没有使用索引
      key_len: NULL
          ref: NULL
         rows: 199949           -- 需要扫描的行数
     filtered: 10.00
        Extra: Using where      -- 使用WHERE过滤
1 row in set, 1 warning (0.00 sec)

EXPLAIN关键字段说明：

字段	说明	当前值分析
`type`	访问类型	`ALL`表示全表扫描，最差
`possible_keys`	可能使用的索引	`NULL`表示没有可用索引
`key`	实际使用的索引	`NULL`表示未使用索引
`rows`	扫描的行数	`199949`表示扫描近20万行
`Extra`	额外信息	`Using where`表示使用WHERE过滤

5.2 建立索引后的查询性能

1. 创建索引

-- 对id字段创建索引
mysql> CREATE INDEX index_t5_id ON bgx.t5(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引创建时间：

在已有数据上创建索引需要时间
数据量越大，创建时间越长
创建索引期间会锁表（取决于存储引擎）

2. 再次执行查询

-- 索引后查询
mysql> SELECT * FROM t5 WHERE id = 199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.00 sec)  -- 耗时几乎为0，性能提升显著

性能对比：

未建立索引：0.08秒
建立索引后：0.00秒
性能提升：数百倍

3. 使用EXPLAIN分析索引效果

-- 建立索引后，再次查看执行计划
mysql> EXPLAIN SELECT * FROM t5 WHERE id = 200000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ref              -- 使用索引查找
possible_keys: index_t5_id      -- 可能使用的索引
          key: index_t5_id      -- 实际使用的索引
      key_len: 5                -- 索引键长度
          ref: const            -- 使用常量值
         rows: 1                -- 只需要扫描1行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

性能提升分析：

指标	无索引	有索引	提升
查询时间	0.08秒	0.00秒	数百倍
扫描行数	199949行	1行	近20万倍
访问类型	ALL（全表扫描）	ref（索引查找）	质的飞跃

5.3 EXPLAIN执行计划详解

type字段（访问类型，从好到坏）

system：表只有一行
const：通过主键或唯一索引查找，最多一行
eq_ref：唯一性索引扫描
ref：非唯一性索引扫描
range：范围扫描
index：全索引扫描
ALL：全表扫描（最差）

key字段说明

possible_keys：可能使用的索引
key：实际使用的索引
key_len：使用的索引长度

rows字段说明

表示MySQL认为执行查询需要扫描的行数
值越小越好
是估算值，不一定准确

Extra字段常见值

Using index：使用覆盖索引
Using where：使用WHERE过滤
Using filesort：需要额外排序
Using temporary：使用临时表

5.4 索引性能测试最佳实践

1. 使用PROFILING分析

-- 开启性能分析
SET profiling = 1;

-- 执行查询
SELECT * FROM t5 WHERE id = 199999;

-- 查看性能分析结果
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

2. 对比测试

-- 测试不同查询的性能
-- 1. 无索引查询
-- 2. 单列索引查询
-- 3. 多列索引查询
-- 4. 覆盖索引查询

3. 压力测试

1 2	-- 使用工具进行压力测试 -- 如：sysbench, mysqlslap等

6. 索引管理

6.1 查看索引

1. 使用SHOW CREATE TABLE

-- 查看表的创建语句，包括索引
mysql> SHOW CREATE TABLE t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

输出说明：

KEY index_t5_id (id)：显示索引名称和字段
可以查看所有索引定义

2. 使用SHOW INDEX

-- 查看表的所有索引详细信息
mysql> SHOW INDEX FROM t5\G
*************************** 1. row ***************************
        Table: t5
   Non_unique: 1              -- 0=唯一索引, 1=非唯一索引
     Key_name: index_t5_id     -- 索引名称
 Seq_in_index: 1               -- 索引中的列序号
  Column_name: id              -- 列名
    Collation: A               -- 排序方式(A=升序, NULL=无排序)
  Cardinality: 199949         -- 索引基数（唯一值数量）
     Sub_part: NULL            -- 索引前缀长度
       Packed: NULL            -- 是否压缩
         Null: YES             -- 是否允许NULL
   Index_type: BTREE           -- 索引类型
      Comment:                 -- 注释
Index_comment:                 -- 索引注释

关键字段说明：

Non_unique：0表示唯一索引，1表示非唯一索引
Cardinality：索引基数，值越大选择性越高
Index_type：索引类型，通常是BTREE

3. 使用INFORMATION_SCHEMA查询

-- 查询索引信息
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'bgx' 
  AND TABLE_NAME = 't5'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

4. 使用DESC查看索引

-- 查看表结构，Key列显示索引
mysql> DESC t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Key列说明：

PRI：主键索引
UNI：唯一索引
MUL：普通索引（Multiple）

6.2 删除索引

基本语法

1	DROP INDEX 索引名 ON 表名;

删除索引示例

-- 查看索引名称
mysql> SHOW CREATE TABLE t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- 删除索引
mysql> DROP INDEX index_t5_id ON t5;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 验证删除
mysql> SHOW INDEX FROM t5;
Empty set (0.00 sec)

使用ALTER TABLE删除索引

-- 使用ALTER TABLE删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

-- 示例
ALTER TABLE t5 DROP INDEX index_t5_id;

删除主键索引

1 2	-- 删除主键索引（需要先删除自增属性） ALTER TABLE 表名 DROP PRIMARY KEY;

6.3 修改索引

MySQL不支持直接修改索引，需要先删除再创建：

-- 1. 删除旧索引
DROP INDEX old_index_name ON table_name;

-- 2. 创建新索引
CREATE INDEX new_index_name ON table_name(column);

6.4 索引维护

1. 重建索引

-- InnoDB表重建索引
ALTER TABLE table_name ENGINE=InnoDB;

-- 或者
OPTIMIZE TABLE table_name;

2. 分析索引

-- 更新索引统计信息
ANALYZE TABLE table_name;

-- 查看索引统计
SHOW INDEX FROM table_name;

3. 检查索引使用情况

-- 查看索引使用统计（需要开启）
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 = 'bgx'
ORDER BY COUNT_FETCH DESC;

6.5 索引管理最佳实践

1. 定期检查未使用的索引

-- 查找可能未使用的索引
SELECT 
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN (
    SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
    FROM INFORMATION_SCHEMA.STATISTICS
) t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA 
   AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.CARDINALITY = 0  -- 基数为0可能表示索引未使用
   OR s.CARDINALITY IS NULL;

2. 监控索引大小

-- 查看索引占用的空间
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS 'Size(MB)'
FROM mysql.innodb_index_stats
WHERE STAT_NAME = 'size'
  AND DATABASE_NAME = 'bgx'
ORDER BY STAT_VALUE DESC;

3. 索引碎片整理

-- 优化表，整理碎片
OPTIMIZE TABLE table_name;

-- 注意：会锁表，需要在维护窗口执行

7. 索引优化策略

7.1 索引设计原则

1. 最左前缀原则

-- 创建多列索引
CREATE INDEX idx_name_age ON users(name, age);

-- 可以使用索引的查询
SELECT * FROM users WHERE name = 'John';           -- ✓ 可以使用
SELECT * FROM users WHERE name = 'John' AND age = 25; -- ✓ 可以使用

-- 不能使用索引的查询
SELECT * FROM users WHERE age = 25;  -- ✗ 不能使用（违反最左前缀）

2. 选择性原则

-- 高选择性字段优先
CREATE INDEX idx_email ON users(email);  -- 邮箱唯一性高

-- 低选择性字段不建索引
-- CREATE INDEX idx_gender ON users(gender);  -- 性别只有几个值

3. 覆盖索引

-- 设计覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查询只需访问索引，不需要回表
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

7.2 索引使用技巧

1. 避免在索引列上使用函数

-- 不推荐
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 推荐
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. 避免在索引列上使用表达式

-- 不推荐
SELECT * FROM products WHERE price * 2 > 100;

-- 推荐
SELECT * FROM products WHERE price > 50;

3. 使用索引提示

-- 强制使用索引
SELECT * FROM table_name USE INDEX (index_name) WHERE ...;

-- 忽略索引
SELECT * FROM table_name IGNORE INDEX (index_name) WHERE ...;

7.3 索引性能监控

1. 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';

2. 使用EXPLAIN分析

-- 分析查询执行计划
EXPLAIN SELECT * FROM table WHERE ...;

-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM table WHERE ...;

3. 索引使用统计

1 2	-- 查看索引使用情况 SHOW STATUS LIKE 'Handler_read%';

8. 常见索引问题与解决方案

8.1 索引未使用

问题：创建了索引但查询仍然很慢

可能原因：

查询条件不符合最左前缀原则
数据类型不匹配
使用了函数或表达式
索引选择性太低，优化器选择全表扫描

解决方案：

-- 1. 检查查询条件
EXPLAIN SELECT ...;

-- 2. 强制使用索引测试
SELECT * FROM table USE INDEX (index_name) WHERE ...;

-- 3. 分析索引选择性
SHOW INDEX FROM table;

8.2 索引过多导致写入慢

问题：INSERT/UPDATE操作很慢

原因：每个索引都需要维护，索引越多写入越慢

解决方案：

删除不必要的索引
使用批量插入
在维护窗口创建索引
考虑使用延迟索引

8.3 索引碎片

问题：索引占用空间大但效率低

解决方案：

-- 重建索引
OPTIMIZE TABLE table_name;

-- 或重建表
ALTER TABLE table_name ENGINE=InnoDB;

9. 总结

9.1 索引的核心价值

提升查询性能：从全表扫描到索引查找，性能提升数百倍
保证数据完整性：唯一索引和主键索引保证数据唯一性
优化排序和分组：索引本身有序，避免临时排序
加速表连接：外键索引提高JOIN性能

9.2 索引使用原则

适度使用：不是越多越好，需要权衡读写性能
合理设计：遵循最左前缀原则，考虑选择性
定期维护：监控索引使用情况，删除无用索引
性能测试：使用EXPLAIN分析，验证索引效果

9.3 架构师建议

设计阶段：根据查询模式设计索引
开发阶段：使用EXPLAIN验证索引使用
测试阶段：进行性能测试，验证索引效果
生产阶段：监控索引使用，定期优化
文档维护：记录索引设计理由和使用场景

9.4 索引优化检查清单

是否为频繁查询的字段创建了索引
是否遵循最左前缀原则
索引选择性是否足够高
是否使用了覆盖索引
是否定期检查未使用的索引
是否监控索引大小和碎片
是否在维护窗口进行索引维护

相关文章：

第428集数据库索引操作

数据库索引操作

1. 索引概述

1.1 什么是索引

1.2 索引的类比理解

1.3 索引的工作原理

B-Tree索引结构（InnoDB默认）

1.4 索引的优势

1. 提高查询速度

2. 加速表连接

3. 保证数据唯一性

4. 优化排序

1.5 索引的代价

1. 存储空间

2. 维护成本

3. 写入性能影响

1.6 索引使用原则

何时应该创建索引

何时不应该创建索引

2. 索引分类

2.1 按功能分类

1. 普通索引（INDEX）

2. 唯一索引（UNIQUE）

3. 全文索引（FULLTEXT）

4. 主键索引（PRIMARY KEY）

2.2 按数据结构分类

1. B-Tree索引（B+Tree）

2. Hash索引

3. 全文索引

2.3 按列数分类

1. 单列索引

2. 多列索引（复合索引）

3. 索引环境准备

3.1 准备测试表

3.2 使用存储过程批量插入数据

1. 创建存储过程

2. 查看存储过程

3. 调用存储过程执行

4. 优化存储过程（批量插入）

3.3 验证数据

4. 索引创建

4.1 创建表时创建索引

基本语法

1. 创建普通索引示例

2. 创建唯一索引示例

3. 创建全文索引示例

4. 创建多列索引示例

4.2 在已存在的表上创建索引

基本语法

1. 创建普通索引示例

2. 创建唯一索引示例

3. 创建全文索引示例

4. 创建多列索引示例

4.3 使用ALTER TABLE创建索引

4.4 索引创建最佳实践

1. 索引命名规范

2. 索引字段选择

3. 索引长度优化

4. 覆盖索引设计

5. 索引测试

5.1 未建立索引的查询性能

1. 执行查询并记录时间

2. 使用EXPLAIN分析执行计划

5.2 建立索引后的查询性能

1. 创建索引

2. 再次执行查询

3. 使用EXPLAIN分析索引效果

5.3 EXPLAIN执行计划详解

type字段（访问类型，从好到坏）

key字段说明

rows字段说明

Extra字段常见值

5.4 索引性能测试最佳实践

1. 使用PROFILING分析

2. 对比测试

3. 压力测试

6. 索引管理

6.1 查看索引

1. 使用SHOW CREATE TABLE

2. 使用SHOW INDEX