MySQL必备
2020-04-27 15:25:55 0 举报AI智能生成
为你推荐
查看更多
MySQL知识系统整理(制作不容易，求个赞)
作者其他创作
大纲/内容
基本架构
连接器
缓存（新版本移除）
sql分析器（语法，词法）
sql优化器（执行方案）
执行器和存储引擎
MySQL必备
存储引擎区别
InnoDB和MyIsam区别
5.5版本之前 MyIsam
优点
性能极佳，很适合读密集的场景。
缺点
不支持事务和行级锁，数据库崩溃后无法安全恢复。不支持MVCC。
5.5 版本之后 InnoDB
支持并发事务，支持且默认开启行级锁
有独特日志系统，崩溃后可以安全恢复，因为有独有的redolog。(binlog是什么引擎都有的)
如何选择
InnoDB事务
事务的四大特性
原子性 undolog来保证
一致性
隔离性 隔离级别和gap锁来保证
持久性 redolog来保证
并发事务带来的问题
脏写 Dirty Write
一个事务修改了另一个事务已修改但未提交的记录，那就意味着发生了脏写。
脏读（单个记录的多次读取）Dirty Read
一个事务读取了另一个事务已修改但未提交的记录，那就意味着发生了脏读。
不可重复读（单个记录多次读取）Non-Repeatable Read
如果一个事务只能读取另一个已经提交的事务修改后的数据，并且另一个事务每对该数据进行一次修改并提交后，该事务都能查询到最新的值，那就意味着发生了不可重复读。
幻读
如果事务A先根据某些条件查询出一些记录，之后事务B又向表中插入了符合这些条件的记录，事务A再次按照该条件查询时，能把事务B插入的记录也读出来，那就意味着发生了幻读。
注意：如果事务B删除了符合条件的记录，事务A再次按照该条件查询，发现记录少了。这种情况并不算是幻读！幻读强调的是一个事务按照相同条件多次读取记录时，后续读到了之前没有读到的记录。对于之前已经读到的记录，之后又读不到这种情况，其实是算作不可重复读的现象。幻读只是强调了读取到了之前没有获取到的记录。
解决方法
GAP间隙锁
只在RR隔离级别生效。并且GAP不所记录本身，只锁未出现的行
丢失更新
一个事务覆盖了另一个事务对同一条数据的修改
乐观锁
for update悲观锁
SQL标准中对不同事务隔离级别的中可能发生问题的规定
在READ UNCOMMITTED隔离级别下，脏读、不可重复读、幻读都可能发生。
在READ COMMITTED隔离级别下，不可重复读、幻读可能发生，脏读不可以发生。
在REPEATABLE READ隔离级别下，幻读不能发生，脏读和不可重复读不可以发生。
在SERIALIZABLE隔离级别下，上述问题都不可以发生。
MySQL对SQL标准中事务隔离级别的实现
READ UNCOMMITTED
读未提交。啥都解决不了。
READ COMMITTED
读已提交。可以解决脏读，但是不能解决不可重复读和幻读。
REPATEABLE READ（默认）
重复读。可以解决脏读，不可重复读，MySQL对RR级别的实现中可以禁止幻读发生。
SERIALIZABLE
串行，没有任何问题。并发性极低。
事务实现原理
解决并发事务问题的两种方式
快照读：读操作用MVCC（无锁）来控制，读写分离
就是在查询时生成了一个ReadView，通过ReadView来获取符合比较条件的版本。ReadView的存在，本身就决定了事务无法读取到其他事务修改未提交的记录，RC级别每次Select都会生成新的ReadView，也就在RC级别下阻止了脏读的发生。查询语句只能读到在生成ReadView之前事务已经提交的版本，在事务正在修改和事务之后提交的记录都对它不可见。而写操作都是针对最新的记录，所以读历史记录和写最新记录并不干扰。并且可以通过生成ReadView时机的控制来解决不可重复读的问题，即区分了出了RC和RR级别。换句话说，采用MVCC，实现了读写分离，读写操作不冲突。
一致性读，或者称为快照读（无锁）（Consistent Reads）
事务通过MVCC进行的读取称为一致性读，也叫作一致性无锁读或者快照读。所有普通select语句在RC或RR级别下都属于一致性读。一致性读不会对表中的任何记录加锁，其他事务可以放心修改。
写操作用锁来控制，读写都要加锁
脏读的发生是事务A读取到事务B已修改但未提交的记录，当事务A或者B读取或者修改时加锁了，那另一方都是无法读到的，也就避免了脏读。不可重复读是事务A多次读取到了事务B对记录已经提交的修改，如果事务A读取时对该记录加锁了，那事务B是无法修改的，也就避免了不可重复读。幻读则是通过锁定未出现的行，另一个事务则无法插入新记录，来解决幻读问题。
锁定读（Locking Reads）
共享锁
事务要读取一条记录时，需要获取S锁。如果事务A想读取记录，可以获得S锁，此时事务B也想读取，也可以获取该S锁进行读取。即S锁和S锁是兼容的。
独占锁
事务要修改一条记录时，需要索取X锁。如果事务A获取S锁进行读取，事务B想获取其X锁进行修改，那么会被S锁阻塞。直到事务A操作结束将S锁释放，事务B才能获取X锁进行修改。即S锁和X锁，X锁和S锁，X锁和X锁之间都是阻塞的。
使用S锁和X锁来锁定读的语句
select * from table lock in share mode
select * from table for update
写操作
增
一般情况下，插入记录时并不会加锁。MySQL中有一种隐式锁来保证插入数据事务结束后，其他事务才能访问。当然，在一些特殊情况下insert操作也是会去获取锁的。
删、改
这个过程可以认定是执行获取X锁的锁定读，锁定待删除记录的位置，再执行操作。
比较
很明显，采用MVCC来实现的读写分离来解决三大问题，性能要高于直接读写加锁。但是在某些场景下，是需要通过加锁的方式来解决问题的，比如银行转账问题，对账户一条记录访问时，必须锁定后修改。
InnoDB索引
索引为何能提高查询速度
索引的优缺点
InnoDB支持的索引类型
B树
自适应Hash，无法干预
聚簇索引和非聚簇索引
聚簇索引（一种数据结构）
以主键创建的索引
叶子节点存储的是主键和对应的数据行
是通往真实数据行的唯一途径。
创建带有主键的表默认使用的就是聚簇索引结构。如果没有主键，引擎会选取带有Unique键(唯一索引)作为主键。如果还没有唯一索引，引擎会自己偷偷给表添加一个row_id隐藏列作为主键来创建聚簇索引。
非聚簇索引（二级索引）
以普通字段创建的索引
叶子节点存储的是索引列和主键
查询时比聚簇索引慢。
创建非聚簇索引的注意事项
联合索引而非多个单列索引，也称为多列索引
索引列不能是表达式的一部分
索引失效的情况举例
索引一般加在哪些字段
多列索引顺序的选择
索引未生效，该如何排查
Explain
InnoDB中的锁
表锁
行锁的类型
Record Lock （LOCK_REC_NOT_GAP）
平时对一条记录上的就是这个普通记录锁。记录锁也是分S锁和X锁的，满足S和X锁互斥，S S锁兼容的原则。
Gap Lock （LOCK_GAP）
RR级别多次索引范围查询时，可是实现重复读，也同时避免了幻读。虽然防止了幻读，但是不能防止插入幻影记录。Gap Lock的出现仅仅就是为了解决插入幻影行的问题。
Next-Key Lock （LOCK_ORDINARY）
是Record Lock和Gap Lock的合体。即能保护当前记录，又能保护住插入间隙。
Insert Intention Lock 插入意向锁
其他事务在向GAP间隙锁中插入记录时，会被阻塞并且获取该间隙中的插入意向锁，当GAP锁的事务结束时，才会去插入记录。插入意向锁并不会组织其他事务继续获取该记录上的其他任何类型的锁（就是个鸡肋）。
隐式锁（不显式的在内存中创建锁结构）
隐式锁是对于Insert语句来说的。事务在执行Insert时一般是不加锁的，一种特殊情况是插入到间隙时遇到了Gap锁，此时的insert会加插入意向锁。而由此引发了一个问题，如果insert不加锁，那插入后事务还未提交时，其他事务如果获取到了这条记录的S锁或者X锁，岂不是会发生脏读和脏写问题吗？这就需要知道Insert过程中的隐式锁了。我们都知道一条记录中包换隐藏列trx_id，记录了最近修改该条记录的事务id。因此当其他事务在尝试获取该记录的S锁或者X锁时，会先检查记录的trx_id是否为当前活跃的事务id，如果是，就帮助当前事务创建一个X锁以阻止其他事务读写，并且为自己也创建一个锁来进入等待状态。
InnoDB log
binlog（逻辑日志）
内容：记录了对数据库数据和表的变更。除了select都记录。
功能：复制和恢复，主从复制就是用这个来做
模式
row
记录每一行的修改情况。日志量很大。
statement
记录执行的修改语句。日志量小。
mixed
前两者的混合。
redolog（物理日志）
内容：记录了xx页做了xxx修改。
功能：数据库崩溃恢复，即crash-safe的能力。
binlog和redolog的三点区别
1、引擎层：redolog是InnoDB引擎特有的，而binlog是MySQL的Server层实现的，所有引擎都有。
2、功能上：redolog是物理日志，记录数据页修改。而binlog是逻辑日志，记录语句的原始逻辑，比如“给ID=2的这行的c字段加1”
3、写入方式上：redolog是循环写的，空间用完了就擦除前面的重新写。而binlog是追加写的，写到一定大小后就新建一个继续写，并不会覆盖之前的日志（这个归档的特性，使得binlog又叫做归档日志）。
binlog和redolog写入细节
重点：通过二阶段提交来保证binlog和redolog的一致性。如果不一致，就乱套了。
undolog（重做日志）
内容：记录着记录修改前的数据，并且通过记录中的roll pointer隐藏列指向备份数据的undo log，事务失败时，好通过undo日志回滚。
功能：回滚和MVCC中的版本链
MVCC原理
MVCC只是在执行普通的Select查询时生效。
概念
版本链
tx_id 事务id：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。
roll pointer 回滚指针：指向修改前版本的指针。
每条记录都必会有tx_id和roll pointer两个隐藏字段。不同事务对同一条记录的修改会形成一个版本链。
ReadView
m_ids 系统当前活跃的事务id集合。
min_trx_id 当前活跃事务id中的最小值。
max_trx_id 系统即将给新事务分配的事务id值。
creator_id 创建当前ReadView的事务id。
使用ReadView来实现RC和RR
RC
RC级别下，每次查询开始时都会生成一个独立的ReadView。
RR
RR级别下，只会在当前事务第一次查询时生成一个ReadView，之后的查询不会生成ReadView了。
主从复制初步理解
1、主库写binlog
2、从库有个I/o线程向主库要数据
3、主库有个dump线程将binlog中数据发送给从库
4、从库收到binlog数据，放到中继日志relaylog
5、从库启动另外一个线程，利用relaylog开始恢复数据。
综合问题
一条SQL执行很慢，会是什么原因？
情况1：大多数情况正常，偶尔很慢
redolog刷脏页中。因为写满了，要去同步磁盘。
遇到了表锁或者行锁，在等待中。
情况2：一直都很慢
索引未生效，这里参考索引未生效的情况
存储引擎选错索引。引擎认为走全表比走索引快，导致全表扫描了。
如何优化查询？
1、每个表上必须要有主键。因为会让表数据未聚簇索引排列，依据主键查询时效率高。
2、当多个查询条件时，可以考虑最左匹配+联合索引。选取的字段依据where，orderby，groupby后字段，索引列顺序依据索引选择性来判断
3、更频繁的查询可以考虑覆盖索引。explain的extra中可以看到使用using index。5.6之后有个索引下推的功能，在存储引擎中索引查询后继续过滤数据行，using index x condition
4、索引未生效的问题解决也可以回答在这里
0 条评论
回复 删除
下一页
