在数据库管理和优化中,索引是一个非常重要的概念。MySQL作为一种流行的关系型数据库管理系统,其索引机制对于提高查询速度、优化数据检索性能具有至关重要的作用。
目录
一、哪些字段适合建立索引
1. 高频查询字段
如果一个字段在查询中经常被用作过滤条件,那么在这个字段上创建索引可能会显著提高查询性能。例如,在电商网站的商品表中,商品名称(product_name
)和价格(price
)等字段经常作为查询条件,因此适合在这些字段上建立索引。
2. 唯一性字段
如果表中的某个字段包含唯一值(如用户ID、身份证号等),那么在这些字段上创建索引不仅可以提高查询性能,还可以防止插入重复的数据。唯一索引不仅可以保证数据的唯一性,还能通过索引快速定位到唯一的数据行。
3. 经常参与连接的字段
在表与表之间的连接查询中,经常参与连接的字段应该建立索引。这样可以加快连接查询的速度,尤其是在处理大型数据库时,索引的作用尤为明显。
4. 经常用于排序和分组的字段
如果某个字段经常用于ORDER BY
或GROUP BY
子句,那么在这些字段上建立索引可以加速排序和分组操作。
5. 小字段
索引本身也需要占用磁盘空间,因此在选择索引字段时,应尽量选择数据类型小的列作为索引。这样可以减少索引的大小,提高查询速度,并节省磁盘空间。
二、Mysql索引主要使用的两种数据结构
1. B-树索引
B-树索引是最常用的索引类型,大部分MySQL存储引擎(如InnoDB)都支持B-树索引。B-树索引通过树形数据结构来存储索引值和数据行的对应关系,可以快速定位到数据行。B-树索引支持全键值、键值范围和键值前缀查询,也可以对查询结果进行排序。
2. 哈希索引
哈希索引通过哈希算法将索引列的值映射为哈希值,然后根据哈希值来定位数据行。MySQL的MEMORY和HEAP存储引擎支持哈希索引。哈希索引的查询速度非常快,但只能进行等值比较,不支持范围查询和排序。
三、索引类型
主键索引
数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键,并且主键不能为 null,不能重复。在 mysql 的 InnoDB 的表中,当没有显示的指定表的主键时,InnoDB 会自动先检查表中是否有唯一索引的字段,如果有,则选择该字段为默认的主键,否则 InnoDB 将会自动创建一6Byte 的自增主键。
辅助索引
二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
聚簇索引
索引直接与数据在一起的就是聚簇索引,主键索引是聚簇索引
聚集索引的优点
- 聚集索引的查询速度非常的快,因为整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。
聚集索引的缺点
-
依赖于有序的数据 :因为 B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢。
-
更新代价大 : 如果对索引列的数据被修改时,那么对应的索引也将会被修改, 而且况聚集索引的叶子节点还存放着数据,修改代价肯定是较大的, 所以对于主键索引来说,主键一般都是不可被修改的。
非聚簇索引
不是聚簇索引的都是非聚簇索引
非聚集索引的优点
- 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的
非聚集索引的缺点
-
跟聚集索引一样,非聚集索引也依赖于有序的数据
-
可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。
回表:如果返回的字段超出了非聚集索引的范围,因为非聚集索引只存储该索引字段的值和主键,这个时候就要再去回表查询