MySql索引__聚集索引&&非聚集索引&&复合索引&&最左前缀原则

1.为什么要使用索引？
索引是什么？
这里很官方的回答是：索引是帮助 MySQL 高效获取数据的数据结构。
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
数据库除了存储数据，它还要维护着很多对数据的操作，这些操作设计到了数据结构和算法，
这样才能更高效更安全的对数据进行操作，而索引就是以某种方式引用（指向）数据，并且在这些数据结构上实现高级查找算法。这种数据结构，就是索引
说白了，索引要解决的就是查询问题。

2.MySQL的基本存储结构是页，页是什么了？
我们知道MySQL 数据库存储数据最终是以文件的形式存储到硬盘的。我们在程序中使用的时候肯定要把磁盘文件中的数据读到内存中。磁盘读取数据靠的是机械运动。访问一次磁盘就是一次磁盘IO。每一次IO读取的数据我们称之为一页(page)。

3.MySQL的索引模型
它所会用到数据结构知识点：
1.单链表和双链表；
2.哈希表；
3.二叉树；
4.B+树；
这些数据结构，我会用再改篇中用java代码去实现并总结，这里主要是聊MySQL的索引模型。

索引相当于数据库的表数据之外新建的数据结构，该数据结构的数据段中存储着实际字段的值以及指向实际数据记录的指针。

数据库表的索引从 数据存储方式 上分两种:

1.聚簇索引
2.非聚簇索引（又叫二级索引）
这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式

1.聚簇索引
聚集（clustered）索引
数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引。
当然你也可以不使用主键做聚簇索引，只需要在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键。

myisam没有聚集索引的概念.

聚簇集：实际的数据行和相关的键值都保存在一起。

图上的叶子节点默认保存的主键，二级节点也是保存的是记录行的主键。你可以通过在设置
主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样这些叶子节点保存的就是你设置的唯一索引。

关于聚簇索引打比方说明
一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面实际存储的顺序结构与数据存储物理机构是一致的。

2.非聚簇索引（又叫二级索引）
非聚集（unclustered）索引。
与聚簇索引有些相反，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

关于非聚簇索引打比方说明
非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。
对照着来看，非聚簇索引的概念非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

我们给IndexTestTable表的Name字段添加一个非聚簇索引：
CREATE NONCLUSTERED INDEX IndexTestTable_index_name ON IndexTestTable(Name);

二次查询问题
聚集索引：索引的叶子节点也就是它的数据节点，也就是说可以直接通过叶子节点获取到它所对应列的数据，故通常没有二次查询的问题。

非聚集索引：叶子节点就是索引的节点，只是一级一级的通过索引节点指向下一级的索引节点，最后只有一个指针指向对应的数据块。

此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

个人理解：
在聚集索引中，因为叶子节点和它的实际存储的顺序一致，子节点也是存储的节点也是和它的实际存储的顺序一致，也就是说索引的叶子节点也就是它的数据节点，那么通过查询索引就可以查询出对应的记录。

在非聚集索引中，因为叶子节点和它的实际存储的顺序无关，而是通过定义索引来做叶子节点的，也就是说，它是由索引节点组成的类似目录一样的节点，没个目录编号对应一个内容，这个内容就是数据库存储的记录，记录的实际存储的顺序和目录的顺序也无关。
所以在查询的列中包含了其他该索引没有覆盖的列，也就是说目录中没有定义该索引，所以要重新查询一边，查到该目录下有的索引。

以及聚集索引clustered index(id), 非聚集索引index(username)。
使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
select id, username from t1 where username = '小明'
select username from t1 where username = '小明'
但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：
select username, score from t1 where username = '小明'

如何解决非聚集索引的二次查询问题
复合索引（覆盖索引）
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2):

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

最左前缀原则
MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是index (name,city)。

最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select col1, col2 from t1 where col1 = '213';// 可以命中索引
select * from user where city=xx; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx ，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的.
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。
ORDERBY子句也遵循此规则。

注意避免冗余索引
冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
MySQLS.7 版本后，可以通过查询 sys 库的 schemal_r dundant_indexes 表来查看冗余索引

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

2.添加UNIQUE(唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3`