MySQL数据库知识整理

MySQL数据库知识整理

MySQL事务详解

事务四大特性ACID

• 原子性（Atomicity）：一个事务是一个不可分割的最小单元，该事务的所有操作要么全部提交，要么失败回滚，不能只执行其中的一部分。
  • 原子性是通过undo log（回溯日志）实现的。undo log是InnoDB存储引擎特有的，具体的实现方式是：将所有对数据的修改（增、删、改）都写入日志undo log，undo log是逻辑日志，其记录了和事务操作相反的SQL。例如事务执行insert，undo log就执行delete。它可以追加写的方式记录日志，不会覆盖之前的日志。如果一个事务中的一部分操作已经成功，但另一部分操作由于断电、系统崩溃等错误而无法执行，则通过回溯日志将已经执行成功的操作撤销，从而达到全部操作失败的目的。除此之外undo log还用来实现数据库的多版本并发控制（MVCC）。
• 一致性（Consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态。事务没有提交，事务中所做的修改也不会保存到数据库中。
  • 用原子性和持久性共同保证了一致性。
• 隔离性（Insolation）：一个事务所做的修改在最终提交之前，对其他事务是不可见的。
  • 隔离性是通过锁机制和MVCC实现的：当一个事务需要对数据库的某行数据进行修改时，需要先给数据加行锁，加了锁的数据其他事务是不运行操作的，只能等待当前事务提交或者回滚将锁释放。
• 持久性（Durability）：一旦事务提交，其所做的修改就会永久保存到数据库中。
  • **持久性是通过redo log来实现的。**redo log也是InnoDB持有的。具体的实现方式是：**当发生数据修改（增、删、改）的时候，InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新就算完成了。同时InnoDB引擎会在合适的时机将记录刷到磁盘中。**redo log是物理日志，记录的是某个数据页做了什么修改，而不是SQL语句的形式。它有固定大小，是循环写的方式记录日志，空间用完后会覆盖之前的日志。

脏读、不可重读和幻读

• 脏读：事务1更新了一份数据但没有提交，事务2在此时读取了事务1更新的数据，由于某个原因，事务1回滚了，那么事务2就读取的是脏数据。
• 不可重读：事务1读取了一个数据，在事务1还没有结束的时候，事务2也访问了该数据进行修改并提交。由于事务2修改的，事务1读取到的数据与之前读取的数据不一样了。
• 幻读：事务1在读取某个范围内的记录时，事务2又在该范围内插入了新的记录，此时事务1再次读取的时候，就产生了幻行。

事务的四种隔离级别

• 未提交读（READ UNCOMMITTED）

  • 事务中的修改即使没有提交，对其他事务也是可见的。即每次总是读取到最新的数据行，而不是符合当前事务版本的数据行。
  • 该级别会导致脏读、不可重复读和幻读。

• 提交读（READ COMMITTED）

  • 一事务开始直到提交前，所做的任何修改对其他事务都是不可见的。实现方式是：读操作加共享锁，但语句执行完后释放。
  • 该级别解决了脏读，但无法解决幻读和不可重读。

• 可重复读（REPEATABLE READ）

  • 该级别保证了同一事务中多次读取同样的记录结果是一致的，是InnoDB默认的事务隔离级别。实现方式是：读操作加共享锁，事务提交之前不释放共享锁，事务执行完后才释放。
  • 该级别解决了脏读和不可重复读，但是没有解决幻读。
  • InnoDB采用了间隙锁解决了当前读的幻读问题，MVCC解决了快照读下的幻读。

• 可串行化（SERIALIZABLE）

  • **最高的隔离级别，强制事务串行执行。**给每一行数据都加锁，这么做虽然安全，但是会导致大量的超时和锁争用问题。一般在需要严格保证数据一致性且接受无并发的情况下使用。
  • 该级别阻止了脏读、不可重读和幻读。

  	脏读	不可重读	幻读
  未提交读	会	会	会
  提交读	不会	会	会
  可重读度	不会	不会	会
  可串行化	不会	不会	不会

MySQL引擎详解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q6y6yGxQ-1679497838441)(C:\Users\12576\Desktop\笔记\常用mysql引擎.jpg)]

MyISAM

• MySQL5.1之前默认存储引擎，不支持事务、行级锁和外键，崩溃后无法安全恢复。
• MyISAM对整张表进行加锁，而不是针对行。读的时候对需要读到的所有表加共享锁，写时则对表加排它锁。在表有数据查询的同时，也可以往表中插入新的记录（并发）。
• MyISAM表可以手动或者自动执行检查和修复操作。表修复操作非常慢，且可能会导致一些数据丢失。
• 支持全文索引，即使是BLOB和TEXT的长字段，也可以基于前500个字符创建索引。
• 如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成后，不会立刻将修改的索引数据写入磁盘，而是写入内存中的键缓冲区。只有清理缓冲区或者关闭表的时候才会将对应的索引数据写入到磁盘。

InnoDB

