mysql查询反应最慢的语句_MySQL查询慢SQL语句

./mysqldumpslow -a -s t -t 20 /data/mysql/data/show_query.log

Reading mysql slow query log from /data/mysql/data/show_query.log

Count: 7 Time=22.36s (156s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]

UPDATE web_target_t SET rand=ROUND(RAND()*100000000)

/**

*  SQL语句功能是打乱排序

*   分析以上输出结果

*    SQL语句执行7次总耗时156s，平均每次执行耗时22.36s

*/

Count: 18  Time=5.62s (101s)  Lock=0.00s (0s)  Rows=1.0 (18), root[root]@[127.0.0.1]

SELECT t.dataDate FROM(

SELECT

DATE_FORMAT(created_time, '%Y-%m-%d') dataDate,COUNT(1) num

FROM web_source_clean_t

GROUP BY DATE_FORMAT(created_time, '%Y-%m-%d')

)t WHERE t.num>=5000 AND t.dataDate NOT IN(

SELECT data_date FROM LOG_CLEAN_T WHERE created_time>=SUBDATE(CURDATE(),INTERVAL 1 DAY)

) ORDER BY RAND() LIMIT 1

Count: 1 Time=7.50s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[127.0.0.1]

UPDATE web_source_clean_t SET `status`=0 WHERE created_time>='2019-05-13' AND created_time < ADDDATE('2019-05-13',INTERVAL 1 DAY)