mysql必知必会

架构

server层

1. 连接器：管理连接，权限验证

2. 查询缓存

3. 分析器：词法、语法解析

4. 优化器：生成执行计划，索引选择

5. 执行器：操作引擎，返回结果

存储引擎层

1. 负责数据存储和提取，插件式，支持InnoDB、MyISAM多个存储引擎

Innodb

后台线程：负责刷新内存池中的数据，保证缓存池中的内存缓存是最近的数据，将已修改的数据刷新到磁盘文件，同时保证数据库发生异常的情况能恢复到正常情况；

内存池：内存池也可以叫做缓存池，主要为弥补磁盘的速度较慢对数据库产生的影响，查询的时候，首先将磁盘读到的页的数据放在内存池中，下次读取的时候直接从内存池中读取数据，修改数据的时候，首先修改内存池中的数据，然后后台线程按照一定的频率刷新到磁盘上。

日志系统

redo log（重做日志）

1. InnoDB引擎的日志，redo log 保证数据库异常重启之前提交的记录不会丢失（crash-safe），确保事务的持久性。
2. 在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到 redo log 日志中，然后更新内存（buffer pool），此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将 redo log 中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术（先写日志再写磁盘）
3. redo log 是物理日志，记录的是在某个数据页上做了什么修改
4. redo log是循环写，空间固定会用完

出现 MySQL 宕机或者断电时，如果有缓存页的数据还没来得及刷入磁盘，当 MySQL 重新启动时，可以根据 redo log 日志文件，进行数据重做，将数据恢复到宕机或者断电前的状态

redo log 日志文件是持久化在磁盘上的，磁盘上可以有多个 redo log 文件，MySQL 默认有 2 个 redo log 文件，每个文件大小为 48M

redo log 日志是存储在磁盘上的，那么此时是不是立马就将 redo log 日志写入磁盘呢？显然不是的，而是先写入一个叫做 redo log buffer 的缓存中，redo log buffer 是一块不同于 buffer pool 的内存缓存区

为什么MySQL 要写到 redo log buff 内存

因为一个事务中可能涉及到多次读写操作，写入Buffer中分组写入，比起一条条的写入磁盘文件，效率会高很多。

binlog（归档日志）

1. server层日志，记录了MySQL对数据库执行更改的所有操作，没有crash-safe能力

2. binlog是逻辑日志，记录的是记录所有数据库表结构变更（例如CREATE、ALTER）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志

3. binlog采用追加写的模式

4. 用途：

• 恢复：binlog日志恢复数据库数据
• 复制：主库有一个log dump线程，将binlog传给从库，从库有两个线程，I/O线程读取主库传过来的binlog内容并写入到relay log，SQL线程从relay log里面读取内容，写入从库的数据库
• 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击

binlog常见格式

format	定义	优点	缺点
statement	记录的是修改SQL语句	日志文件小，节约IO，提高性能	准确性差，有些语句的执行结果是依赖于上下文命令可能会导致主备不一致（delete带limit，很可能会出现主备数据不一致的情况）
row	记录的是每行实际数据的变更	准确性强，能准确复制数据的变更	日志文件大，较大的网络IO和磁盘IO
mixed	statement和row模式的混合	准确性强，文件大小适中	有可能发生主从不一致问题

两段提交

1. 两段提交保证数据库binlog状态和日志redo log恢复出来的数据库状态保持一致

2. 两段提交： 写入redo log处于prepare阶段 –写入bin log –提交事务处于commit状态

• 时刻A崩溃恢复： redo log未提交， bin log 未写，不会传到备库，这时事务会回滚
• 时刻B崩溃恢复：如果 redo log 事务完整有commit标识则直接提交，如果 redo log 事务只有完整的prepare，则判断对应事务 bin log 是否完整，是提交事务，否则回滚事务

3. bin log完整性判断：

• statement格式最后有commit
• row格式最有有一个XID event（redo log 和 bin log关联：共同字段XID）

MySQL 重启后，进行数据重做时，在 redo log 日志中由于该事务的 redo log 日志没有 commit 标识，那么就不会进行数据重做，磁盘上数据还是原来的数据，也就是事务没有提交。

undo log

1. undo log是逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录

2. undo log 作用

• 提供回滚
• 多版本并发控制

宕机恢复流程？

1. 启动开始时检测是否发生崩溃

2. 定位到最近的一个checkpoint

3. 定位在这个checkpoint时flush到磁盘的数据页，检查checksum。如果不正确，说明这个页在上次写入是不完整的，从doublewrite buffer里把正确的页读出来，更新到buffer中的页

4. 分析redo log，标识出未提交事务

5. 顺序执行redo，读取到buffer pool中

6. rollback未提交的事务

Mysql抖动

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为脏页，把内存里的数据写入磁盘。

flush场景

1. InnoDB 的 redo log buffer写满了，系统会停止所有更新操作，把 checkpoint 对应的所有脏页都 flush 到磁盘
2. 系统内存不足，当需要新的内存页，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是”脏页”，就要先将脏页写到磁盘
3. MySQL 认为系统空闲的时候，会flush脏页
4. MySQL 正常关闭的情况，MySQL 会把内存的脏页都 flush 到磁盘上

