4 事务 1、什么是数据库的事务？ 数据库的事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务的典型应用场景，如转账。
2、什么是事务的四大特性（ACID）？ 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用 一致性： 事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。 4、事务的并发问题？ 脏读、幻读和不可重复读。
ref 并发事务带来的问题
5、什么是脏读、幻读和不可重复度？ 脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。
幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
ref MySQL的四种事务隔离级别
6、事务的隔离级别有哪些？
串行化的隔离级别最高，读未提交的级别最低，级别越高，则执行效率就越低，所以在选择隔离级别时应该结合实际情况。
MySQL 支持以上四种隔离级别，默认为 Repeatable read (可重复读)；而 Oracle 只支持 Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。
ref 事务的隔离级别
7、ACID 特性是如何实现的？ 分四个维度去理解，如原子性是 undo 日志，持久性是 redo 日志。（PS 日志具体原理在后续章节讲述。）
ref ACID特性的实现原理
5 锁 1、数据库锁的作用以及有哪些锁？ 当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。即锁的作用是解决并发问题。
从锁的粒度划分，可以将锁分为表锁、行锁以及页锁。
行级锁：是锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。 行级锁开销大，加锁慢，且会出现死锁。但锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁：是粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。
页级锁：是粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。
开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。
从使用性质划分，可以分为共享锁、排它锁以及更新锁。
共享锁（Share Lock）：S 锁，又称读锁，用于所有的只读数据操作。 S 锁并非独占，允许多个并发事务对同一资源加锁，但加 S 锁的同时不允许加 X 锁，即资源不能被修改。S 锁通常读取结束后立即释放，无需等待事务结束。
排他锁（Exclusive Lock）：X 锁，又称写锁，表示对数据进行写操作。 X 锁仅允许一个事务对同一资源加锁，且直到事务结束才释放，其他任何事务必须等到 X 锁被释放才能对该页进行访问。
使用 select * from table_name for update; 语句产生 X 锁。
更新锁：U 锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。 当被读取的页将要被更新时，则升级为 X 锁，U 锁一直到事务结束时才能被释放。故 U 锁用来避免使用共享锁造成的死锁现象。
ref 数据库锁分类和总结
从主观上划分，又可以分为乐观锁和悲观锁。
乐观锁（Optimistic Lock）：顾名思义，从主观上认定资源是不会被修改的，所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否被修改。 乐观锁适用于多读的应用类型，可以系统提高吞吐量。
悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排它特性，每次读取数据时都会认为会被其它事务修改，所以每次操作都需要加上锁。 2、隔离级别和锁的关系？ 1）在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；
2）在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
3）在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；
4）在 SERIALIZABLE 级别下，限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。
3、InnoDB 中的锁算法？ Record lock：单个行记录上的锁 Gap lock：间隙锁，锁定一个范围，不包括记录本身 Next-key lock：record + gap 锁定一个范围，包含记录本身 ref 锁机制与InnoDB锁算法
4、什么是快照读和当前读？ 快照读就是读取的是快照数据，不加锁的简单 Select 都属于快照读。
SELECT * FROM player WHERE ... 1 当前读就是读的是最新数据，而不是历史的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读。
SELECT * FROM player LOCK IN SHARE MODE; SELECT FROM player FOR UPDATE; INSERT INTO player values ... DELETE FROM player WHERE ... UPDATE player SET ... 1 2 3 4 5 5、什么是 MVCC 以及实现？ MVCC 的英文全称是 Multiversion Concurrency Control，中文意思是多版本并发控制，可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率。
其原理是通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。
ref MVCC 原理 ref MVCC详解
6 进阶功能 6.1 视图 6.2 存储过程 1、什么是存储过程？ 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
2、存储过程和函数的区别？ 1）返回值的区别：函数有1个返回值，而存储过程是通过参数返回的，可以有多个或者没有。
2）调用的区别：，函数可以在查询语句中直接调用，而存储过程必须单独调用。
ref 存储过程（procedure）和函数（Function）的区别
6.3 触发器 7、集群 7.1 日志 1、MySQL 中有哪些常见日志？ 重做日志（redo log）：物理日志 作用是确保事务的持久性。 redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已提交事务数据。
回滚日志（undo log）：逻辑日志 作用是保证数据的原子性。 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
二进制日志（binlog）：逻辑日志 常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。
错误日志（errorlog） 记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
普通查询日志（general query log） 记录了服务器接收到的每一个命令，无论命令语句是否正确，因此会带来不小开销，所以也是默认关闭的。
慢查询日志（slow query log） 记录执行时间过长和没有使用索引的查询语句（默认 10s），同时只会记录执行成功的语句。
中继日志（relay log） 在从节点中存储接收到的 binlog 日志内容，用于主从同步。
ref MySQL中的几种日志了解
7.2 主从复制 1、什么是主从复制？ 主从复制是用来建立一个与主数据库完全一样的数据库环境，即从数据库。主数据库一般是准实时的业务数据库。
2、主从复制的作用？ 读写分离，使数据库能支撑更大的并发。 高可用，做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。 3、主从复制的架构？ 一主一从或一主多从 在主库的请求压力非常大时，可通过配置一主多从复制架构实现读写分离，把大量对实时性要求不是很高的请求通过负载均衡分发到多个从库上去读取数据，降低主库的读取压力。而且在主库出现宕机时，可将一个从库切换为主库继续提供服务。
主主复制 双主复制架构适用于需要进行主从切换的场景。 两个数据库互为主从，当主库宕机恢复后，由于它还是原来从库（现在主库）的从机，所以它还是会复制新的主库上的数据。那么无论主库的角色怎么切换，原来的主库都不会脱离复制环境。
多主一从（5.7 开始支持） 联级复制 因为每个从库在主库上都会有一个独立的 Binlog Dump 线程来推送 binlog 日志，所以随着从库数量的增加，主库的 IO 压力和网络压力也会随之增加，这时，联级复制架构应运而生。
联级复制架构只是在一主多从的基础上，再主库和各个从库之间增加了一个二级主库 Master2，这个二级主库仅仅用来将一级主库推送给它的 Binlog 日志再推送给各个从库，以此来减轻一级主库的推送压力。
4、主从复制的实现原理？ 数据库有个 binlog 二进制文件，记录了数据可执行的所有 SQL 语句。主从同步的目标就是把主数据库的 binlog 文件中的 SQL 语句复制到从数据库，让其在从数据的 relaylog 文件中再执行一次这些 SQL 语句即可。
具体实现需要三个线程：
binlog 输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送 binlog内 容到从库。 在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
从库 IO 线程：当 START SLAVE 语句在从库开始执行之后，从库创建一个 IO 线程，该线程连接到主库并请求主库发送 binlog 里面的更新记录到从库上。从库 IO 线程读取主库的 binlog 输出线程发送的更新并拷贝这些更新到本地文件，其中包括 relaylog 文件。
从库 SQL 线程：从库创建一个 SQL 线程，这个线程读取从库 IO 线程写到 relaylog 的更新事件并执行。
ref MySQL主从复制面试之和原理
5、什么是异步复制和半同步？ MySQL 的主从复制有两种复制方式，分别是异步复制和半同步复制：
异步复制 MySQL 默认的主从复制方式就是异步复制，因为 Master 根本不考虑数据是否达到了 Slave，或 Slave 是否成功执行。
如过需要实现完全同步方式，即 Master 需要等待一个或所有 Slave 执行成功后才响应成功，那集群效率可想而知。故 MySQL 5.6 之后出现了一种折中的方式——半同步。
半同步复制 一主一从，一主多从情况下，Master 节点只要确认至少有一个 Slave 接受到了事务，即可向发起请求的客户端返回执行成功的操作。同时 Master 是不需要等待 Slave 成功执行完这个事务，Slave 节点接受到这个事务，并成功写入到本地 relay 日志中就算成功。
另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那 MySQL 会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。
半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。
refMySQL主从复制（异步复制与半同步复制）
6、主从中常见问题以及解决？ 问题 1）主库宕机后，数据可能丢失。
2）从库只有一个sql Thread，主库写压力大，复制很可能延时。
解决 1）半同步复制：确保事务提交后 binlog 至少传输到一个从库 ，解决数据丢失的问题。
2）并行复制：从库多线程apply binlog，解决从库复制延迟的问题。
8 SQL 8.1 语法 SQL 是一门 ANSI 标准计算机语言，用来访问和操作数据库系统。通常 SQL 语句可以分为两类：
数据操作语言（DML）：SELECT、DELETE、INSERT INTO、UPDATE 数据定义语言（DDL）：CREATE、DROP、ALTER 实践中，还有一种
数据控制语言（Data Control Language）：GRANT，REVOKE，COMMIT，ROLLBACK 1、常见的聚合查询？ 使用聚合函数的查询就是聚合查询。所有的聚合函数（UDAF）都应该支持分组查询，内置的聚合函数有：
sum(列名) 求和
max(列名) 最大值
min(列名) 最小值
avg(列名) 平均值
first(列名) 第一条记录
last(列名) 最后一条记录
count(列名) 统计记录数 注意和count(*)的区别
1
2
3
4
5
6
7
ref SQL 聚合查询
2、几种关联查询？ 1） 内连接（自然连接）：只返回匹配的行，如 Inner Join、Union Join。
2）外连接：返回一个表的全集，如 Left、Right、Full 和 Cross。
ref SQL多表查询
3、Where 和 Having 的区别？ where 子句的作用是在对查询结果进行分组前，将不符合条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
总结一下条件的过滤顺序：on->join->where->group by->having。
4、SQL 关键字的执行顺序？
ref SQL的执行顺序
5、In 和 Exists 的区别？ in 适合内表比外表数据小的情况，exists 适合内表比外表数据大的情况。如果查询的内外表大小相当，则二者效率差别不大。
ref SQL语句中exists和in的区别
6、Union 和 Union All 的区别？ Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
Union All：对两个结果集进行并集操作，包括重复行，不进行排序
Union 因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。
ref union和union all的区别
7、Drop、Delete 和 Truncate 的区别？ 虽然通过 delete、truncate、drop 这三个关键字都可以用来删除数据，但场景不同。
从执行速度上讲：drop > truncate >> DELETE。
区别 Delete Truncate Drop SQL类型 属于DML 属于DDL 属于DDL 支持回滚 支持 不支持 不支持 删除内容 表结构还在，删除表的全部或部分数据 表结构还在，删除表中所有数据 从数据库中删除表的所有数据，包括索引和权限 执行速度 速度慢，需要逐行删除 速度快 速度最快 ref delete、truncate、drop的区别有哪些，该如何选择
8.2 优化 1、一条 SQL 是如何执行的？
ref sql执行过程
2、如何判断 SQL 是否走了索引？ EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法，使用 EXPLAIN 只需在查询语句开头增加 EXPLAIN 这个关键字即。
其结果中的几个重要参数：
id ID 代表执行 select 子句或操作表的顺序，如果包含子查询，则会出现多个 ID。值越大，优先级越高，越先被执行。值相同的按照由上至下的顺序执行。
select_type（查询类型） 查询类型主要用于区别普通查询、联合查询以及子查询等复杂查询。
table
type
查询扫描情况，最好到最差依次是：system>const>eq_ref>ref>range>index>All，一般情况下至少保证达到 range 级别，最好能达到 ref。
possible_keys 显示可能应用在这张表中的索引，一个或多个。查询到的索引不一定是真正被使用。
key 实际使用的索引，如果为 null 则表示没有使用索引。因此会出现 possible_keys 列有可能被用到的索引，但是 key 列为 null。
key_len 表示索引中使用的字节数，在不损失精确性的情况下长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。即 key_len 是根据表定义计算而来。
ref 显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值。
rows 根据表统计信息及索引选用情况，估算出找到所需的记录所需要读取的行数。
Extra ref EXPLAIN的参数解析及简单应用
3、索引失效的几种情况？ 1）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；
2）or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；
3）联合索引不使用第一列，索引失效；
4）数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；
5）在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。
6）在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
7）对索引字段进行计算操作、字段上使用函数。
8）当 MySQL 觉得全表扫描更快时（数据少）;
ref Mysql索引查询失效的情况
4、Where 子句如何优化？ 5、超大分页或深度分页如何处理？ 说道 MySQL 的分页，我们首先想到的就是 offset、limit 操作，但随着页数的增加，查询性能指数级增大。
这是由于 MySQL 并不是跳过 offset 的行数，而是取 offset + limit 行，然后丢弃前 offset 行，返回 limit 行，当offset特别大的时候，效率就非常的低下。
此处我们就可以采用覆盖索引+延迟关联技术来减少偏移量的定位进行优化：
##查询语句 select id from product limit 10000000, 10 ##优化方式一 SELECT * FROM product WHERE ID > =(select id from product limit 10000000, 1) limit 10 ##优化方式二 SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID = b.id 1 2 3 4 5 6 ref mysql优化：覆盖索引（延迟关联）
6、大表查询如何优化？ 可以从分库分表、读写分离以及缓存三个维度分别阐述。
