MySQL

MySQL是一个关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性

基础架构

连接器管理

• 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接
• 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

链接的建立过程复杂，操作中如果需要大量长时间的存取数据，使用长链接 使用长链接的缺点： 容易占用内存， 因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM）

解决长连接占用内存，短链接繁琐的问题

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

Mysql缓存

Mysql通过在内存中建立缓冲区[buffer]和缓寸[cache]来提高Mysql性能,key是查询的语句，value是查询的结果，对于InnoDB引擎，Mysql采用缓冲池[buffer pool]的方式来缓存数据和索引,对于MyISAM引擎，Mysql采用缓存的方式来缓存数据和索引,先看缓存数据，如果存在则直接返回。如果没有则直接往下走

• 关键字缓存（key cache）
  • 当Mysql收到传入的sql语句时，它首先和先前已经解析过的sql语句进行比较，如果发现相同，则返回已缓存数据。一定是完全相同,因此MySQL的查询缓存命中率很低
• 查询缓存（query cache）

SHOW variables LIKE 'have_query_cache'; # 查询mysql缓存配置信息

提示

对于一些不常改变的数据且有大量相同sql查询的表，查询缓存会节约很大的性能，对于频繁更改的表，查询缓存是不合适的

分析器

分析器对你执行的sql语句进行解析，首先是词法分析包括一些关键字识别，然后语法分析，查看这条语句是否符合mysql语句

分析器分析识别

• 词法分析(识别关键字)： 输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。 MySQL从你输入的SELECT这个关键字识别出来，这是一个查询语句。它也要把字符串"T"识别成"表名T"，把字符串"ID"识别成"列ID"
• 语法分析 (判断语法)： 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法 如果语法不对，就会收到报错提醒，一般语法错误会提示第一个出现错误的位置 即关注报错中的 user near

优化器

经过分析器的分析,Mysql就知道你要做的事情是什么了,但是，在开始执行之前，需要经过优化器的处理。发现那些查询命中索引，还有表之间的连接顺序等

优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联JOIN的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的JOIN：

SELECT * FROM T1 JOIN T2 USING(id) WHERE T1.c=10 AND T2.d=20;

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。 这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案

执行器

通过上面一系列的验证，使用引擎提供的接口。经过不断的执行将查询的结果存放在结果集中，通过EXPLAIN可以看到执行器具体扫描了多少行。

经过优化器筛选优化的查询语句后，就进入执行器阶段，开始执行语句

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示。

SELECT * FROM T WHERE ID=10; # ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了

对于有索引的表，执行的逻辑也差不多第一次调用的是"取满足条件的第一行"这个接口，之后循环取"满足条件的下一行"这个接口，这些接口都是引擎中已经定义好的。 你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的

日志系统

首先要清楚redolog和binlog两个日志模块

1. redolog（InnoDB特有的日志模块，起初InnoDB是另一个公司以插件的方式加到mysql的，拥有crash-safe故障恢复能力） 重做日志文件，用于记录事务操作的变化，记录修改后的值，不管事务是否提交。保证数据的完整性。其中redolog是固定大小的，是从头开始写，写到末尾在从头开始。同时会有两个指针，一个记录写入的位置，一个标记，当前擦除的位置，不断的循环。整个过程称为crash-safe。即时数据库异常，也会有记录
2. binlog 归档日志文件，用于记录对mysql数据库执行更改的所有操作。binlog是追加写，不会覆盖之前的。

MySQL InnoDB 更新一条语句的流程

UPDATE user_info SET name = "marco" WHERE id = 1;

1. 执行器先找引擎取出ID=1这一行，ID是主键，引擎直接用树搜索找到这一行，如果本来这一行所在的数据页就在内存中，就直接返回给执行器
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新一行数据，再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，同时将这个更新记录到redolog里面，此时redolog处于prepare状态，然后告知执行器执行任务完成了，随时可以提交事务
4. 执行器生成这个操作的binlog，并把binlog卸乳磁盘
5. 执行器调用引擎的提交事务接口，引擎把刚刚卸乳的redolog改为提交commit状态，更新完成

