MySQL基础知识总结

本文是对JavaGuide面试突击版的整理和扩展。

1. 什么是MySQL?

MySQL 是⼀种关系型数据库， 在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，⽅便扩展，且较为稳定。MySQL是开放源代码的，任何⼈都可以在 GPL的许可下下载并根据个性化的需要对其进⾏修改。MySQL的默认端⼝号是3306。

2. MyISAM和InnoDB区别

MyISAM是5.5版本之前MySQL的默认数据库引擎，虽然执⾏速度较快，但不⽀持事务和⾏级锁，⽽崩溃后⽆法安全恢复。

5.5版本之后，MySQL引⼊了InnoDB，即事务性数据库引擎，MySQL 5.5版本后默认的存储引擎为InnoDB。

两者的对⽐：

1. 是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。InnoDB ⽀持事务等⾼级数据库功能，是具有事务、回滚和崩溃修复能⼒的事务安全型表。
2. 是否⽀持⾏级锁 : MyISAM 只有表级锁，⽽InnoDB ⽀持⾏级锁和表级锁,默认为⾏级锁。
3. 是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
4. 是否⽀持MVCC ：InnoDB支持MVCC, 而MyISAM不支持
   5. 全文索引：InnoDB不支持全文索引，而MyISAM支持。 (MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引)

3. 锁机制（考察2次）

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制，即，MyISAM采⽤表级锁。InnoDB⽀持⾏级锁和表级锁,默认为⾏级锁。

MySQL的锁大致可归纳为以下3种锁：
表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁： 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyIsam
在使用MyIsam时，我们只可以使用表级锁，而MySQL的表级锁有两种模式：
表共享锁（Table Read Lock）、表独占写锁（Table Write Lock）。
他们在工作时表现如下：

1. 对某一个表的读操作，不会阻塞其他用户对同一表读请求，但会阻塞对同一表的写请求。。
2. 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
3. MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
4. 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

MyIsam如何加表锁：
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
给MyISAM表显式加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

MyIsam的并发锁：
在一定条件下，MyISAM也支持查询和操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

1. 当concurrent_insert设置为0时，不允许并发插入。
2. 当concurrent_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
3. 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。

InnoDB
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题，例如事务及其ACID属性 （原⼦性、⼀致性、隔离性、持久性）、并发事务带来的问题（脏读等）。

InnoDB的行锁模式及加锁方法
InnoDB实现了两种类型的行锁：共享锁（S）、排他锁（Ｘ）。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，意向共享锁（IS）意向排他锁（IX），这两种意向锁都是表锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB会自动给涉及数据集加共享锁（S）；事务可以显式给记录集加共享锁或排他锁。

InnoDB行锁是通过索引上的索引项来实现的，只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁。

此外，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。

InnoDB有三种行锁的算法：
Record Lock： 单个行记录上的锁。
Gap Lock： 间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
Next-Key Lock： record+gap，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

4. MVCC

innodb的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然，存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

在REPEATABLE READ隔离级别下，MVCC具体操作有：

对于SELECT，innodb会根据两个条件检查每行记录：

1. innodb只查找版本号早于当前事务版本的数据行，即行的系统版本号<=事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
2. 行的删除版本要么未定义，要么大于当前的事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
   　 只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT

INNODB为新插入的每一行保存当前系统版本号作为行版本号

DELETE

innodb为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

innodb为插入一行新纪录，保存当前系统版本号为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

保存这两个额外系统版本号，使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

此外，MVCC只在repeatable read和read committed两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。

5. 事务

事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。

事物的四⼤特性(ACID)

1. 原⼦性（Atomicity）： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤；
2. ⼀致性（Consistency）： 一致性是对数据可见性的约束，事务中间状态的数据对外不可见，只有事务最初和最终状态的数据对外可见。
3. 隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
4. 持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

6.并发事务带来的问题(脏读等）

在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀数据进⾏操作）。并发虽然是必须的，但可能会导致以下的问题。

1. 脏读（Dirty read）:
所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

2.不可重复读（Unrepeatableread）:
指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。

3.幻读（Phantom read）:
幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

4. 丢失修改（Lost to modify）:
指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

7. 事务隔离级别 MySQL默认隔离级别

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)：
允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

