This the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

MySQL

1: CH01-数据类型
2: CH02-存储引擎
3: CH03-索引B+树
4: CH04-性能优化
5: CH05-分库分表
6: CH06-复制分离
7: CH07-执行过程
8: CH08-参考规约

1 - CH01-数据类型

字段类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

时间和日期

MySQL 提供了两种相似的日期时间类型: DATETIME 和 TIMESTAMP。

1. DATETIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

2. TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

选择优化的数据类型

更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；
简单就好；例如，整形比字符串操作代价更低；使用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列；

字符串类型

VARCHAR 和 CHAR

VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。

下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？）。

VARCHAR(5)和VARCHAR(200)

使用VARCHAR(5)和VARCHAR(200)存储"hello"的空间开销是一样的。那么使用更短的列有什么优势吗？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要的空间。

BLOB 和 TEXT

BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。

与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

选择标识符（identifier）

整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。
SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。

2 - CH02-存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

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

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

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

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

对比

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

3 - CH03-索引B+树

B+ Tree 原理

1. 数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1。

2. 操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入删除操作记录会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

3. 与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因:

(一)更少的查找次数

平衡树查找操作的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，检索的次数也就更多。

(二)利用计算机预读特性

为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

MySQL 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

1. B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

2. 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制:

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

3. 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引优化

1. 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引:

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2. 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3. 索引列的顺序

让选择性最强的索引列放在前面，索引的选择性是指: 不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

4. 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

对于前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点:

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎(例如 MyISAM)在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用(通常比较费时)。
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的有点

大大减少了服务器需要扫描的数据行数。

帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。

将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)。

索引的使用场景

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

4 - CH04-性能优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

Explain 结果中的字段含义:

select_type : 查询类型，有简单查询、联合查询、子查询等
- SIMPLE, 表示此查询不包含 UNION 查询或子查询
- PRIMARY, 表示此查询是最外层的查询
- UNION, 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT, UNION 的结果
- SUBQUERY, 子查询中的第一个 SELECT
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
table：查询涉及的表或衍生表
partitions：匹配的分区
type：访问类型，表示MySQL在表中查找所需行的方式。
- ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行；
- index：Full Index Scan，和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据；
  - 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段会显示 Using index.
- range：表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中；
  - 当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个；
- ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
  - 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询；
- eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；
  - 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高；
- const/system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system；
- NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys：此次查询中可能会被选用的索引，不一定真正用到。
key：此次查询中真正使用到的索引。当为复合索引时，不确定是否被充分使用。
key_len：表示索引中使用的字节数，用来计算索引是否被充分使用，不损失精确性的情况下，长度越短越好。
- key_len=字符长度*字节数+类型+是否允许为空
- 索引是否充分使用：复合索引每个列都需要计算，所有索引列都生效了才是充分利用。
- 计算规则：
  - 字节数相关：长度、字符编码、类型（int+0，char+0，varchar+2）、是否允许为空（空+1，非空+0）；
  - int类型字节数为4；
  - char和varchar的长度是指字符数，一个字符在编码gbk为2个字节、utf-8为3个字节，需要：字符数*字节。
- name varchar(50)：表示是varchar类型，长度为50，允许为空，假设是utf8编码
  - key_len=50*3+2+1=153
- emp_no int(255)：表示int类型，字节数为4，允许为空，跟长度和编码无关
  - key_len=4+0=4
ref：哪个字段或常数与 key 一起被使用
rows : 预估扫描的行数
filtered：表示此查询条件所过滤的数据的百分比
Extra：
- Using where：意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中。
  - 查询条件中的相关列，不是索引字段，全表扫描后，通过Using where过滤获取所需的数据**。**
  - 由于索引未覆盖所有查询条件，在存储引擎返回记录后，仍然需要过滤数据。
  - WHERE筛选条件不是索引的前导列，导致不走索引，而走全表扫描。
- Using index：表示直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表；
- Using index condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
- Using where; Using index：表示在索引的扫描过程中，也是需要过滤数据的（Index First Key 、Index Last Key），其实表扫描和索引扫描也是很类似的。只是发生的层面不一样。
- Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
- Using filesort： MySQL中无法利用索引完成的排序操作称为“文件排序”。

Extra 细节参考：https://www.cnblogs.com/kerrycode/p/9909093.html

使用 Explain Analyze 进行分析

MySQL 8.0.18 包含一个全新的功能 EXPLAIN ANALYZE，用来分析和理解查询如何执行。

