关系数据库MySql基本理论

SQL语言分类：

1、数据查询语言(DQL: Data Query Language):其语句，也称为“数据检索语句”，用以从表中获得数据，确 定数据怎样在应用程序给出。保留字 SELECT 是 DQL(也是所有 SQL)用得最多的动词，其他 DQL 常用的保留字 有 WHERE，ORDER BY，GROUP BY 和 HAVING。这些 DQL 保留字常与其它类型的 SQL 语句一起使用。
2、数据操作语言(DML:Data Manipulation Language):其语句包括动词 INSERT、UPDATE 和 DELETE。它 们分别用于添加、修改和删除。
3、事务控制语言(TCL):它的语句能确保被 DML 语句影响的表的所有行及时得以更新。包括COMMIT(提交) 命令、SAVEPOINT(保存点)命令、ROLLBACK(回滚)命令。
4、数据控制语言(DCL):它的语句通过 GRANT 或 REVOKE 实现权限控制，确定单个用户和用户组对数据库对象 的访问。某些 RDBMS 可用 GRANT 或 REVOKE 控制对表单个列的访问。
5、数据定义语言(DDL):其语句包括动词 CREATE,ALTER 和 DROP。在数据库中创建新表或修改、删除表 (CREAT TABLE 或 DROP TABLE);为表加入索引等。
6、指针控制语言(CCL):它的语句，像 DECLARE CURSOR，FETCH INTO 和 UPDATE WHERE CURRENT 用于对 一个或多个表单独行的操作。

A.MySql原理

1.MySql存储模式

缓存分为两种，默认情况下
1.MySQL整体架构，在server里头。
2.在引擎那头。

独占模式

1)、日志组文件:ib_logfile0 和 ib_logfile1，默认均为5M
2)、表结构文件:.frm
3)、独占表空间文件:.ibd
4)、字符集和排序规则文件:db.opt
5)、binlog 二进制日志文件:记录主数据库服务器的 DDL 和 DML 操作
6)、二进制日志索引文件:master-bin.index

共享模式

innodb_file_per_table=OFF 1)、数据都在 ibdata1

2.MySql对SQL执行顺序

实际上这个过程也并不是绝对这样的，中间 mysql 会有部分的优化以达到最佳的优化效果

1. from
2. on
3. join
4. where
5. group by
6. having + 聚合函数
7. select
8. order by
9. limit

3.MySql索引

索引原理

InnoDB 使用 B+ 树实现聚集索引

*一般单表数据不超过2000万原因：

B+树一般使用三层索引结构，这样算出来最多容纳2100万左右的数据。

主键数据类型bigint大小为8B，
索引中的一个指针在InnoDB中一般占6B，
合计14B

假设一条记录是1KB，
B+树一层索引约16KB，可容纳约16*1024/14=1170个指针

两层索引：1170指针16=18720数据
三层索引：1170指针1170指针*16=21902400数据节点

索引类型

Hash
比较适合内存存储的索引结构

B-Tree/B+Tree
比较适合磁盘存储的索引结构，因为B-Tree是按照块来存储数据

*为什么主键要单调递增：

如果单调递增只会在末尾往上加数据，不会插入到中间产生数据页分裂问题，影响范围校小。

聚集索引Primary Key

二级索引Secondary Key

B.安装

《Mac本地安装MySql》
https://blog.csdn.net/holly_08/article/details/114373132

Docker安装

1.拉取mysql镜像
docker pull mysql
2.运行一个mysql实例
docker run

Linux命令安装

brew install mysql

访问/使用mysql

mysql -u root (-p密码)
mysql -P[端口]
mysql -h[IP/服务器域名]
use [DB名/schema名]
查看当前逻辑数据库schema：show databases/show schemas
show tables;
show create table tbl_test1;
show columns from tbl_test1;
创建数据库：create schema/create database
查看当前InnoDB引擎状态：show engine innodb status;
查看SQL执行完之后的警告信息：show warnings;

C.参数配置

1.使用文件

MAC：my.cnf
Windows：my.ini
编辑配置文件路径命令：mysqld -defaults -file
初始化命令(可以非安全地表示root账号密码可以为空)：mysqld -defaults -file=my.cnf —initialize-insecure
(mysqld命令：MySql Server进程的名字)

文件内容

[mysqld]：使用于server
[mysql]：使用于client