REPEATABLE-READ(可重复读)：
对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。

SERIALIZABLE(可串⾏化)：
最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。

因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是 READ-COMMITTED(读取已提交) 。而MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）。

与 SQL 标准不同的地⽅在于 InnoDB 存储引擎在REPEATABLE-READ（可重读） 事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣。因此，InnoDB 存储引擎的 REPEATABLE-READ（可重读） 就已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串⾏化) 隔离级别。

InnoDB 存储引擎在 分布式事务 的情况下⼀般会⽤到SERIALIZABLE(可串⾏化) 隔离级别。

8. 索引的优缺点 分类

优点：
索引是MySQL查询优化最主要的方式，可以加快检索的速度。 索引包含表中的一列或多列生成的键。这些键存储在一个结构中，使 SQL Server 可以快速有效地查找与键值关联的行。就好比书的目录，可以帮助我们快速查找到需要的内容，当数据表记录达到几十万级别的时候，索引加速查询速度的作用就非常明显了。

缺点：
虽然索引大大提高了查询速度，但同时却会降低更新表的速度，即会降低INSERT、UPDATE和DELETE操作的速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，那么索引文件的会膨胀很快。另外，索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

不建议使用索引的情况：
(1) 数据量很小的表；
(2) 空间紧张时；

索引的分类：
索引可以在建表的时候同时创建，也可以在之后使用 ALTER 命令修改。

1. 主键索引 PRIMARY KEY
它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

2. 唯一索引 UNIQUE
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。如：ALTER TABLE table_name ADD UNIQUE (column)

3. 普通索引 INDEX
这是最基本的索引，它没有任何限制。如：ALTER TABLE table_name ADD INDEX index_name (column)

**4. 组合索引 INDEX **
即一个索引包含多个列。如：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

5. 全文索引 FULLTEXT
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。如：ALTER TABLE table_name ADD FULLTEXT (column)

9. 索引相关sql语句 explain drop show

1. explain
explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。explain主要用于分析查询语句或表结构的性能瓶颈。
如：explain SELECT * FROM table_name WHERE column_1=‘123’;

通过explain+sql语句可以知道如下内容：
①表的读取顺序。（对应id）
②数据读取操作的操作类型。（对应select_type）
③哪些索引可以使用。（对应possible_keys）
④哪些索引被实际使用。（对应key）
⑤表直接的引用。（对应ref）
⑥每张表有多少行被优化器查询。（对应rows）

2. 删除索引 (DROP)
DROP INDEX my_index ON tablename；
或者 ALTER TABLE table_name DROP INDEX index_name;

3. 查看表中的索引 (SHOW)
SHOW INDEX FROM tablename

10. 索引底层结构 聚集索引和非聚集索引

索引底层数据结构主要有 哈希索引和 B树索引 。

1. 对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；

2. 其余⼤部分场景，建议选择B树索引，且B树索引又可分为B树索引和B+树索引，B树主要用于文件系统以及部分数据库索引，例如： 非关系型数据库MongoDB。而大部分关系数据库则使用B+树做索引，例如：Mysql数据库.

MySQL的B树索引使⽤的是B树中的B+树，但对于主要的两种存储引擎的实现⽅式是不同的。其中Myisam是非聚集索引，innoDB是聚集索引。

聚集索引和非聚集索引的局别在于：
聚集索引的叶节点就是数据节点；而非聚集索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

具体实现如下：

MyISAM: 非聚集索引， 其B+树叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+树搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“⾮聚集索引”。

InnoDB: 聚集索引， 其表数据⽂件本身就是按B+树组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这被称为“聚集索引”。这个索引的key是数据表的主键,⽽其余的索引都作为辅助索引,在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。

11. B+树和红黑树及B树相比的优势

MySQL的B树索引使⽤的是B树中的B+树，其和红黑树、B树相比，有一些优势：

