目录
1、索引的本质
索引是帮助MySQL高效获取数据的排好序的数据结构
索引数据结构
- 二叉树
- 红黑树
- Hash表
- B-Tree
普通二叉树对应数据结构
1.1 B-Tree 数据结构
- 叶节点具有相同的深度,叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
- 数据在每个节点中
1.2 B+Tree(B-Tree变种) 数据结构
- 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问的性能
2、MySQL中的存储引擎
2.1 什么是存储引擎
存储引擎说白了就是如何存储数据,如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
Oracle和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎。(这里不光指整个库的存储引擎方式,可以下放到表层次)。
2.2 查看MySQL支持的存储引擎
2.2.1 查看数据库支持的引擎
show engines;
2.2.2 查看某个表的执行引擎
SHOW TABLE STATUS LIKE '表名';
// 例如
SHOW TABLE STATUS LIKE 'basic_key';
2.3 MySQL常用存储引擎(5.7版本)
2.3.1 MyISAM
一个表是MyISAM存储引擎,则在磁盘中生成三个文件。
.frm 文件存储表结构
.MYD 存储数据
.MYI 存储索引
MyISAM引擎的索引结构为B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集(聚簇)索引。
在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。由于数据索引和存储数据分离,MyISAM引擎的索引结构是B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是所他的索引和实际数据是分开的。不过索引指向实际的数据,这种索引也就是非聚合索引。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
它没有提供对数据库事务的支持,也不支持表级锁,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。
不过和Innodb不同,MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。
2.3.2 InnoDB
一个表是InnoDB存储引擎,则在磁盘中生成两个文件。
.frm 文件存储表结构
.idb 存储的是数据和索引文件
InnoDB存储引擎的索引也是使用B+Tree结构来存储的,但是InnoDB的索引文件本身就是数据文件,即B+Tree的数据域存储的就是实际数据,这种索引就是聚簇索引。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。
总结:
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录
为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
因为数据文件本身就是主索引,自增主键可以快算的索引建立和数据的插入
为什么非主键索引结构叶子节点存储的是主键值?
(一致性和节省存储空间),数据只再主索引上存在一份,这样可以保持数据的一致性问题,同样也节省了存储空间。
主键索引
非主键索引
联合索引(A字段_B字段_C字段)是按顺序来的,所以最左原则也是通过这个数据结构来的
和MyISAM不同,InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址。
所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。
针对事务的支持,事务(ACID),行级锁,外键,Java中使用事务处理,首先要求数据库支持事务。如使用MySQL的事务功能,就要求MySQL的表类型为Innodb才支持事务
2.3.3 MyISAM和InnoDB区别
1、事务:InnoDB 支持事务,MyISAM 不支持,对于 InnoDB 每一条 SQL 语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条 SQL 语言放在 begin 和 commit 之间,组成一个事务。
2、主外键:InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM会失败。
3、聚集(聚簇)索引:
InnoDB是聚集(聚簇)索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。
MyISAM 是非聚集(聚簇)索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4、表的中行数:InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。