一. 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; #创建表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);
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. 索引的维护
索引维护相关讨论见下方第四节。
3. 索引的使用
① 对于创建了联合索引的情况，只有当查询条件中使用了联合索引中最左边的列，索引才会被使用。
ALTER TABLE dept ADD INDEX union_idx (dname, loc); #如下则不会使用到此索引
SELECT * FROM dept WHERE loc='Beijing';
② 对于使用 like 的查询，左模糊查询不会使用到相应列上的索引，及 like 查询条件的左侧不能为 '%aa' 或 '_aa' 的形式。
如果一定要针对左模糊查询条件使用索引，可以考虑使用全文索引。
③ 如果查询条件中带有 or ，则要求 or 中涉及到所有列都有索引，否则不会使用索引。
建议：尽量避免使用 or 关键字。
④ 如果创建索引的列的类型是字符串，则查询条件中必须使用字符串，才会使用索引。
ALTER TABLE dept ADD INDEX idx_name (dname);
# 可以使用索引
SELECT * FROM dept WHERE dname = 'Tom';
# 不会使用索引
SELECT * FROM dept WHERE dname = 235;
⑤ 如果MySQL判断使用全表扫描比使用索引快，则不会使用索引。（出现的情况少）
4. explain指令的用法
explain 指令可以查看SQL语句的执行计划，效果如下：
说明：
id ：查询序列号
select_type ： 查询类型
table：查询的表名
type：扫描方式，all 代表全表扫描
possible_keys：可能使用到得索引
key：实际使用的索引
rows：SQL语句扫描的行数
Extra：额外信息，如排序方式等
5 查看索引使用情况
> show status like 'Handler_read%';
handler_read_key：越高越好，表示使用索引查询的次数
handler_read_rnd_next：越低越好，此值高说明查询低效
6 SQL 优化小技巧
① 在查询语句中使用 group by 分组时，MySQL会默认对分组结果进行排序，可能会降低速度。使用 order by null 可以关闭排序。
② 有些情况下，使用连接代替子查询。使用 join 不需要在内存中创建临时表。
四. 索引
对于SQL优化来说，最重要的方式之一是添加索引。索引分为四种：主键索引，唯一索引，普通索引，全文索引。
1. 创建索引
① 主键索引：当创建一张表时，指定某列位主键，即在该列上创建了一个主键索引。
CREATE TABLE aaa(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT ''
); CREATE TABLE bbb(
id INT UNSIGNED,
name VARCHAR(50) NOT NULL DEFAULT ''
);
ALTER TABLE bob ADD PRIMARY KEY(id);
② 普通索引：先创建表，然后再创建普通索引，普通索引可以单独一列或多列上创建，在多列上创建的叫做联合索引
CREATE TABLE ddd(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
card_no VARCHAR(20) NOT NULL
);
#普通索引
CREATE INDEX idx_name ON ddd(name);
#联合索引
CREATE INDEX idx_name_cardno ON ddd(name, card_no);
③ 唯一索引：当表的某列被指定为 UNIQUE 约束时，则在此列上创建了一个唯一索引。
CREATE TABLE ccc(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) UNIQUE
) #unique字段可以为NULL，并且可以有多个NULL
CREATE TABLE ddd(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) UNIQUE
); CREATE UNIQUE INDEX name_uni ON ccc(name); #在普通索引创建时加上unique关键字即为唯一索引，唯一联合索引
④ 全文索引：主要针对文件文本的检索，例如文章内容。全文索引只对MyISAM引擎起效。
CREATE TABLE article(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
body TEXT,
FULLTEXT (title, body) # 全文索引
) ENGINE=MyISAM CHARSET UTF8; #全文索引的使用
SELECT * from article WHERE body LIKE '%mysql%'; #错误用法，不会使用全文索引 SELECT * FROM article WHERE MATCH(title, body) AGAINST ('mysql') # 正确用法 #是否使用索引可以通过explain命令查看
注： a. 全文索引只对MyISAM引擎生效
b. MySQL本身提供的全文索引只对英文生效
c. 停止词：在文本中针对所有单词或字符创建索引是一个无穷大的数量级，因此对一些常用词和字符不会创建全文索引，这些词和字符被称作停止词
2. 查询，删除，修改索引
#查询
desc table_name; #缺点：无法显示索引名称
show index(es) from table_name;
show keys from table_name; #删除
alter table table_name drop index index_name; #主键索引的删除(必须是非auto_increment)
alter table table_name drop primary key; #修改:先删除，再添加
alter table table_name drop index index_name;
alter table table_name add index index_name table_name(col_name);
3. 索引的代价
a. 占用更多的磁盘空间（典型的以空间换时间）
b. 使得DML语句变慢
4. 创建索引的依据
a. 创建索引的列经常在WHERE条件中使用
b. 创建索引的表达到一定数据量，数据条数过少没必要创建索引
c. 创建索引的列的字段值不是有限的几个值，例如性别，是否上线等
d. 创建索引的列的字段值不是频繁变化，例如记录登录次数，在线人数等字段
五. 分表技术
当业务数据越来越多的时候，会导致某些数据表的数据量非常巨大，导致系统的性能下降。可以通过分表的方式改善性能。
1. 水平分表
将一张大表中的数据，或者即将产生大量数据的表，按照业务无关的属性随机均匀的存入多张结构相同的分表中。
假设订单信息表，可以创建order_info_00，order_info_01...order_info_99，一共100张分表，订单编号是唯一的，每次存入的时候，用订单编号取hashcode后，再对100（分表数量）取模，得到的结果即为将要存储数据的分表的序号。
Java中可如下操作：
#取哈希值时有可能结果为Integer.MIN_VALUE,导致取序号出错
int index = Math.abs(orderNo.hashcode() % 100);
String idxStr = String.format("%2d", index);
String tableName = "order_info_" + idxStr;
2. 垂直分表
在某些表中，可能会有占用空间比较大得字段，类型如text，varchar(3000)，用于存储文章内容，回帖内容等，这些字段会严重影响系统的检索速度，此类字段查询的次数也相对较少，这个时候可以将其提取出来，单独建表存储，与原来的表共用主键id。这样在保证了数据的关联一致的同时，加快了原来表的检索速度。
3. 数据库中文本视频类数据的存储
通常不直接将文本或视频内容存储在数据库中，而只是存储文本或视频所在的路径，查询时按照路径去检索文件的真正内容。
六. 读写分离
当系统的并发访问量特别大的时候，单一的MySQL服务器的负载特别大，导致数据库性能下降，升值造成服务器崩溃。这个时候可以考虑搭建MySQL集群，使用读写分离技术来改善这种状况。集群中包含一台master服务器，多台slave服务器，master服务器负责执行DML(insert/delete/update)语句，slave服务器负载执行select语句，主服务器通过日志文件将操作同步到从服务器上。
。。。。
七. MySQL配置优化
。。。。
八. 定时维护
1.存储引擎的选择
MyISAM引擎：对事物要求不高，数据以添加和查询为主
InnoDB引擎：严格要求失误，保存的数据都是重要数据
| 事务支持 | 查询添加 | 全文索引 | 锁机制 | 外键支持 | |
| MyISAM | 否 | 快 | 支持 | 表级 | 否 |
| InnoDB | 是 | 慢 | 不支持 | 行级 | 是 |
Memory 引擎：数据频繁变化，不需要入库，并且频繁查询修改，速度极快（MySQL服务重启后数据消失），可以用作缓存。
2. MyISAM 引擎定期进行碎片整理
使用 MyISAM 引擎的表中得数据被删除后，表数据存储所在的文件大小并不会改变，即表文件占用的空间不会释放，长此以往会严重拖慢表的查询速度。所以需要定期对使用 MyISAM 引擎的表进行碎片整理。
> OPTIMIZE TABLE table_name;
3. 定时备份
① 手动备份数据库
#备份数据库，命令行下操作
$ ./mysqldump -u root -pxxxx temp dept > ~/backend/mysql-temp-dept.bak
#xxxx为 MySQL 登录密码，temp 为需要备份的数据库，dept 为需要备份的表名
#表名可以有多个，[tb1, tb2, tb3,....] #使用备份内容恢复数据，MySQL 客户端下操作
> source ~/backend/mysql-temp-dept.bak
②使用定时器完成自动备份
a. Windows下
1> 将备份指令写入一个bat脚本（mybaktask.bat）：
#mysqldump.exe路径包含空格，需要用""包围
"C:\programe files\mysql5.6.26\bin\mysqldump.exe" -u root -pxxxx temp dept >d:\temp.dept.bak
2> 把 mybaktask.bat 做成一个计划任务
"计算机"-（右键）"属性"-"管理"-"系统工具"-"任务计划程序"-"创建任务"
注：windows下希望每次备份的文件名称加上时间戳，不覆盖上一次备份的文件，可以写一个PHP脚本去执行备份命令，然后再定义一个定时任务去调用PHP脚本。
b. Linux / OSX 下
1> 将备份指令写入 shell 脚本（mysql-bak-task.sh）
#!/bin/bash
/usr/local/mysql/bin/mysqldump -u root -pxxxx temp dept > /home/byron/backend/mysql-temp-`date +%s`.bak
2> 使用 contrab 创建定时任务
$ sudo crontab -e #编辑定时任务 # 添加如下内容
* 2 * * * /Users/byron/backEnd/mysql-bak-task.sh # 每天凌晨2点执行一次备份脚本
九. 增量备份
MySQL会以二进制的形式，自动将用户对MySQL数据库的操作 ，记录到文件中。