**EXPLAIN ANALYZE 是什么？**EXPLAIN ANALYZE 是一个用于查询的分析工具，它向用户显示 MySQL 在查询上花费的时间以及原因。它将产生查询计划，并对其进行检测和执行，同时计算行数并度量执行计划中不同点上花费的时间。执行完成后，EXPLAIN ANALYZE 将输出计划和度量结果，而不是查询结果。这项新功能建立在常规的 EXPLAIN 基础之上，可以看作是 MySQL 8.0 之前添加的 EXPLAIN FORMAT = TREE 的扩展。EXPLAIN 除了输出查询计划和估计成本之外，EXPLAIN ANALYZE 还会输出执行计划中各个迭代器的实际成本。

如何使用

我们将使用 Sakila 样本数据库中的数据和一个查询举例说明，该查询列出了每个工作人员在 2005 年 8 月累积的总金额。查询非常简单：

mysql>SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

+------------+-----------+----------+
| first_name | last_name | total    |
+------------+-----------+----------+
| Mike       | Hillyer   | 11853.65 |
| Jon        | Stephens  | 12218.48 |
+------------+-----------+----------+
2 rows in set (0,02 sec)

只有两个人，Mike 和 Jon，我们在 2005 年 8 月获得了他们的总数。

EXPLAIN FORMAT = TREE 将向我们显示查询计划和成本估算：

mysql>EXPLAIN FORMAT=TREE
mysql>SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Nested loop inner join  (cost=1757.30 rows=1787)
            -> Table scan on staff  (cost=3.20 rows=2)
            -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043)

但这并不能表明这些估计是否正确，或者查询计划实际上是在哪些操作上花费的时间。EXPLAIN ANALYZE 将执行以下操作：

mysql>EXPLAIN ANALYZE
mysql>SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

-> Table scan on <temporary>  (actual time=0.001..0.001 rows=2 loops=1)
    -> Aggregate using temporary table  (actual time=58.104..58.104 rows=2 loops=1)
        -> Nested loop inner join  (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)
            -> Table scan on staff  (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)
            -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)

这里有几个新的度量：

获取第一行的实际时间（以毫秒为单位）
获取所有行的实际时间（以毫秒为单位）
实际读取的行数
实际循环数

让我们看一个具体的示例，使用过滤条件的迭代器成本估算和实际度量，该迭代器过滤 2005 年 8 月的数据（上面 EXPLAIN ANALYZE 输出中的第 13 行）。

Filter: (payment.payment_date like '2005-08%')
(cost=117.43 rows=894)
(actual time=0.464..22.767 rows=2844 loops=2)

我们的过滤器的估计成本为 117.43，并且估计返回 894 行。这些估计是由查询优化器根据可用统计信息在执行查询之前进行的。该信息也会在 EXPLAIN FORMAT = TREE 输出中。

我们将从最后面的循环数开始。此过滤迭代器的循环数为 2。这是什么意思？要了解此数字，我们必须查看查询计划中过滤迭代器上方的内容。在第 11 行上，有一个嵌套循环联接，在第 12 行上，是在staff 表上进行表扫描。这意味着我们正在执行嵌套循环连接，在其中扫描 staff 表，然后针对该表中的每一行，使用索引查找和过滤的付款日期来查找 payment 表中的相应条目。由于 staff 表中有两行（Mike 和 Jon），因此我们在第 14 行的索引查找上获得了两个循环迭代。对于许多人来说，EXPLAIN ANALYZE 提供的最有趣的新信息是实际时间“ 0.464..22.767”，这意味着平均花费 0.464 毫秒读取第一行，而花费 22.767 毫秒读取所有行。平均时间？是的，由于存在循环，我们必须对该迭代器进行两次计时，并且报告的数字是所有循环迭代的平均值。这意味着过滤的实际执行时间是这些数字的两倍。如果我们看一下在嵌套循环迭代器（第 11 行）中上一级接收所有行的时间，为 46.135 毫秒，这是运行一次过滤迭代器的时间的两倍多。这个时间反映了整个子树在执行过滤操作时的根部时间，即，使用索引查找迭代器读取行，然后评估付款日期为 2005 年 8 月的时间。如果我们查看索引循环迭代器（第 14 行），我们看到相应的数字分别为 0.450 和 19.988ms。这意味着大部分时间都花在了使用索引查找来读取行上，并且与读取数据相比，实际的过滤成本相对低廉。实际读取的行数为 2844，而估计为 894 行。优化器错过了 3 倍的因素。同样，由于循环，估计值和实际值都是所有循环迭代的平均值。如果我们查看 schema，发现 payment_date 列上没有索引或直方图，因此提供给优化器的统计信息是有限的。如果使用更好的统计信息可以得出更准确的估计值，我们可以再次查看索引查找迭代器。我们看到该索引提供了更加准确的统计信息：估计 8043 行与 8024 实际读取行。发生这种情况是因为索引附带了额外的统计信息，而这些数据对于非索引列是不存在的。

