MySQL数据库优化

最新推荐文章于 2023-09-25 15:55:03 发布

kuraki丶

最新推荐文章于 2023-09-25 15:55:03 发布

阅读量304

点赞数 1

分类专栏： Java 数据库 MySQL 文章标签： MySQL 数据库性能优化

本文链接：https://blog.csdn.net/lingboo111/article/details/88570203

版权

Java 同时被 3 个专栏收录

21 篇文章 0 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

MySQL数据库优化

客户/服务器端通信协议

MySQL客户端/服务端通信协议是半双工的：在任意时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。
当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。
在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

MySQL三层逻辑架构

在这里插入图片描述

第一层：连接层。负责连接管理，授权认证、安全等等。
- 每个客户端的连接都对应着服务器上的一个线程。服务器是哪个维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行验证。可以通过用户名和密码的方式进行认证，也可以通过SSL证书进行认证。
- 登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限—权限认证。
第二层：SQL处理层。MySQL的核心部分，通常叫做SQL Layer。
- 在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括**权限判断，sql解析，执行计划优化，query cache的处理以及所有内置的函数（如日期、时间、数学运算、加密）**等等。
- 各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。
- 负责解析查询（编译SQL），并对其进行优化（如调整表的读取顺序，选择合适的索引等）。
- 对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。
第三层：数据存储层，也叫存储引擎层。
- 负责在MySQL中存储数据、提取数据、开启一个事务等。存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。
- 存储引擎不会去解析SQL。

连接层

用户发起请求，连接/线程处理器开辟内存空间，开始提供查询的机制。
当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器都会新建一个线程处理（如果是线程池的话，则是分配一个空的线程），每个线程独立，拥有各自的内存处理空间，但是，如果这个请求只是查询，没关系；但是如果是修改数据，很显然，当两个线程修改同一块内存是会引发数据同步问题的。
连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（如，是否允许对某个数据库某个表的某个操作）。

SQL处理层

MySQL查询过程
- 很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。
查询缓存
- 在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
- MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。
- 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。
- MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
- 正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。
- 如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外。
  - 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存；
  - 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗；
- 所以，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。
优化查询
- MySQL会解析查询，并创建了一个内部数据结构（解析树）。然后对其进行各种优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等。
- 优化器并是不关心表使用了哪种存储引擎，但是存储引擎对服务器优化查询的方式是有影响的。优化器需要知道存储引擎的一些特性：具体操作的性能和开销方面的信息，以及表内数据的统计信息。例如，存储引擎支持哪些索引类型，这对于查询是非常有用的。
- 在解析查询之前，要查询缓存，这个缓存只能保存查询信息以及结果数据。如果请求一个查询在缓存中存在，就不需要解析，优化和执行查询了。直接返回缓存中所存放的这个查询的结果。
解析器、优化器
- 词法分析：扫描字符流，根据构词规则识别单个单词。
- 语法分析：在词法分析的基础上将单词序列组成语法短语，最后生成语法树，提交给优化器。
- MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划。
  - 注意：它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间。
语法解析和预处理
- MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。
- 预处理则会根据MySQL规则进一步检查解析树是否合法。如检查要查询的数据表和数据列是否存在等。
- 下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。
查询优化
- 由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
- MySQL使用基于成本的优化器。注意，是当前查询的成本，不等价于查询所需要的时间。
- MySQL认为的最优跟我们想的最优并不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。
查询执行引擎
- 在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。
- 整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。
- 实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。
返回结果给客户端
- 即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。
- 如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
- 结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无需存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。
- 结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。
总结：MySQL整个查询执行过程，总的来说分为5个步骤：
- 客户端向MySQL服务器发送一条查询请求；
- 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；
- 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划；
- MySQL根据执行计划，调用存储引擎的API来执行查询；
- 将结果返回给客户端，同时，如果可以，则缓存查询结果。

存储引擎层

