首页 > 代码库 > MySQL优化概述
MySQL优化概述
一 MySQL优化要点
MySQL优化是一门复杂的综合性技术，主要包括：
1 表的设计合理化（符合 3NF，必要时允许数据冗余）
2.1 SQL语句优化（以查询为主）
2.2 适当添加索引（主键索引，唯一索引，普通索引（包括联合索引），全文索引）
3 分表技术（水平分割，垂直分割）
4 读写分离（写包括update/delete/insert）
5 存储过程（模块化编程，提高执行速度）
6 MySQL配置优化
7 数据库服务器硬件升级
8 定时数据清理，碎片整理（MyISAM）
二 3NF是什么
1. 第一范式
第一范式是最基本的范式。要求数据库表中的所有字段值都是不可分解的原子值，即要求列的原子性。
2. 第二范式
第二范式是建立在第一范式的基础之上的，要求数据库表中的记录（行）必须是唯一的，即要求行的唯一性。
通常通过设计一个主键来实现（建议主键不要有具体的业务含义）。
3. 第三范式
满足第三范式必须要满足第二范式。要求非主键列必须直接依赖于主键，不能存在传递依赖，及表中不能有冗余数据。
表中某字段的信息可以通过其他列推导出来，就不应该设计此列。
反3NF：没有冗余的数据库表设计未必是最优设计，有时为了提高效率，需要降低范式标准，适当增加冗余字段。
三 SQL语句优化
1.定位慢查询（查找执行速度慢的SQL语句）
① 了解MySQL数据库运行状态
> show status like ‘uptime‘ -- 查询数据库运行时间（单位：s）
> show [session | global] status like ‘[com_select | com_insert | com_update | com_delete]‘ -- 查询SQL语句执行次数
session | global ：默认为 session ，表示当前 session 的语句执行次数；global 表示数据库运行以来所有次数。
> show status like ‘connections‘ -- 查询当前
> show status like ‘slow_queries‘ -- 显示慢查询次数
② 如何定位慢查询
a.首先构造一张大表的数据，SQL语句如下：
CREATE DATABASE temp0919;USE temp0919;#创建表DEPTCREATE 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 $$#随即添加雇员[光标] 400wcreate 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);
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优化概述