策略概述
对MySQL优化是一个综合性的技术，主要包括一下几个方面：
a. 表的设计的合理化 [ 符合3NF ]
b. 添加适当的索引（index）[ 普通索引 主键索引 唯一索引 全文索引 ]
c. 分表技术 [ 水平分割 垂直分割 ]
d. 读写分离 [ select add/update/delete ]
e. 存储过程 [ 模块化编程 可以提高速度 ]
f. 对mysql配置优化(my.ini) [ 配置最大并发数 调整缓存大小 ]
g. 对mysql服务器硬件升级
h. 定时进行碎片整理 [ 尤其是MyISAM引擎 ]
数据库分类
关系型数据库: mysql/oracle/db2/informix/sysbase/sql server
特点: 面向对象或集合
非关系型数据库: mongodb/redis
特点: 面向文档
三范式标准
合理的表设计应遵循三大范式，即3NF。首先要符合1NF，才能满足2NF，进一步3NF。
1NF:即表的列具有原子性，不可再分解，即列的信息不能再分解，只要数据库是关系型数据库，就自动满足1NF。
2NF:表中的记录是唯一的，就满足2NF，通常我们设计一个主键来实现。主键不含业务逻辑，一般是自增的，数据比较稳定。
3NF:即表中不要有冗余数据，就是说，表的信息如果能够被推导出来，就不应该单独设计一个字段来存放。
比如下面的设计就不满足3NF：
反3NF:没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低方式标准，适当保留冗余数据。
比如下面的设计就要反3NF：
具体的做法是，在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑，降低范式就是增加字段，允许冗余。
SQL语句优化
背景问题: 如何在一个大项目中迅速的定位执行速度比较慢的语句，即定位慢查询。
1.查询mysql运行状态
show status 命令
show status like 'uptime'; //查询当前mysql运行时间
show status like 'com_select'; //查询select命令执行次数(add/update/delete类推)
show [session|global] status like ...//如果不写[session|global]默认是session会话，取出当前窗口命令执行情况，如果你想看所有命令执行汇总，则应指定global
show status like 'connections'; //查询mysql连接数
show status like 'slow_queries'; //查询慢查询次数
2.定位慢查询准备工作
这里为了模仿应用场景，我们使用存储过程构建一个大表，表中有4000w条记录，并且这些记录应不同，否则测试效果和实际情况差距很大。默认情况下，mysql认为10s才是一个慢查询，因此我们需要修改mysql慢查询规定的标准时间。
show variables like 'long_query_time'; //查询当前慢查询规定时间
set long_query_time = 1; //修改慢查询规定时间，这时如果语句执行超过1s就会被统计到
创建大表的SQL代码如下：
/*部门表*/
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;
测试数据：
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 $$
//创建自定义函数：
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#char_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 $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
//创建存储过程
create procedure insert_emp(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 emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
//调用存储过程
call insert_emp(100001,4000000);
MySQL中可以保存的内容有：表，视图，函数，过程，触发器。
MySQL中除了concat()等内置函数以外还可以定义自己的自定义函数，自定义函数也可以在php脚本中调用：
<?php
$conn = mysql_connect("localhost", "root", "root");
if(!$conn)
die("失败");
mysql_select_db("temp");
//dual亚元表，单纯为了使用函数，不与表关联
$sql = "select temp.rand_string(6) my from temp.salgrade";
$res = mysql_query($sql, $conn);
if($row = mysql_fetch_assoc($res))
echo $row['my'];
?>
3.将慢查询SQL语句记录到日志中
默认情况下，MySQL不会记录慢查询，需要在启动MySQL的时候指定慢查询。
bin/mysql.exe --safe-mode --slow-query-log [mysql5.5可以在my.ini配置文件中指定]
bin/mysql.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 5.5/Data"
索引往往和物理存储相关联，数据库中数据迁移的时候应注意这一点。
运行在安全模式下的MySQL会将一些操作记录日志，具备一定的恢复能力。
通过测试，我们可以看到在日志中记录下了MySQL的慢查询语句及其相关信息。
4.针对慢SQL语句的优化方案
通过explain语句分析mysql数据库如何执行sql语句，explain可以帮助我们在不真正执行某个sql语句时，显示mysql会怎样执行该条sql语句，从而帮助我们分析语句执行情况。
//explain分析sql的回显结果说明
id : 查询序列号
select_type：查询类型
table：查询标名
type：扫描方式(all表示全表扫描)
possible_keys：可能使用到的索引
key：实际使用的索引
rows：该sql语句扫描了多少行，可能得到多少记录
Extra：sql语句的额外信息，比如排序方式
索引的使用
-
添加索引
1.1 主键索引
当把一张表的某个列设为主键的时候，则该列就是主键索引：create table aaa( id int unsigned primary key auto_increment, //这个id列就是主键索引 name varchar(32) not null default '' );如果创建表时没有指定主键索引，可以在创建表后添加：
create table bbb (id int , name varchar(32) not null default ''); alter table bbb add primary key (id);1.2 普通索引
一般来说，通常是先创建表，再创建普通索引create table ccc( id in unsigned, name varchar(32) ); create index 索引名 on 表 (列);1.3 全文索引
全文索引主要是针对文本的检索，比如文章等，只有MYISAM存储引擎支持全文索引。
全文索引的添加
create table article( id int unsigned auto_increment not null primary key, title varchar(200), body text, fulltext(title,body) )engine=myisam charset utf8;全文索引的使用
select * from article where body like '%mysql%'; [不会使用全文索引] explain select * from article where body like "%mysql%"; [通过分析我们证明了不会使用全文索引] select * from article where match(title, body) against('database'); [会使用全文索引]全文索引的说明
-
在MySQL中全文索引(fulltext)只针对MYISAM存储引擎有效。
-
MySQL自己提供的全文索引(fulltext)只针对英文有效，针对中文有sphinx(coreseek)解决方案。
-
全文索引的使用方法 match (字段名) against ('关键字');
-
全文索引的停止词：在一个文本中创建索引是一个无穷大的数，因此，对于一些常用的词和字符就不会创建，这些词成为停止词。
1.4 唯一索引
当表的某列被指定为unique约束时，这列就是一个唯一索引。
创建表时创建唯一索引
create table ddd(id int primary key auto_increment, name varchar(32) unique);创建表后创建唯一索引
create table eee(id int primary key auto_increment, name varchar(32)); create unique index 索引名 on 表名 (列名)唯一索引的字段可以为空，为空时可以重复，但是不为空时则不能重复。
主键索引的字段不可以为空，也不可以重复。
-
-
查询索引
desc 表名 [该方法的缺点是不能够显示索引名] show index(es) from 表名; show keys from 表名; -
删除索引
alter table 表名 drop index 索引名; alter table 表名 drop primary key; //删除主键索引 -
修改索引
先删除，再创建。
索引的原理（二叉树算法BTREE）
索引的注意事项
-
索引的代价
磁盘占用
对dml语句(update delete insert)的效率影响，由于维护索引文件，会使其变慢。
-
索引的使用
a. 较为频繁的作为查询条件字段应该创建索引
select * from emp where empno=1;b. 唯一性太差的字段即便频繁作为查询条件也不适合单独创建索引
select * from emp where sex='男';c. 更新非常频繁的字段不适合创建索引
select * from emp where logincount=1;d. 不会出现在where子句中的字段不该创建索引
总结起来满足如下条件的字段才应该创建索引
- 肯定在where条件经常使用。
- 该字段的内容不是唯一的几个值。
- 字段内容不是经常变化的。
-
索引注意事项
alter table dept add index my_ind(dname, loc); //dname是左边的列，loc是右边的列如果我们的表中有复合索引(索引作用在多列上)，此时我们应注意：
-
对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
explain select * from dept where dname='aaa'\G //会用到索引 explain select * from dept where loc='xxx'\G //不会用到索引 -
对于like的查询，查询如果是'%aaa'，不会使用索引，如果是'aaa%'，会使用索引
explain select * from dept where dname like '%aaa'\G //不会使用索引 explain select * from dept where dname like 'aaa%'\G //会使用索引
在like查询时关键字的最前面不能使用'%'或'_'这样的字符，如果一定要在前面有变化的值则考虑使用全文索引或sphinx
- 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段都必须建立索引。
我们建议大家尽量避免使用or关键字
-
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不能使用到索引。(数值类型会转化为字符串类型)
-
如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
-
-
索引使用查询
show status like 'Handle_read%'; handler_read_key 这个值越高表示使用索引查询到的次数越多 handler_read_md_next 这个值越高说明查询效率越低 -
查询语句技巧
-
在使用group by分组查询时，默认分组后还会排序，可能会降低速度
select * from dept group by depname\G //默认会排序 select * from dept group by depname order by null\G //避免了排序 -
有些情况下可以使用连接来代替子查询，因为使用join时MySQL不需要在内存中创建临时表
select * from dept, emp where dept.deptno=emp.deptno; //简单处理方式 select * from dept left join emp on dept.deptno=emp.deptno; //使用左外连接更好
-
网友评论