MySQL高级
存储引擎
数据库的存储引擎是数据库底层的软件组织，数据库管理系统DBMS使用存储引擎创建、更新、删除和查询数据
不同的存储引擎提供了不同的存储机制、索引技巧、锁定水平等功能，都有其特定的功能。
许多数据库管理系统都支持多种存储引擎，常用的存储引擎主要有MyISAM、InnoDB、Memory、TokuDB、Archive和Federated
- 查看MySQL提供的所有存储引擎
- 查看MySQL当前默认存储引擎
- 查看表的存储引擎
MyISAM
MyISAM是 MySQL 5.5 之前的默认存储引擎
MyISAM不支持数据库事务、行级锁和外键，因此在INSERT插入或UPDATE更新数据（即写操作）时需要锁定整个表，效率较低
MyISAM的特点是执行读取操作的速度快，且占用的内存和存储资源较少。它在设计之初就假设数据被组织成固定长度的记录，并且是按照顺序存储的。
在查找数据时，MyISAM直接查找文件的OFFSET，定位比InnoDB更快（InnoDB的寻址要先映射到块，再映射到行）
总的来说，MyISAM的缺点是更新数据慢且不支持事务处理，优点是查询速度快
InnoDB
InnoDB为MySQL提供了事务Transaction支持，回滚Rollback、崩溃修复能力Crash Recovery Capabilities、多版本并发控制Multi-versioned Concurrency Control、事务安全Transaction-safe的操作
InnoDB的底层存储结构为B+树，B+树的每个节点都对应InnoDB的一个page，page的大小是固定的，一般被设置为16KB。其中，非叶子节点只有键值，叶子节点包含完整的数据
InnoDB适合有一下需求的场景
- 经常有数据更新的表，适合处理多重并发更新请求
- 支持事务
- 支持灾难恢复（通过bin-log日志回复）
- 支持外键约束，只有InnoDB支持外键
- 支持自动增加列属性
auto_increment
TokuDB
TokuDB的底层存储结构为Fractal Tree。Fractal Tree的结构与B+树有些类似。Fractal Tree中除了每一个指针（Key），都需要指向一个child(孩子)节点，child节点带一个Message Buffer，该Message Buffer是一个先进先出FIFO队列，用来缓存更新操作
每一次插入操作都只需要落在某节点的Message Buffer上，就可以马上返回，并不需要搜索到叶子节点。这些缓存的更新操作会在后台异步合并并更新到对应的节点上
ToKuDB在线添加索引，不影响读写速度，有非常高的读写性能，主要适用于写入速度快，访问频率不高的数据或历史数据归档
Memory
Memoy表使用内存空间创建
每个Memory表实际上都对应一个磁盘文件用于支持数据持久化。Memory表因为数据是存放在内存中的，因此访问速度非常快，通常使用Hash索引来实现数据索引。
缺点是一旦服务关闭，表中数据会丢失
Memory还支持散列索引和B树索引。B树索引可以使用部分查询和通配查询，也可以使用不等于和大于等于等操作方便批量数据访问，散列索引相对于B树索引来说，基于Key的查询效率更高，但是基于范围的查询效率相对较低
MyISAM和InnoDB的区别
MyISAM是MySQL 5.5 之前的默认数据库引擎。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全回复
MySQL 5.5 之后引入InnoDB，默认引擎更换为InnoDB
大多数时候都是用InnoDB存储引擎，但是在某些情况下使用MyISAM也是合适的，比如读密集的情况下（且不介意MyISAM的崩溃不可回复问题）
对比
是否支持行级锁
MyISAM只支持表级锁（table-level locking），而InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁
是否支持事务和崩溃后安全恢复
MyISAM强调的是性能，每次查询具有原子性，且执行速度比InnoDB引擎个更快，但是不提供事务支持
InnoDB提供事务支持，外键等高级数据库功能。具有事务提交commit、回滚rollback和崩溃修复能力crash recovery capabilities和事务安全transaction-safe（ACID compliant）型表
是否支持外键
MyISAM不支持，InnoDB支持
是否记录总行数
MyISAM 记录表的总行数，
count指令不需要计算，而 InnoDB 的 count 指令需要计算是否支持MVCC
大多数的MySQL事务型存储引擎，如InnoDB，Falcon以及PBXT都在使用一种简单的行锁机制。事实上，他们都和另外一种用来增加并发性的被称为“多版本并发控制（MVCC）”的机制来一起使用。MVCC不只使用在MySQL中，Oracle、PostgreSQL，以及其他一些数据库系统也同样使用它。
你可将MVCC看成行级别锁的一种妥协，它在许多情况下避免了使用锁，同时可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。
仅InnoDB支持
应对高并发事务，MVCC比单纯的加锁更高效
MVCC只在
READ COMMITTED和REPEATABLE READ两个隔离级别下工作MVCC可以使用乐观锁和悲观锁来实现
各种数据库中MVCC实现并不统一
索引
索引, 有哪些索引, 怎么才能命中索引, 最左原则
- 为什么要有索引
- 索引原理, 底层的数据结构, 哈希索引和 B树索引, 两种的区别, InnoDB 支持哪种索引
- 聚簇索引和非聚簇索引
- 组合(联合)索引, 全文索引, 唯一索引, 普通索引
- 主键索引和二级索引
- 拓展: 如何命中索引
- 建立索引的原则
- 联合索引最左原则
为什么需要索引
查询操作远远多于修改删除增加操作，于是要提高查询的效率
索引原理——底层数据结构
MySQL索引使用的数据结构主要有
BTree索引和哈希索引
- 哈希索引：查询单条快，范围查询慢
- b-tree索引：b+树，层数越多，数据量指数级增长（一般就用它，InnoDB默认支持它）
| 引擎 | 事务性 | 锁级别 | 索引 |
|---|---|---|---|
| InnoDB | 支持 | 行锁、表锁 | 支持B-Tree、全文索引；不支持哈希索引 |
| MyISAM | 不支持 | 表锁 | 支持B-Tree、全文索引；不支持哈希索引 |
| Memory | 不支持 | 表锁 | 支持B-Tree、哈希索引；不支持全文索引 |
| NDB | 支持 | 行锁 | 支持哈希索引；不支持B-Tree和全文索引 |
| Archive | 不支持 | 表锁 | 不支持B-Tree、哈希、全文索引 |
对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快
其余大部分场景，建议使用BTree索引
InnoDB支持hash索引吗？
- 默认是不支持的
- 可以手动添加hash索引，但是InnoDB最终还是会把它捣鼓成B+ Tree，所以等于用不了hash索引
- 用不了hash索引不代表InnoDB没用到hash索引，InnoDB会自己生成一些Hash索引来快速定位缓存数据页，但是这些hash索引不是数据库用的那个索引，两种索引不是一回事
聚簇索引与非聚簇索引
MySQL的BTree索引使用的是B树种的B+Tree，但对于主要的两种存储引擎的实现方式是不同的
- MyISAM：B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出data域的值，然后以data域的值为地址读取相应的数据记录。称为非聚簇索引
- 非聚簇索引的多颗 B+ 树之间看起来没什么不同，节点结构完全一致，只是存储内容不同，主键索引 B+ 树存储主键值，辅助索引 B+ 树存储辅助键，表数据存储在独立的地方，B+ 树叶子节点都是用地址指向真正的表数据，由于索引树之间相互独立，所以通过辅助键检索无需访问主键索引树
- InnoDB：**其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这称为聚簇索引（聚集索引）**。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是与MyISAM不同的地方。
- 在根据主键搜索时，直接找到key所在的节点即可取出数据
- 在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引
- 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂
- InnoDB 一定有主键，主键一定是聚簇索引，不手动设置，则会使用 unique 索引，没有 unique 索引，则会使用数据库内部的一个行的
隐藏id作为主键索引，非叶子节点里存储的是主键值 - InnoDB 的这种索引结构决定了
SELECT * FROM ... WHERE username='alice'这种语句比SELECT id FROM ... WHERE username='alice'慢，因为它会触发两次 B+ 树上的搜索
索引分类
主键索引 Primary Key
主键：某一个属性组能唯一标识一条记录
- 它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键
- 一般在建表的时候使用
PRIMARY KEY()同时创建主键索引
特点：
- 最常见的索引类型
- 确保数据记录的一致性
- 确定特定数据记录在数据库中的位置
删除主键索引
普通索引 Index
作用：快速定位特定数据
注意：
index和key关键字都可以设置常规索引- 普通索引的唯一任务是加快对数据的访问数据
- 应该只为那些最经常出现在查询条件(WHERE column = ...)或排序条件(ORDER BY column)中的数据创建索引
- 不宜添加太多常规索引，影响数据的插入、删除和修改操作
唯一索引 Unique
它与前面的普通索引类似，不同的是：
- 普通索引允许被索引的数据列包含重复的值
- 唯一索引列的值必须唯一，但允许空值
- 如果是联合索引，则列值的组合必须唯一
全文索引 FullText
- MySQL 5.6之前的版本，只有MyISAM存储引擎支持全文索引
- MySQL 5.6之后的版本，MyISAM和InnoDB都支持全文索引
- 只有字段数据类型为char、varchar、text及其系列才可以建全文索引
- 测试或使用全文索引时，要先看一下自己的MySQL版本和存储引擎、数据类型是否支持全文索引
- 只能用于MyISAM类型的数据表
- 只能用于CHAR、VARCHAR、TEXT数据列类型
- 适合大型数据集
在创建表时创建
在已存在的表上创建
Alter table在已存在的表上创建
删除索引
显示索引信息
增加全文索引
使用EXPLAIN分析SQK语句执行性能
使用全文索引
- 全文索引通过MATCH()函数完成
- 搜索字符串为against()的参数被给定。搜索以忽略大小写字母的方式执行。对于表中的每个记录行，MATCH()返回一个相关性值。即，在搜索字符串与记录在MATCH()列表中指定的列的文本之间的相似性尺度
联合索引
联合索引时指对表上的多个列合起来做一个索引，省的你查询的时候，where后面的条件字段一直再变，你就想给每个字段加索引的尴尬问题。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列
注意建立联合索引的一个原则：索引是有个最左匹配的原则的，所以建联合索引的时候，将区分度高的放在最左边，依次排下来，范围查询的条件尽可能的往后边放。
联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了
覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性
建立索引的原则
- 选择唯一性索引：唯一性索引一般基于Hash算法实现，可以快速、唯一地定位某条数据
- 为经常需要排序、分组和联合操作的字段建立索引
- 限制索引的数量：索引越多，数据更新表越慢，因为在数据更新时会不断计算和添加索引
- 尽量使用数据量少的索引：如果索引的值很长，则占用的磁盘空间较大，查询速度会受到影响
- 尽量使用前缀来索引：如果索引字段的值太长，则不但影响索引的大小，而且会降低索引的执行效率，这时需要使用字段的部分前缀来索引
- 删除不再使用或使用很少的索引
- 尽量选择区分度高的列作为索引：区分度表示字段不重复的比例
- 索引列不能参与计算：带函数的查询不建议参与索引
- 尽量扩展现有索引：联合索引的查询效率比多个独立索引高
联合索引最左原则
注意建立联合索引的一个原则：索引是有个最左匹配的原则的，所以建联合索引的时候，将区分度高的放在最左边，依次排下来，范围查询的条件尽可能的往后边放。
联合索引问题实战
五大题型
题型一
SELECT * FROM table WHERE a = 1 and b = 1 and c = 3;
- 该如何建立索引
- 错误回答：对 (a, b, c) 建立索引
- 正确回答 (a, b, c) 或 (c, b a) 或 (b, a, c) ... 都可以，重点是把区分度低的放后面，区分度高的字段放前面
- 按照正确的区分度创建联合索引后，执行 SQL 时优化器会自动调整 WHERE 后的条件顺序，使索引生效
题型二
SELECT * FROM table WHERE a > 1 and b = 2;
- 错误回答：(a, b)
- 正确回答：(b, a) ，因为最左匹配原则遇到范围查找就停止匹配，(b , a) 优化器会调整成 WHERE b = 2 and a > 1 来走索引
题型三
SELECT * FROM table WHERE a > 1 and b = 2 and c > 3;
- 如何建立索引
- 肯定是以用了等号的 b 开头，之后任意，可以是 (b, a) 或 (b, c)，根据 a 和 c 的区分度决定
SELECT * FROM table WHERE a = 1 and b = 2 and c > 3;
- (a, b, c) 或者 (b, a, c)，根据 a b 数据的区分度，把区分度高的放在前面
题型四
SELECT * FROM table WHERE a = 1 ORDER BY b;
- 正确姿势：对 (a, b) 建索引，当 a = 1 时，b 相对有序，可以避免再次排序
SELECT * FROM table WHERE a > 1 ORDER BY b;
- 错误姿势：对 (a, b) 建索引
- 为 a 的值时一个范围，这个范围内 b 的值时无序的
SELECT * FROM table WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
- (a, b, c) 或者 (b, a, c)
题型五
SELECT * FROM table WHERE a IN (1, 2, 3) and b > 1;
- 正确姿势：对 (a, b) 建索引，因为 IN 在这里可以视为相等值引用，不会终止索引匹配
锁
- 基于锁的属性分类：共享锁、排它锁
- 基于锁的粒度分类：行锁(InnoDB)、表锁(InnoDB、MyISAM)、页锁（BDB）、记录锁、间隙锁、临建锁
- 基于锁的状态分类：意向共享锁、意向排他锁
属性分类
共享锁 Share Lock
共享锁又称读锁，简称 S锁：当一个事务为数据加上读锁后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复度问题
排它锁 Exclusive Lock
排它锁又称写锁、简称 X锁：当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放后，其他事务才能对数据进行加锁。排它锁的目的是在数据修改时，不允许其他人同时修改，也不允许其他人读取。避免出现脏数据与脏读问题
粒度分类
表锁
锁住整个表，当下一个事务访问该表的时候，必须等待前一个事务释放锁才能进表进行访问
特点：粒度大、加锁简单、容易冲突
行锁
锁住某一行或多行记录，其他事务访问同一张表时，只有被行锁锁住的几率不能访问，其他几率可以被正常访问
特点：粒度小、加锁比表锁麻烦、不容易冲突、相比表锁并发度更高
页锁
粒度介于表锁、行锁之间。表锁锁速度快、但冲突多；行锁冲突少，但速度慢；页锁属于这种，一次锁定相邻的一组数据
特点：开销和加锁时间介于表锁、行锁之间；会出现死锁；粒度在行锁与表锁之间、并发度一般
间隙锁 Gap Lock
属于 行锁 中的一种，间隙锁在事务加锁后锁住表记录的某一区间，当表的相邻 ID 之间出现空隙会形成一个区间，遵守 左开右闭 原则
范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现 REPEATABLE_READ(可重复读)的事务级别中
触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生在同一个事务中，两次查询结果不一样的问题
临建锁 Next-Key Lock
属于 行锁 中的一种，是 InnoDB 默认行锁算法。是 记录锁 与 间隙锁 的组合，左闭右闭
触发条件：范围查询未命中，查询命中了索引
结合记录锁、间隙锁的特性，临建锁避免了在范围查询时出现脏读、重复度、幻读问题。加了临建锁后，范围内的数据不允许被修改和插入
状态分类 （类似 Flag）
某事务在加锁成功后，设置一个状态，告诉后面的事务，当前表已经被其他事务设置了排它锁，因此不能继续加锁。后续的事务，只需要查看这个状态就可以知道自己当前能不能给表加锁，避免了对整个索引进行遍历查看是否存在锁，这个状态就是意向锁
意向共享锁
当一个事务试图对整个表进行加共享锁之前，需要先获得这个表的意向共享锁
意向排他锁
当一个事务试图对整个表进行加排它锁之前，需要先获得这个表的意向排它锁
MySQL 主从复制
MySQL 主从复制过程
- MySQL 的主从复制主要涉及
三个线程：master(binlog dump thread)、salve(I/O thread)、slave (SQL thread)，Master 涉及一条线程、Slave 涉及两条线程
- 主节点 binlog：主从复制的基础是主库记录数据库的所有更改记录到 Binlog。 Bin Log 是数据库服务器启动的那一刻起，保存所有修改数据库结构和内容的一个
文件- 主节点 Log Dump 线程：当 Bin Log 发生改变，Log Dump 线程读取其内容并发送给从节点
- 从节点 I/O 线程：接收 Bin Log 内容，并将其写入 Relay Log 文件中
- 从节点的 SQL 线程：读取 Relay Log 文件内容对数据更新进行重放，最终保证从数据库的一致性
注意
主从节点使用 BinLog 文件 + Position 偏移量来定位主从同步的位置，从节点会保存其已经接受到的偏移量，如果从节点发生宕机重启，则会从 Position 的位置发起同步
同步复制模式
由于 MySQL 默认的复制方式是 异步 的，主库把日志发送给从库后不关心从库是否已经处理，这样就产生了一个问题，假设主库挂了，从库也处理失败了，这时候从库升级为主库，日志就丢失了，由此产生两个概念
全同步复制
主库写入 Bin Log 后强制同步日志到从库，所有从库都执行完成后才返回给客户端，但是很显然这个方式非常影响性能
半同步复制
从库写入日之后返回 ACK 确认消息给主库，主库收到至少一个从库的确认消息就认为写操作完成
Explain
执行计划就是 SQL 执行查询的顺序，以及如何使用索引查询，返回的结果集行数等
参数
ID：一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id 的顺序是按 select 语句出现的顺序增长的。id 列的数值越大，执行优先级就越高，id 列的数值相同则从上往下执行，id 列值为 NULL 最后执行
selectType：表示查询中每个 select 字句的类型
SIMPLE：表示此查询不包含UNION查询或子查询PRIMARY：表示此查询是最外层的查询（包含子查询）SUBQUERY：表示子查询中的第一个 SELECTUNION：表示此查询时 UNION 的第二或随后的查询DEPEDENT UNION：UNION 中的第二个或后面的查询语句，取决于外面的查询UNION RESULT： UNION 的结果DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询，即子查询依赖于外层查询的结果DERIVED：衍生，表示导出表的 SELECT (FROM 子句的子查询)
table：表示该语句查询的表
type：优化 SQL 的重要字段，也是判断 SQL 性能和优化程度的重要指标
const：通过索引一次命中，匹配一行数据
system：表中只有一条记录
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref：非唯一性索引，返回匹配某个值的所有
range：只检索给定范围的行，使用一个索引来选择行，一般用于between、>、<
index：只遍历索引树
ALL：全表扫描，这个类型的查询是性能最差的查询之一，那么基本就是随着表数据规模的增大，执行效率越来越低
possible_keys：表示 MySQL 在执行该 SQL 语句的时候，可能用到的索引信息，仅是可能，不一定真的用到
key：此字段是 MySQL 在当前查询时所真正使用的索引，是possible_keys的子集
key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，也是优化 SQL 时评估索引的重要指标
rows：MySQL 查询优化器根据统计信息，估算 SQL 返回结果集需要扫描的行数，这个值非常重要，索引优化后，扫描读取行越多，说明索引设置不好
filtered：返回结果的行占需要读到的行的百分比，百分比越高，说明查询数据越精准
extra：额外信息
using filesort：表示 MySQL 对结果集进行外部排序，不能通过索引顺序达到排序效果，一般要进行优化，因为这样就不能走索引了using index：覆盖索引扫描，表示查询在索引树中就能找到所需数据，不用扫描表数据文件，一般表示性能不错using temporary：查询使用临时表，一般出现排序，分组、多表 join 的情况，查询效率不高，建议优化using where：SQL 使用了 Where 过滤，效率较高