MySQL

MySQL是一个开源的关系型数据库管理系统（RDBMS），广泛用于Web应用程序和中小型企业数据库。默认端口是 3306。

启动并连接 MySQL

  • 启动 MySQL 服务器（然后尝试连接到 MySQL 服务器；一般情况下 服务器不会自动关闭）
    1
    2
    net start MySQL  # 打开命令提示符（以管理员身份运行），运行以下命令来启动 MySQL 服务
    net stop MySQL   # 停止 MySQL 服务
  • 另外，通过 Navicat 创建与本地(或远程) 的 MySQL(或其他)数据库的连接，以对数据库可视化管理
    本机 username: root，password: “0xx1xx”

SQL 术语


关系(Relation)：通常是指数据库表(table)。每个关系对应数据库中的一个表格，由多个行和列组成，其中每一行通常代表表格中的一个数据记录，而每一列代表记录中的一个属性（字段）。
属性(attribute)：列的名字，上图有学号、姓名、班级、兴趣爱好、班主任、课程、授课主任、分数.
依赖 (relation)：列属性间存在的某种联系
元组(tuple)：每一个行，如第二行 (1301，小明，13班，篮球，王老师，英语，赵英，70) 就是一个元组
模式(schema)：这里指逻辑结构，如 学生信息(学号，姓名，班级，兴趣爱好，班主任，课程，授课主任，分数)的笼统表述。（数据库模式是数据库的结构描述，包括表格、字段、关系、视图、索引等元素的组织方式。描述了数据库中不同关系表格之间的关联和数据的组织方式。通常包含了数据库中表格的定义，包括表格的名称、字段的名称、字段的数据类型、主键等信息。）
域 (domain)：数据类型，如string、integer等，上图中每一个属性都有它的数据类型 (即域)
键(key)：由关系的一个或多个属性组成，任意两个键相同的元组，所有属性都相同。需要保证表示键的属性最少。一个关系可以存在好几种键，工程中一般从这些候选键中选出一个作为主键 (primary key)
主键（Primary Key）：主键是一个表格中的一列或一组列，它的值用来唯一标识表格中的每一行记录。主键的值不能重复，且不能为空。这意味着每一行记录在主键列上必须有唯一的值，用于区分记录。主键用于建立表格之间的关系和确保数据的完整性。通常，每个表格都有一个主键，但也可以由多个列组成复合主键
候选键(candidate key)：由关系的一个或多个属性组成，候选键都具备键的特征，都有资格成为主键
超键(super key)：包含键的属性集合，无需保证属性集的最小化。每个键也是超键。可以认为是键的超集。
外键(foreign key)：如果某一个关系A中的一个(组)属性是另一个关系B的键，则该(组)属性在A中称为外键。
主属性 (prime attribute)：所有候选键所包含的属性都是主属性
投影 (proiection)：选取特定的列，如将关系学生信息投影为学号、姓名即得到上表中仅包含学号、姓名的列
选择 (selection)：按照一定条件选取特定元组，如选择上表中分数>80的元组
笛卡儿积 (交叉连接Cross join)：第一个关系每一行分别与第二个关系的每一行组合
自然连接(natural join)：第一个关系中每一行与第二个关系的每一行进行匹配，如果得到有交叉部分则合并，若无交叉部分则舍弃。
连接(theta join)：即加上约束条件的笛卡儿积，先得到笛卡儿积，然后根据约束条件删除不满足的元组.
外连接 (outer join)：执行自然连接后，将舍弃的部分也加入，并且匹配失败处的属性用NULL代替。
除法运算(division)：关系R除以关系S的结果为T，则T包含所有在R但不在S中的属性，且T的元组与S的元组的所有组合在R中。

SQL 语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE DATABASE mydatabase;  /* 创建数据库 */
USE mydatabase;              /* 选择数据库 */
CREATE TABLE users (                   -- 创建表
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);
DROP TABLE users;                      -- 删除表
ALTER TABLE users ADD col CHAR(20);    -- 添加列
ALTER TABLE mytable DROP COLUMN col;   -- 删除列
ALTER TABLE mytable CHANGE col col1 CHAR(32) NOT NULL DEFAULT '123';   -- 修改列和属性
-- 插入数据
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');  
-- 插入检索出来的数据
INSERT INTO mytable1(col1, col2)
-- 更新数据
UPDATE users SET email='newemail@example.com' WHERE username='user1';  
-- 删除数据
DELETE FROM users WHERE username='user1';

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT distinct id 
FROM users;  
LIMIT 2, 3;                     
ORDER BY col1 DESC, col2 ASC;   
WHERE col is NULL;          
-- 子查询：只能返回一个字段的数据，可以将子查询的结果作为 WHRER 语句的过滤条件，配合 (not )in
SELECT * FROM mytable1    
WHERE col1 IN (SELECT col2
               FROM mytable2);
