数据库优化 - 索引

索引的工作原理

提示

想象一下，如果我们要在一本没有目录的书中查找某个主题，我们需要从头到尾翻阅整本书。这就像是在没有索引的数据库表中进行全表扫描。而有了索引，就像有了目录，我们可以直接翻到相关页面，大大节省了查找时间。

常见的索引类型

1. 主键索引（Primary Key）

主键索引是表中最基本的索引类型，它要求索引列的值必须唯一且不能为空。每个表只能有一个主键索引。主键索引适用于：

表的主键字段
需要唯一标识的字段
经常用于关联查询的字段

2. 唯一索引（Unique Index）

唯一索引要求索引列的值必须唯一，但允许有空值。这种索引常用于确保数据的唯一性。唯一索引适用于：

用户邮箱
手机号码
身份证号
其他需要唯一性的业务字段

3. 普通索引（Normal Index）

普通索引是最基本的索引类型，它没有任何限制，可以包含重复值和空值。普通索引适用于：

经常用于查询条件的列
经常用于排序的列
经常用于连接的列
区分度高的列

4. 空间索引（Spatial Index）

空间索引是专门用于处理地理空间数据的索引类型。它主要用于存储和查询具有地理位置信息的数据，如点、线、面等几何对象。MySQL 使用 R-tree 数据结构来实现空间索引。

使用空间索引时，需要使用空间数据类型（如 GEOMETRY、POINT、LINESTRING、POLYGON 等）和空间函数（如 ST_Distance、ST_Contains 等）。

5. 全文索引（Fulltext Index）

全文索引是一种特殊的索引类型，专门用于全文搜索。它能够对文本内容进行分词，并建立索引，支持自然语言搜索。全文索引适用于：

文章内容搜索
产品描述搜索
评论内容搜索
需要模糊匹配的场景

注意：全文索引只能用于 CHAR、VARCHAR 和 TEXT 类型的列，且只支持 InnoDB 和 MyISAM 存储引擎。

索引的使用

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL|NORMAL] INDEX index_name ON table_name (column1, column2, ...);

其中，UNIQUE 表示创建唯一索引,FULLTEXT 表示创建全文索引,SPATIAL 表示创建空间索引,NORMAL 表示创建普通索引, index_name 是索引的名称,table_name 是要创建索引的表名,(column1, column2, ...) 是要创建索引的列名。

查看索引

SHOW INDEX FROM table_name;

hsv

字段名	说明	注释
Table	表名	显示索引所属的表名
Non_unique	是否唯一	0 表示唯一索引，1 表示非唯一索引
Key_name	索引名称	显示索引的名称，PRIMARY 表示主键索引
Seq_in_index	索引中的列序号	表示索引中的列顺序，从 1 开始
Column_name	列名	索引包含的列名
Collation	排序方式	A 表示升序，NULL 表示无排序
Cardinality	基数	索引中唯一值的数量估计值，用于优化器选择索引
Sub_part	索引前缀长度	如果只索引列的前缀，则显示前缀长度，NULL 表示索引整个列
Packed	是否压缩	表示索引是否被压缩，NULL 表示未压缩
Null	是否允许 NULL	YES 表示允许 NULL 值，NO 表示不允许
Index_type	索引类型	显示索引的类型，如 BTREE、HASH 等
Comment	注释	索引的注释信息
Index_comment	索引注释	创建索引时添加的注释
Visible	索引是否可见	YES 表示索引可见，NO 表示索引不可见（MySQL 8.0 新增特性）
Expression	表达式索引	显示用于创建索引的表达式（MySQL 8.0 新增特性）

删除索引

索引的注意事项

索引会占用额外的存储空间
索引会降低写入性能（INSERT、UPDATE、DELETE 操作）
过多的索引会增加数据库维护成本
小表不需要建立索引
频繁更新的列不适合建立索引

实际案例分析

假设我们有一个用户表，包含以下字段：id、username、email、phone、create_time。根据不同的查询需求，我们可以这样设计索引：

主键索引：id（自动创建）
唯一索引：phone（确保手机号唯一）

CREATE UNIQUE INDEX phone_index ON sys_user(phone)

普通索引：username（用于用户名查询）

CREATE INDEX username_index ON sys_user(username)

复合索引：(phone, create_time)（用于按手机号和创建时间查询）

CREATE INDEX phone_create_time_index ON sys_user(phone,create_time)

这样的索引设计既保证了查询效率，又避免了过度索引带来的性能问题。

总结

索引是数据库优化的重要手段，合理使用索引可以显著提高查询性能。但索引不是万能的，需要根据实际业务场景和查询需求来设计。在创建索引时，要权衡查询性能和写入性能，找到最适合的平衡点。