mysql 优化

最新推荐文章于 2024-04-22 13:59:26 发布

安然_随心

最新推荐文章于 2024-04-22 13:59:26 发布

阅读量230

点赞数

分类专栏：存储、数据库

原文链接：http://mysql.localhost.net.ar/doc/refman/5.1/zh/optimization.html

版权

存储、数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

1. 概述
2. Select 查询速度
3. 优化数据库结构
- 3.1 使你的数据尽可能小

官方文档：http://mysql.localhost.net.ar/doc/refman/5.1/zh/replication.html

1. 概述

使一个系统更快的最重要因素当然是基本设计。此外，还需要知道系统正做什么样的事情，以及瓶颈是什么。

最常见的系统瓶颈是：

磁盘搜索。需要花时间从磁盘上找到一个数据，用在现代磁盘的平均时间通常小于10ms，因此理论上我们能够每秒大约搜索1000次。这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
磁盘读/写。当磁盘放入正确位置后，我们需要从中读取数据。对于现代的磁盘，一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化，因为你能从多个磁盘并行地读。
CPU周期。我们将数据读入内存后，需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表，速度通常不成问题。
内存带宽。当CPU需要的数据超出CPU缓存时，主缓存带宽就成为内存的一个瓶颈。这在大多数系统正是一个不常见的瓶颈但是你应该知道它。

2. Select 查询速度

2.1 explain

https://juejin.im/post/5e0bfddf5188253a937f3c6b
关于explain 的解释：
在这里插入图片描述

2.2 select 优化

2.2.1 基本的优化点

1. 基于逻辑表达式逻辑的语句简单优化。

例如

