无聊的知识点之数据库

概念

事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。
ACID ：
- 原子性(Atomicity)：事务的所有操作要么全部提交成功，要么全部失败回滚。
- 一致性(Consistency)：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
- 隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其它事务是不可见的。
- 持久性(Durability)：一旦事务提交，则其所做的修改将会永远保存到数据库中。
ACID 的意义：
- 一致性保证了事务执行结果正确。
- 持久性保证了系统崩溃后数据不会丢失。
- 事务串行执行时，只要保证了原子性，就自然保证了一致性。
- 事务并行执行时，只有同时保证原子性与隔离性，才能保证一致性。

并发一致性问题

先假设以下所说的事务不满足ACID的隔离性原则，因为一切并发问题都是由于没有完全满足隔离性原则。
丢失修改：事务T1和T2对同一数据进行修改，T1和T2都读取了数据后，T1提交修改，由于T2是在T1提交前读的数据，所以没有考虑T1的修改结果，直接把自己的修改结果提交，结果就是T1做的修改被回滚了，实际只有T2进行了修改。
读脏数据：事务T1修改了某数据，事务T2随后读取了该数据，随后T1回滚了修改操作。T2读到的就是脏数据，因为T2记录的数据值和数据库中的不一致。
不可重复读：事务T2中对某数据读取了两次，第一次读取后，事务T1提交了对该数据的修改，导致T2第二次读到的数据与第一次不同，违背了ACID的一致性原则，使得T2内部可能发生混乱。在这种场景下，不可重复读导致T2内部混乱，可重复读则导致T2读的不是最新数据，都会有问题，所以唯一的解决方法是禁止T1在T2的两次读之间修改数据。
幻影读：事务T1第一次读取某个范围的数据，随后事务T2在这个范围内插入或删除了一些数据，随后T1第二次读取此范围的数据，读取的结果和第一次不同。
不可重复读和幻影读本质上是一个问题，都是在一个事务提交的修改导致另一事务两次读的结果不同。区别在于，不可重复读针对的是UPDATE操作，幻影读针对的是INSERT和DELETE操作。
解决并发不一致问题的方法是通过封锁做并发控制。

封锁

封锁粒度：理论上应该只锁定需要修改的数据，这样能减少锁竞争，提高并发程度。但锁粒度太小会导致频繁的加锁解锁，增加系统开销。所以封锁粒度的选择是在并发度(速度)和资源开销间做权衡。
封锁类型：
- 读写锁：
  - 包含一个读锁(互斥锁，Exclusive，简称X锁)和一个写锁(共享锁，Shared，简称S锁)。
  - 如果事务T对数据A加了X锁，可以执行读写，加锁期间其他事务不能再对A加任何锁。
  - 如果事务T对数据A加了S锁，只能执行读操作，加锁期间其他事务只能对A加S锁，不能加X锁。
- 意向锁：
  - 在读写锁的基础上添加了意向锁，读写锁X/S用于实际封锁行或表，意向锁IX/IS仅用于表示封锁整张数据表的意向。
  - 一个事务在获得某行的 S 锁之前，必须先获得表的 IS 锁。
  - 一个事务在获得某行的 X 锁之前，必须先获得表的 IX 锁。
  - IX/IS锁之间相互兼容，因为并不是实际加锁，所以可以随便申请。只有与X/S锁关联时才需要考虑兼容性。
  - 意向锁的意义在于优化表级的X/S锁。由于对行的修改就相当于对表的修改，所以对表加X锁就需要保证所有行都没有被加X/S锁，对表加S锁就需要保证所有行都没有被加X锁，而这只能靠遍历所有行来确认，非常浪费时间。意向锁相当于对表做了额外的标记，只需要检查表的IX/IS锁就能知道是否有某行正在被读写(表级锁只关心有没有，不关心具体是哪些行)，而不需要遍历所有行。
封锁协议：
- 一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以避免丢失修改问题。
- 二级封锁协议：在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以避免读脏数据问题，因为加S锁的数据不能被回滚。
- 三级封锁协议：在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以避免不可重复读和幻影读问题。
- 两段锁协议：把事务分成两个阶段，第一阶段只允许获得锁，第二阶段只允许释放锁。事务遵循两段锁协议是保证可串行化调度的充分条件，可串行化调度是指能通过并发控制使得并发执行的事务结果与某个串行执行的事务结果相同，消除了并发的不确定性，所以能避免一切并发一致性问题。

