mysql数据库查询优化-缓存

怼烎@ 2022-04-02 07:52 296阅读 0赞

**查询执行的基础**  
一.基础  
当希望mysql能够以更高的性能运行查询时，最好的办法就是弄清楚mysql是如何优化和执行查询的，基本可以从以下3点开始了解:  
1.客户端和mysql服务器的交互过程  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70]  
(1)客户端发送一条查询给服务器  
(2)服务器先检查查询缓存(第二次查询的SQL和第一次查询的SQL完全相同)，如果命中缓存，则立即返回存储在缓存中结果，否则进入下一阶段  
(3)服务器进行SQL解析，预处理，再由优化器生成对应的执行计划  
(4)Mysql根据优化器生成执行计划，调用存储引擎的API来执行查询  
(5)将结果返回客户端

**数据库缓存**  
数据库缓存涉及到的变量，可以使用show variables，show status查看  
“query\_alloc\_block\_size”,“8192”  
“query\_cache\_limit”,“1048576”//mysql能够缓存的最大结果集  
“query\_cache\_min\_res\_unit”,“4096”//查询缓存中分配内存的最小单位  
“query\_cache\_size”,“16777216”//查询缓存使用的总内存空间  
“query\_cache\_type”,“ON”//是否打开缓存  
“query\_cache\_wlock\_invalidate”,“OFF”//如果某个数据表被其他连接锁住，是否仍然从查询缓存中返回结果  
“Qcache\_hits”,“58”  
“Com\_select”,“10”  
“Qcache\_inserts”,“310”  
“Qcache\_free\_memory”,“16218152”  
结合这些变量，查看mysql缓存分配内存来存储结果数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 1]  
因为存在内存分配的最小单元，所以会存在内存碎片，当然管理内存的工作由mysql管理，而不是操作系统  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 2]

什么情况下查询缓存能发挥作用？  
并不是什么情况下查询缓存都会提供系统性能，缓存和失效都会带来额外的消耗，所以只有当缓存带来的资源节约大于其本身资源的消耗才会给系统带来性能提升  
1.判断缓存是否有效可以计算命中率，就是使用查询缓存返回的结果占总查询的比率,  
Qcache\_hits/(Qcache\_hits+Com\_select)  
2.并不是所有查询都会有缓存  
比如:sql中带有NOW(),CURRENT\_DATE()等不确定函数的查询就不会有缓存  
3.更新UPDATE,删除DELETE,插入INSERT等操作会使缓存失效，产生额外的消耗，所以可以计算一下"命中和写入"比率，  
即Qcache\_hits:Qcache\_inserts,如果大于3:1，那么使用缓存的效果是不错的

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70]: /images/20220402/be01e919763c4bc5a7b1a032f05f0ec4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 1]: /images/20220402/e1a69e4cab7d4c6998b694a2dd3dbef1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 2]: /images/20220402/48f0ba0f5f8c4584a96febbf7f61332a.png