Mysql学习宝典（五）--Mysql性能优化万字长⽂（慢SQL查询，Explain执⾏
计。。。
MySQL 性能优化思路和⼯具
⼀、优化思路
作为架构师或者开发⼈员，说到数据库性能优化，你的思路是什么样的？
或者具体⼀点，如果在⾯试的时候遇到这个问题：你会从哪些维度来优化数据库，你会怎么回答？
我们说到性能调优，⼤部分时候想要实现的⽬标是让我们的查询更快。⼀个查询的动作⼜是由很多个环节组成的，每个环节都会消耗时间，我们在第⼀节课讲 SQL 语句的执⾏流程的时候已经分析过了。
我们要减少查询所消耗的时间，就要从每⼀个环节⼊⼿。
⼆、连接——配置优化
第⼀个环节是客户端连接到服务端，连接这⼀块有可能会出现什么样的性能问题？有可能是服务端连接数不够导致应⽤程序获取不到连接。⽐如报了⼀个 Mysql: error1040: Too many connections 的错误。
我们可以从两个⽅⾯来解决连接数不够的问题：
1、从服务端来说，我们可以增加服务端的可⽤连接数。
如果有多个应⽤或者很多请求同时访问数据库，连接数不够的时候，我们可以：
（1）修改配置参数增加可⽤连接数，修改 max_connections 的⼤⼩：
show variables like'max_connections';-- 修改最⼤连接数，当有多个应⽤连接的时候
（2）或者，或者及时释放不活动的连接。交互式和⾮交互式的客户端的默认超时时间都是 28800 秒，8 ⼩时，我们可以把这个值调⼩。show global variables like'wait_timeout';--及时释放不活动的连接，注意不要释放连接池还在使⽤的连接
2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每⼀次执⾏SQL 都创建⼀个新的连接，应该怎么做？
这个时候我们可以引⼊连接池，实现连接的重⽤。
我们可以在哪些层⾯使⽤连接池？ORM 层⾯（MyBatis ⾃带了⼀个连接池）；或者使⽤专⽤的连接池⼯具（阿⾥的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、⽼牌的 DBCP 和 C3P0）。
我们这⾥说到了从数据库配置的层⾯去优化数据库。不管是数据库本⾝的配置，还是安装这个数据库服务的操作系统的配置，对于配置进⾏优化，最终的⽬标都是为了更好地发挥硬件本⾝的性能，包括 CPU、内存、磁盘、⽹络。
在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。
在我们这⼏天的课程⾥⾯也接触了很多的 MySQL 和 InnoDB 的配置参数，包括各种开关和数值的配置，⼤多数参数都提供了⼀个默认值，⽐如默认的 buffer_pool_size，默认的页⼤⼩，InnoDB 并发线程数等等。
这些默认配置可以满⾜⼤部分情况的需求，除⾮有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的⼯作⼀般由专业的DBA 完成。
⾄于硬件本⾝的选择，⽐如使⽤固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这些，更不是我们开发⼈员关注的重点，这个我们就不做过多的介绍了。
除了合理设置服务端的连接数和客户端的连接池⼤⼩之外，我们还有哪些减少客户端跟数据库服务端的连接数的⽅案呢？
我们可以引⼊缓存。
三、缓存——架构优化
3.1 缓存
在应⽤系统的并发数⾮常⼤的情况下，如果没有缓存，会造成两个问题：⼀⽅⾯是会给数据库带来很⼤的压⼒。另⼀⽅⾯，从应⽤的层⾯来说，操作数据的速度也会受到影响。
我们可以⽤第三⽅的缓存服务来解决这个问题，例如 Redis。
运⾏独⽴的缓存服务，属于架构层⾯的优化。
为了减少单台数据库服务器的读写压⼒，在架构层⾯我们还可以做其他哪些优化措施？
3.2 主从复制
如果单台数据库服务满⾜不了访问需求，那我们可以做数据库的集⽅案。
集的话必然会⾯临⼀个问题，就是不同的节点之间数据⼀致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持⼀致？
这个时候我们需要⽤到复制技术（replication），被复制的节点称为 master，复制的节点称为 slave。
主从复制是怎么实现的呢？在第⼀节课我们说过，更新语句会记录 binlog，它是⼀种逻辑⽇志。
有了这个 binlog，从服务器会获取主服务器的 binlog ⽂件，然后解析⾥⾯的 SQL 语句，在从服务器上⾯执⾏⼀遍，保持主从的数据⼀致。
这⾥⾯涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写⼊中继⽇志，这个线程叫做 I/O 线程。
Master 节点上有⼀个 log dump 线程，是⽤来发送 binlog 给 slave 的。
从库的 SQL 线程，是⽤来读取 relay log，把数据写⼊到数据库的。
这个是主从复制涉及到的三个线程。
做了主从复制的⽅案之后，我们只把数据写⼊ master 节点，⽽读的请求可以分担到slave 节点。我们把这种⽅案叫做读写分离。
读写分离可以⼀定程度低减轻数据库服务器的访问压⼒，但是需要特别注意主从数据⼀致性的问题。
我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过⼤的时候，⽐如⼀张表有上亿的数据，单表的查询性能还是会下降，我们要进⼀步对单台数据库节点的数据进⾏拆分，这个就是分库分表。
3.3 分库分表
垂直分库，减少并发压⼒。⽔平分表，解决存储瓶颈。
垂直分库的做法，把⼀个数据库按照业务拆分成不同的数据库：
⽔平分库分表的做法，把单张表的数据按照⼀定的规则分布到多个数据库。
以上是架构层⾯的优化，可以⽤缓存，主从，分库分表
第三个环节：
解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever ⾃⼰处理，跳过。
第四步：优化器
四优化器——SQL 语句分析与优化
优化器就是对我们的 SQL 语句进⾏分析，⽣成执⾏计划。
问题：在我们做项⽬的时候，有时会收到 DBA 的邮件，⾥⾯列出了我们项⽬上⼏个耗时⽐较长的查询语句，让我们去优化，这些语句是从哪⾥来的呢？
我们的服务层每天执⾏了这么多 SQL 语句，它怎么知道哪些 SQL 语句⽐较慢呢？
第⼀步，我们要把 SQL 执⾏情况记录下来。
4.1 慢查询⽇志 slow query log
4.1.1 打开慢⽇志开关
因为开启慢查询⽇志是有代价的（跟 bin log、optimizer-trace ⼀样），所以它默认是关闭的：
show variables like'slow_query%';
除了这个开关，还有⼀个参数，控制执⾏超过多长时间的 SQL 才记录到慢⽇志，默认是 10 秒。
show variables like'%long_query%';
可以直接动态修改参数（重启后失效）。
set @@global.slow_query_log=1;-- 1 开启，0 关闭，重启后失效
set @@global.long_query_time=3;-- mysql 默认的慢查询时间是 10 秒，另开⼀个窗⼝后才会查到最新值
show variables like'%long_query%';
show variables like'%slow_query%';
或者修改配置⽂件 myf。
以下配置定义了慢查询⽇志的开关、慢查询的时间、⽇志⽂件的存放路径。
slow_query_log =ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
模拟慢查询：
select sleep(10);
查询 user_innodb 表的 500 万数据（检查是不是没有索引）。
SELECT*FROM`user_innodb`where phone ='136';数据库优化sql语句
4.1.2 慢⽇志分析
1、⽇志内容
show global status like'slow_queries';-- 查看有多少慢查询
show variables like'%slow_query%';-- 获取慢⽇志⽬录
cat /var/lib/mysql/ localhost-slow.log
有了慢查询⽇志，怎么去分析统计呢？⽐如 SQL 语句的出现的慢查询次数最多，平均每次执⾏了多久？⼈⼯⾁眼分析显然不可能。
2、mysqldumpslow
MySQL 提供了 mysqldumpslow 的⼯具，在 MySQL 的 bin ⽬录下。
mysqldumpslow --help
例如：查询⽤时最多的 10 条慢 SQL：
mysqldumpslow -s t -t 10-g 'select'/var/lib/mysql/localhost-slow.log
Count 代表这个 SQL 执⾏了多少次；
Time 代表执⾏的时间，括号⾥⾯是累计时间；
Lock 表⽰锁定的时间，括号是累计；
Rows 表⽰返回的记录数，括号是累计。
除了慢查询⽇志之外，还有⼀个 SHOW PROFILE ⼯具可以使⽤
4.2 SHOW PROFILE
SHOW PROFILE 是⾕歌⾼级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执⾏的时候使⽤的资源，⽐如 CPU、IO 的消耗情况。
在 SQL 中输⼊ help profile 可以得到详细的帮助信息
4.2.1 查看是否开启
select @@profiling;
set @@profiling=1;
4.2.2 查看 profile 统计
（命令最后带⼀个 s）
show profiles;
查看最后⼀个 SQL 的执⾏详细信息，从中出耗时较多的环节（没有 s）。
show profile;
6.2E-5，⼩数点左移 5 位，代表 0.000062 秒。
也可以根据 ID 查看执⾏详细信息，在后⾯带上 for query + ID。
show profile for query 1;
除了慢⽇志和 show profile，如果要分析出当前数据库中执⾏的慢的 SQL，还可以通过查看运⾏线程状态和服务器运⾏信息、存储引擎信息来分析。
4.2.3 其他系统命令
show processlist 运⾏线程
show processlist;
这是很重要的⼀个命令，⽤于显⽰⽤户运⾏线程。可以根据 id 号 kill 线程。
也可以查表，效果⼀样：（可以 group order by 了）
select*from information_schema.processlist;
show status 服务器运⾏状态
SHOW STATUS ⽤于查看 MySQL 服务器运⾏状态（重启后会清空），有 session和 global 两种作⽤域，格式：参数-值。
可以⽤ like 带通配符过滤。
SHOW GLOBAL STATUS LIKE'com_select';-- 查看 select 次数
show engine 存储引擎运⾏信息
show engine ⽤来显⽰存储引擎的当前运⾏信息，包括事务持有的表锁、⾏锁信息；事务的锁等待情况；线程信号量等待；⽂件 IO 请求；buffer pool 统计信息。
例如：
show engine innodb status;
如果需要将监控信息输出到错误信息 error log 中（15 秒钟⼀次），可以开启输出。
show variables like'innodb_status_output%';-- 开启输出：
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运⾏信息的命令，如果让你去写⼀个数据库监控系统，你会怎么做？
其实很多开源的慢查询⽇志监控⼯具，他们的原理其实也都是读取的系统的变量和状态。
现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪⾥？
MySQL 提供了⼀个执⾏计划的⼯具（在架构中我们有讲到，优化器最终⽣成的就是⼀个执⾏计划），其他数据库，例如 Oracle 也有类似的功能。
通过 EXPLAIN 我们可以模拟优化器执⾏ SQL 查询语句的过程，来知道 MySQL 是怎么处理⼀条 SQL 语句的。通过这种⽅式我们可以分析语句或者表的性能瓶颈。
MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、insert 了。
4.3 EXPLAIN 执⾏计划
我们先创建三张表。⼀张课程表，⼀张⽼师表，⼀张⽼师联系⽅式表（没有任何索引）。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。