mysql优化及原理，这一篇就够了

MySQL优化原理

前言

说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型.....

你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？

我想未必，因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。

MySQL逻辑架构

MySQL查询过程

过程的详细分析

客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的。在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。

这两个动作不能同时发生，一端开始发送消息，另一端要接收完整个消息才能响应它。所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

服务器响应给用户的数据通常会很多，由多个数据包组成。当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

所以在实际开发中我们需要保持一个良好的习惯

保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量，查询中尽量避免使用SELECT * 以及加上 LIMIT 限制。

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。

如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。

这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构）

通过一个哈希值索引，这个哈希值通过查询本身、查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果

比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

缓存失效

MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

所以，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。

如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

　　任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

　　如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

所以，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。

查询缓存系统本身是非常复杂的，所以不要轻易打开查询缓存，特别是写密集型应用。其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等。

语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。

查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

MySQL可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

例如：

mysql> SELECT * FROM users LIMIT 10;

mysql> SHOW STATUS LIKE 'last_query_cost';

+-----------------+-------------+

| Variable_name   | Value       |

+-----------------+-------------+

| Last_query_cost | 6391.799000 |

+-----------------+-------------+

结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。

这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）。

MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小，但成本小并不意味着执行时间短）。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划。

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化。

查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。

MySQL在查询优化阶段就为每一张表创建了一个 handler 实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

客户端向MySQL服务器发送一条查询请求
服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器进行SQL解析、预处理、优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API来执行查询
结果返回给客户端，同时缓存查询结果

性能优化建议

看了这么多，你可能会期待给出一些优化手段。但是，不要听信你看到的关于优化的“绝对真理”。

应该在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。

1、Scheme设计与数据类型优化

2、创建高性能索引

3、索引相关的数据结构和算法

Scheme设计与数据类型优化

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。

越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

几个可能容易理解错误的技巧：

通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
通常来讲，没有太大的必要使用 DECIMAL 数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
schema 的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。

创建高性能索引

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。

应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

索引相关的数据结构和算法

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。

好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。

如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上。

但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。

所以在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

B-Tree 和 B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

通常我们所说的索引是指 B-Tree 索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。

B-Tree 中的 B 是指 balance，意为平衡。需要注意的是，B-Tree 索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B-Tree 是一种多路自平衡的搜索树

它类似普通的平衡二叉树，不同的一点是 B-Tree 允许每个节点有更多的子节点。下图是 B-Tree 的简化图

特点：

所有键值分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
在关键字全集内做一次查找,性能逼近二分查找；

B+Tree 是 B-Tree 的变体，也是一种多路搜索树, 它与 B-Tree 的不同之处在于:

所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
为所有叶子结点增加了一个链指针

简化 B+Tree 如下图

为什么使用B-/B+ Tree

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

MySQL是基于磁盘的数据库系统，索引往往以索引文件的形式存储的磁盘上。

索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。

下面我们来看一下磁盘局部性原理和预读。

局部性原理与磁盘预读

由于磁盘的存取速度与内存之间鸿沟，为了提高效率，要尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读。

磁盘读取完需要的数据，会顺序向后读一定长度的数据放入内存。

而这样做的理论依据是计算机科学中的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率，预读的长度一般为页(page)的整倍数。

MySQL(默认使用InnoDB引擎)，将记录按照页的方式进行管理，每页大小默认为16K(这个值可以修改)，linux 默认页大小为4K。

B-/+Tree索引的性能分析

我们一般以使用磁盘 I/O 次数评价索引结构的优劣。根据B-Tree的定义，可知检索一次最多需要访问h个节点。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

B-Tree 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为 $O (h) = O (l o g_{d} N)$ $O (h) = O (l o g_{d} N)$

$O (h) = O (l o g_{d} N)$

1、在 where 及 order by 涉及的列上建立索引，对查询进行优化，尽量避免全表扫描

2、避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

mysql> SELECT id FROM table WHERE num is null;

可以在 num上设置默认值 0,确保表中 num 列没有 null 值，然后这样查询：

mysql> SELECT id FROM table WHERE num=0;

3、避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

mysql> SELECT id FROM table WHERE num !=0;

4、避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

mysql> SELECT id FROM table WHERE num=1 or num=2;

优化后：

