mysql - IPS99技术分享

标签：事务 log 索引 mysql 数据 name

sql

语法

单行或多行书写，分号结尾
sql语句不区分大小写

DDL 数据定义语言

数据库操作

CREATE DATABASE [IF NOT EXISTS] name [DEFAULT CHARSET = name]
DROP DATABASE name
USE name
SHOW DATABASES
SELECT DATABASE()

表操作

SHOW TABLES
DESC name // 显示表结构
SHOW CREATE TABLE name // 显示创建表的语句
CREATE TABLE name(
column_name data_type [comment "comment"],
...
)

数值类型

TINYINT // 1字节
SMALLINT // 2字节
MEDIUMINT // 3字节
INT // 4字节
BIGINT // 8字节
FLOAT // 4字节
DOUBLE // 8字节比如DOUBLE(7,4)表示总共7位，小数点后4位
DECIMAL // 10进制，依赖于精度(数据位数)和标度(小数位数)

字符串类型

CHAR // 定长字符串长度0-255 多余的空间用空格填充
VARCHAR // 变长字符串长度0-65535

日期类型

DATE // 日期 'YYYY-MM-DD'
TIME // 时间 'HH:MM:SS'
DATETIME // 日期时间 'YYYY-MM-DD HH:MM:SS'

修改

添加字段
ALTER TABLE name ADD name type
修改字段类型
ALTER TABLE name MODIFY name type
修改字段名
ALTER TABLE name CHANGE old_name new_name type
删除字段
ALTER TABLE name DROP name
修改表明
ALTER TABLE old_name RENAME new_name
删除表
DROP TABLE name
改为空表
TRUNCATE TABLE name

DML 数据操作语言

插入(字符串或日期类型需要用单引号括起来，字段顺序和值顺序一一对应)

INSERT INTO name (column1, column2, ...) VALUES (value1, value2, ...) 给指定列插入数据
INSERT INTO name VALUES (value1, value2, ...) 给所有列插入数据

修改

UPDATE name SET name = value, ... WHERE condition
UPDATE name SET name = value, ... 全表修改

删除

DELETE FROM name WHERE condition

DQL 数据查询语言

查询

SELECT column1, column2, ... FROM name WHERE condition GROUP BY column1 HAVING condition ORDER BY column1 ASC/DESC LIMIT 0, 10
SELECT column1 as reference_name, column2 as reference_name, ... FROM name reference_name WHERE condition

去重

SELECT DISTINCT column1, column2, ... FROM name WHERE condition

条件

比较运算符 =, <, >, <=, >=, !=
逻辑运算符 AND, OR, NOT
模糊查询 LIKE, %表示任意个字符，_表示一个字符
范围查询 BETWEEN ... AND ...
空值查询 IS NULL, IS NOT NULL
包含查询 IN (value1, value2, ...)

聚合函数

将一列数据作为一个整体进行操作

COUNT(column) // 计数，空值不计入
SUM(column) // 求和
AVG(column) // 平均值
MAX(column) // 最大值
MIN(column) // 最小值

SELECT func FROM name WHERE condition

分组
GROUP BY column1, column2, ... HAVING condition
HAVING是对分组后的结果进行筛选，WHERE是对原始数据进行筛选，且HAVING可以使用聚合函数
例如select gender, count() from users where age <= 21 group by gender having count() >= 2;
选择性别和数量，年龄小于等于21，按性别分组，组大小大于等于2

排序
ORDER BY column1 ASC/DESC, column2 ASC/DESC, ... 前置column优先级高

分页
LIMIT 起始位置, 返回数量

执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
可以通过别名来验证

DCL 数据控制语言

主要控制用户访问哪些数据，以及对数据的操作权限

用户

查看所有用户
use mysql;
select USER from user;

创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password'
如create user 'kang'@'localhost' identified by '123456'，此时只能在本机访问数据库，且无权限
create user 'kang'@'%' identified by '123456'，此时可以在任何主机访问数据库，且无权限

修改用户密码
SET PASSWORD FOR 'username'@'host' = 'new_password'