那么用户可以使用这些信息做什么？需要一定的练习，用户才可以分析查询并理解为什么它们表现不佳。但是，这里有一些帮助入门的简单提示：

如果疑惑为何花费这么长时间，请查看时间。执行时间花在哪里？
如果您想知道为什么优化器选择了该计划，请查看行计数器。如果估计的行数与实际的行数之间存在较大差异（即，几个数量级或更多），需要仔细看一下。优化器根据估算值选择计划，但是查看实际执行情况可能会告诉您，另一个计划会更好。

EXPLAIN ANALYZE 是 MySQL 查询分析工具里面的一个新工具：

检查查询计划：EXPLAIN FORMAT = TREE
分析查询执行：EXPLAIN ANALYZE
了解计划选择：OPTIMIZER TRACE

优化数据访问

0. 引擎逻辑

当全表扫描速度比索引速度快时，MySQL会使用全表扫描，此时索引失效。
表中存在多个索引时，即使where条件满足某个索引策略，MySQL查询优化器也不一定会使用该索引，可能使用其他索引，取决于性能。另外，当某个索引没有命中也不一定会走全表扫描，可能走其他索引。
理论上索引对顺序是敏感的，也就是说where子句的字段列表需要讲究顺序，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以匹配适合的索引，因此，允许我们不去刻意关注where子句的条件顺序。

1. 减少请求的数据量

只返回必要的列: 最好不要使用 SELECT * 语句。
只返回必要的行: 使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据: 使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

覆盖索引：SQL只需要通过遍历索引树就可以返回所需要查询的数据，而不必通过辅助索引查到主键值之后再去查询数据（回表操作）。

3. 遵循最左前缀匹配

联合索引命中必须遵循“最左前缀法则”。即SQL查询Where条件字段必须从索引的最左前列开始匹配，不能跳过索引中的列。联合索引又称复合索引，类似于书籍的目录，多级的目录结构中子目录依赖于父级目录存在，也是遵循“最左前缀法则”。

联合索引	INDEX idx_empno_birthdate_gender(emp_no,birth_date,gender)
等价建立的索引	实际上联合索引idx_empno_birthdate_gender等价建立了三个索引： - index_1(emp_no) - index_2(emp_no,birth_date) - index_3(emp_no,birth_date,gender)
联合索引命中的where条件字段列表	- index_1命中：emp_no 或 emp_no,gender - index_2命中：emp_no,birth_date - index_3命中：emp_no,birth_date,gender 以上where子句查询条件联合索引idx_empno_birthdate_gender都会命中，只是使用的程度不一样（走的子索引不一样），因此，联合索引有“是否充分使用”衡量指标（key_len），当然使用最充分的条件是：所有字段都命中，即使用了index_3。

联合索引

INDEX idx_empno_birthdate_gender(emp_no,birth_date,gender)

等价建立的索引

实际上联合索引idx_empno_birthdate_gender等价建立了三个索引：
- index_1(emp_no)
- index_2(emp_no,birth_date)
- index_3(emp_no,birth_date,gender)

联合索引命中的where条件字段列表

- index_1命中：emp_no 或 emp_no,gender
- index_2命中：emp_no,birth_date
- index_3命中：emp_no,birth_date,gender
以上where子句查询条件联合索引idx_empno_birthdate_gender都会命中，只是使用的程度不一样（走的子索引不一样），因此，联合索引有“是否充分使用”衡量指标（key_len），当然使用最充分的条件是：所有字段都命中，即使用了index_3。

4. 范围查询字段放最后

联合索引定义时，尽量将范围查询字段放在最后（放在最后联合索引使用最充分，放在中间联合索引使用不充分）。使用联合索引时范围列（当前范围列索引生效）后面的索引列无法生效，同时索引最多用于一个范围列，如果查询条件中有多个范围列，也只能用到一个范围列索引。

5. 不对索引字段进行逻辑操作

在索引字段上进行计算、函数、类型转换（自动\手动）都会导致索引失效。

6. 尽量全值匹配

