印刻数据库

外观

MySQL 索引 ​

索引是提高MySQL查询性能的关键，合理的索引设计和使用直接影响到数据库的查询速度和响应时间。本文将详细介绍MySQL索引的类型、设计原则、优化方法和生产环境最佳实践。

索引概述 ​

什么是索引 ​

索引是一种数据结构，用于快速查找数据库表中的特定记录。它类似于书籍的目录，可以帮助数据库系统快速定位到需要的数据，而无需扫描整个表。

索引的作用 ​

  • 提高查询速度：通过索引可以快速定位到需要的数据，减少磁盘I/O操作
  • 加速排序和分组：索引可以帮助优化ORDER BY和GROUP BY操作
  • 加速连接操作：索引可以加速表之间的连接操作
  • 保证数据唯一性：唯一索引可以确保数据的唯一性

索引的代价 ​

  • 增加存储开销：索引需要占用额外的存储空间
  • 降低写操作性能：插入、更新和删除操作需要同时更新索引，增加了写操作的开销
  • 增加维护成本：索引需要定期维护，如重建、优化等

索引类型 ​

按数据结构分类 ​

B-Tree 索引 ​

B-Tree索引是MySQL最常用的索引类型，基于平衡树数据结构：

  • 适用场景：范围查询、排序、分组
  • 支持的数据类型：数值类型、字符串类型、日期和时间类型
  • 存储引擎支持：InnoDB、MyISAM、Memory等

Hash 索引 ​

Hash索引基于哈希表数据结构：

  • 适用场景：等值查询
  • 支持的数据类型：所有数据类型
  • 存储引擎支持：Memory（默认）、InnoDB（自适应哈希索引）
  • 限制：不支持范围查询、排序、分组

Full-Text 索引 ​

Full-Text索引用于全文搜索：

  • 适用场景：文本内容的全文搜索
  • 支持的数据类型：CHAR、VARCHAR、TEXT类型
  • 存储引擎支持：InnoDB（MySQL 5.6+）、MyISAM

Spatial 索引 ​

Spatial索引用于空间数据查询：

  • 适用场景：地理空间数据查询
  • 支持的数据类型：空间数据类型，如GEOMETRY、POINT、LINESTRING、POLYGON等
  • 存储引擎支持：InnoDB、MyISAM

按物理存储分类 ​

聚集索引 ​

聚集索引是按照表的主键顺序存储数据的索引：

  • 每个表只能有一个聚集索引
  • 数据行的物理顺序与索引顺序一致
  • InnoDB存储引擎使用主键作为聚集索引
  • 如果没有主键，InnoDB会使用唯一索引或生成隐藏的行ID作为聚集索引

非聚集索引 ​

非聚集索引是独立于数据行的索引结构：

  • 每个表可以有多个非聚集索引
  • 索引的叶节点存储的是数据行的指针或主键值
  • MyISAM存储引擎的所有索引都是非聚集索引
  • InnoDB的辅助索引存储的是主键值

按功能分类 ​

主键索引 ​

主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行：

  • 每个表只能有一个主键索引
  • 主键值不能为空（NOT NULL）
  • InnoDB使用主键作为聚集索引

唯一索引 ​

唯一索引确保索引列的值是唯一的：

  • 每个表可以有多个唯一索引
  • 唯一索引列的值可以为空（NULL），但最多只能有一个NULL值
  • 可以通过UNIQUE关键字创建

普通索引 ​

普通索引是最基本的索引，没有唯一性限制：

  • 每个表可以有多个普通索引
  • 可以通过INDEX或KEY关键字创建

前缀索引 ​

前缀索引是对列的前缀部分创建的索引：

  • 适用于长字符串列
  • 可以减少索引的存储空间
  • 可以提高索引的查询效率
  • 通过指定列的前缀长度创建

多列索引 ​

多列索引是对多个列组合创建的索引：

  • 也称为复合索引或联合索引
  • 索引的顺序很重要，遵循最左前缀原则
  • 适用于多列条件的查询

索引设计原则 ​

最左前缀原则 ​

多列索引遵循最左前缀原则，即查询条件中使用了索引的最左N个列，索引就会被使用：

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