删除用户
DROP USER 'username'@'host'

权限

权限等级，主要包括

ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 更新数据
DELETE 删除数据
CREATE 创建数据库或表
DROP 删除数据库或表
ALTER 修改表结构

查询权限
SHOW GRANTS FOR 'username'@'host'
授予权限
GRANT privileges ON database.table TO 'username'@'host'
如grant all privileges on . to 'kang'@'localhost' 授予kang在本机所有数据库的所有权限，数据库名和表明都可以用*通配
撤销权限
REVOKE privileges ON database.table FROM 'username'@'host'

函数

字符串函数

CONCAT(str1, str2, ...) // 连接字符串
LENGTH(str) // 返回字符串长度
UPPER(str) // 转换为大写
LOWER(str) // 转换为小写
LPAD(str, length, pad) // 左填充
RPAD(str, length, pad) // 右填充
TRIM(str) // 去除两端空格
SUBSTRING(str, start, length) // 截取字符串，下标从1开始

数学函数

ROUND(num, length) // 四舍五入
CEIL(num) // 向上取整
FLOOR(num) // 向下取整
ABS(num) // 绝对值
MOD(num1, num2) // 取余
POW(num, power) // 幂运算
RAND() // 随机数

随机生成6位验证码
select lpad(round(rand()*1000000,0), 6, '0');

日期函数

NOW() // 当前日期时间
CURDATE() // 当前日期
CURTIME() // 当前时间
YEAR(date) // 年
MONTH(date) // 月
DAY(date) // 日
DATE_ADD(date, INTERVAL value unit) // 日期加法
DATEDIFF(date1, date2) // 日期差，第一个日期减去第二个日期

如select date_add(now(), INTERVAL 70 day);

流程控制函数

IF(condition, value1, value2) // 条件判断，true返回value1，false返回value2
IFNULL(value1, value2) // 判断value1是否为空，为空返回value2，否则返回value1
CASE value WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END // 多条件判断

比如
select name, case gender when '女' then 't0' when '男' then 't1' else 't2' end from users;

约束

作用于表中字段，保证数据的完整性和一致性

NOT NULL // 非空
UNIQUE // 唯一
PRIMARY KEY // 主键，唯一且非空
FOREIGN KEY // 外键，两张表建立连接，保证数据一致性和完整性
CHECK // 检查，保证字段满足一定条件
DEFAULT // 默认值
AUTO_INCREMENT // 自增

可以在创建或者修改表时添加约束

创建表时添加约束

mysql> create table lmt(
    -> id int primary key auto_increment,
    -> name varchar(10) not null unique,
    -> age int check (age > 0 and age < 120),
    -> status char(1) default '1',
    -> gender char(1));
Query OK, 0 rows affected (0.15 sec)

mysql> desc lmt;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | NO   | UNI | NULL    |                |
| age    | int         | YES  |     | NULL    |                |
| status | char(1)     | YES  |     | 1       |                |
| gender | char(1)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> insert into user(name, age, status, gender) values ('john1',19,'1','男');
ERROR 1146 (42S02): Table 'sqlstudy.user' doesn't exist
mysql> insert into lmt(name, age, status, gender) values ('john1',19,'1','男');
Query OK, 1 row affected (0.02 sec)

mysql> insert into lmt(name, age, gender) values ('john1',19,'男');
ERROR 1062 (23000): Duplicate entry 'john1' for key 'lmt.name'
mysql> insert into lmt(name, age, gender) values ('john2',19,'男');
Query OK, 1 row affected (0.01 sec)

mysql> select * from lmt;
+----+-------+------+--------+--------+
| id | name  | age  | status | gender |
+----+-------+------+--------+--------+
|  1 | john1 |   19 | 1      | 男     |
|  3 | john2 |   19 | 1      | 男     |
+----+-------+------+--------+--------+
2 rows in set (0.00 sec)

以上为测试，发现插入失败id也会自增。

外键约束

添加外键
ALTER TABLE name ADD FOREIGN KEY (column) REFERENCES foreign_table_name(column)
删除外键
ALTER TABLE name DROP FOREIGN KEY name

