MySQL架构及优化原理（五）

1 MySQL

1.1 MySQL架构原理参看下述链接：

https://blog.csdn.net/hguisu/article/details/7106342

1.2 MySQL优化详解参看下述链接：

https://blog.csdn.net/hguisu/article/details/5713180

1.3 MySQL优化详解参看下述链接：

https://blog.csdn.net/hguisu/article/details/5731629

1.4 小结

数据库的优化，可以从四个方面着手：sql语句优化及有效索引、数据结构、系统配置、硬件。

2 数据库优化

搜索多篇博文，大同小异，并不是我想要的答案。没有一个整体的介绍，都是笼统的从sql查询语句优化、创建索引、硬盘、内存等一些几个方面简单的说明优化的方式，很没有层次感，看完后对数据库的优化没有一个整体的概念，很懵逼。下面是我自己整理的一些内容，旨在对数据库优化原理及方式有个整体的认识。

2.1 数据库的几个方面

数据库优化包含下述几个方面：SQL语句以及有效索引、数据结构、系统配置、硬件。

通过下图不难看出，SQL语句和索引的创建是我们开发人员关注的重点，至于表结构设计则要在设计表结构时，根据功能及经验，还有怎么创建表查询性能好，后期维护成本低等来设计了。

1. SQL以及索引的优化是最重要的。首先要根据需求写出结构良好的SQL，然后根据SQL在表中建立有效的索引。但是如果索引太多，不但会影响写入的效率，对查询也有一定的影响。

2. 要根据一些范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能够更有效的查询。

3. 系统配置的优化。MySQL数据库是基于文件的，如果打开的文件数达到一定的数量，无法打开之后就会进行频繁的IO操作。

4. 硬件优化。更快的IO、更多的内存。一般来说内存越大，对于数据库的操作越好。但是CPU多就不一定了，因为他并不会用到太多的CPU数量，有很多的查询都是单CPU。另外使用高的IO（SSD、RAID），但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的，那么硬件优化就没有什么意义。

下面针对这四个方面做详细的说明。

2.1.1 SQL语句和索引

2.1.1.1 sql语句优化

参看链接：

索引：https://www.cnblogs.com/szrs/p/12552280.html

sql语句优化：https://www.cnblogs.com/szrs/p/12553043.html

2.1.1.2 慢查询日志

1、慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

2、通过慢查询日志发现有效率问题的SQL

说明：可以通过开启慢查询日志的方式进行定位有问题的SQL 。

（1）查看MySQL是否开启慢查询日志

show variables like 'slow_query_log';

（2）设置没有索引的记录到慢查询日志

set global log_queries_not_using_indexes=on;

（3）查看超过多长时间的sql进行记录到慢查询日志

show variables like 'long_query_time'

（4）开启慢查询日志

set global slow_query_log=on

（5）设置超时时间

Set global long_query_time=5;--超过5s的语句才记录日志

（6）查看慢查询日志的位置

show variables like 'slow%'

3、慢查询日志内容分析

慢查询日志主要分为5部分，第一部分是慢查询时间，第二部分是慢查询的来源主机和用户，第三部分是查询的执行时间、锁定时间、发送的行数、扫描的行数。最后是时间戳形式记录的命令以及该命令的执行的时间戳。

系统运行一段时间后，慢查询日志可能比较多，需要通过mysqldumpslow、pt-query-digest等工具分析慢查询日志。参考这里。

4、通过explain查看SQL的执行计划

2.1.1.3 索引优化

1、选择索引

（1）选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。

（2）索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

（3）离散度大得列放在联合索引前面

判断离散程度的方法是：

select count(distinct ziduan1),count(distinct ziduan2) from tablename --越大越离散

2、索引优化方法

索引一般情况下都是高效的。不过凡是都有两面性，索引是以空间换时间的一种策略，索引本身在提高查询效率的同时会影响插入、更新、删除的效率。不当的使用索引不仅增加了写操作的负担，也会影响读取的效率。索引越多，数据库分析的越慢。注意点：

（1）InnoDB 每个索引都会加上主键，联合索引不要加上主键，innodb会自动加，否则会冗余。