-- 可以使用索引的查询
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name = 'John' AND age = 30;
SELECT * FROM users WHERE name LIKE 'J%';

-- 无法使用索引的查询
SELECT * FROM users WHERE age = 30;
SELECT * FROM users WHERE age = 30 AND name = 'John'; -- 虽然结果正确，但索引使用效率低

选择合适的索引列 ​

  • 选择查询频繁的列：为经常出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列创建索引
  • 选择高选择性的列：索引列的选择性越高，索引的效果越好
  • 避免在低选择性列上创建索引：如性别、状态等只有少数几个值的列
  • 避免在大文本列上创建全文索引：除非确实需要全文搜索

索引数量控制 ​

  • 避免过多索引：每个表的索引数量不宜过多，建议不超过5个
  • 权衡查询和写入性能：索引可以提高查询性能，但会降低写入性能
  • 定期清理无用索引：删除不使用或很少使用的索引

数据类型选择 ​

  • 选择合适的数据类型：索引列的数据类型应尽可能小
  • 避免使用TEXT、BLOB等大类型作为索引列：除非使用前缀索引
  • 使用相同的数据类型连接表：确保连接列的数据类型相同，以便使用索引

索引创建与管理 ​

创建索引 ​

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

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

-- 创建主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

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

-- 创建前缀索引
CREATE INDEX idx_username ON users (username(10));

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

-- 在表创建时创建索引
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  age INT,
  INDEX idx_name (name),
  INDEX idx_name_age (name, age)
) ENGINE=InnoDB;

查看索引 ​

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

-- 查看表的索引创建语句
SHOW CREATE TABLE users;

-- 查看索引的使用情况
SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

修改索引 ​

sql
-- 重命名索引
ALTER TABLE users RENAME INDEX idx_old_name TO idx_new_name;

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

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

-- 添加新索引
ALTER TABLE users ADD INDEX idx_new_column (new_column);

索引维护 ​

sql
-- 优化索引
OPTIMIZE TABLE users;

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 检查索引碎片
SHOW TABLE STATUS LIKE 'users';

-- 使用pt-online-schema-change在线修改表结构和索引（Percona Toolkit）
pt-online-schema-change --alter "ADD INDEX idx_new_column (new_column)" D=database,t=table --user=root --password=password --execute

索引优化 ​

识别低效索引 ​

  • 使用慢查询日志：识别导致慢查询的索引问题
  • 使用EXPLAIN分析：分析查询执行计划，查看索引使用情况
  • 使用sys schema：MySQL 5.7+提供了sys schema，可以查看索引使用情况
  • 使用Performance Schema：监控索引的使用情况

优化索引的方法 ​

  • 添加缺失的索引：为频繁查询的列添加索引
  • 删除无用的索引：删除不使用或很少使用的索引
  • 合并重复的索引：合并功能相同或相似的索引
  • 调整索引顺序：根据查询频率调整多列索引的顺序
  • 使用前缀索引：对长字符串列使用前缀索引
  • 使用覆盖索引：查询只使用索引中的列，不需要回表查询

避免索引失效 ​

以下情况可能导致索引失效：

  • 使用函数或表达式：在索引列上使用函数或表达式
  • 使用不等于操作符：如!=、<>等
  • 使用IS NULL或IS NOT NULL：特别是在非空列上
  • 使用LIKE以通配符开头：如LIKE '%abc'
  • 使用OR连接条件：除非所有条件都有索引
  • 数据类型不匹配：查询条件的数据类型与索引列的数据类型不匹配
  • 索引列上的隐式转换：如字符串列与数值比较
sql
-- 索引失效的情况
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 函数使用
SELECT * FROM users WHERE age != 30; -- 不等于操作符
SELECT * FROM users WHERE name LIKE '%John'; -- 通配符开头
SELECT * FROM users WHERE name = 123; -- 隐式转换

-- 优化后的查询
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM users WHERE age > 30 OR age < 30;
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE name = '123';

版本差异处理 ​

MySQL 5.6 索引特性 ​

  • InnoDB支持全文索引：从MySQL 5.6开始，InnoDB支持全文索引
  • 索引扫描优化：优化了索引扫描的性能
  • 限制：不支持降序索引、不可见索引等特性