存储引擎，也称为表类型，主要用来存储数据的。不同的存储引擎采用不同的技术（存储机制、索引机制、锁定机制）存储数据，这主要是为了满足数据存储要求。为了更好的数据处理效率采用不同的数据存储技术（即不同存储引擎）。
MySQL的存储引擎是插件式的，也就是说，用户可以随时切换MySQL的存储引擎，默认的是InnoDB。
MySQL的架构可以在多种不同场景中应用并发挥良好的作用。主要体现在存储引擎的架构上，插件式点的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MyISAM和InnoDB的区别

查看命令
- show engines;
- show variables like '%storage_engine%';

MySQL逻辑模块组成

在这里插入图片描述

Connectors:
- 指的是不同语言中与SQL的交互。
Management Services & Utilites
- 系统管理和控制工具（组件），主要用来回滚操作、恢复数据、数据的复制、迁移、元数据的管理等。
Connection Pool:连接池
- 管理缓冲用户连接，线程处理等需要缓存的需求。
- 负责监听对MySQL Server的各种请求，接收连接请求，转发所有连接请求到线程管理模块。
- 每一个连接上MySQL Server的客户端请求都会被分配（或创建）一个连接线程为其单独服务。
- 连接线程的主要工作就是负责MySQL Server与客户端的通信，接受客户端的命令请求，传递Server端的结果信息等。
- 线程管理模块则负责管理维护这些连接线程，包括线程的创建，线程的cache等。
SQL Interface:SQL接口
- 各种SQL接口，用来接收管理SQL命令，比如DML、DDL操作，存储过程，视图，触发器等；
- 接受用户的SQL命令，并且返回用户需要查询的结果。
Parser:解析器
- SQL命令传递到解析器的时候会被解析器验证和解析。
- 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
- 如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
Optimizer:查询优化器
- SQL语句在查询之前会使用查询优化器对查询进行优化。
- 它使用的是选取-投影-联接策略进行查询。以select uid,name from user where gender = 1;为例
  - 这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；
  - 先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤；
  - 将这两个查询条件联接起来生成最终查询结果。
Cache和Buffer:查询缓存
- 主要功能是将客户端提交给MySQL的Select类query请求的返回结果集cache到内存中，并与该query的一个hash值做一个对应。
- 当该Query所取数据的基表发生任何数据的变化之后，MySQL会自动使该query的Cache失效。
- Query Cache对性能的提高是非常显著的，当然它对内存的消耗也是非常大的。
- 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
存储引擎接口
- MySQL可以实现其底层数据存储引擎的插件式管理。
- MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身必需的。
- 注意：存储引擎是基于表的，而不是数据库。

SQL执行顺序

SQL语句

SELECT DISTINCT <select_list>
FROM <left_table> 
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行顺序

FROM <left_table>: 第一步，查询根据的表
ON <join_condition>: 第二步，多表查询时表与表连接的条件
<join_type> JOIN <right_table>: 第三步，表连接的右表
WHERE <where_condition>: 第四步，连接后的查询条件
GROUP BY <group_by_list>: 第五步，分组的依据
HAVING BY <order_by_condition>: 第六步，分组后的查询条件
SELECT: 第七步，查询后输出的结果字段
DISTINCT <select_list>: 第八步，对查询结果的筛选，过滤重复的数据
ORDER BY <order_by_condition>: 第九步，排序
LIMIT <limit_number>: 第十步，分页

索引

概念

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。简单地理解为：排序好了的快速查找的数据结构。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。
唯一索引默认都是使用B+树索引，统称索引。

索引的类型

MySQL索引的结构有：BTree索引、Hash索引、full-text全文索引、R-Tree索引。

BTREE索引

