一、基础

1.引擎

MySQL支持哪些存储引擎？他们的特点是什么？

InnoDB 支持事务，行级锁定和外键，是事务型数据库的首选引擎；MySQL5.5.5 之后的默认存储引擎；
MyISAM 拥有较高的插入、查询速度，但不支持事务。MySQL5.5.5 之前的默认存储引擎；
Memory 基于散列，存储在内存中，对临时表有用。常见的应用场景是：临时存放数据，数据量不大，不需要较高的数据安全性；
Archive 支持高并发的插入操作，但是本身不是事务安全的。常见的应用场景：存储归档数据，如记录日志信息可以使用 Archive。

InnoDB 和 MyISAM的区别：

InnoDB 支持事务；而 MyISAM 不支持事物，强调的是性能，查询速度更快；

InnoDB 支持行级锁和表级锁（默认行级锁），而 MyISAM 只支持表级锁；

InnoDB 支持 MVCC, 而 MyISAM 不支持 MVCC；

InnoDB 支持外键，而 MyISAM 不支持外键；

InnoDB 早期版本不支持全文索引（从 MySQL5.6 开始支持全文索引），而 MyISAM 支持；

InnoDB 不保存表的具体行数，count () 时要扫描一遍整个表来计算有多少行；MyISAM 则内置了一个计数器，count () 时它直接从计数器中读。

一张表里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把MySQL重启，再insert一条记录，这条记录的ID是18还是15 ？InnoDB和MylSAM结果有什么不同，为什么？

InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是18。重启MySQL，这条记录的ID是15。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
MylSAM，这条记录的ID就是18。因为MylSAM表会把自增主键最大ID记录到数据文件里面，重启MYSQL也不会丢失。
如果在这17条记录里面删除的是中间的几个记录（比如删除的是10、11、12三条记录）重启MySQL后insert一条记录后，ID都是18。因为内存或者数据库文件存储都是自增主键最大ID。
MySQL8.0及以上版本这条记录的ID是18，因为这个版本保存ID的值是在redo日志中的，重启之后是可以恢复的。

2.索引（InnoDB）

索引的数据结构是什么？

B+树 图片描述

M为什么采用B+树？不用B树？

索引文件很大，不能全部存储在内存中，只能存储到磁盘上，因此索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数；
数据库系统利用了磁盘预读原理和磁盘预读，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。而B+树的高度是2~4，叶子节点的高度不会超过1，检索一次最多只需要访问4个节点（4 次，即树的高度）。

B+树索引并不能直接找到具体的行，只是找到被查找行所在的页，然后 DB 通过把整页读入内存（磁盘预读），再在内存中查找（局部性原理，即当一个数据被用到时，其附近的数据也通常会马上被使用）。
B+树所有的Data域在叶子节点，其余节点用来索引，而B树是每个索引节点都会有Data域；并且B+树所有叶子节点之间都有一个链指针。这样遍历叶子节点就能获得全部数据，从而支持区域查询。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B树： 图片描述

MySQL支持的索引类型是哪些？

普通索引：用表中的普通列构建的索引，没有任何限制；
唯一索引：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
主键索引：是一种特殊的唯一索引，根据主键建立索引，不允许重复，不允许空值；
全文索引：通过过建立倒排索引，快速匹配文档的方式。MySQL 5.7.6 之前仅支持英文，MySQL 5.7.6 之后支持中文；
组合索引：又叫联合索引。用多个列组合构建的索引，不允许有空值。可以在创建表的时候指定，也可以修改表结构。

聚集索引 (clustered index)，又称为主索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。因为数据真正的数据只能有一种排序方式，所以一个表上只能有一个聚簇索引。

非聚集索引 (secondary index)，又称为辅助索引、普通索引，该索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表可以包含多个非聚集索引。

聚集索引 / 非聚集索引不是一种索引类型，而是一种存储数据的方式。在 InnoDB 中它们还有一个非常重要的区别：聚集索引的叶子节点的的 data 域包含了完整的数据记录，而非聚集索引的叶子节点的data域记录着主键的值。因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找，这称之为回表查询。

什么情况下索引会失效？

条件中有 or；
like查询（以%开头）；
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引；
对列进行函数运算（如 where md5 (password) = “xxxx”）；
负向查询条件会导致无法使用索引，比如 NOT IN、NOT LIKE、!=、>、< 等；
对于联合索引，不是使用的第一部分 (第一个)，则不会使用索引（最左匹配）；
如果mysql评估使用全表扫描要比使用索引快，则不使用索引；

联合索引为什么要遵循最左匹配原则？

对字段(a, b)创建联合索引，其存储结构如下：可以看出联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后再对第二个字段进行排序，所以如果不符合最左匹配原则，就无法找到对应的索引值。

