MySQL总结

Mysql的三大范式

第一范式：每个列都不可以再拆分。保证每列的原子性

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。也就是保证一张表只做一件事情

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键，说白了就是，决定某字段值的必须是主键

InnoDb和MyISAM的对比

这两个都是MySQL常见的存储引擎，InnoDb是MySQL默认的存储引擎，区别如下:

是否支持事务和崩溃恢复：InnoDb支持事务，而MyISAM不支持。 MyISAM 强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持。InnoDB 提供事务支持事务, 具有事务、回滚和崩溃修复的能力。
是否支持行锁：InnoDb支持行锁，MyISAM只支持表锁，意味着InnoDb适合高并发场景。
是否支持外键：MyISAM不支持，InnoDB支持。
是否支持MVCC：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效，MVCC只在 读已提交 和可重复读两个隔离级别下工作。
InnoDb不保存表的具体行数（只是一个估计值），因为InnoDb有事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询，而MyISAM保存精准的行数。
InnoDB是聚簇索引，默认使用B+树作为索引结构，数据文件是和索引绑在一起的，所以必须拥有主键。MyISAM是非聚簇索引，可以没有主键，也使用B+树作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。

一些重要的字符集

ASCII字符集：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共128个字符，所以可以使用1个字节来进行编码，是常用的字符编码方式。
ISO 8859-1字符集：共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。
GB2312字符集：收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其它文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：

如果该字符在ASCII字符集中，则采用1字节编码。
否则采用2字节编码。

GBK字符集：GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。
Unicode字符集：收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。（utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。）

字符集名称	Maxlen
ASCII	1
ISO 8859-1	1
gb2312	2
gbk	2
utf8	3
utf8mb4	4

聚簇索引和非聚簇索引的区别

聚簇索引：索引和数据存放在一起，索引的叶子节点保存的是具体的行数据。
非聚簇索引：索引和数据是分离的，索引的叶子节点保存的是实际数据记录的地址。
聚簇索引的优点：

聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引对于范围查询的效率很高，因为其数据是排序的。

聚簇索引的缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

B树索引和B+树索引的区别

B树的所有节点既存放键(key) ，也存放数据data；而B+树只有叶子节点同时存放 key 和 data，非叶子节点只存放key。这就导致相同的数据量，B树要比B+树要高。
B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点，形成一条链表，有助于范围查询。
B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了；而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

数据库事务及四个特性

事务其实是一系列数据库操作，为了保证操作的原子性，这些操作要么都被执行，要么都不执行。
四个特性（ACID）：
1.原子性（Atomicity）：事务中的所有操作，要么全部执行成功，要么都失败回滚，即回到该事务执行之前的状态。
2.隔离性（Isolation）：事务间的数据库操作互不影响，比如说：A向B同时进行两次金额为5元的转账，可以看成两个事务，每个事务都由读取A账户余额到内存中 --->减去转账金额 ---> 修改后的余额写到磁盘 --->读取B账户余额到内存中 --->加上转账金额 ---> 修改后的余额写到磁盘。假如事务T1的read（A）操作读完之后，紧接着是事务T2的read（A），那么就会出现问题，因为按理说应该是T1事务结束后，A账户少5元，B账户多5元，在执行T2，但是若如上面两个事务穿插着执行，那么最后的结果有可能是A只少了5元，而B多了十元。这不符合现实场景。所以多个事务并发执行时，一个事务的执行不应影响其它事务的执行，这个规则被称之为隔离性。
3.一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。比如 A 和 B 加起来的钱一共是 1000 元，那么不管 A 和 B 之间如何转账，转多少次，事务结束后两个用户的钱加起来还得是 1000，这就是事务的一致性。
4.持久性（Durability）：一旦事务提交，则对其数据库的修改应该永久保存在数据库中。即使数据库遇到特殊情况比如故障的时候也不会产生干扰。

MySQL 的隔离级别有哪些

READ UNCOMMITTED：未提交读，一个事务在提交之前，它所做的修改就能够被其它事务所看到，所以允许发生脏读、不可重复读和幻读。
READ COMMITTED：已提交读，一个事务在提交之后，它所做的变更才能够让其它事务看到，所以允许发生不可重复读和幻读问题，但是不允许发生脏读。
REPEATABLE READ：可重复读，一个事务在执行的过程中，看到的数据是和启动时看到的数据是一致的，所以允许发生幻读问题，但是不允许发生脏读和不可重复读（目前InnoDb可以通过MVCC在可重复读的情况下也能避免幻读）。
SERIALIZABLE：可串行化，最高的隔离级别，它通过强制事务串行执行，避免了幻读的问题。对于同一行记录，写会加写锁，读会加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。在这种隔离级别下，各种不允许任何并发问题的发生。

MySQL的默认隔离级别为可重复读。

事务隔离级别	脏读	不可重复读	幻读
未提交读	√	√	√
已提交读	×	√	√
可重复读	×	×	√
可串行化	×	×	×

脏读、幻读和不可重复读