和红黑树、二叉查找树相比：
如果利用二叉查找树或者红黑树作为索引结构，在寻找到每一个节点时，均会进行一次IO操作，造成大量磁盘ＩＯ操作，最坏情况下为树的高度。即由于树深度过大而造成磁盘IO读写过于频繁，导致效率低下。所以，为了减少磁盘ＩＯ的次数，就必须降低树的深度，一个基本的想法就是：每个节点存储多个元素，并且摒弃二叉树结构，采用多叉树，这样就产生了一个新的查找树结构 ——多路查找树：B树和B+树。

和B树相比：
B树主要用于文件系统以及部分数据库索引，例如： 非关系型数据库MongoDB。而大部分关系数据库则使用B+树做索引，例如：Mysql数据库。
B+树相比B树的优势有三个：

1. Ｂ树每个结点中的每个关键字都有卫星数据，而在Ｂ＋树中，只有叶子结点带有卫星数据，其余中间节点仅仅是索引，没有任何数据关联。这就意味着对于B+树，同样的大小的磁盘页可以容纳更多节点元素，即在相同的数据量下，Ｂ＋树更加“矮胖”，ＩＯ操作更少。（卫星数据即索引元素所指向的数据记录，比如数据库中的某一行）
2. 所有查询都要查找到叶子节点，查询性能稳定；
3. 所有叶子节点形成有序链表，便于范围查询，即B+树的范围查询，则只需要在查到到范围的下限后在链表上做遍历即可，而B树的范围查询只能依靠繁琐的中序遍历。这也是关系型数据库采用B+树的最主要的原因。

12. 建立索引的原则

1. 最左前缀匹配原则
对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。一般，在创建多列索引时，where子句中使用最频繁的一列放在最左边。

如果是联合索引，要想使用索引，where后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。如果跳过某一索引字段，索引无效，即未使用到索引。

特殊情况：where后面查询条件顺序与索引顺序不一致时，还是可以使用索引，这是因为MySql底层索引优化器做了优化，where条件后的语句是可以乱序的，但是最好还是按顺序使用索引。

如果where条件中是OR关系，加索引不起作用。

2. 尽量选择区分度高的列作为索引
比如，我们会选择学号做索引，而不会选择性别来做索引。

3. =和in可以乱序
比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4. 索引列不能参与计算，保持列的“干净”
原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。

5. 尽量的扩展索引，不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

13. MySQL常用优化总结

1. 有索引但未被用到的情况，有一些情况将会导致数据库引擎放弃使用索引而进行全表扫描，使得查询效率大幅度降低：
(1) Like的参数以通配符开头时，即应尽量避免Like的参数以通配符(如%等)开头。
(2)索引不应该包含有NULL值的列，只要列中包含有NULL值都不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
(3) where条件不符合最左前缀原则时，即对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。
(4) 使用不等于操作符时，即应尽量避免使用不等于操作符(！= 或 <>,sql中!=和<>操作符均表示不等于)，使用>或<会比较高效。
(5) 索引列参与计算时，即应尽量避免在 where 子句中对字段进行表达式操作。
(6) 对字段进行null值判断时，应尽量避免在where子句中对字段进行null值判断。
(7) 使用or来连接条件时，应尽量避免在where子句中使用or来连接条件。

2. 避免使用 "select * "
在SQL解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。所以，应该养成一个需要什么就取什么的好习惯。

3.order by 语句优化
任何在Order by语句中的非索引项或者计算表达式都将降低查询速度。
解决方法方法：
1.重写order by语句以使用索引；
2.为所使用的列建立另外一个索引
3.绝对避免在order by子句中使用表达式。

4. GROUP BY语句优化
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

5.用 exists 代替 in