基本原理
- 一颗B树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17~35之间的磁盘块，P3表示大于35的磁盘块。**真实的数据存在于叶子节点，**即3、5、9、10、13、15、28、29、36、68、75、79、90。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在与数据表中。
查询过程如下：
- 如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。
- 在内存中用二分查找确定29在17~35之间，锁定磁盘块1的P2指针，内存事件因为非常短，可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO。
- 29在26~30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
B-Tree索引的限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引中的列；
- 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。
- 这些限制都和索引列的顺序存储有关系。或者说是索引顺序存储导致了这些限制。
【补充】：I/O的次数取决于B+树的高度H，假设当前数据表的数据为N，每个磁盘块的数据项的数量是M，则有：H=log(M+1)N，当数据量N一定的情况下，M越大，H越小；而M=磁盘块大小/数据项大小，磁盘块大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度也就越低。

Hash索引

哈希索引基于哈希表实现的，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值(hash code)，哈希值是一个较小的值，并且不同键值的行计算出来的哈希值不一样。哈希索引将所有的哈希值存储在索引中，同时保存指向每个数据行的指针，这样就可以根据，索引中寻找对应哈希值，然后再根据对应的指针，返回到数据行。
MySQL中只有memory引擎显式支持哈希索引，innodb是隐式支持哈希索引的。
哈希索引的限制
- 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用覆盖索引的优化方式去避免读取数据表。
- 哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序。
- **哈希索引页不支持部分索引列匹配查找。**因为哈希索引始终是使用索引列的全部内容计算哈希值的；
- 哈希索引只支持等值比较查询，包括=、in()、<=>，不支持任何范围查询。
- 访问哈希索引的数据非常快，除非有很多哈希冲突。
- 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

结论

索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变成顺序I/O。

索引的种类

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。
MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换；
MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

索引分为四大类：单列索引（普通索引，唯一索引，主键索引）、组合索引、全文索引、空间索引等。

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。
- 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
- 唯一索引： 索引列中的值必须是唯一的，但是允许为空值。
- 主键索引： 是一种特殊的唯一索引，不允许有空值。
组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
全文索引： 只有在MyISAM引擎上才能使用，只能在CHAR，VARCHAR，TEXT类型字段上使用全文索引。全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。索引总是对整个列进行，不支持前缀索引。

建立索引的原则

最左前缀匹配原则。MySQL会一直向右匹配直到遇到范围查询(>、<、BETWEEN、LIKE)就停止匹配。
等于(=)和IN可以乱序。MySQL的查询优化器会帮你优化成索引可以识别的模式。
尽量选择区分度高的列作为索引，区分度的公式是COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
索引列不能参与计算，尽量保持列"干净"。
尽可能的扩展索引，不要新建立索引。
单个多列组合索引和多个单列索引的检索查询效果不同。

索引的使用

创建索引

-- 1. 第一种创建索引的方式
     -- 创建表的语句 --                -- 创建索引的类别 --       -- 索引关键字--  --索引名字--
CREATE table 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...]    [INDEX|KEY]   [索引名字]
-- 哪个字段设置为索引-- 
(字段名) [ASC|DESC]; -- 对索引进行排序

-- 2. 第二种创建索引的方式
ALTER table 表名 ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][索引名](索引字段名)[ASC|DESC];

删除索引

-- 1. 第一种删除索引的方式
ALTER TABLE 表名 DROP INDEX 索引名;
-- 2. 第二种删除索引的方式
DROP INDEX 索引名 ON 表名;

查看索引

SHOW INDEX FROM 表名;

聚簇索引

所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。
聚簇索引就是**实际存储的循环顺序与数据存储的物理结构一致的一种索引，而一张表的物理结构只有一种，所以一张表的聚簇索引只能有一个。**通常默认是主键，但是可以手动修改。
非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据存储的物理结构没有关系；一张表对应的非聚簇索引可以有多条，根据不同的约束可以建立不同要求的非聚簇索引。
使用聚簇索引的情况：

使用情况描述	使用聚簇索引	使用非聚簇索引
列经常被分组排序	√	√
返回某范围内的数据	√	×
一个或极少的不同值	×	×
小数目不同的值	√	×
大数目不同的值	×	√
频繁更新的列	×	√
外键列	√	√
主键列	√	√
频繁修改索引列	×	√

