mysql聚簇索引怎么写,mysql设置聚簇索引

mysql 聚簇索引怎么用

mysql的聚簇索引是指innodb引擎的特性，mysiam并没有，如果需要该索引，只要将索引指定为主键（primary key）就可以了。

创新互联建站公司2013年成立，先为翁牛特等服务建站，翁牛特等地企业，进行企业商务咨询服务。为翁牛特企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

比如：

1

2

3

4

5

6

7

8

create table blog_user

(

user_Name char(15) not null check(user_Name !=''),

user_Password char(15) not null,

user_emial varchar(20) not null unique,

primary key(user_Name)

)engine=innodb default charset=utf8 auto_increment=1;

其中的 primary key(user_Name) 这个就是聚簇索引索引了；

「Mysql索引原理（六）」聚簇索引

   本节课主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

   叶子节点包含了全部数据，其他节点只包含索引列。InnoDB将通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。

   如果主键比较大的话，那辅助索引将会变的更大，因为 辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

所以建议使用int的auto_increment作为主键

   主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大值时，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。

   聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

   因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

   不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

   MyISM使用的是非聚簇索引， 非聚簇索引的两棵B+树看上去没什么不同 ，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于 索引树是独立的，通过辅助键检索无需访问主键的索引树 。

   所以说，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。 一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引 ，这个要看实际情况。

   聚簇索引和非聚簇索引的数据分布有区别，主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和以外，下面通过一个列子来讲解InnoDB和MyISAM是如何存储数据的：

   该表的主键取值1~10000，按照随机顺序插入并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已是最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

   MyISAM的数据分布很简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：

在行的旁边显示行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。

col2上的索引

   事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。

   InnoDB支持聚簇索引，所以使用不同的方式存储同样的数据。

   第一眼看上去，感觉和前面的没什么区别，但是该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储，这也是为什么MyISAM索引和数据结构是分开的。

   聚簇索引的每一个叶子节点都包含了主键值。事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

   还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针”。

   我们在来看一下 col2索引 。

   每一个叶子节点包含了索引列（这里是col2），紧接着是主键值（col1），上图我们省略了非叶子节点这样的细节。InnoDB非叶子节点包含了索引列和一个指向下一级节点的指针。

   最后，以一张图表示InnoDB和MyISAM保存数据和索引的区别。

   前面讲过，最好使用AUTO_INCREMENT自增列来聚集数据，避免随机的、不连续的、值分布范围大的列做聚簇索引，特别是对于I/O密集型的应用。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：他使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

   为了演示这一点，我们做两个基准测试：

1、使用证书ID插入userinfo表，和uuid作为主键的userinfo_uuid表

   userinfo_uuid表跟userinfo表除了主键给为UUID，其他字段都一样

   测试这两个表的设计，首先在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录。然后向两个表继续插入300万数据，使索引的大小超过服务器的内存容量。测试结果如下：

   向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

   为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。

自整型主键插入

   因为主键是顺序的，所以InnoDB把每一条记录都存在上一条记录的后面。当达到页的最大容量后，下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。

UUID插入

   因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间。这会正价很多的额外工作，并导致数据分布不够优化。

缺点：

把这些随机值载入到聚簇索引后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。

结论 ：使用InnoDB时应尽可能地按主键顺序插入数据，并且尽可能地单调增加聚簇键的值来插入新行。

聚簇索引

  mysql的索引策略中有一条是聚簇索引，而聚簇索引并不是唯一索引，普通索引之类的索引类型，而是一种数据的存储方式。大多数索引存在的形式为B-tree，叶子节点的索引则和其对应的数据行数据紧凑的存储在一起，这就是术语聚簇的含义。实现数据存储形式的是存储引擎，但并不是所有存储引擎都支持聚簇索引，而著名的InnoDB则是支持的引擎之一，下面都以InnoDB为例。

  而存储引擎不能管理两份同样的数据，所以聚簇索引在同一张数据表中只能存在一个，其他的索引只能是非聚簇索引，也就是二级索引。数据表的如果有指定primary key，那么InnoDB就会把primary key作为聚簇索引来存储，如果没有，则会取第一个not null，unique的索引作为聚簇索引，unique的索引也不存在的话，InnoDB就会自行的，隐式的指定一个row ID列作为聚簇索引存储，但这个row ID不会被用户管理。

聚簇索引一些重要的优点：

  1. 在有聚簇索引的数据表中，使用聚簇索引进行查询的时候，因为索引和数据聚集在同一个B-tree中，能够直接从索引获取到数据行，比非聚簇索引的性能要好。

  2. 反之在没有聚簇索引的数据表中，因为不能通过unique的值去聚集数据，所以需要通过非聚簇索引查询数据的物理地址或者全表扫描来获取数据，这样每一行数据可能都会导致一次磁盘I/O。

在提升性能的同时，聚簇索引也存在着缺点：

  1. 更新聚簇索引的代价会很大，因为需要将数据行和主键进行重排，移动到新的位置，并且二级索引可能也需要更新。

  2. 聚簇索引的插入速度严重依赖插入顺序，严格的升序主键是性能最好的方式，但如果主键是乱序的插入，例如用uuid作为主键，当主键值需要插入到某一页已经写满的page中，存储引擎就需要将page分裂成两个页面来容纳数据，这一个 页分裂(page split) 操作，page split会使得数据表占用更多的磁盘空间。

  3. 通过二级索引获取需要两次索引查找，因为二级索引保存的是聚簇索引的主键，而不是指向数据的逻辑指针，所以获取主键后需要再进行一次搜索才能获取数据。

  聚簇索引和二级索引的数据分布方式不同，在MyISAM和InnoDB的数据文件组织方式中也有体现。

  MyISAM的数据由3个文件组成：1. .frm(表结构描述文件) ，2. .MYD(数据行文件) ，3. .MYI(索引文件) 。

  InnoDB则有2个文件：1. .frm(表结构描述文件) ，2. .MYD(数据行文件和索引信息) 。

  MyISAM引擎没有使用索引和数据的聚集的分布方式，所以主键和其他索引的是没有区别，就都存储在索引文件中。

InnoDB的锁机制是使用索引来实现，表现的等级为行级锁，而MyISAM则是表级锁，这也跟数据分布方式有关。InnoDB的主键索引与数据紧凑的聚集在一起，并且包含了事务ID，用于事务MVCC的回滚指针，而MyISAM则是数据与索引分离，无法实现如此细粒度的锁。

  1. InnoDB暂时不能由用户选定索引作为聚簇索引，InnoDB有自己的聚簇索引选取规则，所以在创建表的时候最好设置一个与业务无关的主键id作为聚簇索引，这样修改二级索引和数据的时候，无需移动数据位置，提升性能。

  2. 聚簇索引的主键id不要使用uuid，uuid会使得数据的插入添加额外的页分裂操作，降低性能，最好使用单调自增的id。