浅谈MySQL聚簇索引

目录
  • 1. 什么是聚簇索引
  • 2. 聚簇索引和主键
  • 3. 聚簇索引优缺点
  • 4. 最佳实践

1. 什么是聚簇索引

数据库的索引从不同的角度可以划分成不同的类型，聚簇索引便是其中一种。

聚簇索引英文是 Clustered Index，有时候小伙伴们可能也会看到有人将之称为聚集索引等，与之相对的是非聚簇索引或者二级索引。

聚簇索引并不是一种单独的索引类型，而是一种数据的存储方式。在 MySQL 的 InnoDB 存储引擎中，所谓的聚簇索引实际上就是在同一个 B+Tree 中保存了索引和数据行：此时，数据放在叶子结点中，聚簇聚簇，意思就是说数据行和对应的键值紧凑的存在一起。

假设我有如下数据：

id(主键) username age address gender
1 ab 99 深圳 男
2 ac 98 广州 男
3 af 88 北京 女
4 bc 80 上海 女
5 bg 85 重庆 女
6 bw 95 天津 男
7 bw 99 海口 女
8 cc 92 武汉 男
9 ck 90 深圳 男
10 cx 93 深圳 男

那么它的聚簇索引大概就是这个样子：

那么大家可以看到，叶子上既有主键值（索引）又有数据行，节点上则只有主键值（索引）。

小伙伴们想想，MySQL 表中的数据在磁盘中只可能保存一份，不可能保存两份，所以，在一个表中，聚簇索引只可能有一个，不可能有多个。

2. 聚簇索引和主键

有的小伙伴搞不清楚这两者之间的关系，甚至将两者划等号，这是一个巨大的误区。

在有的数据库中，支持开发者自由的选择使用哪一个索引作为聚簇索引，但是 MySQL 中是不支持这个特性的。

在 MySQL 中，如果表本身就有设置主键，那么主键就是聚簇索引；如果表本身没有设置主键，则会选择表中的一个唯一且非空的索引来作为聚簇索引；如果表中连唯一非空的索引都没有，那么就会自动选择表中的隐式主键来作为聚簇索引。关于 MySQL 中表的隐式主键，松哥会在将来的文章中和大家介绍。

不过一般来说，还是建议大家自己来为表设置主键，因为隐式主键是自增的，自增的都会存在一个问题：在自增值上会存在非常高的锁竞争问题，主键的上界会称为热点数据，因为所有的插入操作都要主键自增，又不能重复，所以会发生锁竞争进而导致性能降低。

根据上面的介绍，我们可以总结出 MySQL 中聚簇索引和主键索引的关系如下：

  • 聚簇索引不一定是主键索引。
  • 主键索引一定是聚簇索引。

3. 聚簇索引优缺点

先来说优点：

  • 相互关联的数据我们可以将之保存在一起。例如有一个用户订单表，我们可以根据 用户 ID + 订单 ID 来聚集所有数据，用户 ID 可能会重复，订单 ID 则不会重复，这样我们就能够将一个用户相关的订单数据都保存在一起，如果需要查询一个用户的所有订单，就会非常快，只需要少量的磁盘 IO 就可以做到。
  • 不需要回表，因此数据访问速度更快。在聚簇索引中，索引和数据都在同一棵 B+Tree 上，因此从聚簇索引中获取到的数据比从非聚簇索引上获取数据更快（非聚簇索引需要回表）。
  • 对于第一点的案例，如果我们想根据用户 ID 查询到这个用户所有的订单 ID，那么此时都不用去到叶子结点了，因为支节点上就有我们需要的数据，所以直接利用覆盖索引的特性，就可以读取到需要的数据。

这些就是聚簇索引一些常见的优点，我们在日常的表设计中，其实应该充分利用好这些优点。