全值匹配也就是精确匹配不使用like查询（模糊匹配），使用like会使查询效率降低。

7. Like查询，左侧尽量不要加%

like 以%开头，当前列索引无效(当为联合索引时，当前列和后续列索引不生效，可能导致索引使用不充分)；当like前缀没有%，后缀有%时，索引有效。

8. 注意null/not null 可能对索引有影响

在索引列上使用 IS NULL 或 IS NOT NULL条件，可能对索引有所影响。

字段定义默认为NULL时，NULL索引生效，NOT NULL索引不生效；
字段定义明确为NOT NULL ，不允许为空时，NULL/NOT NULL索引列，索引均失效；

列字段尽量设置为NOT NULL，MySQL难以对使用NULL的列进行查询优化，允许Null会使索引值以及索引统计更加复杂。允许NULL值的列需要更多的存储空间，还需要MySQL内部进行特殊处理。

9. 尽量减少使用不等于

不等于操作符是不会使用索引的。不等于操作符包括：not，<>，!=。

优化方法：数值型 key<>0 改为 key>0 or key<0。

10. 字符类型务必加上引号

若varchar类型字段值不加单引号，可能会发生数据类型隐式转化，自动转换为int型，使索引无效。

11. OR关键字左右尽量都为索引列

当OR左右查询字段只有一个是索引，会使该索引失效，只有当OR左右查询字段均为索引列时，这些索引才会生效。OR改UNION效率高。

12. OR关键字替换为 UNION

应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20。

他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

13. 避免使用 IN 或 NOT IN

IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

或者使用 JOIN 子查询或 EXISTS。

select num from a where num in(select num from b) 替换为 select num from a where exists(select 1 from b where num=a.num)。

14. 控制索引数量

索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15. 避免更新 clustered 索引数据列

因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

16. 尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

17. 尽可能的使用 varchar, nvarchar

使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

18. 仅返回需要的列

最好不要使用返回所有：select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

19. 使用表别名 ALIAS

当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

20. 使用“临时表”暂存中间结果

简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

21. 查询语句使用 nolock

一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。

使用 nolock 有3条原则：

查询的结果用于“插、删、改”的不能加 nolock；
查询的表属于频繁发生页分裂的，慎用 nolock ；
使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。

22. 控制查询涉及的表数量

不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。

23. 预计算

将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。

23. IN 列表时值的顺序

在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

24. 尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。

存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。

25. 服务器线程数量

当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；

否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

26. EXISTS 判断是否存在

尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。

27. 尽量使用 “>=”，不要使用 “>”

28. 批量执行插入或更新，不要逐条执行

JDBC 设置参数 rewriteBatchedStatements=true。

29. 存储过程避免循环

例如：列出上个月的每一天，用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。

30. 选择最有效率的表名顺序

只在基于规则的优化器中有效：

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

31. 过滤掉无需 GROUP BY 的字段

可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。

低效：

SELECT JOB, AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效：

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER' 
GROUP BY JOB

32. SQL 语句用大写

Oracle 中总是先解析 SQL 语句，把小写的字母转换成大写的再执行。

36. 避免存储过程死锁

在你的存储过程和触发器中访问同一个表时总是以相同的顺序；
事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量；
永远不要在事务中等待用户输入。

37. 表变量优先于临时表

应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

38. 避免使用触发器

触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；
如果能够使用约束实现的，尽量不要使用触发器；
不要为不同的触发事件（Insert、Update 和 Delete）使用相同的触发器；
不要在触发器中使用事务型代码。

39. 索引创建规则

表的主键、外键必须有索引；
数据量超过 300 的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
正确选择复合索引中的主列字段，一般是选择性较好的字段；
复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响；
表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
尽量不要对数据库中某个含有大量重复的值的字段建立索引。

40. 主键设置

应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的（推荐使用 UNSIGNED），并设置上自动增加的 AUTO_INCREMENT 标志。

41. 使用 InnoDB

myisam：
- 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
InnoDB：
- 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（InnoDB 有效地降低删除和更新导致的锁定）。
- 对于支持事务的 InnoDB类型的表来说，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行 SQL 前调用 begin，多条 SQL 形成一个事物（即使 autocommit 打开也可以），将大大提高性能。

42. 选择合适的字段类型

**原则：**更小通常更好，简单就好，所有字段都得有默认值，尽量避免 NULL。

例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型。(mediumint 就比 int 更合适)