InnoDB 的刷盘速度参考两个因素：一个是脏页比例，一个是 redo log 写盘速度

存储引擎

InnoDB

默认存储引擎

• 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。

• 主键索引是聚簇索引，在索引中保存了数据。

• 内部做了很多优化，包括预读、自适应哈希索引、插入缓冲区等。

• 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

适用于只读数据，或者表比较小。

• 提供了大量的特性，包括压缩表、空间数据索引等。

• 不支持事务。

• 不支持行级锁，只能对整张表加锁

比较

• 事务：InnoDB 支持事务。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

MyISAM与InnoDB索引区别

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效；MyISAM主键索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

为什么myisam 的查询要比innoDB 快

1）InnoDB 要缓存数据和索引，MyISAM只缓存索引块， 这中间还有换进换出的减少；

2）InnoDB寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比InnoDB要快

3）InnoDB还需要维护MVCC一致

b+树为什么能三层能存2000多万个，计算过程。

InnoDB存储引擎最小储存单元，页大小是16K。假设一行数据的大小是1k，一个页可以存放16行数据。

在B+树中叶子节点存放数据，非叶子节点存放键值+指针。我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，16384/14=1170。一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。

Innodb引擎特性

写缓冲（change buffer）

Insert Buffer用于非聚集索引的插入和更新操作。先判断插入的非聚集索引是否在缓存池中，如果在则直接插入，否则插入到 Insert Buffer 对象里。再以一定的频率进行 Insert Buffer 和辅助索引叶子节点的 merge 操作，将多次插入合并到一个操作中，减少随机IO带来性能损耗，提高对非聚集索引的插入性能。

二次写

mysql最小的io单位是16k，文件系统io最小的单位是4k，因此存在IO写入导致page损坏的风险

如果数据库发生宕机时，可以通过重做日志对该页进行恢复，但是如果该页本身已经损坏了，进行重做恢复是没有意义的。因此引入了”二次写”方案，解决部分写失败，提高数据页的稳定性。

自适应哈希索引

InnoDB 会监控对表上各个索引页的查询，如果观察到通过哈希索引可以带来性能提升，则自动建立哈希索引。自适应哈希索引通过缓存池的 B+ 树页构造而来，因此建立速度很快。

预读

数据库访问通常都遵循集中读取原则，使用一些数据大概率会使用附近的数据，这就是所谓的局部性原理，它表明提前加载是有效的，能减少磁盘的i/o。

预读机制就是发起一个i/o请求，异步的在缓冲池中预先回迁若干个页面，预计将会用到的页面回迁。

MyISAM为什么不支持事务

MyISAM存储引擎没有redo和undo文件，没法支持事务的ACID特性，锁也只有表锁

数据库字段是如何存储的

• 所有数据都被逻辑地存放在表空间中，表空间又由段（segment）、区（extent）、页（page）组成
• 表空间由各个段构成，叶子节点存储在数据段，非叶子节点存储在索引段
• 段由区组成
• 区是由连续的页组成，默认区的大小为1M，页的大小为16KB
• 页是InnoDB磁盘管理的最小单位。页中存储的是具体的行记录。一行记录最终以二进制的方式存储在文件里。

默认情况下用一个共享表空间 ibdata1 ，如果开启了 innodb_file_per_table 则每张表的数据将存储在单独的表空间中，也就是每张表都会有一个文件

缓存淘汰策略

全表扫描和预读机制可能将频繁访问的数据给淘汰，优化思路就是：对数据进行冷热分离，将 LRU 链表分成两部分，一部分用来存放冷数据，也就是刚从磁盘读进来的数据，另一部分用来存放热点数据，也就是经常被访问到数据。按照5：3的比例把整个LRU链表分成了young区域和old区域。

1、访问young区域，因此和优化前的LRU算法一样，将其移到链表头部

2、要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。

3、处于old区域的数据页，每次被访问的时候都要做下面这个判断：

• 若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；
• 如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。1秒这个时间，是由参数innodb_old_blocks_time控制的。

索引

定义

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

索引是一种特殊的文件，需要占据物理空间的，它们包含着对数据表里所有记录的引用指针。

优缺点

索引的优点

• 加快数据的检索速度。
• 减少查询中分组和排序的时间
• 唯一性索引，可以保证数据的唯一性
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）

索引的缺点

• 建立和维护索引耗费时间、空间

使用场景

where

order by

使用order by按照某个字段排序时，如果该字段没有建立索引，那么会将查询出的所有符合条件的数据使用磁盘临时文件完成外部排序或者在内存中完成排序。具体取决于排序所需的内存和参数sort_buffer_size。

如果我们对该字段建立索引，由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率（一般小表驱动大表，避免了大表的全表扫描）

覆盖索引

辅助索引可以直接提供查询结果，不需要回表。称为覆盖索引。

如果要查询的字段都在某一索引中，那么可以直接在索引表中查询而不会访问原始数据。

尽可能的在select后只写必要的查询字段，以增加覆盖索引的几率。

索引类型

