MySQL知识点小结

前言

    大多互联网公司都在用MySQL而不是Oracle，所以就想将自己掌握的一点小知识做一下记录。理解过于浅显，简称入门级八股文。

1.什么是MySQL？

    MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。

2.存储引擎

    常见的存储引擎包括innodb和MyISAM。

  2.1引擎的特点

    a)InnoDB存储引擎
    InnoDB是事务型数据库的首选引擎，MySQL5.5以后默认使用InnoDB存储引擎。
    InnoDB特点： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。
    如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

    b)MyISAM存储引擎
    MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。
    MyISAM特点： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用

  2.2innodb 和 MyISAM区别

    1)innodb支持事务，MyISAM不支持事务；
    2)innodb支持外键，MyISAM不支持外键；
    3)innodb支持行锁和表锁，MyISAM仅支持表锁；
    4)InnoDB不保存表的具体行数，MyISAM用一个变量保存了整个表的行数；
    5)Innodb存储文件有frm(表定义文件)、ibd(数据文件)，而Myisam是frm(表定义文件)、MYD(数据文件)、MYI(索引文件)；
    6)InnoDB是聚簇索引，使用B+Tree作为索引结构，数据和（主键）索引都存在叶子节点上，必须要有主键，通过主键索引效率很高。
    MyISAM是非聚集索引但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据(回表)。

3.MySQL事务

  3.1什么是事务？

    MySQL事务是为了保证一组数据库操作，要么全部成功，要么全部失败 (事务是在引擎层实现的)。

  3.2如何开启事务？

    Mysql默认开启事务；
    开启事务:
    set autocommit = 0; (0关闭自动提交 1自动提交)
    begin; 或者 start transaction; 手动开启事务，需commit;手动提交后才不可rollback回滚。

  3.3事务的特征

    A：原子性 = 事务是最小的工作单位，不可再分；
    C：一致性 = 同一事务中的SQL，要么全成功，要么全失败；
    I：隔离性 = 事务之间互相隔离；
    D：持久性 = 事务一旦提交，影响是持久的；

  3.4事务的隔离级别

    事务的隔离级别越高，性能越差。

    未提交读 read uncommitted :当前事物可以读取其他事物未提交的数据，容易导致脏读;
    读已提交 read committed :当前事物可以读取其他事物已提交的数据，导致不可重复读现象；
    可重复读(默认) repeaableread :同一事务内的select语句，多次读取的结果是一致的，容易导致幻读；
    串行化读 serializable :多个事务操作同一个表时，各事务按顺序执行，其他事物堵塞，会锁表，消耗资源，影响效率。

  3.5并发问题

    脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

    不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
    解决办法：MVVC(多版本并发控制)，每一行数据都有多个版本，每个版本的记录除了有数据本身外，还有一个表示版本的事务ID，根据时间先后顺序递增。

    幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉出现了幻觉，这就叫幻读。
    解决办法：间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。有索引的情况，如果不是索引列，那么数据库会为整个表加上间隙锁。

4.日志

  redo log(重做日志)：在事务开始之后就会产生redo log，假如发生故障，导致尚有脏页未写入磁盘，那么在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。当对应事务的脏页写入到磁盘之后，redo log占用的空间就可以重用（被覆盖）。

  undo log(回滚日志)：undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。
    可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读，保证数据的原子性。
    当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

  bin log(二进制日志)：事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。
  在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。也可以用于数据库的基于时间点的还原。

  关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

5.索引

  5.1什么是索引？

    在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

  5.2索引分类

    联合索引：在一个表的多列上建立索引，SQL条件遵循最左前缀原则走索引。

    聚簇索引：聚簇索引的数据结构是B+树，叶子结点保存了索引和数据。每个表只能有一个聚集索引。
    非聚簇索引：非聚簇索引的数据结构是B+树，将数据与索引分开存储，叶子节点指向了数据对应的位置。

    当通过非聚簇索引查询数据时，需要两个步骤：第一步在非聚簇索引B+树中检索，到达其叶子节点获取对应的主键。
    第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

    回表：回表就是先通过索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

  5.3什么是最左前缀原则？

    最左前缀原则是针对联合索引来说的，举个栗子：

    在A B C 三列建立联合索引，相当于建立了 A、AB、AC、ABC 索引，当条件为 A=?、A=? AND B=?、A=? AND C=?、A=? AND B=? AND C=?、C=? AND B=? AND A=? (SQL优化器)时，都会走索引，但是当条件为 B=? AND C=? 时，是不走该索引的。

6.大表优化

  当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

  6.1. 限定数据的范围
    禁止不带任何限制数据范围条件的查询语句。
  6.2. 读/写分离
    经典的数据库拆分方案，主库负责写，从库负责读；
  6.3. 垂直分区
    根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
    垂直拆分的优点： 可以使得列数据变小，在查询时减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
    垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
  6.4. 水平分区
    水平拆分是指保持数据表结构不变，将数据表行的拆分，就是把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
    水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。
    水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。