MySQL Optimization Part 2 - Slow Queries

原创 2016年08月30日 22:57:45

MySQL Optimization Part 2 - Slow Queries

目录


常用 MySQL 状态查询

问题:如何从一个大项目中,迅速的定位执行速度慢的语句 (定位慢查询)?

  • 首先我们要了解 MySQL 数据库的一些运行状态如何查询

    show status

    • 当前 MySQL 运行的时间

      show status like ‘uptime’;

    • 一共执行了多少次 select/update/insert/delete

      show status like ‘com_select/update/insert/delete’;

    • 当前 MySQL 的连接数

      show status like ‘connections’;

    • 显示慢查询次数

      show status like ‘slow_queries’;

      默认情况下,MySQL 认为 10 秒才是一个慢查询。
      修改 MySQL 的慢查询为 1 秒:

      show variables like ‘long_query_time’; // 当前慢查询时间
      set long_query_time=1; // 修改慢查询事件

    show [session|global] status like …默认情况下是 session 级别的查询(当前会话),如果想看到所有的状态(从 MySQL 启动时到当前,则应该使用 global )


构建大表

创建表

# 创建部门表
CREATE TABLE dept( 
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

# 创建员工表
CREATE TABLE emp(
empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

# 创建薪水表
CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建存储过程

先创建一个能够返回随机字符串的函数

delimiter $$

drop function rand_string$$
create function rand_string(n INT) 
returns varchar(255) #该函数会返回一个字符串
begin 
    declare chars_str varchar(100) default
    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do 
        set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
        set i = i + 1;
        end while;
    return return_str;  

end $$

再创建一个能够返回随机数字的函数

delimiter $$
drop function rand_num $$

create function rand_num()
returns int(5)

begin
    declare i int default 0;
    set i = floor(10+rand()*500);
    return i;

end $$

最后创建一个存储过程

delimiter $$
drop procedure insert_emp $$

# start 表示雇员的编号是从哪里开始
# max_num 表示一共增加多少雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0,不用每次插入一次就提交一次,节省时间
set autocommit = 0;  
repeat
    set i = i + 1;
    insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
    end repeat;
commit;

end $$

调用存储过程

# 添加 4000000 条数据
call insert_emp(100001,4000000)

数据库可以有各种数据对象:表、存储过程、视图、函数、触发器…

定位慢查询

  1. 任何一条语句执行时间超过1秒钟(不仅仅是查询),就会统计到 slow_quries 这个计数器中.
  2. 如何把慢查询的 sql 记录到我们的一个日志中

    
    # 在默认情况下,我们的 MySQL 不会记录慢查询,需要在启动 MySQL 时候,指定记录慢查询才可以
    
    bin\mysqld.exe --safe-mode  --slow-query-log [mysql5.5 可以在 my.ini 指定]
    bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本 mysql5.0 可以在 my.ini 指定]

    先关闭mysql,再启动。 如果启用了慢查询日志,默认把这个文件放在my.ini 文件中记录的位置

    
    # Path to the database root
    
    datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
  3. 测试:可以看到在日志中就记录下我们的 MySQL 慢 SQL 语句。
    如下记录是把慢查询门限设为 0 时的日志输出,看到日志里记录了 慢查询 的 SQL 语句。非常便于定位、
mysqld.exe, Version: 5.5.8-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 160831  2:22:20
# User@Host: root[root] @ localhost [::1]
# Query_time: 0.177010  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 818926
use test;
SET timestamp=1472581340;
select * from emp where empno=555559;
# Time: 160831  2:22:44
# User@Host: root[root] @ localhost [::1]
# Query_time: 0.002000  Lock_time: 0.001000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1472581364;
show variables like 'long_%';
# Time: 160831  2:23:02
# User@Host: root[root] @ localhost [::1]
# Query_time: 0.170010  Lock_time: 0.001000 Rows_sent: 1  Rows_examined: 818926
SET timestamp=1472581382;
select * from emp where empno=888888;
版权声明:本文为博主原创文章,未经博主允许不得转载。

Non-technical Guide to Isolating Slow MySQL Queries

MySQL is ubiquitous. As such there are many non-technical users who rely on MySQL but do not want to...

MySQL开启慢查询日志log-slow-queries

一个普通WEB站点的页面常常需要查询N条SQL语句后才能得出页面结果,当网站访问速度慢而前端做了大量优化工作以后,数据库瓶颈的查找也是WEB优化的一个重要部分。 MySQL中提供了一个慢查询的日志记录...

MySQL Optimization Part 3 - Indexes Operations

MySQL Optimization Part 3 - Indexes Operations目录 MySQL Optimization Part 3 - Indexes Operations 目录 索...

MySQL Optimization Part 4 - Indexes Usage

MySQL Optimization Part 4 - Indexes Usage目录 MySQL Optimization Part 4 - Indexes Usage 目录 索引的注意事项 索引的...

Lower Lock % and Number of Slow Queries

Gauges tracks several websites. Some get a lot of traffic and others don’t. The sites that get a lot...
  • macyang
  • macyang
  • 2012年02月01日 23:08
  • 532

MySQL Optimization Part 5 - Optimization Tips

MySQL Optimization Part 5 - Optimization Tips目录 MySQL Optimization Part 5 - Optimization Tips 目录 常...

optimization for c++ games---part2

  • 2008年02月20日 16:16
  • 3.46MB
  • 下载

用mysqldumpslow分析mysql的slow query log

mysql有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改,需增加如...

处理 mysql slow sql

在slow_query_log 值为ON的情况下(默认为OFF 默认为10s,同时精确到微秒)默认(log_output值为FIFL时)就会把这种慢查询记录到:slow_query_log_file值...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL Optimization Part 2 - Slow Queries
举报原因:
原因补充:

(最多只允许输入30个字)