再来看看缺点：

  • 小伙伴们发现，前面我们说的聚簇索引的优势主要是聚簇索引减少了 IO 次数，从而提高了数据库的性能，但是有的 IO 密集型应用，可能直接上一个足够大的内存，把数据都读取到内存中操作，此时聚簇索引就没有啥优势了。
  • 随机主键会导致页分裂问题，主键顺序插入的话，相对来说效率会高一些，因为在 B+Tree 中只需要不断往后面追加即可；但是主键如果是非顺序插入的话，效率就会低很多，因为可能会涉及到页分裂问题。以上面那张图为例，假设每个节点可以保存三条数据，现在我们要插入一个主键是 4.5 的记录，那么就需要把主键为 5 的值往后移动，进而导致主键为 8 的节点也要往后移动。页分裂会导致数据插入效率降低并且占用更多的存储空间。
  • 非聚簇索引（二级索引）查询的时候需要回表。因为一个索引就是一棵索引树，数据都在聚簇索引上，所以如果使用非聚簇索引进行搜索，非聚簇索引的叶子上存储的是主键值，先找到主键值，然后拿着主键值再来聚簇索引上搜索，这样一共就查询了两棵索引树，这就是回表。

4. 最佳实践

看了上面的介绍，相信小伙伴已经了解了，在使用聚簇索引的时候，主键最好不要使用 UUID 这种随机字符串，使用 UUID 随机字符串至少存在两方面的问题：

  • 插入效率低，因为插入可能会导致页分裂，这个前面已经说过了。
  • UUID 字符串所占用的存储空间远远大于一个 bigint，如果使用 UUID 来做主键，意味着在二级索引中，一个叶子结点能够存储的主键值就非常有限，进而可能会导致树增高，搜索时候 IO 次数增多，性能下降。

所以相对来说，主键自增会优于 UUID。那么主键自增就是最完美的方案了吗？很多小伙伴可能也听说过一句话：没有银弹！所以，主键自增其实也有问题，具体什么问题，我们下便文章继续。

