mysql体系架构

数据库三层架构

Mysql:是单进程多线程数据库。

1.连接层：最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信，处理客户端连接、授权认证等。

2.服务层：一些sql接口，解析器（parser），优化器（optimizer），缓存（caches或者buffers）

3.存储引擎，负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务，事务是由存储引擎实现的 。 MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。

mysql 的查询流程大致是：

1.mysql 客户端通过协议与 mysql 服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，将缓存中的结果返回给用户即可，这将大大提高系统的性能。

2.如果没有命中：解析，预处理，优化

2.1 语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树”。

2.2 mysql 解析器将使用 mysql 语法规则验证和解析查询，

2.3 预处理器则根据一些 mysql 规则进一步检查解析数是否合法。

2.4 优化器：优化器将解析树被认为是合法的sql，将其转化成执行计划。一条查询可以有很多种执行方式，优化器的作用就是找到这其中最好的执行计划。

MySQL内核：innodb存储引擎

innodb体系架构

2.1 后台线程

InnoDB存储引擎是多线程的,因此后台有多个后台线程负责不同的任务.

2.1.1 Master Thread

Master Thread是一个核心的后台线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据一致性,包括脏页刷新,合并插入缓冲等.

2.1.2 IO Thread

在InnoDB存储引擎中大量使用AIO来处理写IO请求,可以极大的提高数据库的性能. IO Thread主要负责这些IO请求的回调处理,共有4种IO Thread:wirte,read,insert buffer,log IO Thread.

2.1.3 Purge Thread

事务被提交后,其所使用的undo log(回滚日志)可能不再需要,因此需要Purge Thread来回收已经使用并分配的undo页.

在 1.1版本之前,purge操作仅在InnoDB的Master Thread中完成.而从1.1开始,purge操作可以独立到单独的线程中进行.

2.1.4 Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2版本中引入的,作用是将之前版本中脏页刷新的操作都放到单独的线程中来完成.

目的是为了减轻Master Thread的工作及用户查询线程的阻塞,进一步提高InnoDB存储引擎的性能.

2.2 内存

2.2.1 缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的系统通常使用缓存池技术来提供数据库的整体性能。

在数据库中进行读取页的操作，首先将从磁盘读到的页放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时，首页判断该页是否在缓存池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率（checkpoint机制）刷新到磁盘上。

缓冲池的大小直接影响着数据库的整体性能。通过命令show variables like 'innodb_buffer_pool_size'可以查看缓冲池的大小.

（1）预读

InnoDB在I/O的优化上有个比较重要的特性为预读(Read-Ahead)，它会异步地在缓冲池中提前读取多个预计很快就会用到的数据页。数据库请求数据的时候，会将读请求交给文件系统，放入请求队列中；相关进程从请求队列中将读请求取出，根据需求到相关数据区(内存、磁盘)读取数据；取出的数据，放入响应队列中，最后数据库就会从响应队列中将数据取走，完成一次数据读操作过程。接着进程继续处理请求队列，判断后面几个数据读请求的数据是否相邻，再根据自身系统IO带宽处理量，进行预读，进行读请求的合并处理，一次性读取多块数据放入响应队列中，再被数据库取走。

（2）两种算法

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

2.2.2 LRU List ，Free List 和 Flush List

LRU List(Latest Recent Used) InnoDB中缓冲池是一个很大的内存区域,其中存放着各种类型的页.通常来说,数据库中的缓冲池是通过LRU(最近最少使用)算法进行管理的.最频繁使用的页在LRU列表的前端 ,最少使用的页在LRU列表的尾端. 在InnoDB中缓冲池页大小默认是16KB,同样使用LRU算法进行管理,只不过在InnoDB中对传统LRU算法进行了优化.在LRU列表中加入了midpoint(中点)位置,即新读取到的页并不直接放到LRU列表首部,而是放到了列表的midpoint位置,这个算法称为midpoint insertion strategy,默认配置下midpoint位置在LRU列表长度的5/8处,通过参数innodb_old_blocks_pct控制.

