Mysql优化（2）：索引在InnoDB, MyISAM中的区别、聚簇索引，hash索引、主键索引与非主键索引，表数据文件

本篇主要是对于一些概念性的答疑

索引在InnoDB和MyISAM中的区别

我们先看看在InnoDB和MyISAM中，数据文件的不同形式。
不管是索引还是数据，最终它都要落到磁盘上存储起来（数据库能存东西，总不能是凭空而来的，最终都要落地到磁盘文件上）。

MyISAM中，每新建一个表，都会产生三个文件: 后缀分别是 .frm、 .MYD、 .MYI。
文件名对应的就是表名：例如新建了一个 myisam_test表和innodb_test表。

CREATE TABLE `myisam_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `innodb_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `real_name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    我们发现，myisam_test表包含 3 个数据文件。 frm是表定义文件，MYI是表索引文件, MYD是表数据文件。而 innodb_test只有 2 个数据文件： frm同样是表定义文件，ibd是表索引和数据文件，它把表索引和数据放到了一起。

InnoDB的索引跟数据是绑定在一起的，数据文件本身就是一个按B+树而组织的一个索引结构。而MyISAM表的索引和数据是分离的。这也就是聚簇索引和非聚簇索引的区别，聚簇索引不是一种索引类型，它指的是索引的数据存储方式；
在InnoDB中，聚簇索引就是按照表的主键构造一个B+树，树的叶子节点存储了表的记录行的所有数据。主键索引是聚簇索引，而且InnoDB表中也最多只能有一个聚簇索引

InnoDB主键索引的数据结构

叶子节点中的data就是这行记录的所有字段数据。

InnoDB中非主键索引（普通索引）结构

普通索引的结构跟主键索引非常相似，只是叶子节点的data存储的不是行的所有字段，而是其主键值，也就是上图中的，15,18,20,30这些值。

根据普通索引去查询数据的时候，其实遍历了2颗B+树，通过普通索引找到其主键值，再到主键索引结构里找到对应的数据

MyISAM中的索引结构

MyISAM的索引，也是B+树结构。它的叶子节的data存储的是记录所在的硬盘地址指针。
搜索速度：
InnoDB主键索引 > MyISAM索引 > InnoDB普通索引

hash索引

上面我们谈到的都是 B+树索引，其实MySQL中还存在另外一种哈希索引。它通过哈希表来查找数据。例如以上的 innodb_test 的 user_name字段来建立哈希索引，那么当我们搜索 where user_name = ‘小王’ 的时候，先把字符串 '小王’做一次hash运算得到一串字符: hash(小王)，（可能是一个64位或32位的字符串）
哈希索引会维护一张哈希表：将hash(小王)的值跟记录的硬盘地址指针做一次映射，这个映射的查找速度非常快，一次到位。

这么看来，哈希索引的查找速度比B+索引更快呀，确实如此：不考虑哈希碰撞的极端情况，哈希索引具有更快的搜索速度。但是为什么我们日常工作中很少有用到哈希索引？哈希索引有一个致命的弱点：它没法做范围查找。工作中范围查找是大多数情况： where age > 10 ? where name like ‘%123%’, where date > ‘2020-01-01’。这些情况哈希索引都没法用