Mysql知识

基础架构

连接器： 身份认证和权限相关。
查询缓存：执行查询语句的时候，会先查询缓存。
分析器：检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器：调用引擎的接口，返回接口执行的结果。

存储引擎

show engines

MyISAM：表级锁；不支持事务；不支持外键；不支持安全恢复，使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址（非聚集索引）

InnoDB：行级锁，表级锁；支持事务；支持外键；支持安全恢复，主键索引是聚集索引；辅助索引是非聚集索引

MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引

InnoDB表文件含义：.frm表定义，表空间数据和日志文件

表级锁和行级锁

表级锁：粒度最大，资源消耗少，加锁快，不会死锁，并发度最低。
行级锁：粒度最小，资源消耗多，加锁慢，会死锁，并发度最高。

两个session分别通过一个sql持有一把锁，然后互相访问对方加锁的数据产生死锁。
session1
Select * from xxx where id=1 for update
session2
Select * from xxx where id=2 for update
session2
Select * from xxx where id=1 for update
session1
Select * from xxx where id=2 for update

MySQL有两种死锁处理方式
等待， 直到超时（innodb_lock_wait_timeout=50s） 。
发起死锁检测， 主动回滚一条事务， 让其他事务继续执行（innodb_deadlock_detect=on）

InnoDB 存储引擎的锁的算法：
Record lock：记录锁，单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

聚集索引和非聚集索引

聚集索引：索引结构和数据一起存放的索引。主键索引属于聚集索引。定位到索引的节点，就相当于定位到了数据。
优点：查询速度快，找到节点就找到数据
缺点：依赖于有序的数据；更新代价大
非聚集索引：叶节点的 data 域存放的是数据记录的地址。二级索引属于非聚集索引。按定位到索引的节点，取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。
优点：更新代价比聚集索引要小。叶子节点不存放数据。
缺点：非聚集索引也依赖于有序的数据；可能会二次查询(回表)

非聚集索引不一定回表查询：覆盖索引

事务

开启一个事务 START TRANSACTION;
执行sql语句 ；
提交事务 COMMIT; 4.事务回滚ROLLBACK;

事务的四大特性

持久性D：事务对数据库中数据的改变是持久的。redo log(重做日志)
隔离性I：各并发事务之间数据库是独立的。undo log(回滚日志)
原子性A：动作要么全部完成，要么完全不起作用 。锁机制、MVCC
一致性C：执行事务前后，数据保持一致。

并发事务的问题

脏读：读未提交的数据。
幻读：读多插入的数据
不可重复读：读已修改的数据
丢失修改：修改失效

事务隔离级别

读未提交：可能会导致脏读、幻读或不可重复读
读已提交：阻止脏读
可重复读（默认级别）：可以阻止脏读和不可重复读，（MVCC）防止部分的幻读
可串行读（分布式事务XA模式）：均可阻止

索引

加快查询，唯一约束。

索引结构
Hash：MEMORY引擎支持。快速检索数据（接近 O（1），不支持顺序和范围查询
B树：
所有节点既存放键(key) 也存放 数据(data)。
叶子节点都是独立的
检索可能不经过叶子节点
B+树：
只有叶子节点存放 key 和 data，其他内节点只存放 key。
叶子节点有一条引用链指向与它相邻的叶子节点。
检索必须经过叶子节点

索引类型

1. 主键索引(Primary Key)：没有指定表的主键时，会自动创建主键
2. 二级索引(辅助索引)属于非聚集索引：二级索引的叶子节点存储的数据是主键，需要定位主键的位置，再获取数据。
   唯一索引(UniqueKey) ：不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引
   普通索引(Index)：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
   前缀索引(Prefix)：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引
   全文索引(FullText)：全文索引主要是为了检索大文本数据中的关键字的信息
3. 覆盖索引：就是包含了所有查询字段 (where,select,orderby,group by 包含的字段) 的索引
4. 联合索引：使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引

最左前缀匹配原则

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。可以将区分度高的字段放在最左边，这也可以过滤更多数据。

索引失效的场景

类型转换：没有加单引号
or
最左匹配原则
where 条件含有计算表达式

添加索引

