印刻数据库

外观

TiDB 索引优化 ​

索引是提高数据库查询性能的重要手段，通过创建合适的索引，可以加速数据的查找和过滤。

TiDB 索引类型 ​

1. 主键索引 ​

TiDB 使用聚簇索引（Clustered Index）作为主键索引，表数据按照主键的顺序存储。

sql
-- 创建表时指定主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 修改表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

2. 唯一索引 ​

唯一索引确保索引列的值唯一，可以为空值（最多一个空值）。

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

-- 创建表时指定唯一索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

3. 普通索引 ​

普通索引是最基本的索引类型，用于加速数据的查找和过滤。

sql
-- 创建普通索引
CREATE INDEX idx_users_name ON users (name);

-- 创建表时指定普通索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_users_name (name)
);

4. 联合索引 ​

联合索引是由多个列组成的索引，用于加速多列查询。

sql
-- 创建联合索引
CREATE INDEX idx_users_name_email ON users (name, email);

-- 创建表时指定联合索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_users_name_email (name, email)
);

5. 覆盖索引 ​

覆盖索引包含查询所需的所有列，查询时不需要回表，可以提高查询性能。

sql
-- 示例：查询 name 和 email，使用覆盖索引
CREATE INDEX idx_users_name_email ON users (name, email);

-- 查询可以使用覆盖索引，不需要回表
SELECT name, email FROM users WHERE name = 'John';

6. 前缀索引 ​

前缀索引只使用列值的前缀创建索引，适用于长字符串列，可以减少索引大小。

sql
-- 创建前缀索引
CREATE INDEX idx_users_email_prefix ON users (email(20));

索引创建和管理 ​

1. 创建索引 ​

sql
-- 语法：CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ...);

-- 创建普通索引
CREATE INDEX idx_users_name ON users (name);

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

-- 创建联合索引
CREATE INDEX idx_users_name_email ON users (name, email);

-- 创建前缀索引
CREATE INDEX idx_users_email_prefix ON users (email(20));

2. 查看索引 ​

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

-- 查看表的索引信息
DESC users;

3. 修改索引 ​

sql
-- 修改索引名
ALTER TABLE users RENAME INDEX idx_old_name TO idx_new_name;

4. 删除索引 ​

sql
-- 语法：DROP INDEX index_name ON table_name;

-- 删除索引
DROP INDEX idx_users_name ON users;

索引优化原则 ​

1. 选择合适的索引列 ​

  • 选择性高的列：选择区分度高的列作为索引，如身份证号、邮箱等
  • 经常用于查询条件的列：WHERE 子句中频繁使用的列
  • 经常用于排序和分组的列：ORDER BY、GROUP BY 子句中使用的列
  • 经常用于连接的列：JOIN 子句中使用的列

2. 联合索引的顺序 ​

  • 将选择性高的列放在前面
  • 将经常用于查询条件的列放在前面
  • 考虑查询的覆盖性

3. 避免过度索引 ​

  • 索引会占用磁盘空间
  • 索引会增加写入操作的开销
  • 索引会增加查询优化器的负担
  • 定期清理无用的索引

4. 使用覆盖索引 ​

  • 尽量使用覆盖索引，减少回表操作
  • 对于频繁查询的列组合，考虑创建覆盖索引

5. 避免索引失效 ​

  • 避免在索引列上使用函数或表达式
  • 避免在索引列上进行类型转换
  • 避免使用 NOT IN、!=、<> 等操作符
  • 避免使用 LIKE '%xxx' 前缀模糊查询
  • 联合索引中，避免跳过前面的列进行查询

索引优化最佳实践 ​

1. 分析查询语句 ​

使用 EXPLAIN 或 EXPLAIN ANALYZE 分析查询语句的执行计划，确定是否使用了索引，以及索引的使用情况。

sql
-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 分析查询执行计划并显示实际执行情况
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'John';

2. 优化慢查询 ​

  • 识别慢查询语句
  • 分析慢查询的执行计划
  • 根据分析结果创建或修改索引
  • 重新测试查询性能

3. 定期维护索引 ​

  • 重建索引：当索引出现碎片时，重建索引可以提高查询性能
  • 收集统计信息：定期收集表的统计信息，帮助查询优化器生成更好的执行计划
  • 删除无用索引：删除不使用或很少使用的索引
sql
-- 重建索引
ALTER TABLE users REBUILD INDEX idx_users_name;

-- 收集统计信息
ANALYZE TABLE users;

4. 考虑数据分布 ​

  • 对于热点数据，考虑使用分区表或其他方式分散热点
  • 对于大表，考虑使用分库分表
  • 考虑数据的访问模式，调整索引策略

5. 测试索引效果 ​

  • 在测试环境中测试索引的效果
  • 比较索引创建前后的查询性能
  • 考虑索引对写入性能的影响
  • 综合评估索引的收益和成本

常见索引问题及解决方案 ​

问题1：索引不被使用 ​

原因：

  • 查询条件不匹配索引
  • 索引选择性低
  • 查询优化器认为全表扫描更快
  • 统计信息不准确