隔离级别

封锁操作需要用户自己实现，而数据库一般内置了封锁功能，并对用户提供了对应的事务隔离级别。
未提交读(READ UNCOMMITTED)：事务所做的修改即使没有提交，对其它事务也是可见的。不能避免任何问题，同时也违背了ACID里的隔离性原则。
提交读(READ COMMITTED)：事务所做的修改在提交之前，对其它事务是不可见的。仅能避免读脏数据。
可重复读(REPEATABLE READ)：保证同一事务中对同一数据的多次读取结果相同。仅能避免丢失修改、读脏数据和不可重复读。
串行化(SERIALIZABLE)：强制事务串行执行。完全满足了隔离性原则，可以避免一切并发一致性问题。

多版本并发控制

多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 实现隔离级别的一种具体方式。核心思想是保存多个版本的快照，写操作更新最新版本的快照，而读操作去读旧版本快照，读和写没有了互斥关系，类似于CopyOnWrite。因为能保证读到的是提交过的修改，每次写操作的结果也会更新为最新版本，所以以空间为代价实现了并行化的高度隔离。
快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。事务的修改操作(DELETE、INSERT、UPDATE)会为数据行新增一个版本快照。
MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务(活跃事务)ID列表 trxids，以及该列表的最小值 up_limit_id 和最大值 low_limit_id，由于未提交的事务不一定是连续的，所以位于up_limit_id 和 low_limit_id之间的事务不一定就是未提交的。
开启一个事务后，读取数据时会生成该时刻对应的ReadView。在当前事务进行 SELECT 操作时，使用ROLL_PTR指针寻找可用快照，根据数据行快照的 trxid 与 up_limit_id 和 low_limit_id 之间的关系来判断该版本快照是否可以使用：
- trxid < up_limit_id，表示该快照对应的事务在当前事务开启之前就已经提交了，因此可以使用。
- trxid > low_limit_id，表示该快照对应的事务在当前事务开启之后才提交的，因为事务要保持一致性，所以必须对其他事务在当前事务执行期间所做的修改视而不见，因此该快照不可使用。
- up_limit_id <= trxid <= low_limit_id，如果 trxid 在 trxids 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。

在提交读隔离级别下，每次读取数据前都生成一个ReadView。在可重复读隔离级别下，只在第一次读取数据时生成一个ReadView。
对于当前事务使用的快照，因为其对应的事务都是提交了的，所以能避免读脏数据，又因为其对应的事务一定是在当前事务开启前提交的，所以能避免不可重复读。
MVCC不能完全避免幻影读：
- 在提到不可重复读时，一般认为是SELECT读操作，读的是快照，所以能够保持一致性。
- 在提到幻影读时，一般涉及两种读操作。一种是显式的SELECT，可以保持一致性。另一种是DELETE、INSERT、UPDATE操作，这些命令在修改数据前都需要先获取数据，相当于隐式的读，而这种读操作是要获取最新的数据，所以不会从快照中读，因此无法保证一致性。
- InnoDB 采用 MVCC + Next-Key Locks 的方式来完全避免幻影读，Next-Key Locks 结合了 Record Locks 和 Gap Locks，不仅锁定了具体的行索引，也锁定索引之间的间隙，相当于锁定了一个闭区间，锁定期间不允许其他事务在这个区间执行DELETE、INSERT、UPDATE操作。

关系数据库设计范式

假设A是键码，B是属性，对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
第一范式(1NF)：属性不可分。一条数据的每个属性都只能有一个值。
第二范式(2NF)：在第一范式的基础上，要求非主属性完全函数依赖于键码。比如包含姓、名、年龄三个字段的表，以姓+名为键码能够索引到唯一的年龄，而对于该键码的真子集，都不能索引到唯一的年龄(一个姓对应好几个年龄)，所以年龄完全依赖于姓+名。
第三范式(3NF)：在第二范式的基础上，要求非主属性不传递函数依赖于键码。比如包含姓、名、身份证号、年龄四个字段的表，以姓+名为键码能够索引到唯一的年龄和身份证号，而且满足第二范式，但由于身份证号也能索引到唯一的年龄，所以不满足第三范式。第三范式意味着所有非键属性必须相互独立。

