Mysql高级优化（一）

以下内容为本人MySql高级优化整理，转载请附上原文链接

Mysql 执行流程

1> 客户端 : 并非MySQL所独有，诸如 : 连接处理、授权认证、安全等功能均在这一层处理
2> 核心服务 : 包括查询解析、分析、优化、缓存、内置函数(比如 : 时间、数学、加密等函数)，所有的跨存储引擎的功能也在这一层实现 : 存储过程、触发器、视图等
3> 存储引擎 : 负责 MySQL 中的数据存储和提取，和 Linux 下的文件系统类似，每种存储引擎都
有其优势和劣势，中间的服务层通过 API 与存储引擎通信，这些 API接口 屏蔽不同存储引擎间的
差异

组成部分：

1. 连接池组件
2. 管理服务和工具组件
3. Sql接口组件
4. 查询分析器组件
5. 优化器组件
6. 缓冲组件
7. 插件式存储引擎(与其他数据库的主要区别)
8. 物理文件

MySQL数据库区别与其他数据库最重要的一个特点就是期插件式的表存储引擎，mysql插件式的表存储引擎 结构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都 必须的，例如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者可以按照自 己的意愿来进行开发，需要特别注意的是，存储引擎是基于表的，而不是数据库

mysql的体系结构可以分为两层，mysql server层和存储引擎层，在mysql server层中又包括连接层与sql层。

1. 连接层
客户端或者应用程序通过接口(如：ODBC，JDBC)来连接MySQL，最先连接处理的就是连接层，连接层 包括通信协议，线程处理，用户名密码认证三个部分，通信协议负责检测客户端版本是否与服务端 兼容，线程处理是指每一个连接请求都会分配一个对应独立的线程，用户名密码认证创建的账号和 密码，以及host主机授权是否可以连接到mysql服务器

2. sql层
sql层包含权限判断，查询缓存，解析器，预处理，查询优化器，缓存和执行计划； 1) 权限判断可以通过审核用户有没有访问某个库，某个表，或者表里某行的权限。 2) 查询缓存通过query cache进行操作，如果数据在query cache中，则直接返回结果给客户端 3) 查询解析器针对sql语句进行解析，判断语法是否正确，并生成解析器 4) 预处理器解决解析器无法解析的语义 5) 优化器对sql语句进行改写和相应的优化,例如：对连接表重排序，对外连接转内连接，代数等价 法则，计算和减少常量表达式，自查询优化，早期终结，相等传递等，并生成最优的执行计划，然 后就可以调用程序的API接口，通过存储引擎层访问数据；

3. 存储引擎层
MySQL数据库的核心就在与存储引擎； mysql数据库是开源的，所以用户可以根据mysql预定义的存储引擎接口编写自己的存储引擎，如果对现有 存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性。 存储引擎可以分为mysql官网存储引擎和第三方存储引擎，innodb存储引擎早期就是第三方存储引擎，后来 被oracle收购，同时也是mysql数据量OLTP在线事务处理应用最广泛的存储引擎。
3.1 Innodb存储引擎 Innodb存储引擎支持事务，其设计目标主要面向在线事务处理的应用，其特点是行锁设计，支持外 键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁，从mysql数据库5.5版本开始， Innodb存储引擎是默认的存储引擎。

Innodb存储引擎将数据放在一个逻辑表空间中，这个表空间就行黑盒一样由Innodb存储引擎自身进 行管理，从mysql4.1版本开始，他可以将每个Innodb存储引擎的表单独存放到一个独立的ibd文件 中，此外，Innodb存储引擎支持用裸设备来建立其表空间。 Innodb存储引擎通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了sql标准的4中隔离级 别，默认为RR级别，同时，使用一种被称为next-key locking的策略来避免幻读现象的产生，除此 之外，Innodb存储引擎还提供了插入缓存，二次写入，自适应hash索引，预读等高性能和高可用的 功能。对于表中的数据存储，Innodb存储引擎采用聚集的方式，因此每张表的存储都是按主键的顺 序进行存放，如果没有显示在表定义时指定主键，Innodb存储引擎会为每一行生成一个6字节的 ROWID，并以此作为主键。