• MySQL5.5后默认的事务型引擎，用来处理大量短期的事务。提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
• 支持自增长序列，外键约束，行锁。
• 采用MVCC来支持高并发，实现了四个标准的隔离级别。默认隔离级别为可重复读。通过间隙锁的策略来防止幻读，间隙锁使InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻行的插入。
• 基于聚簇索引建立的，对主键的查询有很高的性能。但其二级索引（非主键索引）必须包含主键列，所以如果其主键列很大，其他所有的索引都会很大。一般要求主键自增，这样可以保证每次插入B+树索引都是从右边扩展了，避免了B+树的频繁合并和分裂。
• 解决死锁问题：将持有最少行级排它锁的事务进行回滚。
• 全文索引，5.7之前不支持，5.7后支持。
• 采用两段锁，在事务执行的过程中，随时都可以执行锁定。锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁同时释放。根据隔离级别在需要的时候自动加锁。
• 四大特性
  • 插入缓冲
    • 用于插入性能。其中change buffer是insert buffer的加强，insert buffer只对insert有效，change buffer 对插入、删除和更新都有效。
    • 只对非聚簇、非唯一索引的插入有效：对于每一次的插入不是直接写入索引页，而是先判断插入的非聚簇索引页是否在缓冲中，如果在直接插入，否则先放到insert buffer中，再按照一定频率进行合并操作，再写回磁盘。这样通常能将多个插入合并到一个操作中，目的是为了减少随机IO带来的性能损耗。
    • 其大小默认是缓冲池的1/4，最大可设置为1/2。
  • 二次写缓存
    • 为了解决页断裂的问题，即当数据库异常宕机时，数据库页只有部分写入磁盘，导致页面出现不一致的情况。
    • **二次写缓存位于系统表空间的存储区域，用来缓存InnoDB的数据页从insert buffer中读取但没有写入到数据文件之前的数据。**所以即使在写磁盘的过程中崩溃，InnoDB也可以在二次写中找到数据页的备份来用来恢复。
    • 数据页写入duble write缓存的动作需要的IO操作要小于写入到数据文件的消耗。
    • 大小默认为2M。
  • 自适应哈希索引
    • InnoDB存储引擎会监控对表上的二级索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。
    • 自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快，且不需要将整个表都建立哈希索引。
    • 会占用InnoDB的buffer pool，且只适合搜索等值的查询，不能用于范围查询。
  • 预读：提高IO性能，用于异步的将磁盘的页读取到buffer pool中，预测这些页会马上被读取到。其中分两种实现，线性预读和随机预读。
    • 线性预读：预测在buffer pool中被访问到的数据临近的页也会很快被访问到，能够通过调整连续访问的页的数量来控制。
    • 随机预读：通过buffer pool中存中的页来预测哪些页可能很快会被访问，而不考虑这些页的读取顺序。
• 采用B+树

  • B+树的非叶子结点不存储数据，仅存储键值。

    • 数据库中页（也就是树中的结点）的大小是固定的，InnoDB默认为16KB。如果不存储数据，那么就可以存储更多的键值，这样使得树更矮更胖。这样我们查找数据进行磁盘的IO次数又会再次减少，这样数据查询的效率也会更快。如果一个结点存1000个键值，3层B+树能存1000x1000x1000=10亿个数据！一般根结点放在内存里。

  • B+树索引的所有数据均存储在叶子结点，而且数据是按照顺序排列的。

    • 这么做使得范围查找、排序查找、分组查找以及去重查找变得十分容易。而B树的数据分散在各个结点，要想进行范围查找必须得中序遍历。InnoDB中B+树的各个页之间是通过双向链表连接的，叶子结点中的数据是通过单向链表连接的。

  • 总结B+树优点：

    • 单次请求涉及的磁盘IO次数少（非叶子结点不包含表数据，树的高度小）
    • 查询效率稳定（任何关键字的查询必须从根结点到叶子结点，查询路径长度相同）
    • 遍历效率高（从符合条件的某个叶子结点开始遍历即可，而B树无法避免查询非叶结点）

Memory

• 适合快速访问数据，且这些数据不会被修改，重启后丢失也没有关系。该引擎比MyISAM还要快，因为所有的数据都保存在内存中，不需要进行磁盘IO。其表结构重启以后还会保留，但数据会丢失。
• 支持Hash索引；该表为表级锁，因此并发写入性能较低；不支持BLOB和TEXT，每行的长度都是固定的，所以即使指定VARCHAR，实际存储的时候也会转换为CHAR。

Merge

• 多个MyISAM表合并的虚拟表，现已被废弃

Archive

• 只支持INSERT和SELECT操作，MySQL5.1之前不支持索引。
• 缓存所有的列并利用zlib对插入的行进行压缩，比MySIAM的磁盘IO更少。但每次SELECT都需要执行全表扫描。所以Archive表适合日志和数据采集类应用。
• 支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个SELECT开始直到返回表中存在的所有行数之前，Archive引擎会阻止其他的SELECT执行，以实现一致性读。且实现了批量插入在完成之前对读的操作也不可见。

对比MyISAM和InnoDB

	主键	存储空间	事务	数据缓存	CRUD操作	索引	保存行数	外键
MyISAM	可以没有主键	可被压缩，存储空间较小。支持三种不同的存储格式：默认的静态表、动态表和压缩表	不支持	不支持	MyISAM强调的是性能，所以SELECT很快（无行级锁）。增删的时候需要锁定整个表，效率较低	非聚簇索引。索引的叶子结点存储的是行数据地址，需要再寻址一次才能得到数据	用一个变量保存了整个表的行数，执行count只需要读出该变量即可，速度很快（不能加where，只能是读全部行）	不支持
InnoDB	必须有主键，如果没有指定主键则会选择非空唯一索引作为主键，如果还是没有，那么InnoDB会自定义一个主键，6个字节的长整型	需要更多的内存和存储，在主内存中建立其专用的缓冲池用于高速缓冲数据和索引	支持，对于每一条SQL都会默认封装成事务而自动提交，这样会影响速度，所以最好把多条SQL语句组成一个事务	支持	如果执行大量的INSERT或者UPDATE，InnoDB效率较高。	聚簇索引，主键索引存储行数据，因此通过主键查询十分高效	不保存，执行count时需要全表扫描（InnoDB事务的特性在同一时刻表中的行数对于不同的事务而言是不一样的）	支持

通过show engines \G 命令，里面的default engine来查看数据库支持的引擎。

多版本并发控制MVCC