通过命令看到默认值是37,表示新读取的也查到LRU列表尾端37%的位置.在InnoDB中把midpoint之后的列表称为old列表,之前的称为new列表. 同时InnoDB又引入参数innodb_old_blocks_time,表示页读取到midpoint位置后需要等待多久才会被加入到LRU列表的热端(new列表). 如果预估活跃的热点数据不止63%,可以通过sql语句来减少热点数据被刷出的概率.

Free List

当数据库刚启动时LRU列表是空的,没有任何页,这时的页都会放到Free列表中.当需要从缓冲池中分页时,首先从Free列表中查找是否有空闲页,如果有则将该页从Free列表中删除,放到LRU列表中.否则根据LRU算法淘汰LRU列表末尾页,将内存分配给新的页. 当页从LRU列表的old部分加入到new部分时,称此操作为page made young,因innodb_old_blocks_time设置而导致也没有从old移动到new的操作称为page not made young.可以通过命令来查看LRU列表和Free列表的使用和运行情况.

Flush list

在LRU列表中的页被修改后,称该页为脏页,即缓冲池中的页和磁盘上的页的数据产生了不一致.这时会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页列表. 在上面命令查看到的信息中,Modified db pages就显示了脏页的数量.

2.2.3 重做日志缓冲

InnoDB首先将重做日志信息放入这个缓冲区,然后按一定的频率将其刷新到重做日志文件. 重做日志缓冲一般不需要设置的很大,只需要保证每秒产生的事务量在这个缓冲大小之间即可. 通过innodb_log_buffer_size来控制,默认为8MB

2.2.4 change buffer （写缓冲）

在MySQL中数据分为内存和磁盘两个部分；在buffer pool中缓存热的数据页和索引页，减少磁盘读；通过change buffer就是为了缓解磁盘写的一种手段。

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果数据页不在内存中。在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上（ibdata）。

将 change buffer 中的操作合并到原数据页，得到最新结果的过程称为 merge。

以下情况会触发merge：

访问这个数据页；

后台master线程会定期 merge；

数据库缓冲池不够用时；

数据库正常关闭时；

redo log写满时；

mysql三大日志

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 mysql 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括redo log 和 undo log )。

binlog

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。 binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

逻辑日志：可以简单理解为记录的就是sql语句。
物理日志： mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog 是通过追加的方式进行写入的，可以通过 max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景：

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

主从复制 ：在 Master 端开启 binlog ，然后将 binlog 发送到各个 Slave 端， Slave 端重放binlog 从而达到主从数据一致。
数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED 。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW 。日志格式通过 binlog-format 指定。

STATMENT ：基于 SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到 binlog 中。
ROW ：基于行的复制( row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
- 缺点：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
MIXED ：基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

redo log

为什么需要redo log？

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。那么 mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改 ，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log作用：从随机读到顺序读，优化了性能

redo log基本概念

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo log file )。 mysql 每执行一条 DML 语句，先将记录写入 redo log buffer ，后续某个时间点再一次性将多个操作记录写到 redo log file 。这种 **先写日志，再写磁盘** 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) `技术。

redo log 有什么作用？

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

redo log与binlog区别

	redo log	binlog
文件大小	redo log 的大小是固定的。	binlog 可通过配置参数 max_binlog_size 设置每个 binlog 文件的大小。
实现方式	redo log 是 InnoDB 引擎层实现的，并不是所有引擎都有。	binlog 是 Server 层实现的，所有引擎都可以使用 binlog 日志
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。	binlog通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
适用场景	redo log 适用于崩溃恢复(crash-safe)	binlog 适用于主从复制和数据恢复

由 binlog 和 redo log 的区别可知： binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB 特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog 和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性 底层就是通过 undo log 实现的。 undo log 主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 `MVCC (多版本并发控制)实现的关键。

undo log是把所有没有COMMIT的事务回滚到事务开始前的状态，系统崩溃时，可能有些事务还没COMMIT，在系统恢复时，这些没有COMMIT的事务就需要借助undo log来进行回滚。

