MelonCholi 收录于数据库

2021-12-23 约 6540 字预计阅读 14 分钟

MySQL 学习笔记

登录和退出 MySQL 服务器

1
2
3
4
5
6
7
8
# 登录MySQL
mysql -u [username] -p[password]

# 远程连接
mysql -h [host] -P [port] -u [username] -p[password]

# 退出MySQL数据库服务器
exit;

基本语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 显示所有数据库
show databases;

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
SELECT * from pet;

-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改数据
UPDATE pet SET name = 'squirrel' WHERE owner = 'Diane';

-- 删除数据
DELETE FROM pet WHERE name = 'squirrel';

-- 删除表
DROP TABLE myorder;

建表约束

主键约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 添加主键约束
-- 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

唯一约束

unique

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 建表时创建唯一约束
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一约束
-- 如果建表时没有设置唯一约束，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一约束
ALTER TABLE user DROP INDEX name;

非空约束

not null

1
2
3
4
5
6
7
8
9
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

default

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- 移除非空约束
ALTER TABLE user MODIFY age INT;

外键约束

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 班级
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 这里的 class_id 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。

数据库的三大设计范式

1NF（原子性）

所有字段值都是不可再分的原子值

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

2NF（完全依赖）

在满足第一范式的前提下，其他列都必须完全依赖于主键列。

不完全依赖只可能发生在联合主键的情况下：

1
2
3
4
5
6
7
8
-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

3NF（无传递依赖）

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

1
2
3
4
5
6
CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

常见的存储引擎

MySQL 中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5 版本后默认的存储引擎为 InnoDB。

InnoDB 存储引擎

InnoDB 是 MySQL 默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB 内部做了很多优化，如能够自动在内存中创建自适应 hash 索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM 存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用 MyISAM 引擎。

MyISAM 会将表存储在两个文件中，数据文件 .MYD 和索引文件 .MYI（都是二进制文件）

优点：访问速度快（数据以静态的方式存储在磁盘上）

缺点：MyISAM 不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY 存储引擎

MEMORY 引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY 引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

哈希索引数据不是按照索引值顺序存储，无法用于排序。
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
只支持等值比较，不支持范围查询。
当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE 存储引擎

ARCHIVE 存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE 提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

数据文件以扩展名为".ARZ"的文件格式存储，它是一种高度压缩的存储格式，能够有效地节省磁盘空间。

MyISAM 和 InnoDB 的区别

是否支持行级锁 : MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 不提供事务支持。而 InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
是否支持外键：MyISAM 不支持，而 InnoDB 支持。
是否支持MVCC ：MyISAM 不支持，InnoDB 支持。应对高并发事务，MVCC 比单纯的加锁更高效。
MyISAM 不支持聚集索引，InnoDB 支持聚集索引。

锁机制

数据库锁与隔离级别的关系

隔离级别	实现方式
读未提交	总是读取最新的数据，无需加锁
读已提交	读取数据时加共享锁，读取数据后释放共享锁
可重复读	读取数据时加共享锁，事务结束后释放共享锁
串行化	锁定整个范围的键，一直持有锁直到事务结束

数据库锁的类型

按照锁的粒度可以将 MySQL 锁分为三种：

MyISAM 默认采用表级锁，InnoDB 默认采用行级锁。

从锁的类别上区别可以分为共享锁和排他锁

共享锁：共享锁又称读锁，简写为 S 锁
- 一个事务对一个数据对象加了 S 锁，可以对这个数据对象进行读取操作，但不能进行更新操作。
- 并且在加锁期间其他事务只能对这个数据对象加 S 锁，不能加 X 锁。
排他锁：排他锁又称为写锁，简写为 X 锁
- 一个事务对一个数据对象加了 X 锁，可以对这个对象进行读取和更新操作
- 加锁期间，其他事务不能对该数据对象进行加 X 锁或 S 锁。

InnoDB 的行锁实现

行锁实现方式：InnoDB 的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录进行加锁。

InnoDB 行锁主要分三种情况：

Record lock：对索引项加锁
Grap lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条后的间隙加锁。
Next-key lock：前两种放入组合，对记录及前面的间隙加锁。

InnoDB 行锁的特性：如果不通过索引条件检索数据，那么 InnoDB 将对表中所有记录加锁，实际产生的效果和表锁是一样的。

MVCC 不能解决幻读问题，在可重复读隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

日志 log

MySQL 日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。

其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

二进制日志 bin log

bin log 是 MySQL 数据库级别的文件，记录对 MySQL 数据库执行修改的所有操作，不会记录 select 和 show 语句

主要用于恢复数据库和同步数据库。

重做日志 redo log

redo log 是 innodb 引擎级别，用来记录 innodb 存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。