• MVCC是行级锁的一个变种，但其在很多情况下避免了加锁的操作，因此开销更低，大都实现了非阻塞的读操作，写操作也只是锁定必要的行。
• 原理：MVCC通过保存数据在某个时间点的快照来实现，不管需要执行多少时间，每个事务所看到是数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
• 实现方式：通过在每行记录后面保存两个隐藏的列来实现，一个保存了行的创建版本号，一个保存了行的过期版本号（删除版本号）。每开始一个新的事务，这个系统的版本号就会递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
• 在可重复读的级别下，MVCC的具体操作：
  • SELECT：InnoDB根据以下两个条件查询每行记录
    • 只查找行创建版本早于当前事务版本的数据行（行的系统版本号小于或等于当前事务的系统版本号），这样确保事务读取的行，要么是事务开始前就已经存在的，要么是事务自身插入或者修改过的。
    • 行的删除版本号要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
  • INSERT：InnoDB为新插入的每一行保存当前系统版本号作为创建版本号。
  • DELETE：InnoDB为删除的每一行保存当前系统版本号作为删除版本号。
  • UPDATE：InnoDB为插入的每一行保存当前系统版本号作为创建版本号和删除版本号。
• 保存这两个额外的系统版本号，使大多数操作都可以不用加锁。这样使得读数据操作简单，性能更好，并且保证只会读取到符合标准的行。但每行记录都需要额外的存储空间，增加了额外的开销和维护工作。
• MVCC只在提交读和可重复读两个级别下工作。

数据库的范式

• 第一范式：每个列都不可以再拆分。第一范式是关系模式的最基本要求，不满足第一范式的数据库不是关系数据库。

• 第二范式：第一范式的基础上，每个非主键列完全依赖于主键，而不是依赖于主键的一部分。

• 第三范式：在第二范式的基础上，非主键列只能依赖主键，不得依赖其他非主键。

• BCNF范式：在满足三范式基础上，还应该满足：

  • 所有的非主属性对每一个码都是完全函数依赖
  • 所有主键对每一个不包含它的码也是完全函数依赖
  • 没有任何属性完全函数依赖与非码的任何一组属性

  码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码（即主键）。

MySQL索引详解

• 索引是存储引擎用于快速找到记录的一种数据结构，可以理解为包含对数据表里所有记录的引用指针。

• 优点：大大加快了数据库的检索速度，在查询的过程中，使用优化隐藏器来提高系统的性能。

• 缺点：

  • 创建索引和维护索引要耗费时间。
  • 索引占用额外的物理空间（每创建一个就得建造一颗B+树）。
  • 当对表中的数据进行增加、删除和修改的时候，索引也需要动态的维护，这样会降低增删改的执行效率。

• 使用场景：

  • 根据主键索引进行查询。
  • order by对查询结果进行排序（索引本身有序）。
  • join语句匹配关系设计字段建立索引能够提高效率。

• 索引为什么快？因为DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

• 索引类型：主键索引、唯一索引、普通索引、全文索引。（联合索引、多索引）

• InnoDB的索引数据结构默认是B+树索引，还有hash索引。

• 索引的基本原理就是把无序的数据变成有序的查询：

  • 把创建索引所有的列的内容进行排序
  • 对排序结构生成倒排表
  • 在倒排表内容上拼上数据的地址链
  • 在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到具体数据。

• 索引设计的原则：

  • 适合索引的列应该是出现在where子句中的列，或者链接子句中指定的列。
  • 使用短索引，如果对长字符串列进行索引，应该指定前缀长度，这样能节省大量的索引空间。
  • 不要过度的设置索引。

• 创建原则：

  • 最左前缀匹配原则：MySQL会一直向右匹配直到遇到范围查询（> < between like）就停止匹配，所以如果是以下语句，去依照abcd建立索引，d是用不到的：

    where a=1 and b=2 and c>3 and d=4
    

  • 较频繁作为查询条件的字段应该设立索引，查询很少且有重复值比较多的列不适合建立索引。更新频繁的不适合建立索引。

  • 不能有效区分数据的列不适合做索引。

  • 尽量扩展索引，而不是新建索引。

  • 有外键的数据列一定要建立索引。

  • text（文本字段）、image和bit数据类型的列不适合索引。

• 聚簇索引和非聚簇索引

  • 聚簇索引：按照每张表的主键构造一棵B+树，将数据存储与索引放在一起，索引结构的叶子结点保存了数据。聚簇索引的叶子结点也叫数据页，每张表只能有一个聚簇索引。

    • InnoDB通过主键聚簇索引，如果没有定义主键，则会选择非空的唯一索引代替，如果还是没有这样的索引，那么InnoDB会隐式的定义一个主键作为聚簇索引（字段长为6个字节的长整型）。
    • 优点：
      • 数据访问的更快，因为索引和数据保存在同一个B+数中。
      • 对主键的排序查找和范围查找速度非常快。
    • 缺点：
      • 插入速度严重依赖于主键的排序，按照主键的顺序插入是最快的方式。对于InnoDB表，我们一般定义一个自增的ID作为主键。
      • 更新主键的代价很高，因为这样会导致被更新的行移动，改变B+树的结构。对于InnoDB表，我们一般定义主键不可更新。

  • 非聚簇索引：在聚簇索引上建立的索引。将数据与索引分开存储，索引结构的叶子结点存储是主键值，通过非聚簇索引先找到主键值，然后根据主键值去找对应的数据页。所以其需要二次查找，速度较聚簇索引慢一些。

    • 非聚簇索引的存在不影响数据在聚簇索引的组织，所以一张表可以有多个非聚簇索引。
    • MyISAM默认使用非聚簇索引。
    