索引的使用原则

适用原则
- 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引；
- 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
- 在不同值较少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。
- 不要在列上进行运算。这样会使得MySQL索引失效，也会进行全表扫描；
- 索引不会包含NULL列，如果列中包含NULL值都将不会被包含在索引中，复合索引中如果有一列含有NULL值那么这个组合索引都将失效，一般需要给默认值0或者' '字符串；
宜使用的情况：
- 在经常需要搜索的列上，可以加快索引的速度；
- 主键列上可以确保列的唯一性；
- 在表与表的连接条件上加上索引，可以加快连接查询的速度；
- 在经常需要排序(order by)，分组(group by)和的distinct列上加索引，可以加快排序查询的时间， (单独order by用不了索引，索引考虑加where或加limit)；
- 在一些where之后的<=、>=、BETWEEN、IN以及某个情况下的like建立字段的索引(B-TREE)；
- like语句的如果对pname字段建立了一个索引。当查询的时候的语句是pname lick '%ABC%'那么这个索引将不会起到作用，而pname like 'ABC%'那么将可以用到索引；
- 选择越小的数据类型越好，因为通常越小的数据类型通常在磁盘、内存、CPU、缓存中占用的空间很少，处理起来更快。
不宜使用的情况：
- 查询中很少使用到的列不应该创建索引。如果建立了索引然而还会降低mysql的性能和增大了空间需求；
- 很少数据的列也不应该建立索引。比如，一个性别字段 0或者1，在查询中,结果集的数据占了表中数据行的比例比较大，mysql需要扫描的行数很多,增加索引，并不能提高效率；
- 定义为text和image和bit数据类型的列不应该增加索引；
- 当表的修改(UPDATE，INSERT，DELETE)操作远远大于检索(SELECT)操作时不应该创建索引，这两个操作是互斥的关系。

索引的优缺点

优势
- 可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性；
- 建立索引可以大大提高检索的效率，以及减少表的检索行数；
- 在表连接的连接条件可以加速表与表直接的相连；
- 在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）；
- 建立索引，在查询中使用索引可以提高性能。
缺点
- 在创建索引和维护索引会耗费时间，随着数据量的增加而增加；
- 索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间；
- 当对表的数据进行**INSERT、UPDATE、DELETE的时候，索引也要动态的维护**。
总结
- 索引实际上是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间的；
- 虽然索引大大提高了查询速度，同时会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

索引优化

order by优化

官方文档介绍：即使ORDER BY语句不能精确匹配（组合）索引列也能使用索引；只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数或没有就行。
即要想提高order by的效率，需遵循如下几点：
- order by时，select *是一个大忌，只Query需要的字段，去掉返回中不需要的字段，这点非常重要。
- 尝试提高sort_buffer_size:不管是哪种算法，提高这个参数都会提高效率。当然，要根据系统的能力去提高，这个参数是针对每一个进程的。
- 尝试提高max_length_for_sort_data:提高这个参数，会增加用改进算法（单路排序）的概率。
order by子句，尽量使用Index方式排序，在索引列上遵循索引的最佳左前缀原则。尽量避免因索引字段的缺失或索引字段顺序的不同引起的FileSort排序。

GROUP BY优化

group by与order by的索引优化基本一样，group by实质是先排序后分组，也就是分组之前必排序，遵照索引的最佳左前缀原则可以大大提高group by的效率。
当无法使用索引列排序时，适当增大sort_buffer_size参数+适当增大max_length_for_sort_data参数可以提高filesort排序的效率。
注意：可能会出现Using temporary，也就是说mysql在对查询结果排序时使用了临时表。

慢查询