当数据库发生故障，innoDB 存储引擎会使用 redo log 恢复到发生故障前的时刻，以此来保证数据的完整性。将参数 innodb_flush_log_at_tx_commit 设置为 1，那么在执行 commit 时会将 redo log 同步写到磁盘。

回滚日志 undo log

除了记录 redo log 外，当进行数据修改时还会记录 undo log，undo log 用于数据的撤回操作，它保留了记录修改前的内容。

通过 undo log 可以实现事务回滚，并且可以根据 undo log 回溯到某个特定的版本的数据，实现 MVCC。

bin log 和 redo log 有什么区别？

bin log 会记录所有日志记录，包括 InnoDB、MyISAM 等存储引擎的日志；redo log 只记录 innoDB 自身的事务日志。
bin log 只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有 redo log 不断写入磁盘。
bin log 是逻辑日志，记录的是 SQL 语句的原始逻辑；redo log 是物理日志，记录的是在某个数据页上做了什么修改。

MySQL 架构

MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块：bin log 日志模块。
- 连接器： 当客户端连接 MySQL 时，server 层会对其进行身份认证和权限校验。
- 查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
- **分析器：**没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
- 优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
- 执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。
存储引擎层：主要负责数据的存储和读取。server 层通过 api 与存储引擎进行通信。

分表与分区

当单表的数据量达到 1000W 或 100G 以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据切分可以分为两种方式：垂直划分和水平划分。

两种方式

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少 I/O 次数。

缺点：

主键出现冗余，需要管理冗余列；
会引起表连接 join 操作，可以通过在业务服务器上进行 join 来减少数据库压力；
依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或 id 序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

分片事务一致性难以解决
跨节点 join 性能差，逻辑复杂
数据分片在扩容时需要迁移

分区表

分区是把一张表的数据分成 N 多个区块。分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是 mysql 内部实现。分库分表和分区并不冲突，可以结合使用。

分区表类型

range 分区

按照范围分区。比如按照时间范围分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

在 /var/lib/mysql/data/ 可以找到对应的数据文件，每个分区表都有一个使用 # 分隔命名的表文件：

1
2
3
4
5
6
   -rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
   -rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list 分区

list 分区和 range 分区相似，主要区别在于 list 是枚举值（离散）列表的集合，range 是连续的区间值的集合。

对于 list 分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无法插入。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash 分区

可以将数据均匀地分布到预先定义的分区中。

1
2
3
4
5
6
create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

分区的问题

打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE 和一次删除多行数据。
维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。
所有分区必须使用相同的存储引擎。

主从同步

定义

什么是主从同步

主从同步 / 主从复制使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

为什么要做主从同步

读写分离，使数据库能支撑更大的并发。
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
数据备份，保证数据的安全。

读写分离

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

主服务器负责写，从服务器负责读，缓解了锁的竞争
从服务器可以使用 MyISAM，提升查询性能及节约系统开销
增加冗余，提高可用性

主从复制的实现

为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制主要有三个线程：binlog 线程，I/O 线程，SQL 线程。

binlog 线程：运行在 Master 上，负责将主服务器上的数据更改写入到二进制日志（Binary log）中
I/O 线程：运行在 Replica 上，负责从主服务器上读取 bin log，并写入从服务器的中继日志（Relay log）中
SQL 线程：运行在 Replica 上，负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放

Master 在每个事务更新数据完成之前，将操作记录写入到 bin log 中。
Slave 从库连接 Master 主库，并且 Master 有多少个 Slave 就会创建多少个 binlog dump 线程。当 Master 节点的 binlog 发生变化时，binlog dump 会通知所有的 Slave，并将相应的 binlog 发送给 Slave。
I/O 线程接收到 bin log 内容后，将其写入到中继日志（Relay log）中。
SQL 线程读取中继日志，并在从服务器中重放。

乐观锁和悲观锁

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁
- 假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。
- 实现方式：使用数据库中的锁机制。
乐观锁
- 假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加 version 字段，在修改提交之前检查 version 与原来取到的 version 值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。
- 实现方式：乐观锁一般使用版本号机制（version）或 CAS 算法实现。

存储过程

MySQL 的存储过程是一组预编译的 SQL 语句，保存在数据库中。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
DELIMITER //

CREATE PROCEDURE GetCustomerCount()
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM customers;
    SELECT count;
END //

DELIMITER ;

CALL GetCustomerCount();

其他技巧

`\G` 和 `\g`

`\g`

在 MySQL 的 sql 语句后加上 \g，效果等同于加上定界符，一般默认的定界符是分号 ;

`\G`

在 MySQL 的 sql 语句后加上 \G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。

即将查到的结构旋转 90 度变成纵向；

使用前，显示效果很差

使用后，显示效果不错，方便查询

关于 MySQL 的一些笔记