关闭

MySQL Optimization Part 2 - Slow Queries

标签: 慢查询数据库
106人阅读 评论(0) 收藏 举报
分类:

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;
0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:16971次
    • 积分:923
    • 等级:
    • 排名:千里之外
    • 原创:79篇
    • 转载:0篇
    • 译文:1篇
    • 评论:0条