MySQL性能管理及架构设计（三）：SQL查询优化、分库分表 - 完结篇

灰太狼 2021-01-29 10:57 519阅读 0赞

## 一、SQL查询优化（重要） ##

### 1.1 获取有性能问题SQL的三种方式 ###

1.  通过用户反馈获取存在性能问题的SQL；
2.  通过慢查日志获取存在性能问题的SQL；
3.  实时获取存在性能问题的SQL；

### 1.1.2 慢查日志分析工具 ###

相关配置参数：

slow_query_log # 启动停止记录慢查日志，慢查询日志默认是没有开启的可以在配置文件中开启(on) slow_query_log_file # 指定慢查日志的存储路径及文件，日志存储和数据从存储应该分开存储 long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001秒(1毫秒)比较合适 log_queries_not_using_indexes #是否记录未使用索引的SQL

常用工具：mysqldumpslow和pt-query-digest

pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log

### 1.1.3 实时获取有性能问题的SQL（推荐） ###

![9773ea3d0837c6ece4483a94e7f31137.png][]

SELECT id,user,host,DB,command,time,state,info FROM information\_schema.processlist WHERE TIME>=60

查询当前服务器执行超过60s的SQL，可以通过脚本周期性的来执行这条SQL，就能查出有问题的SQL。

### 1.2 SQL的解析预处理及生成执行计划（重要） ###

### 1.2.1 查询过程描述（重点！！！） ###

![47a83485bb52e98dc0163badcfa08485.png][]

### 通过上图可以清晰的了解到MySql查询执行的大致过程： ###

1.  发送SQL语句。
2.  查询缓存，如果命中缓存直接返回结果。
3.  SQL解析，预处理，再由优化器生成对应的查询执行计划。
4.  执行查询，调用存储引擎API获取数据。
5.  返回结果。

### 1.2.2 查询缓存对性能的影响（建议关闭缓存） ###

第一阶段： 相关配置参数：

query_cache_type # 设置查询缓存是否可用 query_cache_size # 设置查询缓存的内存大小 query_cache_limit # 设置查询缓存可用的存储最大值（加上sql_no_cache可以提高效率） query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单

> 缓存查找是利用对大小写敏感的哈希查找来实现的，Hash查找只能进行全值查找（sql完全一致），如果缓存命中，检查用户权限，如果权限允许，直接返回，查询不被解析，也不会生成查询计划。

### 在一个读写比较频繁的系统中，建议关闭缓存，因为缓存更新会加锁。将query\_cache\_type设置为off,query\_cache\_size设置为0。 ###

### 1.2.3 第二阶段：MySQL依照执行计划和存储引擎进行交互 ###

这个阶段包括了多个子过程：

![d7fecdcf5019f919d5134da37bb77858.png][]

![bc0a4db27f9681d067c16917d13825ee.png][]

![9a591f94e5a8e700c22a8e2030ae1f98.png][]

一条查询可以有多种查询方式，查询优化器会对每一种查询方式的（存储引擎）统计信息进行比较，找到成本最低的查询方式，这也就是索引不能太多的原因。

### 1.3 会造成MySQL生成错误的执行计划的原因 ###

1、统计信息不准确 2、成本估算与实际的执行计划成本不同

![429b18518cfe67cb4851e3f8f6eeb09c.png][]

3、给出的最优执行计划与估计的不同

![44d1655e92f4fe19d4d5ff772a0be3be.png][]

4、MySQL不考虑并发查询 5、会基于固定规则生成执行计划 6、MySQL不考虑不受其控制的成本，如存储过程，用户自定义函数

### 1.4 MySQL优化器可优化的SQL类型 ###

> 查询优化器：对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划，查询优化器会对一些查询进行改写

可以优化的sql类型

1、重新定义表的关联顺序；

![13ee723905f1ee3d8e12c99f09e8d997.png][]

2、将外连接转换为内连接；

3、使用等价变换规则；

![75f255f0830051f3afc7e3f2d770be76.png][]

4、优化count(),min(),max()；

![ffd06172147ffd982e9469e3c464963d.png][]

5、将一个表达式转换为常数； 6、子查询优化；

![d034d452f8fc31e6e3596a45ffd7003f.png][]

7、提前终止查询，如发现一个不成立条件(如where id = -1)，立即返回一个空结果；

8、对in()条件进行优化；

### 1.5 查询处理各个阶段所需要的时间 ###

### 1.5.1 使用profile(目前已经不推荐使用了) ###

set profiling = 1; #启动profile,这是一个session级的配制执行查询 show profiles; # 查询每一个查询所消耗的总时间的信息 show profiles for query N; # 查询的每个阶段所消耗的时间