2.查看配置参数

默认情况下，很多全局和当前会话的参数内容是一样的。

当前会话参数

show variables like ‘%dir%’;
show variables like ‘%port%’;
show variables like ‘%version%’;

全局参数

show global variables like ‘%inno%’;

查询具体变量

select @@innodb_version;

设置变量

set global xxxx; // 5.6，5.7版本中设置的全局变量的值不会持久化，重启之后恢复默认。

3.常用参数/变量

1)连接请求的变量

1、max_connections：默认的最大连接数151一般不够用，如果三个应用程序，每个连接池都是100个，三个应用最大就得300个连接数，得设置。
2、back_log：半连接数量，只读Readonly
3、wait_timeout：等待超时时间，如果指定时间内没有连接请求，之前的系统会自动断开。所以现在的开发都加上了连接心路，不停尝试发起test之类的sql来保证双方都有效。现在的系统如果断开了也会尝试重新连接服务器。
4、interative_timeout：交互状态下的等待超时时间

2)缓冲区变量

1. key_buffer_size：MyISAM索引内存里的buffer大小，决定了索引操作的速度。非innoDB的参数，但一些临时表还是使用MyISAM。
2. query_cache_size(查询缓存简称 QC) ：server里的缓存，在5.6/5.7版本中使用，从8.0版本中被去掉了，因为现在的业务数据改动频繁，查询缓存的意义不大，再就是大家都用自己的缓存，比如redis。所以干脆去掉，还省事。如果业务上确实有这个必要，可以拿出来使用。
3. max_connect_errors：连接时允许的最大错误数。
4. sort_buffer_size：默认1MB，排序时如果有file字段会导致排序内存不够用，从而把排序文件放到磁盘中的临时文件夹。这样的话排序速度就变慢。
5. max_allowed_packet：默认32MB，给MySqlSever发送数据包时允许的最大容量。例如，批量insert所跟的数据量超过32MB，就会执行失败。
6. join_buffer_size：默认2MB
7. thread_cache_size：每个线程可用的内存大小默认300

3)配置 InnoDB 的几个变量

1. innodb_buffer_pool_size=128M：重要
2. innodb_flush_log_at_trx_commit
3. innodb_thread_concurrency=0
4. innodb_log_buffer_size：跟4.事务日志的重做日志redo log相关
5. innodb_log_file_size=50M
6. innodb_log_files_in_group=3
7. read_buffer_size=1M
8. read_rnd_buffer_size=16M
9. bulk_insert_buffer_size=64M
10. binary log：binlog，高可用

D.事务

1.定义

ACID

• Atomicity: 原子性，一次事务中的操作要么全部成功，要么全部失败。
• Consistency: 一致性，跨表、跨行、跨事务，数据库始终保持一致状态。业务意义上的一致。
• Isolation: 隔离性/可见性，保护事务不会互相干扰，包含4种隔离级别。
• Durability: 持久性，事务提交成功后，不会丢数据。如电源故障，系统崩溃。

跟多线程的线程安全设计结构相似。

InnoDB引擎

双写缓冲区、故障恢复、操作系统、fsync() 、磁盘存储、缓存、UPS、网络、备份策略 ……
在可靠性和性能之间找到一个平衡点

2.锁

MySQL 8版本中，可以用此语句直接查看锁：
select * from performance_schema.data_locks;

表级锁

重点：上锁之前需要先上意向锁！！
show engine innodb status; // 查看当前数据库的锁和事务

1)意向锁：表明事务稍后要进行哪种类型的锁定

• 共享意向锁(IS): 打算在某些行上设置共享锁
• 排他意向锁(IX): 打算对某些行设置排他锁
• Insert 意向锁: Insert 操作设置的间隙锁

2)非意向锁

• 共享锁(S)
• 排他锁(X)

3)锁类型的兼容性：

4)其他锁

• 自增锁(AUTO-IN) ：表设置为自增主键时，并发插入时在内存中有自增锁，以保证主键不会冲突。
• LOCK TABLES/DDL：执行DDL或者类似于mysql dump命令时会直接锁表或者锁库，这种显示的lock/unlock过程。

5)手动上锁
select from t_tbl for update: // 给该表上排他锁X
select from t_tbl in share model; // 给该表上共享锁S

行级锁