• 面试题：like走不走索引？怎么走？

  • 走索引的情况的SQL语句应该是 like h% 或者 like h。而like %h是不走索引的。

• 面试题：为什么不采用其他的数据结构作为InnoDB的存储引擎

  • 用有序数组的问题：
    • 有序数组可以通过时间复杂度为OlogN二分查找，但是插入和删除操作代价太高了为ON。
  • 用Hash表的问题：
    • 哈希算法没有冲突的情况下的时间复杂度O1（在这里提一点，数组随机查询是O1，但是按值查询最快是二分查找OlogN），也就是说它的检索效率要比B+树索引需要从根结点到叶结点，多次IO访问高得多。
    • 但是其缺点也很明显：
      • 经过哈希算法处理之后的哈希值的大小关系并不是跟没处理前完全一样的，这也导致它不能去满足一些范围查询，例如 > 、< 等，他只能满足 = 、IN 、<=> 。（<=> 类似与 = ，不同的是结果，a<=>NULL 为0 NULL<=>NULL为1，而=的话这两个操作都是NULL）
      • 哈希索引不能利用部分索引键查询，哈希索引在计算哈希值的时候是组合索引键合并后一起计算的，而不是单独计算。
      • 任何时候都不能避免表扫描，不同索引键可能存在相同的哈希值，所以即便是满足某个哈希值，也无法直接查询，还得继续访问表中的实际数据。
      • 如果遇到了大量的等值哈希值，性能不一定比B+Tree高。
  • 用二叉搜索树（红黑树）的问题：
    • 不管是何种二叉搜索树，它的高度会随着结点数增加而增加。数据库的索引很大不可能直接装进内存（根结点可能装在内存）。查找的时候每次往下找一层就需要读一次磁盘IO。而IO的效率是很低的。
    • 数据库是以页的形式存储，InnoDB存储引擎默认一页为16K，一页可以看成是一个结点，二叉树的一个结点只能存储一个数据，造成了极大的空间浪费。
  • 用B树的问题：
    • B树不管是叶子结点还是非叶子结点，都会保存数据，这就导致在非叶子结点中能保存的指针数量变少。指针少的情况下要保存大量数据只能增加树的高度，导致IO操作变多，查询效率变低。
    • B树要想实现范围查找，只能进行中序遍历。

覆盖索引：对于SELECT、JOIN、WHERE操作，只从索引中就可以取得所有要的数据，而不需要回表从数据表中读取（查询列被所使用的索引覆盖）。

• 回表：使用非聚簇索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表。
• 因为建立索引的时候，查询出来的B+树叶子结点已经有了所有索引的数据以及主键值，自然不用回表

联合索引：两个或更多列上的索引被称为联合索引。

• 联合索引的最左前缀：MySQL从左到右使用索引中的字段，一个查询可以只使用索引中的一部分，但是只能是最左侧部分，例如联合索引是index(a,b,c)，可以支持a ab abc三种组合进行查找，但不支持bc进行查找。实现是采用B+树，每个节点含有多个关键字，排序的时候按照关键字来排序。ps：多个单列索引在多条件查询下只会生效第一个索引。
• 联合索引也是一颗B+树，排序方式是按照索引各列排序。例如依照（bcd）去建立联合索引，bcd就成了一棵二叉树。符合最左匹配原则，即首先按照第一个索引b去进行排序。如果第一个索引等值则按照第二个索引进行排序，以此类推。如果按照最左的索引排好序，其余的索引是无序的。

MySQL中的锁

• 隔离级别中的锁：

  • 未提交读：读数据不需要加共享锁，这样就不会和被修改的数据上的排他锁冲突。
  • 提交读：读操作加共享锁，但语句执行完后释放。
  • 可重读：读操作加共享锁，事务提交之前不释放共享锁，事务执行完后才释放。
  • 可串行化：锁定整个范围的键直到事务完成。

• 按照粒度分，有三种锁：

  • 行级锁：只针对当前操作的行进行加锁，行级锁能大大减少数据库的操作冲突。粒度最小，但加锁的开销也最大。行级锁分为共享锁和排它锁。
  • 表级锁：针对当前操作的整张表进行加锁，实现简单，开销小。粒度最大。
  • 页级锁：粒度介于行级锁和表级锁。表级冲突多，但速度快；行级冲突少，但速度慢，开销也介于行级锁和表级锁之间，会出现死锁且并发度一般。

• InooDB基于索引来完成行锁，其锁算法有三种：

  • Record Lock：行锁，单个行记录上的锁。

  • Gap Lock：间隙锁，锁定一个范围，不包括记录本身。

  • Next-key Lock：锁定一个范围且包括记录本身。

    • InnoDB对于行查询使用Next-key Lock，且其解决了幻读问题。当查询的索引含有为唯一属性时，将Next-key Lock降级为Record Lock。

    • Gap锁的设计目的是为了阻止多个事务将记录插入到同一范围内，防止幻读。

      事务A							    事务B										事务C
      begin							 begin									
      查询id、姓名					
      两个数据：1,hty1  20,hty20		
      更新hty = hty11 where id = 12
      								插入条数据，id=15，waiting					  插入条数据，id=33
      commit;							事务A commit之后，这条语句才插入成功			  commit;
      								commit;
      

      我们开三个事务，事务A更新的id=12在(1,20]之间，即使没有修改任何数据，InnoDB也会在这个区间加Gap锁，而其他区间不影响。所以事务B的插入id=15，需要等待事务A提交后才能正常提交。事务C的插入id不在此范围，正常插入。

      如果我们没有使用任何索引，即使没有匹配到任何数据，那么会给全表加gap锁（没有索引没有区间），除非该事务提交，否则其他事务无法插入任何数据

  • 行锁防止别的事务修改或删除，Gap锁防止别的事务新增，行锁和Gap锁结合的Next-key共同解决了RR级别写数据的幻读问题。