6.尽可能的使用 varchar/nvarchar 代替 char/nchar
首先，因为变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
char:固定长度，存储ANSI字符。
nchar：固定长度，存储Unicode字符。
varchar：可变长度，存储ANSI字符。
nvarchar：可变长度，存储Unicode字符。
ANSI主要是以单字节来存储数据，一般适合英文。而我们常用的汉字需要用两个字节来存储，所以就要使用unicode的数据类型，不然读取出来的数据可能会乱码。如果项目中可能涉及不同语言之间的转换，建议用nchar和nvarchar。
7. 能用UNION ALL就不要用UNION
union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序。
union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复了，而且不会对获取的结果进行排序操作。
即在没有去重的前提下，使用union all不执行SELECT DISTINCT函数，执行效率要比union高。
8.在进行Join的时候使用相同类型的字段，并将其索引
如果需要进行很多JOIN 查询，当两个表中Join的字段被建立了索引，MySQL内部会启动优化Join的SQL语句的机制。而且，这些被用来Join的字段，应该是相同的类型的，且对于那些STRING类型，还需要有相同的字符集才行。

14. 查询缓存的使⽤

执⾏查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实⽤。

MySQL执⾏以下命令可以开启查询缓存:
set global query_cache_type=1;
set global query_cache_size=600000;

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。缓存建⽴之后，MySQL的查询缓存系统会跟踪查询中涉及的每张表，如果这些表发⽣变化，那么和这张表相关的所有缓存数据都将失效。缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做⼀次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应⽤来说更是如此。

可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

15. mysql日志 redo log binlog undo log

MySQL日志类型分为：物理日志（存储了数据被修改的值）和逻辑日志（存储了逻辑SQL修改语句），其中，redo log （重做日志）是物理日志，undo log （回滚日志）和 binlog （二进制日志）是逻辑日志，物理日志的恢复速度远快于逻辑日志。

1. redo log、undo log

innodb 事务日志包括 redo log 和 undo log，redo log 是重做日志，提供前滚操作，undo log 是回滚日志，提供回滚操作，其中前者保证事务的持久性，后者保证事务的原子性，两者可以统称为事务日志。

redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。而undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；例如某一事务的事务序号为A，其对数据X进行修改，设X的原值是0，修改后的值为1，那么Undo日志为<A, X, 0>，Redo日志为<A, X, 1>。

前滚： 未完全提交的事务，即该事务已经被执行 commit 命令了，只是现在该事务修改所对应的脏数据块中只有一部分被写到磁盘上的数据文件中，还有一部分已经被设置了提交标记的脏块还在内存上，如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用前滚来完成事务的完全提交，即将先前那部分已经被设置了提交标记且还在内存上的脏块写入到磁盘上的数据文件中。

回滚： 未提交的事务，即该事务未被执行 commit 命令。但是此时，该事务修改的脏块中也有可能一部分脏块写入到数据文件中了。如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用回滚来将先前那部分已经写入到数据文件的脏块从数据文件上撤销掉。

redo log (重做日志)
作用： 防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。事务开始之后，就开始产生 redo log 日志了，在事务执行的过程中，redo log 开始逐步落盘，当对应事务的脏页写入到磁盘之后，redo log 的使命就完成了，它所占用的空间也就可以被覆盖了。
内容： redo log 包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，redo log 存储的是物理格式的日志，记录的是物理数据页面的修改信息，它是顺序写入 redo log file 中的。
落盘方式（将 innodb 日志缓冲区的日志刷新到磁盘）
1.Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件
2.每个事务提交时会将重做日志刷新到重做日志文件
3.当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件
原理： 对于mysql，如果每次更新操作都要写进磁盘，然后磁盘要找到对应记录，然后再更新，整个过程io成本、查找成本都很高。
相应的解决方案就是：WAL技术（Write-Ahead Logging）。先写日志，再写磁盘。
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

write pos 是当前记录的位置，一边写一边后移，当一组有4个文件时，写到第 3 号文件末尾后就会回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是log上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

undo log(回滚日志)
undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

生命周期：
事务开始之前，将当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性。当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。

存储内容：
undo log 存储的是逻辑格式的日志，保存了事务发生之前的上一个版本的数据，可以用于回滚。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo 链找到满足其可见性的记录。

存储位置：
默认情况下，undo 文件是保存在共享表空间的，也即 ibdatafile 文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的 undo log 信息，这些信息全部保存在共享表空间中，因此共享表空间可能会变得很大，默认情况下，也就是 undo log 使用共享表空间的时候，被“撑大”的共享表空间是不会、也不能自动收缩的。因此，MySQL5.7 之后引入了“独立 undo 表空间”的配置。

