MySQL索引，InnoDB和MyISAM对比，聚簇索引，非聚簇索引，覆盖索引

索引的优点：
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O

MySQL数据库中InnoDB存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。
InnoDB使用的是聚簇索引，MyISAM使用的是非聚簇索引

InnoDB中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

聚簇索引(聚集索引)
　　聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
　InnoDB如下图：
　

辅助索引（非聚簇索引）
　　在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

InnoDB辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在InnoDB中有时也称辅助索引为二级索引。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
　同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
　　MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引和非聚簇索引访问区别，如下图所示，一个表存储了4行数据。其中Id作为主索引，Name作为辅助索引。

聚簇索引的优缺点
　　优点：
　　　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
　　　　2.聚簇索引对于主键的排序查找和范围查找速度非常快
　　　　3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值
　　缺点：
　　　　1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
　　　　3.二级索引可能比想象的更大，因为在二级索引的叶子节点包含了应用行的主键列，且访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
　　　　4.聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放到内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了
　　　　5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的，或者由于页分裂导致数据存储不连续的时候

索引覆盖

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> age tinyint(4),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，age 字段是普通索引（二级索引）

---

插入数据

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

mysql> select * from user;
+----+--------+------+
| id | name  | age |
+----+--------+------+
| 1 | 张三  |  30 |
| 2 | 李四  |  20 |
| 3 | 王五  |  40 |
| 4 | 刘八  |  10 |
+----+--------+------+

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
例如：select id,age from user where age = 10;

如何实现覆盖索引

将被查询的字段，建立到联合索引里去。

1、如实现：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引

2、实现：select id,age,name from user where age = 10;
explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询

为了实现索引覆盖，需要建组合索引idx_age_name(age,name)
drop index idx_age on user;
create index idx_age_name on user(age,name);
explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

---

在InnoDB表钟按逐渐顺序插入行

创建表

mysql> create table user(
    -> id int unsigned NOT NULL auto_increment,
    -> age tinyint(4),
    -> name varchar(30),
    -> city varchar(30),
    -> adress varchar(30),
    -> email varchar(30),
    -> gender varchar(30),
    -> password varchar(30),
    -> career varchar(30),
    -> hobby varchar(30),
    -> primary key (id),
    -> index idx_age (age)
    -> )engine=innodb charset=utf8mb4;

此处使用自增的证书ID做为主键
第二张表user_uuid，除了主键改为uuid,其余的和前面的表一样
对两张表插入相同的数据

	表名   		   行数       时间(s)   索引大小（MB）
	user		 1 000 000	  137		342
	user_uuid	 1 000 000	  180		544
	user		 3 000 000	  1233		1036
	user_uuid	 3 000 000	  4525		1770

注意到向uuid主键插入行不仅花费时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长；另一方面毫无疑问是由于页分裂和碎片导致的
主键自增的表，因为主键值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子时页大小的15/16，留出部分空间用于以后修改，下一条记录机会写入新的页钟。一旦数据按照这种顺序的方式加载，主键页就会近似的被顺序记录填满，这也是正是所期望的结果（然而，二级索引页可能是不一样的）
而uuid，因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。因为写入时乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量的数据，一次插入最少需要修改三个页而不是一个页，且可能会产生数据碎片。写入的目标页可能已经刷到磁盘上，并从缓存中移除，或者是还没有被加载到缓存钟，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
另外关于索引优化，避免索引失效可遵循如下小口诀：
*全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写 * ；
不等空值还有OR，索引影响要注意；
VAR引号不可丢， SQL优化有诀窍。

可以简单的对照下图理解

另外，一个值只有0和1的字段，到底要不要建索引？
不要！
但是如果表里面这个字段的值分布极度不均匀的情况下，而且你需要查询分布较少的记录的话，索引就非常有用了
举个例子，假设表中有一千万条记录，某个状态为0的记录总数大概会有100条，那么你想查询状态为0的记录时，有没有索引影响非常大，而查询状态为1的记录，则索引基本无用。如果两种状态的记录数相差无几的话，索引也基本无用。

所有的关于索引的文章，建议你不要为这种字段建索引的依据，都是以值分布是均匀为前提的。但如果值分布不均匀的时候，这个建议就不一定是正确的了。当我们需要查询的记录恰好是分布较少的记录的时候，值分布越是不均匀，索引就越有价值！

索引的本质就是排序，因为大家都排好了队，所以一样的数据都依次排在一块了。这个时候，就像奥运会开幕式入场的运动员们一样，中国队、美国队、德国队。。。队长都举着自己国家的小牌子，让人远远地就能看到那个瑞典队在什么地方(有索引)。这个时候，你要采访瑞典队，直接跑过去就行了。当散场后你再想去餐厅找瑞典队采访(没有索引)，估计就得按个问过去：兄弟，你是不是瑞典队的？等你找到采访对象，还是算了吧，人家都吃完饭走人了(查询超时)。。。

二值的情况下，形象点说，就是你在上海人民广场找人，看你找的是中国人还是外国人。找10个中国人很简单，基本上是逮着一个算一个。但要找10个外国人，就比较费时间了。。。这个时候如果大家都排队，中国人排前面，外国人排后面，你就可以直奔队伍的末尾去喊上10个外国人了。