mysql小试牛刀类笔记
事务四大特性
原子性：要么成功，要么失败
一致性：事物完成时，必须使所有的数据都保持一致状态。
隔离性：数据库的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或者回滚，对数据库中的数据就是永久的。
并发事务问题
脏读：一个事物读到另外一个事务还没有提交的数据。
不可重复读：一个事物先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事物按照条件查询数据时，没有对应的数据行，但插入数据时，又发现这行数据已经存在，好像出现了”幻影“。
事务隔离级别
主要解决上面并发引发的问题，事务隔离级别越高，数据越安全，但性能越低
| 隔离级别 | 脏读 | 不可重复读 | 脏读 |
|---|---|---|---|
| 读未提交 | √ | √ | √ |
| 读提交 | × | √ | √ |
| 可重复读(默认) | × | × | √ |
| 串行化 | × | × | × |
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以也可被称为表类型。(默认：InnoDB)
1.创建表时，指定存储引擎
CEEATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
.....
字段n 字段n类型[COMMENT 字段n注释]
)ENGINE = INNODB[COMMENT 表注释];
2.查看当前数据库支持的存储引擎
show engines;
InnoDB
兼顾高可靠和高性能的通用存储引擎
特点：
- DML操作遵循ACID模型，支撑
事务 行级锁，提高并发访问性能- 支持
外键FOREIGN KEY约束，保证数据完整和正确性
MyISAM
早期默认的存储引擎
特点：
- 不支撑
事务，不支持外键 - 支持表锁，不支持行锁
- 访问速度快
Memory
表数据存储在内存中，受到硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
特点
- 内存存放
- hash 索引(默认)
索引
索引结构
Hash
索引特点
- Hash 索引只能用于对等比较(=,in),不支持范围查询(between,>,<,…)
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
索引分类
根据索引的存储形式，分为以下两种
SQL性能分析
慢查询日志
记录了所有执行时间超过指定参数(long_query_time)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在配置文件中配置
explain执行计划
id:slect 查询的序列号，表示查询中执行select字句或者操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type:表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询、即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
type:表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、 range、 index、 all。
possible_key:
索引使用法则
- 最左前缀法则
联合索引中，查询中必须包含最左字段，否则索引不⽣效，中间如果有跳过的字段，那么此字段之后的索引不⽣效(部分失效) - 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效 - 字符串运算
索引字段进⾏了字符串函数运算或者不加单引号，索引失效 - 模糊匹配
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效 - or连接条件
两侧字段都有索引，索引才会⽣效，任意⼀⽅没有索引，索引将失效 - 数据分布影响
因为MySQL在查询时，会评估使⽤索引的效率与⾛全表扫描的效率，如果⾛全表扫描更快，则放弃索引，⾛全表扫描。 因为索引是⽤来索引少量数据的，如果通过索引查询返回⼤批量的数据，则还不如⾛全表扫描来的快，此时索引就会失效 - 覆盖索引
覆盖索引是指 查询使⽤了索引，并且需要返回的列，在该索引中已经全部能够找到
尽量使⽤覆盖索引，否则会造成回表查询 - 联合索引
count优化
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使⽤ count()。
锁
分类
按照锁的粒度分
- 全局锁：锁定数据库中的所有表
- 表级锁：每次操作锁住整张表
- 行级锁：每次操作锁住对应的行数据
全局锁
加锁后整个实例处于只读状态，后续DML、DDL语句，已更新操作的事务提交语句都将被阻塞。
典型场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据完整性。
加锁后：只能读，不能写
表级锁
每次操作锁住整张表，发生锁冲突的概率最高，并发度最低
分为三类：表锁、元数据锁(MDL)、意向锁
表锁⼜分为表共享读锁和表独占写锁 只有读读不互斥
加锁：lock tables 表名… read/write
释放锁：unlock tables;
元数据锁：MDL加锁过程是系统⾃动控制，⽆需显式使⽤，在访问⼀张表的时候会⾃动加上。MDL锁主要作⽤是维护表元数据的数据⼀致性，在表上有活动事务的时候，不可以对元数据进⾏写⼊操作。为了避免DML和DDL冲突，保证读写的正确性
意向锁：为避免DML在执行时，加的行锁和表锁得冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁得检查。
- 意向共享锁(IS): 与表锁共享锁兼容，与表锁排他锁互斥；
- 意向排他锁(IX): 与所有表锁互斥，意向锁之间不会互斥
行级锁
- 行锁：锁定单个记录的锁，防止其他事物对此行进行update、delete
- 间隙锁：锁定索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙insert，产生幻读。
- 临建锁：行锁和间隙锁得组合，同时锁住数据，并锁住数据前面得间隙Gap。
行锁
共享锁：
排他锁：
间隙锁/临建锁
默认情况下，InnoDB使用next-key锁进行搜索和索引扫描，防止幻读；
- 索引上得等值查询(唯一索引)，给不存在得记录加锁时，优化为间隙锁；
- 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁；
- 索引上的范围查询(唯一索引)–会访问到不满足条件得第一个值为止。
注意：间隙锁得唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事物采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
InnoDB引擎
架构
说明：业务操作的时候会操作内存结构中的缓冲区，若缓冲区无数据，会将磁盘中数据加载回来存储到缓冲区中，我们增删改查的时候会操作这块缓冲区，缓冲区的数据会以一定的频率(时机)，通过后台线程刷新到磁盘中，然后再磁盘中永久化的保存下来。
架构-内存架构
change buffer:更改缓冲区，执行DML语句时，若这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更当存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
Change Buffer的意义？
不同于聚集索引，二级索引通常是非唯一的，以向对随机的顺序插入二级索引。同样删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO，有了changebuffer后，我们可在缓冲池中进行合并处理，减少磁盘IO。
自适应哈希索引：用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，若观察到hash索引可以提升速度，就建立hash索引，自适应哈希索引无需人工干预，是系统根据情况自动完成的。
架构-磁盘架构
架构-后台线程
作用：将InnoDB存储引擎缓冲池中的数据库在合适的时机刷新到磁盘文件中
- Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的⼀致性，还
包括脏⻚的刷新、合并插⼊缓存、undo⻚的回收 。 - IO Thread
AIO处理IO请求，可提高数据库性能，而IO thread主要负责这些IO请求的回调
- Purge Thread
回收事务已经提交了的undo log，在事务提交之后，undo log可能不⽤了，就⽤它来回收。 - Page Cleaner Thread
协助 Master Thread 刷新脏⻚到磁盘的线程，它可以减轻 Master Thread 的⼯作压⼒，减少阻塞。
事务
事务原理
redo log
重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性
两部分组成：重做日志缓冲(redo log buffer (内存中) )以及重做日志文件(redo log file (磁盘中) )。事务提交后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用，从而保证事务持久性。
直接把buffer pool中数据刷新到磁盘中，存在严重性能问题(涉及到随机磁盘IO，性能低)，所以用redo log.
undo log
回滚日志，用于记录数据被修改前的信息，作用：1.提供回滚 2.MVCC(多版本并发控制)
逻辑日志，当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，记录一条对应相反的update记录，当执行roolback时，可从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁：
Undo log存储：端的方式进行管理和记录
MVCC(多版本并发控制)
当前读：读取的是记录的最新版本，保证其他并发事务不能修改当前记录，会对读取的记录进行加锁，如：select……lock in share mode(共享锁)，select……for update,update,insert,delete(排他锁)都是一种当前读。
快照读：读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。select(不加锁)是快照读；
MVCC：维护一个数据的多个版本，使得读写操作没有冲突，快照读给MVCC提供了一个非阻塞读功能。其具体实现依赖于三个隐式字段、undo log日志、readView。
记录中的隐藏字段：
DB_ROW_ID：主打一个看情况行事，人家定了主键就不需要你了，没定就生成这个隐藏字段。
undo log:
undo log版本链：不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧纪录，链表尾部是最早的旧纪录。
readview
不同的隔离级别，生成readview的时机不同：
READ COMMITTED: 在事务中每一次执行快照读时生成readview;
REPEATABLE READ: 仅在事务中第一次执行快照读时生成readview，后续复用该readview;
RR隔离级别下，仅在事务中第一次执行快照读时生成readview，后续服用该readview。
事务原理总结：
原子性 ---- undo log 持久性 ------ redo log
一致性 ---- undo log + redo log 隔离性 ----- 锁+MVCC
MySQL管理
系统数据库
总结：
图片及文本基本来自B站黑马程序员Mysql视频，若有侵权，联系删除！
1017
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
