学习MySQL性能优化，只需这一篇就够了

最新推荐文章于 2024-08-03 10:34:30 发布

程序人生_小高

最新推荐文章于 2024-08-03 10:34:30 发布

阅读量255

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43954926/article/details/102872695

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

当提到MYSQL性能优化时，你想到的都有什么呢？相信大部分人张口就会说：当然是建索引了，然后就会是各种的索引失效原则。如果你也是这么说的话，那么看这篇文章你就看对了，这篇文章将会多维角度来讲解MySQL的性能优化

想要了解MySQL的性能优化一定需要先知道其查询过程，这样才可以根据查询的不同阶段采用不同的优化策略。MySQL的查询过程如图：

MySQL的查询过程为：
1. 建立连接：根据通信协议建立数据库连接
2. 传输与接收数据：从client端向MySQL传输SQL
3. 查询缓存：如果有缓存则直接返回结果
4. 解析器：如果没有缓存则通过解析器进行词法解析和语法解析，生成解析树
5. 预处理器：会检查生成的解析树，检查解析器无法解析的语义
6. 优化器：根据解析树生成不同的执行计划，然后选择一种最优的执行计划
7. 执行引擎：执行引擎根据执行计划给出的指令逐步执行得出结果，在执行过程中主要是利用存储引擎提供的 API 来完成对存储引擎的操作。
8. 存储引擎：主要用于组织数据的存储结构并且给执行引擎提供相应的API。

（敲敲小黑板）下面就是本文的重点

下面详细解释一下上述几点并给出相应的优化方案：
1. 建立连接：根据通信协议建立数据库连接。

知识普及
1. 通信协议：MySQL实现了四种通信协议：TCP/IP协议；Unix Socket协议；Share Memory协议；Named Pipes协议。
  1. TCP/IP协议：又叫：TCP/IP套接字，是基于三次握手的通信协议，任何系统下都可以使用的方式，也是使用最多的方式。
  2. Unix Socket协议：又叫Unix套接字，在Linux和Unix环境下，可以使用Unix套接字进行MySQL服务器的连接。
  3. Share Memory协议：又叫共享内存，只有windows可以使用，使用这个协议需要在配置文件中在启动的时候使用–shared-memory参数，注意的是，使用此协议，一个host上只能有一个server，所以这个东西一般没啥用的
  4. Named Pipes协议：这个协议也是只有windows才可以用，同shared memory一样，使用此协议，一个host上依然只能有一个server，即使是使用不同的端口也不行，Named Pipes 是为局域网而开发的协议。
2. 基于TCP/IP协议的连接方式：主要分为握手认证和命令执行两个阶段
3. 通信方式：有单工、半双工、全双工三种通信方式
  1. 单工：指的是数据只能单向传递，无法双向传递。
  2. 半双工：指的是数据可以双向传递，但是不能同时传输（A与B进行通信，同一时间只能由A发给给B或者由B发给A，并不能A、B同时发送）
  3. 全双工：数据双向传递并且可以同时传输
4. 连接方式：
  1. 短连接：就是操作完毕以后，马上 close 掉。
  2. 长连接：可以保持打开，后面的程序访问的时候还可以使用这个连接。
调优指南
1. 由于Share Memory协议和Named Pipes协议一个host上只能有一个server，尽量不用的好，一般就用TCP/IP协议就可以了。
2. 由于MySQL 使用半双工的通信方式，所以不要一次传输太多数据，如果发送给MySQL的数据包过大，就需要调整MySQL的max_allowed_packet参数的值（默认是4M，通过show variables like 'max_allowed_packet’查看），并且当大数据量列表查询时，尽量加上limit。
3. MySQL既支持短连接，也支持长连接，长时间不活动的连接，MySQL服务器会断开。MySQL【5.7版本】默认的最大连接数是151，支持的最大连接数为16384（2^14），可以根据需要适当修改超时断开时间以及最大连接数，并且使用数据库连接池。如：Druid，Hikari。
  - show global variables like ‘wait_timeout’;(非交互式超时时间，如JDBC程序)
  - show global variables like ‘interactive_timeout’;(交互式超时时间，如数据库连接工具)
  - show global variables like ‘max_connections’;(MySQL设置的最大连接数)
  - show full processlist;(查看执行状态)

2. 传输与接收数据：同上。
3. 查询缓存：如果有缓存则直接返回结果