SQL语法简要

创建数据库

CREATE DATABASE test;
USE test;

创建表

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

修改表

ALTER TABLE mytable ADD col CHAR(20);-- 添加列

ALTER TABLE mytable DROP COLUMN col;-- 删除列

DROP TABLE mytable;-- 删除表

插入

INSERT INTO mytable(col1, col2) VALUES(val1, val2);-- 插入数据

INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;-- 从一个表获取数据插入到另一个表

CREATE TABLE newtable AS SELECT * FROM mytable;-- 从一个表获取数据建立新的表

更新

UPDATE mytable SET col = val WHERE id = 1;

删除

DELETE FROM mytable WHERE id = 1;-- 删除一行
TRUNCATE TABLE mytable;-- 清空整张表

查询

# DISTINCT: 相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。
SELECT DISTINCT col1, col2 FROM mytable;

# LIMIT: 限制返回的行数。第一个参数为起始行，不写的话默认从 0 开始；第二个参数为返回的总行数。
SELECT * FROM mytable LIMIT 5;-- 返回前 5 行，省略第一个参数
SELECT * FROM mytable LIMIT 0, 5;-- 返回前 5 行
SELECT * FROM mytable LIMIT 2, 3;-- 返回第 3 ~ 5 行

排序

# ORDERBY 的字段，越靠前的字段，排序优先级越高。
SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

过滤

# WHERE 可用的条件符号有=、<、>、!=或<>、<=或!>、>=或!<、BETWEEN、IS NULL
# AND 和 OR 用于连接多个过滤条件。IN 用于匹配一组值，其后也可以接一个 SELECT 子句。NOT 操作符用于否定一个条件。
SELECT * FROM mytable WHERE col IS NULL;

通配符

# 使用 Like 进行通配符匹配。% 匹配任意个字符，_ 匹配 1 个任意字符，[] 匹配集合内的字符， ^ 可以对其进行否定，也就是不匹配集合内的字符。
SELECT * FROM mytable WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

计算字段

SELECT col1 * col2 AS alias FROM mytable;-- AS 用于取别名
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;-- CONCAT 用于拼接字段，TRIM 用于去除首尾空格

内置函数

SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

分组

SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2 GROUP BY col HAVING num >= 2;
# 先用 WHERE 筛选 col 列的值
# 再用 GROUP BY 对筛选出的值分组，把相同的值归为一组，如果没有 GROUP BY ，默认所有结果是一组
# 最后用 HAVING 对 SELECT 的结果做筛选

子查询

# 子查询中只能返回一个字段的数据
SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

连接

# 连接可以替代子查询，比子查询效率高，语法也更精简

# 内连接：仅保留满足等值判断的行
SELECT A.value, B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key = B.key;
SELECT A.value, B.value FROM tablea AS A, tableb AS B WHERE A.key = B.key;-- 没写INNER JOIN，默认就是内连接

# 自连接：与自己的内连接
SELECT name FROM employee WHERE department = ( SELECT department FROM employee WHERE name = "Jim");-- 子查询写法
SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2 ON e1.department = e2.department AND e2.name = "Jim";

# 自然连接：内连接的特例，不指定连接的列，默认把两个表所有同名列都连接起来
SELECT A.value, B.value FROM tablea AS A NATURAL JOIN tableb AS B;

# 外连接：保留满足等值判断的行，同时选择性地保留不满足等值判断的行。根据保留哪张表，可以分为左外连接、右外连接和全外连接
SELECT Customers.cust_id, Customer.cust_name, Orders.order_id FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

组合查询

# 把两条查询语句的结果纵向拼接在一起(横向的叫连接)
SELECT col FROM mytable WHERE col = 1 UNION SELECT col FROM mytable WHERE col =2;

视图

# 视图是虚拟的表，用于简化SQL操作。此外，通过只给用户访问视图的权限，可以保证数据的安全性。
CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

存储过程

# 相当于自定义批处理函数，实现代码复用
# 定义存储过程之前要使用 delimiter 修改分隔符，让命令行忽视掉其中的分号，定义完还要把分隔符改回分号
# 包含 in、out 和 inout 三种参数。给变量赋值都需要用 select into 语句。

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

call myprocedure(@ret);
select @ret;

游标