• MySQL的死锁问题

  • 死锁：两个多个事务在同一资源上互相占用，并同时请求对方的资源造成相互等待，若无外力作用，它们都无法进行下去。
  • 解决方案：
    • 保证以相同的顺序去访问表。
    • 保持事务简短。
    • 同一事务尽量做到一次锁定所需要的所有资源。
    • 降低隔离级别，将RR降低为RC，可以避免很多因为Gap锁而造成的死锁。
    • InnoDB针对死锁：将持有最少行级锁的事务回滚。
  • 死锁的四个必要条件：
    • 互斥条件：一个资源只能被一个进程使用。
    • 请求和保持：一个进程因请求资源而阻塞时，对已经获得的资源不放。
    • 不可剥夺：进程已经获取的资源，未使用完之前不能被强行剥夺。
    • 环路等待：若干进程之间形成一种头尾相连的循环等待资源关系。

• 两段锁协议：将事务分为加锁阶段和解锁阶段。

  • 加锁阶段：在对任何数据进行读操作之前要申请并获得共享锁（其他事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得排它锁（其他事务不能加任何锁）。加锁不成功则事务进入等待状态，直到加锁成功。
  • 解锁阶段：一个事务释放了一个锁后，进入解锁阶段，此阶段只能解锁不能加锁。

MySQL的页分裂与页合并

页的内部原理

页可以为空或者填满，行记录会按照主键顺序来进行排列，如果你使用AUTO_INCREMENT，你会有顺序的ID 1234等。

页还有一个属性时MERGE_THRESHOLD。该参数的默认值是50%页的大小，其在InnoDB中页合并中扮演了很重要的角色。当插入数据时，数据大小能够放进页中，则它们是按顺序将页填满的；若页满或无法放进，则下一行记录会被插入下一页中。

根据B+树的特性，其可以自顶向下遍历，也可以水平遍历各个叶子结点，因为每个叶子结点都有指向包含下一条顺序记录的页的指针。这种机制下可以做到快速的顺序扫描（范围扫描）。

页合并

当进行记录删除时，实际上记录并没有被物理删除，记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近页（前后都可以），看看是否可以将两个页合并以优化空间使用。合并使得其他页的空间得到了释放，以接纳新的数据。当然更新也是可以的。

页分裂

某个页已满或者即将满，此时来了一个数据大小大于剩余空间的数据：

根据下一页逻辑，该数据记录应该由这一页的下一页去处理，然而下一页也不够了，数据也不能不按照顺序插入：

此时InnoDB的做法是：

• 创建新的页
• 判断当前页和下一页可以从哪里进行页分裂（记录层面）
• 移动记录行
• 重新定义页之间的关系

经过页分裂后，B+树的水平方向性依然满足，然而从物理存储上讲页是乱序的，而且有可能落到不同的区。

页分裂会发生在插入或者更新，并且造成页的错位（落到不同的区）。出现这种情况要想恢复顺序就是进行新的页分裂出来的页面因为低于合并阈值而被删除掉，这时候InnoDB用页合并将数据合并回来。

一条SQL语句是怎么被执行的

• 首先连接到数据库上，此时由连接器接待。连接器的作用是负责跟客户端建立连接、获取权限、维持和管理连接。
• MySQL拿到一个查询请求后，先到查询缓存看看之前是否执行过该语句，如果执行过则直接从缓存中返回结果。
• 如果没有，分析器会识别出这条语句中的字符串是什么，代表什么，判断是否满足SQL语法。
• 通过分析器的分析后，执行优化器进行优化。优化器是在表里有多个索引的情况下，决定使用哪个索引，或者在一个语句有多表关联的时候决定各个表的连接顺序。
• 然后执行器执行语句，执行的时候先判断这条语句对这个表是否有访问的权限，如果没有则直接返回没有权限错误。
• 连接器-》查缓存-》分析器-》优化器-》执行器

MySQL关于语句的一些问题

MySQL关键字执行顺序

EXISTS vs IN

• EXISTS是对外表用loop逐条查询，每次查询都会查看EXISTS中的语句，当EXISTS里的条件语句能够返回记录行时候，条件就为真，返回当前loop到的这条记录。反之，当前loop的这条记录被丢弃。
• IN则是多个or条件的叠加，先将子查询条件的记录全部查出来，假设结果集为r，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。
• IN中的子查询要求返回结果必须只有一个字段，而EXIST就没有这个限制。
• IN在查询的时候会改成EXISTS去执行。

UNION vs UNION ALL

这两个操作都是对两个结果集进行并集操作，区别

• UNION的结果不包括重复行，相当于distinct，同时进行默认规则进行排序；而UNION ALL则包括重复行，所有的结果全部显示，且不进行排序。
• UNION会对获取的结果进行排序操作，而UNION ALL不会。

常见的聚合函数

• AVG：返回指定列中的平均值，忽略空值。
• COUNT：返回指定列中的行数量。
• MAX、MIN分别返回指定列中的最大值和最小值。
• SUM：返回指定数据的和，只能用于数字列，忽略空值。

WHERE vs HAVING

• 使用HAVING的时候，我们要求前面必须筛选出条件中的字段，而WHERE不做要求。
• WHERE条件后面是不能跟聚合函数的，因为WHERE执行顺序大于聚合函数，如果需要用聚合函数作为过滤条件则用HAVING。
• HAVING通常是对分组以后的数据进行筛选，所以一般都是在使用GROUP BY或者聚合函数后使用，而WHERE是在分组前对数据进行过滤。

GROUP BY

• 分组汇总，其要求SELECT出的结果字段都是可汇总的，否则就会出错。一般结合聚合函数，根据一个或多个列对结果集进行分组

