MySQL数据库优化篇章·上

@心欲无痕  July 16, 2018

一般的应用开发或多或少的涉及到了数据库,有使用MySQL、PostgreSQL、Oracle等,这里主要讲讲MySQL的优化经验,很多人都不知道怎么表述?很多都是说这个做过,那个做过,但是就是不知道怎么表达,其实大家可以从MySQL官网去看看文档的优化(第八章);好了,闲话不多说,从以下几个方面讲解:

1、WHERE语句优化

  • 去掉多余的括号,如:

((a AND b) AND c OR (((a AND b) AND (c AND d))))

转换为:

(a AND b AND c) OR (a AND b AND c AND d)
  • 常量替换
        (a<b AND b=c) AND a=5
替换为: b>5 AND b=c AND a=5
  • 简化表达式
        (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
替换为:  B=5 OR B=6
  • COUNT(*)

COUNT(*)语句在底层的SQL引擎层面进行了优化,如果是MyISAM或者Memory数据表,那么直接从表的信息中直接返回,所以查询非常快,当然仅限单表操作。

一般情况下查询非常快的示例,假设key_part1key_part2tbl_name 表的索引:


SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

这里涉及的 ORDER BYGROUP BY聚合查询 后面会讲解的。

2、单索引区间优化

在MySQL中,WHERE条件中使用了如下的操作符操作常值的时候都是但'索引区间查询',常值表示如下的含义:

  • 跟字查询不相关的结果集
  • 常量 12 or 'abc' 等
  • 一个const表或者system表,'const表'表示数据量只有一条或者常值的数据表 'system表'表示NULL表,也就是没有数据的表

如:

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

其实在 MySQL 底层,复杂的 WHERE 表达式,引擎会自动的进行简化来查询结果集,所以如果您的 表达式过于复杂或者层级过于难懂,可以考虑简化查询如下:

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

我们可以看到 其实最后的一个 OR 条件其实 就是 FALSE ,所以结果表达式就是:

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  FALSE;

3、复合索引区间优化

除了单一索引外,MySQL还支持复合索引,所以这里说说复合索引的优化思路:从左到右依次索引如:index(i_a_id) (id, age, s_id) 因此,生效的索引顺序依次就是 idid,ageid,age,s_id, 但是 age,s_id 就不生效的

4、IN与OR条件优化

首先我们可以输入如下的 查询,来查看 系统的配置参数:

SELECT @global.eq_range_index_dive_limit;

查看系统默认配置的 IN 参数的个数,我这边显示200个,也就是说默认情况下 IN 参数可以支持分割 200 个,如果输入过大,那么就需要重新分配buffer,造成多余的内存或者磁盘的IO,性能也就下降,所以一般下需要考虑此参数的参数值。

5、限制区间查询的内存缓存

一般下在MySQL 5.7 版本中,一个 OR 条件需要消耗 230 字节, 一个 AND 需要消耗 125 字节,IN 条件消耗的是 区间的乘积,如:

SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

消耗的内存缓存是 N * 230 字节

SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;

消耗的内存是 N * 125 字节

SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

消耗的缓存空间是 M N 125 字节。 MySQL官方文档解析说,MySQL5.7.11版本之前每一个 OR 条件消耗的空间是 大约700字节。因此合理的使用区间查询看样可以提升很高的SQL效率

5、索引合并查询优化

索引合并查询是指在 EXPLAIN 中输出的 type 列的值是 index_merge, 如下几个索引合并查询,这里假设 key1 与 key2 都是表 tbl_name 的索引 :

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

基本的使用规则就是索引列放到WHERE条件的最前列,其余条件依次。

6、MySQL的连接算法:Nested-Loop Join(NLJ)与(BNL)

在之前的版本中,每一个join连接,MySQL都会按照如下的算法进行连接处理:

表名   Join类型
t1      range
t2      ref
t3      ALL
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

也就是说针对于每一个JOIN查询,MySQL都会如下操作:把第一个符合条件的表依次传入内嵌的循环,这样就存在一个问题,如果外层表很大如:6000,000数据,但是t3表只有6000条数据,那么会浪费极大的缓存空间和IO消耗,所以NLJ算法要求外层的表是小数据量表,内层匹配大表,降低buffer量,减少匹配来优化性能,不能新版本的MySQL已经很智能了,采用的BNL算法可以自动的判断表的数据量那个最小进行缓存的t1表使用。因此这个规则只适合之前的旧版本。不过还是建议用户编写SQL的时候采用适合 NLJ 算法的格式编写SQL来符合所有的SQL查询优化,注意这里的缓存设计到一个MySQL的系统配置:join_buffer_size,默认的配置大小,可以看到如下:

属性 值
--join-buffer-size

Default Value 262144
Minimum Value 128
Maximum Value (Other, 64-bit platforms) 18446744073709547520
Maximum Value (Other, 32-bit platforms) 4294967295
Maximum Value (Windows) 4294967295

7、GROUP BY 优化

  • 松散的索引扫描,其实说白了就是复合索引的利用,索引只是用了复合最左前缀索引的一部分

这里假设有索引 idx(c1,c2,c3) 在表 t1(c1, c2, c3, c4) 中,下面的都是复合 松散索引扫描的 SELECT 查询。

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面列举一些不适用松散索引扫描的列子,及其原因:

SELECT c1, SUM(c2) FROM t1 GROUP BY c1; -- 使用了 SUM() 函数,除了 MIN与MAX之外

SELECT c1, c2 FROM t1 GROUP BY c2, c3;  -- 违背了 LEFTMOST PREFIX最左前缀原则

SELECT c1, c3 FROM t1 GROUP BY c1, c2;  -- c3 没有 常值表达式
  • 紧凑索引扫描

既然有松散索引扫描,那么也有紧凑索引扫描,顾名思义:也就是索引是紧凑的,没有断开。下面说说示例:

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; -- 虽然断开了 c2索引,但是WHERE条件存在,所以复合紧凑索引

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3; -- 虽然不符合最左前缀索引,但是WHERE条件使用了第一个索引,所以符合紧凑索引

注意:在每一个 GROUP BY 中 MySQL 默认携带 ORDER BY 效果,如果不充分使用索引的话,那么就会进行 filesort 极大的消耗系统的性能,查询效果极差,如果不需要 默认的 ORDER BY ,那么可以在语句中加上 ORDER BY NULL, 就可以。

8、ORDER BY 优化

  • 使用索引迎合ORDER BY, 如:
SELECT * FROM t1 ORDER BY key_part1, key_part2;

但是记住这样只是避免了filesorting,实际上碰上大数据量的时候性能也不乐观。官方有一段英文说明了:

In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results. If so, the optimizer probably will not use the index. If SELECT * selects only the index columns, the index will be used and sorting avoided.

我这边翻译一下:

在那种情况下,扫描整个索引并且回表查找不在索引列的字段比起扫描全表然后排序代价或许还要昂贵,如果是这样的话,优化器可能不会使用索引,如果 SELECT * 只查询索引列,那么索引就会被使用,并且排序也会被避免,因此速度极快。

所以对比下方与上方的查询,下方的查询就很快:

SELECT pk, key_part1, key_part2 FROM t1 ORDER BY key_part1, key_part2;

下面列举一些不能使用索引优化ORDER BY的查询示例,但是结果集会使用索引来查找数据:

SELECT * FROM t1 ORDER BY key1, key2;  -- 索引不能优化 ORDER BY, 但是会依据索引查找数据

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3; -- 不符合最左前缀原则,key_part2索引没有使用

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; -- 混用了DESC 与 ASC,导致 排序耗时

SELECT * FROM t1 WHERE key2=constant ORDER BY key1; -- ORDER BY字段不是单一的索引结果列

SELECT * FROM t1 ORDER BY ABS(key); -- ORDER BY 使用了函数
SELECT * FROM t1 ORDER BY -key;     -- ORDER BY 使用了表达式

这里看一个最优的 ORDER BY 示例:

SELECT a FROM t1 ORDER BY a;

再看下一个示例:

SELECT ABS(a) AS a FROM t1 ORDER BY a;

这个就跟下面的语句其实一样的效果:

SELECT * FROM t1 ORDER BY a;

其实这里的原因很简单, ORDER BY 的结果是从表的结果集中查找,如果是原始数据,那么会充分利用结果,如果不是的话,那么会从临时表缓存中查找数据,因此性能就大打折扣,增加了一次全表的查询时间。

SELECT ABS(a) AS b FROM t1 ORDER BY a;

注意:上面的结果却会充分利用索引,因为表的原始数据中 a 列的内容没有改变,可以索引,因此性能很高。

注意,任何使用了 filesort 的查询,其缓存的大小是根据系统的配置参数决定的。默认值:

SELECT @@GLOBAL.sort_buffer_size;

Default Value 262144
Minimum Value 32768
Maximum Value (Other, 64-bit platforms) 18446744073709551615
Maximum Value (Other, 32-bit platforms) 4294967295
Maximum Value (Windows) 4294967295

SELECT @@GLOBAL.max_sort_length;

Default Value 1024
Minimum Value 4
Maximum Value 8388608

SELECT @@GLOBAL.read_rnd_buffer_size

Default Value 262144
Minimum Value 1
Maximum Value 2147483647

根据实际情况调优这些参数即可,根据业务量就可以决定,比如你的数据量都比较小,那么就可以适当的减小这些参数,如果数据量比较大,那么可以适当的调大这些参数,灵活配置接口,当然了,如果您的服务器配置极高,那么不用考虑,调大参数吧。

9、LIMIT 优化

在后台的报表系统中,经常会涉及到 LIMIT 分页,因为 MySQL提供了这么好用的功能,那么用户肯定会使用方便的功能,以免自己实现分页。但是在数据量很大的情况下,经常会发现查询好慢,数据越靠后,查询越耗时,那么我们这里提供一些小技巧给大家,来优化SQL语句:

  • 灵活根据当前的系统的情况来决定,索引该加的加,SQL该重构的就重写,这里提示大家,数据千万别删,可以假删除。如,此时 v_record表存在一亿数据量,实际情况就是一亿。

我们执行如下的查询:

SELECT * FROM v_record LIMIT 9000000, 100;

屏幕快照 2018-07-16 下午4.34.12.png

我们会发现查询好慢好慢,基本是2.7秒才出结果,我这个配置还算比较高的,I7-4790 16G 240SSD。

但是我们稍微优化下,再试试:

SELECT id FROM v_record LIMIT 9000000, 100;

屏幕快照 2018-07-16 下午4.35.47.png

看是不是提速了?,其实如果我们这里是连续的ID的情况,我们可以使用WHERE条件与BETWEEN条件,来讲速度提升到毫秒级如:

SELECT id FROM v_record WHERE id BETWEEN 9000000 AND 9000100;

屏幕快照 2018-07-16 下午4.37.25.png

所以这里我们就可以看出一个良好的数据库结构设计是多么重要的一件事情,数据库结构设计巧妙,完善,那么我们就算使用 MySQL来完成亿级表的查询,丝毫不会觉得性能好慢。这里可以提示大家一下,在MySQL里面我们如果灵活利用索引,那么结果会是极其乐观,要不官方的第一句优化的经验就是告诉大家,官方原文:

To make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. Set up indexes on columns used in the WHERE clause, to speed up evaluation, filtering, and the final retrieval of results. To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.

翻译过来就是:

要使得 一个 SELECT ... WHERE 的慢查询更快,第一件事要检查的是否你可以添加一个索引。给列添加索引然后在WHERE条件中使用,来加速计算、过滤、和返回结果集。为了避免磁盘的空间浪费,你可以在你的应用中构造一个小集合的索引来加速关系查询。

好啦,优化的上篇已经搞完,整理整理一下思路,开始中篇与下篇...


评论已关闭