# 在存储过程中使用游标可以对一个结果集进行移动遍历

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;
        # 定义游标
        declare mycursor cursor for select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1 ，用于退出下面的 repeat 循环
        declare continue handler for sqlstate '02000' set done = 1;
        # 打开游标
        open mycursor;
        repeat
            # 根据游标取数据
            fetch mycursor into ret;
            select ret;
        until done end repeat;
        # 关闭游标
        close mycursor;
    end //
 delimiter ;

触发器

# DELETE、INSERT、UPDATE 三种操作可以绑定触发器，BEFORE 和 AFTER 用于指定触发时机
# MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。
# INSERT 触发器包含一个名为 NEW 的虚拟表，DELETE 触发器包含一个名为 OLD 的虚拟表，UPDATE 触发器同时包含了 NEW 和 OLD 。其中 NEW 可以读写，而 OLD 是只读的。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable FOR EACH ROW SELECT NEW.col into @result;
SELECT @result; -- 获取结果

事务管理

# BEGIN 或 START TRANSACTION 显式地开启一个事务，COMMIT 提交事务，ROLLBACK 回滚事务，SAVEPOINT identifier 创建回滚的锚点

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

字符集

# CHARACTER 可以分别对字段和整张表指定字符集，COLLATE 用于指定排序规则，比如 latin1_general_ci 就是 latin 字符集下的一种排序规则。

CREATE TABLE mytable (col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci ) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

权限管理

# MySQL 的账户信息保存在 mysql 数据库中。

CREATE USER myuser IDENTIFIED BY 'mypassword';
RENAME USER myuser TO newuser;-- 修改用户名户
DROP USER myuser;-
SHOW GRANTS FOR myuser;-- 查看权限
GRANT SELECT, INSERT ON mydatabase.* TO myuser;-- 授予权限
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;-- 删除权限
SET PASSWROD FOR myuser = Password('new_password');-- 更改密码，必须使用 Password() 函数进行加密

MySQL

MyISAM & InnoDB

MyISAM 只有表级锁，InnoDB 支持行级锁和表级锁，默认为行级锁。
MyISAM 不一定比 InnoDB 快。
MyISAM 支持全文检索，但5.6之后InnoDB也支持了。
InnoDB 支持事务。
InnoDB 支持外键。
InnoDB 支持MVCC。
InnoDB 支持在线热备份。

表级锁 & 行级锁

表级锁：MySQL中锁定粒度最大的一种锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。但触发锁冲突的概率最高，并发度最低。
行级锁：MySQL中锁定粒度最小的一种锁，能大大减少锁冲突，提高并发度。但加锁开销大，加锁慢，会出现死锁。 InnoDB支持三种行级锁：
- Record Lock: 对索引项加锁，锁定符合条件的行。
- Gap Lock: 对索引项之间的间隙加锁，锁定记录的范围，不包含索引项本身。其他事务不能在锁范围内插入数据，这样就避免了幻影读。
- Next-key Lock：锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可避免幻影读。

大表优化

单表优化：不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度。一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。
- 字段：
  - 尽量使用TINYINT、SMALLINT、MEDIUM_INT而非INT
  - VARCHAR只分配需要的长度
  - 使用枚举或整数代替字符串类型
  - 使用TIMESTAMP而非DATETIME
  - 避免使用NULL字段，因为NULL需要更多的存储空间并且无法参与某些运算。
- 索引：
  - 索引不是越多越好，根据需要创建
  - 避免在WHERE子句进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描$\color{red}{(存疑)}$
  - 取值有限的字段不适合建索引，比如性别
  - 字符字段只建前缀索引
  - 字符字段最好不要做主键
  - 尽量不用外键和UNIQUE，由程序保证约束
- 查询语句：
  - 不要做列运算，任何对列的操作都将导致表扫描，应该尽可能将计算移至等号右边。比如把 SELECT id WHERE age+1=10 改成 SELECT id WHERE age=10-1
  - 语句尽可能简单，把大语句拆小语句，减少锁时间与锁竞争
  - 不用 SELECT *
  - 不用函数和触发器，在应用程序上实现
  - 少用 JOIN
  - 尽量不在WHERE子句中使用!=或<>，否则将导致引擎放弃使用索引而进行全表扫描$\color{red}{(存疑)}$
  - 对于连续数值的范围，使用BETWEEN而非IN
  - 不要一次读整张表，要使用LIMIT来分页