MySQL Query Cache

query cache是MySQL数据库用于缓存select语句以及语句的结果集。该缓存在技术细节上类似键值对存储，将select语句和语句的查询结果集做了一个hash映射并保存在一定的内存区域中。当客户端发起sql查询时，query cache的查找逻辑是：先对sql进行相应的权限验证，接着通过query cache查找结果。它不需要经过Optimizer模块进行执行计划的分析优化，也不需要发送同任何存储引擎的交互，减少了大量的磁盘IO和CPU运算，所以有时候效率非常高。

缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要去解析和执行sql，服务器直接从缓存中取到结果，而不需要在去解析和执行sql，如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值得相关条目被清空，所以对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。

综上：query cache在生产中建议关闭，因为它只能缓存静态数据信息，一旦数据发生变化，经常读写，query cache成了鸡肋，数据仓库可能会考虑开启query cache

设置参数

可以通过调整MySQL的配置（通常在/etc/my.cnf）来设置query cache，主要有5个参数：

query_cache_limit 允许缓存的单条查询结果集的最大容量，默认是1MB，超过此参数设置的查询结果集将不会被缓存
query_cache_min_res_unit 设置查询缓存query cache每次分配内存的最小空间大小，即每个查询的缓存最小占用的内存
query_cache_size 设置query cache所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是，MySQL会自动调整降低最小量以达到1024的倍数
query_cache_type 控制query cache功能的开关，有0、1、2三种模式 0：关闭缓存，任何情况都不使用缓存 1：开启缓存，但是当select语句中使用sql_no_cache时，不使用缓存 2：开启缓存，但是只有当select语句中使用sql_cache时，才使用缓存
query_cache_wlock_invalidate 控制当有写锁定发生在表上的时刻是否先失效该表相关的Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。

开启Query Cache功能可能并不能让系统性能有提升，有时反而会有下降。原因是MySql为了保证Query Cache缓存的内容和实际数据绝对一致，当某个数据表发生了更新、删除及插入操作，MySql都会强制使所有引用到该表的查询SQL的Query Cache失效。对于密集写操作，启用查询缓存后很可能造成频繁的缓存失效，间接引发内存激增及CPU飙升，对已经非常忙碌的数据库系统这是一种极大的负担。

成本模型

基本每种数据库，在执行语句前都会评估执行语句的执行计划的 cost ，通过cost 来判断到底目前哪个 prepare plan 更好更快更强？

首先数据库的获知， 1. 操作的成本 2. 是否有替换的方案 3. 在众多的方案中找到“最低”的执行计划；

上边的查询会包含几个点： 1. JOIN 的次序 2. 访问的方法 3. 子查询

那到底怎么评判那个 JOIN 的次序好，方法秒，子查询怎么查，MYSQL 基于 CPU I/O 两个量来进行计算和最重的判断哪个COST 最低.

那下面问题来了，到底我怎么知道我要访问的表的cost是多少，有没有索引，索引里面怎么设置的。

那就引出 METADATA 数据库表状态，其中包含了 row ,index 的SIZE， index 的信息，是否是唯一的索引，表的大小，范围的评估，基数等等统计信息；

在打开OPTIMIZER_TRACE功能的状态下，执行一条sql语句后，我们看看OPTIMIZER_TRACE能给我什么？

mysql高可用解决方案

主从复制

一般应用对数据库而言都是“ 读多写少 ”，也就说对数据库读取数据的压力比较大，因此，我们可以采用数据库集群的方案，做主从架构、进行读写分离，这样可以提升数据库的并发处理能力。

注意：主库其实也可以进行读取，但我们一般只对其执行写操作，不进行读取，即默认不读取主库。

主从复制的作用

读写分离

我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理数据的能力。其中一个是Master主库，负责写入数据，我们称之为写库。其它都是Slave从库，负责读取数据，我们称之为读库。当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取。

数据备份

我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

高可用性

数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

主从复制原理

一句话描述：Slave会从Master读取binlog来进行数据同步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

主从复制基本原则

