MySQL查询优化之查询慢原因和解决技巧

在做开发的朋友特别是和mysql有接触的朋友会碰到有时mysql查询很慢，当然我指的是大数据量百万千万级了，不是几十条了，

下面我们来看看解决查询慢的办法

会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影响，例如一个几千万条记录的大表要全部扫描，或者是不停的做filesort，对数据库和服务器造成io影响等。这是镜像库上面的情况。

而到了线上库，除了出现没有索引的语句，没有用limit的语句，还多了一个情况,mysql连接数过多的问题。说到这里，先来看看以前我们的监控做法 ：

  1. 部署zabbix等开源分布式监控系统，获取每天的数据库的io，cpu，连接数
  2. 部署每周性能统计，包含数据增加量，iostat，vmstat，datasize的情况
  3. Mysql slowlog收集，列出top 10

以前以为做了这些监控已经是很完美了，现在部署了mysql节点进程监控之后，才发现很多弊端

  • 第一种做法的弊端: zabbix太庞大，而且不是在mysql内部做的监控，很多数据不是非常准备，现在一般都是用来查阅历史的数据情况
  • 第二种做法的弊端：因为是每周只跑一次，很多情况没法发现和报警
  • 第三种做法的弊端: 当节点的slowlog非常多的时候，top10就变得没意义了，而且很多时候会给出那些是一定要跑的定期任务语句给你。。参考的价值不大

那么我们怎么来解决和查询这些问题呢

对于排查问题找出性能瓶颈来说，最容易发现并解决的问题就是MYSQL的慢查询以及没有得用索引的查询。
OK，开始找出mysql中执行起来不“爽”的SQL语句吧。

方法一： 这个方法我正在用，呵呵，比较喜欢这种即时性的。

Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

mysql> show variables like 'long%'; 注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> set long_query_time=1; 注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow%';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slow_launch_time | 2 |
| slow_query_log | ON | 注：是否打开日志记录

| slow_query_log_file | /tmp/slow.log | 注： 设置到什么位置
+---------------------+---------------+
3 rows in set (0.00 sec)
mysql> set global slow_query_log='ON' 注：打开日志记录

一旦slow_query_log变量被设置为ON，mysql会立即开始记录。
/etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。
long_query_time=1
slow_query_log_file=/tmp/slow.log

方法二:mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /tmp/slow-log
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
比如
/path/mysqldumpslow -s r -t 10 /tmp/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。 最后总结一下节点监控的好处

  1. 轻量级的监控，而且是实时的，还可以根据实际的情况来定制和修改
  2. 设置了过滤程序，可以对那些一定要跑的语句进行过滤
  3. 及时发现那些没有用索引，或者是不合法的查询，虽然这很耗时去处理那些慢语句，但这样可以避免数据库挂掉，还是值得的
  4. 在数据库出现连接数过多的时候，程序会自动保存当前数据库的processlist，DBA进行原因查找的时候这可是利器
  5. 使用mysqlbinlog 来分析的时候，可以得到明确的数据库状态异常的时间段

有些人会建义我们来做mysql配置文件设置

调节tmp_table_size的时候发现另外一些参数
Qcache_queries_in_cache在缓存中已注册的查询数目
Qcache_inserts被加入到缓存中的查询数目
Qcache_hits缓存采样数数目
Qcache_lowmem_prunes因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory查询缓存的空闲内存总数
Qcache_free_blocks查询缓存中的空闲内存块的数目
Qcache_total_blocks查询缓存中的块的总数目
Qcache_free_memory可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。

