MySQL 聊聊索引

最新推荐文章于 2025-05-19 14:36:36 发布

原创最新推荐文章于 2025-05-19 14:36:36 发布 · 254 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库同时被 2 个专栏收录

11 篇文章

订阅专栏

MySQL

9 篇文章

订阅专栏

索引

索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

可以简单理解为"排好序的快速查找数据结构"。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

优劣

优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

基本语法

# 创建索引
CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
如果是CHAR,VARCHAR类型，length可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定length。

ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

# 删除索引
DROP INDEX [indexName] ON mytable;

# 查看索引
SHOW INDEX FROM table_name\G

# 添加索引的四种方式

# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL.
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
# 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
# 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_ name ADD INDEX index_name (column_list)
# 该语句指定了索引为FULLTEXT, 用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

BTree索引

Btree索引(或Balanced Tree)，是一种很普遍的数据库索引结构。其特点是定位高效、利用率高、自我平衡，特别适用于高基数字段，定位单条或小范围数据非常高效。理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。

数据结构利用率高、定位高效

结构看起来Btree索引与Binary Tree相似，但在细节上有所不同，上图中用不同颜色的标示出了Btree索引的几个主要特点：

树形结构：由根节(root)、分支(branches)、叶(leaves)三级节点组成，其中分支节点可以有多层。多分支结构：与binary tree不相同的是，btree索引中单root/branch可以有多个子节点(超过2个)。双向链表：整个叶子节点部分是一个双向链表(后面会描述这个设计的作用) 单个数据块中包括多条索引记录。

结构上Btree与Binary Tree的区别，在于binary中每节点代表一个数值，而balanced中root和Btree节点中记录了多条”值范围”条目(如:[60-70][70-80])，这些”值范围”条目分别指向在其范围内的叶子节点。既root与branch可以有多个分支，而不一定是两个，对数据块的利用率更高。

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
在高并发下倾向创建组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表
Where条件里用不到的字段不创建索引
频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引，加重IO负担
数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。