1)记录锁(Record): 始终锁定索引记录，注意隐藏的聚簇索引
eg:
select from t_tbl where id=5 for update;//给该行上排他锁X
select from t_tbl where id=5 in share model; // 给该行上共享锁S
2)间隙锁(Gap): 锁住一个范围

3)临键锁(Next-Key): 记录锁+间隙锁的组合;可“锁定”表中不存在记录
4)谓词锁(Predicat): 空间索引
5)死锁:

• 阻塞与互相等待：针对一个范围/区间操作时，锁的力度较大，跟其他的事务进行的读写操作有冲突。解决办法：1.主动：强制kill掉其中一个事务，打破死锁环。2.被动：给事务加超时时间，超时后事务自动执行失败。
• 增删改、锁定读
• 死锁检测与自动回滚
• 锁粒度与程序设计：

6)避免死锁的设计事项：

• 降低锁的粒度
• 尽量把业务隔离开

3.事务隔离

事务隔离是数据库的基础特征。

MySQL:
• 可以设置全局的默认隔离级别
• 可以单独设置会话的隔离级别
• InnoDB 实现与标准之间的差异

隔离级别：

《SQL:1992标准》规定了四种事务隔离级别(Isolation):
1)读未提交: READ UNCOMMITTED
因不能保证一致性，容易产生脏读，很少使用。除非对数据准确性要求非常低，但对性能要求非常高。
可能问题：脏读，幻读，不可重复读
2)读已提交: READ COMMITTED

• 常见的关系数据库默认隔离级别。读取的是其他事务已提交的数据，因此不存在脏读问题。
• 每次查询都会设置和读取自己的新快照。
• 仅支持基于行的 bin-log
• UPDATE 优化: 半一致读(semi-consistent read)
• 不可重复读: 不加锁的情况下, 其他事务 UPDATE 或 DELETE 会对查询结果有影响
• 幻读(Phantom): 加锁后, 不锁定间隙，其他事务可以 INSERT

3)可重复读: REPEATABLE READ

• MySQL.InnoDB默认隔离级别，其实RC完善后也可以使用于MySQL，但历史包袱不好乱改。
• 使用事务第一次读取时创建的快照
• 多版本技术

4)可串行化: SERIALIZABLE
全表锁，性能最低，最严格的级别，事务串行执行，资源消耗最大。

问题读：

1)脏读:Dirty Read
读到其他事务还未提交随时可能回滚的数据，或者早期版本。
2)幻读Phantom Read:
多次读数据每次出来的结果都不一致。第一次读出来3条，第二次读出来5条。(行级别)
加锁后, 不锁定间隙范围，其他事务可以 INSERT，对查询结果有影响。
3)不可重复读Unrepeated Read：
多次读数据，同样一行数据的具体设置内容不一样。(列级别)
不加锁的情况下, 其他事务 UPDATE 或 DELETE 会对查询结果有影响。
4)解决方法：
提高隔离级别、使用间隙锁或临键锁

4.日志

撤消日志undo log

• 用处: 保证事务的原子性，事务回滚, 一致性读、崩溃恢复。
• 记录事务回滚时所需的撤消操作
• 一条 INSERT 语句，对应一条 DELETE 的 undo log
• 每个 UPDATE 语句，对应一条相反 UPDATE 的 undo log
• 保存位置：•system tablespace(MySQL 5.7 默认) •undo tablespaces(MySQL 8.0 默认)

重做日志redo log

背景：事务执行中是把执行内容放在操作系统OS中，然后commit时再由操作系统刷到磁盘中持久化。会有极端情况在commit之间操作系统崩了。

• 确保事务的持久性，防止事务提交后数据未刷新到磁盘就掉电或崩溃。
• 事务执行过程中写入 redo log，记录事务对数据页做了哪些修改。
• 提升性能: WAL(Write-Ahead Logging) 技术，先写日志，再写磁盘。WAL是顺序写日志的一个技术，比直接往磁盘里写数据块要快得多。
• 日志文件: ib_logfile0, ib_logfile1
• 日志缓冲: innodb_log_buffer_size
• 强刷: fsync()

5.MVCC实现机制(多版本并发控制Multi-Version Concurrency Control)

1)隐藏列：
为了实现快照机制，InnoDB在每行数据下都添加了三个隐藏列

2)事务链表
优化事务隐藏列，保存还未提交的事务，事务提交则会从链表中摘除