MySQL进阶知识点总结
存储引擎
接收上层传下来的指令，对表中的数据进行提取或写入操作
MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。
myisam和innodb的区别
1）InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2）InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3）InnoDB是聚簇索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
4）InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
5）Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了。
6）MyISAM表格可以被压缩后进行查询操作
7）InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
8）InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有。
9）Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。Innodb：frm是表定义文件，ibd是数据文件； Myisam：frm是表定义文件，myd是数据文件，myi是索引文件。
10）MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据
11）MyISAM崩溃后无法安全恢复
索引
索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的目的是减少磁盘I/O次数，加快查询效率。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。
优点
降低数据库的IO成本，提高了查询速度
通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性
加速表和表之间的连接
减少查询中分组和排序的时间
缺点
创建索引和维护索引要耗费时间
索引需要占磁盘空间
降低更新表的速度
B+树
在B树的基础上，为叶子节点增加了链表指针，所有的关键字都在叶子节点中出现，非叶子节点作为叶子节点的索引。
Innodb中的索引方案
聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，叶子节点就是数据文件。
特点：
1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
页内的记录是按照主键的大小顺序排成一个 单向链表 。
各个存放用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。
2. B+树的 叶子节点 存储的是完整的用户记录。
所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
（InnoDB会自动创建聚簇索引）
优点：
数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。
缺点：
插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新
二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据
二级索引（辅助索引、非聚簇索引）
根据其他列大小排序的B+树，叶子节点是主键的值
回表
我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
联合索引
同时以多个列的大小作为排序规则，也就是同时为多个列建立索引
MyISAM中的索引方案
MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。
MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。
主键索引
辅助索引
索引的分类
从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照 作用字段个数 进行划分，分成单列索引和联合索引。
MySQL优化
索引与查询优化
表结构设计
分表（冷热数据分离、垂直拆分、水平拆分）、增加中间表、增加冗余字段、字段类型、范式、存储引擎
系统配置
架构优化
连接池、减少对MySQL的访问、增加cache层、主从复制（读写分离）、数据分片、负载均衡
硬件优化
配置较大的内存、配置高速磁盘系统、合理分布磁盘I/O、配置多处理器
事务
ACID
1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
事务的并发问题
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
4、脏写：如果事务 A 修改了 另一个 未提交 事务 B 修改过 的数 据，那就意味着发生了 脏写
事务隔离级别
事务隔离级别 脏写 脏读 不可重复读 幻读
读未提交（read-uncommitted） 否 是 是 是
读已提交（read-committed） 否 否 是 是
可重复读（repeatable-read） 否 否 否 是
串行化（serializable） 否 否 否 否
主从复制
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。
作用
1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。
原理
Slave 会从 Master 读取 binlog 来进行数据同步
在主从复制过程中，会基于 3 个线程 来操 作，一个主库线程，两个从库线程。
二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进 制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释 放掉。
从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的 二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
binlog 的分类
➢ statement
语句级，binlog 会记录每次一执行写操作的语句。相对 row 模式节省空间，但是可 能产生不一致性，比如 update tt set create_date=now() 如果用 binlog 日志进行恢复，由于执 行时间不同可能产生的数据就不同。
优点： 节省空间
缺点： 有可能造成数据不一致。
➢ row
行级， binlog 会记录每次操作后每行记录的变化。
优点：保持数据的绝对一致性。因为不管 sql 是什么，引用了什么函数，他只记录 执行后的效果。
缺点：占用较大空间。
➢ mixed
statement 的升级版，一定程度上解决了因为一些情况而造成的 statement 模式不一 致问题 。默认还是 statement，在某些情况下譬如：当函数中包含 UUID() 时；包含 AUTO_INCREMENT 字段的表被更新时；执行 INSERT DELAYED 语句时；用 UDF 时； 会按照 ROW 的方式进行处理
优点：节省空间，同时兼顾了一定的一致性。
缺点：还有些极个别情况依旧会造成不一致，另外 statement 和 mixed 对于需要对 binlog 的监控的情况都不方便。