脏读：某个事务修改了一条数据，该事务还未提交，另一个事务此时读取这条修改后的数据，所以该事务读取到了另一个事务的脏数据。
不可重复读：一个事务范围内，多次查询某个数据，却得到不同的结果。
幻读：一个事务根据某些条件查询出一个范围的记录，之后另一个事务又向这个范围中插入某条记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读。
区别：不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或删除，比如多次读取一条记录发现记录增多或减少了。

为什么一条SQL执行的很慢

大多数情况下很正常，偶尔很慢，则有如下原因：
1. 数据库在刷新脏页，同步到磁盘。
  1. redo log写满了
  2. 内存不够用了
2. 执行的时候，遇到锁，如表锁、行锁。可以用 show processlist这个命令来查看当前的状态。
这条 SQL 语句一直执行的很慢，则有如下原因。
1. 没有用上索引
  1. 例如该字段没有索引；
  2. 由于对字段进行运算、函数操作导致无法用索引。
2. 数据库选错了索引
  - 系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。所以采样不合适，就会导致数据库判断错误，选错索引。

为什么要使用索引

可以大大加快数据的检索速度（大大减少的检索的数据量）, 将随机I/O变为顺序I/O，这也是创建索引的最主要的原因。
帮助服务器避免排序和临时表。
可以加速表和表之间的连接，特别是在实现数据的参照完整性方面特别有意义。

为什么不对表中每个字段都创建索引。

对于每个索引都需要建立一颗B+树，每个字段都建索引会加大磁盘空间的成本，也会增加数据库服务器写入操作的成本。而且每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引，增加了时间成本。其次，比如我们查询一条记录，MySQL的查询优化器会分别计算用所涉及的的索引来查询这条语句所带来的成本，经过比较，选择成本最小的索引来执行数据库操作。所以索引越多，那么查询优化器所要做的工作（选择时间）就越多。

索引失效的几种情况。

条件中有or；
where中索引列有数学运算或使用了函数；
模糊查询like以%开头；
联合索引未用最左列字段；
存在索引列的数据类型隐形转换；
如果mysql觉得全表扫描更快时（数据少）；

什么是覆盖索引。

为了彻底告别回表操作带来的性能损耗，在查询列表里只包含索引列，这样的方式叫覆盖索引。也就是说SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

有个表叫person_info，其中有个联合索引索引 idx_name_birthday_phone_number (name, birthday, phone_number)

SELECT name, birthday, phone_number FROM person_info WHERE name > 'lisi' AND name < 'Zhangsan'

因为我们只查询name, birthday, phone_number这三个索引列的值，所以在通过idx_name_birthday_phone_number索引得到结果后就不必到聚簇索引中再查找记录的剩余列，这样就省去了回表操作带来的性能损耗。

什么是最左前缀原则。

当建立一个联合索引时，比如上面说的idx_name_birthday_phone_number，当你想要使用这个索引时，你的where里面必须按照从左到右的顺序写查询条件：
比如：

SELECT * FROM person_info WHERE name = 'harvey'
SELECT * FROM person_info WHERE name = 'harvey' and age = 18
SELECT * FROM person_info WHERE name = 'harvey' and age = 18 and phone_number = '120110119114'

当然
SELECT * FROM person_info WHERE age = 18 and name = 'harvey'
SELECT * FROM person_info WHERE phone_number = '120110119114' and age = 18 and name = 'harvey'

这两个也用到了索引，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

上面这些方式都可以用到该索引，因为它符合最左前缀原则。

SELECT * FROM person_info WHERE age=18
SELECT * FROM person_info WHERE phone_number = '13837981231' and age=18

像上面这种就不能使用该索引，因为它没有用到最左列。

索引下推

举个例子：对于user_table表，我们现在有（username,age）这个联合索引，如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，如下：

select * from user_table where username like '张%' and age <= 10

该查询有两种执行可能：

根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。
根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

很明显，第二种方式回表的记录要少很多，所以InnoDb优先采用这种方式来查询数据，这种方式就是索引下推。MySQL是默认启动索引下推的。

注：

InnoDb的表，索引下推只能用于二级索引。
索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

为什么 B+树更适合做索引

Hash：虽然精确查询的速度很快，但是缺点是不支持范围查询。

AVL 树：树高太高，查询的成本就会随着树高的增加而增加。

B树：B树相对于AVL树来说树高降低了，磁盘IO效率提高了。但是范围查询能力不强。

Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

Mysql的锁

按类别：

共享锁：又叫做读锁
排他锁：又叫做写锁

按粒度：

行锁：行锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行加锁。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。
表锁：表锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁。特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度低。

按手段：

悲观锁：假定一定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
- 实现方式：使用数据库中的锁机制。（select status from t_goods where id=1 for update;）
- 使用场景：比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过表中添加版本号的方式来进行锁定。
- 实现方式：使用版本号机制或CAS算法实现。（update table set x=x+1, version=version+1 where id=#{id} and version=#{version};）
- 使用场景：比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

InnoDB的行锁如何实现：

InnoDB是基于索引来完成行锁，for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起。

例: select * from tab_with_index where id = 1 for update;

