mysql 存储引擎和索引

存储引擎

引擎	特性	文件
InnoDB	默认，支持事务，支持外键，支持行锁和表锁	.frm 文件存储表结构 .ibd 文件存储数据和索引
MyISAM	不支持事务，不支持外键，只支持表锁不支持行锁 专门维护了一个常量保存每个表的总记录数（count 很快） MyISAM 强调的是性能，所以性能上优于InnoDB，但安全性和并发性较低	.frm 文件存储表结构 .MYD 文件存储数据 .MYI 文件存储索引
Memory	数据存在内存中，所以性能上来看是最好的，缺点也很明显，一旦宕机数据就没了	只有表结构文件（只有 frm 文件）

索引

索引是一种数据结构，目的是提高检索数据的效率，减少磁盘I/O，也是一个文件存放在磁盘中（Memery 这种引擎数据都在内存中，索引也不可能有实质的文件了）
有利有弊，索引也是需要维护的，所以索引越多维护的成本也增大
按照不同的规则可以划分出不同的类型，比如按照字段个数：单列索引，复合索引；是否主键：主键索引，非主键索引。按照索引的数据结构上又能分为：聚簇索引和非聚簇索引

索引类型	支持的存储引擎	数据结构	节点内容	特点
聚簇索引/主键索引	InnoDB	b+tree	叶子节点存放整行数据 非叶子存放主键和数据页的关系	一半不超过3级，因为3级都差不多上亿的数据量了 主键如果没有顺序，插入效率会很低，因为索引是有序的，所以没有序的数据可能要修改整棵索引树
非聚簇索引/辅助索引/二级索引	InnoDB	b+tree	和聚簇索引的区别是：叶子节点只存放主键	可能会回表（符合索引下推就不回表）
MyISAM 索引	MyISAM	b+tree	和聚簇索引的区别是：数据对应在硬盘的地址	一定会回表，因为索引和数据文件都不是同一个 可以没有主键，因为不支持聚簇索引
Hash 索引	Memory	hash		数据结构类似 java 的 HashMap，根据 key 算下标，然后取数据，大多数情况下时间复杂度是 O(1)，查询效率相当高

• b-tree 是一种数据结构，这种数据结构是叶子节点和非叶子节点都可能存放数据。但是数据库索引没有采用这种结构！
• 为什么非聚簇索引叶子不也放整行数据，这样就能避免回表？数据冗余，比如10个非聚簇索引，就要放11份数据
• InnoDB 能不能使用 Hash 索引？不能手动创建 Hash 的索引，但是如果是访问频繁的数据，内部会自动构建一个自适应哈希索引
• 既然 Hash 索引查询效率高，为什么 InnoDB 不使用 Hash 要用 B+Tree？
  • 根据 key 得到下标然后得到数据的，如果是 like 或者范围查询就 gg 了，因为 key 都不是常量
  • 默认查询的结果集是没有顺序的，如果要 ORDER BY 内部会再次对结果集进行排序计算
  • 如果是联合索引，hash 值是多个列联合计算的，无法根据其中的一个列进行索引

InnoDB 聚簇索引数据结构

• 索引的数据结构是 b+tree，每个数据页（目录页）是双向链表，数据页里面的数据是单向链表，为什么是 b+tree 后面会说到
• 计算机与磁盘交互的基本单位是数据页（图中目录页也是数据页，为了区分 bTree 的叶子节点和非叶子节点的叫法而已），每个数据页大小16k
• sql 不管走不走索引都要发生磁盘交互，因为索引也是文件。数据会放在多个数据页，索引的目的是提高检索效率减少磁盘IO次数
• 比如要找 id=9 的数据，查询过程如下：
  • 先到根节点的目录页，里面是个单向循环链表，能确定在目录页19
  • 目录页19 里面的节点也是单向循环链表，能知道数据位于数据页21
  • 到数据页21 里面得到 id=7 的数据

b+tree 和 b-tree

B+Tree 只是叶子节点才存放数据，B-Tree 非叶子节点也可能存放数据，因为数据页的大小是固定的 16k，所以 B-Tree 更高瘦
B+Tree 查询更稳定，正因为有的数据放在目录页，而一个页的大小又是固定的，所以快的时候效率高有的时候就比较低