MySql数据库优化（1）

最新推荐文章于 2024-07-25 16:06:56 发布

伶俜Pluto

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量216

点赞数

分类专栏： mysql 文章标签： mysql 索引优化慢查询日志数据库

本文链接：https://blog.csdn.net/qq_41203284/article/details/89846090

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql数据库优化

数据库优化的目的

避免出现页面访问错误，如超时的500，慢查询造成的页面无法加载，阻塞造成的数据无法提交。
增加数据库的稳定性。
优化用户体验。

数据库优化入手方面

入手方面有以下几点：SQL及索引，数库表结构，系统配置，硬件。
成本从左到右越来越高，效果从左到右越来越差。

学习使用的数据库

sakila样例数据库DVD租赁业务流程解读

sakila数据库主要描述了DVD租赁系统的业务流程，这里列举其中的关键点解读该流程如何运行：

每个商店维护自己的租赁影片清单，当客户取走或归还DVD光盘时会有一个专门的店员对这个清单进行维护；
影片描写的内容同样在维护信息范围之列，如分类（动作、冒险、喜剧等）、演员、等级、特殊分类（如被删除的情节和预告片）。这些信息可能被打印在DVD包装的标签上；
必须在商店注册成为会员才可以租赁光盘；
客户可以在任何一家商店租赁一张或多张光盘，同时商店希望在每张光盘对应的租赁期内规划之前租赁的光盘；
顾客可以在任意时间对任何租赁的光盘付费；

数据库开启慢查询日志

这里简单通过以下语句来简单了解以下查看日志的过程。

配置

SHOW VARIABLES LIKE 'slow_query_log'; #查看慢日志开启状态
SET GLOBAL slow_query_log=ON; #开启慢日志
SHOW VARIABLES LIKE 'long_query_time'; #查看超时规则
SET GLOBAL long_query_time=0; #设置全局超时时间为1
SET SESSION long_query_time=1; #设置session找时间是为1
SET GLOBAL log_queries_not_using_indexes=ON; #开启记录索引的语句
SHOW VARIABLES LIKE 'slow%'; #查看慢日志的路径

上面就是对mysql的慢日志的相关配置，接下来我们只要执行一个查询语句，并且查询时长超过1秒，就可以在日志文件中查看到相关的记录。

慢日志包含的内容

执行sql的主机信息
sql的执行信息
sql的执行时间
sql的内容

如何查看mysql日志

mysqldumpslow

这篇博客有着详细的mysqldumpslow的使用语法：https://www.cnblogs.com/moss_tan_jun/p/8025504.html

pt-query-digest

转载博客语法地址：https://blog.csdn.net/xiaoweite1/article/details/80299754

它所查询出来的内容简单的分为三个部分：

第一个部分
第二个部分
第三个部分

这些分析工具可以大大的减少我们工作中对mysql慢日志的分析。

如何通过慢查日志发现有问题的sql？

查询次数多且每次查询占用时间长的sql
- 通常为pt-query-digest分析的前几个查询
IO大的sql
- 注意pt-query-digest分析中的Rows examine项
未命中索引的sql
- 注意pt-query-digest分析中Rows examine和Rows Send的对比

如何分析sql查询？

使用explain查询sql的执行计划（数据库语句的执行都是先分析，后执行）

贴一个例子
这些分别代表什么？
extra需要注意的返回值
- 这种情况都是我们使用了外部的表或者需要临时表用来存储数据，所以我们需要进行优化。

Count（）和Max（）的优化方法

Max（）优化方法

max通常被使用于查询最大的或者最后的操作，比如查找出最后的支付时间。

这里是执行结果，那么我们怎么进行优化呢？

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment//目标表
   partitions: NULL   //没有索引
         type: ALL    //全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086  //查询行数
     filtered: 100.00 //过滤
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

添加索引

mysql> create index idx_paydate on payment(payment_date);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引后的执行结果

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)
//这时我们可以清楚的看到执行时间大大的缩短了

Count（）优化方法

Count（列名）和Count（*）是不一样的。

列名：如果这一列有空值，那么结果是不会包含空值的。

*：表中具体的行数。

上面的语句使用count（列名的特性），如果列名是2006/2007，那么就进行计数，如果不是，那么就判定为空，不进行计数。利用好这个特性，对我们的sql逻辑的实现很有帮助。

子查询优化以及limit分页优化以及groupBy优化放在下一篇文章。

感谢浏览，希望对你有所帮助。

伶俜Pluto

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库优化（1）

目录MySql数据库优化数据库优化的目的数据库优化入手方面学习使用的数据库数据库开启慢查询日志配置慢日志包含的内容如何查看mysql日志mysqldumpslowpt-query-digest如何通过慢查日志发现有问题的sql？如何分析sql查询？Count（）和Max（）的优化方法Max（）优化方法Count（）优化方法MyS...
复制链接

扫一扫

专栏目录