提示

注意：Mysql的redolog模块写入拆成2步走，prepare和commit，称为两阶段提交。 1.redolog的prepare状态 2. binlog的写入

如果binlog没有写入并没有提交事务回滚 如果binlog写入事务没提交，数据库回复后自动完成commit

基础拓扑

数据结构

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优，MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

B树、B-树、B+树

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构

B 树 即二叉搜索树

1. 所有非叶子结点至多拥有两个儿子（Left和Right)
2. 所有结点存储一个关键字；
3. 非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

B树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；否则，如果查询关键字比结点关键字小，就进入左儿子；如果比结点关键字大，就进入右儿子；如果左儿子或右儿子的指针为空，则报告找不到相应的关键字；

如果B树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么B树的搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变B树结构（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销；

但B树在经过多次插入与删除后，有可能导致不同的结构

右边也是一个B树，但它的搜索性能已经是线性的了；同样的关键字集合有可能导致不同的树结构索引；所以，使用B树还要考虑尽可能让B树保持左图的结构，和避免右图的结构，也就是所谓的“平衡”问题；
实际使用的B树都是在原B树的基础上加上平衡算法，即“平衡二叉树”；如何保持B树结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在B树中插入和删除结点的策略；

B- Tree 一种多路搜索树（并不是二叉的）

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点;

1. 关键字集合分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 其搜索性能等价于在关键字全集内做一次二分查找；
5. 自动层次控制；

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败

B+ Tree B-树的变体，也是一种多路搜索树

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找

1. 其定义基本与B-树同，除了：
2. 非叶子结点的子树指针与关键字个数相同；
3. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
4. 为所有叶子结点增加一个链指针；
5. 所有关键字都在叶子结点出现；

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

磁盘存取原理

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存 储单元。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读 由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。 程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧： 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。 B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。综上所述，用B-Tree作为索引结构效率是非常高的。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

MyISAM 索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分

InnoDB 索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

1. InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
2. 与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域 InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

InnoDB支持以下几种索引：

• 全文索引：全文索引是将存在数据库的整本书的任意内容信息查找出来的技术，InnoDB从1.2.x版本支持。每张表只能有一个全文检索的索引
• 哈希索引：哈希索引是自适应的，也就是说这个不能人为干预在一张表生成哈希索引，InnoDB会根据这张表的使用情况来自动生成。
• B+树索引：B+树索引是传统意义上的索引，B+树索引并不能根据键值找到具体的行数据，B+树索引只能找到行数据所在的页，然后通过把页读到内存，再在内存中查找到行数据。B+树索引也是最常用的最为频繁使用的索引。

聚集索引

聚集索引是按表的主键构造的B+树，叶子节点存放的为整张表的行记录数据，每张表只能有一个聚集索引。优化器更倾向采用聚集索引。因为直接就能获取行数据。 请选择自增id来做主键，不要非空UK列。避免大量分页碎片。下面来看一个聚集索引的图： 那么很简单了，每个叶子节点，都存有完整的行记录。对于主键的查找速度那是相当的快，美滋滋

辅助索引

辅助索引也叫非聚集索引，叶子节点除了键值以外还包含了一个bookmark，用来告诉InnoDB在哪里可以找到对应的行数据，InnoDB的辅助索引的bookmark就是相对应行数据的聚集索引键。也就是先获取指向主键索引的主键，然后通过主键索引来找到一个完整的行。如果辅助索引的树和聚集索引的树的高度都是3，如果不是走主键索引走辅助索引的话，那么需要6次逻辑IO访问得到最终的数据页

一颗聚集索引B+树可以放多少行数据？

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。假设一行记录的数据大小为1k，那么单个叶子节点（页）中的记录数=16K/1K=16。

那么现在我们需要计算出非叶子节点能存放多少指针，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16kb/14b=1170。那么可以算出一棵高度为2的B+树，大概能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树大概可以存放：1170117016=21902400行数据。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次逻辑IO操作即可查找到数据。