我们将超过指定时间的SQL语句查询称为慢查询。
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阙值（设置的一个时间参数）的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，然后再结合explain进行全面分析。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会带来一定的性能影响。
可以借助mysqldumpslow日志分析工具，分析SQL语句。
慢查询的优化步骤
- 查看运行效果，是否真的很慢，主要设置SQL_NO_CACHE；
- WHERE条件单表查询，锁定最小返回记录表。这句话的意思是，把查询语句的WHERE都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高；
- EXPLAIN查看执行计划，是否与预期一致（从锁定记录较少的表开始查询）。
- ORDER BY LIMIT形式的SQL语句，让排序的表优先查。

Show Profile

是MySQL提供可以从来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
默认情况下，参数处于关闭状态，该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。
根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。
性能优化的三大利器：
- 慢查询：分析出现问题的SQL语句。
- Explain
  - 显示了MySQL如何使用索引来处理SELECT语句以及连接表。
  - 可以帮助选择更好的索引和写出更优化的查询语句。
- Profile
  - 查询到SQL会执行多少时间, 并看出CPU/Memory使用量, 执行过程中Systemlock，Table lock花多少时间等等。
分析步骤
- 是否支持：show variables like 'profiling';
- 开启功能：set profiling = on;
- 运行SQL
- 查看结果：show profiles;
- 诊断SQL：Show profile type, block type for query 问题sql数字号码;。其中type类型：
  - ALL: 显示所有的开销信息
  - BLOCK IO：显示块IO相关开销
  - CONTEXT SWITCHS: 上下文切换相关开销
  - CPU : 显示cpu相关开销
  - IPC: 显示发送和接收相关开销
  - MEMORY：显示内存相关开销
  - PAGE FAULTS：显示页面错误相关开销信息
  - SOURCE：显示和Source_function, Source_file, Source_line相关的开销信息
  - SWAPS：显示交换次数相关的开销信息

Explain

概念

EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或表结构的性能瓶颈。

各字段的解释

在这里插入图片描述

id
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，具体如下：
  - id相同，执行顺序由上至下
  - 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  - 总结：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
select_type
- 查询的类型，主要用于区别：普通查询、联合查询、子查询等的复杂查询
- SIMPLE:简单的select查询，查询中不包含子查询或者UNION；
- PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
- SUBQUERY:在SELECT或WHERE列表中包含了子查询；
- DERIVED:用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为派生表，因为该临时表是从子查询中派生出来的。
- UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED；
- UNION RESULT:从UNION表获取结果的SELECT。
table: 显示这条语句查询的数据是来自哪一张表的。
type:显示查询使用了何种类型，从最好到最差依次是：

在这里插入图片描述

NULL > system > const > eq_ref > ref > range > index > ALL
- NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。
- system: 表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
- const:表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快；当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问；如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref:唯一性索引扫描。对于每个索引键值，表中只有一条记录与之配，常见用于主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者unique key作为关联条件。
- ref:非唯一性索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的记录行。然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
- range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index: Full Index Scan，index与ALL区别为index类型只遍历索引树。**这通常比ALL快，因为索引文件通常比数据文件小。
- ALL:Full Table Scan，将遍历全表以找到匹配的行。
possible_keys
- 显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被抛出，但不一定被查询使用.
key
- 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL；
- 查询中若使用了覆盖索引，则该索引仅出现在key列表中。
key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。
ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
Extra
- Using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称之为"文件排序"。
- Using temporary:使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- Using index:表明相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而没有执行查找动作。
  - MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，即包含所有满足查询需要的数据的索引称为覆盖索引。
  - 一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
- Using where:表示MySQL服务器将在存储引擎检索行后再进行过滤。不是所有带where字句的查询都会显示**Using where**。
- Using join buffer:该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。
- impossible where: 这个值强调了where语句会导致没有符合条件的行。
- select tables optimized away: 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
- distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
- index merges: 当MySQL决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。

总结

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
EXPLAIN不考虑各种缓存。
EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
部分统计信息是估算的，并非精确值；
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
作用：

1.表的读取顺序；2. 数据读取操作的操作类型；3. 哪些索引可以使用；
4. 哪些索引被实际使用；5. 表之间的引用；6. 每张表有多少行被优化器查询。

kuraki丶

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录