印刻数据库

外观

TDSQL 索引管理 ​

索引类型与特点 ​

1. 主键索引 ​

  • 唯一标识表中的每一行记录
  • 自动创建，一个表只能有一个主键
  • 存储引擎会自动为主键创建B+树索引
  • 主键值不能为空

2. 唯一索引 ​

  • 确保索引列的值唯一
  • 允许有空值（但最多一个）
  • 适合用作业务唯一标识

3. 普通索引 ​

  • 最基本的索引类型
  • 加速数据查询
  • 允许重复值和空值

4. 复合索引 ​

  • 基于多个列创建的索引
  • 遵循最左前缀原则
  • 适合多列查询场景

5. 全文索引 ​

  • 用于全文搜索
  • 支持自然语言搜索和布尔搜索
  • 适合大文本字段查询

6. 空间索引 ​

  • 用于地理空间数据类型
  • 支持空间关系查询
  • 适合地图应用等场景

索引设计原则 ​

1. 选择合适的索引列 ​

  • 经常出现在WHERE子句中的列
  • 用于JOIN操作的列
  • 用于ORDER BY和GROUP BY的列
  • 高选择性的列（唯一值比例高）

2. 复合索引设计 ​

  • 将最常用的列放在前面
  • 遵循最左前缀原则
  • 避免创建过多复合索引
  • 考虑索引覆盖查询

3. 避免过度索引 ​

  • 索引会占用存储空间
  • 降低写操作性能
  • 增加维护成本
  • 建议每个表的索引数量不超过5个

4. 索引命名规范 ​

  • 主键索引：pk_表名
  • 唯一索引：uk_表名_列名
  • 普通索引：idx_表名_列名
  • 复合索引：idx_表名_列1_列2

索引创建与管理 ​

1. 索引创建 ​

创建表时创建索引 ​

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(11),
    created_at DATETIME,
    INDEX idx_name (name),
    INDEX idx_created_at (created_at),
    INDEX idx_name_created_at (name, created_at)
);

为现有表创建索引 ​

sql
-- 创建普通索引
CREATE INDEX idx_phone ON users(phone);

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

-- 创建复合索引
CREATE INDEX idx_name_phone ON users(name, phone);

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

2. 索引查询 ​

sql
-- 查看表的索引信息
SHOW INDEX FROM users;

-- 查看表的索引信息（详细）
SHOW CREATE TABLE users;

-- 查询索引使用情况
SELECT * FROM information_schema.statistics WHERE table_schema = 'database_name' AND table_name = 'users';

3. 索引修改与删除 ​

sql
-- 修改索引（先删除后重建）
DROP INDEX idx_phone ON users;
CREATE INDEX idx_phone ON users(phone);

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

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

索引使用与优化 ​

1. 索引使用规则 ​

索引有效场景 ​

  • WHERE子句中使用索引列进行精确匹配
  • JOIN操作中使用索引列
  • ORDER BY和GROUP BY使用索引列
  • 覆盖索引查询

索引失效场景 ​

  • 使用!=或<>运算符
  • 使用IS NULL或IS NOT NULL
  • 使用LIKE '%xxx'（前缀模糊查询）
  • 使用函数或表达式操作索引列
  • 类型转换导致索引失效
  • 不遵循最左前缀原则

2. 索引优化建议 ​

1. 使用EXPLAIN分析索引使用 ​

sql
-- 分析查询计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 分析查询计划（详细）
EXPLAIN EXTENDED SELECT * FROM users WHERE name = '张三';

2. 索引覆盖查询 ​

  • 只查询索引包含的列
  • 避免回表操作
  • 提高查询性能
sql
-- 覆盖索引查询示例
SELECT id, name FROM users WHERE name = '张三';
-- 假设idx_name是name列的索引，该查询会使用覆盖索引

3. 前缀索引优化 ​

  • 对长字符串列使用前缀索引
  • 减少索引存储空间
  • 提高索引效率
sql
-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(20));