2. binlog
MySQL 包括 Server 层（负责MySQL 功能层面的事情）和引擎层（负责存储相关的具体事宜）。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
binlog 用于主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。用于数据库的基于时间点、位点等的还原操作。binlog 的模式分三种：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）。

redo log 和binlog有以下三点不同：

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

生命周期：
事务提交的时候，一次性将事务中的 sql 语句（一个事务可能对应多个 sql 语句）按照一定的格式记录到 binlog 中，这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候才刷新到磁盘，而是在事务开始之后就开始逐步写入磁盘。binlog 的默认保存时间是由参数 expire_logs_days 配置的，对于非活动的日志文件，在生成时间超过 expire_logs_days 配置的天数之后，会被自动删除。

执行update 语句时的内部流程：(“给 ID=2这一行的 c 字段加 1 ”)

1. 执行器先找引擎取 ID=2这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据C，把这个C值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
   最后三步，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是 “两阶段提交” 。
   

两阶段提交:
两阶段提交，是为了binlog和redolog两份日志之间的逻辑一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。可能造成的问题：
还是以update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 先写 redo log 后写 binlog。
   假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
2. 先写 binlog 后写 redo log。
   如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
   如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致线上出现主从数据库不一致的情况。

16. ⼤表优化

当MySQL单表记录数过⼤时，数据库的CRUD性能会明显下降，⼀些常⻅的优化措施如下：
限定数据的范围
务必禁⽌不带任何限制数据范围条件的查询语句。⽐如：我们当⽤户在查询订单历史的时候，我们可以控制在⼀个⽉的范围内；
读/写分离
经典的数据库拆分⽅案，主库负责写，从库负责读；

垂直分区
垂直分区是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表，如下图所示：
垂直拆分的优点： 可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤层进⾏Join来解决。此外，垂直分区会让事务变得更加复杂；

⽔平分区
保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。 即，⽔平分区是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数据拆成多张表来存放。⽔平拆分可以⽀撑⾮常⼤的数据量。

需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以 ⽔平拆分最好分库。

⽔平拆分能够 ⽀持⾮常⼤的数据量存储，应⽤端改造也少，但 分⽚事务难以解决 ，跨节点Join性能较差，逻辑复杂。 所以尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂度 ，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择客户端分⽚架构，这样可以减少⼀次和中间件的⽹络I/O。

下⾯补充⼀下数据库分⽚的两种常⻅⽅案：
客户端代理： 分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当⽹的Sharding-JDBC 、阿⾥的TDDL是两种⽐较常⽤的实现。
中间件代理： 在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、⽹易的DDB等等都是这种架构的实现。

17. 池化思想 数据库连接池

池化思想
我们常⻅的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。

数据库连接池
数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限之类的信息，所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重⽤这些连接。
如果为每个⽤户打开和维护数据库连接，既昂贵⼜浪费资源。而创建连接池后，创建连接后，将其放置在池中，进行重复使用，节约了资源消耗。连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

18. 分布式id生成方案

ID是数据的唯一标识，传统的做法是利用UUID和数据库的自增ID，UUID太长以及无序，所以并不适合在Innodb中来作为主键，自增ID比较合适，但是随着数据量的扩大，需要对数据进行分表，而分表后，每个表中的数据都会按自己的节奏进行自增，很有可能出现ID冲突。这时就需要一个单独的机制来负责生成唯一ID，生成出来的ID也可以叫做分布式ID，或全局ID。

⽣成全局 id 有⼏种⽅式：

1. 数据库⾃增 id :
这种生成分布式ID的机制，需要一个单独的Mysql实例，虽然可行，但是业务系统每次需要一个ID时，都需要请求数据库获取，性能低，并且如果此数据库实例下线了，那么将影响所有的业务系统。

为了解决数据库可靠性问题，产生了第二种分布式ID生成方案:

2. 数据库多主模式

如果我们两个数据库组成一个主从模式集群，正常情况下可以解决数据库可靠性问题，但是如果主库挂掉后，数据没有及时同步到从库，这个时候会出现ID重复的现象。因此我们可以使用双主模式集群，也就是两个Mysql实例都能单独的生产自增ID，单独给每个Mysql实例配置不同的起始值和自增步长，能够大大提高效率。

