共计 3736 个字符，预计需要花费 10 分钟才能阅读完成。
mysql 聚簇索引 和聚簇索引（二级索引）的 那些事
mysql 的聚簇索引是指 innodb 引擎的个性，mysiam 并没有，如果须要该索引，只有将索引指定为主键（primary key）就能够了。
比方：
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) 这个就是聚簇索引索引了；
聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点依然是索引节点，并保留一个链接指向对应数据块。
聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。相比之下，聚簇索引适宜排序，非聚簇索引（也叫二级索引）不适宜用在排序的场合。因为聚簇索引自身曾经是依照物理程序搁置的，排序很快。非聚簇索引则没有按序寄存，须要额定耗费资源来排序。当你须要取出肯定范畴内的数据时，用聚簇索引也比用非聚簇索引好。另外，二级索引须要两次索引查找，而不是一次能力取到数据，因为存储引擎第一次须要通过二级索引找到索引的叶子节点，从而找到数据的主键，而后在聚簇索引中用主键再次查找索引，再找到数据。
innodb 索引分类：聚簇索引(clustered index) 1) 有主键时，依据主键创立聚簇索引 2) 没有主键时，会用一个惟一且不为空的索引列做为主键，成为此表的聚簇索引 3) 如果以上两个都不满足那 innodb 本人创立一个虚构的汇集索引 辅助索引(secondary index) 非聚簇索引都是辅助索引，像复合索引、前缀索引、惟一索引
myisam 索引：因为 myisam 的索引和数据是离开存储存储的，myisam 通过 key_buffer 把索引先缓存到内存中，当须要拜访数据时（通过索引拜访数据），在内存中间接搜寻
索引，而后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的起因
innodb 索引：innodb 的数据和索引放在一起，当找到索引也就找到了数据
自适应哈希索引：innodb 会监控表上的索引应用状况，如果察看到建设哈希索引能够带来速度的晋升，那就建设哈希索引，自 适应哈希索引通过缓冲池的 B + 树结构而来，
因而建设的速度很快，不须要将整个表都建哈希索引，InnoDB 存储引擎会主动依据拜访的频率和模式来为某些页建设哈希索引。自适应哈希索引不须要
存储磁盘的，当停库内容会失落，数据库起来会本人创立，缓缓保护索引。
聚簇索引：MySQL InnoDB 肯定会建设聚簇索引，把理论数据行和相干的键值保留在一块，这也决定了一个表只能有一个聚簇索引，即 MySQL 不会一次把数据行保留在二个中央。
1) InnoDB 通常依据主键值 (primary key) 进行聚簇
2) 如果没有创立主键，则会用一个惟一且不为空的索引列做为主键，成为此表的聚簇索引
3) 下面二个条件都不满足，InnoDB 会本人创立一个虚构的汇集索引
长处：聚簇索引的长处, 就是进步数据拜访性能。聚簇索引把索引和数据都保留到同一棵 B + 树数据结构中，并且同时将索引列与相干数据行保留在一起。这意味着，当你拜访同一数据页不同行记录时，曾经把页加载到了 Buffer 中，再次拜访的时候，会在内存中实现拜访，不用拜访磁盘。不同于 MyISAM 引擎，它将索引和数据没有放在一块，放在不同的物理文件中，索引文件是缓存在 key_buffer 中，索引对应的是磁盘地位，不得不通过磁盘地位拜访磁盘数据。
毛病：1) 保护索引很低廉，特地是插入新行或者主键被更新导至要分页 (page split) 的时候。倡议在大量插入新行后，选在负载较低的时间段，通过 OPTIMIZE TABLE 优化表，因为必须被挪动的行数据可能造成碎片。应用独享表空间能够弱化碎片
2) 表因为应用 UUId 作为主键，使数据存储稠密，这就会呈现聚簇索引有可能有比全表扫面更慢，所以倡议应用 int 的 auto_increment 作为主键
3) 如果主键比拟大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间
辅助索引 在聚簇索引之上创立的索引称之为辅助索引，辅助索引拜访数据总是须要二次查找。辅助索引叶子节点存储的不再是行的物理地位，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据叶，再通过数据叶中的 Page Directory 找到数据行。
复合索引 由多列创立的索引称为合乎索引，在合乎索引中的前导列必须呈现在 where 条件中，索引才会被应用 ALTER TABLE test.users ADD INDEX idx_users_id_name (name(10) ASC, id ASC) ;
前缀索引 当索引的字符串列很大时，创立的索引也就变得很大，为了减小索引体积，进步索引的扫描速度，就用索引的前局部字串索引，这样索引占用的空间就会大大减少，并且索引的选择性也不会升高很多。而且是对 BLOB 和 TEXT 列进行索引，或者十分长的 VARCHAR 列，就必须应用前缀索引，因为 MySQL 不容许索引它们的全副长度。
应用：列的前缀的长度抉择很重要，又要节约索引空间，又要保障前缀索引的选择性要和索引全长度选择性靠近。
惟一索引 惟一索引比拟好了解，就是索引值必须惟一，这样的索引选择性是最好的
主键索引 主键索引就是惟一索引，不过主键索引是在创立表时就创立了，惟一索引能够随时创立。
阐明 主键和惟一索引区别 1) 主键是主键束缚 + 惟一索引 2) 主键肯定蕴含一个惟一索引，但惟一索引不是主键 3) 惟一索引列容许空值，但主键列不容许空值 4) 一个表只能有一个主键，但能够有多个惟一索引
索引扫描形式：紧凑索引扫描(dense index)：在最后，为了定位数据须要做权表扫描，为了进步扫描速度，把索引键值独自放在独立的数据的数据块里，并且每个键值都有个指向原数据块的指针，因为索引比拟小，扫描索引的速度就比扫描全表快，这种须要扫描所有键值的形式就称为紧凑索引扫描
涣散索引扫描(sparse index)：为了进步紧凑索引扫描效率，通过把索引排序和查找算法（B+trre），发现只须要和每个数据块的第一行键值匹配，就能够判断下一个数据块的地位或方向，因而无效数据就是每个数据块的第一行数据，如果把每个数据块的第一行数据创立索引，这样在这个新创建的索引上折半查找，数据定位速度将更快。这种索引扫描形式就称为涣散索引扫描。
笼罩索引扫描(covering index)：蕴含所有满足查问须要的数据的索引称为笼罩索引，即利用索引返回 select 列表中的字段，而不用依据索引再次读取数据文件
索引相干常用命令：1) 创立主键 CREATE TABLE pk_tab2 (id int(11) NOT NULL AUTO_INCREMENT, a1 varchar(45) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2) 创立惟一索引 create unique index indexname on tablename(columnname); alter table tablename add unique index indexname(columnname);
3) 创立单列个别索引 create index indexname on tablename(columnname); alter table tablename add index indexname(columnname);
4) 创立单列前缀索引 create index indexname on tablename(columnname(10)); // 单列的前 10 个字符创立前缀索引 alter table tablename add index indexname(columnname(10)); // 单列的前 10 个字符创立前缀索引
5) 创立复合索引 create index indexname on tablename(columnname1，columnname2); // 多列的复合索引 create index indexname on tablename(columnname1，columnname2(10)); // 多列的蕴含前缀的复合索引 alter table tablename add index indexname(columnname1，columnname2); // 多列的复合索引 alter table tablename add index indexname(columnname1，columnname(10)); // 多列的蕴含前缀的复合索引
6) 删除索引 drop index indexname on tablename;; alter table tablename drop index indexname;
7) 查看索引 show index from tablename; show create table pk_tab2;