建立外键后，删除父表中的数据时，对于子表有不同的操作方式

RESTRICT 禁止删除/更新
CASCADE 级联删除/更新
SET NULL 置空
NO ACTION 与RESTRICT相同

添加时设置行为
ALTER TABLE name ADD FOREIGN KEY (column) REFERENCES foreign_table_name(column) ON UPDATE CASCADE ON DELETE CASCADE

多表查询

SELECT * FROM table1, table2
会返回两个表的笛卡尔积，即两个表的所有组合

内连接

查询的是两个表中交集的部分
隐式
SELECT [] FROM table1, table2 WHERE table1.column = table2.column
显式
SELECT [] FROM table1 INNER JOIN table2 ON table1.column = table2.column

外连接

左外, 查询的是左表的所有数据和右表的交集
SELECT [] FROM table1 LEFT JOIN table2 ON table1.column = table2.column
右外, 查询的是右表的所有数据和左表的交集
SELECT [] FROM table1 RIGHT JOIN table2 ON table1.column = table2.column

自连接

SELECT [] FROM table refname1 JOIN table refname2 ON refname1.column = refname2.column

查询员工和对应的经理
select a.name, b.name from users a, users b where a.managerid = b.id;

如果没有经理，可以使用左外连接
select a.name, b.name from users a left join users b on a.managerid = b.id;

联合查询

UNION会去重，UNION ALL不会去重，需要查询的字段数和类型必须一致
SELECT [] FROM table1 UNION SELECT [] FROM table2
SELECT [] FROM table1 UNION ALL SELECT [] FROM table2

子查询

SELECT [] FROM (SELECT [] FROM table) refname

事务

事务是一组sql操作，要么全部成功，要么全部失败，是不可分割的工作单位。
每条sql语句都是一个事务，是自动提交的，如果需要多条sql语句作为一个事务，需要手动开启事务，提交或者回滚。

UPDATE account SET money = money - 1000 WHERE name = 'Tim';
异常
UPDATE account SET money = money + 1000 WHERE name = 'Lucy';

select @@autocommit; 结果为1，表示自动提交
set @@autocommit = 0; 关闭自动提交
提交 COMMIT
回滚 ROLLBACK

不修改配置，手动开启事务
此时观察到的是，本页面的修改可以被查到，但其他页面无法查到，直到提交。
START TRANSACTION开始
COMMIT或者ROLLBACK结束

四大特性

原子性事务是不可分割的工作单位，要么全部成功，要么全部失败
一致性事务执行前后，数据保持一致
隔离性多个事务之间是相互隔离的，一个事务不会影响其他事务，不受并发影响
持久性事务一旦提交或回滚，对数据库的改变是永久的

并发问题

脏读一个事务读取到另一个事务未提交的数据
不可重复读一个事务读取读取另一个事务未提交和已提交的数据，两次读取结果不一致
幻读查询时无数据，但插入时发现数据已存在(或再次查询时发现了数据)

隔离级别

读未提交 READ UNCOMMITTED 一个事务可以读取到另一个事务未提交的数据，存在三个问题
读已提交 READ COMMITTED 一个事务只能读取到另一个事务已提交的数据，存在后两个问题
可重复读 REPEATABLE READ 一个事务在自己的生命周期内多次读取到的数据是一致的，存在最后一个问题
串行化 SERIALIZABLE 一个事务在执行时，其他事务无法对其进行操作，不存在并发问题

SELECT @@transaction_isolation; 查看当前隔离级别，默认为REPEATABLE-READ
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 设置隔离级别

存储引擎

存储引擎是基于表的，是mysql体系结构的核心部分
客户端连接器 -> 连接池 -> [sql接口, 解析器, 优化器, 缓存] -> 存储引擎(多种可供选择,现在默认Inoodb) -> 文件系统

索引由存储引擎实现，不同存储引擎的索引实现方式不同。

创建表时指定存储引擎
CREATE TABLE name (column1 type, column2 type) ENGINE = engine_name