• 原则：SELECT后面所有的列中，没有使用聚合函数的列，必须在GROUP的后面。例如下列语句就是错的，因为姓名性别和年龄未被汇总，且不一定单一：

  SELECT id, name, sex, sum(score) FROM students GROUP BY id /*该语句错误！*/
  

面试题：char和varchar的区别？

char是一种固定长度的类型，varchar则是一种可变长度的类型。

• char(M)类型的数据列里，每个值都固定占用M个字节，如果某个列的长度小于M，则MySQL就会在它的右边用空格字节去补足，检索的时候再将填补出来的空格字节去掉。
• 在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（M+1个字节）

在MySQL中，用来判断是否需要进行对数据列类型转化的规则：

• 一个数据表中，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的。
• 只要有一个数据列的长度是可变的，那么各个数据行的长度都是可变的。
• 如果某个数据表里的数据行的长度是可变的，那么为了节约存储空间，MySQL会把这个数据表里固定长度类型的数据列转换为相应可变的长度类型（长度小于4个字符不会被转换）

MySQL的连接

交叉连接

CROSS JOIN：笛卡尔积，直接把两个表的记录强行拼在一起。

外连接

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行。如果右表没有记录就返回null。

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行。如果左表没有记录就返回null。

FULL OUTER JOIN：求全集，有的DBMS是FULL JOIN，但是mysql不支持，只能left union right。有就返回数据，没有就返回null。

内连接

INNER JOIN：如果表中有至少一个匹配，则返回行。等同于直接JOIN。也就是两个表取交集，没有返回null，严格相连。

自然连接

NATURAL JOIN：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的列必须是相同的属性列（名字相同），无须添加任何连接条件，并且在结果中消除重复的属性列。

例如：有两个表A和B

如果执行： select * from table1 natural join table2

则返回：

MySQL优化

• 开启慢查询日志，在日志中记录运行比较慢的SQL语句。

  • 修改配置文件，在my.ini增加几行：主要是慢查询的定义时间以及慢查询日志记录。

  • 通过MySQL数据库开启慢查询

    

• 分析MySQL慢查询日志，可以利用explain关键字。

  • 执行EXPLAIN SELECT * FROM res_user ORDER BY xxx LIMIT 0,1000; 显示结果：
    • table 显示这一行的数据是关于哪张表的
    • type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
    • rows 显示需要扫描行数
    • key 使用的索引

优化点

• 不要建立太多索引。

  • 空间代价：每建立一个索引就要为它建立一棵B+树，而每个B+树的每一个结点都是一个数据页，一个页默认占用16KB的存储空间，B+树很大会十分占空间。
  • 时间代价：每次对表中的数据进行增、删、改都要修改各个B+树索引。B+树每层节点都是按照索引列的值从小到大顺序排成的双向链表。叶子和内结点中的值都是从小到大的单链表，增删改会对这些结点记录排序造成破坏，所以存储引擎需要一些额外的时间去进行一些记录移位。

• 联合索引的问题，例如index(name, birthday, phone)，联合索引在B+树中是按照索引的先后顺序进行排序的。所以在索引index中，先按照name列进行排序，name相同再按照birthday，birthday相同则按照phone。

  • 匹配最左边的索引：B+树的数据页记录是先按照name列的值排序的，如果name列值相同才按照birthday，如果name列值不同，则birthday是无序的。例如：

    SELECT * FROM person WHERE birthday = '1990-09-27';
    

    没有name的查询，则索引失效。

  • 匹配范围值：使用联合索引进行范围查找的时候，如果对多个列同时进行范围查找的话，只有对索引最左边的列进行范围查找才能用到B+树索引，例如：

    SELECT * FROM person WHERE name > 'ddd' AND name < 'hty' AND birthday > '2000-11-11';
    

    对于联合索引来说可以用name快速定位到通过条件name > ‘ddd’ AND name < ‘hty’，但无法通过birthday > '2000-11-11’继续过滤。因为通过name进行范围查找记录中可能不是按照birthday排序的。

  • 精确匹配某一列并范围匹配另外一列

    SELECT * FROM person WHERE name = 'hty' AND birthday > '2000-11-11' AND birthday < '2011-11-11' AND phone > '11111111111';
    

    name是精确查找，所以在name相同的情况下birthday是排好序的，对于birthday进行范围查找是可以用到B+树索引的，但对于phone来说，birthday排好序但phone是乱序的，就无法使用索引了。

  • 排序问题

    • 对于联合索引来说，ORDER BY的子句后面的列顺序也必须按照索引列的顺序给出，如果：

      ORDER BY phone, birthday, name DESC
      

      就用不了B+树索引了。

    • 对于ASC、DESC混用，不能使用联合索引进行排序，我们要求各个排序列的排序顺序是一致的，要么各个列都是ASC，要么都是DESC。

    • WHERE子句中出现非排序使用到的索引列无法使用索引，例如：

      SELECT * FROM person WHERE country = 'China' ORDER BY name LIMIT 10;
      

      这条语句需要回表后查出整行记录进行过滤后才能进行排序，无法使用索引。

    • 排序列包含非同一个索引的列时无法使用索引，例如

      SELECT * FROM person ORDER BY name, country LIMIT 10;
      

    • ORDER BY中使用函数也无法使用索引。

• 匹配列前缀（最左匹配原则）：如果一个字段是VARCHAR类型的，在索引中name字段的排列就会依照如下的规则：

  • 先比较字符串的第一个字符，第一个字符小那么这个字符串就比较小。

  • 如果两个字符串的第一个字符相同，那就比较第二个，第二个比较小的字符串比较小，以此类推。

  • 这也就是我们常说的like走索引问题：

    SELECT * FROM person WHERE name LIKE 'ht%';  /*走索引*/
    SELECT * FROM person WHERE name LIKE '%ty';  /*不走索引*/
    