主键索引： 不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引：不允许重复，允许为NULL。

普通索引：基本的索引类型，允许为NULL值。

全文索引： 是目前搜索引擎使用的一种关键技术。

创建索引的原则

• 联合索引的最左前缀匹配原则，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的顺序可以任意调整。
• 较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引
• 若是不能有效区分数据的列不适合做索引列（如性别），选择基数较大的列做索引
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。一页存储的数据越多一次IO操作获取的数据越大，效率越高。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。联合索引比单个索引的性价比更高。
• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构。

使用索引查询一定能提高查询的性能吗

使用索引查询不一定能提高查询性能，索引范围查询（INDEX RANGE SCAN）适用于两种情况：

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索（？？？）

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。

百万级别或以上的数据如何删除

索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加，修改，删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率。

• 先删除索引（三分钟）
• 然后删除其中无用数据（两分钟）
• 删除完成后重新创建索引（约十分钟左右）

前缀索引

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

索引的数据结构

和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等。

• InnoDB存储引擎的默认索引实现为B+树索引，适用于全键值、键值范围和键前缀查找。

• 哈希索引底层的数据结构是哈希表，适合场景为绝大多数查询为单条记录查询。InnoDB 存储引擎的自适应哈希索引，当某个索引值被使用的非常频繁时，创建一个哈希索引，实现快速哈希查找。
• 全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

B+树原理

B树和B+树

区别

B树中，键和值存放在内部节点和叶子节点；B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

B+数的叶子节点是一个页。B+树的叶子节点有一条双向链表相连，而B树的叶子节点各自独立。

B树优点

B树内部节点同时存储键和值，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。

B+树优点

• B+树的内部节点只存放键，不存放值，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。
• B+树的叶节点由一条双向链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。

使用B+树而不是B树

• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。B+树的内部结点只存放索引，能容纳的数据更多，让索引树更加矮胖。
• B+树的查询效率更加稳定。B树搜索越靠近根节点的记录查找时间越短。B+树中，任何关键字的查找都必须走一条从根节点到叶节点的路，查找路径长度相同，查询效率相当。
• B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用双向指针，只要遍历叶子节点就可以实现整棵树的遍历。

Hash索引和B+树优劣

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值

B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值

那么可以看出他们有以下的不同：

• hash索引进行等值查询更快，但是却无法进行范围查询。

• hash索引不支持使用索引进行排序。

• hash索引不支持模糊查询以及多列索引的最左前缀匹配。

• hash索引不稳定发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定。

B+树与红黑树的比较

使用 B+ 树访问磁盘数据有更高的性能。B+树顺序访问性能更好。

B+ 树有更低的树高，寻道的时间与树高成正比，所以 B+ 树更适合磁盘数据的读取。

B+树分裂与合并

分裂

只需要将这个节点分裂成两个节点。

节点分裂之后，其上层父节点的子节点个数就有可能超过 m 个。需将父节点也分裂成两个节点。

合并

如果某个节点的子节点个数小于 m/2，我们就将它跟相邻的兄弟节点合并。

合并之后结点的子节点个数有可能会超过 m。需要再分裂节点。

索引是如何存储的

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：数据存储和索引分开，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要通过索引访问数据，在内存中直接搜索索引，然后通过索引找到磁盘相应数据。索引不在key buffer命中时，速度慢。

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。

为什么InnoDB表必须有主键

InnoDB表必须有主键，并且推荐使用整型的自增主键。

1、如果没有显式定义主键，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；如果没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的主键索引。

2、如果使用非自增主键（如果身份证号或学号等）
由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置，此时MySQL需要移动数据，频繁的移动、分页操作造成了大量的碎片。

为什么非主键索引存储的是主键值

减少了出现数据页分裂时，二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引）

聚簇索引其索引树的叶子节点中存的是整行数据。非聚簇索引的叶子节点内容是主键的值。

非聚簇索引一定会回表查询吗？

不一定，查询语句所要求的字段全部命中了索引，就不必再进行回表查询。

联合索引

使用多个字段建立索引。

• 一般情况下，将查询需求频繁或者字段选择性高的列放在前面。

• 组合索引的最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询。

唯一索引和非唯一索引的区别

1、普通索引的字段可以重复，唯一索引的字段重复。

2、数据修改操作，普通索引可以用 Change Buffer，而唯一索引不行。

• 在内存：普通索引找位置，插入值。唯一索引找位置，判断没有冲突，插入值。一个判断的差别，耗费微小CPU时间。
• 不在内存：唯一索引将数据页读入内存，判断到没有冲突，插入值。普通索引将更新记录在change buffer。

3、查询数据时，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况可以忽略不计。

索引下推

如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

过程

读取索引树，在索引树上查找，把满足已经下推的条件的（经过查找，红色的满足）从表记录中读出

COUNT(id)好还是COUNT(*)好

• 当 count 统计某一列时，比如count(a)，是不统计 null 的。
• count(*)无论是否包含空值，都会统计。5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*)语句。所以count(id) 没count (*)快。
• COUNT(1)与count(*)执行计划相同，速度没有明显差别。

几千万记录，数据库表结构如何平滑变更？