- 引擎：MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表
读写分离：从库负责读，主库负责写，一般不要采用双主或多主引入很多复杂性。
缓存：
- MySQL内部：调整缓存相关的系统调优参数
- 数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象
- 应用服务层：通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象
- Web层：针对web页面做缓存
- 客户端：浏览器的缓存
垂直拆分：把一个多字段的大表按常用字段和非常用字段进行拆分，每个表的行数相同，只是字段不一样，使用主键关联。
- 优点：
  - 可以使单行数据变小，一个数据块(Block)能存放更多的数据，在查询时就会减少I/O次数(每次查询需要读取的Block变少)
  - 可以最大化利用Cache，因为不需要保存非常用字段
  - 数据维护简单
- 缺点：
  - 主键出现冗余，需要管理冗余列
  - 可能需要频繁执行 JOIN 操作
  - 依然存在单表数据量(行数)过大的问题
  - 事务处理复杂，因为要处理多个表
水平拆分：把一个行数多的大表拆成多个行数少的表，每个表的字段完全一样。有库内分表和分库两种实现，库内分表仅仅解决了单一表数据过大的问题，由于没有把多个表分布到不同的机器上，对于减轻MySQL服务器的压力来说没有太大帮助。
- 优点：
  - 不存在单库大数据和高并发的性能瓶颈
  - 提高了系统的稳定性和负载能力
- 缺点：
  - 分片事务一致性难以解决
  - 跨节点Join性能差，逻辑复杂
  - 数据多次扩展难度跟维护量极大

数据库连接池

数据库连接本质上就是一个 socket 的连接。与线程池类似，数据库连接池就是数据库连接的缓存，收到对数据库的连接请求时可以重用这些连接。为每个新用户都创建和维护数据库连接，代价昂贵、浪费资源，用户还必须浪费时间先等待与数据库建立连接。在连接池中，可以复用旧的连接，因而大大降低了创建新连接的频率。

MySQL 基础架构

Connectors：负责通过MySQL协议与MySQL Server建立TCP连接，一般都是调用各个编程语言的SDK，比如JDBC。
Connection Management：MySQL会为每一个客户端连接绑定一个管理线程，通常会使用线程池。管理线程负责管理客户端链接，对客户端进行认证，认证基于用户名、主机名、密码。如果用了SSL或者TLS的方式进行连接，还会进行证书认证。
SQL Interface：负责提供 DML(数据操作语言)、DDL(数据定义语言)、存储过程、视图、触发器、自定义函数等SQL语言接口。
Parser：负责解析SQL语句，并为其创建语法树，同时验证该客户端是否具有执行该操作的权限。还会对SQL查询语句做语法优化，进行查询重写。
Optimizer：语法解析和查询重写之后，MySQL会根据语法树和数据的统计信息对SQL进行优化，包括决定表的读取顺序、选择合适的索引等，最终生成SQL的具体执行步骤。
Caches & Buffers：缓存 SELECT 语句以及该语句的结果集，MySQL 8.0 版本删除缓存功能，可能是觉得没什么用吧。
Pluggable Storage Engine：存储引擎的具体实现，这些存储引擎都实现了MySQL定义好的存储引擎API的部分或者全部。MySQL可以动态安装或移除存储引擎，可以有多种存储引擎同时存在。存储引擎负责在文件系统之上，管理表的数据、索引、Cache、Buffer、事务、Log等数据和功能。

SQL语句的执行过程