• 覆盖索引：如果我们能查询到的所有列都在索引中找到，那么就不用回表去查找对应的列了。例如：

  SELECT name, birthday, phone FROM person WHERE name > 'ddd' AND name < 'hty'
  

  我们只查询这三个，都是联合索引中有的，那么就查询结果后就不必到聚簇索引中再查找剩余的列。

• 让索引列在比较表达式中单独出现：假设表中有一个整数列col，我们为这个列建立索引，进行如下查询：

  WHERE col * 2 < 4
  WHERE col < 4 / 2
  

  这两句虽然语义是一样的，但是效率有所差别：

  • 第一条的col并不是以单独列的形式出现，而是以col * 2这样的表达式的形式出现的。存储引擎会依次遍历所有记录，计算这个表达式是否小于4，这个情况是不走col列建立的索引的。
  • 第二条的col列是单独出现，则可以走索引。

• 页分裂带来的性能消耗

  • 我们假设一个页只能存储5条数据，中间三个是具体的数据，按照135排序。我们插入一个4，那么我们就要再分配一个新页。5>4 索引有序，我们需要将5移动到下一页，并插入一条id=4的新的数据到原页中。这个过程我们叫做页分裂，页分裂和记录移位意味着性能损耗。我们得尽量避免这种没必要的性能损耗。所以一般让插入记录的主键依次递增，即主键AUTO_INCREMENT，让存储引擎自己生成主键。

• 减少行锁的时间

  • 两段锁协议：InnoDB中，行锁是在需要的时候加上的，但并不是不需要了就立刻释放，而是等到事务结束时才释放。所以如果事务中需要锁多个行，把最可能造成锁冲突、影响并发度的锁往后放。

  • 假设有一业务：

    A在电影院B购买电影票：
    1.从顾客A账户余额中扣除电影票价
    2.给影院B的账户余额增加这张电影票价
    3.记录一条交易日志
    

    正常完成该交易的逻辑就是123，我们需要两条update和一条insert。为了保证交易的原子性，我们把这三个记录放在一个事务里。如果顾客A和顾客C同时买票，那么这两个事务会在语句2冲突，因为他们要更新同一个影院账户的余额，修改同一行数据。根据两段锁协议，不管怎么安排语句，所有的操作需要的行锁都是在事务提交的时候才释放，那么如果安排为312顺序的语句，这就最大程度的减少了事务之间锁的等待，提高了并发度。

• count函数优化：对比count(*)、count(主键id)、count(字段)和count(1)

  count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

  • 对于count(主键id)来说，InnoDB会遍历整张表，把每一行的id取出来，返回给server层，server层拿到id后，判断不可能是空的就按行累加。
  • 对于count(1)来说，InnoDB遍历整张表，但不取值，server层对于返回的每一行放进一个数字1，判断不可能为空就按行累加。很显然，count(1)比较快。
  • 对于count(字段)来说
    • 如果该字段定义为not null的话，一行行的从记录里面读这个字段，判断不可能null，直接按行累加。
    • 如果这个字段定义允许null，那么执行的时候，判断到有可能是null，则把值取出来判断，不是null才累加。
  • 对于count(*)，MySQL对其做了具体的优化，也是一种不取值的策略，底层会自动优化到执行哪一个字段。具体效率跟count(1)差不多。
  • 故：count(*) ≈ count(1) > count(主键id) > count(字段not null) > count(字段允许null)

• ORDER BY性能优化

  • MySQL排序中会用到内存来进行排序（sort_buffer_size），就是MySQL为排序开辟的内存（sort_buffer）大小，如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。如果排序量太大，内存放不下，就得利用磁盘临时文件辅助排序。如果查询返回的字段很多，那么sort_buffer里面要放得字段数太多，这样内存里能够同时放下的行数很少，就得分成很多个临时文件，排序性能很差。
  • 所以我们执行ORDER BY的时候
    • 返回的数据列数尽量的少，不返回不必要的数据列。
    • 索引天然有序，如果要排序的列有必要可以设置成索引，那么就不需要在sort_buffer中排序就可以直接返回了。
    • 如果有必要的话可以使用覆盖索引，这样返回数据的时候连通过主键回表都不需要就可以直接查询到数据。

• 隐式类型转换、隐式字符编码转换

  SELECT * FROM student WHERE stu_id = 11;
  

  我们指定stu_id为主键索引，VARCHAR类型，这条语句是不走索引而是走了全表。因为输入的是一个INT的整形，这说明隐式类型转换的SQL是无法走索引的。隐式字符编码转换同理。

• JOIN优化：有两个表t1和t2，表结果一模一样，字段a是索引字段

  • 情况1：双索引查找

    SELECT * FROM t1 JOIN t2 ON (t1.a = t2.a);
    

    1.从t1中读取一行数据R
    2.从数据R中取出a字段到表t2中查找
    3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分
    4.重复执行步骤1到3，直到t1的末尾循环结束

    这个SQL使用了索引，所以将t1表的数据取出来后根据t1表的a字段，实际上是对t2表的一个索引的等值查找，所以t1和t2比较的行数是相同的。由于是驱动表t1去匹配被驱动表t2，那么匹配的次数取决于t1有多少数据。所以在用索引关联的时候，最好使用数据量少的表作为驱动表。

  • 情况2：单索引查找

    SELECT * FROM t1 JOIN t2 ON (t1.a = t2.b);
    

    被驱动表没有可用索引，则此时进行如下操作：
    1.把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是SELECT *，因此把整个t1放入内存
    2.扫描t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分。如果join_buffer默认的256k放不下，则进行分段放，分段的话被驱动表就要扫描多次，影响性能。

  • 综上，对于哪个表作为驱动表的时候，我们应该根据各自的条件过滤，过滤完后计算参与join的各个字段的总数据量，数据量小的表作为驱动表。