3.2 myisam存储引擎 myisam存储引擎不支持事务。支持全文索引，主要面向一些OLAP数据应用，mysql5.5版本之前 myisam存储引擎是默认的存储引擎，数据库系统与文件系统很大的一个不同之处在于对事务的支 持，然而myisam存储引擎是不支持事务的，这个也不是很难理解，试想一下用户是否在所有的应用 中都需要事务呢？在数据仓库中，如果没有ETL这些操作，只是简单的报表查询是否还需要事务的支 持呢？此外，Myisam存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不是缓冲 数据文件，这一点和大多数的数据库都非常不同。 Myisam存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件，可以通过使用 myisampack工具进一步压缩数据文件，因为myisampack工具使用赫夫曼编码静态算法来压缩数据， 因此使用myisampack工具压缩后的表是只读的，也可以通过myisampack来解压数据文件 在mysql5.0版本之前，myisam默认支持表大小为4GB，如果需要支持大于4GB的myisam表时，则需要 指定MAX_WORS和AVG_ROW_LENGTH属性，从mysql5.0版本开始，myisam默认支持256GB的表数据，这足 够满足一般的需求

MySQL 整个查询执行过程，总的来说分为 5 个步骤 :

1. 客户端向 MySQL 服务器发送一条查询请求 2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段 3. 服务器进行 SQL解析、预处理、再由优化器生成对应的执行计划 4. MySQL 根据执行计划，调用存储引擎的 API来执行查询 5. 将结果返回给客户端，同时缓存查询结果
客户端/服务端通信协议 MySQL客户端/服务端通信协议 是 “半双工” 的，在任一时刻，要么是服务器向客户端发送数据，要么是客 户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才 能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独 的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet参数，如 果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通 常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果， 而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返 回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一 查询缓存 在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的 数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查 询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表 (不要理解成table，可 以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的 数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果 都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，再 比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样 的查询结果缓存起来没有任何的意义 MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关 的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失 效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会 儿，而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外 : 1. 任何的查询语句在开始之前都必须经过检查，即使这条 SQL语句 永远不会命中缓存 2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗 基于此，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型 应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查 询缓存，并在数据库设计上做一些优化 : 1. 用多个小表代替一个大表，注意不要过度设计 2. 批量插入代替循环单条插入 3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适 4. 可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存 注 : SQL_NO_CACHE 是禁止缓存查询结果，但并不意味着 cache 不作为结果返回给 query，之前的 缓存结果之后也可以查询到

可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些 一天只执行一次的查询，都可以指定不进行查询缓存，使用 SQL_NO_CACHE 选项。对于那些变化不频繁的 表，查询操作很固定，可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是 从缓存获得结果，可以有效地改善查询的性能，使用 SQL_CACHE 选项

查看开启缓存情况

对于查询缓存的一些操作 FLUSH QUERY CACHE : 清理查询缓存内存碎片 RESET QUERY CACHE : 从查询缓存中移出所有查询 FLUSH TABLES : 关闭所有打开的表，同时该操作将会清空查询缓存中的内容 查询优化 经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条 查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。 MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的 一个。在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本 mysql> SELECT * FROM p_product_fee WHERE total_price BETWEEN 580000 AND 680000; mysql> SHOW STATUS LIKE ‘last_query_cost’; # 显示要做多少页的随机查询才能得到最后一查 询结果， 这个结果是根据一些列的统计信息计算得来的，这些统计信息包括 : 每张表或者索引的 页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等 有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成 本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样 (我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短) 等等

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划 :
1. 重新定义表的关联顺序 (多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可 以指定关联顺序) 2. 优化 MIN() 和 MAX()函数 (找某列的最小值，如果该列有索引，只需要查找 B+Tree索引 最左端，反之 则可以找到最大值) 3. 提前终止查询 (比如 : 使用 Limit 时，查找到满足数量的结果集后会立即终止查询) 4. 优化排序 (在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排 序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数 据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多) 查询执行引擎 在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步 执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。实际上，MySQL在查询优化阶段就为每一 张表创建了一个 handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、 索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一 样完成了一次查询的大部分操作 返回结果给客户端 查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相 关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL 也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条 结果时，就开始向客户端逐步返回结果集。这样服务端就无须存储太多结果而消耗过多内存，也可以让客 户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足客户端/服务器通信协议的 数据包发送，再通过 TCP协议 进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送

Mysql 物理文件

1. 数据库的数据存储文件
2. 慢查询日志
3. 错误日志与二进制文件
4. 二进制文件基本操作
5. 使用二进制文件恢复数据

1. 数据库的数据存储文件 MySQL数据库会在data目录下面简历一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同 的数据库引擎，每个表的扩展名也不一样 ，例如： MyISAM用“.MYD”作为扩展名，Innodb用“.ibd”， Archive 用“.arc”，CSV 用“.csv “.FRM”文件 无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的’.frm’文件。frm文件主要存放与表相关的 数据信息，主要包括表结构的定义信 息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。 “.MYD”文件
“.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与 之对应，同样存放于所属数据库的文件夹 下， 和“.frm”文件在一起。 “.MYI”文件 “.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说， 可以被cache 的内容主要就是来源 于“.MYI”文件中。 每一个MyISAM表对应一个“.MYI”文件，存放于位置 和“.frm”以及“.MYD”一样。 “.ibd”文件与”.ibdata”文件 这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为 Innodb的数据存储方式能够通过配置来决 定是使用共享 表空间存放存储数据，还是独享表空间存放存储数 据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件 ，文件存放在和MyISAM数据 相 同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个 （或者多个， 可自行配置）ibdata文件。 ibdata文件可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称) 两个参数配置组成 innodb_data_file_path中可以一次配置多个ibdata文件

