一 、mysql执行流程
1.查詢緩存(8.0抛弃了这个功能),需要开启
2.如果在缓存中查询到, 返回结果,否则到下一步 解析器,解析sql语法是否正确(类似java的编译器)
3.查询优化器：如果建立多个索引，优化器会找效率最高的索引来执行
4.执行引擎
二、存储引擎
InnoDB、MyISAM 两大存储引擎,还有其他的不一一列举
1.InnoDB:支持事务,删除更新就选择InnoDB
2.MyISAM:不支持数据,查询效率高,如果是静态表的话,可以用MyIASM
| 对比项 | MyISAM | InnoDB |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁,操作一条记录锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其他行有影响,适合高并发的操作 |
| 缓存 | 只缓存索引,不缓存真实数据 | 即缓存索引也缓存真实数据,对内存要求搞,而内存的大小对性能有决定的影响 |
| 关注点 | 性能:节省资源,消耗少,简单业务 | 事务:并发写,事务,更大资源 |
三、索引的数据结构
索引的本质：排好序的快速查找的数据结构
优点：
1.降低数据库的IO成本
2.创建唯一索引，可以保证每行数据的唯一性
3.可以加快表和表之间的连接
4.减少查询中分组和排序的时间，降低了CPUD的消耗
缺点：
1.创建索引要耗费时间
2.占用磁盘空间
3.降低更新表的速度
B+树
1. 根目录记录的是非叶子节点的最小和页数（一个地址），如上图的根目录页33里面的1就是非 叶子节点页30的最小值1
2.非页子节存放的是叶子节点的最小值和数据
3.叶子节点存储真正的数据
流程： 1）这样如果要查一条数据是的8的，先在根目录里面找（1次io），
2）大于1，小于320，证明在1那个地址就是页30，再从页30中去找（2次io），
3）大于5，小于12，从页28中去找（3次io）,最后找到8的那条记录
如果一个根目录和非叶子节点可能存储1000条数据（因为只存储地址，没有数据，就多点），叶子节点存储100条数据（这里面是真实的sql数据）；
B+树三层；一共可以存放的数据：1000*1000*100=100000000（数据相当大了）
四、聚簇索引和非聚簇索引
1.聚簇索引：索引即数据，InnoDB存储引擎的主键和数据是存储在一起的
优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中
- 聚簇索引对于主键是的排序查找和范围查找速度非常快
- 节省了大量的IO操作
缺点：
- 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的Id
- 更新主键的代价很高，因为将会导致被更新的行移动，因此，对于InnoDB表，我们一般定义为主键不可更新
- 二级索引访问需要两次索引查找，第一从找到主键值，第二次根据主键找到行数据
限制：
- 只有InnoDB支持聚簇索引。而MyISAM并不支持聚簇索引。
- MySQL表中只有一个聚簇索引，一般情况就是该表的主键
- 如果没有定义主键，InnoDB会选择非空的唯一索引代替，如果没有这样索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
- InnoDB的表主键列尽量选择有序的顺序id
2.二级索引（辅助索引、非聚簇索引）
- 一般的我们除了聚簇索引，所建立的其他索引都叫二级索引，例如：一张表有ID ,name , age我们把name和age建立一个索引,就叫二级索引
小结：
- 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置，非聚簇索引不会影响数据表的物理存储顺序
- 一个表只能有一个聚簇索引，因为只能有一中排序存储的方式。可以有多个非聚簇索引
- 聚簇索引查询效率高，对于插入，删除，更新效率比非聚簇的索引底
3.联合索引（非聚簇索引）
由两个或以上的组成的索引叫联合索引，联合索引的叶子节点不存储数据，只存储数据的地址，因为在一个表中由很多的联合索引，如果把数据都存储在叶子节点上，会需要很多内存空间，所以，如果我们查询的值不止只有联合索引的这些字段，找到最后比如最左边的20（主键）这个数据，还要根据这个主键20去聚簇索引里面找到对应的数据。
五、InnoDB的B+树索引的注意事项
1.B+树的构建
- 一开只有一个数据也的时候，B+树只有一个数据页（A数据页）
- 随着业务数据的增加，一个数据页放不下了，就复制一份A数据出来,这时A数据页就成了根结点
- 就这样，如果把二级数据页页添加满了，二级数据页就变成我们所说的非叶子节点，就有了我们的叶子节点
- 整个过程是自上而下的
2.二级索引记录项的唯一（非叶子节点）
为了防止索引的值都是相同的，插入的过程中，不知道往哪个页中插入数据，所以为了让插入的记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项一共有三部分
- 索引列的值
- 主键值
- 页号
主键是唯一的，所以插入数据的数据，就能够区分插入到哪一页了
六、MyISAM中的索引方案
- MyISAM是将索引和数据分开的存储，也没有聚簇索引。
- MyISAM的索引文件仅仅保存数据记录的地址，*.MYD，存储数据；*.MYI，存储索引。
- MyISAM查询效率快，虽然需要回表查询，但是是拿地址找的，所以快
七、MySQL数据结构选择的合理性
1.Hash索引
hash结构效率高，那么为什么索引结构设计成树形
原因：
- Hash索引仅能满足（=）（<>）和IN查询，如果进行范围查询，哈希性是索引，时间复杂度会退化为O(n)
- 没有顺序 order by就要重新排序
- 联合索引是几个字段一起的，如果几个字段一起hash的话，会出现问题 比如 (12 a 23)和 (1 2a 23)hash出来的值就一样了
- 如果索引的列重复值或的话，效率会降低，比如性别，年龄
八、索引的设计原则
1.索引分类
- 从功能逻辑上说：普通索引、唯一索引、主键索引、全文索引
- 物理实现方式：聚簇索引和非聚簇索引
- 作用字段个数：单列索引和联合索引
2.索引设计原则
- 一个字段或多个字段有唯一性的限制，也必须建成唯一索引。
- 频繁操作的where查询条件、更新、删除的字段创建索引。
- 经常order by 和 group by的列创建索引，如果是多个字段建议建联合索引（注意最左原则）。
- DISTINCT字段需要创建索引。
- 多表连接的字段创建索引，最好不要超过三张表关联，关联的字段类型要一致的，如果不一致，导致类型转换就是用了函数，索引就会失效。
- 大字段不适合创建索引，占用页的空间大。
- 区分度高的列适合创建索引，例如：id之类的， 反例：性别。公式：select count(distinct a)/count(*) from t1 计算区分度，越接近1 的越好，超过33%就算是比较高的索引了。
- 最频繁的列放在最左侧
- 多个字段创建索引要优于单个索引
- 注意索引数量，建立不要超过6个，索引多了只用磁盘空间、操作数据需要维护索引。优化器会评估哪个索引才是最好的执行计划，太多索引也会增加优化执行的时间
3.哪些情况不适合创建索引
- 在where中不用的字段不创建索引
- 数据量小的表不要创建索引
- 大量重复的列不要创建索引（区分度不高）
- 避免对频繁更新的表创建过多的字段
- 不建议用无序的值创建索引
- 删除不用或者用得少的索引
九、性能分析工具
1.查看系统性能参数
show status like '对应的参数'
例如查看MySQL服务器连接的次数
show status like 'Connentions'
参数如下：
2.定位慢查询的SQL:慢查询日志
默认是关闭的，需要打开
#开启慢查询日志
set global slow_query_log=on;
#关闭慢查询日志
set global slow_query_log=off;
#查询慢查询是否开启
show variables like '%slow_query_log';
#查看慢查询日志存放位置
show variables like '%slow_query_log%';
#查询多长时间算是慢查询，默认是10s
show variables like '%long_query_time%';
#把慢查询时间改为1s
set global long_query_time=1 ;
#把慢查询时间改为1s
set long_query_time=1 ;
#查看有多少条慢查询sql
show status like 'slow_queries'查询慢查询日志：
mysqldumpslow -s t -t 5 /var/lib/mysql/932c98c63253-slow.log
十、EXPLAIN分析语句
| 列名 | 描述 |
| id | 在一个大的查询语句中每个select关键字都对应一个唯一的id |
| select_type | select关键字对应的那个查询的类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对表单的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| extra | 一些额外的信息 |
创建两个表s1 ,s2
#1.创建表
CREATE TABLE s1(
id int auto_increment,
key1 VARCHAR(100),
key2 int,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part (key_part1,key_part2,key_part3)
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2(
id int auto_increment,
key1 VARCHAR(100),
key2 int,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part (key_part1,key_part2,key_part3)
)ENGINE=INNODB CHARSET=utf8;
#2.创建函数， 假如报错，需要开启如下命令。允许创建函数设置
set global log_bin_trust_function_creators=1;
#3.创建函数
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该数会返回个学符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str =CONCAT(retUrn_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#4.创建存储过程，s1和s2一样，改名字即可
DELIMITER //
CREATE PROCEDURE insert_s2 (
IN min_num INT ( 10 ),
IN max_num INT ( 10 )) BEGIN
DECLARE
i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2
VALUES
(
( min_num + i ),
rand_string1 ( 6 ),
( min_num + 30 * i + 5 ),
rand_string1 ( 6 ),
rand_string1 ( 1 ),
rand_string1 ( 5 ),
rand_string1 ( 10 ),
rand_string1 ( 10 ));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#5.调用存储过程 10000条数据
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);
1.id
小结:
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
- 关注点: id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好
2.type
完整的访问方法如下:
system，const, eq_ref, ref , fulltext, ref_or_null,index_merge,unique_subquery,index_subquery,range , index ，All。
- system:只有一条记录，并且改表使用MyISAM引擎才会有
- const:当我们根据主键或者唯一索引列与常数进行等值匹配时，对单表的访问方法就const
- eq_ref:在连接查询时，如果被驱动表是通过主键或唯一索引列等值匹配的方式进行访问的，例如：
#因为id是主键也是唯一的，所以s2是eq_ref的 EXPLAIN SELECT * from s1 INNER JOIN s2 on s1.id=s2.id -
ref:当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref；例如
EXPLAIN SELECT * from s1 WHERE key3 = "111" - ref_or_null:当对普通二级索引进行等值匹配查询，该索引列的值也可以是“NULL"值时，那么对该表的访问方法就可能是ref_or_null
- index_merge:两个单列的普通索引用’OR‘关键字进行查询，如果用and就是ref
SELECT * from s1 WHERE key3 = "111" or key1='41' - range:如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法
EXPLAIN SELECT * from s1 WHERE key1 in ('a' , 'b' , 'c') EXPLAIN SELECT * from s1 WHERE id >10 and id <20000 - index:当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
#因为有一个（key_part1，key_part2 ，key_part3 ）的联合索引， EXPLAIN SELECT key_part2 from s1 WHERE key_part3 ='a' #这个key1不在联合索引列，所以要回表查询 EXPLAIN SELECT key1, key_part2 from s1 WHERE key_part3 ='a' - All:全表扫描
3.key_len
主要针对于联合索引，值越大越好
计算key_len的值
- varchar(10) 变长宁段且允许NULI= 10 * ( character set: utf8=3,gbk=2,latin=1)+1(NULI)+2(变长字段)
- varchar(10)变长宁段且不允许NULI = 10 * ( character set: utf8=3,gbk=2,latinl=1)+2(变长宁段)
- char(10) 固定宁段且允许NULL= 10 * ( character set: utf8=3,qbk=2,latin1=1)+1(NULL)
- char(10)周定宁段且不允许NULL = 10 *( character set: ut8=3,qbk=2,latin1=1)
4.rows
预估的需要读取的记录的条目数，值越小越好
十一、索引优化与查询优化
CREATE TABLE class(
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL,
PRIMARY KEY (id)
)
ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE student(
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopgrstuvwxyzABCDEFJHIJKLMNOPORSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT'';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN return_str;
END //
DELIMITER
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE I INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1));
RETURN i;
END //
DELIMITER
DELIMITER //
CREATE PROCEDURE insert_stu(START INT,max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO student (stuno, name ,age ,classId ) VALUES((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)) ;
UNTIL i = max_num
END REPEAT;
COMMIT;
END//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE insert_class(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i=i+1;
INSERT INTO class ( classname,address,monitor ) VALUES(rand_string(8),rand_string(10),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END//
DELIMITER;
CALL insert_class(10000)
CALL insert_stu(100000,500000)1.索引失效案例
- 全值匹配：如果是多个条件查询的，考虑建索引就把多个条件的建成联合索引
- 最左匹配：比如 A B C字段建立联合索引Z，如果在where最左的条件不是在这个联合索引里的，比如D，就不会使用这个Z索引了，如果只有A C字段，会降低查询效率，因为只会命中A这个字段，虽然会用到索引，如果是顺序互换B C A依然可以用Z索引
- 主键顺序插入：不用容易导致位置移动和页分裂
- where后面不使用函数，计算，类型转换
- 范围条件右边的列索引失效，如：< 、>、 <= 、>=和between等
- 不等于 (!=或<>)索引失效
- is null可以使用索引，is not null不能使用索引、
- like通配符%开头的索引失效
- or前后存在非索引的列，索引失效
- 数据库和表统一使用utf8mb4,不同字符集进行比较前需要进行转换会造成索引失效
练习：假设：index(a,b,c)
| where语句 | 索引是否被使用 |
| where a=3 | 使用到a |
| where a = 3 and b = 5 | 使用到a,b |
| where a =3 and b = 5 and c = 4 | 使用到a,b,c |
| where b = 3 或者 where b=3 and c =4 或者 where c =4 | 没有用到索引，最左原则，没有用到a |
| where a =3 and c = 5 | 用到 a,c没有用到，b断了 |
| where a=3 and b>4 and c=5 | 用到a和b,在范围之后，不能用索引 |
| where a <> 3 | 不能使用索引 |
| where abs(a) = 3 | 不能使用索引 |
| where a = 3 and b like 'kk%' and c = 4 | 使用 a,b,c索引 |
| where a = 3 and b like '%dd' and c=4 | 用到a |
一般性建议:
- 对于单列索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好.
- 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的素引。
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
2.关联查询优化
- 连接的字段类型必须是一致的
- 关联查询条件可以考虑建索引
- 对于内连接来说，如果两边条件字段都有索引或者都没有索引，查询优化器会选择小表作为驱动表，大表作为被驱动表（这里的大小表是取决于结果集，结果集小的就是小表，大的就是大表）；如果只有一个字段有索引，有索引这个就是被驱动表
- 小表驱动大表本质就是减少外层循环的数量
3.子查询优化
子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因:
- 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
- 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引，所以查询性能会受到一定的影响。
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在MySQL中，可以使用连接 (JOIN)查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好
4.排序优化
使用order by字段加上索引
两种排序方式：
- Index:索引保证数据的有序性，不需要再进行排序，效率更高
- FileSort:排序再内存中，占用CPU较多，如果带排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低
小结：
INDEX a_b_c(a,b,c)
#order by 能使用索引最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,C DESC
#如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const ORDER BY b,c
WHERE a = const AND b > const ORDER BY b,c
#不能使用索引进行排序
ORDER BY a ASC,b DESC,C DESC /* 排序不一致 */
WHERE g = const ORDER BY b,c /*丢失a索引*/
WHERE a = const ORDER BY c /*丢失b索引*/
WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/5.覆盖索引
- 什么是覆盖索引：一个索引包含了满足查询结果的数据就叫覆盖索引
好处:
- 避免了对主键的二次查询，减少了IO操作
- 可以把随机IO变成顺序IO加快查询效率
弊端：
索引字段需要维护
6.索引下推
在联合索引中，abc索引,如果a可以用索引，b索引失效了，会优先在a索引查出来的数据，在进行一步过滤，减少回表查询的次数，减少IO次数，这就是索引下推
7.其他优化策略
1.count(*)和count(具体字段)
- 两个本质上没有区别
- 如果是MyISAM存储引擎，统计行数只需要o(1)，因为每张MyISAM表都维护一个字段来统计表的行数
- 在InnoDB存储引擎中，如果采用count(具体字段)，就尽量采用二级索引，因为主键采用的聚簇索引包含的信息太多了，明显会大于二级索引（非聚簇索引）。对于count(*)和count(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果都多个二级索引，会采用key_len最小的二级索引进行扫描，没有二级索引时，才会使用主键索引进行统计
2.关于select *
- MySQL解析过程中，会通过查询数据字典将 * 按顺序转成所有列名，这会大大的耗费资源和时间
- 无法使用覆盖索引
3.LIMIT 1 对优化的影响
- 如果确定只有一条记录，那么就加上 limit 1，当找到一条结果时就不往下找了，避免了全表扫描
- 如果字段是建立唯一索引的，就不用加 limit 1了
8.淘宝数据库主键如何设计
自增ID的问题：
- 可靠性不高：存在自增ID回溯问题
- 安全性不高：自增的容易暴露数据量
- 性能差：自增ID的性能比较差，需要在数据库服务器端生成
- 局部唯一性：在分布式系统中，如果进行数联合查询或者汇总，ID可能会是一样
推荐的主键
雪花算法
十二、数据库的设计规范
- 第一范式：每列都不可再拆分
- 第二方式：一张表只表达一个意思，确保每列都和主键完全依赖
- 第三方式：确保每列都和主键列直接相关，而不是间接相关
范式只是提出设计的标准，有时候为了提高性能，通过冗余数据来提高查询效率，以空间换时间，因此，在开发中要合理的运用范式。
建立数据库关系：
(1) 一个 实体 通常转换成一个 数据表 ;
(2) 一个 多对多的关系，通常也转换成一个 数据表 ;
(3) 一个 1 对 1，或者 1 对多 的关系，往往通过表的 外键 来表达，而不是设计一个新的数据表
十三、数据库的优化
-
拆分表：冷热数据分离：一个表的数据，把经常用的数据放在另一个表，不常用放在另一个表
-
增加中间表：两个表常用的字段放在一个表中
-
增加冗余字段
-
优化数据类型
-
优化插入记录的速度
-
使用非空约束：使用默认值‘’和0
-
限定查询范围：比如一个月，两个月的数据
-
读写分离
-
垂直拆分：分库分表
-
水平拆分
十四、事务
1.基本概念
# 案例: AA用户给BB用户转账100
update account set money = morwhere name ='AA'
#服务器宕机
update account set money = money+ 100 where name = 'BB'如果服务宕机了，对应的AA应该退回这100块钱
2.事务ACID特性
- 原子性 (atomicity)：
原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形，A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。 - 一致性(consistency):
而不是语法上的，跟具体的业务有关根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是 语义上的
举例1:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据 是不一致的，为什么呢? 因为你定义了一个状态，余额这列必须 >= 0。
举例2:A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并 没有增加。你也知道此时数据是不一致的，为什么呢? 因为你定义了一个状态，要求A+B的总 余额必须不变。
- 隔离型 (isolation):
事务的隔离性是指一个事务的执行 不能被其他事务干扰，即一个事务内部的操作及使用的数据对 并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 - 持久性 (durability):
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
3.事务的隔离级别
- 脏写（Dirty Write）：
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为李四然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为张三。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时Session A中的事务就没有效果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么变化也没有。这里大家对事务的隔离级比较了解的话，会发现默认隔离级别下，上面SessionA中的更新语句会处于等待状态，这里只是跟大家说明一下会出现这样现象。 - 脏读（Dirty Read）：
对于两个事务 Session A、Session B，Session A读取了已经被 Session B 更新 还没有被提交 的字段。之后若Session B 回滚 ，Session A 读取 的内容就是临时且无效的。 - 不可重复读 (Non-Repeatable Read)(针对修改) ：
对于两个事务Session A、 Session B，Session A 读取了一个字段，然后 Session B更新了该字段。之后 Session A再次读取 同一个字段，值就不同 了。那就意味着发生了不可重复读。 - 4.幻读 ( Phantom)(针对新增):
对于两个事务Session A、Session B, Session A从一个表中读取了一个字段,然后 Session B 在该表中插入了一些新的行。之后,如果 Session A 再次读取 同一个表,就会多出几行。那就意味着发生了幻读。删除的不算幻读。
4种隔离级别：
READ UNCOMMITTED: 读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
READ COMMITTED: 读已提交，它满足了隔离的简单定义: 一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别(但不是MySOL默认的)。可以避免脏读，但不可重复读、幻读问题仍然存在。
REPEATABLE READ:可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySOL的默认隔离级别
SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复对该表执行插入、读和幻读。
| 隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
| READ UNCOMMITTED | YES | YES | YES | NO |
| READ COMMITTED | NO | YES | YES | NO |
| REPEATABLE READ | NO | NO | YES | NO |
| SERIALIZABLE | NO | NO | NO | YESS |
十五、MySQL事务日志
1.为什么要REDO日志：
- 修改量与刷新磁盘工作量严重不成比例
有时候我们仅仅修改了某个页面中的一个字节，但是我们知道在InnoDB中是以页为单位来进行磁盘10的，也就是说我们在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，我们又知道一个页面默认是16KB大小，只修改一个字节就要刷新16KB的数据到磁盘上显然是太小题大做了
- 随机10刷新较慢
一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，假如该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的Buffer Pol中的页面刷新到磁盘 时，需要进行很多的 随机I0，随机1O比顺序IO要慢，尤其对于传统的机械硬盘来说。
InnoDB擎的事务采用了WAL技术 (Write-Ahead Logging )，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用。
1.1.REDO日志的好处、特点
好处：
- redo日志降低了刷盘频率
- redo日志占用空间非常小
特点：
- redo日志是顺序写入磁盘的
- 事务执行的过程中，redo log 不断记录
redo log 跟bin log 的区别，redo log 是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次霜入到bin log文件中。
1.2.redo工作流程
第1步:先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
第2步: 生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第3步:当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第4步:定期将内存中修改的数据刷新到磁盘中
1.4 redo默认的刷盘流程：
小结: innodb _flush_log at trx_commit=1
为1时，只要事务提交成功， redo log 记录就一定在硬盘里，不会有任何数据丢失。如果事务执行期间 MySOL 挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。可以保证ACID的D，数据绝对不会丢失，但是 效率最差 的。
建议使用默认值，虽然操作系统宕机的概率理论小于数据库宕机的概率，但是一般既然使用了事务，那么数据的安全相对来说更重要些。
1.5redo日志文件还有两个重要的属性
- write pos 当前记录的位置，一边写一边后移
- checkpoint 是当前擦除的位置，也是往后推移
每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint后移更新。write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。
如果 write pos 追上 checkpoint，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。
2.Undo日志
redo log是事务持久性的保证，undo og是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log 。
2.1 理解Undo日志
比如我们insert、delete、update
- 你插入一条记录 时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的 记录删掉就好了。 (对于每个INSERT，InnoDB存储引擎会完成一个DELETE)
- 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录 插入到表中就好了。 (对于每个DELETE，InnoDB存储引擎会执行一个INSERT)
- 你修改了一条记录 ，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录 更新为旧值 就好了。(对于每个UPDATE，InnoDB存储引擎会执行一个相反UPDATE，将修改前的行放回去)
MySOL把这些为了回滚而记录的这些内容称之为 撤销日志 或者 回滚日志即 (undo lg)。注意，由于查询操作(SELECT)并不会修改任何用户记录，所以在查询操作执行时，并 不需要记录 相应的undo日志。
此外，undo log 会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。
2.2 Undo日志的作用
- 回滚数据
用户对undo日志可能有误解: undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此undo是 逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。 - MVCC
undo的另一个作用是MVCC，即在innoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
2.3 undo log 的生命周期
假设有两个值 ，分别为 A=1 和 B=2 ，然后A修改为 3，B修改为4
1.start trinsaction;
2.记录 A=1 到undo log;
3.update A = 3;
4.记录 A=3 到redo log:
5.记录 B=2 到undo log;
6.update B= 4;
7.记录B = 4 到redo log;8.将redo log刷新到磁盘
9.commit
十六、锁
事务的隔离性由锁来实现。
1.MySQL访问相同记录
- 读-读：不加锁
- 写-写：加锁需要排队执行
- 读-写：一个事务查询，一个事务修改，MySQL解决，会发生脏读，不可重复读，幻读，MySQL的默认级别已经解决了不可重复度问题
2.并发问题的解决方案
怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢? 其实有两种可选的解决方案
- 方案一: 读操作利用多版本并发控制 ( MVCC ，下章讲解) ，写操作进行 加锁。
所谓的MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本(历史版本由 undo日志构建)。查询语句只能读 到在生成ReadView之前 已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而 写操作 肯定针对的是 最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写 操作并不冲突。
普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录.
- 在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象;
- 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有,第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读的问题。
3.从操作类型划分：读锁、写锁
- 共享锁/读锁：不阻塞
- 排他锁/写锁：写会阻断其他的读写
3.1.锁定读
可以利用select * from a for update 来限制其他事务不能读写
3.2.写操作
平常所用到的写操作 无非是 DELETE 、 UPDATE 、 INSERT 这三种:
- DELETE :
对一条记录做DELETE操作的过程其实是先在 B+ 树中定位到这条记录的位置，然后获取这条记录的X锁，再执行 delete mark 操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取 X锁的锁定读。 - UPDATE:
在对一条记录做UPDATE操作时分为三种情况。
情况1: 未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。则先在 B+ 树中定位到这条记录的位置，然后再获取一下记录的 X锁，最后在原记录的位置进行修改操作。我们也可以把这个定位待修改记录在 B+ 树中位置的过程看成是一个获取 锁 的锁定读情况2: 未修改该记录的键值，并且至少有一个被更新的列修改前后发生变化。则先在 B+ 树中定位到这条记录的位置，然后获取一下记录的 X锁，将该记录彻底删除掉(就是把记录彻底移入垃圾链表)，最后再插入一条新记录。这个定位待修改记录在 B+ 树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由 INSERT 操作提供的 隐式锁 进行保护
情况3:修改了该记录的键值，则相当于在原记上做 DELETE 操作之后再来一次INSERT 操作，加锁操作0就需要按照 DELETE 和 INSERT 的规则进行了 - INSERT :
般情况下，新插入一条记录的操作并不加锁，通过一种称之为 隐式锁 的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。
4.从数据操作的粒度划分：表级锁、页级锁、行锁
锁越小，消耗的资源就越大
1.表锁：开销小，很好的避免死锁，并发率大打折扣
- 意向锁（IX）：一个事务A给A表中的某条记录加上排他锁，就自动给我们的表级别加上意向锁（IX），事务B给A表就加不了表锁了，如果事务A加的是共享锁，就可以
2.行锁：在存储引擎层实现
优点：锁定力度小，发生所冲突概率低，可以实现的并发度高
缺点：锁的开销比较大，加锁会比较慢，容易出现死锁情况
- 记录锁：一个事务修改id为1的记录，就把id为1的记录锁住（排他锁），不能让其他事务对id为1的记录修改或查询
- 间隙锁：防止插入其他事务插入新的事务出现幻影记录，REPEATABLE READ隔离级别下解决幻读，如果3和8之间还有间隙，给8加个间隙锁，3到8之间就不能插入新的数据
- 临键锁：本质就是记录锁+间隙锁，它既能保护该条记录，又能阻止被的事务将新记录插入被保护记录前边的间隙
- 插入意向锁：如果一个事务A持有间隙锁3到8范围的，另外一个事务B再插入一个7，这时候就会插入失败，事务B进入等待状态，需要等待第一个事务A提交,查看事务B是否超时，这时候才能插入一条新的记录
3.页锁：开销基于表锁和行锁之间，会出现死锁，并发一般
5.悲观锁、乐观锁
- 悲观锁：事务A占有锁了，其他事务只能等待事务A释放锁，select ..........for update ,查询条件一定要是索引，否则全表扫描，把整个表锁住，适用写多的场景
- 乐观锁：利用版本号控制，适合读多的场景
十七、多版本并发控制
1.什么是MVCC
是通过数据行的多个版本管理来实现数据库的并发控制
| 时间顺序 | 事务10 | 事务20 |
| 1 | begin； | |
| 2 | begin； | |
| 3 | update a set name='李四' where id =1 | |
| 4 | update a set name='王五' where id =1 | |
| 5 | commit； | |
| 6 | update a set name='钱七' where id =1 | |
| 7 | update a set name='宋八' where id =1 | |
| 8 | commit； |
每次对记录进行改动，都会记录一条undo日志，每条undo志也都有一个roll_pointer 属性 (INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本)，可以将这些undo日志 都连起来，串成一个链表:
2.MVCC原理之ReadView
MVCC依赖于：隐藏字段、Undo Log、Read View。
2.1隐藏字段:
- trx_id:每次一个事务对某条聚族索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id 隐藏列。
- roll_pointer:每次对某条聚族索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
3.MVCC操作流程
- 首先获取事务自己的版本号，也就是事务 ID;
- 获取 ReadView;
- 查询得到的数据，然后与 ReadView 中的事务版本号进行比较
- 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照
- 最后返回符合规则的数据
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对T该事务完全不可见，查询结果就不包含该记录。
InnoDB 中，MVCC 是通过 Undo Log+ Read View 进行数据读取，Undo Log保存了历史快照，而 Read View 规则帮我们判断当前版本的数据是否可见。
读已提交（read committed）: 一个事务每一次select 查询都会重新获取一次 read view.如下：
| 事务 | 说明 |
| begin; | |
| select * from s where s >2; | 获取一次 read view |
| ............. | |
| select * from s where s >2; | 获取一次 read view |
| commit; |
可重复读 (repeatable read)：一个事务只在第一次select 的时候会获取一次 read view ，而后面所有的select 都会复用这个 read view
| 事务 | 说明 |
| begin; | |
| select * from s where s >2; | 获取一次 read view |
| ............. | |
| select * from s where s >2; | |
| commit; |
3.1举例
read committed隔离级别下:
1.有一个学生表的数据：id=1，name=张三，trx_id =8
2.事务10更新它
begin;开启事务
update set name = '李四' from student where id =1;
update set name = '王五' from student where id =1;
3.事务20去获取 id=1的数据
select * from student where id =1 ;#此时read view 里面的trx_ids 是 10（活跃的事务）, 此时名字是张三
#事务10在这里提交了事务
#事务20再去获取id=1的数据
select * from student where id =1；此时名字是王五
repeatable read隔离级别下:可以解决不可重复读和幻读
1.有一个学生表的数据：id=1，name=张三，trx_id =8
2.事务10更新它
begin;开启事务
update set name = '李四' from student where id =1;
update set name = '王五' from student where id =1;
3.事务20去获取 id=1的数据
select * from student where id =1 ; #此时read view 里面的trx_ids 是 10（活跃的事务）,此时名字是张三
#事务10在这里提交了事务
#事务20再去获取id=1的数据
select * from student where id =1；此时名字还是张三
MVCC解决的问题：
- 读写之间阻塞的问题
- 降低了死锁的概率
- 解决快照读的问题
扫一扫
7558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