小结索引失效的情况

• 联合索引失效情况：
  • 如果最左边的索引没有出现在查询结果的第一位，或者没有出现，索引失效。
  • 进行范围查找，只有最左边的列才能用到索引。如果最左边的列有相等查询，则第二个索引能走，以此类推。
  • ORDER BY的子句后面的列顺序也必须按照索引列的顺序给出，否则无效。
  • ASC、DESC混用，不能使用联合索引进行排序。
  • WHERE子句中出现非排序使用到的索引列无法使用到索引。
  • 排序列包含非同一个索引的列无法使用索引。
  • ORDER BY 中使用了函数也无法使用索引。
• 如果查询条件用or，必须or条件中的每个列都加上索引，否则无效。
• LIKE ‘%ty’ 无法走索引。
• WHERE col * 2 < 4 无法走索引，因为索引列没有在表达式中单独出现。WHERE子句中有聚合函数也无法使用索引
• 如果含有隐式类型、隐式字符编码转化则无法走索引。

SQL注入

定义：SQL注入是指将web页面的原URL、表单域或者数据输入的参数，修改拼接成SQL语句，传递给web服务器，进而传给数据库服务器以执行数据库命令。如果web程序对用户所输入的数据或者cookie等内容不进行过滤或者验证（存在注入点），那么就直接传输给数据库导致拼接的SQL被执行，获取对数据库的信息以及提权，发生SQL注入攻击。

防范：

• 分级管理：严格限制用户的权限，对于普通用户，禁止给予数据库的写权限。
• 参数传值：不要将变量直接写入SQL语句，而是通过设置相应的参数去进行传递相关的变量，数据的输入不能直接嵌入到查询语句中。
• 过滤：对用户的输入进行检查，如果遇到提交特殊字符则进行处理，确保数据输入的安全性。

MySQL中主从复制、log与数据库恢复

前面说的undo log和redo log并不是直接写到磁盘上的，而是先写入log buffer，再等合适的实际同步到OS buffer，再由OS系统决定何时刷到磁盘。undo log和redo log都是从log buffer到OS buffer再到磁盘，如果中途还是有可能因为断电/硬件故障等原因导致日志丢失。为此MySQL提供了三种持久化的方式：

• 一个参数：innodb_flush_log_at_trx_commit，该参数就是控制InnoDB将log buffer中的数据写入OS buffer，并刷到磁盘的时间点，取值分别为0，1，2，默认是1
  • 0：每秒写入OS buffer并调用fsync()刷到磁盘。
  • 1：每次提交写入OS buffer并调用fsync()刷到磁盘。
  • 2：每次提交写入OSbuffer，然后OS buffer每秒调用fsync()刷新到磁盘。

从保证数据一致性的角度来说，1这种方式是安全的，但是效率不高。每次提交都直接写入OS buffer并写入到磁盘，会导致单位时间内IO次数过多而效率低下。其中方式2对比方式0，写入OS buffer再写入磁盘少了一次拷贝的过程（从log buffer到OS buffer），相对于方案0高效一些。

这两个日志可以让数据库从异常的状态恢复到正常的状态：

• 数据库系统奔溃后重启，此时数据库处于不一致的状态，必须先进行一个crash recovery的过程：读取redo log，把成功提交但是还没来得及写入磁盘的数据重新写入磁盘，保证了持久性。在读取undo log将还没有成功提交的事务进行回滚，保证了原子性。crash recovery结束后，数据库恢复到一致性状态。

bin log

bin log是一个二进制日志，它记录了所有的DDL和DML语句（处理数据查询语句等），以事件的形式记录，还包含了语句所执行的消耗时间。其中，MySQL的二进制日志是事务安全的，binlog的主要目的就是复制和恢复。

• MySQL主从复制：MySQL Replication在Master端开启bin log，Master把它的二进制日志传递给Slaves来达到master-slave数据一致的目的。
• 数据恢复：通过使用MySQL bin log工具来恢复数据。

主从复制：通过binlog实现

步骤：

• 主库db的更新事件(update、insert、delete)被写到binlog
• 从库发起连接，连接到主库
• 此时主库创建一个binlog dump thread（主库线程），把binlog的内容发送到从库
• 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log（本地中继日志）
• 还会创建一个SQL线程，从relay log（中继日志）里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到从库的db

整个MySQL的主从复制是异步的。

记录bin log的时机

Statement：基于 SQL语句级别的 Binlog，每条修改数据的 SQL都会保存到 Binlog里。

Row：基于行级别，记录每一行数据的变化，也就是将每行数据的变化都记录到 Binlog 里面，记录得非常详细， 但是并不记录原始 SQL；在复制的时候， 并不会因为存储过程或触发器造成主从库数据不一致的问题，但是记录的日志量较 Statement格式要大得多 。

Mixed：混合Statement和Row模式，默认情况下采用 Statement模式记录，某些情况（由系统状况决定）下会切换到 Row模式同时也对应了 MysQL复制的3种技术。

存bin log进磁盘的时机

bin log存进磁盘的时机：对于支持了事务的引擎InnoDB而言，必须要提交了事务才会记录binlog。binlog什么时候刷新到磁盘跟参数sync_binlog相关。

• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中；
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果sync_binlog = 0或者sync_binlog大于1，当发生店员故障或者操作系统奔溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。在MySQL5.7.7之前，默认值sync_binlog是0，MySQL5.7.7和更高版本使用默认值1，这是安全的选择。一般设置为0或者100来提高性能。