innodb_data_file_path中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方 式

共享表空间以及独占表空间都是针对数据的存储方式而言的。 共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。 独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有 一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。

两者对比 （1.）共享表空间： 优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。 缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大 量删除操作后表空间中将会有大量的空 隙，特别是对于统计分 析，日值系统这类应用最不适合用共享表空 间。 （2.）独立表空间： 优点: 1. 每个表都有自已独立的表空间。 2. 每个表的数据和索引都会存在自已的表空间中。 3. 可以实现单表在不同的数据库中移动。 4. 空间可以回收 a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回 缩不用的空间。 b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 缺点：单表增加过大，如超过100 个G。 相比较之下，使用独占表空间的效率以及性能会更高一点 共享表空间和独立表空间之间的转换

Mysql 慢查询日志

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

慢查询相关参数

MySQL 慢查询的相关参数解释： slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会 默认给一个缺省的文件host_name-slow.log slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统 则会默认给一个缺省的文件host_name-slow.log long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。 log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。 log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。 log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据 库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系 统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获 得更高的系统性能，那么建议优先记录到文件。

###慢查询日志配置
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开 启，如下所示：

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如 果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。

修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器，如下 所示

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控 制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运 行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间，
之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

如上所示，修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？ 注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改 值。你用show variables like ‘long_query_time’查看是当前会话的变量值，你也可以不用重新连接会话，而是 用show global variables like ‘long_query_time’; 如下所示：

在MySQL里面执行下面SQL语句，然后我们去检查对应的慢查询日志，就会发现类似下面这样的信息。

log_output 参数是指定日志的存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。 log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据 库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系 统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获 得更高的系统性能，那么建议优先记录到文件。

日志分析工具

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 查看mysqldumpslow的帮助信息：

本作品采用《CC 协议》，转载必须注明作者和本文链接