4. 定期重建索引 ​

  • 解决索引碎片问题
  • 提高索引查询效率
  • 释放存储空间
sql
-- 重建表（同时重建索引）
ALTER TABLE users ENGINE = INNODB;

-- 重建特定索引
DROP INDEX idx_name ON users;
CREATE INDEX idx_name ON users(name);

索引监控与维护 ​

1. 索引使用监控 ​

查看索引使用统计 ​

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

-- 查看索引使用情况
SELECT * FROM information_schema.index_statistics WHERE table_schema = 'database_name' AND table_name = 'users';

-- 查看索引命中率
SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';

2. 索引碎片管理 ​

查看表碎片情况 ​

sql
-- 查看表碎片
SELECT table_name, data_free, engine FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'users';

优化表（清理碎片） ​

sql
-- 优化表
OPTIMIZE TABLE users;

-- 或使用ALTER TABLE重建表
ALTER TABLE users ENGINE = INNODB;

3. 冗余索引识别 ​

识别冗余索引 ​

sql
-- 使用pt-duplicate-key-checker工具识别冗余索引
-- 或使用以下SQL查询可能的冗余索引
SELECT 
    t1.table_schema,
    t1.table_name,
    t1.index_name AS redundant_index,
    t2.index_name AS covering_index
FROM 
    information_schema.statistics t1
JOIN 
    information_schema.statistics t2 ON t1.table_schema = t2.table_schema 
    AND t1.table_name = t2.table_name 
    AND t1.index_name != t2.index_name
WHERE 
    t1.table_schema = 'database_name' 
    AND t1.seq_in_index = 1
    AND t2.seq_in_index = 1
    AND t1.column_name = t2.column_name;

常见问题（FAQ） ​

Q1: 如何判断是否需要创建索引？ ​

A1: 判断是否需要创建索引可以参考以下原则：

  • 表的数据量较大（通常超过1万行）
  • 查询频率较高的列
  • 查询条件中经常使用的列
  • 用于连接操作的列
  • 用于排序和分组的列

Q2: 复合索引的最左前缀原则是什么？ ​

A2: 最左前缀原则是指在复合索引中，只有当查询条件包含索引的最左列时，索引才会被使用。例如，对于复合索引idx_name_age(name, age)，查询条件WHERE name = '张三'可以使用索引，而WHERE age = 20则无法使用该索引。

Q3: 为什么有时候索引没有被使用？ ​

A3: 索引没有被使用可能有以下原因：

  • 查询条件不满足索引使用规则
  • 表的数据量太小，全表扫描更快
  • 索引选择性太低
  • 统计信息不准确
  • 发生了类型转换

Q4: 如何优化慢查询中的索引使用？ ​

A4: 优化慢查询中的索引使用可以采取以下措施：

  • 使用EXPLAIN分析查询计划
  • 检查索引是否被正确使用
  • 调整查询语句，使其符合索引使用规则
  • 考虑创建新的索引或修改现有索引
  • 优化索引覆盖查询

Q5: 索引越多越好吗？ ​

A5: 不是。索引越多会带来以下问题：

  • 占用更多的存储空间
  • 降低INSERT、UPDATE、DELETE操作的性能
  • 增加索引维护成本
  • 可能导致优化器选择错误的索引

Q6: 如何维护索引？ ​

A6: 索引维护包括以下方面：

  • 定期分析索引使用情况
  • 清理冗余索引
  • 重建碎片化严重的索引
  • 根据业务变化调整索引
  • 监控索引性能指标

Q7: 全文索引和普通索引有什么区别？ ​

A7: 全文索引和普通索引的主要区别：

  • 适用场景：全文索引适合大文本字段的全文搜索，普通索引适合精确匹配和范围查询
  • 搜索方式：全文索引支持自然语言搜索和布尔搜索，普通索引支持精确匹配和前缀匹配
  • 存储结构：全文索引使用倒排索引，普通索引通常使用B+树结构
  • 性能特点：全文索引在大文本搜索时性能更好，普通索引在精确匹配时性能更好