每个Master和Slave只能有一个唯一的服务器ID。
每个Slave只能有一个Master。
每个Master可以有多个Slave。

主从同步的要求

读库和写库的数据一致（最终一致）。
写数据必须写到写库。
读数据必须到读库。

主从复制的延时问题

读写分离情况下，解决主从同步中数据不一致的问题，就是解决主从之间数据复制方式的问题，如果按照数据一致性从弱到强来进行划分，有以下3种复制方式。

2.1 异步复制

异步模式就是客户端提交COMMIT之后不需要等从库返回任何结果，而是直接将结果返回给客户端，这样做的好处是不会影响主库写的效率，但可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。

2.2 半同步复制

MySQL5.5版本之后开始支持半同步复制的方式。原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了Binlog，并且写入到中继日志中，再返回给客户端。这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。

在MySQL5.7版本中还增加了一个rpl_semi_sync_ master_wait_for_slave_count参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大,可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。

2.3 群组复制

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。MGR很好地弥补了这两种复制模式的不足。

群组复制技术，简称MGR（MySQL Group Replication），它是MySQL在 5.7.17版本中推出的一种新的数据复制技术，这种复制技术是基于Paxos协议的状态机复制。

首先将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应Node节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接COMMIT即可。在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。

mysql事务，锁与索引

事务

数据库事务隔离级别及解决的问题

参考： cloud.tencent.com/developer/a…

事务隔离方法

（1）读前加锁

InnoDB支持表锁和行锁

（2）写前加锁（写独占锁）

目的：自己加上了别人不能再加上读共享锁、也不能再加上写独占锁。别的事务执行select或 udpate/insert/delete，等待50s，无法成功，就会失败。自己加上了写独占锁，当前事务既然可写，也可读。

什么时候加写独占锁？

1、写insert update delete，默认加上了写独占锁，只有当前事务可读可写（但是select默认不加上写锁，也默认不加上读锁）。

2、对于select，写成 select … for udpate，就加上了写独占锁。写成 select … lock in shared mode，就加上了读共享锁。

（3）意向读共享锁(表锁) + 意向写独占锁(表锁)

问题：这两个表锁（意向共享锁、意向排他锁）存在的意义是什么？

一个事务给一行数据加上锁（共享锁、独占锁），如果另一个事务想来加锁，必须全表扫描，这样效率太慢。所以，Mysql设计

如果一个事务给一行数据，加上一个读共享锁，那么先给这个表加上一个意向共享锁。

如果一个事务给一行数据，加上一个写独占锁，那么先给这个表加上一个意向独占锁。

这样设计的理由：一个事务可以给一张表加表锁的前提是：没有任何一个事务给这张表的任何一个记录加行锁。

这样一来，每个事务加行锁之前，只要看一下这个表上有没有意向共享锁和意向排他锁，只要有，自己就不加锁了，从来提高判断效率。 所以，这两个表锁（意向共享锁、意向排他锁）大大的提高了不同事务给表加行锁的效率，这就是这两个表锁存在的最大意义。

两个行锁锁住的实际是一行记录的索引

共享锁，一个事务给一行数据加锁，另一个事务还可以给这行数据加锁；

排他锁，一个事务给一行数据加锁，另一个事务不可以给这行数据加锁。

底层是怎么实现的？所谓的共享锁和排他锁锁住的是一行数据的什么东西呢？实际上，锁住的是索引，普通索引也行，唯一索引也行、主键索引也行。

（4） mvcc多版本并发控制

建立了一个快照，同一个事物无论查询多少次得到的都是同一个结果；

MVCC的规范，如下：第一次查询就确定了快照版本，

能读取到，快照建立后创建的事务已提交的修改，不能读取到，快照建立后创建的事务；

能读取到，当前自己事务的修改(包括未提交的)，不能读取到，其他事务未提交的修改；

四种隔离级别,哪种mvcc,哪种加锁？

在read uncommited啥都没有，没有锁，没有mvcc

在read commited里面，有mvcc，有锁，只有记录锁，没有间隙锁，所以无法解决幻读问题

