mysql开启慢查询 影响性能_MYSQL: 慢查询和基于慢查询的性能优化

什么是慢查询慢查询：

执行很慢的查询。超过 long_query_time 参数设定的时间阈值(默认10s)，就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。

查看是否开启：

SHOW VARIABLES LIKE 'log_slow_queries';

slow_query_log OFF

设置慢查询秒数：

set global slow_query_log = 5;

查看慢查询日志位置：

SHOW VARIABLES LIKE 'slow_query_log_file';

slow_query_log_file/usr/local/mysql/data/BI-slow.log

是否记录未使用索引的SQL：

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

SHOW VARIABLES LIKE 'log_output';

日志慢查询解读：

第一行：记录时间

第二行：用户名 、用户的IP信息、线程ID号

第三行：执行花费的时间【单位：毫秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数

第四行：这SQL执行的时间戳

第五行：具体的SQL语句

表级别锁是mysql实现的， 行级锁是引擎实现的比如InnoDb。

读取表的时候一般是增加表级锁来锁定表进行读取操作。

慢查询的优化方式：

一，第一步.开启mysql慢查询

方式一：

修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询)，以及慢查询log日志记录( slow_query_log)

方法二：通过MySQL数据库开启慢查询:

二，分析慢查询日志

直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

例如：执行EXPLAIN SELECT * FROM bi2_cache.`app_appngaapp_day` WHERE platform="all" AND dau>1000000 ORDER BY data_time DESC LIMIT 1000;

返回：

idselect_type table typepossible_keyskey key_len refrowsExtra

1SIMPLE app_appngaapp_day indexNULL day_data196 NULL 1000Using where

得到如下结果： 显示结果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解释：

table 显示这一行的数据是关于哪张表的

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

rows 显示需要扫描行数

key 使用的索引

三，常见的慢查询优化

(1)索引没起作用的情况

1. 使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

2. 使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

(2)优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

(3)分解关联查询

将一个大的查询分解为多个小查询是很有必要的。

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效

(4)优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：

select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大(我们暂不考虑筛选字段上要不要添加索引的影响)，导致每次查询都要先从整个表中找到满足条件 的前M条记录，

之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。

如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，

找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询

能直接从该位置 开始，这样就不必每次 查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

方法一：虑筛选字段(title)上加索引

title字段加索引 (此效率如何未加验证)

方法二：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三：“关延迟联”

如果这个表非常大，那么这个查询可以改写成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

方法四：建立复合索引 acct_id和create_time

select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

注意sql查询慢的原因都是:引起filesort