建立连接：SQL客户端与与服务器建立TCP连接，该请求被发送到Connection Management ，连接成功后会验证权限等。MySQL服务器与客户端之间的通信是半双工的。
查询缓存：MySQL8.0 以前缓存功能，如果查询命中缓存(哈希查找)则直接返回结果，返回结果前还会检查一次用户权限。如果没有命中缓存，则进行SQL解析。
语法解析器和预处理：Parser 通过MySQL关键字解析语句，生成语法解析树，检查是否有语法错误。预处理器则根据MySQL的规则进一步检查语法，如库表是否存在、字段是否存在等。
查询优化器：SQL语句在 Optimizer 中转换成执行计划，一条SQL语句可以有多种方式执行，不同查询方式的性能和效果不同，Optimizer 的作用就是选择合适的执行计划。MySQL使用基于成本的优化器，通常是选择成本最小的方案。
执行计划：MySQL的执行计划实际上是一颗指令树，存储引擎自底向上执行指令并返回结果。
执行SQL：在解析和优化阶段，MySQL生成了执行计划，由执行计划调用存储引擎的API来执行相应操作，最后将结果返回给客户端。
更新语句：相比于查询语句，INSERT、UPDATE、DELETE等语句还会修改数据，修改过程需要用到存储引擎的日志模块。MySQL 自带的日志模块是 binlog(归档日志)，InnoDB 的日志模块是 redo log(重做日志)，修改数据同时用到了这两个模块，大致流程是：
- 先获取要修改的数据，其实就是查询语句。
- 调用引擎 API 接口修改数据，同时记录 redo log，此时 redo log 进入 prepare 状态，然后通知执行器可以提交了。
- 执行器收到通知后记录 binlog，然后调用引擎接口提交， redo log 变为提交状态。
- 因为 InnoDB 需要通过 redo log 来支持事务，MySQL 底层又必须使用原生的 binlog，所以两个模块只能都使用。
- 之所以采用“记录redo log->记录binlog->提交binlog->提交redo log” 这样的两段式提交，是为了保证两个日志要么都提交要么都不提交，如果机器恰好在两个日志提交的间隔挂掉了，机器重启后MySQL将根据两个日志的状态和完整性来决定是否提交。总之，两段式提交+MySQL异常处理机制，可以保证数据一致性。

一条SQL语句执行得很慢的原因有哪些？

大多数情况下很正常，偶尔很慢
- 数据库在刷新脏页
  - 数据库的更新并不会立即同步持久化到磁盘中去，而是先把这些更新写入到 redo log 日志中，等到系统空闲时再通过 redo log 把最新数据同步到磁盘。当内存数据页跟磁盘数据页内容不一致时，就称这个内存页为“脏页”，内存数据页是新数据，磁盘上的是旧数据，所以刷新脏页就是把内存的数据写到磁盘。
  - 如果系统不空闲时还在疯狂刷新脏页，一般有两种可能。一是 redo log 满了，系统必须暂停其他操作，专心同步数据。二是内存满了，系统需要淘汰一部分内存数据页，如果其中恰好有大量脏页，就要在淘汰前先写到磁盘。
- 执行的时候遇到锁。
一直就很慢
- 没有用上索引。分两种情况：
  - 本来就没有给字段添加索引。
  - 字段有索引，但由于一些特殊情况(上面的大表优化里写了)，系统放弃使用索引，进行全表扫描。
- 数据库选错了索引。
  - 主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而是存放主键字段的值。
  - 当使用非主键索引时，会查询到对应主键的值，然后再根据主键的值使用主键索引，查询到整行数据返回。当满足查询条件的行数超过一定比例时，这种索引查询有可能比直接扫描全表还要慢。所以在执行查询语句时，系统会预先估计到底是使用索引快还是扫描全表快，因此就算有索引系统也不一定会用到。
  - 系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。所以索引的基数越大，意味着走索引查询越有优势。然而系统是通过采样来估计索引区分度的，如果采样的结果刚好基数很小，系统就会放弃使用索引。所以为了避免这种统计失误，最好在查询语句中强制使用索引。

