讲解MySQL聚集索引与非聚集索引的区别:
-
聚集索引(Clustered Index): 聚集索引决定了表中数据行的物理存储顺序。在InnoDB存储引擎中,主键索引默认就是聚集索引。每个表最多只能有一个聚集索引。聚集索引的叶子节点直接包含行数据,这意味着当你通过聚集索引查找数据时,可以直接定位到数据所在的页。
-
非聚集索引(Non-clustered Index / Secondary Index): 非聚集索引并不影响数据行的物理存储顺序,它的叶子节点存储的是指向对应行数据的指针(对于InnoDB是主键值)。创建非聚集索引意味着除了数据本身之外,还有额外的空间来存储索引结构。非聚集索引可以有多个,并且可以基于除主键以外的其他列建立。
对话举例:
熊二:光头强,你知道数据库里的那个“聚集索引”吗?我听城里人说,这东西跟图书馆里按书名排序的目录似的!
光头强:嘿,那我懂了!就像咱村图书馆,按照书名首字母排序的就是聚集索引,想找《熊出没全集》,一翻目录就知道在哪排着。
熊二:对啊,那非聚集索引呢?
光头强:非聚集索引嘛,就像是按作者姓氏排序的那个目录,你找到光头强写的书,但只知道它在哪个区,还要再根据书名目录去找具体的书架位置。
创建一个表并说明聚集索引和非聚集索引如何表示:
-- 创建一个用户表 users 并指定主键 id 为聚集索引
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY, -- 聚集索引,数据行按照id的顺序存放
username VARCHAR(50),
email VARCHAR(255),
created_at TIMESTAMP,
INDEX idx_username (username) -- 非聚集索引,以username字段创建二级索引
);
-- 描述:
-- 在这个例子中,`id` 列被定义为主键,因此 InnoDB 存储引擎会自动将其作为聚集索引。
-- 当我们查询 `users` 表时,如果查询条件是 `id`,那么可以直接定位到数据页。
-- 同时,我们创建了一个名为 `idx_username` 的非聚集索引,当执行如 `SELECT * FROM users WHERE username = '熊二'` 的查询时,
-- 先在 `idx_username` 索引中查找到对应的用户名,然后通过索引叶子节点中的主键值,回表到聚集索引中找到实际的数据行。
所以,在这个例子里,如果你要找特定用户名的用户记录,先使用非聚集索引idx_username
快速定位到用户名,然后再根据找到的主键值去聚集索引中检索完整的用户记录。