mysql服务器为什么磁盘io会飙高 服务器 mysql

▎MySQL服务器的逻辑架构

采用的是分层架构；从整体上重点可以分为Server层和存储引擎层，如图：

• 第一层：处理客户端连接、授权认证等
• 第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等
• 第三层：存储引擎，负责MySQL中数据的存储和提取。

整个的执行过程如下：

一、连接器

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行！

当客户端应用连接到MySQL服务器时，首先接待它的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

☛ 连接mysql 的命令：mysql -u [用户名] -p

✦ 连接器作用

• 认证用户名密码：失败，返回1045 error，客户端程序结束执行。成功，与服务器建立连接。
• 验证用户权限：连接成功后，继续验证用户的权限，比如有哪些表的查询/修改/授权权限。

注意：验证权限完毕后，该连接中的权限判断，都是基于此时读到的权限。如果修改了权限，一定要记得重新连接!

✦ 连接器类型

• 长连接：连接成功后，如果客户端持续有请求，则一直使用通过一个连接。
• 短连接：有数据交互就建立一个连接，完成后关闭连接。即每次连接只完成一项业务的发送

长连接主要用于在少量客户端与服务端的频繁通信，如用短连接频繁通信常会发生 Socket 出错，并且频繁创建 Socket 连接也是对资源的浪费。

建议使用长链接的，建立连接的过程比较复杂，应该尽量减少建立连接的动作。

二、查询缓存

MySQL缓存的数据是以key-value的形式存在的，key就是sql语句，并区分大小写，空格等，即两个 sql 必须完全一致才会导致 cache 命中。value就是sql语句对应的查询结果。

！注意：MySQL 8.0 版本开始，缓存功能已被删除

与服务器建立连接后，所执行的sql语句，例如： select * from user where id =1; 

会先查询缓存中是否存在，如存在，无需后面的解析和执行步骤，提高效率。如不存在，才会去表里查询数据，再将结果集添加到缓存中。

✦ 缓存何时失效 和重构？

针对频繁操作的表，缓存将会成为系统的负担。

在表结构 或 表数据发生改变时，该表的所有缓存都会失效。如表数据更新，无论是否影响到了cache的数据，该表的所有缓存都会失效。

☁ 思考：为什么不判断修改的是否 cache 的内容再决定失效缓存？

答：因为分析 cache 内容太复杂，服务器需要追求最大的性能。

✦ 缓存组件相关命令

-- 查询缓存是否可用
show variables like '%have_query_cache%'; 

-- 查询缓存是否开启  OFF：关闭  ON:开启中
select @@query_cache_type; 

-- sql设置缓存开关   0表示关闭；  1表示开启缓存；  2表示SQL_CACHE才缓存
set global query_cache_type = 0; 

注意：也可以修改my.cnf配置文件(Windows是my.ini文件)，添加配置：query_cache_type=0;

如关闭缓存后，某些sql语句希望能使用缓存，可通过SQL_CACHE显式的指定sql使用缓存。例如：select SQL_CACHE * from user; 

三、解析器

真正执行sql语句时，解析器会先分析该条sql语句。解析成内部数据结构(解析树)，然后优化器就可以对其优化。

sql语句主要是字符串和空格组成。第一步解析出字符串识别其含义。第二步校验语法规范。

✦ select id from

例如识别sql关键字，将字符串 'student' 识别为表名，把 'id' 识别为列。解析器完成字符串识别后，开始校验给定的字符串是否符合语法规范。

☛ 如果表或者列不存在，或者语法有问题，可以收到错误信息：

四、优化器

（MySQL在开始执行语句之前还会对sql进行优化）

一条sql 有很多执行方式，最后结果都相同。优化器的作用就是找到其中最好的执行计划，即查询树，由一系列物理操作符组成，这些操作符按照一定的运算关系组成查询的执行计划。

MySQL的优化器主要是将SQL经过语法解析/词法解析后得到的语法树，通过MySQL的数据字典和统计信息的内容，经过一系列运算，从而得出一个执行计划树的构成。

即优化器的输入是一个语法树，输出是一个执行树（或执行计划），优化器的具体模块参考如下：

✦ 优化器作用

最主要的目标是 尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。

• 选择最合适的索引；
• 选择表扫还是走索引；
• 选择表关联顺序；
• 优化 where 子句；
• 排除管理中无用表；
• 决定 order by 和 group by 是否走索引；
• 尝试使用 inner join 替换 outer join；
• 简化子查询，决定结果缓存；
• 合并试图；

★

MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行。 在一些情况下，会用到临时表：比如在 FROM 子句中遇到子查询时，会先执行子查询并将其结果放到一张临时表中；在 UNION 查询时先将一系列查询结果放到临时表中。

注意，临时表使用的是 Memory 存储引擎，是没有任何索引的。

优化器在处理关联查询时，会通过更改关联顺序来让查询进行更少的嵌套循环和回溯操作。即让首先执行的查询结果集尽可能小，减少下一个查询的数据范围。

★

如果是单个索引，查询优化器，会选择最合适的索引。如果是复合索引，则会选择复合索引

1. 创建一张表（某奶茶店订单日志记录）

