[Mysql相关优化] 笔记(篇一)

- 日理万妓 2023-10-05 20:33 37阅读 0赞

### Mysql优化目标: ###

**1.减少 IO 次数**

IO永远是数据库最容易瓶颈的地方，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑;

**2.降低 CPU 计算**

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。所以降低CPU也是优化性能目标了  
　　  
　　我理解sql优化就是为:**减少数据库底层繁琐的操作，又繁到简的sql最ok**  
　　  
**2.count(column) 和 count(\*) 是一样的**

这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和 count(\*) 是一个完全不一样的操作，所代表的意义也完全不一样。

**count(column)** 是表示结果集中有多少个column字段不为空的记录  
　　**count(\*)** 是表示整个结果集有多少条记录

**3.select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量**  
　　这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。  
　　实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

**所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。**

当然，也有**例外情况**，那就是我们的这个**查询在索引中**就可以完成，也就是说当只取 a,b两个字段的时候，不需要回表，而**c这个字段不在使用的索引中**，需要回表取得其数据。在这样的情况下，二者的IO量会有较大**差异。**

例如你要查询的返回 属性值有abc ，正常查询 abc 都需要有一个回表操作，但是你建立了 ab这二个字段的索引以后 ，就只用c 需要回表操作了， 我打算去仓库拿三个凳子去教室，但是提前这个索引就把二个凳子送到教室门口了， 还有一个凳子在仓库 …

### **图一:**   没有索引的sql,根据name 查询 查询列 查询了五次   ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]   图二:   建立索引   ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 1]   图三:   建立索引以后就指定根据条件去查询对应的列了,查询列 查询 一次   ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 2]   **上图均使用 使用explain来查询sql的执行计划和索引情况** ###

一些其他优化sql的方法:  
exist和in  
select …from table where in/ex ist (子查询)  
select …from table where 字段 in (子查询)  
如果主查询数据集大,则使用IN  
如果子查询数据集大,则用exist  
则子查询表大的用exists，子查询表小的用in：

select 返回属性值 from table where exists (select 子查询)  
如果使用exists 来子查询的话 查询数据集 如果数据集没有值 返回就是null  
empet set sql语句返回就是null的意思

order by 优化:

using filesort 二种算法:双路排序 和单路排序  
4.1之前版本是 双路 4.1 以后就是单路排序

双路排序:扫描二次磁盘  
第一次是从磁盘读取排序字段, 对排序字段进行排序(在buffer中进行排序)  
buffer是:内存的一块区域 也可以理解为一个空间, 因为太消耗IO性能 所以转为单路 单路排序减少了 IO访问次数

单路排序:只读取一次(全部字段),在buffer中排序, 单路有一定隐患 不一定是单路请求一次IO 可能是多次IO 、

如果数据量太大,则无法读取所有字段，不能一次性读完,只能"分片读取,多次读取"

注意:单路排序 比 双路排序会占用更多的buffer

数据量大可以调整buffer大小:set\_max\_length\_for\_sort\_data=1024

如果set\_max\_length\_for\_sort\_data=1024的值 太低 就会从单路变为双路  
这个变化是根据排序的列总大小超过了max\_lengh…定义的字节数

提高order By查询的策略,  
a:选择使用单路或者双路:调整buffer的容量大小 来设置

b:避免select *… 因为这个* 底层的数据库 还要去计算这个\*是什么 直接规定他返回什么查询的值

c:复合索引 不要跨列使用，跨列会出现using filesort 避免出现

d:保证全部的排序字段,排序的一致性,(都是升序 或是降序 )

SQL排序-慢查询日志 用来查看那一条sql 比较慢:  
mysql 提供的一种日志记录，用于记录mysql响应时间超过阈值的sql语句(long\_query\_time) 建议:开发调优是 打开,而最终部署时关闭

检查是否开启了 慢查询日志:show variables like ‘%slow\_query\_time%’;

OFF 是关闭 ON是开启  
开启指令  
a:临时开启: set global slow\_query\_log=1L; 退出sql 它自动关闭 重启就会自动关闭

b:永久性开启: /etc/my.cnf 中追加配置：  
在MysqlId 里面末尾追加：  
slow\_query\_log=1  
slow\_query\_log\_file=(日志存放路径)

慢查询阈值:  
show variables like ‘%slow\_query\_time%’; 默认阈值是10

可以设置默认阈值:  
临时设置:set global long-query\_time=5;(要重新登录才生效)  
永久性设置阈值:  
/etc/my.cnf 中追加配置：  
在MysqlId 里面末尾追加：  
long\_query\_time=5

查询超过阈值的SQL : show global status like ‘%slow\_queries%’ ;

慢查询的sql 被记录在了日志里 ，查看具体慢的sql  
cat 查看上面你开启sql 记录日志的那个路径 在linux 页面查看 也可以在数据库去查看sql 执行时间

EXPLAIN SELECT SQL\_NO\_CACHE \* FROM `product_form`  
总结:可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，我们都可以指定不进行查询缓存，使用 SQL\_NO\_CACHE 选项。  
对于那些变化不频繁的表，查询操作很固定，我们可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用SQL\_CACHE 选项。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210227110016894.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210227105909684.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210227110134189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70