除不必要的括号：

  ((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

2. 索引使用的常数表达式仅计算一次。

3. 所有常数的表在查询中比其它表先读出。常数表为：

空表或只有1行的表。
与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。

下列的所有表用作常数表：

mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

4. 如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。

5. 对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。

6. 如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。

7. 如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。

8. 在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。

9 输出每个记录前，跳过不匹配HAVING子句的行。

10. 对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。

11. 尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。

12. 每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。

2.2.2 范围优化

range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。

个人理解：范围优化的核心思想是当有where 条件、且where 条件中包含索引时， mysql 优化器想通过索引检索来避免全表扫描。但是可能where 条件比较复杂，且所有的条件不一定都是对同一个索引上条件、甚至是有对无索引的列上的条件。 mysql 的策略是：通过构造一个大于实际where 范围的单索引约束范围，先初步过滤一次，获取可能满足条件的数据列表，然后在用实际的where 条件过滤，得到最终的数据范围。当前这个执行策略的提前是：mysql 优化器判定使用索引的代价比全表扫描小

2.2.2.1 单元素索引的范围访问方法

个人理解：单元素索引就是所有的范围都只是对某一列的值做范围约束。

对于单元素索引，可以用WHERE子句中的相应条件很方便地表示索引值区间，因此我们称为范围条件而不是“区间”。

单元素索引范围条件的定义如下：

对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。
对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE ‘pattern’(其中 'pattern’不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。
对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。

前面描述的“常量值”系指：

查询字符串中的常量
同一联接中的const或system表中的列
关联子查询的结果
完全从前面类型的子表达式组成的表达式

下面是一些WHERE子句中有范围条件的查询的例子：

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子句提取范围条件。在提取过程中，不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。

例如，考虑下面的语句，其中key1是有索引的列，nonkey没有索引：

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');

key1的提取过程如下：

用原始WHERE子句开始：

 (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
 (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z')

删除nonkey = 4和key1 LIKE ‘%b’，因为它们不能用于范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到：

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
    (key1 < 'bar' AND TRUE) OR
    (key1 < 'uux' AND key1 > 'z')

取消总是为true或false的条件：

 (key1 LIKE 'abcde%' OR TRUE)总是true
 (key1 < 'uux' AND key1 > 'z')总是false

用常量替换这些条件，我们得到：

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

删除不必要的TRUE和FALSE常量，我们得到

(key1 < 'abc') OR (key1 < 'bar')

10.将重叠区间组合成一个产生用于范围扫描的最终条件：

  (key1 < 'bar')

总的来说(如前面的例子所述)，用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。

范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。

2.2.2.2 多元素索引的范围访问方法

个人理解：多元素索引指的是对于混合索引的范围优化

多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。

例如，考虑定义为key1(key_part1, key_part2, key_part3) （索引key1 是混合索引）的多元素索引，以及下面的按关键字顺序所列的关键元组：

key_part1	key_part2	key_part3
NULL	1	‘abc’
NULL	1	‘xyz’
NULL	2	‘foo’
1	1	‘abc’
1	1	‘xyz’
1	2	‘abc’
2	1	‘aaa’

条件key_part1 = 1定义了下面的范围：

(1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf)
范围包括前面数据集中的第4、5和6个元组，可以用于范围访问方法。

通过对比，条件key_part3 = 'abc’不定义单一的区间，不能用于范围访问方法（最左前缀规则）

下面更加详细地描述了范围条件如何用于多元素索引中。

对于HASH索引，可以使用包含相同值的每个区间。这说明区间只能由下面形式的条件产生：

 key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

这里，const1，const2，…为常量，cmp是=、<=>或者IS NULL比较操作符之一，条件包括所有索引部分。(也就是说，有N 个条件，每一个对应N-元素索引的每个部分）。

例如，下面为三元素HASH索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

对于BTREE索引，区间可以对结合AND的条件有用，其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE ‘pattern’ (其中’pattern’不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=，为两个区间)的记录的单一的关键元组。例如，对于条件：

 key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单一区间为：

('foo'，10，10) < (key_part1，key_part2，key_part3) < ('foo'，+inf，+inf)

创建的区间可以比原条件包含更多的记录。例如，前面的区间包括值(‘foo’，11，0)，不满足原条件。

如果包含区间内的一系列记录的条件结合使用OR，则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND，则形成包括一系列包含在区间交集内的记录的一个条件。例如，对于两部分索引的条件：

   (key_part1 = 1 AND key_part2 < 2)    OR (key_part1 > 5)

区间为：

(1, -inf) < (key_part1, key_part2) < (1, 2)

(5, -inf) < (key_part1, key_part2)

在该例子中，第1行的区间左侧的约束使用了一个关键元素，右侧约束使用了两个关键元素。第2行的区间只使用了一个关键元素。EXPLAIN输出的key_len列表示所使用关键字前缀的最大长度。

在某些情况中，key_len可以表示使用的关键元素，但可能不是你所期望的。假定key_part1和key_part2可以为NULL。则key_len列显示下面条件的两个关键元素的长度：

key_part1 >= 1 AND key_part2 < 2

但实际上，该条件可以变换为：

key_part1 >= 1 AND key_part2 IS NOT NULL

2.2.3 索引合并优化

https://www.cnblogs.com/digdeep/p/4975977.html
注意：虽然mysql 支持索引合并，但不是说就可以对每个字段加索引，然后让mysql 对各个索引进行遍历，然后对索引结果进行合并处理。最好的方法应该是根据查询特点，建立联合索引，减少索引遍历次数。另外，在建立联合索引时，应该将区分度高的、使用频道高的字段放在最左边。

我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。

在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

 

SELECT * FROM tbl_name

    WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=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);

索引合并方法有几种访问算法 (参见EXPLAIN输出的Extra字段)：

交集
联合
排序并集

后面几节更加详细地描述了这些方法。

注释：索引合并优化算法具有以下几个已知缺陷：

如果可以对某些关键字进行范围扫描，则不考虑索引合并。
如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案。

对于第一个问题，个人理解就是 mysql 发现一个可用的范围索引后，就会直接使用该索引来避免全表扫描，而不会区分索引的好坏（区分度）。例如

例如，下面的查询：

 SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

对于该查询，可以有两个方案：

使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。
使用badkey < 30条件进行范围扫描。

然而，优化器只考虑第2个方案。如果这不是你想要的，你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行：

SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)

WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

 

SELECT * FROM t1 IGNORE INDEX(badkey)

WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

对于第二个缺陷：如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案，通过下面的识别法则尝试分布各条件：

(x AND y) OR z = (x OR z) AND (y OR z)
(x OR y) AND z = (x AND z) OR (y AND z)

index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。

2.2.3.1 索引合并交集访问算法

该访问算法可以用于当 WHERE子句 结合AND 被转换为不同的关键字的几个范围条件，每个条件为下面之一：

条件使用到复合索引中的所有字段或者左前缀字段，例如： key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
任何InnoDB或BDB表的主键的范围条件。

例如：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

 

SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

索引合并交集算法同时对所有使用的索引进行扫描，然后对扫描的结果求交集。

如果某个合并条件是InnoDB或BDB表的主键的一个条件，不用于记录查询，但用于过滤使用其它条件搜索的记录。

2.2.3.2 索引合并并集访问算法

该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句 结合OR 被转换为不同的关键字的几个范围条件的时候

例如：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
 
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

2.2.3.3 索引合并排序并集访问算法

该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。

2.2.4 MySQL如何优化DISTINCT

在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。
例如，下面的两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
 
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

结合LIMIT row_count 和DISTINCT后，MySQL发现唯一的row_count行后立即停止。

如果不使用查询中命名的所有表的列，MySQL发现第1个匹配后立即停止扫描未使用的表。在下面的情况中，假定t1在t2之前使用(可以用EXPLAIN检查)，发现t2中的第1行后，MySQL不再(为t1中的任何行)读t2：

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

2.2.5 MySQL如何优化LEFT JOIN和RIGHT JOIN

g
left join :

A LEFT JOIN B :如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。

2.2.6 MySQL如何优化ORDER BY

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。

即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数，就可以使用索引。

例如：

SELECT * FROM t1

    ORDER BY key_part1,key_part2,... ;

   

SELECT * FROM t1

    WHERE key_part1=constant

    ORDER BY key_part2;

   

SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

   

SELECT * FROM t1

    WHERE key_part1=1

    ORDER BY key_part1 DESC, key_part2 DESC;

在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括：

对不同的关键字使用ORDER BY：

SELECT * FROM t1 ORDER BY key1, key2；

对关键字的非连续元素使用ORDER BY：

 SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；

混合ASC和DESC：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

用于查询行的关键字与ORDER BY中所使用的不相同：

 SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

通过EXPLAIN SELECT …ORDER BY，可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort，则不能解决查询。

文件排序优化不仅用于记录排序关键字和行的位置，并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样：

读行匹配WHERE子句的行，如前面所示。
对于每个行，记录构成排序关键字和行位置的一系列值，并且记录查询需要的列。
根据排序关键字排序元组
按排序的顺序检索行，但直接从排序的元组读取需要的列，而不是再一次访问表。

该算法比以前版本的Mysql有很大的改进。

3. 优化数据库结构

3.1 使你的数据尽可能小

最基本的优化之一是使表在磁盘上占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快，并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。

可以通过如下方式来是你的表相对变小：

尽可能地使用最有效(最小)的数据类型
尽可能使用较小的整数类型使表更小
如果可能，声明列为NOT NULL
每张表的主索引应该尽可能短。这使一行的识别容易而有效。
只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。**如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。**如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。
如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持对一个字符列的最左边部分创建一个索引(参见13.1.4节，“CREATE INDEX语法”)。更短的索引会更快，不仅因为它们占较少的磁盘空间，而且因为它们将在索引缓存中提供更多的访问，因此磁盘搜索更少。参见7.5.2节，“调节服务器参数”。

安然_随心

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 优化

文章目录1. 概述2. Select 查询速度2.1 explain2.2 select 优化2.2.1 基本的优化点2.2.2 范围优化2.2.2.1 单元素索引的范围访问方法2.2.2.2 多元素索引的范围访问方法2.2.3 索引合并优化2.2.3.1 索引合并交集访问算法2.2.3.2 索引合并并集访问算法2.2.3.3 索引合并排序并集访问算法2.2.4 MySQL如何优化DISTINCT2.2.5 MySQL如何优化LEFT JOIN和RIGHT JOIN2.2.6 MySQL如何优化OR
复制链接

扫一扫