索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有B树、B+树和Hash。使用索引可以大大提升数据检索速度，但代价是创建和维护索引的耗时，以及存储索引的额外空间。
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
索引的原理：数据库里的数据默认采用无序链表的结构进行组织，使用索引后，不仅可以把数据按顺序组织，还采用了搜索效率更高的树结构。但具体有没有用树结构直接替换原来的链表结构，要看存储引擎的内部实现。
Inodb存储引擎默认是B+ 树索引，MyISAM 存储引擎默认是Fulltext索引，Memory 存储引擎默认是Hash索引。
B树 & B+树：
- 都是有序的平衡多叉树。
- B树的所有节点既存放key也存放data。B+树只有叶节点存放 key 和 data，非叶节点只存放key。
- B树的叶节点都是独立的。B+树的叶节点有一条引用链指向与它相邻的叶节点(经典B+树是没有顺序访问指针的，数据库使用的B+树是对经典的优化)。
- B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶节点，检索就结束了。而B+树的检索效率就很稳定，任何查找都是从根节点到叶子节点的过程。
为什么不使用二叉树作为索引结构?：
- 平衡二叉树、红黑树等结构是常用的排序树，但要索引存储在磁盘上的数据时，他们都不适合，就是因为分叉少(也就是节点出度小)。
- 一方面，数据库的数据量很大，当节点数量相同时，二叉树的高度比多叉树高很多，当高度差距到一定程度时，二叉树的查找效率就不如多叉树了。
- 另一方面，整棵索引树通常是很大的，存储在磁盘上，通常每次只能从磁盘中读取一个磁盘页的数据到内存中，这种情况下，磁盘I/O会成为主要的性能瓶颈。由于考虑到局部性原理，系统每次读取一个磁盘页的数据时都会顺便把其相邻的数据也读到内存中，这叫做磁盘局部预读。树结构在磁盘中是按照层次顺序存储在一维数组中的，也就意味着数组中相邻的节点就是树的某一层的相邻节点。树的分支越少，树的高度就越高，对于排序树而言，就会导致高层里相邻的两个节点往往在实际顺序中相距很远。因此，如果用二叉树作为索引，局部预读读到的相邻索引节点实际距离很大，结果反而与局部性原理背道而驰，使得系统读取了非局部数据，需要更频繁的磁盘I/O操作来置换内存中的数据。
为什么B+树比B树更适合作为索引结构？
- 二叉树没能充分利用磁盘预读功能，而B树和B+树都是为了充分利用磁盘预读功能而创建的一种数据结构。
- 如果想遍历数据库中连续的一段数据，基于B树需要中序遍历，而基于B+树只需要沿着指针扫一段叶节点。在数据库中基于范围的查询是非常频繁的，所以采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历效率低下的问题。
为什么B+树比Hash表更适合作为索引结构？
- Hash表的优点是定位数据快。
- Hash表的小缺点是哈希碰撞，但并不会十分影响查询效率。
- Hash表的最大缺点是不支持顺序和范围查询，B树好歹只是效率低，而Hash表直接就不支持了。
索引类型：
- 主键索引：主键列使用的就是主键索引。
- 二级索引：二级索引的叶子节点存储的data是主键，所以二级索引用于定位主键的位置，定位数据还需要通过主键索引。
- 聚簇索引：聚簇索引是索引结构和数据一起存放的索引，所以主键索引属于聚簇索引。
  - 优点：查询速度非常快，定位到索引节点就同时定位到了数据。
  - 缺点：依赖于有序的数据，如果索引的数据不是有序的，就需要在插入时排序。更新代价大，数据和索引必须同时修改。
- 非聚簇索引：非聚簇索引即索引结构和数据分开存放的索引，所以二级索引属于非聚簇索引。
  - 优点：更新代价比聚簇索引要小。
  - 缺点：也依赖于有序的数据。需要二次查询，即先定位主键再定位数据，但有些特殊情况只需要查询一次，比如key和data相同，或者查询的就是主键而不是数据。
- 覆盖索引：如果一个索引包含所有需要查询的字段的值，就称之为覆盖索引，其实就是上面说的key和data相同的情况。
最左前缀原则：使用联合索引(多列属性组成的索引)时，尽量把查询最频繁的字段作为最左(第一个)字段，查询的时候也尽量以这个字段为第一条件。
MyISAM & InnoDB:
- MyISAM 的索引文件和数据文件是分离的，采用非聚簇索引，叶节点的data域存放的是数据记录的地址。
- InnoDB 的数据文件本身就是按B+树结构组织的，所以索引文件和数据文件是一体的。采用聚簇索引，叶节点的data域存放的是数据的值而不是地址。

概念

事务

并发一致性问题

封锁

隔离级别

多版本并发控制

关系数据库设计范式

SQL语法简要

创建数据库

创建表

修改表

插入

更新

删除

查询

排序

过滤

通配符

计算字段

内置函数

分组

子查询

连接

组合查询

视图

存储过程

游标

触发器

事务管理

字符集

权限管理

MySQL

MyISAM & InnoDB

表级锁 & 行级锁

大表优化

数据库连接池

MySQL 基础架构

SQL语句的执行过程

一条SQL语句执行得很慢的原因有哪些？

索引

Redis