扛得住的 MySQL 数据库性能优化

遵循“先抽象后具体，先软件后硬件”原则，通过数据库结构设计、SQL 优化、存储引擎、操作系统以及服务器硬件等方面，设计方案以优化数据库性能。

数据库性能分析

数据库性能优化顺序：原则是先抽象后具体，先软件后硬件。
- Case.01：数据库结构设计与 SQL 优化
- Case.02：存储引擎选型
- Case.03：操作系统选型
- Case.04：服务器硬件升级

数据库结构设计

过分的范式化设计会造成太多表关联，影响查询效率。过分的反范式化设计会造成表中太多冗余列。在项目当中要以实际场景出发合理设计数据表。
避免在 OLTP 环境中使用不恰当的分区表。
使用外键保证了数据完整性，但对于表维护等操作的效率影响是尤为明显的。

数据库存储引擎

围绕事务、备份、锁粒度等方面选择合适的数据库存储引擎。

MySQL 如何选择合适的存储引擎：存储引擎

服务器硬件

CPU 方案

对于高并发场景，CPU 多核心数量比 CPU 单核心频率更重要。
对于复杂 SQL 以及 CPU 密集型场景，CPU 单核心频率更重要。

内存方案

理论上，内存大小、内存频率的选型方案以硬件支持的最大性能输出最好。

磁盘方案

使用 RAID 增强传统机械硬盘性能：磁盘冗余阵列（RAID），利用多块磁盘实现数据的并发读写和数据备份，主要为了改善磁盘的访问延迟，增加磁盘的可用性和容错能力。

RAID
- RAID 0：根据磁盘将数据分为 n 份，数据同时并发写入 n 快磁盘，使得数据整体写入速度是一块磁盘的 n 倍。同理，RAID 0 也具有极速的读速度。
  
  但 RAID 0 不做数据备份，n 块硬盘中只要有一块损坏，数据完整性即被破环，所有磁盘的速度都会损坏。
- RAID 1：一份数据同时写入两块硬盘，这样任何一块硬盘损坏都不会导致数据丢失，插入一块新磁盘可通过复制数据方式自动修复。
- RAID 10：将所有磁盘平均分成两份，数据同时在两份磁盘写入（相当与 RAID 1）。但每一份数据在 n/2 块磁盘上，利用 RAID 0 技术并发读写。
  
  结合 RAID 0 和 RAID 1 两种方案，但 RAID 10 磁盘利用率较低（一半磁盘用来备份数据）。
- RAID 3：一般情况，一台服务器不会同时损坏两块磁盘的情况（物理因素除外），假设在只损坏一块磁盘情况下，利用其他磁盘的数据回复损坏磁盘的数据，即保证了可靠性和性能，同时提升了磁盘利用率。
  
  将数据分成 n-1 份，并发写入 n-1 块磁盘，并在第 n 块磁盘记录校验数据，任何一块磁盘损坏（包含校验数据磁盘），都可利用其他 n-1 块磁盘恢复数据。
- RAID 5：原理与 RAID 3 相似，但校验数据不是写入第 n 块磁盘，而是螺旋式地写入所有磁盘中。即检验数据也平均到所有磁盘上，避免了频繁写坏一块磁盘。
- RAID 6：原理与 RAID 3 相似，数据写入 n-2 块磁盘中，并螺旋式地在两块磁盘中写入校验信息（使用不同算法生成）。
使用固态存储 SSD 与 PCIe 卡
- 较于机械磁盘有较好随机读写性能
- 解决单线程负载 I/O 性能瓶颈问题
使用网络存储 NAS 与 SAN：两种外部文件存储设备加载到服务器上的方法
- NAS（Network-Attached Storage）：NAS 设备使用标准以太网连接，NAS 通过基于文件协议来访问数据，比如 NFS / SMB 协议。
- SAN（Storage Area Network）：SAN 设备通过光纤连接，SAN 在块级别存储数据。
  
  通俗理解，SAN 是网络上的硬盘，NAS 则是网络上的文件系统。

数据库结构设计

设计的目的

尽量避免数据维护中出现更新、插入以及删除异常。

学号	姓名	年龄	课程号	课程	学分
1	张三	20	C1	马克思基本原理概论	3.0
2	张三	20	C2	数据结构	5.0
3	李四	21	C3	高等数学	5.0
4	王五	19	C2	数据结构	5.0