### 1.5.2 performance\_schema是5.5引入的一个性能分析引擎（5.5版本时期开销比较大） ###

启动监控和历史记录表：use performance\_schema

update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%'; update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';

![51dd44c10f1ac388201eea9c3e380192.png][]

![c2e22388979b703a1543a91029e18f92.png][]

1.6 特定SQL的查询优化

### 1.6.1 大表的数据修改 ###

![a734d011226c319f5f662a147b3edf42.png][]

![0e5df9265381934df773a7c801f8cd80.png][]

### 1.6.2 大表的结构修改 ###

![ccb2ff1e7522ab6d17cb05d2a8baa832.png][]

1.  利用主从复制，先对从服务器进入修改，然后主从切换
2.  （推荐）

> 添加一个新表（修改后的结构），老表数据导入新表，老表建立触发器，修改数据同步到新表， 老表加一个排它锁（重命名）， 新表重命名， 删除老表。

![a525aaf3bf670231aa9f46aec5ed5ac2.png][]

修改语句这个样子：

alter table sbtest4 modify c varchar(150) not null default ''

利用工具修改：

![2a7cf9d93b758be638269d7a1187d18b.png][]

1.6.3 优化not in 和 <> 查询

子查询改写为关联查询：

![714bd351e0f6ac7c7c69ceff18995606.png][]

二、分库分表

### 2.1 分库分表的几种方式 ###

> 分担读负载 可通过 一主多从，升级硬件来解决。

### 2.1.1 把一个实例中的多个数据库拆分到不同实例（集群） ###

![68efb457b3f89097dd97e688f4a5b46e.png][]

拆分简单,不允许跨库。但并不能减少写负载。

### 2.1.2 把一个库中的表分离到不同的数据库中 ###

![abe8273d4dac65f0088d7b4339bf0e93.png][]

该方式只能在一定时间内减少写压力。

以上两种方式只能暂时解决读写性能问题。

### 2.1.3 数据库分片 ###

> 对一个库中的相关表进行水平拆分到不同实例的数据库中

![d9b8e71293788dc37c28568effa11155.png][]

2.1.3.1 如何选择分区键

1.  分区键要能尽可能避免跨分区查询的发生
2.  分区键要尽可能使各个分区中的数据平均

### 2.1.3.2 分片中如何生成全局唯一ID ###

![11948fe3cdfe4903ba57869511b1c747.png][]

[9773ea3d0837c6ece4483a94e7f31137.png]: /images/1611917788388.png
[47a83485bb52e98dc0163badcfa08485.png]: /images/1611917765909.png
[Link 2]: /images/1611917735469.png
[d7fecdcf5019f919d5134da37bb77858.png]: /images/1611917674178.png
[bc0a4db27f9681d067c16917d13825ee.png]: /images/1611917638941.png
[9a591f94e5a8e700c22a8e2030ae1f98.png]: /images/1611917607293.png
[429b18518cfe67cb4851e3f8f6eeb09c.png]: /images/1611917589257.png
[44d1655e92f4fe19d4d5ff772a0be3be.png]: /images/1611917574782.png
[13ee723905f1ee3d8e12c99f09e8d997.png]: /images/1611917554437.png
[75f255f0830051f3afc7e3f2d770be76.png]: /images/1611917517756.png
[ffd06172147ffd982e9469e3c464963d.png]: /images/1611917501716.png
[d034d452f8fc31e6e3596a45ffd7003f.png]: /images/1611917483065.png
[51dd44c10f1ac388201eea9c3e380192.png]: /images/1611917468070.png
[c2e22388979b703a1543a91029e18f92.png]: /images/1611917453069.png
[a734d011226c319f5f662a147b3edf42.png]: /images/1611917436109.png
[0e5df9265381934df773a7c801f8cd80.png]: /images/1611917370943.png
[ccb2ff1e7522ab6d17cb05d2a8baa832.png]: /images/1611917332897.png
[a525aaf3bf670231aa9f46aec5ed5ac2.png]: /images/1611917310804.png
[2a7cf9d93b758be638269d7a1187d18b.png]: /images/1611917269229.png
[714bd351e0f6ac7c7c69ceff18995606.png]: /images/1611917235715.png
[68efb457b3f89097dd97e688f4a5b46e.png]: /images/1611917220120.png
[abe8273d4dac65f0088d7b4339bf0e93.png]: /images/1611917195966.png
[d9b8e71293788dc37c28568effa11155.png]: /images/1611917094966.png
[11948fe3cdfe4903ba57869511b1c747.png]: /images/1611917071377.png