记一次因线上mysql优化器误判引起慢查询事件

前言:

收到疯狂的慢查询及请求超时报警,通过metrics分析出来自mysql请求的异常,cli —> show proceslist 看到很多慢查询。 先前该sql是没有的,后面因为数据量的增长才出现了这问题。 虽然feeds表大到一个亿,但因为feeds流信息有近期热的特征,所以不是因为 innodb_buffer_pool_size 低效引起的io频繁。 后来经过进一步explain执行计划分析得出了原因,mysql查询优化器选择了他认为高效的索引。

mysql查询优化器大多数情况是靠谱的!  但是你的sql语言含有多个索引时就要注意了,往往最后的结果令人有些彷徨了。因为mysql同一个sql只能使用一个索引,那么选择哪个呢? 在数据量小时候,mysql优化器会把主键索引后置,优先使用 index和unique 。 当你达到一个数据量级后,又因为你的查询操作有 in ,那么mysql查询优化器很可能会选用主键的 !

记住一句话,mysql查询优化是基于检索成本考虑,而不是基于时间成本考虑。 优化器是根据现有的数据状态来推算代价,而不是真的去执行一遍sql.

所以,mysql优化器并不是每次都可以达到优化的效果的。 它并不能准确预估代价,如果要准确得到走各个索引的代价就要去真的执行一遍才能知道,所以代价分析只是做了一个预估,既然是预估那么就有误判。

我们这里说的表是feed信息流表,我们知道feeds信息流表访问不仅频繁,而且数据量也很大。 但是这个表的数据结构很简单,索引也简单.   一共就两个索引,一个是主键索引, 一个是unique唯一键索引。

如下,该表的量级已经到亿级别了,因为有足够多的cache前顶,又因为这样那样的原因,所以没来的及做分库分表。

问题是这样的, 当数据量级不到一个亿的时候,mysql优化器选择使用 index索引, 当数据量级超过一个亿后,mysql查询优化器选择使用 主键索引了。  这样带来的问题就是 查询速度太慢。

这是正常情况下:

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)     AND cid IN (1001,1005,1054,1092,1093,1095)  AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
  partitions: NULL
     type: range
possible_keys: PRIMARY,feed_user_target
     key: feed_user_target
   key_len: 6
     ref: NULL
     rows: 18
   filtered: 50.00
    Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

同样的sql语句,在数据量有较大变化后,mysql查询优化器对索引的选择也有了变化。

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)    AND cid IN (1001,1005,1054,1092,1093,1095)    AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
     type: range
possible_keys: PRIMARY,feed_user_target
     key: PRIMARY
   key_len: 4
     ref: NULL
     rows: 11873197
    Extra: Using where
1 row in set (0.00 sec)

那么解决方法是使用 force index,强制查询优化器使用我们给出的index 。 我这里是python开发环境,常见的python orm都有force index,ignore index,user index 参数的。

explain  SELECT * FROM `feed` force index (feed_user_target) WHERE user_id IN (116537309,116709093,116709377) ...

那么我们应该怎么预防这种 因为数据的增进,mysql优化器选择了一个低效索引的问题呢?

针对这个问题请教了几个厂的dba,得到的答案和我们的方法是一样的。 都是只能通过后期的慢查询来发现问题,然后在sql语句中指定force index来解决索引问题。 另外,在系统上线初期就会做这类问题的规避,但往往业务开发人员初期都会配合dba们的审查工作,但后期为了省事,或者说自以为是认为没有问题,所以造成了 mysql查询事故。

我自己对于mysql优化器选择索引规则一知半解的,后面准备花时间好好研究下规则

(0)