pt-online-schema-change

假设：

user(uid, name, passwd)要扩展到： user(uid, name, passwd, age, sex)

1. 先创建一个扩充字段后的新表：user_new(uid, name, passwd, age, sex)
2. 在原表 user 上创建三个触发器，对原表 user 进行的所有 insert/delete/update 操作，都会对新表 user_new 进行相同的操作；
3. 分批将原表 user 中的数据 insert 到新表 user_new，直至数据迁移完成；
4. 删掉触发器，把原表移走（默认是 drop 掉）；
5. 把新表 user_new 重命名（rename）成原表 user；扩充字段完成，整个过程不需要锁表，可以持续对外提供服务。

• 变更过程中，写操作需要建立触发器，所以如果原表已经有很多触发器，方案就不行（互联网大数据高并发的在线业务，一般都禁止使用触发器）；

• 触发器的建立，会影响原表的性能，所以这个操作必须在流量低峰期进行；

这个自增主键用完了该怎么办?

把自增主键的类型改为BigInt类型就好了，int范围20亿，一般不会用完，用完前早就分库分表，采用分布式id了

pt-online-schema-change/gh-ost

数据库范式

第一范式：表的每一列都是不可分割。

第二范式：1NF基础上，确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关。

第三范式：2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

第四范式：消除多值依赖。例如，职工表（职工编号，职工孩子姓名，职工选修课程），在这个表中，同一个职工有多个职工孩子姓名，也会有多个职工选修课程，不符合第四范式

mysql有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问，由mysql_install_db脚本初始化。

1. user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

2. db权限表：记录各个帐号在各个数据库上的操作权限。

3. table_priv权限表：记录数据表级的操作权限。

4. columns_priv权限表：记录数据列级的操作权限。

5. host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。

视图

虚拟的表，只包含动态检索数据的查询；不包含任何列或数据。

视图创建后，可以使用与表相同的方式利用它们。

视图不能被索引，也不能有关联的触发器或默认值

• 视图是由实表产生虚表。
• 视图的建立和删除不影响实表。对视图内容的更新（添加，删除和修改）影响实表。
• 视图的列可以来自不同的表。当视图来自多个实表时，不允许添加和删除数据。

视图的使用场景有哪些？

常见使用场景

1. 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
2. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
3. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

• 简化sql查询，提高开发效率。

• 数据安全性。能够对机密数据提供安全保护

视图的缺点

• 性能。数据库必须把视图的查询转化成对基本表的查询，如果视图是由复杂的多表查询，视图的查询需要花费一定的时间。
• 修改限制。修改、插入、删除视图的某些行时，数据库把它转化为对基本表某些行的修改。对于比较复杂的视图，可能是不可修改的。

游标

• 系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
• 用户可以通过游标，逐一获取记录，进一步处理。

事务

what？

一个不可分割的数据库操作序列，是数据库并发控制的基本单位。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性

• atomicity（原子性） ：要么全执行，要么全都不执行
• consistency（一致性）：在事务开始和完成时，数据都必须保持一致状态
• isolation（隔离性） ：事务处理过程中的中间状态对外部是不可见的
• durability（持久性） ：事务完成之后，它对于数据的修改是永久性的

怎么保证一致性的

• 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。

• 应用层，在事务里不能写违反约束的代码。

怎么保证原子性的？

当事务回滚时，撤销所有已经成功执行的sql语句，需要记录你要回滚的相应日志信息。

例如

• 当delete一条数据，回滚的时候，insert这条旧数据
• 当update一条数据，回滚的时候，根据旧值执行update操作
• 当insert一条数据，回滚的时候，根据主键执行delete操

undo log记录了这些回滚需要的信息，当事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

怎么保证持久性的？

1. MySQL Server 层的执行器调用 InnoDB 存储引擎的数据更新接口；
2. 存储引擎更新 Buffer Pool 中的缓存页
3. 同时存储引擎记录一条 redo log 到 redo log buffer 中，并将该条 redo log 的状态标记为 prepare 状态；
4. 接着存储引擎告诉执行器，可以提交事务了。执行器接到通知后，会写 binlog 日志，然后提交事务；
5. 存储引擎接到提交事务的通知后，将 redo log 的日志状态标记为 commit 状态；
6. 接着根据 innodb_flush_log_at_commit 参数的配置，决定是否将 redo log buffer 中的日志刷入到磁盘（真正的事务提交）。

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

脏读：读取未提交的事务。

不可重复读：多次读取同一数据，读取的数据不一致。

幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

事务的隔离级别

• 未提交读（Read Uncommited）：在一个事务提交之前，它的执行结果对其它事务也是可见的。
• 提交读（Read Commited）：一个事务只能看见已经提交的事务所作的改变。
• 可重复读（Repeatable Read）：可以确保同一个事务在多次读取同样的数据时得到相同的结果。（MySQL的默认隔离级别）。
• 可串行化（Serializable）：强制事务串行执行，使之不可能相互冲突，从而解决幻读问题。可能导致大量的超时现象和锁竞争，实际很少使用。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

事务隔离机制的实现基于锁机制和并发调度。并发调度使用的是MVCC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

