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 条件在逻辑上是对连接操作之后的结果进行过滤。

Feedback

Was this page helpful?

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.