1.原理和SQL
（1）什么是事务？事务基本特性ACID？
事务指的是满足ACID特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。
A原子性(atomicity) 指的是一个事务中的操作要么全部成功，要么全部失败。
C一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
I隔离性(isolation) 指的是一个事务的修改在最终提交前，对其他事务是不可见的。
D持久性(durability) 指的是一旦事务提交，所做的修改就会永久保存到数据库中。
（2）数据库中并发一致性问题？
在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。
丢失修改：T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
读脏数据：T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读：T2读取一个数据，T1对该数据做了修改。如果 T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻影读：T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
（3）事务的隔离等级
未提交读：事务中的修改，即使没有提交，对其它事务也是可见的。
提交读：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
可重复读：保证在同一个事务中多次读取同样数据的结果是一样的。
可串行化：强制事务串行执行。
提交读可解决脏读问题
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
（4）ACID靠什么保证
A原子性(atomicity)：由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性(consistency) 一般由代码层面来保证（是由其他三大特征保证,程序代码要保证业务上的一致性）
I隔离性(isolation) 由MVCC多版本并发控制机制来保证
多版本并发控制：通过建立快照，维护版本链，进行多版本并发控制，决定当前快照读能查询到的版本，使得不同事务的读-写操作不会冲突（通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。）
D持久性(durability) 由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复
（通过redolog日志来保证。在我们进行增删改数据的时候，会先在redolog日志中保存。然后再将数据写入到磁盘。
因为修改数据是在磁盘中随机读写，而写入日志文件是顺序读写，顺序读写的效率要明显高于随机读写。因此，只要数据保存到了redolog中，即使之后读写磁盘的过程发生了故障，也不会造成数据的丢失。
例如：服务器意外宕机，innoDB可以根据redolog日志重新恢复到宕机前的状态，以此保证数据的完整性。）
1.对查询进行优化，尽量避免全表扫描。首先考虑在where及order by涉及的列上建立索引。
2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用100个字符的空间的，如果是varchar这样的变长字段，null不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
3.应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
5.in 和 not in 也要慎用，否则会导致全表扫描
对于连续的数值，能用between就不要用in了（between and是包括边界值的,not between不包括边界值）
很多时候用exists代替in是一个好的选择：
若要提高效率，可以考虑全文检索。
全文搜索(Full-Text Search）是基于分词的文本检索功能，依赖于全文索引。全文索引不同于传统的平衡树（B-Tree）索引和列存储索引，它是由数据表构成的，称作倒转索引（Invert Index），存储分词和行的唯一键的映射关系。
2.TRUNCATE TABLE 只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL Data Definition Language)，执行后不能撤销。
3.DELETE TABLE 删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以撤销。
（6）Buffer Pool、Redo Log Buffer和undo log、redo log、bin log概念以及关系
Buffer Pool是MySQL的一个非常重要的组件，因为针对数据库的增删改操作都是在Buffer Pool中完成的。
Undo log 记录的是数据操作前的样子。 原子性
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）。持久性
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）。
（7）从准备更新一条数据到事务的提交流程描述
1.首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
2.在数据被缓存到缓存池的同时，会写入undo log日志文件
3.更新动作是在BufferPool中完成的，同时会将更新后的数据添加到redo log buffer中
4.完成以后就可以提交事务，在提交的同时会做以下三件事：
5.将redo log buffer中的数据刷入到redo log文件中
6.将本次操作记录写入到bin log文件中
7.将bin log文件名字和更新内容在bin log中的位置记录到redo log中，同时在redo log最后添加commit标记
https://zhuanlan.zhihu.com/p/27700617 B树、B+树、B*树
事务指的是满足ACID特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。
A原子性(atomicity) 指的是一个事务中的操作要么全部成功，要么全部失败。
C一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
I隔离性(isolation) 指的是一个事务的修改在最终提交前，对其他事务是不可见的。
D持久性(durability) 指的是一旦事务提交，所做的修改就会永久保存到数据库中。
（2）数据库中并发一致性问题？
在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。
丢失修改：T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
读脏数据：T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读：T2读取一个数据，T1对该数据做了修改。如果 T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻影读：T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
（3）事务的隔离等级
未提交读：事务中的修改，即使没有提交，对其它事务也是可见的。
提交读：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
可重复读：保证在同一个事务中多次读取同样数据的结果是一样的。
可串行化：强制事务串行执行。
提交读可解决脏读问题
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
（4）ACID靠什么保证
A原子性(atomicity)：由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性(consistency) 一般由代码层面来保证（是由其他三大特征保证,程序代码要保证业务上的一致性）
I隔离性(isolation) 由MVCC多版本并发控制机制来保证
多版本并发控制：通过建立快照，维护版本链，进行多版本并发控制，决定当前快照读能查询到的版本，使得不同事务的读-写操作不会冲突（通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。）
D持久性(durability) 由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复
（通过redolog日志来保证。在我们进行增删改数据的时候，会先在redolog日志中保存。然后再将数据写入到磁盘。
因为修改数据是在磁盘中随机读写，而写入日志文件是顺序读写，顺序读写的效率要明显高于随机读写。因此，只要数据保存到了redolog中，即使之后读写磁盘的过程发生了故障，也不会造成数据的丢失。
例如：服务器意外宕机，innoDB可以根据redolog日志重新恢复到宕机前的状态，以此保证数据的完整性。）
（5）SQL优化的实践经验
1.对查询进行优化，尽量避免全表扫描。首先考虑在where及order by涉及的列上建立索引。
2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用100个字符的空间的，如果是varchar这样的变长字段，null不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
| select id from t where num = 0 |
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
| select id from t where num = 10union allselect id from t where Name = 'admin' |
对于连续的数值，能用between就不要用in了（between and是包括边界值的,not between不包括边界值）
很多时候用exists代替in是一个好的选择：
| select num from a where num in(select num from b) |
| select num from a where exists(select 1 from b where num=a.num) |
- 下面的查询也将导致全表扫描：
| select id from t where name like ‘%abc%’ |
全文搜索(Full-Text Search）是基于分词的文本检索功能，依赖于全文索引。全文索引不同于传统的平衡树（B-Tree）索引和列存储索引，它是由数据表构成的，称作倒转索引（Invert Index），存储分词和行的唯一键的映射关系。
- 如果在 where 子句中使用参数，也会导致全表扫描。可以改为强制查询使用索引
| select id from t where num = @num |
| select id from t with(index(索引名)) where num = @num |
- 应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
| select id from t where num/2 = 100 |
| select id from t where num = 100*2 |
- 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
| select id from t where substring(name,1,3) = ’abc’ |
| select id from t where name like 'abc%' |
- 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
- 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
- 不要写一些没有意义的查询
- Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
- 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
- select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
- 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
- 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
- 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
- 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
- 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。
- 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
- 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table，然后drop table ，这样可以避免系统表的较长时间锁定。
2.TRUNCATE TABLE 只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL Data Definition Language)，执行后不能撤销。
3.DELETE TABLE 删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以撤销。
- 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
- 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
- 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
- 在所有存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。
- 尽量避免大事务操作，提高系统并发能力。
- 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
（6）Buffer Pool、Redo Log Buffer和undo log、redo log、bin log概念以及关系
Buffer Pool是MySQL的一个非常重要的组件，因为针对数据库的增删改操作都是在Buffer Pool中完成的。
Undo log 记录的是数据操作前的样子。 原子性
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）。持久性
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）。
（7）从准备更新一条数据到事务的提交流程描述
1.首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
2.在数据被缓存到缓存池的同时，会写入undo log日志文件
3.更新动作是在BufferPool中完成的，同时会将更新后的数据添加到redo log buffer中
4.完成以后就可以提交事务，在提交的同时会做以下三件事：
5.将redo log buffer中的数据刷入到redo log文件中
6.将本次操作记录写入到bin log文件中
7.将bin log文件名字和更新内容在bin log中的位置记录到redo log中，同时在redo log最后添加commit标记
https://zhuanlan.zhihu.com/p/27700617 B树、B+树、B*树