mysql> SELECT id FROM table WHERE num=1 UNION ALL SELECT id FORM table WHERE num=2;

5、in 和 not in 也要慎用，否则会导致全表扫描

mysql> SELECT name FROM users WHERE id IN(1,2,3,4,5);

对于连续的数值，能用 between 就不要用 in 了

mysql> SELECT name FROM users WHERE id BETWEEN 1 AND 5;

6、使用 like 查询会导致全表扫描

mysql> SELECT id FROM users WHERE name LIKE '%abc%';

若要提高效率，可以考虑全文检索。条件：在MySQL5.6以下，只有MyISAM表支持全文检索，在MySQL5.6以上Innodb引擎表也提供支持全文检索。相应字段建立FULLTEXT索引。

7、如果在 where 子句中使用参数，也会导致全表扫描。

因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。如果在编译时建立访问计划变量的值还是未知的，所以无法作为索引选择的输入项将进行全表扫描。

mysql> SELECT id FROM users WHERE name=@name;

可以改为强制查询使用索引：

mysql> SELECT id FROM users WITH(index('索引名')) WHERE  name=@name;

8、避免在 where 子句中使用表达式，这将导致引擎放弃使用索引而进行全表扫描

不要在 where 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则全表扫描。

mysql> SELECT id FROM users WHERE age/2=10;

优化后：

mysql> SELECT id FROM users WHERE age=10*2;

9、避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

mysql> SELECT id FROM users WHERE substring(name, 1, 2)='abc'; #name 已开头 ‘abc’的

优化后：

mysql> SELECT id FROM users WHERE name like 'abc%';

10、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段

参考：

https://www.cnblogs.com/liujiacai/p/7605612.html

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

posted @ 2018-11-19 16:55 丶中医阅读(463) 评论(0) 编辑收藏举报

刷新页面返回顶部

丶心情就像夜

丶心情就像夜

<?php echo "Hello World"-#FC635D #FDBE41 #35CB4B

mysql优化及原理，这一篇就够了

MySQL优化原理

前言

MySQL逻辑架构

MySQL查询过程

过程的详细分析

客户端/服务端通信协议

保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量，查询中尽量避免使用SELECT * 以及加上 LIMIT 限制。

查询缓存

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

缓存失效

语法解析和预处理

查询优化

查询执行引擎

返回结果

性能优化建议

Scheme设计与数据类型优化

创建高性能索引

索引相关的数据结构和算法

B-Tree 和 B+Tree

为什么使用B-/B+ Tree

局部性原理与磁盘预读

B-/+Tree索引的性能分析

$O (h) = O (l o g_{d} N)$

1、在 where 及 order by 涉及的列上建立索引，对查询进行优化，尽量避免全表扫描

2、避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3、避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

4、避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

5、in 和 not in 也要慎用，否则会导致全表扫描

6、使用 like 查询会导致全表扫描

7、如果在 where 子句中使用参数，也会导致全表扫描。

8、避免在 where 子句中使用表达式，这将导致引擎放弃使用索引而进行全表扫描

9、避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

10、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段

公告

丶心情就像夜

丶心情就像夜

<?php echo "Hello World"-#FC635D #FDBE41 #35CB4B

mysql优化及原理，这一篇就够了

MySQL优化原理

前言

MySQL逻辑架构

MySQL查询过程

过程的详细分析

客户端/服务端通信协议

保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量，查询中尽量避免使用SELECT * 以及加上 LIMIT 限制。

查询缓存

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

缓存失效

语法解析和预处理

查询优化

查询执行引擎

返回结果

性能优化建议

Scheme设计与数据类型优化

创建高性能索引

索引相关的数据结构和算法

B-Tree 和 B+Tree

为什么使用B-/B+ Tree

局部性原理与磁盘预读

B-/+Tree索引的性能分析

下面总结的一些优化策略供参考学习

1、在 where 及 order by 涉及的列上建立索引，对查询进行优化，尽量避免全表扫描

2、避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3、避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

4、避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

5、in 和 not in 也要慎用，否则会导致全表扫描

6、使用 like 查询会导致全表扫描

7、如果在 where 子句中使用参数，也会导致全表扫描。

8、避免在 where 子句中使用表达式，这将导致引擎放弃使用索引而进行全表扫描

9、避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

10、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段

公告

$O (h) = O (l o g_{d} N)$