对于这种生成分布式ID的方案，需要单独新增一个生成分布式ID应用，比如DistributIdService，该应用提供一个接口供业务应用获取ID， 当业务应用需要一个ID时，通过rpc的方式请求DistributIdService，然后DistributIdService再随机去两个Mysql实例中去获取ID。因为是双主模式集群，实行这种方案后，就算其中某一台Mysql实例下线了，也不会影响DistributIdService，其仍然可以利用另外一台Mysql来生成ID。

但是这种方案的扩展性不太好，如果两台Mysql实例不够用，需要新增Mysql实例来提高性能时，这时就会比较麻烦。 如果现在要新增一个实例mysql3，那么首先，mysql1、mysql2的步长肯定都要修改为3，第二，因为mysql1和mysql2是不停在自增的，对于mysql3的起始值我们要定得大一点，以给充分的时间去修改mysql1，mysql2的步长。 最后，在修改步长的时候很可能会出现重复ID，要解决这个问题，可能需要停机才行。

为了解决上面的问题，以及进一步提高DistributIdService的性能，产生了第三种生成分布式ID机制。

3. 号段模式
我们可以使用号段的方式来获取自增ID，号段可以理解成批量获取，比如DistributIdService从数据库获取ID时，如果能批量获取多个ID并缓存在本地的话，可以大大提高获取ID的效率。

例如DistributIdService每次从数据库获取ID时，就获取一个号段，比如(1,1000]，这个范围表示了1000个ID，业务应用在请求DistributIdService提供ID时，其只需要在本地从1开始自增并返回即可，而不需要每次都请求数据库，一直到本地自增到1000时，也就是当前号段已经被用完时，才去数据库重新获取下一号段。

这种方案不再强依赖数据库，就算数据库不可用，那么DistributIdService也能继续支撑一段时间。但是如果DistributIdService重启，会丢失一段ID，导致ID空洞。

同时，为了提高可用性，可能需要做一个集群，但是业务在请求DistributIdService集群获取ID时，会随机的选择某一个DistributIdService节点进行获取，因此可能会产生多个节点同时请求数据库获取号段的并发问题，此时可以利用乐观锁来进行控制。

4. 雪花算法：
对于分布式ID，只要能让负责生成分布式ID的每台机器在每毫秒内生成不一样的ID就行了。雪花算法是一个开源的分布式ID生成算法，它不依赖于数据库。
核心思想是：分布式ID固定是一个long型的数字，一个long型占8个字节，也就是64个bit：

第一个bit位是标识部分，在java中由于long的最高位是符号位，正数是0，负数是1，一般生成的ID为正数，所以固定为0。

之后是时间戳部分，占41bit，这个是毫秒级的时间，一般实现上不会存储当前的时间戳，而是时间戳的差值（当前时间-固定的开始时间），这样可以使产生的ID从更小值开始(41位的时间戳可以使用69年，(1L << 41) / (1000L * 60 * 60 * 24 * 365) = 69年)。

然后是工作机器id，占10bit，这里比较灵活，比如，可以使用前5位作为数据中心机房标识，后5位作为单机房机器标识，可以部署2^10=1024个节点。

最后是序列号部分，占12bit，支持同一毫秒内同一个节点可以生成2^12=4096个ID。
根据雪花算法的逻辑，只需要将这个算法用Java语言实现出来，封装为一个工具方法，那么各个业务应用可以直接使用该工具方法来获取分布式ID，只需保证每个业务应用有自己的工作机器id即可，而不需要单独去搭建一个获取分布式ID的应用。

另外在实际应用中，很多时候并没有直接使用雪花算法，而是进行了改造，因为雪花算法中最难实现的就是工作机器id，原始的雪花算法需要人工去为每台机器去指定一个机器id，并配置在某个地方，从而让雪花算法从此处获取机器id。但是当机器很多时，人力成本太大且容易出错，所以有时需要对雪花算法进行改造。