在read repeated里面，有mvcc，有锁

在serializable，只有锁，没有mvcc

锁

java主流锁

死锁

死锁产生：
- 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
- 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
- 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依innodb_lock_wait_timeout设置进行事务回滚。

乐观锁和悲观锁

乐观锁和悲观锁是两种思想，它们的使用是非常广泛的，不局限于某种编程语言或数据库。

悲观锁的实现方式是加锁，加锁既可以是对代码块加锁（如synchronized关键字），也可以是对数据加锁（如MySQL中的排它锁）。

乐观锁的实现方式主要有两种：CAS(Compare And Swap 比较并替换)算法和版本号机制；

乐观锁在Java中是通过使用无锁编程来实现，最常采用的是CAS算法，Java原子类中的递增操作就通过CAS自旋实现的。

乐观锁

CAS机制（Compare And Swap 比较并替换）

每次不加锁而是假设没有冲突而去完成某项操作，冲突失败就重试，直到成功为止。

乐观的认为自己在操作数据时，别人不会对当前数据进行修改，因此不会加锁。如果有人对数据进行了修改，则重新获取修改后的数据，进行操作。直到成功为止。

版本号机制

除了CAS，版本号机制也可以用来实现乐观锁。版本号机制的基本思路是在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。当某个线程查询数据时，将该数据的版本号一起查出来；当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作。

需要注意的是，这里使用了版本号作为判断数据变化的标记，实际上可以根据实际情况选用其他能够标记数据版本的字段，如时间戳等。

悲观锁

悲观锁认为自己在使用数据的时候一定有别的线程来修改数据，因此在获取数据的时候会先加锁，确保数据不会被别的线程修改。Java中，synchronized关键字和Lock的实现类都是悲观锁。

索引

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

慢查询优化

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

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

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

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

explain命令

rows是核心指标，绝大部分rows小的语句执行一定很快，所以优化语句基本上都是在优化rows。

适当拆分

　　有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。

　　当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

　　上面几点的优化都是为了减少每条记录的存储空间大小，让每个数据库中能够存储更多的记录条数，以达到减少 IO 操作次数，提高缓存命中率

开启数据库缓存

若数据库写入频繁，高并发，写入量大则不要开查询缓存

尽量从内存中读取数据

增加配置参数innodb_buffer_pool_size

show global status like 'innodb_buffer_pool_pages_%'

innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5

其他缓存

读缓存，线程缓存，排序缓存

sort_buffer_size = 2M connection级参数。太大将导致在连接数增高时，内存不足。

max_allowed_packet = 32M 网络传输中一次消息传输量的最大值。系统默认值为1MB，最大值是1GB，必须设置1024的倍数。

join_buffer_size = 2M 和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

tmp_table_size = 256M 默认大小是 32M。GROUP BY 多不多的问题

max_heap_table_size = 256M key_buffer_size = 2048M 索引的缓冲区大小，对于内存在4GB左右的服务器来说，该参数可设置为256MB或384MB。

read_buffer_size = 1M read_rnd_buffer_size = 16M 进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索

bulk_insert_buffer_size = 64M 批量插入数据缓存大小，可以有效提高插入效率，默认为8M

Innodb缓存

innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5 只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 ——如果你的数据量不大，并且不会暴增，那么无需把innodb_buffer_pool_size 设置的太大了。

innodb_additional_mem_pool_size = 16M 网络传输中一次消息传输量的最大值。系统默认值为1MB，最大值是1GB，必须设置1024的倍数。

innodb_log_files_in_group = 3 循环方式将日志文件写到多个文件。推荐设置为3

innodb_lock_wait_timeout = 120 InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。

error log：错误日志

DBA：日志查看能力才是核心的能力。

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件。该文件不但记录了出错信息，也记录一些警告信息或者正确的信息。总的来说，这个文件更类似于 Oracle的alert文件，只不过在默认情况下是 err结尾。你可以通过 show variables like ‘log_error’ ，来定位该文件

MySQL总结