查看引擎
SHOW ENGINES

InnoDB

兼顾高可靠性和高性能，支持事务，行级锁，外键约束

xxx.ibd文件是表空间文件，存储数据和索引等，每张表都有
使用idb2sdi工具可以查看表空间文件的结构

逻辑存储结构
表空间 -> 段 -> 区(1M,64页) -> 页(16K) -> 行

MyISAM

早期的存储引擎，不支持事务，不支持行级锁(只支持表锁)，不支持外键约束，但是性能较高，访问速度快。

xxx.MYD文件存储数据，xxx.MYI文件存储索引，xxx.sdi文件存储表结构

MEMORY

将表存储在内存中，速度最快，但是数据量不能太大，断电后数据丢失
支持hash索引，xxx.sdi文件存储表结构

选择

对事务完整性要求高，并发下要求数据一致性，插入查询更新删除都比较频繁，选择InnoDB
读和插入比较频繁，更新和删除比较少，对事务完整性要求不高，选择MyISAM
数据量不大，对性能要求高，无法保证数据完整性，选择MEMORY，但目前被redis替代

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，可以高速获取数据

无索引时，查询时需要遍历整个表，时间复杂度为O(n)
优点：索引可以提高查询速度，降低数据排序的时间复杂度
缺点：索引会占用磁盘空间，降低插入更新删除的速度

索引结构

B+树索引适用于范围查询，排序，最常用三个存储引擎都支持
哈希索引只支持精确匹配，不支持范围查询三个中只有MEMORY支持
R树索引适用于空间数据，如地理位置只有MyISAM支持
全文索引适用于文本数据，通过建立倒排索引，快速匹配文本三个中只有MyISAM和InnoDB支持

为什么不使用平衡二叉树？
平衡二叉树的查询时间复杂度为O(logn)，但是树的高度会很高，导致查询变慢。

数据结构

B树

m阶B树，每个节点最多有m个子节点，最少有m/2个子节点。m个子节点指节点有m-1个关键字，m个子节点指针。
所有节点都存储数据。

B+树

和B树基础定义相同，但是B+树的非叶子节点不存储数据，只存储索引，叶子节点存储所有数据，且叶子节点之间有指针相连形成链表。

叶子和索引相同时，位于索引key的右侧，即大于等于key的值位于右侧。

mysql实际使用时，叶子节点使用双向链表连接。每个节点都存储在页中，页的大小为16K。
Inoodb的索引是聚集索引，即索引和数据存储在一起，直接存储在叶子节点中。
MyISAM的索引是非聚集索引，即索引和数据分开存储，叶子节点存储的是指向数据的指针。

hash

哈希索引是将索引列的值通过哈希函数计算出一个哈希值，然后将哈希值和对应的行指针存储在哈希表中。
只支持精确匹配，不支持范围查询，不支持排序。
Inoodb不支持哈希索引，但是具有自适应哈希索引，当B+树在指定条件下，会自动创建哈希索引。

总结
相比于红黑树，层数更少，查询速度更快，适合磁盘存储，适合范围查询。
相比于B树，页中存储的索引键值更多，更适合磁盘存储，适合范围查询。

索引分类

主键索引只能有一个，唯一且非空
唯一索引
普通索引
全文索引

根据存储形式又分为

聚集索引 InnoDB的主键索引，数据和索引放在一起，叶子结点保存了行数据。
二级索引(非聚集索引) 数据和索引分开，叶子结点存储对应的主键值。需要进行两次查找，先查找二级索引，再查找主键索引，称为回表。

聚集索引规则

如果有主键，InnoDB会选择主键作为聚集索引
如果表没有主键，InnoDB会选择第一个UNIQUE索引作为聚集索引
以上都无法满足，InnoDB会生成一个隐藏的键作为聚集索引

索引语句

创建索引
CREATE [UNIQUE] INDEX index_name ON table(column, ...)
一个索引可以关联多个列，称为联合索引，只关联一列称为单列索引

查看索引
SHOW INDEX FROM table_name

删除索引
DROP INDEX index_name ON table_name

