MySQL
索引
分类
索引类型
- 主键索引
- 普通索引
- 唯一索引
- 联合索引
myISAM和Innodb
区别
-
- 事务，myisam不支持事务，Innodb支持
- 2.锁机制，myisam是表锁，Innodb支持行锁
-
- 索引结构，都是B+树结构，Innodb是聚集索引，数据文件和主键索引绑在一起了，主键索引效率高；Myisam是非聚集索引，索引和数据文件分开的，索引只保存指向数据文件的地址指针
- 4.Innodb支持外键，myisam不支持
-
B树和B+树对比
存储
-
- B+树数据都存储在叶子结点，非叶子结点值存储索引
-
- B+树非叶子结点的key都按照从小到大的顺序排列，叶子结点的记录也按key大小排列
-
- 每个叶子结点都存有相邻叶子结点的指针，叶子节点也是按从小到到的顺序连接的
-
总结
- 1.B+树的优点：单个结点存储的元素更多，使得查询的IO次数变少
- 2.所有的查询都要查到叶子结点，查询性能是稳定的，而B树每个结点都可以找到数据，性能不稳定
- 3.所有叶子结点形成一个有序链表，便于顺序查找，范围查询
B+树和hash索引对比
- 哈希索引查询效率更高，但是仅仅能满足=, IN, <>这样的查询，不支持范围查询。原因：哈希是用于等值的过滤，不能用于范围的过滤
- 哈希索引不能用于排序运算，哈希冲突多的情况下，查询性能可能比B+树低
事务
并发一致性问题
幻读
- 一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行；（按范围查询，第二次数据多了） 幻读的重点在INSERT
原因
- 行锁只能锁住行，不能阻止新插入的记录
解决
- RR隔离下，间隙锁，顾名思义，就是给两个值之间的间隙加锁，两个值会产生3个间隙，加3个间隙锁就能保住无法插入新的记录了
脏读
- 第一个事务里，读取到第二个事务尚未提交的数据，然后第二个事务撤销了这次修改，那么读到的就是脏数据
不可重复读
- 不可重复读重点在update和delete，即在一个事务里面，对同一行数据的多次读取结果不同，原因是第一个事务未结束，其他事务就update或者delete了数据
事务隔离级别
可重复读
概念
- 在一个事务中，多次读取同一条数据的结果是一样的
快照读
- 开始一个事务的时候，会创建一个视图，后面的读都是基于这个视图的，不会去查询最新值
当前读
- select for update或者select for share mode，在更新之前必须查询当前值，能读到所有已经提交的记录最新值
解决问题
- 脏读和不可重复读
读未提交
- 事务中的修改，即使没有提交，对其他事务也是可见的
解决问题
- 都解决不了
读已提交
- 事务中的修改，只有提交后，其他事务才可见
解决问题
- 脏读
串行化
事务只能串行执行
- 可解决幻读问题
事务隔离的原理
可重复读
- MVCC多版本并发控制，同一条记录在系统中可以存多个版本，即MVCC，这个版本号是事务开始的时候向系统申请的，根据时间顺序递增。可重复读在事务开始时生成快照，快照规定了同一事务的更新可见，在快照创建之前提交的版本可见。
锁
行锁
- 锁加在索引记录上面，锁住的是key
间隙锁
- Innodb为了在可重复读时解决幻读，锁定索引记录间隙，确保索引记录的间隙不变。
- 问题：检索条件必须有索引，不然会锁整张表记录
next-key lock
- 行锁和间隙锁的组合
日志
slow log
- 慢查询日志，
binlog
statement
- 基于SQL语句的复制
- 记录真实执行的语句，会加上begin和commit
- 问题：主库和从库执行语句时，修改的数据不是同一条数据，比如：delete......limit 1，因为使用的索引可能不同，导致删数据不同
row
- 基于行的复制
- 日志中记录每一行数据被修改的形式，再从库对相同数据进行修改，
- 不记录SQL，而是记录Event，比如：（1）Table_map event：用于说明接下来要操作的表是哪个库哪张表；（2）Delete_rows event：定义删除的行为；
- 优点：binlog记录每一行
mixed
- 前2种的混合
为什么要有混合型的binlog
- 1.statement格式的binlog可能导致主从数据不一致，而row格式的数据又很占空间，比如：delete10w行数据，binlog要记10w条记录，影响执行速度；2.mixed类型，MySQL会自行判断SQL是否会引起主从不一致，如果有可能，则用row格式，否则statement
集群问题
主从复制
为什么要主从复制？
- 1.架构的扩展，单机无法满足过高的I/O频率；2.读写分离，数据库能支持更大的并发；3.数据的热备份
复制的原理
- 主库将变更写到binlog，从库的IO线程将主库的binlog日志拷贝过来，写入relay log，接着从库的SQL线程从中继日志中读binlog，执行binlog中的内容，保持自己跟主库数据一致
- 3个线程：（1）binlog输出线程，有从库连到主库时，主库会创建一个线程将binlog内容发给从库；（2）从库I/O线程，该线程连接到主库，读取binlog拷贝到本地文件relay log；（3）从库SQL线程，从库读取relay log的更新事件并执行
- 从库是串行执行的
主从延迟问题
原因
- MySQL的主从复制是单线程的，主库对DDL和DML产生binlog，binlog是顺序写，效率高；从库的IO线程不是顺序的，成本高，SQL线程也是单线程的，一个DDL执行完，才会执行下一个，这就导致了延迟
3种同步方式
异步同步
- MySQL默认是异步的，主库执行完事务后立即给客户端返回结果，不关心从库是否处理；
问题
- 如果这时候主库挂了，主库上已提交的事务没有传到从库，导致主从数据不一致
全同步
- 主库执行一个事务，所有从库都执行完这个事务才返回给客户端
- 缺点：主库完成一个事务的时间拉长，性能降低
半同步
- 主库只需要等待一个从库结点收到并且反馈即可，不需等待所有从库返回
XMind - Trial Version