比如时间字段：datetime 和 timestamp。datetime 占用8个字节，timestamp 占用4个字节，只用了一半。而 timestamp 表示的范围是 1970—2037 适合做更新时间。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。

因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如：在定义邮政编码这个字段时，如果将其设置为 CHAR(255)，显然给数据库增加了不必要的空间。甚至使用VARCHAR 这种类型也是多余的，因为 CHAR(6) 就可以很好的完成任务了。

同样的，如果可以的话，我们应该使用 MEDIUMINT 而不是 BIGIN 来定义整型字段，应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

重构查询方式

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2. 分解大连接查询

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

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
减少锁竞争；
在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
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);

语句执行顺序

以下是 SQL 中各个子句的语法顺序，前面括号内的数字代表了它们的逻辑执行顺序：

(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1)  FROM t1 JOIN t2
(2)    ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5)HAVING having_condition
(7) UNION [ALL]
   ...
(8) ORDER BY col1 ASC,col2 DESC
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

也就是说，SQL 并不是按照编写顺序先执行 SELECT，然后再执行 FROM 子句。从逻辑上讲，SQL 语句的执行顺序如下：

首先，FROM 和 JOIN 是 SQL 语句执行的第一步。它们的逻辑结果是一个笛卡尔积，决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会使用 ON 和 WHERE 过滤条件进行优化访问；
其次，应用 ON 条件对上一步的结果进行过滤并生成新的数据集；
然后，执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数情况下的效果相同，但是外连接查询有所区别，我们将会在下文给出示例；
接着，基于 GROUP BY 子句指定的表达式进行分组；同时，对于每个分组计算聚合函数 agg_func 的结果。经过 GROUP BY 处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果；
如果存在 GROUP BY 子句，可以利用 HAVING 针对分组后的结果进一步进行过滤，通常是针对聚合函数的结果进行过滤；
接下来，SELECT 可以指定要返回的列；如果指定了 DISTINCT 关键字，需要对结果集进行去重操作。另外还会为指定了 AS 的字段生成别名；
如果还有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 语句，执行该查询并且合并两个结果集。对于集合操作中的多个 SELECT 语句，数据库通常可以支持并发执行；
然后，应用 ORDER BY 子句对结果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字，只能使用分组字段和聚合函数进行排序；否则，可以使用 FROM 和 JOIN 表中的任何字段排序；
最后，OFFSET 和 FETCH（LIMIT、TOP）限定了最终返回的行数。

了解 SQL 逻辑执行顺序可以帮助我们进行 SQL 优化。例如 WHERE 子句在 HAVING 子句之前执行，因此我们应该尽量使用 WHERE 进行数据过滤，避免无谓的操作；除非业务需要针对聚合函数的结果进行过滤。

除此之外，理解 SQL 的逻辑执行顺序还可以帮助我们避免一些常见的错误，例如以下语句：

-- 错误示例
SELECT emp_name AS empname
  FROM employee
 WHERE empname ='张飞';

该语句的错误在于 WHERE 条件中引用了列别名；从上面的逻辑顺序可以看出，执行 WHERE 条件时还没有执行 SELECT 子句，也就没有生成字段的别名。

另外一个需要注意的操作就是 GROUP BY，例如：

-- GROUP BY 错误示例
SELECT dept_id, emp_name, AVG(salary)
  FROM employee
 GROUP BY dept_id;

由于经过 GROUP BY 处理之后结果集只保留了分组字段和聚合函数的结果，示例中的 emp_name 字段已经不存在；从业务逻辑上来说，按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总，可以使用窗口函数。

如果使用了 GROUP BY 分组，之后的 SELECT、ORDER BY 等只能引用分组字段或者聚合函数；否则，可以引用 FROM 和 JOIN 表中的任何字段。

还有一些逻辑问题可能不会直接导致查询出错，但是会返回不正确的结果；例如外连接查询中的 ON 和 WHERE 条件。以下是一个左外连接查询的示例：

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id)
 WHERE e.emp_name ='张飞';
emp_name|dept_name|
--------|---------|
张飞     |行政管理部|

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='张飞');
emp_name|dept_name|
--------|---------|
刘备     |   [NULL]|
关羽     |   [NULL]|
张飞     |行政管理部|
诸葛亮   |   [NULL]|
...

第一个查询在 ON 子句中指定了连接的条件，同时通过 WHERE 子句找出了“张飞”的信息。

第二个查询将所有的过滤条件都放在 ON 子句中，结果返回了所有的员工信息。这是因为左外连接会返回左表中的全部数据，即使 ON 子句中指定了员工姓名也不会生效；而 WHERE 条件在逻辑上是对连接操作之后的结果进行过滤。

