MySQL基础复习1
MySQL数据库专题
- MySQL特点
- MySQL事务特性（ACID）
A：原子性：事务是最小的执行单位，不允许分割，动作要不全部成功，要不全部不起作用
C：一致性：执行事务前后，数据保持一致性，多个事务对同一个数据读取的结果是相同的
I：隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库也是独立
D：持久性：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响
参考：https://www.cnblogs.com/csniper/p/5525477.html
脏读： A事务能够读取其他事务的update/delete/insert操作
原因：RUC隔离级别下：事务的任何操作都不会加锁
不可重复读：A事务能够读取到其他事务已经commit的update/delete操作(即同一条select语句读到不同的结果)
幻读：A事务读取到其他事务已经commit的Insert操作（即同一条语句select语句读到"多"的结果）
不可重复读和幻读的区别：
- 不可重复读重点在update/delete；幻读重点在insert
SQL标准定义的4个隔离级别：
RUC未提交读：允许读取未提交事务的数据变更->会导致脏读/幻读/不可重复读
RC提交读：允许读取提交事务的数据变更->解决了脏读问题；会导致幻读/不可重读
RR可重复读：不允许读取更新操作的提交事务->解决了脏读/不可重复读；会导致幻读
SZ可串行化：所有操作串行化 读用读锁、写用写锁、读锁和写锁互斥->解决了脏读/不可重复读/幻读
RR级别如何解决不可重复读问题？
SQL第一次读取到的数据后，将对这些数据加行级锁；其他事务无法对其进行delete/update操作
- 但是可以进行insert操作，这就是导致幻读的问题（但是在INNODB中有next-key锁解决幻读问题）
Next-key锁 = gap锁 + 行级锁 来解决【当前读】中的幻读问题
gap锁： 锁定范围，但不包含记录本身
Next-key锁： 锁定范围，并包含记录本身（INNODB在RR级别下默认方式）
在RR级别中，当前事务A进行update后加锁，事务B无法Insert新数据，这样事务A在update前后读的数据保持一致，避免了幻读
这个锁其实就是GAP锁（间隙锁）
<------
Next-key算法下，对于索引进行扫描，范围内的索引都会被锁住
A事务进行update操作后不仅仅对该条记录加上行级锁，还在该记录的“上下区间”加上gap锁；这样事务B就无法进行Insert
a) 上下空间：因为数据是B+树进行存储的，所以当update操作有走索引就存在上下空间；如果没有走索引就会给整张表上gap锁
b) 如果在上下区间外进行insert操作，是允许的；记住： Next-key锁解决的【当前读】中的幻读问题
-----> 以上内容和《高性能MySQL》还是有出入
《高性能MySQL》中：
select对于单个值的索引查询, INNODB会选择最小的算法模型，即：只采用行级别锁，意味着可以进行insert操作
官网定义： 不可重复读被定义为幻象
- MySQL数据管理(导出导入数据等)
https://blog.csdn.net/u012436346/article/details/86570902
- MySQL建立索引的语法
MySQL具体场景问题：
（1）查询姓“王”的同学
（2）查询不重复的字段
（3）表数据量大了，要根据姓名进行查询，如何做？
（4）这种方式索引为什么有效？
（5）给性别加索引可以吗？
- SQL的in和exsit区别
- MySQL索引类型（重点：聚簇索引）： 主键索引/唯一索引/普通索引/全文索引/联合索引
聚簇索引： 按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据(所以叶子节点也称之为数据页)
非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值
- MySQL索引的数据结构（重点：B+树）
- Mysql有哪些版本的存储引擎(重点:InnoDB + 应用场景/锁/)
具体问题：
（1）讲讲MySQL有哪些存储引擎？
（2）MyISAM和InnoDB的存储引擎用的索引数据结构分别是什么？
（3）画一下b+树的数据结构？
（4）现在有index(a,b,c),联合索引使用(a,c)会使用到索引吗？使用(b,c)会使用到索引吗，结合b+树的图来说说
(1) 存储引擎： INNODB和MySIAM区别
a) 是否支持行级锁： INNODB支持行级锁和表级锁；MySIAM支持表级锁
b) 是否支持事务：INNODB支持事务（回滚及崩溃恢复能力）；MySIAM不支持，但是查询速度效率高
c) 是否支持外键：InnoDB支持；MySIAM不支持
d) 是否支持MVCC：仅INNODB支持
参考：https://www.cnblogs.com/jiawen010/p/11805241.html
(2) 都是采用B+树； 不采用二叉树的原因：高度（磁盘 IO次数）；不采用B树的原因是：非叶子节点是不存放数据(内存中可以存放更多的索引)
(3) .... 看相关参考博客
关于INNODB和MySIAM聚簇索引的B+区别：索引页和数据页（INNODB叶子节点存放索引和数据；MySIAM叶子节点存放数据地址）
- MySQL主从复制和并行复制原理(如何优化主从复制)
主从复制过程： Master更新数据->写binlog->dump线程->IO线程->relog更新->sql线程->Slave执行更新
主库生成一个线程： dump线程
SQL线程： 用来给从库的IO线程传送binlog
从库生成两个线程：IO线程和SQL线程
IO线程：将得到的binlog日志写到relog日志中
SQL线程： 读取relog日志，并解析成具体操作，来实现主从操作的一致性
- MySQL主主复制及实践：Haproxy+MySQL双主实验
- Mha原理相关（Mha应用场景、选举过程、主从原理、读写分离）
- MySQL优化相关
博客：https://blog.csdn.net/liuyanqiangpk/article/details/79827239
总结：
1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1
6. InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，
也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分
7. 一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
- MySQL特点
- MySQL事务特性（ACID）
A：原子性：事务是最小的执行单位，不允许分割，动作要不全部成功，要不全部不起作用
C：一致性：执行事务前后，数据保持一致性，多个事务对同一个数据读取的结果是相同的
I：隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库也是独立
D：持久性：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响
参考：https://www.cnblogs.com/csniper/p/5525477.html
脏读： A事务能够读取其他事务的update/delete/insert操作
原因：RUC隔离级别下：事务的任何操作都不会加锁
不可重复读：A事务能够读取到其他事务已经commit的update/delete操作(即同一条select语句读到不同的结果)
幻读：A事务读取到其他事务已经commit的Insert操作（即同一条语句select语句读到"多"的结果）
不可重复读和幻读的区别：
- 不可重复读重点在update/delete；幻读重点在insert
SQL标准定义的4个隔离级别：
RUC未提交读：允许读取未提交事务的数据变更->会导致脏读/幻读/不可重复读
RC提交读：允许读取提交事务的数据变更->解决了脏读问题；会导致幻读/不可重读
RR可重复读：不允许读取更新操作的提交事务->解决了脏读/不可重复读；会导致幻读
SZ可串行化：所有操作串行化 读用读锁、写用写锁、读锁和写锁互斥->解决了脏读/不可重复读/幻读
RR级别如何解决不可重复读问题？
SQL第一次读取到的数据后，将对这些数据加行级锁；其他事务无法对其进行delete/update操作
- 但是可以进行insert操作，这就是导致幻读的问题（但是在INNODB中有next-key锁解决幻读问题）
Next-key锁 = gap锁 + 行级锁 来解决【当前读】中的幻读问题
gap锁： 锁定范围，但不包含记录本身
Next-key锁： 锁定范围，并包含记录本身（INNODB在RR级别下默认方式）
在RR级别中，当前事务A进行update后加锁，事务B无法Insert新数据，这样事务A在update前后读的数据保持一致，避免了幻读
这个锁其实就是GAP锁（间隙锁）
<------
Next-key算法下，对于索引进行扫描，范围内的索引都会被锁住
A事务进行update操作后不仅仅对该条记录加上行级锁，还在该记录的“上下区间”加上gap锁；这样事务B就无法进行Insert
a) 上下空间：因为数据是B+树进行存储的，所以当update操作有走索引就存在上下空间；如果没有走索引就会给整张表上gap锁
b) 如果在上下区间外进行insert操作，是允许的；记住： Next-key锁解决的【当前读】中的幻读问题
-----> 以上内容和《高性能MySQL》还是有出入
《高性能MySQL》中：
select对于单个值的索引查询, INNODB会选择最小的算法模型，即：只采用行级别锁，意味着可以进行insert操作
官网定义： 不可重复读被定义为幻象
- MySQL数据管理(导出导入数据等)
https://blog.csdn.net/u012436346/article/details/86570902
- MySQL建立索引的语法
MySQL具体场景问题：
（1）查询姓“王”的同学
（2）查询不重复的字段
（3）表数据量大了，要根据姓名进行查询，如何做？
（4）这种方式索引为什么有效？
（5）给性别加索引可以吗？
- SQL的in和exsit区别
- MySQL索引类型（重点：聚簇索引）： 主键索引/唯一索引/普通索引/全文索引/联合索引
聚簇索引： 按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据(所以叶子节点也称之为数据页)
非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值
- MySQL索引的数据结构（重点：B+树）
- Mysql有哪些版本的存储引擎(重点:InnoDB + 应用场景/锁/)
具体问题：
（1）讲讲MySQL有哪些存储引擎？
（2）MyISAM和InnoDB的存储引擎用的索引数据结构分别是什么？
（3）画一下b+树的数据结构？
（4）现在有index(a,b,c),联合索引使用(a,c)会使用到索引吗？使用(b,c)会使用到索引吗，结合b+树的图来说说
(1) 存储引擎： INNODB和MySIAM区别
a) 是否支持行级锁： INNODB支持行级锁和表级锁；MySIAM支持表级锁
b) 是否支持事务：INNODB支持事务（回滚及崩溃恢复能力）；MySIAM不支持，但是查询速度效率高
c) 是否支持外键：InnoDB支持；MySIAM不支持
d) 是否支持MVCC：仅INNODB支持
参考：https://www.cnblogs.com/jiawen010/p/11805241.html
(2) 都是采用B+树； 不采用二叉树的原因：高度（磁盘 IO次数）；不采用B树的原因是：非叶子节点是不存放数据(内存中可以存放更多的索引)
(3) .... 看相关参考博客
关于INNODB和MySIAM聚簇索引的B+区别：索引页和数据页（INNODB叶子节点存放索引和数据；MySIAM叶子节点存放数据地址）
- MySQL主从复制和并行复制原理(如何优化主从复制)
主从复制过程： Master更新数据->写binlog->dump线程->IO线程->relog更新->sql线程->Slave执行更新
主库生成一个线程： dump线程
SQL线程： 用来给从库的IO线程传送binlog
从库生成两个线程：IO线程和SQL线程
IO线程：将得到的binlog日志写到relog日志中
SQL线程： 读取relog日志，并解析成具体操作，来实现主从操作的一致性
- MySQL主主复制及实践：Haproxy+MySQL双主实验
- Mha原理相关（Mha应用场景、选举过程、主从原理、读写分离）
- MySQL优化相关
博客：https://blog.csdn.net/liuyanqiangpk/article/details/79827239
总结：
1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1
6. InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，
也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分
7. 一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
掌阅科技HR面4人在聊
查看23道真题和解析