（2）索引存在的目的是为了加快查询的效率，不过不是索引越多越好，建立索引要适当才好。过多的索引会增加数据库判断使用什么索引来查询的开销，所以，有时候也会出现以去掉重复或者无效的索引为优化手段的优化方式。

（3）主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了。

3、索引的原理可以参考这里或者这里。理解索引原理对于索引优化有很大帮助。

4、创建索引

参看链接：https://www.cnblogs.com/vole/p/12552280.html

2.1.2 数据表结构优化

1、选择合适的数据类型
（1）使用可存下数据的最小的数据类型。

（2）使用简单地数据类型，int要比varchar类型在mysql处理上更简单。

（3）尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值。

（4）尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率。

例子1、用int存储日期时间

--from_unixtime()可将Int类型的时间戳转换为时间格式

select from_unixtime(1392178320); --输出为 2014-02-12 12:12:00 unix_timestamp()可将时间格式转换为Int类型

select unix_timestamp('2014-02-12 12:12:00'); --输出为1392178320

例子2、存储IP地址——bigInt

--利用inet_aton(),inet_ntoa()转换

select inet_aton('192.169.1.1'); --输出为3232301313

select inet_ntoa(3232301313); --输出为192.169.1.1

2.1.3 数据库配置优化

这方面目前了解的并不多，参考这里吧。

2.1.4 硬件优化

硬件层面的优化是最后的手段。主要需考虑CPU、存储、网络等几个方面。

CPU：CPU并不是越多越好，之前看到网上的分析有说很多的查询都是单CPU的，增加CPU数量并不能提高性能。（缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算）

存储：机械磁盘 or SSD（当然是SSD更快）；单个大磁盘 or 多个小磁盘组合使用（单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取）（数据访问、数据写入、日志记录、大数据量排序、大表连接）

网络：一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。（结果数据传输、SQL请求、远程数据库访问（dblink））

2.1.5 小结

影响数据传输的因素主要为延迟和带宽；效率排序：

cup的处理>网络传输数据>访问磁盘

显然，在访问磁盘时效率最低。

所以，优化数据库，主要是对数据访问的优化，即减少访问的数据。上面对于sql查询语句的优化和创建索引，都是为了减少访问的数据。

参看链接：

https://www.zhihu.com/question/36431635/answer/459619138

2.2 数据库优化原理

2.2.1 优化原理的简单分析

说起MySQL的查询优化，相信大家积累一堆技巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。

MySQL逻辑架构
如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。

MySQL逻辑架构，来自：高性能MySQL

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

2.2.2MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？

MySQL查询过程

2.2.2.1客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

2.2.2.2 查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果
都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

用多个小表代替一个大表，注意不要过度设计
批量插入代替循环单条插入
合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

个人理解：这里就是对应了硬件优化中的：内存优化可以提高查询效率。

2.2.2.3 语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

2.2.2.4 查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

Mysql代码

mysql> select * from t_message limit 10;  
...省略结果集  
  
mysql> show status like 'last_query_cost';  
+-----------------+-------------+  
| Variable_name   | Value       |  
+-----------------+-------------+  
| Last_query_cost | 6391.799000 |  
+-----------------+-------------+

示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

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

重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）
提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。

2.2.2.5 查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

2.2.2.6返回结果给客户端

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

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

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

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

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

2.2.2.7 小结

通过上述MySQL的工作流程及原理的简单分析，对于数据库的优化，就是从减少访问与返回数据（降低数据传输成本）、降低I/O磁盘读写、较快得到最优执行计划这几个方面着手。

2.2.2.7 性能优化建议

https://www.cnblogs.com/szrs/p/12552370.html

参看链接：https://www.iteye.com/news/32381

2.3 性能优化具体步骤

2.3.1优化方向

1、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

2、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

3、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。

在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。

系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

4、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。

5、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。

6、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。

7、sql语句的执行计划是否正常；

8、减少应用和数据库的交互次数、同一个sql语句的执行次数；

9、数据库实体的碎片的整理；

10、减少表之间的关联，特别对于批量数据处理，尽量单表查询数据，统一在内存中进行逻辑处理，减少数据库压力；

11、对访问频繁的数据，充分利用数据库cache和应用的缓存；

12、数据量比较大的，在设计过程中，为了减少其他表的关联，增加一些冗余字段，提高查询性能。

2.3.2具体步骤