添加 PRIMARY KEY（主键索引）：ALTER TABLE table_name ADD PRIMARY KEY ( column )
添加 UNIQUE(唯一索引)：ALTER TABLE table_name ADD UNIQUE ( column )
添加 INDEX(普通索引)：ALTER TABLE table_name ADD INDEX index_name ( column )
添加 FULLTEXT(全文索引)：ALTER TABLE table_name ADD FULLTEXT ( column)
添加多列索引：ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

优化建议

1.临时库表必须以 tmp_为前缀并以日期为后缀，备份表必须以 bak_为前缀并以日期 (时间戳) 为后缀
2.数据库和表的字符集统一使用 UTF8（如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集）
3.分区表在物理上表现为多个文件，在逻辑上表现为一个表；
4.使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
5.限制每张表上的索引数量,建议单张表索引不超过 5 个
6.出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列建索引
7.包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
8.并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
9.对于频繁的查询优先考虑使用覆盖索引
10.子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引
11.对应同一列进行 or 判断时，使用 in 代替 or

mysql日志

错误日志，查询日志，慢查询日志，事务日志，二进制日志

redo log（重做日志）

InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，然后再写入写到文件系统缓存（page cache），最后接着刷盘到 redo log 文件里。

InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

刷盘时机：innodb_flush_log_at_trx_commit
1.每次事务提交时不进行刷盘操作–》如果MySQL挂了或宕机可能会有1秒数据的丢失。
2.每次事务提交时都将进行刷盘操作（默认值）–》不会丢失数据。事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失
3.每次事务提交时都只把 redo log buffer 内容写入 page cache–》如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

存储形式：
日志文件组：硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。
它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写
每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

binlog（二进制日志）

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。依靠binlog来同步数据，保证数据一致性。
事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到文件系统缓存（page cache），最后刷盘到binlog文件中。

刷盘流程：sync_binlog
1.每次提交事务都只write，由系统自行判断什么时候执行fsync。
2.每次提交事务都会执行fsync。
3.每次提交事务都write，但累积N个事务后才fsync。

存储三种格式：
statement：记录的内容是SQL语句原文
row：解决函数带来的数据不一致问题，记录是具体的数值。
mixed：内容是前两者的混合。

两者之间的区别

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。
binlog（归档日志）保证了MySQL集群架构的数据一致性。
redo log与binlog的写入时机不一样：redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入

两阶段提交机制

解决的数据不一致的问题：执行过程中写完redo log日志后，binlog日志写期间发生了异常。从库使用binlog日志恢复，数据丢失修改。
原理：将redo log的写入拆成了两个步骤prepare和commit
情景：update tb_student A set A.age=‘19’ where A.name=’ 张三 ';
1.先查询到张三这一条数据，如果有缓存，也是会用到缓存。
2.然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
3.执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
4.更新完成。

MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

undo log（回滚日志）

保证事务的原子性，在异常发生时，对已经执行的操作进行回滚。所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。MVCC的基本实现。
insert undo log：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

MVCC机制

多版本并发控制机制,读已提交与可重复读都用了MVCC机制,主要用到了undo log和read view
非锁定读的实现。读取行处于锁定状态，不需要行释放锁，而是形成快照并且读取快照。
MVCC 的实现依赖于：
1.隐藏字段: InnoDB 存储引擎为每行数据添加了三个 隐藏字段
DB_TRX_ID（6字节）:表示最后一次插入或更新该行的事务 id
DB_ROLL_PTR（7字节）:回滚指针，指向该行的 undo log
DB_ROW_ID（6字节）:如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引
2.Read View:用来做可见性判断
m_low_limit_id:目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见。
m_up_limit_id:活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见。表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了
m_ids:其他未提交的活跃事务 ID 列表
m_creator_trx_id:创建该 Read View 的事务 ID

数据可见性算法

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

RC 和 RR 隔离级别下 MVCC 的差异

在事务隔离级别 RC 和 RR （InnoDB 存储引擎的默认事务隔离级别）下，InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同
在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)，导致不可重复读
在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表）

MVCC➕Next-key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：
1、执行普通 select，此时会以 MVCC 快照读的方式读取数据
2、执行 select…for update/lock in share mode、insert、update、delete 等当前读

基本命令：

mysql -u xxx -p yyy

库的操作：