插入异常：表中某个实体随着另一个实体而存在。

比如：插入一门新课程 (C4, 论文写作, 2.0)，但此课程并暂没有学生选修，此时会报 “ ‘学号’ doesn’t have a default value” 异常。
更新异常：更改表中某个实体的单独属性时，需对多行进行更新。

比如：修改课程 “数据结构” 的学分为 4.5，则多条数据更新异常。
删除异常：删除表中某一实体则会导致其他实体消失。

比如：删除 “学号=4” 的记录，”高等数学” 这门课随之删除。

减少数据冗余
提高查询效率
节省存储空间

设计的步骤

需求分析：存储需求、数据处理需求、数据的安全性以及完整性
逻辑设计：设计数据的逻辑存储结构
- 数据实体之间的逻辑关系，解决数据冗余和数据维护异常
- 范式设计准则
物理设计：根据所使用的数据库特征进行表结构设计
维护优化：索引、存储结构优化等

范式设计

范式化设计

第一范式（1NF）：
- 属性不可分，即数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
- 单一属性列由基本的数据类型所构成。
第二范式（2NF）：
- 表中只具有一个主键。
- 每个非主属性完全函数依赖于键码，而不能存在非主属性部分依赖于键码。
  
  比如，复合主键中常包括两种实体，如上述例子中的学生实体与课程实体，它们各自的属性依赖于各实体的主键。
第三范式（3NF）：
- 非主属性即不部分依赖，也不传递函数依赖于键码。
  
  简而言之，第三范式就是属性不依赖于其它非主属性。
- 上述的关系中存在以下传递函数依赖：学号 $\to$ 课程 $\to$ 学分。

反范式设计

反范式化：鉴于性能和读取效率考量，适当违反数据库范式设计要求，允许少量数据冗余。

优劣比较

优劣	范式化	反范式化
优势	1) 可尽量减少数据冗余 2) 更新操作较反范式化快	1) 减少表关联查询 2) 更好进行索引优化
劣势	1) 多表关联查询 2) 难以进行索引优化	1) 存在数据冗余及数据维护异常

物理设计

存储引擎

选择合适的存储引擎

存储引擎	事务	锁粒度	忌用
MyISAM	不支持	支持并发插入的表级锁	读写操作频繁
Innodb	支持	行级锁	--
Archive	不支持	支持并发插入的表级锁	随时读取、更新、删除
Ndb Cluster	支持	行级锁	--