索引优化

查看查询删等操作的次数
show global status like 'Com_______';

慢查询日志

记录了执行时间超过指定时间的sql语句，从而定位性能问题并进行优化

查看是否开启慢查询日志
show variables like 'slow_query_log';

修改系统配置
set global slow_query_log = 1; // 开启
set global long_query_time = 1; // 设置阈值1s

mysql文件存在/var/lib/mysql/下
慢查询日志文件为/var/lib/mysql/*-slow.log

profile

查看sql语句的执行时间
select @@profiling; // 查看是否开启
set profiling = 1; // 开启
show profiles; // 查看指令执行时间
show profile for query id; // 查看指令执行时间详情

explain/desc

查看sql语句的执行计划
explain select * from table_name\G; \G表示行转列格式化输出

id 查询序号

select_type 查询类型

SIMPLE 简单查询
PRIMARY 主查询
SUBQUERY 子查询

type 连接类型，依次从差到好

ALL 全表扫描
index 索引
range
ref 非唯一索引
eq_ref
const 唯一索引或主键

possible_keys 可能使用的索引

key 实际使用的索引

key_len 使用的索引长度

索引使用

最左前缀原则

使用了联合索引，只有当查询条件中包含了索引的最左前缀时，索引才会生效。
跳跃某一列会导致后面的索引字段失效。
至于索引字段的顺序，并不影响使用，因为mysql会自动优化。

范围查询

如果索引字段中有范围查询，该字段后的索引字段都会失效。
视频中演示的是，使用>时失效了，使用>=时未失效，暂时没明白。

索引失效

对索引使用函数
字符串不加引号，出现隐式转换
like模糊匹配，如果%在前，索引失效
or条件中有一个字段没有索引，索引失效
mysql评估全表扫描比索引扫描更快，索引失效。比如范围查找和is null

覆盖索引

查询的字段在索引字段中或者是主键，不需要回表，直接从索引中获取数据。
否则需要进行回表，遍历两次。

前缀索引

对字符串类型的字段，可以只取前几位进行索引，节省空间。
CREATE INDEX index_name ON table_name(column_name(n));

如果有相同前缀，使用？

单列和组合索引

如果查询条件包括了两个单列索引，只会走一个，然后回表。
涉及多个字段的查询，使用组合索引，可以减少回表次数。
有时有多个可供选择的单列和组合索引，mysql可能会自动选择单列而不是更高效的组合索引，这是可以在命令中指定索引。

设计原则

对查询频繁的字段建立索引，对更新频繁的字段不建立索引。
常常作为查询条件，排序条件，分组条件，字段需要建立索引。
对区分度较高的字段建立索引，区分度越高，索引效果越好。
较长的字符串字段建立前缀索引。
尽量使用联合索引，可以覆盖索引，节省空间。

视图

视图是一张虚拟的表，是一个动态的表，不存储数据。
视图可以进行增删改查，但是实际上是对基表的操作。

InnoDB

逻辑存储结构

表空间 ibd文件，存储记录、索引等数据
段分为数据段、索引段和回滚段。数据段为B+树的叶子节点，索引段为B+树的非叶子节点。
区表空间单元结构，大小为1M
页 Inoodb的最小存储单位，大小为16K，为了保证页的连续性，Inoodb每次申请4-5个区。
行 Trx_id 每次改动会将事务id写入行数据，对记录进行改动时会把旧的版本写入undo log，使用Roll_pointer找到旧版本。

内存架构

Buffer Pool

内存的一个区域，缓存磁盘上经常操作的真实数据，增删改查时，先操作缓冲池中的数据，再以一定频率刷新到磁盘，从而减少磁盘IO。

free page 空闲页
clean page 干净页，页中的数据和磁盘上的数据一致
dirty page 脏页，页中的数据和磁盘上的数据不一致，需要刷新到磁盘

Change buffer

更改缓冲区。针对非唯一二级索引页

二级索引通常是非唯一的，以相对随机的顺序插入二级索引，删除和更改可能影响索引树种不相邻的二级索引页，每次都操作的话会导致大量的随机IO，因此Inoodb使用更改缓冲区来缓存这些更改，然后适时合并到Buffer Pool中，最终刷新到磁盘。

Adaptive Hash Index

自适应哈希索引，用于优化Buffer Pool中的数据查询

Log buffer

日志缓冲区，用于缓存要写入磁盘中的日志数据（redo log，undo log），定期刷新到磁盘。
innodb_flush_log_at_trx_commit参数控制刷新策略，0表示每秒写入并刷新到磁盘，1表示每次事务提交时写入并刷新，2表示每次事务提交时写入，但是每秒刷新。
innodb_log_buffer_size参数控制缓冲区大小。

磁盘架构

System tablespace

系统表空间是更改缓冲区的存储区域，如果表是在系统表空间而不是每个表文件或者通用表空间中创建的，也可能包含表和索引数据结构

innodb_data_file_path参数控制系统表空间的大小和数量

File-Per-Table tablespace

每个表都有一个独立的表空间，存储表和索引数据，存储在单个文件中，文件名为表名.ibd

General tablespace

多个表共享一个表空间，需要手动创建，适用于多个表的数据和索引都比较小的情况。

Undo tablespace

存储事务的undo log，初始化会自动创建两个默认的undo表空间。

Temporary tablespace

存储临时表数据。

Doublewrite buffer

双写缓冲区，用于保护数据页的一致性，当数据刷新到磁盘时，先写入双写缓冲区，然后再写入数据文件，如果写入数据文件失败，可以从双写缓冲区恢复。

Redo log

包括redo log buffer和redo log file，用于保护事务的持久性。
前者位于内存中，后者位于磁盘中，事务提交后会把修改信息存入改日志中，用于恢复数据。

后台线程

将缓冲池中的数据刷新到磁盘，清理脏页，合并更改缓冲区，刷新日志等。

master thread 核心线程，负责调度其他线程，将缓冲池中的数据异步刷新到磁盘，刷新脏页，合并更改缓冲区。
IO thread 负责读写磁盘数据，异步非阻塞IO
- read thread 读线程，负责读取数据
- write thread 写线程，负责写入数据
- insert buffer thread 负责将插入缓冲区中的数据刷新到磁盘
- log write thread 负责将日志写入磁盘
purge thread 负责回收已经提交的事务的undo log
Page Cleaner thread 负责将脏页刷新到磁盘

事务原理

原子性，一致性，持久性由redo log和undo log保证
隔离性由锁和MVCC保证

流程 WAL(write ahead log)
redo log 保证一致性
修改Buffer Pool中的数据，写入redo log，事务提交时将redo log写入磁盘，后续发生异步脏页刷新时如果刷新成功，就可以清理redo log，如果刷新失败，可以通过redo log恢复数据。

undo log 保证原子性
记录数据被修改前的信息，作用是提供回滚和MVCC功能
undo log是逻辑日志，记录的是修改前的数据，而redo log是物理日志，记录的是修改后的数据。

为什么不直接刷新Buffer Pool中的数据到磁盘？
性能问题，Buffer Pool中的数据可能会被多次修改，如果每次都刷新到磁盘，会导致大量的随机IO，降低性能。

MVCC 多版本并发控制

当前读
读取的是最新的数据，读取时保证其他事务不会修改数据。

快照读
读取的是快照数据，读取到的是数据的可见版本，有可能是历史数据，不加锁，不会阻塞其他事务。
read commited 每次select都生成一个快照读
repeatable read 事务开始后第一个select生成一个快照读，后续select使用同一个快照读
serializable 退化为当前读

MCCC实现取决于

记录中的隐藏字段
- DB_TRX_ID 事务id
- DB_ROLL_PTR 回滚指针，指向记录的历史版本
- DB_ROW_ID 隐藏主键，有主键时使用主键，没有时使用隐藏主键
undo log
- 事务开始时，将当前版本的数据写入undo log，事务提交时删除undo log
- 事务回滚时，将undo log中的数据写入Buffer Pool
- 事务对同一条记录的修改，会导致undo log生成记录版本链表
read view 读视图
- 快照读执行时提取数据的依据，记录并维护当前活跃的事务
- 核心字段
  - m_ids 事务id列表
  - min_trx_id 最小事务id
  - max_trx_id 预分配的事务id，是当前事务id的最大值+1
  - creator_trx_id 创建者事务id
- 将trx_id和creator_trx_id进行比较
  - trx_id >= max_trx_id 不可以访问
  - trx_id < min_trx_id 说明数据是已经提交的
  - trx_id = creator_trx_id 数据是当前事务修改的
  - trx_id >= min_trx_id and trx_id < max_trx_id，且trx_id不在m_ids中，说明数据是已经提交的，可以访问

read commited

每次快照读都会生成一个新的read view

repeatable read

仅在第一次快照读时生成一个read view，后续快照读使用同一个read view

持久化梳理

redo log流程

Redo Log 记录的是物理日志(就是记录修改了那个表空间，哪个页，修改了什么数据)，记录磁盘数据页的修改，保证服务崩溃后数据的一致性。

数据从磁盘加载到内存
内存中修改数据
新数据写入Redo Log Buffer
Redo Log Buffer写入Redo Log File
Redo Log File持久化刷新到数据库磁盘
定期将内存中修改的数据刷新到磁盘中

好处，减少随机IO和IO次数，提高性能

从Redo Log Buffer到Redo Log File的刷新策略由innodb_flush_log_at_trx_commit参数控制
在Redo Log Buffer和Redo Log File之间还有一层用户不可见的系统缓冲区OS Buffer(page cache)，用于提高IO性能
Redo Log Buffer的内容会随着事务的进行不断增加。
0 延迟写，提交事务时不刷新，每秒刷新到OS Buffer，并调用fsync刷新到Redo Log File，可能会丢失1s内的数据
1 实时写，提交事务时write刷新到OS Buffer，并调用fsync刷新到Redo Log File
2 延迟刷新，提交事务时刷新到OS Buffer，每秒调用fsync刷新到Redo Log File

Redo Log File的大小固定位1G，可以有多个(4)文件，采用循环写入的方式，类似于循环队列

如果刷新到Redo Log File时失败了怎么办？面试的时候被问到过，但是没答上来。
Redo Log File是循环写入的，如果刷新失败，会导致Redo Log File中的数据不一致，这时会导致数据库无法启动，需要手动恢复。

Redo Log两阶段提交

prepare 预提交，将事务的redo log写入到redo log buffer，标记为prepare
commit 事务commit后，标记为commit

重启时，redo log处于commit状态，或者存在bin log中，可以通过redo log恢复数据，否则就回滚。

undo log流程

记录的是逻辑日志，也就是sql语句，用于回滚事务和MVCC
通过两个隐藏列trx_id和roll_pointer实现，建立版本链，在事务查询时生成read view。

bin log流程

记录的是逻辑日志，用于主从复制和数据备份
有三种格式，statement，row，mixed

statement 记录sql语句，对于与时间相关的函数，可能会导致数据不一致
row 记录行数据的变化，保证数据一致性，但是日志量大
mixed 根据sql语句的特点自动选择，是statement和row的混合，默认statement，涉及日期、函数相关的时候采用row模式，既减少了数据量，又保证了数据一致性。

采用追加的形式写入，也采用WAL机制，用sync_binlog参数控制刷新到磁盘的时机
0 不刷新，由系统决定什么时候刷盘
1 每次提交事务时刷新
N 每N次提交事务时刷新

事务执行过程中，redo log不断记录。
redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到 bin log文件中。

链接：https://www.nowcoder.com/questionTerminal/f1484c39c10949e8a229e84d7508b54a?
来源：牛客网

得分点
持久性、一致性、MVCC、备份

参考答案
标准回答

redo log用来实现事务的持久性，undo log用来实现事务的一致性以及MVCC，bin log主要用于数据备份。

redo log

重做日志用来实现事务的持久性，它由两部分组成：一是内存中的重做日志缓冲（redo log buffer），它是易失的；二是重做日志文件（redo log file），它是持久的。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。

undo log

事务有时还需要进行回滚操作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段中，这个段称为undo段。undo段位于共享表空间内。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。当InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。

除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

bin log

二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。总的来说，二进制日志主要有以下几种作用：

恢复：某些数据的恢复需要二进制日志。
复制：通过复制和执行二进制日志使一台远程的MySQL数据库与一台MySQL数据库进行实时同步。
审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。
加分回答

redo log通常是物理日志，记录的是页的物理修改操作。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。
undo log是逻辑日志，根据每行记录进行记录，而undo log是需要进行随机读写的。同时，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。
bin log是在数据库的上层产生的，并且bin log 不仅仅针对于InnoDB存储引擎，MySQL中任何存储引擎对于数据库的更改都会产生二进制日志。bin log 是一种逻辑日志，其记录的是对应的SQL语句，其记录的是对于每个页的修改，bin log只在事务提交完成后进行一次写入。

锁

全局锁

锁定数据库中的所有表，此时只允许读操作，写操作阻塞。

典型使用场景是mysqldump备份，对所有表进行锁定，获取数据一致性
但是InnoDB支持非锁定备份，可以通过--single-transaction参数实现，原理是通过快照读。

加锁指令

flush tables with read lock
解锁指令
unlock tables

表级锁

锁定整张表

表锁

read lock (共享)读锁，只允许读操作，写操作阻塞，所有客户都一样
write lock (独占)写锁，当前客户对表可以读写，其他客户无法读写
命令

lock table table_name read/write
unlock tables

元数据锁

系统自动加的锁，用于保护元数据，如表结构，表状态等，主要为了避免DDL操作和DML操作冲突

select等操作会自动加上SHARED_READ锁，insert等操作会自动加上SHARED_WRITE锁，DDL操作会自动加上EXCLUSIVE锁

意向锁

行级锁

锁定表中的某一行

主从复制

将主数据库的DDL和DML通过二进制日志传到从数据库，然后在从库上对日志重新执行，从而实现主从数据的一致性。
优点
主库出现问题时，可以快速切换到从库
实现读写分离，降低主库压力
在从库备份数据，不影响主库性能

bin log

bin log是二进制日志，记录了对数据库执行更改的所有操作，
从库的iothread会读取主库的bin log，然后写入到relay log(中继日志)中；sqlthread会读取relay log，然后执行sql语句。

mysql in c

初始化

MYSQL* mysql_init(MYSQL *mysql)

参数为NULL时，返回一个新的MYSQL结构体，否则初始化一个已经存在mysql指向的的MYSQL结构体

连接

MYSQL* mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)

参数分别为MYSQL结构体指针，主机名，用户名，密码，数据库名，端口号，套接字名，客户端标志

下发mysql指令

int mysql_query(MYSQL *mysql, const char *stmt_str)

获取查询结果

MYSQL_RES* mysql_store_result(MYSQL *mysql)

MYSQL_RES是由函数内部动态分配的，需要调用mysql_free_result()释放

unsigned int mysql_num_fields(MYSQL_RES *result) // 获取结果集中的列数
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *res) // 获取结果集中的列信息，每次调用都会返回下一列的信息，返回NULL时表示列信息已经全部返回
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res) // 获取所有列属性信息
my_ulonglong mysql_num_rows(MYSQL_RES *res) // 获取结果集中的行数
MYSQL_ROW mysql_fetch_row(MYSQL_RES *res) // 获取结果集中的一行数据，自动迭代。

返回结果MYSQL_ROW本质上是一个二级指针，MYSQL_RES保存的是查询到的多行结果，可以看作MYSQL_ROW数组

可以先分别获得行数和列数，然后遍历二维数组获取全部内容。
完成后需要使用

void mysql_free_result(MYSQL_RES* result)

释放资源

关闭连接

void mysql_close(MYSQL *sock)

标签：事务,log,索引,mysql,数据,name
From： https://www.cnblogs.com/wangerblog/p/18144774