5 - CH05-分库分表

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

Sharding 策略

哈希取模: hash(key) % NUM_DB
范围: 可以是 ID 范围也可以是时间范围
映射表: 使用单独的一个数据库来存储映射关系

Sharding 存在的问题及解决方案

1. 事务问题

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

2. 链接

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

3. ID 唯一性

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

6 - CH06-复制分离

主从复制

主要涉及三个线程: binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
I/O 线程 : 负责从主服务器上读取二进制日志，并写入从服务器的中继日志中。
SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于:

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

7 - CH07-执行过程

前言

天天和数据库打交道，一天能写上几十条 SQL 语句，但你知道我们的系统是如何和数据库交互的吗？MySQL 如何帮我们存储数据、又是如何帮我们管理事务？….是不是感觉真的除了写几个「select * from dual」外基本脑子一片空白？这篇文章就将带你走进 MySQL 的世界，让你彻底了解系统到底是如何和 MySQL 交互的，MySQL 在接受到我们发送的 SQL 语句时又分别做了哪些事情。

MySQL 驱动

我们的系统在和 MySQL 数据库进行通信的时候，总不可能是平白无故的就能接收和发送请求，就算是你没有做什么操作，那总该是有其他的“人”帮我们做了一些事情，基本上使用过 MySQL 数据库的程序员多多少少都会知道 MySQL 驱动这个概念的。就是这个 MySQL 驱动在底层帮我们做了对数据库的连接，只有建立了连接了，才能够有后面的交互。看下图表示

这样的话，在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接，那么问题来了，我们系统肯定不是一个人在使用的，换句话说肯定是存在多个请求同时去争抢连接的情况。我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭，这样会有什么问题呢？如下图

java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的。必然会大大降低我们系统的性能，但是如果给你提供一些固定的用来连接的线程，这样是不是不需要反复的创建和销毁连接了呢？相信懂行的朋友会会心一笑，没错，说的就是数据库连接池。

数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。

常见的数据库连接池有 HakariCP、Druid、C3P0、DBCP，连接池实现原理在这里就不深入讨论了，采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的「池化」思想，不管是线程池还是 HTTP 连接池，都能看到它的身影。

数据库连接池

到这里，我们已经知道的是我们的系统在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。不过这里有个小问题，业务系统是并发的，而 MySQL 接受请求的线程呢，只有一个？

其实 MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。

至此系统和 MySQL 数据库之间的连接问题已经说明清楚了。那么 MySQL 数据库中的这些连接是怎么来处理的，又是谁来处理呢？

网络连接必须由线程来处理

对计算基础稍微有一点了解的的同学都是知道的，网络中的连接都是由线程来处理的，所谓网络连接说白了就是一次请求，每次请求都会有相应的线程去处理的。也就是说对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。

那这些线程会怎么去处理这些请求？会做哪些事情？

SQL 接口

MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。

查询解析器

假如现在有这样的一个 SQL

SELECT stuName,age,sex FROM students WHERE id=1

但是这个 SQL 是写给我们人看的，机器哪里知道你在说什么？这个时候解析器就上场了。他会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言，至于怎么解析的就不需要在深究了，无非是自己一套相关的规则。

现在 SQL 已经被解析成 MySQL 认识的样子的，那下一步是不是就是执行吗？理论上是这样子的，但是 MySQL 的强大远不止于此，他还会帮我们选择最优的查询路径。

什么叫最优查询路径？就是 MySQL 会按照自己认为的效率最高的方式去执行查询.

具体是怎么做到的呢？这就要说到 MySQL 的查询优化器了

MySQL 查询优化器

查询优化器内部具体怎么实现的我们不需要是关心，我需要知道的是 MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本

IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关

CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

MySQL 优化器会计算「IO 成本 + CPU」成本最小的那个索引来执行

优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句

存储引擎

查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的（存储引擎是一个非常重要的组件，后面会详细介绍）

执行器

执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行

初识存储引擎

我们以一个更新的SQL语句来说明，SQL 如下

UPDATE students SET stuName = '小强' WHERE id = 1

当我们系统发出这样的查询去交给 MySQL 的时候，MySQL 会按照我们上面介绍的一系列的流程最终通过执行器调用存储引擎去执行，流程图就是上面那个。在执行这个 SQL 的时候 SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机IO读写的速度肯定让人无法接受的，所以每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池 Buffer Pool

