mysql类

曾经终败给现在 2023-07-11 15:29 1阅读 0赞

通过**SHOW STATUS LIKE 'COM\_%'  查询当前数据库的 增删改查 的操作次数，大致了解各种操作比例 ，有利于优化sql。**

**还可以查看 增删改查 操作影响的行数。**

**可以查看 事务提交 和 回滚的次数 频率，回滚频率高 可能是否业务逻辑存在缺陷，需要优化**

SHOW STATUS LIKE 'connections' ;  -- **试图链接mysql次数**  
SHOW STATUS LIKE 'uptime' ;  -- **服务器工作时间**  
SHOW STATUS LIKE 'Slow\_queries'; -- **慢查询次数**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70][]

![20200306173611109.png][]![20200306173619336.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 1][]

查看数据库 是否支持 show profile ；和查询状态，以及设置开启

![20200312162736381.png][]

SELECT @@have\_profiling;  
SELECT @@profiling;  
set profiling = 1;  
show  PROFILES

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 2][]

**然后通过query id 进行详细查询；看到每个阶段的耗时；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200306173553520.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5,size_16,color_FFFFFF,t_70
[20200306173611109.png]: https://img-blog.csdnimg.cn/20200306173611109.png
[20200306173619336.png]: https://img-blog.csdnimg.cn/20200306173619336.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200306165102824.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5,size_16,color_FFFFFF,t_70
[20200312162736381.png]: https://img-blog.csdnimg.cn/20200312162736381.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200312162959640.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200312170625314.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5,size_16,color_FFFFFF,t_70