-- 分组：将数据按照一个或多个列的值分成不同的组，常与聚合函数（如 SUM、COUNT、AVG 等）一起以对每个组聚合操作
-- GROUP BY 子句： 用于指定按哪些列进行分组； HAVING 子句： 用于对分组后的数据进行筛选。
-- WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
SELECT col, COUNT(*) AS num   
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2       
ORDER BY SUM(column2) DESC;
  1. SELECT：查询出属性，用 AS 给列名、计算字段和表名取别名，以简化 SQL 语句以及连接相同表；
    select 中 sql 函数计算出的值作为查询出的属性（select round(count()/3, 2) from ..）
    select 中可以加一个select 用于属性计算（select id, count(    )/(select count(*) from Users) per from ..）
    若有重复列，使用 DISTINCT 去除重复值
  2. LIMIT：LIMIT 2, 3 返回第 3 ~ 5 行。配合排序实现获取最大/最小值。。
  3. WHERE：过滤行，AND 和 OR 用于连接多个过滤条件。优先处理 AND，使用 () 决定优先级；
    is 搭配 null，IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
  4. ORDER：可以按多个列进行排序，并指定不同的排序方式，默认升序ASC, 降序DESC
  5. GROUP BY 可以放在 WHERE 前、后，想清楚在分组前、后过滤
  6. 子查询的结果需要指定别名。

连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 连接(内连接)：返回多个表中匹配条件满足的行，不匹配的行不会被包括在结果集中。
-- 使用 (INNER )JOIN 关键字，条件语句使用 ON 而不是 WHERE，连接可以替换子查询且效率一般会更快。
SELECT A.value, B.value
FROM tablea AS A JOIN tableb AS B
ON A.key = B.key;
-- 自连接：可以看成内连接的一种，只是连接的表是自身而已。
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;
-- 外连接：保留了没有关联的那些行。分为左，右外连接以及全外连接。
-- 左连接返回左表的所有行以及与右表匹配的行。如果右表中没有匹配的行，将会返回 NULL 值。
-- on 后等于新连接出了一张表，where 在新表上进行查询
select Employee.name, Bonus.bonus
from Employee left join Bonus
on Employee.empId = Bonus.empId
where Bonus.bonus < 1000 or Bonus.bonus is null

SQL 函数

  1. 数学函数：SUM()：计算指定列的总和。 AVG()：计算指定列的平均值。 MAX()：找出指定列的最大值。 MIN()：找出指定列的最小值。 COUNT()：计算指定列的行数。 ROUND(x,y)：把 x 四舍五入到 y 位小数。 ABS()：返回绝对值。
  2. 字符串函数：CONCAT()：连接两个或多个字符串。 SUBSTRING() 或 SUBSTR()：从字符串中提取子字符串。 LENGTH()：返回字符串的长度。 UPPER()：将字符串转换为大写。 LOWER()：将字符串转换为小写。 TRIM()：去除字符串首尾的空格或其他指定字符。 REPLACE()：替换字符串中的子串。
  3. 日期和时间函数：NOW() 或 CURRENT_TIMESTAMP()：返回当前日期和时间。 DATE()：从日期时间值中提取日期部分。 TIME()：从日期时间值中提取时间部分。 YEAR()：从日期中提取年份。 MONTH()：从日期中提取月份。 DAY()：从日期中提取天。HOUR()：从时间中提取小时。 MINUTE()：从时间中提取分钟。 SECOND()：从时间中提取秒。
  4. 逻辑函数： IF() 或 CASE：根据条件返回不同的值，if(rating<3, 1, 0)；COALESCE()：返回第一个非空值。
  5. 聚合函数： GROUP_CONCAT()：将组内的值连接成一个字符串。 GROUP_BY：分组聚合查询结果。
  6. 窗口函数：ROW_NUMBER()：为结果集的每行分配一个唯一的行号。 RANK()：为结果集中的行分配排名。 DENSE_RANK()：为结果集中的行分配密集排名。 OVER()：定义窗口以进行窗口函数计算。

