存储引擎

show engines;

引擎	说明	是否支持事物	XA	savepoints
MEMORY	Hash based, stored in memory, useful for temporary tables	否
InnoDB	Supports transactions, row-level locking, and foreign keys	是
PERFORMANCE_SCHEMA		否
MyISAM		否
MEG_MYISAM		否
BLACKHOLE		否
CSV		否
ARCHIVE		否

并发

CAP

Consistency（一致性）

指数据在多个副本之间能够保持一致的特性（严格的一致性）

Availability（可用性）

指系统提供的服务必须一直处于可用的状态，每次请求都能获取到非错的响应（不保证获取的数据为最新数据）

Partition tolerance（分区容错性）

分布式系统在遇到任何网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务，除非整个网络环境都发生了故障

事务

ACID性质

1. 原子性 atomicity ：事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做

2. 一致性 consistency

3. 隔离性 isolation ：一个事务的执行不能被其他事务干扰。

4. 持久性 durability ：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性一般由代码层面来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复。

隔离

一般的DBMS系统，默认都会使用读提交（Read-Comitted，RC）作为默认隔离级别，如Oracle、SQLServer等，而MySQL却使用可重复读（Read-Repeatable，RR）。要知道，越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为：串行化 > RR > RC >读未提交

1. read uncommited ：读到未提交数据，存在脏读问题

2. read committed：读已提交，解决了脏读问题，但存在不可重复读

3. repeatable read：可重读，解决了不可重复读的问题，但可能存在幻读问题

4. serializable ：串行事物，事务顺序执行，但是效率很低

InnoDB MVCC

InnoDB 中 MVCC 的实现方式为：每一行记录都有两个隐藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果没有主键，则还会多一个隐藏的主键列）。

如此一来不同的事务在并发过程中，SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

锁

锁机制

• 共享锁（读锁）：其他事务可以读，但不能写。

• 排他锁（写锁） ：其他事务不能读取，也不能写。

• 乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。

• 悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

粒度

表锁（table-level locking）

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

支持的存储引擎

MyISAM 和 MEMORY

优点

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。

适用场景

表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

行锁 （row-level locking）

支持的存储引擎

InnoDB 存储引擎既支持行级锁（），也支持表级锁，但默认情况下是采用行级锁。

优点

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

最大程度的支持并发，同时也带来了最大的锁开销。

在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

行级锁只在存储引擎层实现，而Mysql服务器层没有实现。

适用场景

行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

页面锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

死锁

原因

1. 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

2. 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

3. 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测

数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

show status like 'innodb_row_lock_%';

恢复

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

避免死锁

1. 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁

如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会

通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

隐式类型转换

原因

sql传入的参数类型与字段定义的类型不同，因为存在这个机制，间接导致mysql查询用不到索引的情况。

规则

• 如果一个或两个参数都是NULL，比较的结果是NULL，除了NULL安全的<=>相等比较运算符。对于NULL <=> NULL，结果为true。不需要转换

• 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。

• 如果两个参数都是整数，则将它们作为整数进行比较。

• 如果不与数字进行比较，则将十六进制值视为二进制字符串

• 如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数与十进制值进行比较，如果另一个参数是浮点值，则将参数与浮点值进行比较

• 如果其中一个参数是TIMESTAMP或DATETIME列，另一个参数是常量，则在执行比较之前将常量转换为时间戳。

• 在所有其他情况下，参数都是作为浮点数（实数）比较的。

优化方法

关注慢查询

索引

种类

1. 主键索引：一个表只能有一个主键

2. 普通索引

3. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

4. 全文索引

索引与数据的组织形式

非聚簇索引

是myisam存储引擎的存储方式

聚簇索引

innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

索引存储的数据结构

B+树

区别于B树

• 有k个子结点的结点必然有k个关键码。

• 非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。

• 树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。

优点

B+树的磁盘读写代价更低

B+树的查询效率更加稳定

B+树更便于遍历

B+树更适合基于范围的查询

哈希

索引覆盖

我们上面说到如果是非聚簇索引的话会需要回表，查询两次，但是如果要查询得字段，数据直接就在索引上是可以不需要回表的，这种情况叫做索引覆盖

联合索引的最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配了，比如 a=3 and b=4 and c>5 and d=6 如果建立(abcd)顺序的索引,d就用不到索引了，如果建立(abdc)的索引则都可以用到索引

mysql 是创建复合索引的规则是根据索引最左边的字段进行排序，在第一个字段排序的基础上再进行第二个字段排序，类似于order by col1，col2… 所以第一个字段是绝对有序的 第二个字段就是无序的了，所以mysql 强调最左前缀匹配

创建原则

经常用的列优先（特别是联合索引注意有最左匹配原则）

离散度高的列优先（离散度高原则）

宽度小的列优先（最少空间原则）

执行计划分析SQL

explain 或 desc

读写分离

数据同步原理

数据同步方案

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

异步复制

MySQL master事务的提交不需要经过slave的确认