MySQL学习笔记（未完待续...）

预备知识

---

• SQL基本语句：可参考此博文：SQL常用语句
• B 树、B+ 树：可参考此博文：b树和b+树的区别

MySQL

---

一、索引（Index）

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

1. 索引的优点

• 大大减少了服务器需要扫描的数据行数（不再全表扫描）。

• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

2. 什么情况下应该添加索引

• 数据量比较庞大，对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效

• 没有频繁的增、删、改操作（需要维护索引）

• 经常作为SQL查询语句的条件（WHERE子句中）

3. B+ 树索引

B+树是大多数MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描（类似于汉语字典中一页一页的去查找一个汉字），只需要对树进行搜索即可（类似于利用汉语字典中的目录页去快速定位一个汉字，索引等效于目录的功能），所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序（ORDER BY）和分组（GROUP BY）。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

我们根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了找到主键索引的一个二级索引，先找到主键索引再通过主键索引找到行记录数据；

注：

• 聚簇索引：就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

• 非聚簇索引：非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是存放数据的地址。

4. 哈希索引

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

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

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

二、存储引擎

1. InnoDB

MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

2. MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

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

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

3. 对比

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句，而MyISAM不支持事务。

• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

• 外键：InnoDB 支持外键。

• 备份：InnoDB 支持在线热备份。

• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

• 其它特性：MyISAM 支持压缩表和空间数据索引。

参考：https://github.com/CyC2018/CS-Notes/blob/master/notes/MySQL.md