MySQL 5.7 索引特性 ​

  • 虚拟生成列索引：支持在虚拟生成列上创建索引
  • JSON数据类型索引：支持在JSON数据类型上创建索引
  • 空间索引增强：增强了空间索引的支持
  • sys schema：提供了sys schema，便于查看索引使用情况

MySQL 8.0 索引特性 ​

  • 降序索引：支持真正的降序索引，提高ORDER BY DESC查询的性能
  • 不可见索引：支持创建不可见索引，可以测试索引效果而不影响生产环境
  • 隐藏主键：支持隐藏主键，不显示在SELECT *结果中
  • 函数索引：支持在函数或表达式上创建索引
  • 索引跳跃扫描：优化了多列索引的使用，支持跳过某些索引前缀

生产环境最佳实践 ​

索引设计最佳实践 ​

  • 遵循最左前缀原则：合理设计多列索引的顺序
  • 优先使用唯一索引：唯一索引的性能优于普通索引
  • 使用覆盖索引：减少回表查询，提高查询性能
  • 避免过度索引：每个表的索引数量不宜过多
  • 定期审查索引：使用pt-index-usage等工具分析索引使用情况

索引使用最佳实践 ​

  • 避免在索引列上使用函数：会导致索引失效
  • 使用EXPLAIN分析查询：确保查询使用了合适的索引
  • 优化慢查询：及时发现并优化导致索引失效的慢查询
  • 监控索引使用情况：使用Zabbix、Prometheus等工具监控索引使用情况

索引维护最佳实践 ​

  • 定期优化索引：使用OPTIMIZE TABLE命令优化索引
  • 定期重建索引：对于频繁更新的表，定期重建索引
  • 监控索引碎片：及时发现并处理索引碎片问题
  • 使用pt-online-schema-change：在线修改表结构和索引，减少对业务的影响

性能优化最佳实践 ​

  • 使用连接池：减少连接开销
  • 优化查询语句：避免全表扫描，使用适当的索引
  • 分区表：对于大表，考虑使用分区表
  • 读写分离：将读操作和写操作分离，提高系统性能

索引性能分析 ​

使用EXPLAIN分析索引 ​

EXPLAIN命令可以帮助分析查询的执行计划，查看索引的使用情况：

sql
-- 基本使用
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 查看详细信息
EXPLAIN EXTENDED SELECT * FROM users WHERE name = 'John';
SHOW WARNINGS;

-- 查看JSON格式的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John';

EXPLAIN输出解读 ​

  • id：查询的序列号，表示查询中操作的顺序
  • select_type：查询的类型，如SIMPLE、PRIMARY、SUBQUERY等
  • table：查询的表名
  • partitions：查询涉及的分区
  • type：访问类型，从好到坏依次是：system > const > eq_ref > ref > range > index > ALL
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • key_len：使用的索引长度
  • ref：索引的哪一列或常数被用于查找索引列的值
  • rows：估计要读取的行数
  • filtered：按表条件过滤的行百分比
  • Extra：额外的信息，如Using index、Using where、Using filesort等

索引使用情况监控 ​

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

-- Handler_read_first：索引中第一条记录的读取次数
-- Handler_read_key：根据索引读取一行的请求数
-- Handler_read_last：索引中最后一条记录的读取次数
-- Handler_read_next：根据索引读取下一行的请求数
-- Handler_read_prev：根据索引读取上一行的请求数
-- Handler_read_rnd：根据固定位置读取一行的请求数
-- Handler_read_rnd_next：从数据文件中读取下一行的请求数

-- 查看InnoDB索引使用情况
SHOW ENGINE INNODB STATUS LIKE 'TRANSACTIONS';

-- 使用sys schema查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_index_statistics;

结论 ​

索引是提高MySQL查询性能的关键，合理的索引设计和使用可以显著提高数据库的查询速度和响应时间。通过了解索引的类型、设计原则、优化方法和生产环境最佳实践，可以创建高效的索引，提高数据库的性能和可维护性。

在实际生产环境中，应该根据业务需求和查询模式设计合适的索引，并定期监控和维护索引，确保索引的有效性和高效性。同时，应该根据MySQL版本的特性，选择合适的索引类型和优化方法，充分利用数据库系统的性能优势。