mysql系列—超详细索引讲解（下）

主键和唯一索引的区别：

1. 一个表的主键只能有一个，而唯一索引可以建多个。
2. 主键可以作为其它表的外键。（ 外键不一定是主键 ， 只要唯一就行；SQL 的主键和外键就是起约束作用，插入非空值时，如果主键表中没有这个值，则不能插入。可以有复合主键，但是不能有多个主键。）
3. 主键不可为 null，唯一索引可以为 null。

哈希索引：

​ 基于哈希表实现，只有 memory 引擎显示支持哈希索引（使用链地址法解决哈希冲突），InnoDB 有一个特殊的功能**“自适应哈希索引”**，当某些索引值被引用非常频繁，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引。

​ 哈希索引查找速度很快，但哈希索引数据不是按照索引顺序存储的，所以无法用于排序；只支持等值查找，不支持范围查找（如大于多少）；如果哈希冲突很多（如选择性低的），当查找或删除一行，需要遍历对应哈希值的链表的每一行，冲突越多，代价越大

索引的创建

表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

创建索引还可以用 alter 实现

InnoDB 按照主键进行聚集，如果没有定义主键，InnoDB 会试着使用唯一的非空索引来代替。

如果没有这种索引，InnoDB 就会定义隐藏的主键（row_id 大小为6 个字节）然后在上面进行聚集。mysql不能手动创建聚集索引。

主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

索引的实现（数据结构学完，还可以再详细）

视频资料

​ 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O存取的消耗要高几个数量级，而 B-Tree 的高度低（多叉树），可以减少 I/O 次数

​ InnoDB（聚簇索引）的数据文件本身就是索引文件（索引和数据存放在一个文件 idb）。从上文知道，MyISAM（非聚簇索引）索引文件（MYI）和数据文件（MYD）是分离的，索引文件仅保存数据记录的地址。

​ mysql 中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引（普通索引、唯一索引），又叫辅助索引（secondary indexes）。

实现区别

​ MyISAM 引擎使用 B+Tree 作为索引结构，叶结点的 data 域存放的是数据记录的地址。MyISAM 的索引方式也叫做“非聚集”的。

​ MyISAM 左图为主索引，右图为辅助索引（二级索引），两者在结构上没什么区别，都是 B+树。

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

InnoDB 上图图为主索引、下图为辅助索引，辅助索引结构也是 B+树

​ 第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道，MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，**这棵树的叶结点 data 域保存了完整的数据记录。**这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

​ 第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。

​ 这里以英文字符的 ASCII 码作为比较准则（排序）。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

​ 由于实际的数据页只能按照一颗 B+树进行排序，因此每张表只能有一个聚集索引。

联合索引（组合索引）与覆盖索引

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

​ 联合索引实现：每个节点含有多个关键字，排序时按照多个关键字的顺序进行排序。而这个顺序就是你创建索引时候的顺序

​ 如果你经常要用到多个字段的多条件查询，可以考虑建立联合索引，建立了一个联合索引就相当于建立了多个索引

​ 联合索引 sql 会先过滤出 last_name 符合条件的记录，在其基础上再过滤 first_name符合条件的记录。那如果我们分别在 last_name 和 first_name 上创建两个列索引，mysql的处理方式就不一样了，它会选择一个最严格的索引来进行检索，可以理解为检索能力最强的那个索引来检索，另外一个利用不上了，这样效果就不如多列索引了。虽然此时有了两个单列索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。如果经常使用单独一列作为查询条件，那么应该使用单列索引。（如有两个单列索引 a、b，查询的时候只用 a 或只用 b）

​ 多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格（选择性大）的索引放在前面，这样筛选的力度会更大，效率更高

​ 覆盖索引一定是这个 联合索引覆盖了你所有 select 中需要的数据！就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖（一个索引包含所有需要查询的字段的值）。

​ 覆盖索引对于 innodb 表特别的有用，因为 innodb 是聚集缓存。innodb 的 secondaryindex 在叶子节点保存了主键的值，因此，覆盖了查询的第二索引，在主键中避免了另外一次索引查找

​ 覆盖索引和联合索引基本没有区别，覆盖索引只是特定于具体 select 语录而言的联合索引。也就是说一个联合索引对于某个 select 语句，通过索引可以直接获取查询结果，而不再需要回表查询啦，就称该联合索引覆盖了这条 select 语句。

​ 索引覆盖是指建索引的字段正好是覆盖查询条件中所涉及的字段,这里需要注意的是,必须是从第一个开始覆盖,比如

索引字段	条件字段	有没有覆盖
a,b,c	a,b	覆盖了
a,b,c	b,c	没有覆盖

​ 覆盖索引的好处：二级索引如果是覆盖索引，避免了一次聚簇索引的查找，且覆盖索引的大小只包含需要的数据，而聚簇索引包含全部数据，覆盖索引可以更好的放入到内存当中。

关于索引可能的面试题（补充）

问题一：MySQL的底层数据结构

MyISAM引擎索引文件和数据文件是分离的，InnoDB引擎索引数据文件本身就是索引文件(聚簇索引)。

问题二：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键

InnoDB通过主键聚集数据，如果不存在主键，InnoDB会选择唯一的非空索引，如果也没有唯一非空索引，InnoDB隐式定义一个主键来作为聚簇索引。
自增主键可以保证数据顺序增加，如果不是顺序增加，会引起B+Tree叶子节点因为插入导致的数据检索，页分裂和保持顺序等消耗，而顺序增加只需要在B+Tree最后叶子节点加入就可以了。

问题三：为什么非主键索引结构叶子节点存储的是主键值

这样的设计**减少了行移动或者页分裂时二级索引的维护成本，**但是在二级索引访问时却需要两次索引查找。