1.CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
数据库选项：
CHARACTER SET charset_name
COLLATE collation_name
2.SHOW CREATE DATABASE 数据库名
3.DROP DATABASE[ IF EXISTS] 数据库名

表的操作：

1.创建表：

CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的字段结构定义 )[ 表选项]
对于字段的定义：
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT ‘string’]
数据类型：
整型：tinyint；smallint；mediumin；int;bigint
浮点型:float;double.float(M, D) double(M, D) M表示总位数，D表示小数位数
定点数:decimal(M, D) M也表示总位数，D表示小数位数。
字符串：char定长字符串，速度快，但浪费空间；varchar变长字符串，速度慢，但节省空间；blob 二进制字符串（字节字符串）；text 非二进制字符串（字符字符串）
日期时间类型：timestamp ；datetime
枚举和集合：enum(val1, val2, val3…)
列约束：
PRIMARY 主键：
UNIQUE 唯一索引：
NULL 约束：
DEFAULT 默认值属性：
AUTO_INCREMENT 自动增长约束
COMMENT 注释
FOREIGN KEY 外键约束
表选项：
字符集：CHARSET = charset_name
存储引擎：ENGINE = engine_name
自增起始数：AUTO_INCREMENT = 行数、
数据文件目录：DATA DIRECTORY = ‘目录’
索引文件目录： INDEX DIRECTORY = ‘目录’
表注释：COMMENT = ‘string’

2.查看表结构

SHOW CREATE TABLE 表名

3.修改

表本身的选项：ALTER TABLE 表名 表的选项
对表进行重命名：RENAME TABLE 原表名 TO 新表名
修改表的字段结构：ALTER TABLE 表名 操作名
– 操作名：
ADD[ COLUMN] 字段定义 – 增加字段
ADD PRIMARY KEY(字段名) – 创建主键
ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
ADD INDEX [索引名] (字段名) – 创建普通索引
MODIFY[ COLUMN] 字段名 字段属性 – 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
CHANGE[ COLUMN] 原字段名 新字段名 字段属性 – 支持对字段名修改
DROP[ COLUMN] 字段名 – 删除字段
DROP PRIMARY KEY – 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
DROP INDEX 索引名 – 删除索引
DROP FOREIGN KEY 外键 – 删除外键
删除表：DROP TABLE[ IF EXISTS] 表名
清空表数据：TRUNCATE [TABLE] 表名
复制表结构：CREATE TABLE 表名 LIKE 要复制的表名

数据的操作

增：INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]

查：SELECT 字段列表 FROM 表名[ 其他子句]

执行流程：SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
GROUP BY 字段/别名 [排序方式]
分组后会进行排序。升序：ASC，降序：DESC
以下[合计函数]需配合 GROUP BY 使用：
count 返回不同的非NULL值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接
HAVING 子句，条件子句
与 where 功能、用法相同，执行时机不同。
where 在开始时执行检测数据，对原数据进行过滤。
having 对筛选出的结果再次进行过滤。
having 字段必须是查询出来的，where 字段必须是数据表存在的。
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
where 不可以使用合计函数。一般需用合计函数才会用 having
SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。

删：DELETE FROM 表名[ 删除条件子句]
ORDER BY 子句，排序子句
order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]…
升序：ASC，降序：DESC
支持多个字段的排序。
LIMIT 子句，限制结果数量子句
仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
limit 起始位置, 获取条数
省略第一个参数，表示从索引0开始。limit 获取条数
DISTINCT, ALL 选项
distinct 去除重复记录
默认为 all, 全部记录

改：UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

删： DELETE FROM 表名[ 删除条件子句]
TRUNCATE [TABLE] tbl_name
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。
4，当被用于带分区的表时，truncate 会保留分区

特殊查询：
UNION：将多个select查询的结果组合成一个结果集合。SELECT … UNION [ALL|DISTINCT] SELECT …
子查询：
– from型：select * from (select * from tb where id>0) as subfrom where id>1;
– where型：
select * from tb where money = (select max(money) from tb);
select column1 from t1 where exists (select * from t2);
select * from t1 where (id, gender) in (select id, gender from t2);

连接查询(join)：
内连接(inner join)
左外连接 left join：如果数据不存在，左表记录会出现，而右表为null填充
右外连接 right join：如果数据不存在，右表记录会出现，而左表为null填充