InnoDB
- MySQL 默认的事务型存储引擎，只有在它不支持某些特性时，才考虑其它存储引擎。
- 实现了四个标准的隔离级别，默认级别是可重复读 (REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制 (MVCC) + Next-Key Locking 防止幻影读。
- 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
  
  聚簇与非聚簇的区别在于：数据文件与索引文件是否分开存储。比如非聚簇索引，索引文件的叶子节点的 data 域仅保存数据记录地址。
- 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
- 支持真正的 在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
MyISAM
- 管理非事务表，是 ISAM (Indexed Sequential Access Method，有索引的顺序访问方法) 的扩展格式。
- 不是事务安全的，且不支持外键。
- 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入。
- 可手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

数据类型

整数类型

描述	列类型	存储空间	显示宽度（默认值 M）	取值范围
标准整型	int	4 字节	int(11)	有符号：[-2^{31}, 2^{31}-1] 无符号：[0, 2^{32}]
大整型	bigint	8 字节	int(20)	有符号：[-2^{63}, 2^{63}-1] 无符号：[0, 2^{64}]

显示宽度和数据类型的取值范围是没有任何关系的，显示宽度指 MySQL 最大可能显示的数字个数，数值位数小于指定宽度时由空格填充。
若插入大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入。

浮点类型

描述	列类型	存储空间	显示宽度（默认值 M）	取值范围
定点数	decimal	9 个数 / 4 字节小数点占 1 字节	decimal(10, 0)	--

整个数字长度 M = 小数点左边位数 + 小数点右边位数 D，但不包括负号

字符类型

描述	列类型	存储空间	取值范围
固定长度字符串	char	255 字符	[0, 2^8-1]
可变长度字符串	text	65535 字符	[0, 2^{16}-1
非二进制字符串	varchar	65535 字符	[0, 2^{16}-1
二进制大对象	blob	--	[0, 2^{16}-1

使用比较运算符比较 char 值时，MySQL 不会考虑尾随空格，例如：=，<>，>，< 等。
当使用模式匹配检索 char 值时，LIKE 运算符会考虑尾随空格。

日期类型

列类型	存储空间	取值范围
date	3 字节	[0000-01-01, 9999-12-31]
datetime	8 字节	YYYY-MM-DD HH:MM:SS

[注意] 原则上不要使用字符串类型存储日期/时间数据！

数据库索引优化

索引的数理基础

B-Tree & B+Tree

引入背景

区别于动态查找算法，它们都是在内存中进行的，适用于小规模文件查找。B-Tree/B+Tree 则适用于磁盘等直接存取的设备上组织动态查找。
B-Tree/B+Tree 多应用于数据库索引中，而数据库索引是存储在磁盘的索引文件上的。
- 当数据量较大时，整个索引无法全部加载至内存中，只能逐一加载每一个磁盘页（对应索引树的节点）。
- 换句话说，数据库索引利用磁盘预读原理，把一个 B-Tree/B+Tree 节点大小刚好设为一个页的大小，这样一个树结点只需一次 I/O 即可完成加载。为此，检索目标一次最多需要访问 h 个结点（h 次 I/O 操作）。
  
  对于 B-Tree/B+Tree 的数据结构来说，树的高度可代表 I/O 次数，而 “矮胖” 就是 B-Tree/B+Tree 的阶，阶的大小取决于磁盘页的大小。
  
  B 树与 B+树

参考资料

B-Tree

B-Tree（Balanced Tree），一种 平衡 的多路查找树，它或是一棵空树，或满足下列特性的 m 叉树（$m \geq 3$）：
- 树中每个结点至多有 m 棵子树；
- 树中非叶子结点至少有两棵子树；
- 除根结点与叶子结点外，至少有 $\lceil m/2 \rceil$ 棵子树；
- 每个结点的关键字个数 = ${ n \, | \, \lceil m/2 \rceil \leq n \leq m-1 }$；
  
  1) 根结点至少可以有一个关键字，即关键字个数 = ${ n \, | \, 1 \leq n \leq m-1 }$。
  2) 当结点的关键字数量大于等于 m-1 时，则触发结点分裂过程，以结点中间的关键字为中心分裂成左右两部分。
- 每个结点中的关键字都按照从小到大排序，每个结点的关键字的左子树中所有关键字都小于它，而右子树中所有关键字都大于它。
- 所有叶子结点都位于同一层，或者说根到每个叶子结点的长度都相同。
  
  在实际应用中，B-Tree 的阶数 m 通常很大（$m \geq 100$），即使存储大量数据 B-Tree 高度仍然很小。

B+Tree

B+Tree 是 B-Tree 的一种变形形式，一棵 m 阶的 B+Tree 定义如下：
- 树中每个结点至多有 m 棵子树；
- 若根结点而非叶子结点，则至少有两棵子树；
- 除根结点与叶子结点外，至少有 $\lceil m/2 \rceil$ 棵子树；
  - 每个结点的关键字个数比它的孩子结点个数少 1；
    
    各种资料上对于 B+Tree 结点的关键字个数定义不同，这里取 Wikipedia 的定义（也可以同步 B-Tree 的关键字个数定义）。
- 所有叶子结点包含了全部关键字及相应记录的存储地址，按照从小到大的顺序排序，并存放在同一层的叶子节点中，而且叶子节点以指针进行链接。
- 所有 非叶子结点 仅起到 索引作用，即结点中的每一个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。

两者比较

对于 范围查找 来说，B+Tree 只需 遍历叶子节点链表 即可，B-Tree 却需要重复地 中序遍历整棵树。
B+Tree 只有叶子结点存储数据，非叶子结点只起到索引作用；B-Tree 的所有结点都存储数据。

索引的数据结构

B-Tree (平衡树, Balance Tree)：也称为 多路平衡查找树，并且所有叶子节点位于同一层。
B+Tree：
- 它不仅具有 B-Tree 的平衡性，并且可通过 顺序访问指针 来提高 区间查询 的性能。
- 在 B+Tree 中，一个节点中的 key 从左到右非递减排列，若某个指针的 $keyi$ 左右相邻分别是 $key{i-1}$ 和 $key{i+1}$，且不为 null，则该指针指向节点的所有 key 满足 $key{i-1} \leq keyi \leq key{i+1}$。
- B+Tree 与 B-Tree 最大区别是，B+Tree 的非叶子结点不保存数据，只用于索引，所有数据都保存在叶子结点中。而且叶子结点间按照从小到大顺序链接起来。如图 3-1 (b) 所示的为一棵 B+Tree。
  
  B 树与 B+树
B-Tree/B+Tree 的增删改查：
- 查找操作：首先在根节点进行 二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在 叶子节点 上进行 二分查找，找出 key 所对应的 data。
  
  二分查找要求表有序，正好 B-Tree 和 B+Tree 结点中的 key 从左到右非递减有序排列。
- 增删操作：会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

红黑树方案考量

同样是平衡树，红黑树（自平衡的二叉树）也可以用来实现索引。但在文件系统及数据库系统中，普遍采用 B+Tree 作为索引结构，主要有以下两个原因：

更少的查找次数：平衡树查找操作的时间复杂度和树高 $h$ 相关，$O(h)=O(log_dN)$，其中 d 为每个节点的出度，即 d叉树的阶。红黑树的出度 d 为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大，查找的次数也就大。
利用磁盘预读特性：

存储器原理解释推荐阅读：小土刀. 读薄CSAPP-内存与缓存. wdxtub.com
- 索引本身也很大，不可能全部存储在内存中，为此索引往往以索引文件的形式存储的磁盘上。索引查找过程中就要产生磁盘I/O消耗，即索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数。
- 为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会 预读。
- 预读过程中，即使只需一个字节，磁盘也是从这个位置开始，向后 顺序读取 一定长度的数据放入内存 (预读的长度一般为页的整倍数)。
  
  顺序读取不需要进行 磁盘寻道，只需要很短的磁盘旋转时间。
- 操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。
  
  页是计算机管理存储器的逻辑块。
- 数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。这样一来，最多花费 h 次 I/O 操作即可检索到目标。

索引的存取原理

磁盘存取原理
局部性原理与磁盘预读

MySQL 索引

索引，在 MySQL 也称为键（Key），是 存储引擎 快速找到记录的一种 数据结构。相当于图书的目录，可根据目录中的页码快速找到所需的内容。
索引，是 存储引擎层 的概念，所以不同存储引擎具有不同的索引类型和实现。下文主要以 MyISAM 和 InnoDB 两个存储引擎的索引实现方式展开讨论。

索引结构类型

B+Tree 索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。
InnoDB 的 B+Tree 索引分为 主索引（Primary key）和 辅助索引（Secondary key）。而且 InnoDB 的主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为 聚簇索引。

MyISAM 的索引方式也叫做 非聚集 的。MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
因为 B+ Tree 的 有序性，因此可用于 部分查找、范围查找、排序 和 分组。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。若不是按照索引列的顺序进行查找，则无法使用索引。

Hash 索引

Hash 索引能以 O(1) 时间进行查找，但是失去了有序性。因此无法用于排序与分组，无法用于部分查找和范围查找，只支持 精确查找。

Hash 索引仅满足 =，IN 和 <=> 查询，不能使用范围查询。因为 Hash 索引比较的是 Hash 运算后的 Hash 值，所以它只能用于等值的过滤。
InnoDB 存储引擎有一个特殊的功能叫 自适应哈希索引，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。

MyISAM 索引实现

MyISAM 引擎可使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址
MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。如图所示，为 MyISAM 的 B+Tree 主索引结构：

MyISAM 的 B+Tree 主索引结构

这里假设表一共有三列，以 Col1 为主键，则上图是一个主索引示意图。
在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复，比如在 Col2 上建立一个辅助索引，如图所示：

MyISAM 的 B+Tree 辅助索引结构
MyISAM 的索引检索方法是按照 B+Tree 搜索算法实现的，若指定的 key 存在，则取出其 data 域的值，然后以 data 域的值为地址，再以地址读取相应数据记录。

InnoDB 索引实现

InnoDB 引擎也可使用 B+Tree 作为索引结构，在 InnoDB 中数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。
InnoDB 的 B+Tree 主索引结构：索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引结构的。
- 因 InnoDB 的数据文件本身要按主键聚集的，为此 InnoDB 要求表必须有主键。
- 若没有显式指定，则 MySQL 会自动选择一个可以唯一标识数据记录的列作为主键。
- 若不存在这种列，则 MySQL 会自动生成一个隐含字段作为主键（字段长度为 6 个字节，类型为长整型）。
  
  InnoDB 的 B+Tree 主索引结构
InnoDB 的 B+Tree 辅助索引结构：索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。如图所示在 Col3 上的一个辅助索引：

使用辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

InnoDB 的 B+Tree 辅助索引结构

索引的优点缺点

优点

大大减少了服务器需要扫描的数据行数。
避免服务器进行排序和分组操作，以避免创建 临时表。

B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表。
将 随机 I/O 变为 顺序 I/O。

B+Tree 索引是有序的，会将相邻的数据都存储在一起。

缺点

索引并不是越多越好，索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效率，因为 INSERT 或 UPDATE 时有可能会 重建索引。

索引的设计原则

从索引的优、缺点考虑索引的设计原则。

忌过度索引：索引需要额外的磁盘空间，而且会降低写操作的性能。
- 在修改表内容时，索引会进行更新甚至重构，索引列越多花销时间越长。为此优化检索性能，只保持需要的索引即可。
- 经常用在 排列、分组 和 范围搜索 的列适合创建索引，因为索引是有序的。
- 经常出现在 WHERE 子句的列，或是 JOIN 连接子句中指定的列适合创建索引。
使用短索引：若对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

索引的优化策略

独立的列：在进行查询时，索引列不能是 表达式 的一部分，也不能是 函数参数，否则无法使用索引。例如下面的查询不能使用 actor_id 列的索引：
1
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。
1
2
SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;
索引列的顺序：让选择性最强的索引列放在前面。

索引的选择性：不重复的索引值和记录总数的比值最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如，从下面显示的结果中可知，customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。
1
2
3
4
5
6
7
8
9
SELECT
COUNT(DISTINCT staff_id)/COUNT(*) AS StaffId,
COUNT(DISTINCT customer_id)/COUNT(*) AS CustomerId,
COUNT(*)
FROM payment;

-- StaffId: 0.0001
-- CustomerId: 0.0373
-- COUNT(*): 16049
前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。
覆盖索引：索引包含所有需要查询的字段的值。具有以下优点：
- 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
- 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
- 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的使用场景

对于 非常小的表：大部分情况下简单的 全表扫描 比建立索引更高效；
对于 中大型的表：建立索引 非常有效；
对于 特大型的表：建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配。例如可以使用 分区技术。

创建与删除索引

创建表时：

CREATE TABLE 表名 (
	字段名1 数据类型 [完整性约束条件…],
	字段名2 数据类型 [完整性约束条件…],
	[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
	[索引名] (字段名[(长度)] [ASC | DESC])
);

CREATE 在已存在的表上创建索引：

1 2	CREATE [UNIQUE \| FULLTEXT \| SPATIAL] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC \| DESC]) ;

ALTER TABLE 在已存在的表上创建索引：

1 2	ALTER TABLE 表名 ADD [UNIQUE \| FULLTEXT \| SPATIAL] INDEX 索引名 (字段名[(长度)] [ASC \| DESC]) ;

删除索引：
1
DROP INDEX 索引名 ON 表名字;

SQL 大查询优化

使用 EXPLAIN 分析性能

使用 EXPLAIN 关键字可以模拟执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL语句的，分析查询语句可知道查询语句的性能瓶颈。
1
EXPLAIN SELECT * FROM mytable
通过 EXPLAIN 可以分析出以下结果：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询

执行计划中较重要字段含义 ( 详细可参考另一篇文章 MySQL 查询优化之 Explain )：
- select_type : 查询类型，有简单查询、联合查询、子查询等
- key : 使用的索引类型
- rows : 扫描的行数

优化数据访问

减少请求的数据量：
- 只返回必要的列：最好不要使用 SELECT * 语句。
- 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
- 缓存重复查询数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
减少服务器端扫描的行数：最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小而重要的查询任务。
1
2
# 大查询 SQL：删除历史数据的任务
# Delete from messages Where created_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);

解决方案：

# 1）一次删除一万行一般来说是一个比较高效而且对服务器影响也最小的方案
# 2）执行一次删除任务建议暂停一会再执行任务，还可以大大减少删除时锁的持有时间。
rows_affected = 0
do {
	rows_affected = do_query(
		"Delete from Messages created_time < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000"
	)
} while rows_affected > 0

分解大连接

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

减少锁的竞争
缓存效率更高

对于连接查询，若其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然不影响使用。
减少冗余记录的查询：单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

高性能和可拓展查询：在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可拓展。

例如，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机连接要更高效。

# 大连接查询示例
SELECT * FROM tag
	INNER JOIN tag_post ON tag_post.tag_id = tag.id
	INNER JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

# 分解大连接
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id IN (123, 456, 567, 9098, 8904);

巧用临时表

临时表：顾名思义，临时表只是在当前连接可见，当关闭连接时，MySQL 会自动删除临时表并释放存储空间。

# 创建临时表：表结构与数据存放于内存
Create Temporary Table tmp_user (
	username VARCHAR(16) NOT NULL
	age int(3) NULL
	user_status char(2) NOT NULL DEFAULT '1'
) ENGINE = MyISAM DEFAULT CHARSET=utf8;
insert into tmp_user values('Kofe', 26, 1);

# 创建内存表：表结构存放于磁盘上，数据存放于内存
Create Temporary Table tmp_user (
	Select * from t_user
) TYPE ＝ HEAP ENGINE=MEMORY DEFAULT CHARSET=utf8；

# 查询直接创建临时表
Create Temporary Table tmp_user (
	Select * from t_user
)；

# 删除临时表
DROP TABLE tmp_user;

使用场景：大查询或者关联查询频繁关联一些中间大结果集的子集，使用临时表可提高此类场景的查询效率。

高可用架构设计

切分

参考资料

水平切分

水平切分：将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，水平切分是必然选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

水平切分
切分方式
- Range：根据一定范围进行分发。比如：根据时间范围，一个月内的数据存储为一张表，或者根据用户 ID 这种自增序列，用户 ID 在 000001 至 100000 范围的存一张表、100001 至200000 范围的存一张表等。
  - 优势是数据扩容时方便。
  - 不足是容易产生数据热点问题。
- Hash：通过一次哈希运算然后取余（分表数量-1）的方式确定数据要存的表位置。
  
  比如：根据用户姓名进行 Hash 分发。用户姓名小明，计算 hashcode，得到754703，预先确定分表数量为 8，再取余 7 得到 3，即分发到索引为 3 表位置上。
  - 优势是数据分发均匀，不会产生数据热点问题。
  - 不足是扩容时候非常不方便，还需要重新计算数据的哈希值。

垂直切分

垂直切分：将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分。
- 利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中，让数据库可以以更少字段缓存更多的行，进而带来性能的提升。
- 数据库的三大范式设计其实也是一种垂直切分。
在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中。

垂直切分

分表分库中间件

不同的分库分表（包括水平以及垂直切分）中间件都有什么优点和缺点？

Mycat：基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议的服务器。
- 基于 Cobar 改造的、同属于 proxy 层方案。
- 前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问。
- 后端使用 MySQL 原生协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分库分表。配合数据库的主从模式还可实现读写分离。
Sharding-jdbc：属于 ShardingSphere 的 client 层方案。ShardingSphere 还提供 proxy 层的方案 Sharding-Proxy。因为 SQL 语法支持也比较多，没有太多限制，支持分库分表、读写分离、分布式 ID 生成、柔性事务（最大努力送达型事务、TCC 事务等）。

复制

主从复制

主从复制：主要涉及三个线程
- Binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  
  二进制日志：记录了所有对 MySQL 数据库修改，不限于数据增删改查、表结构修改等事件。
- I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
- SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
二进制日志格式：比如同一 SQL 修改 1000 条数据
- 基于行 binlog_format = ROW，日志记录 1000 条执行语句。
- 基于段 binlog_format = STATEMENT，日志只记录该 SQL 语句。
- 混合日志格式 binlog_format = MIXED，由系统决定是基于端还是基于行方式记录日志。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：
- 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
- 增加冗余，提高可用性。
- 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。