到此这篇关于MySQL查询优化之查询慢原因和解决技巧的文章就介绍到这了,更多相关MySQL查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL千万级大数据SQL查询优化知识点总结

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null 可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否

  • mysql慢查询优化之从理论和实践说明limit的优点

    很多时候, 我们预期查询的结果最多是1条记录数据, 那么这个时候, 最好用上limit 1,  当查到这条数据后, mysql会立即终止继续查询, 不进行更多的无用查询, 从而提升了效率. 我们来实际测试一下, 在一个拥有10万的mysql表中, 查找lily的分数(假设系统中只有1个lily, 而我们预期也只需要这条数据).为了显示出时间的差别, 我并不对表的name字段建索引. 先看看表结构: mysql> show create table tb_province; +----------

  • Mysql慢查询优化方法及优化原则

    1.日期大小的比较,传到xml中的日期格式要符合'yyyy-MM-dd',这样才能走索引,如:'yyyy'改为'yyyy-MM-dd','yyyy-MM'改为'yyyy-MM-dd'[这样MYSQL会转换为日期类型] 2.条件语句中无论是等于.还是大于小于,WHERE左侧的条件查询字段不要使用函数或表达式或数学运算 3.WHERE条件语句尝试着调整字段的顺序提升查询速度,如把索引字段放在最前面.把查询命中率高的字段置前等 4.保证优化SQL前后其查询结果是一致的 5.在查询的时候通过将EXPLA

  • 一篇文章掌握MySQL的索引查询优化技巧

    前言 本文的内容是总结一些MySQL的常见使用技巧,以供没有DBA的团队参考.如无特殊说明,存储引擎以InnoDB为准. MySQL的特点 了解MySQL的特点有助于更好的使用MySQL,MySQL和其它常见数据库最大的不同在于存在存储引擎这个概念,存储引擎负责存储和读取数据.不同的存储引擎具有不同的特点,用户可以根据业务的特点选择适合的存储引擎,甚至是开发一个新的引擎.MySQL的逻辑架构大致如下: MySQL默认的存储引擎是InnoDB,该存储引擎的主要特点是: 支持事务处理 支持行级锁 数

  • 一步步教你MySQL查询优化分析教程

    前言 MySQL是关系性数据库中的一种,查询功能强,数据一致性高,数据安全性高,支持二级索引.但性能方面稍逊于非关系性数据库,特别是百万级别以上的数据,很容易出现查询慢的现象.这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的. 这时候MySQL 提供的 EXPLAIN 命令就尤其重要, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化. 而且就在查询语句前加上 Explain 就成: E

  • MySQL之select in 子查询优化的实现

    下面的演示基于MySQL5.7.27版本 一.关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询,优化器会选择不同的策略. 1. 对于 IN.=ANY 子查询,优化器有如下策略选择: semijoin Materialization exists 2. 对于 NOT IN.<>ALL 子查询,优化器有如下策略选择: Materialization exists 3. 对于 derived 派生表,优化器有如下策略选择: derived_merge,将派生表合并到外部查询

  • MySQL查询优化之查询慢原因和解决技巧

    在做开发的朋友特别是和mysql有接触的朋友会碰到有时mysql查询很慢,当然我指的是大数据量百万千万级了,不是几十条了, 下面我们来看看解决查询慢的办法 会经常发现开发人员查一下没用索引的语句或者没有limit n的语句,这些没语句会对数据库造成很大的影响,例如一个几千万条记录的大表要全部扫描,或者是不停的做filesort,对数据库和服务器造成io影响等.这是镜像库上面的情况. 而到了线上库,除了出现没有索引的语句,没有用limit的语句,还多了一个情况,mysql连接数过多的问题.说到这里

  • MySQL启动失败之MySQL服务无法启动的原因及解决

    目录 MySQL服务无法启动的原因及解决 1.缺乏my.ini文件 2.缺乏data文件 3.端口占用(MySQL的默认端口是3306) 总结 MySQL服务无法启动的原因及解决 此处我的电脑为win10,mysql为8.0版本. 一般情况下,通过压缩的安装包,安装MySQL可能会出现MySQL无法启动的情况,如下图: 综合网上的各种资料,加自己的实际操作,总结为以下三种原因: 1.缺乏my.ini文件 通过记事本的方式创建my.ini文件, 也可以在电脑中搜索其他的my.ini文件,将其复制后

  • Android内存泄漏的原因及解决技巧

    Android内存泄漏的原因及解决技巧

    正确的生命周期管理如何防止Android内存泄漏 OutOfMemoryException是一个常见的令人沮丧的错误,也是导致应用程序意外关闭的主要原因之一. "如果应用程序昨天运行良好,为什么现在会发生这种情况?这个问题让Android的开发者和新手都感到困惑. 导致OutOfMemory异常的潜在原因有很多种,但其中最常见的是内存泄漏-应用程序中的内存分配从未释放.本文将解释如何通过有效的生命周期管理(开发过程中一个重要但经常被忽视的部分)来最小化这种风险. 为什么安卓系统会发生内存泄漏?

  • Mysql中分页查询的两个解决方法比较

    Mysql中分页查询的两个解决方法比较

    mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下 复制代码 代码如下: SELECT COUNT(*) FROM foo WHERE b = 1; SELECT a FROM foo WHERE b = 1 LIMIT 100,10; 另外一种是使用SQL_CALC_FOUND_ROWS 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10; SELECT FOUND_

  • MySQL主从同步延迟的原因及解决办法

    MySQL主从同步延迟的原因及解决办法

    由于历史原因,MySQL复制基于逻辑的二进制日志,而非重做日志.多次被问到何时MySQL能支持基于物理的复制,其实这就看MySQL各位大佬的想法.上次和赖老师脑暴,倏地说道:MySQL会不会来个基于Paxos的redo复制? 物理复制的真正好处不在于正确性,因为基于ROW格式的日志复制也已能完全保证复制的正确性.由于物理日志的写入是在事务执行过程中就不断写入,而二进制日志的写入仅仅在事务提交时.因此物理日志的优势如下所示: 复制架构下,大事务日志提交速度快: 复制架构下,主从数据延迟小: 假设执

  • MySQL查询优化:连接查询排序浅谈

    情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team(id int primary key,tname varchar(100)); create table t_people(id int primary key,pname varchar(100),team_id int,foreign key (team_id) referen

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • 全面分析MySQL ERROR 1045出现的原因及解决

    全面分析MySQL ERROR 1045出现的原因及解决

    目录 通常从网上都能找到解决方案 1.停止服务 2.跳过验证 3.修改密码 4.重启服务 原因分析 SO 解决办法 在命令行输入mysql -u root –p,输入密码,或通过工具连接数据库时,经常出现下面的错误信息,相信该错误信息很多人在使用MySQL时都遇到过. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 通常从网上都能找到解决方案 1.停止服务 停止MySQL服务:

  • mysql数据库锁的产生原因及解决办法

    数据库和操作系统一样,是一个多用户使用的共享资源.当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况.若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性.加锁是实现数据库并 发控制的一个非常重要的技术.在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严 重影响应用的正常执行. 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Lock

  • mysql服务器查询慢原因分析与解决方法小结

    会经常发现开发人员查一下没用索引的语句或者没有limit n的语句,这些没语句会对数据库造成很大的影响,例如一个几千万条记录的大表要全部扫描,或者是不停的做filesort,对数据库和服务器造成io影响等.这是镜像库上面的情况. 而到了线上库,除了出现没有索引的语句,没有用limit的语句,还多了一个情况,mysql连接数过多的问题.说到这里,先来看看以前我们的监控做法 1. 部署zabbix等开源分布式监控系统,获取每天的数据库的io,cpu,连接数 2. 部署每周性能统计,包含数据增加量,i

随机推荐