数据库学习— 索引、存储引擎

索引

索引的目的
在于提高查询效率。

B+ TREE

B 树只指Balance Tree 平衡树。平衡树是一颗查找树，所有叶子节点位于同一层。
B+ 树是基于B 树 和 叶子节点顺序访问指针进行实现。它具有B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

B+ 树是一种树数据结构，通常用于数据库和操作系统的文件系统中。
B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素，自底向上插入，这与二叉树恰好相反。详情B+ 树

上图中，每一个整体块我们称之为一个磁盘块，每个磁盘块包含几个数据项（灰色）和指针（白色区域的黑点点）。如最上面的磁盘块，包含数据项3 和 5，包含下面对应的三个指针。最左边的指针表示小于3的磁盘块，中间的指针表示在3 和 5之间的磁盘块，最右边的指针表示大于5的磁盘块。真实的数据存在于叶子节点，即d1、d2、d3、d4、d5、d6、d7。非叶子节点不存储真实数据，只存储指引搜索方向的数据项，最上面的3 和 5并不真实存在于数据表中。

B+ 树的查找过程

进行查找操作时首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。知道查找到叶子节点，然后在叶子节点上进行二分查找，找出key对应的data。

插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作时，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

B+ 树的性质

平衡树查找操作时间复杂度和树高度h相关，O(h)=O(logdN)，d为每个节点的出度。
假设当前数据表的数据为N，每个磁盘块的数据项的数量为m，则有h = log(m+1)N【(m+1)为底数】，当数据量N一定的情况下，m越大，h越小。

为了减少磁盘I/O操作，磁盘往往不是严格按需读取，而是每次都会预读。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以也为单位交换数据。
数据库系统将索引的一个节点的大小设置为页的大小，使得一次I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先加载。

• B+ 树索引是大多数MySQL存储引擎的默认索引类型。因为不需要进行全表扫描，只需要对数进行搜索即可，所以查找速度快很多。
• 因为B+ 树的有序性，所以除了用于查找，还可以用于排序和分组。
• InnoDB 默认支持 b+ 树

哈希索引

哈希索引能够以O(1) 时间复杂度进行查找，但是失去了有序性：

• 无法用于排序和分组
• 只支持精确查找，无法用于部分查找和范围查找

InnoDB 存储引擎有一个特殊的功能叫自适应哈希索引，当某个索引值被使用的非常频繁时，会在B+ 树索引之上，再创建一个哈希索引，这样就让B+ 树索引具有哈希索引的一些优点，比如快速的哈希查找。

看到有的资料说InnoDB 不支持哈希索引，但还看到说InnoDB 自适应哈希索引，就像上面写的。所以一开始有些迷。
还看到有资料解释说：

• InnoDB 用户无法手动创建哈希索引，从这个方面讲，InnoDB 是不支持哈希索引的；
• InnoDB 会自调优(self-tuning)，如果判定建立自适应哈希索引，能够提升查询效率，InnoDB自己会建立相关哈希索引，从这个方面讲，InnoDB又是支持哈希索引的。👀

MySQL 索引

索引实在存储引擎层实现的，而不是在服务器层，所以不同的存储引擎具有不同的索引类型和实现。

索引分类

• 主键索引（Primary Key）
• 唯一索引（Unique）
• 普通索引（Index）
• 全文索引（FullText）

主键索引

主键：某一个属性组能唯一标识一条记录。
特点：

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

CREATE TABLE mytable(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(32),
	PRIMARY KEY (`id`)
	);

唯一索引

作用：避免同一个表中某数据列中的值重复。
与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

CREATE TABLE `mytable`(
  `id` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `name` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `id` (`id`)
)

普通索引

作用：快速定位特定数据。

创建表的时候一起创建：

CREATE TABLE mytable(
	name VARCHAR(32),
	INDEX index_mytable_name (name));

建表后创建：

create index 索引名 on table_name(column1,column2);
alter table table_name add index 索引名(column1,column2);

全文索引

全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)。

• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

建表时一起创建：

CREATE TABLE `article`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`title` char(250) NOT NULL,
	`contents` text NULL,
	`create_at` int(10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	FULLTEXT (contents)
	);

创建表后，在已有的表上创建索引：

CREATE FULLTEXT INDEX index_article_contents ON article(contents);

存储引擎

InnoDB

MySQL默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它的存储引擎。
实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking（InnoDB的锁机制）防止幻影读。
内部做了很多优化：

• 从磁盘读取数据时采用可预测性读
• 能够加快读操作并且自动创建的自适应哈希索引
• 能够加快插入操作的插入缓冲区等

MyISAM

设计简单，数据以紧密格式存储。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

InnoDB 与 MyISAM 的比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

补充：
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

学习自：

• MySQL索引原理及慢查询优化
• CYC