MySQL 数据类型

  1. 整数类型： 用于存储整数值，包括 TINYINT, SMALLINT, MEDIUMINT, INT, 和 BIGINT。
  2. 浮点数类型： 用于存储浮点数，包括 FLOAT 和 DOUBLE。 DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。
    FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽。如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分
  3. 定点数类型： 用于存储定点数，包括 DECIMAL 或 NUMERIC。
  4. 字符串类型： 用于存储文本数据，主要有 CHAR, VARCHAR 两种类型，一种是定长的，一种是变长的。
    VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。
  5. 二进制数据类型： 用于存储二进制数据，包括 BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, 和 LONGBLOB.
  6. 日期和时间类型（Date and Time Types）： DATE： 用于存储日期。日期格式为’YYYY-MM-DD’，如’2023-10-12’。 TIME： 用于存储时间。时间格式为’HH:MM:SS’，如 ‘14:30:45’。 YEAR： 用于存储年份，可以使用两位或四位格式（’YY’或’YYYY’）
    DATETIME： 用于存储日期和时间。能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。它与时区无关，格式为’YYYY-MM-DD HH:MM:SS’，如’2023-10-12 14:30:45’。
    TIMESTAMP： 用于存储日期和时间，在插入或更新时自动记录当前时间。使用 4 个字节，只能表示从 1970 年到 2038 年。时区有关，即一个时间戳在不同的时区所代表的具体时间是不同的。应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。
  7. 布尔类型： 用于存储布尔值，包括 BOOLEAN, BOOL, TINYINT(1)。
  8. 枚举类型： 用于存储枚举值，其中一个预定义的枚举值，如 ENUM('value1', 'value2', ...)
  9. 集合类型： 用于存储一个或多个预定义的集合值，如 SET('value1', 'value2', ...)
  10. 自动增长类型： 用于自动生成唯一标识符，如 AUTO_INCREMENT。

函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。
对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
对于 A->B，B->C，则 A->C 是一个传递函数依赖。
有依赖：学号 -> 姓名、学院， 学院 -> 院长， 学号、课程-> 成绩
则 成绩Grade 完全函数依赖于键码（学号，课程），它没有任何冗余数据，每个学生的每门课都有特定的成绩。姓名, 学院 和 院长 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

MySQL 范式

范式理论是为了解决四种异常。不符合范式的关系，会产生很多异常：1、冗余数据。2、修改异常: 修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。3、删除异常: 删除一个信息，那么也会丢失其它信息。4、插入异常: 例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。
高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

  1. 第一范式 (1NF)：属性不可分。
  2. 第二范式 (2NF)：每个非主属性完全函数依赖于键码。可以通过分解来满足。（一张表分解成多张表）
  3. 第三范式 (3NF)：非主属性不传递函数依赖于键码。可以进行分解。
    for more：https://blog.csdn.net/calcular/article/details/79332453