Buffer Pool

Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，如下图

按照上面的那幅图，这条 SQL 语句的执行步骤大致是这样子的

innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在
发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中
该条记录会被加上一个独占锁（总不能你在修改的时候别人也在修改吧，这个机制本篇文章不重点介绍，以后会专门写文章来详细讲解）

undo 日志文件：记录数据被修改前的样子

undo 顾名思义，就是没有做，没发生的意思。undo log 就是没有发生事情（原本事情是什么）的一些日志

我们刚刚已经说了，在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

这样做的目的是什么？

Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响，看图说话

这里说句额外话，其实 MySQL 也是一个系统，就好比我们平时开发的 java 的功能系统一样，MySQL 使用的是自己相应的语言开发出来的一套系统而已，它根据自己需要的功能去设计对应的功能，它即然能做到哪些事情，那么必然是设计者们当初这么定义或者是根据实际的场景变更演化而来的。所以大家放平心态，把 MySQL 当作一个系统去了解熟悉他。

到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的，那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是小强数据库中的记录是旺财，这种情况 MySQL是怎么处理的呢，继续往下看

redo 日志文件：记录数据被修改后的样子

除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了

画外音：redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的

redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来，例如，此时将要做的是update students set stuName=‘小强’ where id=1; 那么这条操作就会被记录到 redo log buffer 中，啥？怎么又出来一个 redo log buffer ,很简单，MySQL 为了提高效率，所以将这些操作都先放在内存中去完成，然后会在某个时机将其持久化到磁盘中。

截至目前，我们应该都熟悉了 MySQL 的执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：

准备更新一条 SQL 语句
MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
innodb 会在 Buffer Pool 中执行更新操作
更新后的数据会记录在 redo log buffer 中

上面说的步骤都是在正常情况下的操作，但是程序的设计和优化并不仅是为了这些正常情况而去做的，也是为了那些临界区和极端情况下出现的问题去优化设计的

这个时候如果服务器宕机了，那么缓存中的数据还是丢失了。真烦，竟然数据总是丢失，那能不能不要放在内存中，直接保存到磁盘呢？很显然不行，因为在上面也已经介绍了，在内存中的操作目的是为了提高效率。

此时，如果 MySQL 真的宕机了，那么没关系的，因为 MySQL 会认为本次事务是失败的，所以数据依旧是更新前的样子，并不会有任何的影响。

好了，语句也更新好了那么需要将更新的值提交啊，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作

将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘（具体策略情况在下面小总结出会详细介绍）,上图

如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？此时数据是在内存中，数据岂不是丢失了？不，这次数据就不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中（这边我的理解是和 Redis 的持久化机制是差不多的，在 Redis 启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中）

到此为止，从执行器开始调用存储引擎接口做了哪些事情呢？

准备更新一条 SQL 语句
MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载
到缓冲池（BufferPool）中 3.在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
innodb 会在 Buffer Pool 中执行更新操作
更新后的数据会记录在 redo log buffer 中
MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
- 值为 0 表示不刷入磁盘
- 值为 1 表示立即刷入磁盘
- 值为 2 表示先刷到 os cache
myslq 重启的时候会将 redo 日志恢复到缓冲池中

截止到目前位置，MySQL 的执行器调用存储引擎的接口去执行【执行计划】提供的 SQL 的时候 InnoDB 做了哪些事情也就基本差不多了，但是这还没完。下面还需要介绍下 MySQL 级别的日志文件 bin log

bin log 日志文件：记录整个操作过程

上面介绍到的redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:

性质	redo Log	bin Log
文件大小	redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了）	bin log 可通过配置参数max_bin log_size设置每个bin log文件的大小（但是一般不建议修改）。
实现方式	redo log是InnoDB引擎层实现的（也就是说是 Innodb 存储引起过独有的）	bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。	bin log 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
使用场景	redo log适用于崩溃恢复(crash-safe)（这一点其实非常类似与 Redis 的持久化特征）	bin log 适用于主从复制和数据恢复

bin log文件是如何刷入磁盘的?

bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。

刷入 bin log 有以下几种模式

STATMENT

基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中

【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能

【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等

ROW

基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题

【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨

MIXED

基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

那既然bin log也是日志文件，那它是在什么记录数据的呢？

其实 MySQL 在提交事务的时候，不仅仅会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log最后写入 commit 标记，这样就表示本次事务被成功的提交了。

如果在数据被写入到bin log文件的时候，刚写完，数据库宕机了，数据会丢失吗？

