个人复习总结~ 文中举例图片暂时存在一些问题，之后有时间会逐步替换掉
MySQL基础
数据类型
- 数值类型
| 类型 | byte |
|---|---|
| TINIINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT | 4 |
| BIGINT | 8 |
| FLOAT | 4 |
| DOUBLE | 8 |
| DECIMAL(M,D) | 总长度为M，小数位数占D位的数值 |
- 日期类型
| 类型 | byte | 格式 |
|---|---|---|
| DATE | 3 | YYYY-MM-DD |
| TIME | 3 | HH:MM:SS |
| YEAR | 1 | YYYY |
| DATE TIME | 8 | YYYY-MM-DD-HH:MM:SS |
| TIMESTAMP | 4 | YYYYMMDD HHMMSS |
- 字符串类型
| 类型 | byte |
|---|---|
| CHAR | 0~255 |
| VARCHAR | 0~65535 变长 |
| TINYBLOB | 0~255 二进制字符串 |
| MEDIUMBLOB | |
| LONGBLOB | |
| BLOB | 0~65535 二进制形式长文本数据 |
| TINYTEXT | |
| MEDIUMTEXT | |
| TEXT | 0~65535 长文本数据 |
| LONGTEXT | 极大文本数据 utf-8编码大约10921个字符 |
SQL语句
select distinct
<select list>
from
<left_table> <join_type>
join <right_table> on <join_condition>
where
<where_condition>
group by
<groupby_list>
having
<having_condition>
order by
<order_condition>
limit …
存储引擎
MySQL Server的核心基础代码和存储引擎是分离的，
MySQL支持不同的表使用不同的引擎。
InnoDB 和 MyISAM
在MySQL 5.5及以后的版本中，InnoDB是MySQL的默认引擎。
InnoDB的优点有：
- 灾难恢复性好。
- 支持全部四种级别的事务。
- 使用行级锁。
- 对于InnoDB引擎中的表，其数据的物理组织形式是簇表(Cluster Table)，数据按主键来组织，也就是说主键索引和数据是在一起的，数据按主键的顺序物理分布。数据表的另一种常见形式是非簇表，其索引是有序的，而数据是无序的。
- 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取，相比之下MyISAM只是缓存了索引。
- 支持外键。
- 支持热备份。
MyISAM的特性：
- 可以配合锁，实现操作系统下的复制备份、迁移
- 使用表级锁，并发性差
- 支持全文检索
- 主机宕机后，MyISAM表易损坏，灾难恢复性不佳
- 无事务支持
- 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的，可能引发过多的系统调用且效率不佳
- 数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
| MyISAM | InnoDB | |
|---|---|---|
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 支持事务 | 否 | 是 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是 |
| 支持外键 | 否 | 是 |
| 支持全文索引 | 是 | 是(5.6后支持) |
| 适合操作类型 | 大量select | 大量insert delete update |
存储引擎相关的命令
查看所有存储引擎 show engines;
查看当前默认存储引擎 show variables like '%storage_enginw%';
查看表的存储引擎 show table status like "table_name";
MySQL8.0为什么取消缓存
MySQL查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较，如果匹配，则返回上一次查询的结果。
这种查询缓存需要严格字符匹配，比如说SELECT和select都是匹配不上的。
而且如果有一张表发生了改动，那么将会导致所有与该表相关的查询缓存都失效，对于经常改动的表来说，这种缓存命中率低非常鸡肋。
这种查询缓存比较有效的场景也有，比如说很少改动的表，需要做百万千万级数据查询出少量的数据，那这种时候做查询结果缓存还是可以的。
除此之外，查询缓存的另一个问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。
索引
MySQL支持的索引类型
-
B-Tree索引(B树)：最常见的索引类型，支持大部分索引引擎
-
B+Tree索引：在B-Tree索引的基础上进行优化的结果，MySQL中大部分存储引擎会支持B+Tree索引。如果没有为数据库或数据表显式地指定索引类型，则MySQL底层会默认使用B+Tree索引
-
Hash索引：比较适合存储Key-Value型数据。查询Key-Value型数据时，会根据Key快速获取数据。但是Hash索引有一个弊端，即不适合根据某个数据范围来查询数据。
-
Full-Text索引：主要用于全文检索。在MySQL 5.6版本之前，Full-Text索引只支持MyISAM存储引擎，从5.6版本开始，InnoDB存储引擎开始支持Full-Text索引
创建索引
create index idx_id on people(id);
删除索引
drop index idx_id on people;
聚簇索引和非聚簇索引
MySQL中，索引分为两大类，聚簇索引和非聚簇索引。
聚簇索引是按照数据存放的物理位置为顺序为顺序的，非聚簇索引则不同。
- 聚簇索引能够提高多行检索的速度
- 非聚簇索引则对单行的检索速度很快
InnoDB使用的是聚簇索引，将主键组织到一B+树中，行数据存储在叶子节点上。
比如对Name进行条件搜索：
- 在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键
- 使用主键在主索引B+树再一次检索，到达叶子节点获取整行数据
MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没有什么不同，节点的结构完全一致，只是存储的内容不同。
主键索引的B+树存储了主键，辅助键索引B+树存储了辅助键。
表数据存储在独立的地方，B+树的叶子节点存储指向真正表数据的地址。
为什么是B+树
数据查询步骤：
- 将索引页加载到缓存中，从缓存中找到数据页
- 将数据页加载到缓存，然后将数据返回
单元素查询：
- B树只要找到匹配元素即可，查找不稳定
- B+树必须最终查找到叶子节点，B+树查找是稳定的
范围查询：
- B树先自顶向下找到起始数据，再通过中序遍历找到其余的，遍历过程很繁琐
- B+树先自顶向下找到起始数据，再直接通过双向链表遍历
总结：
- 单一节点存储更多的元素，使IO次数减少
- 所有查询都要查找到叶子节点，查询性能稳定
- 所有叶子节点形成有序链表，便于范围查询
InnoDB的B+Tree底部是双向链表
InnoDB是以页为存储单位的，
每个B+树的叶子节点都是一个页的大小的倍数，
默认一页的大小是16K。
每个页包含两个页指针，一个指向上页，一个指向下页。
所以B+树的叶子节点之间是双向链表的结构。
索引的优缺点
优点：
- 所有字段类型都可以添加索引
- 可以为数据表中的一列或者多列添加索引
- 能够极大地提高数据的查询性能
- 能够提高数据分组与排序的性能
缺点：
- 索引本身需要占用一定的存储空间，如果大量的使用索引，则索引文件会占用大量的磁盘空间
- 索引的创建与维护需要耗费一定的时间，随着数据量的不断增长，耗费的时间会越来越长
- 对数据表中的数据进行增加、删除和修改操作时，MySQL内部需要对索引进行动态维护，这也会消耗一定的维护时间。
索引的创建原则
- 尽量使用小的数据类型的列创建索引，节省空间
- 尽量使用简单的数据类型的列创建索引，系统开销小一些（比如int比varchar简单)
- 尽量不要在null值字段上创建索引，null会使索引、索引的统计信息和比较运算更加复杂
适合创建索引的场景
- 必须为数据表中的主键和外键添加索引
- 数据表中的数据达到一定量级时，应当为数据表适当添加索引
- 与其他表进行关联的字段，并且经常进行关联查询时，应当为连接字段创建索引
- 作为where子句的条件判断字段，并且经常用来进行相等比较操作的字段，应当添加索引
- 作为order by语句的的字段，并且经常用来执行查询操作，应当添加索引
- 作为搜索一定范围内的字段，并且经常用来执行查询操作，应当添加索引
索引SQL
- 创建【普通索引】
普通索引查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
create table food_menu (
id int not null auto_increment primary key,
food_id int not null default 0,
price float,
create_time date,
create_shop_id int,
index shop_index (create_shop_id)
);
- 创建【唯一索引】
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后就会停止继续检索。
创建唯一索引的列值必须唯一，但是允许值为空。
如果创建的唯一索引中包含多个字段，也就是复合索引，
则索引中包含的多个字段的值的组合必须唯一。
create table if not exists market(
id int not null unique,
name varchar(20) not null default "",
address varchar(20) not null default "",
door_number int not null unique,
unique index market_index (id,door_number)
);
unique index market_index (id,door_number)
- 创建【主键索引】
主键索引是特殊类型的唯一索引，与唯一索引不同的是，
主键索引不仅具有唯一性,而且不能为空
primary key
- 创建【单列索引】
单列索引表示在创建的索引中，只包含数据表中的单个字段或列
MySQL支持在一张数据表中创建多个单列索引
create table t1(
id int not null,
t_name varchar(10) not null default "",
index id_index (id)
);
- 使用索引查询数据
这里explain是为了查看select语句是否真的用到了索引。
explain select * from food_menu where create_shop_id = 1;
- 【删除索引】
alter table t1 drop index id_index;
-- 或者
drop index id_index on t1;
- 【隐藏索引】
create table t2(
num1 int,
num2 int
);
-- 在num1上创建普通索引
create index t2_index on t2(num1);
-- 在num2上创建隐藏索引
create index t22_index on t2(num2) invisible;
隐藏索引，只需要将待删除的索引设置为隐藏索引，
使查询优化器不再使用这个索引，
确认将索引设置为隐藏索引后系统不受任何影响，就可以彻底删除索引。
这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。
将索引设置为隐藏索引，通过查询优化器的开关，使隐藏索引对查询优化器可见， 通过explan对索引进行测试，确认新创建的隐藏索引有效，再将其设置为可见索引， 这种方式就是灰度发布。
show create table t2;
show index from t2;
-- 使用explain 查看查询优化器对索引的使用情况
explain select * from t2 where num1=0;
explain select * from t2 where num2=0;
-- 隐藏索引默认对查询优化器是不可见的
-- 想对查询优化器可见可以
-- select @@optimizer_switch 可以看到use_invisible_indexs=off
-- set session optimizer_switch="use_invisible_indexs=on"
- 【降序索引】
MySQL 8.0 版本支持降序索引，但是只有innoDB存储引擎支持降序索引
同时只有BTREE索引支持降序索引
另外不再对group by 语句进行隐式排序
create database testDB;
use testDB;
create table test_desc(
c1 int,
c2 int,
index idx (c1 desc , c2 desc)
);
show create table test_desc;
- 【函数索引】
支持在索引中使用函数或者表达式的值,
也就是在索引中可以包含函数或者表达式
函数索引中支持降序索引
create table t3(
c1 varchar(10),
c2 varchar(10)
);
create index c2_index on t3 ((upper(c2))); -- 为c2字段创建一个将字段转换为大写的函数索引
show index from t3;
各种索引总结
- 单例索引
只包含一个字段的索引。 - 复合索引/联合索引
包含两个或以上字段的索引。 - 唯一索引
在表上一个或多个字段组合建立的索引，这个或这几个字段的值组合起来在表中不可以重复。 - 主键索引
特殊的唯一索引，不过唯一索引允许null值，而主键列不允许null值。一张表最多建立一个主键，也可以不建立主键。 - 聚簇索引
B+树叶子节点存放的数据是按照物理位置顺序来放置的，非聚簇索引反之。
使用索引的场景
- 全值匹配
全值匹配是指在MySQL的查询条件中包含索引中的所有列，
并且针对索引中的每列进行等值判断。 - 查询范围
- 匹配最左前缀
MySQL在使用联合索引查询数据时从联合索引中的最左的列开始查询，并且不能跳过索引中的列，如果跳过索引中的列查询数据，则在后续的查询中将不再使用索引。 - 查询索引列
MySQL在查询包含索引的列或者查询的列都在索引中时，查询的效率比使用select * 或者查询没有索引的列的效率高很多。 - 匹配字段前缀
如果数据表中的字段存储的数据比较长，则在整个字段上添加索引会影响数据的写入性能，增加MySQL维护索引的负担。可以在字段的开头部分添加索引，并按照此索引进行数据查询。
alter table UserI add index index1(email(7));
- 精确与范围匹配索引
在查询数据时，可以同时精确匹配索引并按照另一个索引的范围进行数据查询。 - 连接查询匹配索引
使用JOIN连接语句查询多个数据表中的数据，并且当实现JOIN连接的字段上添加了索引时，MySQL会使用索引查询数据。 - like匹配索引
当like语句中的查询条件不以通配符开始时，MySQL会使用索引查询数据。
无法使用索引的场景
- 以通配符开始的LIKE语句
当使用以通配符开始的LIKE语句查询数据时，MySQL不会使用索引
比如：
explain select * from t1 where name like "%t%";
- 数据类型转换
当查询的字段数据进行了数据类型转换时，MySQL不会使用索引查询数据。
name是varchar类型。
explain select * from t2 where name = 0;
- 联合索引未匹配最左列
type类型为ALL，说明MySQL进行了全表扫描操作
这样就起作用了
- OR语句
查询语句中使用OR来连接多个查询条件时，只要查询条件中存在未创建索引的字段，MySQL就不会使用索引
-- id加了索引，age没有
explain select * from t3 where id=1 or age=1;
- 计算索引列或使用函数
查询数据时对查询条件的字段添加了索引，而且在查询数据时对字段进行了计算或者使用了函数，此时MySQL不会使用索引
explain select * from t3 where exp(id)=1;
- 范围条件右侧的列无法使用索引
使用联合索引查询数据时，如果按照联合索引中字段的某个范围查询数据，则此字段后面的列无法使用索引，会进行全表扫描
explain select * from t3 where id > 1 and door = 1;
这点我也很疑惑，，，，这里是range
- 使用<>或!=操作匹配查询条件
这个我的尝试也有问题。
- 匹配not null值
不是所有情况都会……
补充说明
当一张表的字段没有非索引字段时，select * 的查询内容和条件都在联合索引里，联合索引树的叶子节点包含 索引列和主键 ，所以查联合索引树就能查到全部结果了。
为一张表创建(a,b,c)联合索引，相当于建了(a) (a,b) (a,b,c)，
但是使用
where a = 0;
where a = 0 and b = 0;
where a = 0 and c = 0;
where a = 0 and b = 0 and c = 0;
where a = 0 and c = 0 and b = 0;
都可以生效
是因为使用and时，MySQL查询优化器会做调整(MySQL 高版本)。
比如a=0 and c = 0 走的是 (a,b,c)，如果是低版本，应该只有a走索引，剩下的走全表。
explain
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
一些现象
create table if not exists api(
id bigint not null unique,
name varchar(20) not null default "",
url varchar(20) not null default "",
flag varchar(2) not null default "0",
method varchar(10) not null default "GET",
service_id bigint not null,
namespace_id bigint not null,
unique index api_index (id,service_id,namespace_id)
);
insert into api(id,service_id,namespace_id)
values (1,2,3),(2,2,3),(3,1,2),(4,4,1),(5,1,2);
range:只检索给定范围的行，使用一个索引来选择行
range:只检索给定范围的行，使用一个索引来选择行
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
index与ALL区别为index类型只遍历索引树
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
range:只检索给定范围的行，使用一个索引来选择行
顺序不影响
事务
在Mysql中开启事务：
begin或者start transaction
提交：commit
回滚：rollback
事务的特点ACID
数据库事务是构成单一逻辑工作单元的操作集合，一组sql语句要么成功要么失败。
Spring中使用@Transactional注解配置事务管理
- 原子性
Undo log 实现
事务中的所有操作作为一个整体像原子一样,
事务作为一个整体被执行，包含在事务中的对数据库的操作要么全部被执行，要么全部都不执行。
比如InnoDB事务内执行了一个insert多个值的语句，只要其中一个值插入失败就全部失败；对于MyISAM，在出错之前的值是可以被正常插入到表中的。 - 一致性
事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指：
-/ 系统的状态满足数据的完整性约束
-/ 系统的状态反应数据库本应描述的现实世界的真实状态
事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应该满足约束。 - 隔离性
并发的事务不会相互影响，对数据库的影响就像串行执行时一样。
多个事务并发执行时，一个事务的执行不影响其他事务的执行。 - 持久性
redo log 实现
事务一旦提交，其对数据库的更新就是持久的。任何事务或者系统故障都不会导致数据丢失。已被提交的事务对数据库的修改应该被永久保存在数据库中。
redo log和undo log
redo log :
将修改了哪些东西记录下来，已经commit了的内容，就算出现故障，再次恢复是也要有。
undo log:
为了回滚而记录的东西，撤销日志
数据库的四个隔离级别
事务的隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
理论上包含如下4个隔离级别，按隔离级别从低到高排列如下：
- read uncommitted 读未提交
事务可以看到其他事务更改了但还没有提交的数据，即存在脏读的情况。 - read committed 读已提交
事务可以看到在它执行的时候，其他事务已经提交的数据，不允许脏读，但允许不可重复读。 - repeatable read 可重复读
同一个事务内，同一个查询请求，若多次执行，则获得的记录集是相同的，但不能杜绝幻读。 - serializable 序列化
最高级别的锁，解决了幻读，将锁施加在所有访问的数据上。
为查询语句涉及的数据加上共享锁，阻塞其他事务修改真实数据。
不同的隔离级别会导致不同的并发异常
| 事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
读未提交 read uncommitted | T | T | T |
读已提交 read committed | T | T | |
可重复读 repeatable read | T | ||
串行化 serializable |
实际上MySQL在可重复读级别下就可以禁止幻读了。
设置事务的隔离级别：
set session transaction isolation level xxx;
set global transaction isolation level xxx;
脏读：
一个会话读取到另一个会话未提交的数据
不可重复读：
一个会话里，未经过其他操作，同样的SQL查询语句，得到了不同的结果。
查询时暴露的问题幻读：
另一个会话里添加了某数据并提交，本会话里再插入出现了报错(比如已经有这个数据了)
插入时暴露的问题
MySQL与SQL的标准不一样，在可重复读隔离级别实际上已经就解决了幻读问题。
怎么解决脏读、不可重复读、幻读这些问题？
- 读操作 MVCC(生成ReadView，通过ReadView找到符合条件的记录版本(undo构建))，写操作加锁
- 读写都加锁 都去排队去吧
事务id
一个事务可以是一个只读事务，或者是一个读写事务。
start transaction read only 开启一个只读事务
start transaction read write 开启一个读写事务
或者 begin 、start transaction 开启的事务默认 是读写事务
只读事务不可以对普通的表(其他事务也能访问到的表)进行增删改，但可以对临时表进行增删改。
如果某个事务执行过程中对某个表执行了增删改操作，那么InnoDB存储引擎就会给它分配一个独一无二的事务id。
对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增删改操作时才会为这个事务分配一个事务id。
对于读写事务来说，只有在它第一次对某个表执行增删改时为这个事务分配一个事务id。
事务id本质上就是一个数字，服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当做事务id分配给该事务，并且把该变量自增1。
MySQL的锁
锁的分类 🔒
按照锁的粒度：
表锁、 行锁
按照锁的思想:
悲观锁、 乐观锁
按照兼容性：
共享锁: S锁 在读取一条记录时，先获取该记录的S锁
独占锁：X锁 在事务要改动一条记录时，需要先获取X锁
兼容性：
| 兼容 | X | S |
|---|---|---|
| X | No | No |
| S | No | Yes |
SELECT * FROM A WHERE id = 1 lock in share mode；（加S锁）
SELECT * FROM A WHERE id > 0 for update; (加X锁)
按照锁的实现：
意向锁 (表级锁)、 记录锁
间隙锁、 临键锁
插入意向锁、自增锁
表锁
MySQL Server级别的锁大致有以下两种
- Table locks 表锁
lock tables table_name read;
- Global locks 全局锁
select get_lock('my lock', 10);
MySQL支持对MyISAM和MEMORY表进行表级锁。
行锁
行锁=记录锁
行级锁定的优点：
- 当前很多线程中访问不同的行时只存在少量锁定冲突
- 回滚时只有少量的更改
- 可以长时间锁定单一的行
数据库的锁定技术往往是基于索引来实现的。
如果SQL语句里没有利用到索引，那么InnoDB将会执行一个全表扫描，锁定所有的行。
锁过多的行，增加了锁的竞争，降低了并发率，建立索引是很重要的，
InnoDB需要索引来过滤(在存储引擎层中)掉那些不需要访问的行。
InnoDB有几种不同类型的行锁技术，如记录锁(record lock)、间隙锁(gap lock)和next-key锁。
记录锁
索引记录锁，建立在索引记录上的锁。
假设现在有这么个表：
| num | name |
|---|---|
| 1 | 赵 |
| 3 | 钱 |
| 8 | 李 |
| 20 | 毛 |
给num=8加记录锁，就是把着条记录给锁住了。
间隙锁
这是施加于索引记录间隙上的锁。
还是上面假设的那张表，给8号记录加gap锁，意思就是不允许别的事务在num值为8的记录前的间隙插入新记录。
现在有个4号想插入，定位到着条记录的下一条是8，8有gap锁，会阻塞插入操作，直到拥有这个gap锁的事务提交后，区间(3,8)才能被插入。
但是，如果我在20后面插入，怎样能够防止呢。
数据页有两个伪记录：
Infimun 表示该页面中最小的记录
Supremun 表示该页面中最大的记录
所以对于20后的防止插入，可以利用supremun记录。
怎样解决幻读问题
gap锁涉及到一个实际问题，怎样解决幻读问题，有两种方案：
- MVCC
- 加锁，第一次读幻影是不在的，所以无法直接加锁，就需要间隙锁来解决
next-key锁
记录锁+间隙锁的组合，也可以解决幻读问题。
插入意向锁
事务在插入一条记录时，判断有没有gap。
在等待的时候，也需要在内存中生成一个插入意向锁，
表示有个事务也想在某个间隙插入新记录。
意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能。
等待行锁超时
有时我们在慢查询日志中会看到一些很耗时的查询，但是单独执行却很快，此时可能就是因为该查询因等待InnoDB行锁而超时。
MVCC原理
MVCC(Multiversion Concurrency Control)
MVCC的原理主要包括两个部分：版本链+ReadView
聚簇索引中都包含两个必要的隐藏列：
- trx_id: 每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id
- roll_pointer: 每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中。roll_pointer相当于一个指针，通过这个指针找到记录修改前的信息。
每次对记录进行改动，都会记录一条undo日志，每条undo日志也有一个roll_pointer属性，可以将这些undo日志都连起来，串成一个链表。
所有版本都会被roll_pointer属性连接成一个链表，就形成了一个版本链。
ReadView是快照读操作产生的读视图，属性有活跃的事务ID、最小的事务ID、尚未分配的下一个事务ID、生成该ReadView的事务ID。
- 读已提交：每次读取数据前都生产一个ReadView
- 可重复读：在第一次读取数据时生成一个ReadView
日志
MySQL的日志有：
redo log 事务日志，重做日志
undo log 回滚日志
binlog 二进制日志，归档日志
错误日志
查询日志
慢查询日志
redo log
redo log是InnoDB存储引擎独有的。
MySQL实例挂了或宕机，重启时会使用redo log恢复数据–>持久性
MySQL中数据以页为单位，查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫做数据页，放入到Buffer Pool中。
查询都会先从Buffer Pool中找，没有命中再去硬盘加载，减少硬盘IO。
更新数据表，如果Buffer Pool里有这个要更新的数据，就直接在Buffer Pool里更新。
然后把在某个数据页上做了什么修改信息和数据记录到 redo log buffer, 然后根据策略刷盘到redo log 文件。
刷盘时机：
InnoDB提供了
innodb_flush_log_at_trx_commit参数
支持三种策略：
- 0: 设置为0的时候，表示每次事务提交时不进行刷盘操作
- 1: 设置为1时，表示每次事务提交时都将进行刷盘操作(默认)
- 2: 设置为2，表示每次事务提交时都只把redo log buffer内容写入page cache
InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存(page cache)，然后调用dsync刷盘。
一个没有提交事务的redo log记录，也可能会刷盘。
数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 byte。
据页刷盘是随机写，一个数据页对应的位置可能在硬盘文件的随机位置，性能很差。如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。
所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，让数据库的并发能力更强。内存的数据页在一定时机也会刷盘，称为页合并。
binlog
redo log是物理日志，属于存储引擎，说明在某一数据页上做了什么修改。
binlog 是逻辑日志，属于MySQL Server层，记录操作语句的原始逻辑（顺序写），所有存储引擎通用， 只要数据改动就会产生binlog。
MySQL数据库的数据备份、主备、主主、主从都需要依靠binlog来同步数据，保证数据一致性。
binlog日志有三种格式
通过binlog_format参数指定
- statement: 记录执行的SQL语句 （存在时间不一致情况）
- row: 记录执行的SQL语句+操作的具体数据（存在占空间，耗IO情况）
- mixed: 上面两种的混合版，会判断这条SQL语句是否会有数据不一致情况，做不同的选择
一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
- 事务执行过程中，先把日志写到binlog cache
- 事务提交的时候，再把binlog cache数据write到page cache中
- 执行fsync将page cache里的数据刷盘到binlog文件
write和fsync的时机，可以由参数sync_binlog控制，默认是0。
为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。
为了安全起见，可以设置为1，表示每次提交事务都会执行fsync。
最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
选择哪种看场景，是对IO的需求更高还是对数据完整性要求更高。
redo log 让InnoDB存储引擎拥有了崩溃恢复能力。
binlog 保证了MySQL集群架构的数据一致性。
都属于持久化的保证。
redo log和binlog保持一致
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。
原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。
MySQL数据重启，恢复是使用redolog，但是主备，主从数据同步恢复是使用binlog，所以要想办法保持一致。
- 开始事务
- 更新数据
- 写入redo log : prepare阶段
- 提交事务
- 写入binlog
- redo log 设置commit 阶段
在binlog写入前的redo log是prepare阶段，写入后是redo log 设置commit阶段。
恢复的时候看看redo log是不是commit阶段就知道数据一不一致了。
如果恢复数据的时候，发现redo log 处于prepare阶段，并且没有对应binlog，就回滚事务。
如果commit阶段发生异常，恢复数据的时候，可以通过事务id找到对应的binlog日志，MySQL认为是完整的。
undo log
undo log --> 保证事务的原子性
发生意外错误不得不回滚或者后悔了手动回滚都需要undo log
暂未归类
数据库操作
选择某数据库
：use 数据库名;
查看当前数据库
：select database();
显示当前时间、用户名、数据库版本
：select now(),user(),version();
创建库
：create database [if not exists] 数据库名;
查看已有库
：show databases [like 'xxx'];
查看当前库信息
：show create database 数据库名;
删除库
：drop database [if exists] 数据库名;
数据库3大范式
第一范式：确保每列保持原子性
第二范式：确保表中的每列都和主键相关
第三范式：确保每列都和主键列直接相关，而不是间接相关
一些杂七杂八的问题
分页limit的原理
mysql中的limit offset,count的原理是先取出offset+count条记录，然后抛弃前面offset条，然后读后面的count条，主要是offset的问题。所以会导致偏移量越大，性能越差。
模拟乐观锁
在多线程\进程的情况下，执行两个语句：
update一个count给它+1，
然后再select出来，希望得到的就是update的时候+1的值，
怎样去保证？
就是保证不会被其他线程修改count值导致和期望的不一样。
除了加锁、封装成事务之外的方法。
可以先select出这个值，然后+1传给需要的地方，然后再update给count+1。
有点像乐观锁的感觉。
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