可重复读/MVCC如何实现？

可重复读是指：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

MVCC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

可重复读隔离级别下，事务在启动的时候就”拍了个快照“。

• InnoDB 里面每个事务都有一个唯一的事务 ID。它在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

• 每条记录在更新的时候都会同时记录一条 undo log，这条 log 就会记录上当前事务的 transaction id，记为 row trx_id。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

• 在可重复读隔离级别下，一个事务在启动时，InnoDB 会为事务构造一个数组，用来保存这个事务启动瞬间，当前正在”活跃“的所有事务ID。”活跃“指的是，启动了但还没提交。

• 视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

  • 如果 trx_id 小于低水位，表示这个版本在事务启动前已经提交，可见；

    如果 trx_id 大于高水位，表示这个版本在事务启动后生成，不可见；

    如果 trx_id 大于低水位，小于高水位，分为两种情况：

    1. 若 trx_id 在数组中，表示这个版本在事务启动时还未提交，不可见；
    2. 若 trx_id 不在数组中，表示这个版本在事务启动时已经提交，可见。

锁

并发事务可能会产生数据的不一致，需要锁机制来保证访问的次序。

锁分类

行级锁

分为共享锁和排他锁。

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

表级锁

分为表共享锁与表排他锁。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

页级锁（BDB引擎 ）

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

锁类别，有共享锁和排他锁。

共享锁： 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁： 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。其他的排他锁，共享锁都相斥。

意向锁

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

什么时候加行锁？

• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给相关数据集加排他锁；
• 对于普通SELECT语句，InnoDB不会加任何锁；
• 可以显示加锁：
  共享锁：select * from tableName where ... lock in share more
  排他锁：select * from tableName where ... for update

什么时候加表锁

InnoDB默认采用行锁，没有使用索引字段查询时，会使用表锁。

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

也可手动加锁：

lock table xxx read/write;

什么时候加间隙锁？

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，加间隙锁

两阶段锁

行锁是在需要的时候才加上的，而是要等到事务结束时才释放。

启发：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

InnoDB存储引擎的锁的算法

Record lock：单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：record+gap 锁定一个范围，包含记录本身

死锁

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

Mysql死锁策略

• 直接进入等待，直到超时，超时时间innodb_lock_wait_timeout
• 发起死锁检测，发现死锁，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行，将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

常见的避免死锁的方法

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大减少死锁。

2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录

3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

4. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定粒度，通过表级锁定来减少死锁产生的概率；

5. 在RR隔离级别下，如果两个线程同时对相同条件记录用 SELECT...FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加间隙锁成功，程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁，这种情况下，将隔离级别改成RC不会产生间隙锁，就可避免问题

6. 可以用使用乐观锁

死锁的必要条件

1. 互斥条件：一个资源每次只能被一个进程使用。
2. 请求与保持条件：进程因请求资源而阻塞时，保持已获得的资源的占用。
3. 不剥夺条件：进程已占用的资源，在末使用完之前，不能强行剥夺。
4. 循环等待条件：若干进程之间存在一种循环等待关系。

如何避免互相转账的死锁问题

1、破坏请求与保持

单机下，可以使用同步方法，对两个账户同时加锁。处理请求前需要两个账户都没有锁的情况下才可以

2、破坏不剥夺条件

超时：在一段时间之内没有获取到锁，不是进入阻塞状态，而是返回一个错误，那这个线程也有机会释放曾经持有的锁。

非阻塞地获取锁：如果尝试获取锁失败，并不进入阻塞状态，而是直接返回，那这个线程也有机会释放曾经持有的锁。

3、破坏循环等待条件

可以将需要获取的锁资源排序，按照顺序获取，这样就不会多个线程交叉获取相同的资源导致死锁，而是在获取相同的资源时就等待，直到它释放。

比如根据账号的主键 id 进行排序，从小到大的获取锁，这样就可以避免循环等待。

乐观锁和悲观锁是什么？怎么实现的？

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询数据的时候就把事务锁起来，直到提交事务。

实现方式

使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否有别的事务更新这个数据，如果被更新过，则失败重试。

实现方式

加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段，更新前比较是否一致

两种锁的使用场景

乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

一般多写的场景下用悲观锁就比较合适，乐观锁需要反复重试。

数据库中加锁的具体实现？

InnoDB 行锁是通过给索引项加锁来实现的。

只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

写锁一定阻塞读吗？

默认是 MVCC 机制，是不上锁的。

mysql怎么实现分布式锁

唯一索引

实现

• 获取锁时在数据库中insert一条数据，包括id、方法名（唯一索引）、线程名（用于重入）、重入计数
• 获取锁如果成功则返回true
• 获取锁的动作放在while循环中，周期性尝试获取锁直到结束或者可以定义方法来限定时间内获取锁
• 释放锁的时候，delete对应的数据

做一个定时任务，每隔一定时间把数据库中的超时数据清理一遍。实现锁失效处理。

优点：

• 实现简单、易于理解

缺点

• 并发量大的时候请求量大，获取锁的间隔，如果较小会给系统和数据库造成压力；

排他锁

实现