事务管理

  • 事务：指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。
    • 原子性(Atomicity)事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
    • 一致性(Consistency)数据库在事务执行前后都保持一致性状态。一致性状态下，所有事务对一个数据的读取结果都是相同的。
    • 隔离性(Isolation)一个事务所做的修改在最终提交以前，对其它事务是不可见的。
    • 持久性(Durability)一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。可以通过数据库备份和恢复来实现，在系统发生崩溃时，使用备份的数据库进行数据恢复。
  • 并发一致性：产生不一致的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。
    • 丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
    • 读脏数据：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
    • 不可重复读：T2 读取一个数据，T1 对该数据做了修改。 T2 再次读取这个数据时读取的结果和第一次读取的结果不同。
    • 幻影读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据 结果和第一次不同。
      并发控制可以通过 封锁 来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的 隔离级别，让用户以一种更轻松的方式处理并发一致性问题。
  • 封锁
    • 封锁粒度：MySQL 中提供了两种封锁粒度，行级锁以及表级锁。
    • 读写锁：1、排它锁(Exclusive)，简写为 X 锁，又称写锁。 2、共享锁(Shared)，简写为 S 锁，又称读锁。
    • 意向锁：使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。
    • 封锁协议：1. 三级封锁协议 2. 两段锁协议
  • 隔离等级：MySQL支持 四种 标准的事务隔离级别，这些隔离级别定义了事务之间的可见性和并发控制。
    READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE 脏读 不可重复读 幻影读
    未提交读（允许一个事务读取另一个事务未提交的数据） √ √ √
    提交读（一个事务只能读取到另一个事务已经提交的数据） × √ √
    可重复读（事务执行期间，一个事务多次读取同一行数据时，会得到相同的结果） × × √
    可串行化（最高的隔离级别，确保事务串行执行） × × ×
  • 数据库默认隔离级别
    oracle数据库默认的隔离级别是：读已提交。
    mysql数据库默认的隔离级别是：可重复读。
  • InnoDB 中如何防止幻读 or MVCC 实现
    • 1、执行普通 select，此时会以 MVCC 快照读的方式读取
      （1）一致性非锁定读（快照读），普通的SELECT，通过多版本并发控制（MVCC）实现。
      （2）在快照读下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”。
      （3）只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读。
    • 2、执行 select…for update/lock in share mode、insert、update、delete 等当前读
      （1）一致性锁定读（当前读），SELECT … FOR UPDATE/SELECT … LOCK IN SHARE MODE/INSERT/UPDATE/DELETE，通过锁实现。
      （2）在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读。
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      START TRANSACTION;  -- 启动事务，用于标志事务的开始。在这之后的 SQL 语句都将在一个事务中执行 
      COMMIT;             -- 提交事务，当所有的 SQL 语句都执行成功时，使用 `COMMIT` 命令来提交事务，将更改永久保存到数据库。提交后，事务结束。
      ROLLBACK;           -- 回滚事务，如果在事务执行的过程中发生了错误或者不符合条件，可以使用 `ROLLBACK` 命令来回滚事务，撤销所有的更改，使数据库回到事务开始前的状态。

      SAVEPOINT savepoint_name;    -- 保存点，用于创建一个保存点，以便在事务中的某一时刻回滚到这个保存点。可以在事务中设置多个保存点。
      ROLLBACK TO savepoint_name;  -- 回滚到保存点，如果在事务中的某一步出现问题，可以回滚到之前设置的某个保存点，而不必回滚整个事务。

      -- 设置事务隔离级别，包括 `READ UNCOMMITTED`、`READ COMMITTED`、`REPEATABLE READ` 和 `SERIALIZABLE`。
      SET TRANSACTION ISOLATION LEVEL level;

      -- MySQL 默认是自动提交模式，即每个 SQL 语句都会自动成为一个事务并提交。命令关闭自动提交，再通过 `COMMIT` 手动提交，或者通过 `ROLLBACK` 回滚。
      SET autocommit = 0;  
        
      --示例 1
        START TRANSACTION;
        -- 执行一系列 SQL 语句
        -- 提交事务
        COMMIT;

      --示例 2
        START TRANSACTION;
        -- 执行一系列 SQL 语句
        -- 如果发生错误，回滚事务
        ROLLBACK;

MySQL 存储引擎

  • InnoDB
    • 是 MySQL 默认的事务型存储引擎。 实现了四个标准的隔离级别，默认级别是可重复读。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读
    • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等
    • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
  • MyISAM
    • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它
    • 提供了大量的特性，包括压缩表、空间数据索引等。
    • 不支持事务
    • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。
    • 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作非常慢
    • 如果指定了 DELAY KEY WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作

MySQL 索引

在MySQL中，索引是一种用于提高数据库查询效率的数据结构。它类似于书的目录，通过在数据库表上创建索引，可以快速定位并访问表中的特定数据行，而无需全表扫描。索引在数据库的性能优化中扮演着关键的角色，特别是在大型数据集上。
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

  • 索引类型
    1. B+Tree 索引：是大多数 MySQL 存储引擎的默认索引类型。因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。
      InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
    2. 哈希索引：能以 O(1) 时间进行查找，但是失去了有序性。存在限制：无法用于排序与分组；只支持精确查找，无法用于部分查找和范围查找。
      InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
    3. 全文索引：MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
    4. 空间数据索引：MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据
  • 索引的优点：大大减少了服务器需要扫描的数据行数。帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)。
  • 索引的使用场景
    • 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
    • 对于中到大型的表，索引就非常有效。
    • 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组可以指定多个列作为索引列，多个索引列共同组成键.
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引.
主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

索引优化

1.独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。例如下面的查询不能使用 actor id 列的索引:
2.多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor id和 film id 设置为多列索引。
3.索引列的顺序
4.前缀索引对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。对于前缀长度的选取需要根据索引选择性来确定。
5.覆盖索引

MySQL - 一条 SQL 的执行过程详解

https://pdai.tech/md/db/sql-mysql/sql-mysql-execute.html

在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。
java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，多线程请求的时候频繁的创建和销毁连接显然是不合理的。在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。

网络连接必须由线程来处理

