MySQL Optimization Part 2 - Slow Queries

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;