• 获取锁可以通过，在select语句后增加for update，数据库会在查询过程中给数据库表增加排他锁。当某条记录被加上排他锁之后，其他线程无法再在该行记录上增加排他锁，我们可以认为获得排它锁的线程即可获得分布式锁；
• 其余实现与使用唯一索引相同；
• 释放锁通过connection.commit()操作，提交事务来实现。

优点

• 实现简单、易于理解

缺点

• 排他锁会占用连接，产生连接爆满的问题

乐观锁

实现

一般是通过为数据库表添加一个 version 字段来实现读取出数据时，将此版本号一同读出.

之后更新时，对此版本号加1，在更新过程中，会对版本号进行比较，如果是一致的，没有发生改变，则会成功执行本次操作；如果版本号不一致，则会更新失败。

缺点：

1. 这种操作方式，使原本一次的update操作，必须变为2次操作
2. 增加了数据库的冗余
3. 不支持高并发，秒杀场景行锁压力大，CAS重试等等

存储过程

存储过程是一个预编译的SQL语句，只需要创建一次，就可以调用多次。

优点

1. 存储过程是预编译过的，执行效率高。

2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3. 安全性高，执行存储过程需要有一定权限的用户。

4. 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1. 移植问题

2. 重新编译问题

3. 维护麻烦

触发器

什么是触发器

一类由事件驱动的特殊的存储过程。触发器是当触发某个事件时，自动执行某段代码。

使用场景

可以通过数据库中的相关表实现级联更改。

六种触发器

Before Insert、After Insert、Before Update、After Update、Before Delete、After Delete

常用SQL语句

SQL语句主要分为哪几类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

数据查询语言DQL（Data Query Language）SELECT

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

主键 超键 候选键 外键

• 主键：存储数据对象予以唯一和完整标识的数据列或属性的组合。只能有一个主键，且主键的取值不能缺失，不能为空值

• 外键：在一个表中存在的另一个表的主键称此表的外键。

• 超键：能唯一标识的属性集称为超键。候选键和主键一定是超键。

• 候选键：是最小超键，即没有冗余元素的超键。

SQL 约束

NOT NULL： 字段的内容一定不能为空。

UNIQUE： 字段内容不能重复，一个表允许有多个 Unique 约束。

PRIMARY KEY： 设置主键。主键的取值不能缺失，不能为空值

FOREIGN KEY： 预防破坏表之间连接的动作，也能防止非法数据插入外键列。

CHECK： 用于控制字段的值范围。

关联查询

交叉连接

内连接

• 等值连接：ON A.id=B.id

• 不等值连接：ON A.id > B.id

• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接

• 左外连接：以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN

• 右外连接： 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并

如果使用UNION ALL，不会合并重复的记录行，效率 UNION ALL 高于 UNION

什么是子查询

一条SQL语句的查询结果做为另一条查询语句的条件。多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

子查询的三种情况

• 子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符

• 子查询是多行单列的情况：结果集类似于一个数组，父查询使用in

• 子查询是多行多列的情况：结果集类似于一张虚拟表，select子句

in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

• IN适合于外表大而子查询表小的情况。

• EXISTS适合于外表小而子查询表大的情况，exists是对外表作loop循环，每次loop循环再对内表进行查询。

drop、delete、truncate

drop直接删掉表，truncate、delete删除表中数据。

1. delete 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中。truncate table则一次性删除所有数据，不保存日志，删除行是不能恢复的。

2. 表和索引所占空间。当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

3. 应用范围。truncate只能对table，delete可以是table和view

使用场景：

不再需要一张表的时候，用drop

想删除部分数据行时候，用delete，并且带上where子句

保留表而删除所有数据的时候用truncate

	Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

SQL优化

如何定位及优化SQL语句的性能问题？

explain命令来查看语句的执行计划 。type访问类型可以看到ALL 扫描全表数据、index 遍历索引、range 索引范围查找；key ，实际使用的索引；ref 表的连接匹配条件；rows ，估算的结果集数目；extra Using index 使用覆盖索引，Using where 使用了用where子句来过滤结果集。

select_type查询类型。

select_type	description
SIMPLE	不包含任何子查询或union等查询
PRIMARY	包含子查询最外层查询就显示为 PRIMARY
SUBQUERY	在select或 where字句中包含的查询
DERIVED	from字句中包含的查询
UNION	出现在union后的查询语句中
UNION RESULT	从UNION中获取结果集，例如上文的第三个例子

type访问类型

ALL 扫描全表数据

index 遍历索引

range 索引范围查找

index_subquery 在子查询中使用 ref

unique_subquery 在子查询中使用 eq_ref

ref_or_null 对Null进行索引的优化的 ref

fulltext 使用全文索引

ref 使用非唯一索引查找数据

eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

key ，实际使用的索引。

ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows ：估算的结果集数目，并不是一个准确的值。

extra 的信息非常丰富，常见的有：

• Using index 使用覆盖索引

• Using where 使用了用where子句来过滤结果集

• Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。

• Using temporary 使用了临时表

超大分页怎么处理？

类似于select * from table where age > 20 limit 1000000，10