网络中的连接都是由线程来处理的，所谓网络连接说白了就是一次请求，每次请求都会有相应的线程去处理的。也就是说对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。

SQL 接口：MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。
查询解析器：会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言
MySQL 查询优化器：查询优化器内部具体怎么实现的我们不需要是关心，我需要知道的是 MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本。MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行
存储引擎：查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的（存储引擎是一个非常重要的组件，后面会详细介绍）
执行器：执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行

存储引擎

当我们系统发出这样的查询去交给 MySQL 的时候，MySQL 会按照我们上面介绍的一系列的流程最终通过执行器调用存储引擎去执行，流程图就是上面那个。
在执行这个 SQL 的时候 SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机IO读写的速度肯定让人无法接受的，所以每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池 Buffer Pool

关于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系：
Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
Undo log 记录的是数据操作前的样子
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）
、、，，
，。

从准备更新一条数据到事务的提交的流程描述

  • 首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
  • 在数据被缓存到缓存池的同时，会写入 undo log 日志文件更
  • 新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
  • 完成以后就可以提交事务，在提交的同时会做以下三件事
    • 将redo log buffer中的数据刷入到 redo log 文件中
    • 将本次操作记录写入到 bin log文件中
    • 将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
  • 至此表示整个更新事务已经完成

关系型数据库是如何工作的

SQL DB 组成

核心组件

进程管理器(process manager): 很多数据库具备一个需要妥善管理的进程/线程池。再者，为了实现纳秒级操作，一些现代数据库使用自己的线程而不是操作系统线程
网络管理器(network manager): 网路 I/O 是个大问题，尤其是对于分布式数据库。所以一些数据库具备自己的网络管理器
文件系统管理器(File system manager): 磁盘 I/O 是数据库的首要瓶颈。具备一个文件系统管理器来完美地处理OS文件系统甚至取代OS文件系统，是非常重要的。
内存管理器(memory manager): 为了避免磁盘 I/O 带来的性能损失，需要大量的内存。但是如果你要处理大容量内存你需要高效的内存管理器，尤其是你有很多查询同时使用内存的时候。
安全管理器 (Security Manager) : 用于对用户的验证和授权
客户端管理器 (Client manager) : 用于管理客户端连接。

工具

备份管理器 (Backup manager) : 用于保存和恢复数据
恢复管理器 (Recovery manager) : 用于崩溃后重启数据库到一个一致状态。
监控管理器 (Monitor manager) : 用于记录数据库活动信息和提供监控数据库的工具。
管理员管理器(Administration manager) : 用于保存元数据(比如表的名称和结构)，提供管理数据库、模式、表空的工具

查询管理器

查询解析器 (Query parser) : 用于检查查询是否合法
查询重写器 (Query rewriter) : 用于预优化查询
查询优化器 (Query optimizer) : 用于优化查询
查询执行器 (Query executor) : 用于编译和执行查询

数据管理器

事务管理器 (Transaction manager) : 用于处理事务
缓存管理器 (Cache manager): 数据被使用之前置于内存，或者数据写入磁盘之前置于内存
数据访问管理器 (Data access manager) : 访问磁盘中的数据

数据查询的流程

  • 客户端管理器： 客户端管理器是处理客户端通信的。客户端可以是一个（网站）服务器或者一个最终用户或最终应用。客户端管理器通过一系列知名的API（JDBC, ODBC, OLE-DB …）提供不同的方式来访问数据库。客户端管理器也提供专有的数据库访问API。
    （JDBC 是 Java 编程语言中用于连接和操作数据库的标准API。可以理解为 JDBC 是 Java 与数据库之间的桥梁，允许 Java 应用程序与 MySQL 数据库进行通信和交互。MyBatis 是一个开源的Java持久层框架，用于将对象与关系数据库的表之间进行映射,使用 MyBatis 来操作 MySQL 数据库。）
  • 查询管理器：查询首先被解析并判断是否合法、然后被重写，去除了无用的操作并且加入预优化部分、接着被优化以便提升性能，并被转换为可执行代码和数据访问计划、然后计划被编译、最后，被执行
  • 数据管理器：在这一步，查询管理器执行了查询，需要从表和索引获取数据，于是向数据管理器提出请求。
  • 客户端管理器

管理用户权限

1
2
3
4
5
6
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
-- 授予用户权限
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;

备份和恢复

1
2
3
4
# 备份数据库
mysqldump -u username -p mydatabase > backup.sql
# 恢复数据库
mysql -u username -p mydatabase < backup.sql