MySQL优化概述

DATABASE temp0919; USE temp0919; #创建表DEPT 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 ; #创建表EMP雇员 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; INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); #定义一个新的命令结束符合 delimiter $$ #删除自定的函数 drop function rand_string $$ #创建一个函数(随机产生字符串) #rand_string(n INT) rand_string 是函数名 (n INT) //该函数接收一个整数 create function rand_string(n INT) returns varchar(255) #该函数会返回一个字符串 begin #chars_str定义一个变量 chars_str,类型是 varchar(100),默认值‘abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ‘; 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 $$ #函数: 随机产生部门编号 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 $$ #****************************************** #向emp表中插入记录(海量的数据) drop procedure insert_emp $$ #随即添加雇员[光标] 400w create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 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 $$ delimiter ; #调用刚刚写好的函数, 4000000条记录,从100001号开始 call insert_emp(100001,4000000); #************************************************************** # 向dept表中插入记录 delimiter $$ drop procedure insert_dept $$ create procedure insert_dept(in start int(10),in max_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into dept values ((start+i) ,rand_string(10),rand_string(8)); until i = max_num end repeat; commit; end $$ delimiter ; call insert_dept(100,10); View Code

　　

　　b. MySQL默认执行时间大于10s的语句为慢查询，此处修改为1s

　　> show variables like ‘long_query_time‘　　-- 显示慢查询时间标准

　　> set long_query_time = 1　　-- 修改慢查询时间，当前session有效

 

　　c. 记录慢查询到日志文件中

　　默认情况下MySQL不会记录慢查询日志，需要在启动MySQL服务时，指定相应的参数，才可以记录慢查询日志。

　　(1) 修改配置后启动(linux : my.conf,  windows : my.ini)　

[mysqld]
#慢查询日志文件位置, 此目录文件一定要有写权限
log-slow-queries="/usr/local/mysql5.6/data/black-slow.log"
#慢查询时间标准
long_query_time = 2
#没有使用到索引的查询也将被记录在日志中
log-queries-not-using-indexes

　　　　修改配置文件后重新启动MySQL服务：

　　　　$ sudo $MYSQL_HOME/support-files/mysql.server start　　--Linux/OSX

　　　　> mysqld.exe 　　--Windows

　　(2) MySQL客户端修改参数

　　　　使用MySQL客户端登陆MySQL数据库

 　　> show variables like ‘%slow_query%‘　　　-- 查看慢查询相关参数

　　 > show variables like ‘long_query_time‘    -- 查询慢查询时间标准

　　 > set global slow_query_log = ON;　　-- 开启慢查询日志

　　 > set global long_query_time = 1;　　-- 调整慢查询时间标准

 　　此时不需要重启MySQL服务，即可开启慢查询日志。

 

　　2. 索引

　　对于SQL优化来说，最重要的方式之一是添加索引。

　　

MySQL优化概述

标签：