知识普及
1. MySQL 内部自带了一个缓存模块。默认是关闭的。主要是因为 MySQL 自带的缓存的应用场景有限，第一个是它要求 SQL 语句必须一模一样。第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效。在MySQL5.8中，已经将查询缓存功能移除了
2. MySQL维护查询缓存会有很大的性能开销：试想一下，如果一张大数据量的表中有大量缓存，当修改某一条数据时，所有的缓存都会失效，那将会及其影响这条修改语句的效率。
- 调优指南
  1. 除非必要，尽量不要使用MySQL自带缓存功能
  2. 可以使用第三方缓存，如Redis

解析器：对查询语句进行词法解析和语法解析，生成解析树。
1. 词法解析：词法解析就是把一个完整的SQL语句打碎成一个个的单词。例如： select name from student where id = 5;会被打碎成8个符号，记录每个符号是什么类型的，从哪里开始到哪里结束。
2. 语法解析：语法解析会根据MySQL的语法规则对SQL做一些语法检查，如：select后是不是跟着查询字段，从而最终生成一个叫解析树的数据结构。
3. 理解了词法解析和语法解析，如果我说一条sql语句其实就是由预定义关键字、变量名以及常量组成，大家认可吗？如上面一条sql（select name from student where id = 5;）
  - 预定义关键字（符号）：select、from、where、=
  - 变量名：name[列名]、student[表名]（如果存在内置函数或者自定义函数，其实也只是在当前sql中记录了函数名这个变量，而函数里面也是由这三部分组成的）
  - 常量：5
    也就是说，我们写的任意一条sql语句，都是在mysql预定义关键字的语法之下，向特定位置填充的相应变量。这就是为什么最后可以生成解析树，因为解析器根据sql语句的预定义关键字就可以确定sql的整体结构，也就可以生成一棵树的枝干，只需要在生成解析树枝干的时候，再根据MySQL的语法规则把相应的变量名插入到适当的位置即可。
预处理器：会检查生成的解析树，检查解析器无法解析的语义
- 会检查生成的解析树，解决解析器无法解析的语义。比如他会检查表和列名是否存在，检查名字和别名，保证没有歧义。
优化器：根据解析树生成不同的执行计划，然后选择一种最优的执行计划
- 查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL里面使用的是基于开销的优化器，哪种执行计划开销最小，就用哪种。
- show status like ‘Last_query_cost’;代表了需要随机读取几个4k的数据页才可以完成查找
1. 想要查看优化器是怎么得到执行计划的，需要开启优化器的追踪（默认是关闭的）【show variables like ‘optimizer_trace’; set optimizer_trace=“enabled=on”;】,开启之后会消耗性能，因为它要把优化分析的结果写在表里面，所以不要轻易开启，或者查看完之后关闭它；然后执行sql语句【select name from innodb where id = 1; 】就会生成执行计划并记录在系统表里，然后查询【select * from information_schema.optimizer_trace\G;】即可,记得关掉它【set optimizer_trace=“enabled=off”; show variables like ‘optimizer_trace’;】
2. 优化器能够帮我们做什么呢？
  1. 当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表，一般情况下以小表驱动大表，原因在于关联时是拿驱动表里面的每一条数据到从表进行匹配；并且在多表联查时，根据笛卡尔积生成的中间表更小。
  2. 多个where条件时，选择进行过滤的顺序。【如：select * from student where a=1 and b=2 and c=3,如果c=3 的结果有 100 条，b=2 的结果有 200 条，a=1 的结果有 300 条，会按照c=3，b=2，a=1的顺序进行过滤，而不是where条件中定义的顺序】
  3. 如果查询条件中有一些恒等式，进行移除
  4. 查询数据，是不是能直接从索引里面取到值，需不需要回表。
  5. count()、min()、max()是不是能从索引中直接取到值
  6. 等等。
3. 优化器最终会把解析变成一个查询执行计划【是一个数据结构】，这个执行计划不一定最优的执行计划，但大概率是最优的。
4. MySQL提供了一个执行计划工具，在sql前面加EXPLAIN【explain select name from student where id=1】，实际中主要在建索引之后通过explain查看是否用到索引。因压缩篇幅explain详解、索引优化以及索引失效原则请查看：MySQL索引优化详解。
执行引擎：执行引擎根据执行计划给出的指令逐步执行得出结果，在执行过程中主要是利用存储引擎提供的 API 来完成对存储引擎的操作。
存储引擎：主要用于组织数据的存储结构并且给执行引擎提供相应的API。