• 修改为select * from table where id in (select id from table where age > 20 limit 1000000,10)。这样虽然也load了一百万的数据，但是由于索引覆盖，要查询的所有字段都在索引中，所以速度会很快.
• 如果ID连续，我们还可以select * from table where id > 1000000 limit 10效率也是不错的

mysql 分页

LIMIT 接受一个或两个数字参数。返回记录行的偏移量+ 返回记录行的最大数目

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

varchar怎么实现

VARCHAR需要使用1或者2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

SQL语句优化

• 尽量避免全表扫描，首先应考虑在 where 、JOIN ON、 order by 涉及的列上建立索引。
• 不使用SELECT *，只查询必须的字段，避免加载无用数据，无法使用覆盖索引。
• 能用UNION ALL的时候就不用UNION，UNION过滤重复数据要耗费更多的cpu资源。

避免索引失效

• 使用!= 或者 <> 或者或者or 来连接条件导致索引失效：需要判断索引成本
• 筛选字段上的函数、运算符，或者条件判断时前后类型不一致，导致的索引失效
• 模糊搜索的前缀模糊导致的索引失效
• NOT IN、NOT EXISTS导致索引失效：需要判断回表成本
• 尽量避免在 where 子句中对字段进行 null 值判断

数据库优化

• 使用索引、优化SQL 语句、分析慢查询
• 使用缓存，节约磁盘 IO
• 优化硬件，采用 SSD，使用磁盘队列技术（RAID0,RAID1,RDID5）等
• 采用 MySQL 内部自带的表分区技术，把数据分成不同的文件，能够提高磁盘的读取效率
• 主从读写分离
• 垂直分表，把一些不经常读的数据放在一张表里，节约磁盘 I/O
• 水平分表，数据路由

left join 原理

Simple Nested-Loop Join

双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果

Index Nested-Loop Join

通过外层表匹配条件，内层表索引匹配，避免和内层表的每条记录去进行比较，提升了 join的性能。

Block Nested-Loop Join

通过一次性缓存外层表的多条数据，以此来减少内层表的扫表次数，从而达到提升性能的目的。如果无法使用Index Nested-Loop Join的时候，默认使用的是Block Nested-Loop Join

基于后两者的时间复杂度，考虑小表驱动大表。Simple Nested-Loop Join没有时间上的差异。

Batched Key Access

MRR

尽量使用顺序读盘。如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

MRR能够提升性能的核心在于，查询语句在索引的是范围查询，可以得到足够多的主键id。排序后，再去主键索引查数据，才能体现出“顺序性”的优势。

结合NLJ和BNL，将驱动表中相关列放入 join_buffer 中，批量将关联字段的值发送到 Multi-Range Read（MRR） 接口，MRR 通过接收到的值，根据其对应的主键 ID 进行排序，然后再进行数据的读取和操作，返回结果给客户端。

集群

主从复制

异步复制

• 在主库开启 binlog 的情况下，如果主库有增删改的语句，会记录到 binlog 中。
• 主库通过 IO 线程把 binlog 里面的内容传给从库，主库给客户端返回 commit 成功（不管从库是否已经收到了事务的 binlog）
• 从库的 SQL 线程负责读取 relay log 并应用到从库数据库中。

半同步复制

• 在主库开启 binlog 的情况下，如果主库有增删改的语句，会记录到 binlog 中。
• 主库通过 IO 线程把 binlog 里面的内容传给从库，从库收到 binlog 后，发送给主库一个 ACK，表示收到了，主库收到这个 ACK 以后，才能给客户端返回 commit 成功
• 从库的 SQL 线程负责读取 relay log 并应用到从库数据库中。

galera复制原理

Galera采用的是多主同步复制。

事务在本节点乐观执行，然后在提交时运行一个验证过程以保证全局数据一致性。

所谓乐观执行是指，事务在一个节点提交时，被认为与其它节点上的事务没有冲突，首先在本地执行，然后再发送到所有节点做冲突检测，无冲突时在所有节点提交，否则在所有节点回滚。

分区

在本地针对表的分区进行操作，它可以将一张表的数据分别存储为多个文件。

分区对于应用是透明的，只是数据库对于数据的重新整理。

优点

• 在执行查询的时候，优化器根据分区定义过滤部分数据分区，查询只需要查找包含需要数据的分区即可。
• 相关的数据存放在一起，想要一次批量删除整个分区的数据也会变得很方便。

缺点

• 分区字段的选择有限制。分区字段必须是整数类型或解析为整数的表达式
• 若查询不走分区键，则可能会扫描所有分区，效率不会提升。
• 若数据分布不均，分区大小差别较大，可能性能提升也有限。

垂直拆分

一般情况下，应该先考虑垂直拆分，垂直可以理解为分出来的库表结构是互相独立各不相同的。

• 如果有多个业务，每个业务直接关联性不大，那么久可以把每个业务拆分为单独的实例，库或表。

• 如果在一个实例上，有多个数据库，那么从分摊压力的角度考虑，可以把每个数据库才分到单独的实例上。

• 如果在一个库里面有多张表，那么可以把每张表拆分到不同的实例上。