隔离级别

事务基本要素 ACID

1. 原子性 (Atomicity）事务开始后所有操作，要么全部做完，要么全部不做，事务在执行过程中，若有错误，会回滚到事务开始前到状态，所有的操作就像没有发生一样，也就是事务是一个不可分割的整体，与化学中的原子一样，是物质构成的基本单位
2. 一致性 (Consistency）事务开始前和结束后，数据库的完整性约束不会被破坏
3. 隔离性 (Isolation) 同一时间，只允许一个事务请求同一数据，不同实务之间彼此没有任何干扰
4. 持久性 (Durability) 事务完成后，事务对数据库所有更新将保存到数据库，不能回滚

事务的并发问题

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读到的就是脏数据（多余的）
2. 不可重复度：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据进行了更新并提交，导致事务A多次读取同一数据时，结果不一致
3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读

提示

PS：不可重复读和幻读容易混淆，不可重复度侧重于修改，幻读侧重于新增或删除，解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表

事务级别

Mysql默认的事务隔离级别为可重复读 repeatable-read

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MVCC

InnoDB

Explain

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助 选择更好的索引和写出更优化的查询语句

数据库优化

• 创建高性能的索引 这里面就多了,根据业务设计符合业务的索引
• 查询性能优化 查询之前,设计表的时候应该根据范式和业务结合设计一个好的表 查询语句使用得当,根据索引能获得较大的效果
• 优化服务器设置 根据使用情况修改MySQL默认的配置文件 根据实际情况可做一些负载的设置 配置内存使用等等

查询优化

查找分析查询速度慢点原因

1. 分析SQL查询慢的方法
2. 记录慢查询日志

优化手段

分区

分区表后，提高了MySql性能。如果一张表的话，那就只有一个.ibd文件，一颗大的B+树。如果分表后，将按分区规则，分成不同的区，也就是一个大的B+树，分成多个小的树。 读的效率肯定提升了，如果走分区键索引的话，先走对应分区的辅助索引B+树，再走对应分区的聚集索引B+树。 如果没有走分区键，将会在所有分区都会执行一次。会造成多次逻辑IO！平时开发如果想查看sql语句的分区查询可以使用explain partitons select xxxxx语句。可以看到一句select语句走了几个分区。

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+
row in set (0.00 sec)

1. 查看MySQL是否支持分区

a. MySQL5.6以及之前版本

SHOW variables LIKE '%partition%';

b. MySQL5.7

SHOW PLUGINS;

2. 分区表的分类与限制

• a. 分区表分类
  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
  • LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
  • 复合分区：在MySQL 5.6版本中，只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。
• b. 分区表限制
  • 分区键必须包含在表的所有主键、唯一键中。
  • MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。
  • 最大分区数： 不使用NDB存储引擎的给定表的最大可能分区数为8192（包括子分区）。如果当分区数很大，但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题，当然同时打开文件的数量也可能由操作系统限制。
  • 不支持查询缓存： 分区表不支持查询缓存，对于涉及分区表的查询，它自动禁用。 查询缓存无法启用此类查询。
  • 分区的innodb表不支持外键。
  • 服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同，甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果，您应该始终在主机和从机上使用相同的服务器SQL模式。
  • ALTER TABLE … ORDER BY： 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。
  • 全文索引。 分区表不支持全文索引，即使是使用InnoDB或MyISAM存储引擎的分区表。
  • 分区表无法使用外键约束。
  • Spatial columns： 具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。
  • 临时表： 临时表不能分区。
  • subpartition问题： subpartition必须使用HASH或KEY分区。 只有RANGE和LIST分区可能被分区; HASH和KEY分区不能被子分区。
  • 分区表不支持mysqlcheck，myisamchk和myisampack。

3. 创建分区表

• a. range分区

CREATE TABLE `test_range` (
  `id` int(11) NOT NULL,
  `t` date NOT NULL,
  PRIMARY KEY (`id`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE (to_days(t))
(PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);123456789

然后查看分区状态

insert into test_range values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");
SELECT PARTITION_NAME AS "分区",TABLE_ROWS AS "行数" FROM information_schema.partitions WHERE TABLE_NAME="test_range";

分区	行数
p20170801	1
p20170901	3
pmax	0

可以看出分区p20170801插入1行数据，p20170901插入的3行数据。 可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换，然后分区。

• b. list分区

CREATE TABLE test_list (
       c1 INT,
       c2 INT
     )
 PARTITION BY LIST(c1) (
 PARTITION p0 VALUES IN (1, 4, 7),
 PARTITION p1 VALUES IN (2, 5, 8)
 );

INSERT INTO test_list VALUES (4, 5);
SELECT PARTITION_NAME AS "分区",TABLE_ROWS AS "行数" FROM information_schema.partitions WHERE TABLE_NAME="test_list";

和range分区一样，只是list分区面向的是离散的值, 与RANGE分区的情况不同，没有catch-all，如MAXVALUE; 分区表达式的所有预期值应在PARTITION VALUES IN（）子句中涵盖。 包含不匹配的分区列值的INSERT语句失败并显示错误，插入的数字必须在这个范围内

• c. hash分区

根据用户自定义表达式的返回值来进行分区，返回值不能为负数

CREATE TABLE test_hash (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH(YEAR(col3))
    PARTITIONS 4;

INSERT INTO test_hash VALUES (1, '2','2020-03-08');
SELECT PARTITION_NAME AS "分区",TABLE_ROWS AS "行数" FROM information_schema.partitions WHERE TABLE_NAME="test_hash";

• d. key分区

根据MySQL数据库提供的散列函数进行分区

CREATE TABLE test_key (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

KEY仅列出零个或多个列名称。 用作分区键的任何列必须包含表的主键的一部分或全部，如果该表具有一个。如果没有列名称作为分区键，则使用表的主键（如果有）。如果没有主键，但是有一个唯一的键，那么唯一键用于分区键。但是，如果唯一键列未定义为NOT NULL，则上一条语句将失败。与其他分区类型不同，KEY使用的分区不限于整数或空值

• e. column分区
• f. 子分区（组合分区）

4. 普通表转换为分区表

用alter table table_name partition by命令重建分区表

ALTER TABLE jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8;

分库分表

当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。

mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。

垂直拆分

垂直分库（根据业务不同与微服务类似单独服务对应单独库） 垂直分表 垂直分表是基于数据库中的”列”进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过”大表拆小表”，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。 拆分字段的操作建议在数据库设计阶段就做好。如果是在发展过程中拆分，则需要改写以前的查询语句，会额外带来一定的成本和风险，建议谨慎

• 优点：
  • 解决业务系统层面的耦合，业务清晰
  • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈
• 缺点：
  • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
  • 依然存在单表数据量过大的问题（需要水平切分）
  • 分布式事务处理复杂

水平拆分

根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果

• 优点：
  • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
  • 应用端改造较小，不需要拆分业务模块

“冷热数据分离”实现方案

• 缺点：
  • 跨分片事务难以保证
  • 跨分片的复杂查询如join关联查询
  • 数据多次扩展难度和维护量极大

数据分片规则

1. 冷热数据隔离（近6个月或者1年的数据作为热数据，历史数据作为冷数据再进行时间维度拆分）
2. 地域区域或者其他拆分方式
3. userNo范围分表，比如0~500w用户在user1表，501w-1000w在user2表等

• 优点：
  • 单表大小可控
  • 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数 据进行迁移
  • 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题
• 缺点：
  • 热点数据成为性能瓶颈。连续分片可能存在数据热点

HASH取模MOD的切分方式

• 优点：根据主键id进行数据切分，达到数据均匀分布，使用一致性hash算法可以避免后期扩展问题
• 缺点：跨分片聚合操

参考文献

• MySQL索引的数据结构以及算法原理
• MySQL基本原理和使用技巧
• MySQL5.1参考手册