5. 百度（uid-generator）
uid-generator使用的就是雪花算法的变体，在生产机器id，也叫做workId时有所不同。

uid-generator中的workId是自动生成的，uid-generator需要新增一个WORKER_NODE表，借助该表，用户可以自己去定义workId的生成策略，默认提供的策略是：应用启动时由数据库分配，即应用在启动时会往数据库表WORKER_NODE中去插入一条数据，数据插入成功后返回的该数据对应的自增唯一id就是该机器的workId。

对于uid-generator中的workId，占用了22个bit位，时间戳占用了28个bit位，序列号占用了13个bit位，需要注意的是，这里时间戳的单位是秒，而不是毫秒，workId也不一样，同一个应用每重启一次就会消耗一个workId。

6. 美团（Leaf）

美团的Leaf也是一个分布式ID生成框架，既支持号段模式，也支持雪花算法模式。其雪花算法模式和原始雪花算法的不同点，也主要在workId的生成，Leaf中workId是基于ZooKeeper的顺序Id来生成的，每个应用在启动时都会都在Zookeeper中生成一个顺序Id，相当于一台机器对应一个顺序节点，也就是一个workId。

总的来说，百度（uid-generator）和美团（Leaf）都是自动生成workId，使得系统更加稳定并且减少人工操作。

7. 使用Redis来生成分布式ID

和利用Mysql自增ID类似，可以利用Redis中的incr命令来实现原子性的自增与返回。

使用redis的效率是非常高的，灵活方便，不需要依赖数据库，但是引入了新的组件造成系统更加复杂，且要考虑持久化对ID生成的影响。

Redis支持RDB和AOF两种持久化的方式。RDB持久化相当于定时产生一个快照进行持久化，如果产生快照后，连续自增了几次，还没来得及做下一次快照持久化，这个时候Redis挂掉了，重启Redis后会出现ID重复。AOF持久化相当于对每条写命令进行持久化，如果Redis挂掉了，不会出现ID重复的现象，但是会由于incr命令导致重启恢复数据时间较长。

19. 什么是SQL注入，如何避免？（考察1次）

SQL注入是目前黑客最常用的攻击手段，它的原理是利用数据库对特殊标识符的解析强行从页面向后台传入。改变SQL语句结构，达到扩展权限、创建高等级用户、强行修改用户资料等等操作。
比如：假设name是用户提交来的数据
String name = “‘盖伦’ OR 1=1”;
拼接出来的语句是：
select * from hero where name = ‘盖伦’ OR 1=1
因为有OR 1=1，这是恒成立的，那么就会把所有的英雄都查出来，而不只是盖伦，如果Hero表里的数据是海量的，比如几百万条，把这个表里的数据全部查出来，会让数据库负载变高，响应变得极其缓慢。
解决方案：
1.检查变量数据类型和格式，过滤特殊符号
2…绑定变量，使用预编译语句，预编译语句使用的是参数设置，从根本上杜绝了SQL注入攻击的发生。
3.对数据库信息进行加密。

20. left join、right join、inner join的区别

内连接 innner join：
组合两个表中的记录，返回关联字段的记录，返回两个表交集部分。

左连接 left join：
左连接是外连接的一种，左表的数据全显示，右表显示符合搜索条件的记录，右表剩余的部分显示null。

右连接 right join ：
右连接也是外连接的一种，右表的数据全显示，左表显示符合搜索条件的记录，左表剩余的部分均为null。

全连接：目前mysql不支持、

21.where、having

1. where条件后面是不能跟聚合函数(sum,min,max,count)的，因为where执行顺序大于聚合函数，如果需要用聚合函数作为过滤条件则用having。
2. having后面可以跟聚合函数(sum,min,max,count)的，其通常是对分组以后的数据进行筛选，所以一般都是在group by后使用，而where是在分组前对数据进行过滤。

where、聚合函数、having 在from后面的执行顺序：where>聚合函数(sum,min,max,avg,count)>having

举例如下：
select sum(score) from student where sex=‘man’ group by name having sum(score)>210

22 delete truncate drop

在速度上，一般来说，drop> truncate > delete。

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。