• 如果你有一张表，但这个表里的字段很多，每个字段都有不同的含义，那么当该表太大的时候，就可以把每个字段独立拆分为一张新表。

水平拆分

水平拆分是针对一张表来说的。在经过垂直拆分之后，如果数据量仍然巨大，如注册用户已经超过10亿，那么治好通过某种算法进行水平拆分。拆分后的结果是多张具有相同表结构的表，每张表里面存储一部分数据

Sharding策略

• 哈希取模：hash(key)% N
• 范围： ID 范围or时间范围；
• 映射表：使用单独的一个数据库来存储映射关系。

Sharding 存在的问题

1. 事务问题

使用分布式事务来解决，比如 XA 接口。

2. 连接

可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。

3. ID 唯一性

• 使用全局唯一 ID（GUID）
• 为每个分片指定一个 ID 范围
• 分布式 ID 生成器（如 Twitter 的 Snowflake 算法）

Twitter的分布式自增ID算法Snowflake ，就是毫秒级时间戳41位、 机器ID 10位、同一毫秒内序列号12位。保证全局唯一，单机递增。

时间问题回拨的解决方法：

1. 当回拨时间小于15ms，就等时间追上来之后继续生成。
2. 当时间大于15ms时间我们通过更换workid来产生之前都没有产生过的来解决回拨问题。

全局自增ID

可以基于redis INCR实现。比较适合使用 Redis 来生成每天从0开始的流水号。比如订单号 = 日期 + 当日自增长号。可以每天在 Redis 中生成一个 Key ，使用 INCR 进行累加。

mycat分库分表原理

拦截了用户发送过来的SQL语句，对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

分库分表的原则

1、能不分就不分：升级硬盘，升级内存，升级CPU，升级网络，升级数据库版本，读写分离及负载均衡等

2、数据量太大，正常的运维影响正常的业务访问：如果某张表过大，对此表做DDL的时候，mysql会锁住全表，时间会很长；整个表的热点数据，数据访问和更新频繁，经常有锁等待

3、某些数据表出现了无穷增长的情况

4、业务耦合性考虑

平滑扩容

精确分片算法

• 业务前缀固定为4位，6-10位为分库位和分表位置，跟着是时间和自增（自增潜在一定的数据增量泄露风险，分库分表位以后保证不重复即可）
• 当我们需要扩容，增加数据库，增大生成范围就能满足。需要注意的问题是，新增的数据库，创建数据表数量需要保持一致（例如每个库的分片表统一都为4个表，增加表需要所有库都同步增加到相应的数量）
• 设置权重，可以控制各个库写入的数量

双倍扩容策略

• 扩容前每个节点的数据，有一半要迁移至一个新增节点中，对应关系比较简单。
• 增两个数据库 A2/B2 作为从库，设置主从同步关系为：A=>A2、B=>B2，直至主从数据同步完毕（早期数据可手工同步）；
• 调整分片规则并使之生效
• 解除数据库实例的主从同步关系，并使之生效
• 四个节点的数据都已完整，只是有冗余（多存了和自己配对的节点的那部分数据），择机清除即可

range+hash

hash是可以解决数据均匀的问题，range可以解决数据迁移问题

range划分不同的组，然后再做hash

扩容就是新增range就可以了

备份

mysqldump工具备份

支持基于InnoDB的热备份，–single-transaction。但由于是逻辑备份，所以速度不是很快，适合备份数据量比较小的场景。

使用percona提供的xtrabackup（推荐）

支持InnoDB的物理热备份，支持完全备份，增量备份，而且速度非常快，而且支持InnoDB引擎的数据在不同数据库迁移

sql语句中where与having的区别

• Where 约束来自数据库的数据，Where是在结果返回之前起作用的，Where中不能使用聚合函数。

• Having返回结果集以后对结果过滤，在Having中可以使用聚合函数。

在查询过程中聚合语句（sum,min,max,avg,count）要比having子句优先执行。而where子句在查询过程中执行优先级高于聚合语句。

select 列 from
表名
join [表名]
on [条件]
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

执行顺序

　　　　 　　　　1. FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1
　　　　 　　　　2. ON:对vt1表应用ON筛选器只有满足 join_condition 为真的行才被插入vt2

　　　　 　　　　 　　　　 　　　　3. OUTER（join）：如果指定了 OUTER JOIN保留表中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。
　　　　 　　　　 　　　　 　　　　4. WHERE：对vt3应用 WHERE 筛选器只有使 where_condition 为true的行才被插入vt4
　　　　 　　　　 　　　　 　　　　5. GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5
　　　　 　　　　 　　　　 　　　　6. HAVING：对vt6应用HAVING筛选器只有使 having_condition 为true的组才插入vt7
　　　　 　　　　 　　　　 　　　　7. SELECT：处理select列表产生vt8
　　　　 　　　　 　　　　 　　　　8. DISINCT：将重复的行从vt8中去除产生vt9
　　　　 　　　　 　　　　 　　　　9. ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

高并发时，如何避免重复插入 ？

• 幂等：保证多次同意请求后结果一致
• 并发控制：单表唯一索引、分布式多表分布式锁