相关推荐

  • 探究MySQL优化器对索引和JOIN顺序的选择

    本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序.表结构和数据准备参考本文最后部分"测试环境".这里主要介绍MySQL优化器的主要执行流程,而不是介绍一个优化器的各个组件(这是另一个话题). 我们知道,MySQL优化器只有两个自由度:顺序选择:单表访问方式:这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择. explain select * from employee as A,department as B where A.LastName = '

  • mysql 开启慢查询 如何打开mysql的慢查询日志记录

    mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢? 其实打开mysql的慢查询日志很简单,只需要在mysql的配置文件里(windows系统是my.ini,linux系统是my.cnf)的[mysqld]下面加上如下代码: 复制代码 代码如下: log-slow-queries=/var/lib/mysql/slowquery.log long_query_time=2 注: log-slow-queries

  • MySQL慢查询优化之慢查询日志分析的实例教程

    数据库响应慢问题最多的就是查询了.现在大部分数据库都提供了性能分析的帮助手段.例如Oracle中会帮你直接找出慢的语句,并且提供优化方案.在MySQL中就要自己开启慢日志记录加以分析(记录可以保存在表或者文件中,默认是保存在文件中,我们系统使用的就是默认方式). 先看看MySQL慢查询日志里面的记录长什么样的: Time Id Command Argument # Time: 141010 9:33:57 # User@Host: root[root] @ localhost [] Id: 1

  • MySQL前缀索引导致的慢查询分析总结

    前端时间跟一个DB相关的项目,alanc反馈有一个查询,使用索引比不使用索引慢很多倍,有点毁三观.所以跟进了一下,用explain,看了看2个查询不同的结果. 不用索引的查询的时候结果如下,实际查询中速度比较块. 复制代码 代码如下: mysql> explain select * from rosterusers limit 10000,3 ; +----+-------------+-------------+------+---------------+------+---------+-

  • 深入mysql慢查询设置的详解

    在web开发中,我们经常会写出一些SQL语句,一条糟糕的SQL语句可能让你的整个程序都非常慢,超过10秒一般用户就会选择关闭网页,如何优化SQL语句将那些运行时间 比较长的SQL语句找出呢?MySQL给我们提供了一个很好的功能,那就是慢查询!所谓的慢查询就是通过设置来记录超过一定时间的SQL语句!那么如何应用慢查询呢? 1.开启MySQL的慢查询日志功能默认情况下,MySQL是不会记录超过一定执行时间的SQL语句的.要开启这个功能,我们需要修改MySQL的配置文件,windows下修改my.in

  • MYSQL5.7.9开启慢查询日志的技巧

    用MYSQL 5.7.9 作为ZABBIX 2.4.7 的监控数据库. 前段时间开启了慢查询日志, 后来发现慢查询日志膨胀到了700M 查看最后100条 大部分都是 0.1 秒的 后来想改, 以前是动态设置的 set global slow_query_log=1; 方式的 . 然后想直接用配置文件/etc/my.cnf 配慢查询 # Remove leading # and set to the amount of RAM for the most important data # cache

  • MySQL慢查询查找和调优测试

    编辑 my.cnf或者my.ini文件,去除下面这几行代码的注释: 复制代码 代码如下: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes 这将使得慢查询和没有使用索引的查询被记录下来. 这样做之后,对mysql-slow.log文件执行tail -f命令,将能看到其中记录的慢查询和未使用索引的查询. 随便提取一个慢查询,执行explain: 复制代

  • 对MySQL慢查询日志进行分析的基本教程

    0.首先查看当前是否开启慢查询: (1)快速办法,运行sql语句 show VARIABLES like "%slow%" (2)直接去my.conf中查看. my.conf中的配置(放在[mysqld]下的下方加入) [mysqld] log-slow-queries = /usr/local/mysql/var/slowquery.log long_query_time = 1 #单位是秒 log-queries-not-using-indexes 使用sql语句来修改:不能按照m

  • mysqlsla慢查询分析工具使用笔记

    且该工具自带相似SQL语句去重的功能,能按照指定方式进行排序(比如分析慢查询日志的时候,让其按照SQL语句执行时间逆排序,就能很方便的定位出问题所在) + ------------- 安装mysqlsla慢查询日志分析工具 ------------- + 复制代码 代码如下: yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMakeryum -y install perl-DBI perl-DBD-MySQLyum -y insta

  • mysql正确安全清空在线慢查询日志slow log的流程分享

    1, see the slow log status; mysql> show variables like '%slow%';+---------------------+------------------------------------------+| Variable_name       | Value                                    |+---------------------+-------------------------------

随机推荐