CREATE TABLE log(
    id int NOT NULL ,
    menuName varchar(20) NOT NULL ,  -- 菜单
    property varchar(20) NOT NULL,   -- 加料属性
    KEY menuName_key(`menuName`),
    KEY property_key(`property`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入几条数据如下：

☛ 执行一：假设我要知道今天，加了红豆的波霸奶茶售卖了多少杯，那么优化器如何优化的呢？

-- 注意：where后面的 menuName和 property 无论是什么顺序，结果都是一样的
select  *  from  log  where  menuName="波霸奶茶"  and  property="加红豆";

答：优化器会先分析数据表，得知有 menuName_key 和 property_key

1. 先判断 menuName_key 索引，从7条数据中排出不是 “ 波霸奶茶 ” 的订单，即3条
2. 先判断 property_key 索引，从7条数据中排出没有加 “ 红豆 ” 的订单，即5条

结论：按照优化器的原理：找到其中最好的执行计划。故会选择第2种，先检验property_key索引，再从中挑出 menuName="波霸奶茶" 的数据行。虽然二者最终的执行结果没区别，但第2种方式排除的数据行多，减少了磁盘的输入输出。

► 我们可以使用explain执行计划，来查看具体：

✧ 解析：可以选择的索引有menuName_key 和 property_key，实际用到的索引只有property_key

• possible_keys：查询涉及到的字段上若存在索引，则该索引将被列出。
• key：在查询中实际使用的索引，若没有使用索引，显示为NULL。

☛ 执行二：增加复合索引，那么优化器如何优化的呢？

两个或更多个列上的索引被称作复合索引。

-- 创建复合索引
ALTER TABLE log ADD KEY menu_property_key(`menuName`,`property`);

如果有复合索引，会优先选择复合索引：

☛ 总结：查询优化器在查询时，优化器会选择最合适的索引，索引能够加速查找效率，反之如索引过多，不仅仅是给数据的更新和插入带来了压力，同时也增加了优化器的压力。

☁ 其他扩展：调节优化器的优化

优化器会自动选择合适的索引，如能够知道哪些索引 不会用到 或 会用到，可使用 

• force index (索引1...) 或 use index (索引1...) ：强制指定某些索引
• ignore index(索引1...)  ：忽略某些索引

a. 强制使用menuName_key  索引

b. 忽略menuName_key 索引 和  menu_property_key 复合索引

★

排序是一个成本很高的操作，尽可能通过索引进行排序。

将需要排序的字段加入联合索引中，如不能使用索引生成排序结果，MySQL 就需自己进行排序，在数据小的时候在内存中进行，数据量大时需借助磁盘，MySQL 自己排序的过程叫文件排序。

MySQL 的排序算法：

two-pass (旧版本)： 读取行指针和需排序的字段对其排序，再根据排序结果读取所需的数据行。

• 缺点：两次数据传输，第二次将产生大量随机 IO
• 优点：排序时存储尽可能少的数据，让“排序缓存区”中容纳尽可能多的行数进行排序

single-pass (新版本)：先读取查询所需的行和列，再根据给定列进行排序，最后直接返回排序结果

• 优点：一次数据传输，无需任何的随机 IO。对 IO 密集型应用效率更高
• 缺点：返回的列非常多、非常大。额外占用大量的空间

优化器在处理文件排序时，如果 ORDER BY 子句中的所有列都来自关联的第一个表，则 MySQL 在关联处理的第一个表的时候就进行文件排序。

五、执行器

执行开始之前，先验证是否有对该表的权限， 若无，返回无权限的错误信息。若有，就打开表继续执行。打开表时，执行器会根据表的引擎定义，去使用这个引擎提供的接口。

✦ 举例说明：比如 log 表中的数据如下 

☛ 执行sql语句:  select  *  from  log  where  id = 10；其中 id 字段没有索引，执行器流程如下：

1. 调用 InnoDB 引擎接口取 log表的第一行，判断 id =10，不是则跳过，是则将该行放入结果集
2. 调用引擎接口取下一行，重复第一步逻辑，直到取到该表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

没有索引需要取出所有数据，一个个进行比较；而有索引则是直接取满足条件的数据；

对于有索引的表，执行的逻辑也差不多。第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口，这些接口都是引擎中已经定义好的。

六、存储引擎

存储引擎层负责数据的存储和提取。

存储引擎是插件式的，支持InnoDB、MyISAM、Memory等多种存储引擎，MySQL也提供了一些第三方的存储引擎，插件式的结构设计，可以灵活根据自己的需求选择不同的引擎。

其中InnoDB 是 MySQL5.5版本之后默认的存储引擎。

——关于更多存储引擎的介绍信息，可手动翻阅我MySQL专栏中存储引擎的文章，谢谢！

▎总结

MySQL逻辑架构主要分为Server层和存储引擎层。

• Server层负责连接、缓存、解析 、优化、执行。
• 存储引擎负责数据的存储和提取。

存储是插件式的，可以根据每个表的需求选择不同的存储引擎，但不建议。除非需要用到InnoDB不具备的特性，并且无法解决只能选择其他引擎的情况下才需要修改，否则都按照默认的InnoDB引擎。而且除非万不得已，不建议混合使用多种引擎。

✩ 另外扩展：在讲解优化器时讲到了复合索引，需要注意几点

复合索引有最左匹配原则

如果有复合索引 (a, b, c) 那么 where a, b, c、where a, b、where a

但是 where b, c 就不走索引了，因为按照左前缀原则，必须要出现 a 才行。

查询条件	实际使用
a, b, c	a, b, c
a, c	a
a, b	a, b
a	a
b, c	无

✦ 解析

• 第二行：虽然 a, c 是联合索引(a, b, c) 中有两个字段，但未出现 b，按照左前缀原则就断开了，所以只能使用到 a
• 注意where 后的顺序可随意组装，比如 a, b, c 可以写成 a, c, b、 c, a, b 等，不影响结果，跟出现的字段有关

除了 where 外，还有 order。

• 如果是where a order c ：只会用到 (a, b, c) 中的 (a) 
• 如果是where a order b ：则能用到 (a, b)