InnoDB的行锁的算法：

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

注：

innodb对于行的查询使用next-key lock
当查询的索引含有唯一属性时，将next-key lock降级为record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

形成数据库死锁的原因、如何能避免死锁。

系统资源不足
进程运行推进的顺序不合适
资源分配不当

互斥条件：一个资源每次只能被一个进程使用。
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。
循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
避免死锁方法：
下列方法有助于最大限度地降低死锁：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
使用低隔离级别。

日志系统

binlog
binlog是mysql自带的日志，它记录了数据库表结构和数据的变更，主要用来做主从复制和数据恢复的。比如说要保证一个主服务器和多个从服务器中的mysql数据库保证一致，可以用binlog来实现。如果数据库的数据不小心被清了，也可以用binlog来恢复。还有就是binlog中记录的是数据的逻辑变化，记录格式有三种：

statement格式：记录的就是SQL语句的原文。缺点：会造成主从不一致。
row格式：记录的是两行记录，一个是修改前的，一个是修改后的。缺点：占内存。
mixed格式：折中方案，MySQL 自己会判断这条 SQL 语句是否可能引起主从不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

写入机制：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。接下来会涉及两个操作：write和fsync，write是指写到磁盘的缓存里，但是还没有持久化，fsync才是将数据持久化磁盘的操作。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

redo log
redo log是InnoDb特有的一种日志，它记录的也是对数据库的修改操作，不过日志里记载的是数据的物理变化，比如说在哪一页修改了什么内容。redo log的作用是可以保证内存中数据的持久化，数据写完内存，如果数据库发生异常重启了，那我们可以通过redo log恢复那些还没有来得及刷盘的数据，主要作用就是用来进行崩溃恢复的。所以redo log具有crash-safe的能力。

写入机制：事务执行过程中，先把日志写到 redo log buffer，事务提交的时候，再把 redo log buffer 写到 redo log 文件中。为了控制 redo log 的写入策略，InnoDB 提供了innodb_flush_log_at_trx_commit参数：

innodb_flush_log_at_trx_commit = 0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中；
innodb_flush_log_at_trx_commit = 1：表示每次事务提交时都将 redo log 直接持久化到磁盘；
innodb_flush_log_at_trx_commit = 2：表示每次事务提交时都只是把 redo log 写到 page cache。（InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。）

注：通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务提交时，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

两阶段提交：

阶段1：redo log 先写盘，这时候redo log处于prepare状态。然后告知执行器执行完成了，可以随时提交事务。
阶段2：执行器开始对binlog进行写盘，binlog持久化成功后，把redo log的状态改为commit。

两阶段提交的目的是为了让两份日志之间的逻辑一致。

undo log
undo log主要有两个作用：事务回滚和MVCC。在一个事务中，我们对数据进行了修改，undo log中就会记录一条相反操作的记录，如果事务没能提交成功，我们就使用undo log回滚事务的操作，保证事务的原子性。

MVCC

MVCC是多版本并发控制，也是行锁的一个变种，它能够避免加锁的操作，所以开销更小。简单来讲就是对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，形成一条版本链，这样查询一条数据的时候，事务可以根据隔离级别选择要读取哪个版本的数据，使得查询时可以完全不加锁。（所以说它的实现是通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。）从用户的角度来看，好像是数据库可以提供同一数据的多个版本。在执行普通的SELECT操作时访问记录的版本链，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

并且MVCC只能在可重复读和可提交读的隔离级别下生效。（未提交读总是读取最新的行版本。可串行化会对所有读取的行加锁。）

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

如何分析Mysql慢SQL

开启慢查询日志捕获慢SQL
1. 查询mysql是否开启慢日志查询：show variables like '%slow_query_log%'，如果没有开启，开启：set global show_query_log=1
2. 查看慢查询的时间阈值：show global variables like '%long_query_time%'；可以根据实际情况去调整时间：set global long_query_time=2;
3. 查询多少SQL超过了慢查询时间的阈值： show global status like '%Slow_queries%'；
4. 使用MySQL的mysqldumpslow命令，获取差慢SQL

使用explain分析慢SQL：一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，而我们可以用EXPLAIN来查看执行计划。

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值。
rows	根据表统计信息及索引选用情况，大致估算出找到所需记录多需要读取的行数。
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息 Using filesort/Using temporary/Using index/impossible where/distinct

使用show profile查询SQL执行细节：show profiles是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量
1. 查看状态：show variables like 'profiling';
2. 开启：set profiling=on;
3. 查看结果：show profiles;
4. 诊断SQL：show profile cpu,block io for query 上一步SQL数字号码;
常见的SQL优化
1. 尽量避免在where字句中使用or来连接条件，否则将导致放弃使用索引而进行全表扫描。
2. 正确使用like查询。
3. 尽量避免在where字句中对字段进行表达式操作
4. 如果确认查询结果数量，应尽可能加上limit
5. 不用要使用隐式转换
6. 正确使用联合索引
7. 如果使用了join，请尽量使用小表join大表
8. 正确使用exists和in