首先可以确定的是，只要redo log最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失了，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中。

好了，到目前为止，一个更新操作我们基本介绍得差不多，但是你有没有感觉少了哪件事情还没有做？是不是你也发现这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到Buffer Pool中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？

其实 MySQL 会有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。

本文总结

到此，关于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系就基本差不多了。

我们再回顾下

Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
Undo log 记录的是数据操作前的样子
redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

从准备更新一条数据到事务的提交的流程描述

首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
在数据被缓存到缓存池的同时，会写入 undo log 日志文件
更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
完成以后就可以提交事务，在提交的同时会做以下三件事
- 将redo log buffer中的数据刷入到 redo log 文件中
- 将本次操作记录写入到 bin log文件中
- 将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记

至此表示整个更新事务已经完成

8 - CH08-参考规约

建表

强制：表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint （1 表示是，0 表示否）。说明：任何字段如果为非负数，必须是 unsigned。注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在<resultMap>设置从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。
强制：表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。正例：aliyun_admin，rdc_config，level3_name 反例：AliyunAdmin，rdcConfig，level_3_name
强制：表名不使用复数名词。说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
强制：禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
强制：主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。
强制：小数类型为 decimal，禁止使用 float 和 double。
- 说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
强制：如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
强制：varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
强制：表必备三字段：id, gmt_create, gmt_modified。
- 说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。gmt_create,
- gmt_modified 的类型均为 datetime 类型，前者现在时表示主动创建，后者过去分词表示被动更新。
推荐：表的命名最好是加上“业务名称_表的作用”。正例：alipay_task / force_project / trade_config
推荐：库名与应用名称尽量一致。
推荐：如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
推荐：字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循： 1）不是频繁修改的字段。 2）不是 varchar 超长字段，更不能是 text 字段。正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。
推荐：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
参考：合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。

索引

强制：业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
强制：超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。说明：即使双表 join 也要注意表索引、SQL 性能。
强制：在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
- 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。
强制：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
推荐：如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。正例：where a=? and b=? order by c; 索引：a_b_c
- 反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
推荐：利用覆盖索引来进行查询操作，避免回表。说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。
推荐：利用延迟关联或者子查询优化超多分页场景。说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。正例：先快速定位需要获取的 id 段，然后再关联：
- SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
推荐： SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。说明： 1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 2）ref 指的是使用普通的索引（normal index）。 3）range 对索引进行范围检索。反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。
推荐：建组合索引的时候，区分度最高的在最左边。正例：如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。
推荐：防止因字段类型不同造成的隐式转换，导致索引失效。
参考：创建索引时避免有如下极端误解： 1）宁滥勿缺。认为一个查询就需要建一个索引。 2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。 3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

SQL

强制：不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
强制：count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
强制：当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题。
- 正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
强制：使用 ISNULL()来判断是否为 NULL 值。
- 说明：NULL 与任何值的直接比较都为 NULL。
  - 1） NULL<>NULL 的返回结果是 NULL，而不是 false。
  - 2） NULL=NULL 的返回结果是 NULL，而不是 true。
  - 3） NULL<>1 的返回结果是 NULL，而不是 true。
强制：在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
强制：不得使用外键与级联，一切外键概念必须在应用层解决。说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
强制：禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
强制：数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。
推荐：in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。
参考：如果有国际化需要，所有的字符存储与表示，均以 utf-8 编码，注意字符统计函数的区别。说明： SELECT LENGTH(“轻松工作”)；返回为 12 SELECT CHARACTER_LENGTH(“轻松工作”)；返回为 4 如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf-8 编码的区别。
参考：TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

ORM

强制：在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络消耗，尤其是 text 类型的字段。
强制：POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性之间的映射。说明：参见定义 POJO 类以及数据库字段定义规定，在中增加映射，是必须的。在 MyBatis Generator 生成的代码中，需要进行对应的修改。
强制：不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个 POJO 类与之对应。说明：配置映射关系，使字段与 DO 类解耦，方便维护。
强制：sql.xml 配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现 SQL 注入。
强制：iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
- 说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start,size 的子集合。
- 正例：Map<String, Object> map = new HashMap<>(); map.put("start", start); map.put("size", size);
强制：不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
- 说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
强制：更新数据表记录时，必须同时更新记录对应的 gmt_modified 字段值为当前时间。
推荐：不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
参考：@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
参考：中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时带上此条件；表示不为空且不为 null 时执行；表示不为 null 值时执行。