你平时是怎样进行索引优化的？

将经常被查询的区分度高的列做索引，区分度控制到20%-40%左右；
最左原则
回盘顺序，将order by的列与where的列建立联合索引，这样在查询出where的数据之后可以直接进行order by排序，不需要再根据主键值查询order by数据；
覆盖索引
简单来说，就是where的列和select的列一致，不需要回表查询，通过索引就可以查出所有的数据；

不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。文章上面的内容已经指出，当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。
```
//建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1';
```
这句SQL语句的执行过程如下:

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值；

　然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置；

　最后，从得到的真实数据中取得user_name字段的值返回，也就是取得最终的结果。

　我们把birthday字段上的索引改成双字段的覆盖索引。
```
create index index_birthday_and_user_name on user_info(birthday, user_name);
```
这句SQL语句的执行过程就会变为:

　　通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面，因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大的提高了查询性能。
小表驱动大表

二、优化

数据库调优一般从哪几个角度去操作？

查询优化

索引类型	数据量	查询时间
主键查询	千万	1-10ms
唯一索引	千万	10-100ms
非唯一索引	千万	100-1000ms
无索引	百万	1000ms+

批量写优化
- 尽量使用Execute once insert into table values (1),(2),(3),(4)...;
- SQL编译N次和1次的时间与空间复杂度
- 网络消耗的时间复杂度
- 磁盘寻址的复杂度
索引优化
Innodb相关优化

max_connection=1000 增大最大连接数，默认为100

innodb_file_per_table=1 可以存储每个innodb表和他的索引在自己的文件中，将表之间的索引数据隔离

innodb_buffer_pool_size=1G 缓存池大小，设置为当前数据库服务内存的60%-80%

innodb_log_file_size=256M 一般取256M可以兼顾性能和recovery的速度，写满后只能切换日志靠buffer存储

innodb_log_buffer_size=16M 该参数确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前可以继续MySQL事务操作

innodb_flush_log_at_trx_commit=2

1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。

2时，在每个事务提交时，日志缓冲被写到系统缓冲，但不对日志文件做到磁盘操作的刷新。然后根据innodb_flush_log_at_timeout（默认为1s）时间flush disk只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。

0时，效率更高，但安全性差。每秒才write日志任何mysqld进程的崩溃会删除崩溃前最后一秒的事务。

innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend 指定表数据和索引存储的空间，可以是一个或者多个文件。

日志缓冲：跟着mysqld进程走，进程崩溃会丢失数据；

系统缓冲：跟着操作系统走，操作系统崩溃会丢失数据；
读写分离

一主多从：事务提交后，master记录binlog，slave监听binlog，同步数据；

读库延迟问题处理：应用层让步；强行读master；

主从切换处理：只有当slave都同步完之后再返回数据，或者完成1/2的slave后返回；
分库分表

垂直拆分：根据不同的业务进行拆分的，拆分成不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等；

水平拆分：把同一张表中的数据拆分到不同的数据库中进行存储，或把一张表拆分成 n 多张小表；

多主多从：保证可用性

1.SQL调优

平时是怎样分析SQL的？

使用explain关键字

索引调优有几种类型？分别什么意思？至少要优化到什么级别？

system：仅一行
const: 主键or唯一键的常量等值查询
eq_ref：主键or唯一键的扫描或关联查询
ref：非唯一索引的常量等值查询
range：索引的范围查询
index：索引全查询
all：遍历表查询

从上到下效率递减

优化至少要到range范围

explain结果字段的意思是什么？

id：SQL执行的顺序的标识，SQL从大到小的执行；
select_type：查询中每个select子句的类型；

SIMPLE(简单SELECT,不使用UNION或子查询等)

PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

UNION(UNION中的第二个或后面的SELECT语句)

DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

UNION RESULT(UNION的结果)

SUBQUERY(子查询中的第一个SELECT)

DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

DERIVED(派生表的SELECT, FROM子句的子查询)

UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table：数据属于的表，有事不是表名，看到的是derivedx(x是个数字，是第几步执行的结果)；
type：MySQL在表中找到所需行的方式，又称访问类型；

常用类型： ALL/index/range/ref/eq_ref/const/system/NULL（从左到右，性能从差到好）
possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。
key：key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra：该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”。

Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

2.结构调优

如何进行分库分表？

主要有垂直分区、垂直分表和水平分区、水平分表。

垂直分区：数据表列的拆分，把一张列比较多的表拆分为多张表。

在这里插入图片描述

优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

垂直分表：把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

在这里插入图片描述

优点：按照列热度区分，可以减少某些不常用列的读取，可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数。

缺点：有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差。对于应用层来说，逻辑算法增加开发成本。管理冗余列，查询所有数据需要join操作。

