MySQL学习笔记

  • Z时代
  • 2024-01-10
  • 分类：综合

database

作者： Grey

说明

注：本文中的SQL语句如果用到了特定方言，都是基于MySQL数据库。

关于DDL

DDL 的英文全称是 Data Definition Language，中文是数据定义语言。它定义了数据库的结构和数据表的结构。在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。

需要注意的是：在执行 DDL 的时候，不需要 COMMIT，就可以完成执行任务。

排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感。

DISTINCT

DISTINCT 其实是对后面所有列名的组合进行去重

SELECT DISTINCT attack_range, name FROM heros

其实是对(attack_range,name)这个组合去重。

LIMIT

另外在查询过程中，我们可以约束返回结果的数量，使用 LIMIT 关键字。

SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

如果是 DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字：

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

如果是 Oracle，你需要基于 ROWNUM 来统计行数：

SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC

需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序, 如果这样写：

SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5

就表示先执行查询结果，再来过滤结果中的前五条。

WHERE语句中 AND 和 OR优先级

WHERE 子句中同时出现 AND 和 OR 操作符的时候，你需要考虑到执行的先后顺序，也就是两个操作符执行的优先级。一般来说 () 优先级最高，其次优先级是 AND，然后是 OR。

SQL中的命名规范

MySQL 在 Linux 的环境下，数据库名、表名、变量名是严格区分大小写的，而字段名是忽略大小写的。

而 MySQL 在 Windows 的环境下全部不区分大小写。

SQL编写的一个规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写

  • SQL保留字、函数名、绑定变量等都大写

  • 数据表的字段名推荐采用下划线命名

  • SQL语句必须以分号结尾

COUNT(字段) , COUNT( * ) 和 COUNT(1)

COUNT(字段)会忽略字段值值为 NULL 的数据行，而 COUNT( * ) 和 COUNT(1) 只是统计数据行数，不管某个字段是否为 NULL。

AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行。

关联子查询和非关联子查询

可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询。子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询

一个非关联子查询的例子：

SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

一个关联子查询的例子：

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

(NOT) EXISTS子查询

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

IN VS EXISTS

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

实际上在查询过程中，在我们对 cc 列建立索引的情况下，我们还需要判断表 A 和表 B 的大小。在这里例子当中，表 A 指的是 player 表，表 B 指的是 player_score 表。如果表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高，因为这时 B 表中如果对 cc 列进行了索引，那么 IN 子查询的效率就会比较高。同样，如果表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高，因为我们可以使用到 A 表中对 cc 列的索引，而不用从 B 中进行 cc 列的查询。

当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

当 B 小于 A 时用 IN，因为实现的逻辑类似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN。

其他一些子查询的关键字：EXISTS、IN、ANY、ALL 和 SOME

函数

一个简单的函数例子：Leetcode 177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee GROUP BY Salary
      ORDER BY Salary DESC LIMIT 1 OFFSET N
  );
END

Employee表数据如下：

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

函数调用：

SELECT getNthHighestSalary(3);

结果：

+------------------------+
| getNthHighestSalary(3) |
+------------------------+
| 100                    |
+------------------------+

存储过程

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

调用

CALL add_num(10);

另一个例子

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

调用

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, "战士");
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

如何使用游标

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;
       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量
     DECLARE done INT DEFAULT false;
       -- 定义游标
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       -- 指定游标循环结束时的返回值
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
       OPEN cur_hero;
       read_loop:LOOP
       FETCH cur_hero INTO hp;
       -- 判断游标的循环是否结束
       IF done THEN
                     LEAVE read_loop;
       END IF;
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

更复杂的一个例子

CREATE PROCEDURE `alter_attack_growth`()
BEGIN
       -- 创建接收游标的变量
       DECLARE temp_id INT;
       DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;
       -- 创建结束标志变量
       DECLARE done INT DEFAULT false;
       -- 定义游标
       DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
       -- 指定游标循环结束时的返回值
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
       OPEN cur_hero;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       REPEAT
                     IF NOT done THEN
                            SET temp_diff = temp_max - temp_start;
                            IF temp_growth < 5 THEN
                                   IF temp_diff > 200 THEN
                                          SET temp_growth = temp_growth * 1.1;
                                   ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
                                          SET temp_growth = temp_growth * 1.08;
                                   ELSEIF temp_diff < 150 THEN
                                          SET temp_growth = temp_growth * 1.07;
                                   END IF;
                            ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
                                   SET temp_growth = temp_growth * 1.05;
                            END IF;
                            UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
                     END IF;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       UNTIL done = true END REPEAT;
       CLOSE cur_hero;