到此这篇关于浅谈MySQL聚簇索引的文章就介绍到这了,更多相关MySQL聚簇索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL学习教程之聚簇索引

    聚簇,其实是相对于InnoDB这个数据库引擎来说的,因此在将聚簇索引的时候,我们通过InnoDB和MyISAM这两个MySQL的数据库引擎展开. InnoDB和MyISAM的数据分布对比 CREATE TABLE test (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2)); 首先通过以上SQL语句创建出一个表格,其中col1是主键,两列数据均创建了索引.然后我们数据的主键取值为1-10000,按照随机的顺序插

  • 详解MySQL 聚簇索引与非聚簇索引

    1.聚集索引 表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致.对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页. 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种. 从物理文件也可以看出 InnoDB(聚集索引)的数据文件只有数据结构文件.frm和数据文件.idb 其中.idb中存放的是数据和索引信息 是存放在一起的. 2.非聚集索引 表数据存储顺序与索引顺序无关.对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,

  • mysql聚簇索引的页分裂原理实例分析

    mysql聚簇索引的页分裂原理实例分析

    本文实例讲述了mysql聚簇索引的页分裂.分享给大家供大家参考,具体如下: 在MySQL中,MyISAM采用的是非聚簇索引的,InnoDB存储引擎是采用聚簇索引的. 聚簇结构的特点: 根据主键查询条目时,不用回行(数据就在主键节点下) 如果碰到不规则数据插入时,造成频繁的页分裂 为什么会产生页分裂? 这是因为聚簇索引采用的是平衡二叉树算法,而且每个节点都保存了该主键所对应行的数据,假设插入数据的主键是自增长的,那么根据二叉树算法会很快的把该数据添加到某个节点下,而其他的节点不用动:但是如果插入的

  • MySQL聚簇索引和非聚簇索引的区别详情

    MySQL聚簇索引和非聚簇索引的区别详情

    目录 聚簇索引 非聚簇索引 总结 前言: 在 MySQL 默认引擎 InnoDB 中,索引大致可分为两类:聚簇索引和非聚簇索引,它们的区别也是常见的面试题,所以我们今天就来盘它们. 聚簇索引 聚簇索引(Clustered Index)一般指的是主键索引(如果存在主键索引的话),聚簇索引也被称之为聚集索引. 聚簇索引在 InnoDB 中是使用 B+ 树实现的,比如我们创建一张 student 表,它的构建 SQL 如下: drop table if exists student; create t

  • MySQL之MyISAM存储引擎的非聚簇索引详解

    MySQL之MyISAM存储引擎的非聚簇索引详解

    在InnoDB中索引即数据,也就是聚簇索引的那颗B+树的叶子节点中已经包含了所有完整的用户记录.MyISAM的索引方案虽然也是使用树形结构,但是却将索引和数据分开存储,这种索引也叫非聚簇索引. create table index_demo( c1 int, c2 int, c3 char(1), primary key(c1) ) ROW_FORMAT=COMPACT; 将表中的记录按照记录的插入顺序单独存储在一个文件中,这个文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少个记录,这

  • 浅谈MySQL聚簇索引

    目录 1. 什么是聚簇索引 2. 聚簇索引和主键 3. 聚簇索引优缺点 4. 最佳实践 1. 什么是聚簇索引 数据库的索引从不同的角度可以划分成不同的类型,聚簇索引便是其中一种. 聚簇索引英文是 Clustered Index,有时候小伙伴们可能也会看到有人将之称为聚集索引等,与之相对的是非聚簇索引或者二级索引. 聚簇索引并不是一种单独的索引类型,而是一种数据的存储方式.在 MySQL 的 InnoDB 存储引擎中,所谓的聚簇索引实际上就是在同一个 B+Tree 中保存了索引和数据行:此时,数据

  • 浅谈MySQL之浅入深出页原理

    浅谈MySQL之浅入深出页原理

    一.页的概览 我们往 MySQL 插入的数据最终都是存在页中的.在 InnoDB 中的设计中,页与页之间是通过一个双向链表连接起来. 而存储在页中的一行一行的数据则是通过单链表连接起来的. 上图中的 User Records 的区域就是用来存储行数据的.那 InnoDB 为什么要这么设计?假设我们没有页这个概念,那么当我们查询时,成千上万的数据要如何做到快速的查询出结果?众所周知,MySQL 的性能是不错的,而如果没有页,我们剩下的只能是逐条逐条的遍历数据了. 那页是如何做到快速查询的呢?在当前

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

  • 浅谈mysql可有类似oracle的nvl的函数

    要用ifnull,而不是isnull isnull是判断是否为null,返回值是1表示null或者0表示不为空 ifnull等同于oracle的nvl,用法如下 mysql> select ifnull(1,10); +--------------+ | ifnull(1,10) | +--------------+ |            1 | +--------------+ 1 row in set (0.00 sec) mysql> select ifnull(null,10);

  • 浅谈mysql 针对单张表的备份与还原

    A.MySQL 备份工具xtrabackup 的安装 1. percona 官方xtrabackup 的二进制版本:二进制版本解压就能用了. 2. 解压xtrabackup & 创建连接 tar -xzvf percona-xtrabackup-2.3.4-Linux-x86_64.tar.gz -C /usr/local/ ln -s /usr/local/percona-xtrabackup-2.3.4 /usr/local/xtrabackup 3. 设置PATH环境变量 export P

  • 浅谈MySQL和Lucene索引的对比分析

    浅谈MySQL和Lucene索引的对比分析

    MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

  • 浅谈mysql密码遗忘和登陆报错的问题

    mysql登录密码忘记,其实解决办法很简单,只需要在mysql的主配置文件my.cnf里添加一行"跳过授权表"的参数选择即可! 在my.cnf中添加下面一行: [root@test-huanqiu ~]# vim /etc/my.cnf              //在[mysqld]区域里添加 ........ skip-grant-tables                       //跳过授权表 然后重启mysql服务,即可无密码登录 [root@test-huanqiu

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • 浅谈mysql数据库中的换行符与textarea中的换行符

    1. mysql数据库中的换行符 在mysql数据库中, 其换行符为\n 即 char(10), 在python中为chr(10) 2. textarea中的换行符 textarea中的换行符为\r\n 3. web应用中换行符转换 以下是python django web的处理: # data为textarea获取的数据, 其中包括换行符`\r\n`, 以下是过渡处理 data = data.replace('\r\n', '\n') # 或 data = data.replace('\r\n

  • 浅谈mysql的子查询联合与in的效率

    最近的产品测试发现一个问题,当并发数量小于10时,响应时间可以维持在100毫秒以内.但是当并发数到达30个时,响应时间就超过1秒.这太不能接受了,要求是通过1秒中并发100个. 经过检测发现,时间主要是耗在其中的一个存储过程中.把存储过程的语句一条一条的过一遍也没有发现明显的不合理.因为mysql本身不能提供毫秒级别的时间,google了一个mysql的能提供毫秒的时间函数,再做测试,做了一个定位.发现是其中一条语句,语句是这个样子: select .... from A, B where ..

随机推荐