5.2 InnoDB的索引实现，唯一索引、覆盖索引、前缀索引、联合索引、、最左匹配原则；

https://www.cnblogs.com/aspwebchh/p/6652855.html

熟练使用数据库和数据库索引是开发人员的必备技能。
CREATE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

为什么要给表加上主键？
为什么加索引后会使查询变快？
为什么加索引后会使写入、修改、删除变慢？
什么情况下要同时在两个字段上建索引？

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree。

主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。

如果给表上了主键，那么表在磁盘上的存储结构为树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。整个表变成了一个索引，也就是所谓的「聚集索引」。

这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，
因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
树的所有结点（底部除外）的数据都是由主键字段中的数据构成，
也就是通常我们指定主键的id字段。
最下面部分是真正表中的数据。 假如我们执行一个SQL语句：
from table where id = 1256;
首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。
然而， 事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的，
因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，
破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，
也就是为什么索引会给查询以外的操作带来副作用的原因。
讲完聚集索引 ， 接下来聊一下非聚集索引， 也就是我们平时经常提起和使用的常规索引。
非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，
假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，
在数据改变时， DBMS需要一直维护索引结构的正确性。
如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。
每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。
因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。
非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ，
再使用主键的值通过聚集索引查找到需要的数据。
不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。
然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「覆盖索引」查询，
也就是平时所说的复合索引或者多字段索引查询。
文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中，
如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。
先看下面这个SQL语句
//建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'
这句SQL语句的执行过程如下
首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置
最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果
我们把birthday字段上的索引改成双字段的覆盖索引
create index index_birthday_and_user_name on user_info(birthday, user_name);
这句SQL语句的执行过程就会变为
通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，
然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面，
因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即
 https://www.cnblogs.com/protected/p/6762940.html

 MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
 MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引在结构上没有任何区别，
 只是主索引要求key是唯一的，而辅助索引的key可以重复。
 MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 InnoDB也使用B+Tree作为索引结构，但具体实现方式与MyISAM不同。
 第一个重大区别是InnoDB的数据文件本身就是索引文件。
 从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
 而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
 这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

 InnoDB主索引（同时也是数据文件）,叶节点包含了完整的数据记录。这种索引叫做聚集索引。
 因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），
 如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，
 则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
 换句话说，InnoDB的所有辅助索引都引用主键作为data域。
 聚集索引这种实现方式使得按主键的搜索十分高效，
 但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，
 就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，
 过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，
 因为InnoDB数据文件本身是一颗B+Tree，
 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，
 而使用自增字段作为主键则是一个很好的选择。

 补充 ：
 聚集索引和非聚集索引：
 聚集索引（InnoDB中的主索引）
 　　一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
 　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。
  由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。
  但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。
 例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，
 然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。
 同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），
 避免每次查询该列时都进行排序，从而节省成本。

 当索引值唯一时，使用聚集索引查找特定的行也很有效率。
 例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
 聚集索引一般用于选择单调的字段建立。

 非聚集索引（MySIAM）
 　　一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
 
 https://blog.csdn.net/winy_lm/article/details/49718193
 唯一索引
 普通索引允许被索引的数据列包含重复的值。唯一索引不允许，我们可以使用关键字UNIQUE来定义唯一索引。
 人们创建唯一索引的目的往往是为了避免数据出现重复。

 联合索引 ：可以覆盖多个数据列，像INDEX(columnA, columnB)索引，这就是联合索引。

 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，
 但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，
 但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

 最左匹配原则
 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合，
 所以在建立联合索引的时候查询最频繁的条件要放在左边
     
 MySQL 前缀索引
 参考：https://www.jianshu.com/p/fc80445044cc
 当要索引的列字符很多时 索引则会很大且变慢
 ( 可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 )
 建立前缀索引 以前4位字符来创建
 alter table x_test add index(x_name(4));