END

自动提交（autocommit）

set autocommit =0;  //关闭自动提交

set autocommit =1;  //开启自动提交

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT "关羽";
COMMIT;
BEGIN;
INSERT INTO test SELECT "张飞";
INSERT INTO test SELECT "张飞";
ROLLBACK;
SELECT * FROM test;

"张飞"这条记录，如果数据库未开启自动提交，则不会入库，如果开启了自动提交，则第二个”张飞“输入会回滚不插入，但是第一条”张飞“数据依然会插入。

completion_type

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT "关羽";
COMMIT;
INSERT INTO test SELECT "张飞";
INSERT INTO test SELECT "张飞";
ROLLBACK;
SELECT * FROM test;

MySQL 中 completion_type 这个参数有 3 种可能：

  • completion=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。

  • completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务）。

  • completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。

关于事务

  1. 脏读（Dirty Read）

读到了其他事务还没有提交的数据。

  1. 不可重复读（Nnrepeatable Read）

对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。

  1. 幻读（Phantom Read）

事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。

SQL-92 标准还定义了 4 种隔离级别来解决这些异常情况。

这些隔离级别能解决的异常情况如下表所示：

脏读

不可重复读

幻读

读未提交（READ UNCOMMITTED）

允许

允许

允许

读已提交（READ COMMITTED）

禁止

允许

允许

可重复读（REPEATABLE READ）

禁止

禁止

允许

可串行化（SERIALIZABLE

禁止

禁止

禁止

  1. 读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。

  2. 读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句

  3. 可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。MySQL 默认的隔离级别就是可重复读。

  4. 可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。

“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。

查看隔离级别

SHOW VARIABLES LIKE "transaction_isolation";

配置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

关于MVCC

多版本并发控制技术，是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

通过 MVCC 我们可以解决以下几个问题：

  1. 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。

  2. 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。

  3. 解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

使用了如下内容来实现：

  • 事务版本号

  • 行记录的隐藏列

  • Undo Log

InnoDB 中，MVCC 是通过 Undo Log + Read View 进行数据读取，Undo Log 保存了历史快照，而 Read View 规则帮我们判断当前版本的数据是否可见。需要说明的是，在隔离级别为读已提交（Read Commit）时，一个事务中的每一次 SELECT 查询都会获取一次 Read View。

出现幻读的原因是在读已提交的情况下，InnoDB 只采用记录锁（Record Locking）。这里要介绍下 InnoDB 三种行锁的方式：记录锁：针对单个行记录添加锁。间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。Next-Key 锁：帮我们锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题。在隔离级别为可重复读时，InnoDB 会采用 Next-Key 锁的机制，帮我们解决幻读问题。

为什么建议尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

一天一备 VS 一周一备

在一天一备的模式里，最坏情况下需要应用一天的 binlog。比如，你每天 0 点做一次全量备份，而要恢复出一个到昨天晚上 23 点的备份。一周一备最坏情况就要应用一周的 binlog 了。

关于数据库调优

导图引用自20丨当我们思考数据库调优的时候，都有哪些维度可以选择？

关于各种范式

1NF 指的是数据库表中的任何属性都是原子性的，不可再分。

2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性。

一个没有满足 2NF 的例子，

一张球员比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，

这里候选键和主键都为:

（球员编号，比赛编号），

我们可以通过候选键来决定如下的关系：