解决方案：

  • 调整查询条件，匹配索引
  • 优化索引设计，提高索引选择性
  • 更新统计信息
  • 使用 FORCE INDEX 提示（谨慎使用）

问题2：索引失效 ​

原因：

  • 在索引列上使用了函数或表达式
  • 在索引列上进行了类型转换
  • 使用了不支持索引的操作符
  • 联合索引中跳过了前面的列

解决方案：

  • 避免在索引列上使用函数或表达式
  • 确保查询条件的类型与索引列一致
  • 调整查询条件，使用支持索引的操作符
  • 按照联合索引的顺序使用列

问题3：索引过多 ​

原因：

  • 为每个列都创建了索引
  • 创建了很多不常用的索引
  • 索引维护成本高

解决方案：

  • 分析索引使用情况，删除不常用的索引
  • 合并相似的索引
  • 考虑使用联合索引替代多个单列索引
  • 定期审查索引策略

问题4：索引碎片 ​

原因：

  • 频繁的插入、更新和删除操作
  • 索引页分裂

解决方案：

  • 重建索引
  • 优化表结构，减少碎片产生
  • 定期维护索引

索引优化案例 ​

案例1：单表查询优化 ​

问题：

sql
SELECT * FROM users WHERE name = 'John' AND age > 30;

优化方案：

  • 创建联合索引：CREATE INDEX idx_users_name_age ON users (name, age);
  • 查询可以使用联合索引，提高查询性能

案例2：连接查询优化 ​

问题：

sql
SELECT u.name, o.order_id FROM users u JOIN orders o ON u.id = o.user_id WHERE u.name = 'John';

优化方案：

  • 在 users 表上创建索引：CREATE INDEX idx_users_name ON users (name);
  • 在 orders 表上创建索引：CREATE INDEX idx_orders_user_id ON orders (user_id);
  • 查询可以使用索引加速连接和过滤

案例3：排序查询优化 ​

问题：

sql
SELECT * FROM users WHERE age > 30 ORDER BY name;

优化方案：

  • 创建联合索引：CREATE INDEX idx_users_age_name ON users (age, name);
  • 查询可以使用索引进行过滤和排序，避免额外的排序操作

案例4：覆盖索引优化 ​

问题：

sql
SELECT name, email FROM users WHERE name = 'John';

优化方案：

  • 创建覆盖索引：CREATE INDEX idx_users_name_email ON users (name, email);
  • 查询可以使用覆盖索引，不需要回表，提高查询性能

索引监控和分析 ​

1. 监控索引使用情况 ​

sql
-- 查看索引使用统计信息
SELECT * FROM information_schema.statistics WHERE table_schema = 'your_database' AND table_name = 'your_table';

-- 查看慢查询日志，分析索引使用情况

2. 使用 TiDB 诊断工具 ​

  • TiDB Dashboard：提供索引使用情况的可视化视图
  • TiDB Inspect：分析索引使用情况和性能
  • Prometheus + Grafana：监控索引相关的性能指标

3. 索引使用统计 ​

sql
-- 启用索引使用统计
SET GLOBAL tidb_enable_index_merge = ON;

-- 查看索引使用统计
SELECT * FROM information_schema.tidb_index_usage WHERE table_schema = 'your_database' AND table_name = 'your_table';

常见问题（FAQ） ​

Q1: 如何确定是否需要创建索引？ ​

A1: 可以通过以下方式确定：

  • 分析查询语句的执行计划
  • 监控查询的执行时间
  • 考虑查询的频率和重要性
  • 评估索引的收益和成本

Q2: 如何选择合适的索引列？ ​

A2: 选择合适的索引列需要考虑：

  • 列的选择性：选择区分度高的列
  • 查询条件：经常用于 WHERE 子句的列
  • 排序和分组：经常用于 ORDER BY、GROUP BY 的列
  • 连接条件：经常用于 JOIN 子句的列

Q3: 联合索引的顺序如何确定？ ​

A3: 联合索引的顺序应该：

  • 将选择性高的列放在前面
  • 将经常用于查询条件的列放在前面
  • 考虑查询的覆盖性
  • 考虑排序和分组的需求

Q4: 如何避免索引失效？ ​

A4: 可以通过以下方式避免索引失效：

  • 避免在索引列上使用函数或表达式
  • 确保查询条件的类型与索引列一致
  • 避免使用 NOT IN、!=、<> 等操作符
  • 避免使用 LIKE '%xxx' 前缀模糊查询
  • 按照联合索引的顺序使用列

Q5: 如何处理索引过多的问题？ ​

A5: 处理索引过多的问题可以：

  • 分析索引使用情况，删除不常用的索引
  • 合并相似的索引
  • 考虑使用联合索引替代多个单列索引
  • 定期审查索引策略

Q6: 如何监控索引的使用情况？ ​

A6: 可以通过以下方式监控索引的使用情况：

  • 使用 EXPLAIN 或 EXPLAIN ANALYZE 分析查询执行计划
  • 查看慢查询日志
  • 使用 TiDB Dashboard 查看索引使用情况
  • 启用索引使用统计，查看统计信息