水平分区：保持数据表结构不变，通过某种策略存储数据分片。每一片数据分散到不同的表或者库中，达到了分布式的目的。

在这里插入图片描述

优点：支持非常大的数据量存储，应用端改造也少。缺点：分片事务难以解决，跨节点Join性能较差，逻辑复杂。

水平分表：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

在这里插入图片描述

优点：适合表中的数据本身就有独立性的，例如表中分表记录各个地区的数据或者不同时期的数据，可以区分有些数据常用，有些不常用。并且可以根据需要把数据存放在多个介质上。

缺点：给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作。在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。

数据库分片的常见方案？

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

为什么有的库指定ID不是1、2、3自增的，而是1、3、5，指定都为奇数或者都为偶数？

可能是为了预防双主双写场景下，ID冲突，如下图，张三数据路由到M1库，李四数据路由到M2库，因为要保持双主数据一致，所以需要进行同步，如果自增ID为1、2、3自增，就可能会造成ID冲突，同步失败的情况。

三、事务

1.事务特性

Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

原子性：要么做要么不做

一致性：做完前后数据是正确的，完整的

隔离性：自己做自己的

持久性：做了有记录

2.隔离机制

什么是脏读？幻读？不可重复读？

脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读:是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作第一个事务的用户就会发现表中还有没有修改的数据行。

描述一下事务的几种隔离机制？

Read Uncommitted（读未提交）：事务可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。而且可能出现脏读（Dirty Read），即一个事务读取到另一个事务还有没提交的记录。
Read Committed（读提交） ：事务只能看见已经提交的事务所做的改变。这种隔离级别可能导致不可重复读（Nonrepeatable Read），即同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一个查询操作执行两次或多次的结果不一致。
Repeatable Read（可重复读） ，事务的多个实例在并发读取数据时读到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（PhantomRead），它是指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的 “幻影” 数据。
Serializable（串行化） ：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

两个事务做如下操作，id 是表 t 的主键，num初始值为3，那么事务 2 的 update 后再 select 的值是多少呢？

图片描述

值为6。若引擎为InnoDB，琐是默认加在索引上的，如果id存在索引（主键有唯一索引，否则锁表），则事务1的两个update操作会将对应的数据锁住，其他事务无法修改（但是可以查询，因为读的是快照），事务2执行update会阻塞并等待事务1释放对应的行锁，等事务1commit之后释放锁，事务2再继续执行，所以最终结果为6。

/* 事务1：*/ update t set num = num + 1 where id = 1;/* 添加行锁 此时事务中 num = 4,表中依旧是3 */
/* 事务2：*/ update t set num = num + 2 where id = 1;/* 无法执行，一直等待，因为数据被事务1锁住，超时后抛出 */
/* 事务1：*/ commit;/* 释放锁 此时num = 4被更新到表中，num = 4 */
/* 事务2：*/ commit;/* 获取到锁，执行语句后返回，此时执行num = num + 2，即4 + 2 = 6，并更新到表中 */

什么是快照读？什么是当前读？

MySQL的事务是基于MVCC（多版本并发控制）实现的。

快照读： 简单的select操作，属于快照读，不加锁。每次查询语句时会先标记当前更新数据的版本，作为快照，然后返回数据，如果不用update、delete、for update之类的语句，例如select，在更新操作事务提交之前，读的都是老版本的快照；

当前读： 读取的是最新版本，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。对于update、delete、for update之类的写操作，其他语句会被阻塞，直到操作事务提交。

什么是行锁？表锁？间隙锁（区间锁）？

行锁是粒度最细的锁，是以索引为基础进行加锁，如果没有索引，则会变成表锁，导致锁定整张表；
间隙锁一般发生在一般索引上，例如一般索引列A中有数据5和10，在进行当前读的时候，where条件为A=6，因为找不到为6的数据，所以锁定(5,10]，这个左开右闭的区间数据，当再insert之间的数据的时候就会被阻塞，但是对于其他没有加锁的区间没有影响；

步骤事务A 事务B
1 begin; select * from t where id = 6 for update; -
2 - insert into user value(12,12,12);阻塞
3 commit; -

当有如下事务A和事务B时，事务A会对数据库表增加(5,10]这个区间锁，这时对应区间数据的时候就会因为区间锁(5,10]而被锁住无法执行。
对于普通索引的当前读操作，不仅会对等值的行加上行锁，还会在上下区间加上区间锁，例如列A有数据5、10、15，当对10进行当前读，则会锁定(5,15)之间所有的数据，主要是为了防止幻读的产生，导致查询数量的变化；

步骤	事务A	事务B
1	begin; select * from t where id = 6 for update;	-
2	-	insert into user value(12,12,12);阻塞
3	commit;	-