(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

上面这个关系说明球员编号和比赛编号的组合决定了球员的姓名、年龄、比赛时间、比赛地点和该比赛的得分数据。

但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

(球员编号) → (姓名，年龄)
(比赛编号) → (比赛时间, 比赛场地)

也就是说候选键中的某个字段决定了非主属性。

插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。

删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。

更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。比如：


你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合 3NF 的要求。

关于索引

索引模型

Hash索引

适用于只有等值查询的场景，因为不是有序的，所以做范围查询的速度是很慢的。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀, 有序数组索引只适用于静态存储引擎

二叉搜索树

查询复杂度是：O(log(N)) ，需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

B树和B+树

先看单次查询。为了尽可能快的命中数据，我们希望尽可能的将更多的索引数据存储在内存中。b树有一个特点，每一层都会存储真正的数据，这会挤压索引可用的内存空间，从而在整体上增加io次数。另外，如果只看等值查询的话，树型索引是不如hash索引的。 其次，关系数据库中还会大量使用范围查询、有序查询等，比如某时间范围内的用户交易数据。范围查询，这种查询的特点是会大量使用排序，比较，返回结果也往往是多条。 如果使用b树的话，需要使用中序遍历，因为数据节点不在同一层上，会频繁引起io，从而导致整体速度下降。而在b+树中，所有的数据节点都在叶子节点，相近的叶子节点之间也存在着链接，因此会节约io时间。这样，b+树整体上就比b树要快。 其实，b+树主要应用于关系型数据库中。也有使用b树做索引的数据库，比如mangodb。

MySQL 的 InnoDB 存储引擎还有个“自适应 Hash 索引”的功能，就是当某个索引值使用非常频繁的时候，它会在 B+ 树索引的基础上再创建一个 Hash 索引，这样让 B+ 树也具备了 Hash 索引的优点

索引的分类

功能上分：普通索引，唯一索引，主键索引，全文索引

基于主键索引和普通索引的查询有什么区别？(ID是主键索引，k是普通索引)

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。

这个过程称为回表。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

物理结构上：聚集索引(顺序)和非聚集索引(非顺序)，类比链表和数组的区别。

字段上分：单一索引和联合索引（最左匹配原则）

  • 聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

  • 一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。

  • 使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。实验 3：使用聚集索引和非聚集

什么时候创建索引，什么时候不应该创建索引？

创建索引

  1. 字段唯一

  2. WHERE频繁查询

  3. 经常GROUP BY或者ORDER BY的列

  4. DISTINCT字段

不适合的情况

  1. 频繁更新的字段

  2. 重复数据比较多的字段

  3. WHERE用不到的字段

分析一下哪些场景下应该使用自增主键，而哪些场景下不应该？

自增主键每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，还可以从存储空间的角度来看。

假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？

比如典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

什么时候索引失效

  1. 索引字段使用了表达式

  2. 使用函数

  3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

  4. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

  5. 最左原则

数据库中的存储结构

数据库管理存储空间的基本单位是页（Page），一页中可以存储多行记录, InnoDB中页大小查看

show variables like "%innodb_page_size%";

我们在分配空间的时候会按照页为单位来进行分配，同一棵树上同一层的页与页之间采用双向链表，而在页里面，记录之间采用的单向链表的方式。

区（Extent）是比页大一级的存储结构，InnoDB 中，页大小默认是 16KB, 一个区会分配 64 个连续的页。所以一个区的大小是:

64*16KB=1MB。

段（Segment）由一个或多个区组成，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。在 InnoDB 中存在两种表空间的类型：

共享表空间和独立表空间。

如果是共享表空间就意味着多张表共用一个表空间。

如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

show variables like "innodb_file_per_table";

关于数据库缓冲池

-- 每个缓冲池多大
show variables like "innodb_buffer_pool_size"
-- 可以开启多个缓冲池
show variables like "innodb_buffer_pool_instances"
-- 获取查询页的数量
SHOW STATUS LIKE "last_query_cost";

三星索引

  1. 在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；

  2. 将 GROUP BY 和 ORDER BY 中的列加入到索引中；

  3. 将 SELECT 字段中剩余的列加入到索引片中。

关于数据库中的锁

按锁粒度划分

  1. 行锁

  2. 表锁

  3. 页锁

不同的数据库和存储引擎支持的锁粒度不同，InnoDB 和 Oracle 支持行锁和表锁。而 MyISAM 只支持表锁，MySQL 中的 BDB 存储引擎支持页锁和表锁。SQL Server 可以同时支持行锁、页锁和表锁。

我们还可以从数据库管理的角度对锁进行划分。共享锁和排它锁

加共享锁：

LOCK TABLE product_comment READ;

解锁：

UNLOCK TABLE;

加排他锁

LOCK TABLE product_comment WRITE;

解锁

UNLOCK TABLE;

意向锁（Intent Lock），简单来说就是给更大一级别的空间示意里面是否已经上过锁。

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。同理，事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录，不能对整个表进行全表扫描。

从程序员角度划分

  • 乐观锁：通过版本号或者时间戳来控制

  • 悲观锁：对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

  1. 如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获取，这样可以减少死锁发生的概率；

  2. 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；

  3. 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

当然在数据库中，也有一些情况是不会发生死锁的，比如采用乐观锁的方式。另外在 MySQL MyISAM 存储引擎中也不会出现死锁，这是因为 MyISAM 总是一次性获得全部的锁，这样的话要么全部满足可以执行，要么就需要全部等待。

使用 MySQL InnoDB 存储引擎时，为什么对某行数据添加排它锁之前，会在数据表上添加意向排他锁呢？

因为要告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。而不需要进行全表的扫描，节省时间，提高效率！

一条SQL的执行流程

CBO VS RBO

第一种是基于规则的优化器（RBO，Rule-Based Optimizer），规则就是人们以往的经验，或者是采用已经被证明是有效的方式。通过在优化器里面嵌入规则，来判断 SQL 查询符合哪种规则，就按照相应的规则来制定执行计划，同时采用启发式规则去掉明显不好的存取路径。

第二种是基于代价的优化器（CBO，Cost-Based Optimizer），这里会根据代价评估模型，计算每条可能的执行计划的代价，也就是 COST，从中选择代价最小的作为执行计划。相比于 RBO 来说，CBO 对数据更敏感，因为它会利用数据表中的统计信息来做判断，针对不同的数据表，查询得到的执行计划可能是不同的，因此制定出来的执行计划也更符合数据表的实际情况。

但我们需要记住，SQL 是面向集合的语言，并没有指定执行的方式，因此在优化器中会存在各种组合的可能。我们需要通过优化器来制定数据表的扫描方式、连接方式以及连接顺序，从而得到最佳的 SQL 执行计划。

你能看出来，RBO 的方式更像是一个出租车老司机，凭借自己的经验来选择从 A 到 B 的路径。而 CBO 更像是手机导航，通过数据驱动，来选择最佳的执行路径。

5.7.10 版本之后，MySQL 会引入两张数据表，里面规定了各种步骤预估的代价（Cost Value） ，我们可以从mysql.server_cost和mysql.engine_cost这两张表中获得这些步骤的代价

定位慢SQL方法

  • mysqldumpslow

  • EXPLAIN 查看执行计划

  • SHOW PROFILE 查看开销

主从同步原理

提到主从同步的原理，我们就需要了解在数据库中的一个重要日志文件，那就是 Binlog 二进制日志，它记录了对数据库进行更新的事件。实际上主从同步的原理就是基于 Binlog 进行数据同步的。

在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地形成中继日志（Relay log）。从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，从而将从库中的数据与主库保持同步。

如何解决主从不一致问题

  • 异步复制

  • 半同步复制

MySQL5.5 版本之后开始支持半同步复制的方式。原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了 Binlog，并且写入到中继日志中，再返回给客户端。这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。在 MySQL5.7 版本中还增加了一个rpl_semi_sync_master_wait_for_slave_count参数，我们可以对应答的从库数量进行设置，默认为 1，也就是说只要有 1 个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。

  • 组复制（MySQL 5.7.17 以后 ，基于Paxos）

MySQL的备份与恢复

方式1：

innodb_force_recovery

方式2：

Linux下MySQL数据库的备份与恢复

Redo Log VS BinLog

Redo Log是引擎层日志

使用了WAL技术，先写日志，再写磁盘，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

redo log是固定大小的，可以配置一组4个文件，每个文件大小1GB，那么可以记录4G记录。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog是Server层日志

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。redo log 是循环写的，空间固定会用完；

  3. binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交

MySQL在执行这段更新操作的时候：

update T set c=c+1 where ID=2;

图片引用自：MySQL实战45讲

操作过程如上图: 图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。最后三步将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

为什么要使用两阶段提交？如果不使用，就会出现如下情况：

  1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

参考文档

MySQL实战45讲

SQL必知必会

以上是 MySQL学习笔记 的全部内容， 来源链接： utcz.com/z/535857.html

回到顶部