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 DATABASEtemp0919;USEtemp0919;

#创建表DEPTCREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,

dnameVARCHAR(20) NOT NULL DEFAULT"",

locVARCHAR(13) NOT NULL DEFAULT""

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#创建表EMP雇员CREATE TABLEemp(

empno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0, /*编号*/enameVARCHAR(20) NOT NULL DEFAULT "", /*名字*/jobVARCHAR(9) NOT NULL DEFAULT "",/*工作*/mgr MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,/*上级编号*/hiredate DATENOT NULL,/*入职时间*/salDECIMAL(7,2) NOT NULL,/*薪水*/commDECIMAL(7,2) NOT NULL,/*红利*/deptno MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0 /*部门编号*/)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#工资级别表CREATE TABLEsalgrade

(

grade MEDIUMINT UNSIGNEDNOT NULL DEFAULT 0,

losalDECIMAL(17,2) NOT NULL,

hisalDECIMAL(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 functionrand_string $$

#创建一个函数(随机产生字符串)

#rand_string(nINT) 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

#函数: 随机产生部门编号drop functionrand_num $$create functionrand_num( )returns int(5)begin

declare i int default 0;set i = floor(10+rand()*500);returni;end$$

#******************************************#向emp表中插入记录(海量的数据)drop procedureinsert_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;

repeatset i = i + 1;insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

until i=max_numendrepeat;commit;end$$

delimiter ;

#调用刚刚写好的函数, 4000000条记录,从100001号开始

call insert_emp(100001,4000000);

#**************************************************************# 向dept表中插入记录

delimiter $$drop procedureinsert_dept $$create procedure insert_dept(in start int(10),in max_num int(10))begin

declare i int default 0;set autocommit = 0;

repeatset i = i + 1;insert into dept values ((start+i) ,rand_string(10),rand_string(8));

until i=max_numendrepeat;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. 索引的维护

索引维护相关讨论见下方第四节。

3. 索引的使用

① 对于创建了联合索引的情况，只有当查询条件中使用了联合索引中最左边的列，索引才会被使用。

ALTER TABLE dept ADD INDEXunion_idx (dname, loc);

#如下则不会使用到此索引SELECT * FROM dept WHERE loc='Beijing';

② 对于使用 like 的查询，左模糊查询不会使用到相应列上的索引，及 like 查询条件的左侧不能为 '%aa' 或 '_aa' 的形式。

如果一定要针对左模糊查询条件使用索引，可以考虑使用全文索引。

③ 如果查询条件中带有 or ，则要求 or 中涉及到所有列都有索引，否则不会使用索引。

建议：尽量避免使用 or 关键字。

④ 如果创建索引的列的类型是字符串，则查询条件中必须使用字符串，才会使用索引。

ALTER TABLE dept ADD INDEXidx_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 TABLEaaa(

idINT UNSIGNED PRIMARY KEYAUTO_INCREMENT,

nameVARCHAR(50) NOT NULL DEFAULT '');CREATE TABLEbbb(

idINTUNSIGNED,

nameVARCHAR(50) NOT NULL DEFAULT '');ALTER TABLE bob ADD PRIMARY KEY(id);

② 普通索引：先创建表，然后再创建普通索引，普通索引可以单独一列或多列上创建，在多列上创建的叫做联合索引

CREATE TABLEddd(

idINT UNSIGNED PRIMARY KEYAUTO_INCREMENT,

nameVARCHAR(50) NOT NULL,

card_noVARCHAR(20) NOT NULL);

#普通索引CREATE INDEX idx_name ONddd(name);

#联合索引CREATE INDEX idx_name_cardno ON ddd(name, card_no);

③ 唯一索引：当表的某列被指定为 UNIQUE 约束时，则在此列上创建了一个唯一索引。

CREATE TABLEccc(

idINT UNSIGNED PRIMARY KEYAUTO_INCREMENT,

nameVARCHAR(32) UNIQUE)

#unique字段可以为NULL，并且可以有多个NULLCREATE TABLEddd(

idINT UNSIGNED PRIMARY KEYAUTO_INCREMENT,

nameVARCHAR(32) UNIQUE);CREATE UNIQUE INDEX name_uni ONccc(name);

#在普通索引创建时加上unique关键字即为唯一索引，唯一联合索引

④ 全文索引：主要针对文件文本的检索，例如文章内容。全文索引只对MyISAM引擎起效。

CREATE TABLEarticle(

idINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

titleVARCHAR(200) NOT NULL,

bodyTEXT,

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. 查询，删除，修改索引

#查询desctable_name; #缺点：无法显示索引名称

showindex(es) fromtable_name;

show keysfromtable_name;

#删除alter table table_name drop indexindex_name;

#主键索引的删除(必须是非auto_increment)alter table table_name drop primary key;

#修改:先删除，再添加alter table table_name drop indexindex_name;alter table table_name add index index_name table_name(col_name);

View Code

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 区别

事务支持

查询添加

全文索引

锁机制

外键支持

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数据库的操作 ，记录到文件中。