mysql优化笔记

mysql 优化

mysql优化笔记

【mysql优化部分】
优化大致思路:
a. 表的设计合理化(符合3NF)
b. 添加适当的目录(index)
mysql的目录大概分成四类:
普普通通索引、主键索引、唯一索引、全文索引
c. 分表技术(水平划分、垂直细分)
d. 读写分离(读 select 写 insert/delete/update)
e. 存款和储蓄进程(模块化编制程序,能够增强速度)
f. 对MySQL的配备优化(如 最大并发数 max_connections等)
g. MySQL服务器硬件升级
h. 定时清除不须求的多寡 定时实行零散整理(尤其是myisam存款和储蓄引擎)

1.show status命令介绍

Show status 命令精通各个sql的执行效用

 

查阅当前有稍许个三番五次

 

Show status like  ‘connections’

翻开数据库启用了多长期

Show status like  ‘uptime’

查阅慢查询的次数

Show status like ‘slow_queries’

询问慢查询的年华

Show variables like ‘long_query_time’

修改慢查询的年月

set long_query_time

【壹 、表的布署合理化】

2.开启慢查询 找到比较慢的sql语句

mysql 数据库暗中同意慢查询的光阴是10s

暗许意况不记录慢查询 须要手动开启

应用安全形式打开mysql

Bin/mysql.exe –safe-mode  –slow-query-log

一 、表的设计原则

1NF:表的列属性不可分割。(关系型数据库都满意,不必考虑)
2NF:表中的笔录唯一。(通过设置主键来促成)
主键一般不含业务逻辑,自增进
3NF:表中不含冗余数据(表的一点字段能被演绎出来,
就不应有单独设计字段来存放他们)

突发性会用到反3NF的字段设计表。
例如:
[分类表]:id view
[详细表]:id view name cid
其中[分类表]中的浏览量要经过测算[详细表]中的浏览量得来
那么我们得以,在[分类表]中也加1个view字段,
在更新[详细表]的时候,也增加[分类表]中的view值,那么提取的
时候可防止于总计,进步查询效用。

看来,要求计算得出的字段,尽量不要在询问的时候
展开计算,将她们改在更新或插队的时候作为四个字段总结好。

3.explain分析sql语句

该命令能够在不着实进行时,就足以告知程序员功能

图片 1

Id 表示类别号

Select_type  simple 查询类型(primary  subquery dependent subquery union )

Table 查询的表

Type 类别 all 表示全表扫描 system 表示一行 const 表最多有三个匹配行

Possible_keys 代表有啥索引可用 null 表示尚未索引可用

Key 代表具体在动用什么索引

Rows 扫描后有稍许结果

Extra 表示什么艺术排序  ( useing filesort useing temporary using where )

二 、字段的陈设性基准(保短不保长)

能用tinyint 就毫无用 int
若是能用char 就不用用varchar
能用varchar 就无须用 text

同理可得,正是尽量选择11分的字段类型设计表的字段。

4.确立适合的目录

增强数据库品质 只要适度扩大索引就能增加速度,不过同时会潜移默化dml语句的成效

创设索引会采纳数据库的寄放索引的文件增大。每一回执行dml语句的时候
会维护索引,造成i/o读写次数变慢。(以空间换时间)

目录分类:主键索引(primary) 唯一索引(unique) 普通索引 全文索引

使用索引注意事项

在下边包车型地铁事态大概会使用到目录

对于开创的多列索引。只要查询条件使用最左边的列

目录一般都会被用到

create index index_name on table_name(colum1,colum2)

最左侧的colum1将会被用到

当使用 like ‘%’ 索引不会被用到 ‘aa%’会利用到目录

下列的景色不应用索引

1.借使条件中有or 即便在那之中有规范带索引 也不会动用

2.对此多列索引,不是接纳的首先片段,则不会选拔索引

3.Like 查询 %开头

4.如若列是字符串 那一定要在原则少将数据利用引号,否则不会用到目录

 

对此多量插入数据的时候

对于myisam 

Alter table table_name disable keys;

Loading data/insert 语句

Alter table table_name enable keys

对于innodb 

快要导入的多少遵照主键排序

Set unique_checks=0 关闭唯一性校验

Set autocommit=0 关闭自动提交

大规模sql语句的优化

 

1.group by

暗中认可意况下 mysql 对负有的group by col co2进行排序 

 

万贰头想分组 不排序 可利用order by null 禁止使用排序

图片 2

图片 3

 

 2.join

使用左连接或然右连接 替代普通多表连接查询

【二 、选拔适合的仓库储存引擎】

慎选适当的贮存引擎

 

一旦选用是以读写操作和插入操作为主,唯有很少的换代和删除操作,并且对工作依赖性不高的精选MyISAM

Innodb 提供了装有提交 回滚和崩溃恢复生机能力的业务安全。可是对于MyISAM 写的拍卖作用差一点 并且会占用越多的的磁盘空间

 

 

对于仓库储存引擎是myisam 的数据库  须要定时优化

 

因为myisam引擎 删除数据后 不会存放数据的文件大小不会压缩

 

要定时执行 optimize table
table_name 

 

壹 、选择原则

myisam:表对事务的渴求不高,首要以询问和丰盛修改为主,
设想动用此引擎(如评价表等)。
innodb:对事情的渴求高,保存的都是至关心重视要数据,提议利用
此引擎(如订单表、账号表)。
memory:数据变动频仍,不必要入库。同时频繁的询问和改动,
考虑选择此引擎(如用户的报到意况等)。
注:memory数据存在内部存款和储蓄器中,重启mysql会丢掉。

数据库参数的合理配置

 

最珍视的参数是内存,使用innodb引擎

 

所以innodb_additionnal_mem_pool_size=64

 

Innodb_buffer_pool_size=1g

 

对此Myisam 需求调整key_buffer_size

 

在my.ini修改端口 暗中同意存储引擎和最达累斯萨拉姆接数

 

max_connections=512

 

一经机器内部存款和储蓄器超过4g 建议使用陆12个人的操作系统

 

 

 

2、myisam与innodb的区别

① 事情安全(innodb)
② 查询和足够速度(myisam)
③ 帮助全文索引(myisam)
④ 锁机制(innodb)
⑤ 外键(innodb)

③ 、大批量数码写入

① 对此myisam,关闭索引
alter table table_name disable keys;
插入加载数据
alter table table_name enable keys;
因为创制表的时候会自动创设索引,这样负载会加大

② 对于innodb
即将插入的数目按主键举办排序
set unique_checks=0;#关门唯一索引(唯一性检查影响功能)
set autocommit=0; #关门自动提交

【③ 、建立适当的目录】

多样索引的利用(主键、唯一 、全文、普通索引)

① 、主键索引

添加索引 alter table art add primary key(id);
去除索引 alter table art drop primary key;

② 、唯一索引

表的某一列被钦命为unique 关键字是时 即为唯一索引
唯一索引允许为null 和 ”
只是足以允许多少个null值存在,不能够有三个”(空串)存在
create unique index 索引名 on 表名 (列名1,…)

叁 、全文索引

在创建表的时候创造
create table art(
id int primary key,
title varchar(20),
body text,
FULL TEXT(title,body)
) engine=myisam charset utf8;

注意:
① 全文索引只援救myisam引擎
② mysql 系统提供的全文索引,只协理英文,不扶助中文
假定要帮助粤语的话,要求下载sphinx插件
③ 全文索引有一个甘休词,在一篇小说中,创制全文索引是二个
无穷大的数,所以只会给不常见的词创立全文索引。
④ 使用全文索引必须遵照使用规则 match() against();
select * from art where match(title,body) against(‘daye’);

四 、普通索引

create index 索引名 on 表名(列名);
alter table 表名 add index 索引名(列名);

删除索引: alter table 表名 drop index 索引名

伍 、索引的查询

① 表结构查询
desc 表名;

② 查询单个索引
select index(索引名) from 表名\G

③ 查询表的具备索引
show keys from 表名\G

④ 查看索引的运用意况
show status like ‘handler_read%’
handler_read_key 高 表明索引使用率高
handler_read_rnd_next 高 表达查询成效低

陆 、索引的应用口径

① 创制了多列的目录,唯有最左侧的列被使用时,索引才会被应用
② 使用like 关键字展开询问时,初叶无法有通配符’%’、’_’等
再不不会动用索引
③ 条件中含or关键字 不会采纳索引

柒 、索引添加原则 与 优缺点

①独到之处 查询速度快 使用二叉树log2n次查询
②缺点 占用磁盘空间
对dml语句(非查询语句) 频仍操作的表 会造成速度变慢
③添加原则
添加where子句中一再利用到的字段为索引
唯一性太差的字段不吻合单独做索引

【④ 、表的撤销合并技术】

1、水平划分

即将2个表复制成多张表 结构不变
原则:
① 表结构不变
② 应基于工作的供给,找到分表的行业内部,并在查找页面
封锁用户权限。

[例] 一张qq登录表,几亿条数据 qqlogin
大家依据用户 id%3 的余数决定将用户存入哪一张表
uuid(自动生成用户id)
qqlogin0(存入id求余结果为0的用户)
qqlogin1(存入id求余结果为1的用户)
qqlogin2(存入id求余结果为2的用户)

② 、垂直细分

即将一张表中 常用 和不常用的字段分离出来,组成两张分裂表
原则:
① 将表中不常用的字段分离出来
② 将表中数据量较大,会潜移默化查询速度的表分离出来
③ 注意分离表与原表的涉及关系

【伍 、读写分离】

壹 、表的主从复制

insert into tab1
select col1 col2 … from tab2;

【六 、主从复制】

(略) 详细前面章节实行讲解

【⑦ 、定位慢查询sql】

(注意:那里慢查询不自然只指select语句,其它语句执行进度
相比慢的也叫慢查询)
SQL优化一般思路:
壹 、通过show status 命令领会各类sql执行的频率
二 、定位执行成效较低的sql语句
③ 、通过explain 分析低作用sql语句的实施情状
肆 、鲜明难题选取对应的格局

壹 、通过show status 命令领悟各个sql执行的功能

show [session|global] status like ‘%%’;
里面:session为当下的对话窗口总结。暗中同意项
global 则为有着会话窗口总结。

① mysql的周转时刻:
show status like ‘uptime’;

② 一共执行的次数:
select: show status like ‘com_select’;
update: show status like ‘com_update’;
insert: show status like ‘com_insert’;
delete: show status like ‘com_delete’;

③ 当前连接数
show status like ‘connections’;

④ 展现慢查询次数
show status like ‘slow_queries’;

二 、定位执行功效较低的sql语句

咱俩要由此以下几步定位慢查询sql语句:
① 关闭mysql服务
在windows下,打开”服务”,找到mysql,关闭服务
在Linux下,直接找到mysqld 进程,kill掉

② 命令行进入mysql的安装目录 输入
版本5.5及以后
bin\mysqld.exe –safe-mode –slow-query-log
版本5.0及以前
bin\mysqld.exe -log-slow-queries=d:/ab.log

回车

③ 再度进入mysql命令行格局 更改慢查询设定的时光范围为1s
set long_query_time = 1;

④ 此时慢查询日志已开启
记录地址在:my.ini 中的datadir所指的目录中

③ 、通过explain 分析低效用sql语句的实践境况

mysql> explain select * from emp where empno = 345680\G
*************************** 1. row
***************************
id: 1 #询问系列号
select_type: SIMPLE #查询类型
PRIMARY/
table: emp #询问的表名
type: ALL #扫描情势 ALL(全表扫描,尽量制止)
SYSTEM 表仅有一行
CONST 表匹配到的仅有一行

possible_keys: NULL #表中只怕选取到的目录
key: NULL #事实上采纳的目录
key_len: NULL
ref: NULL
rows: 4000000 #该sql语句扫描了有个别行,恐怕获取记录数
Extra: Using where #额外音信 比如排序情势 如filesort等
1 row in set (0.00 sec)

④ 、明确难题选取对应的主意

优化sql语句

① 优化group by 语句
动用group by子句后 系统会默许进行排序
万一不需求开始展览排序,则提出加上 order by null

② 使用连接 join 代替子查询

【捌 、碎片整理】

针对myisam引擎实行零散整理

//对点名的表展开零散整理

mysql> optimize table table_name;

【九、备份/还原】

① 、PHP定时形成数据库备份

① 手动备份 命令
备份数据库
# mysqldump -uroot -psa 数据库名 > /文件路径
备份表
#mysqldump -uroot -psa 数据库名.表1 数据库名.表2… > 文件路径

② 手动数据 苏醒
mysql> source 备份文件路径

贰 、定时职务

① windows 批拍卖 (职责管理器)
(1)建立 .bat 批处理文件 (如 my.bat )
找到mysql文件的安装的bin 目录 复制文件路径
在my.bat中进入如下语句
D:\phpservice\mysql\bin\mysqldump -uroot -psa demp >
d:demp.bak.sql

(2)将my.bat 文件参加定时职分
控制面板 -> 管理工科具 -> 职责布署程序 -> 操作 -> 创设任务->
操作 中程导弹入职责
触发器 中新建设置触发时间
常规 中装置任务名称
原则 中装置任务时间
安装 中设置任务的相干标准

(3) 设置好之后,就会如期触发了

② linux crontab -e 陈设职分
本条更简约
一贯将下面写入的本子路径 与 程序路径 更改一下就ok

③ 、PHP达成定时数据库备份

<?php
//设置时区
date_default_timezone_set(“PRC”);
//设置文件名
$bakfile_path = date(‘Y-m-d H:i:s’, time());
//拼装命令
$commond = “D:\phpservice\mysql\bin\mysqldump -uroot -psa demp >
d:{$bakfile_path}.bak.sql”;
//执行命令
exec($commond);

?>

四 、mysql的增量备份

mysql数据库会以二进制的花样,将mysql对数据库的操作,记录到文件
当用户愿意过来的时候,能够动用该文件实行备份复苏。

增量备份原理
① 记录dml语句(不含查询语句)
② 记录 a. 操作语句我
b. 操作时间
c. 操作position

怎样举行增量备份/与回复
(1) 配置mysql.ini 启用二进制的备份
在[mysqld] 下扩充语句
log-bin = d:\binlog\mylog

(2) 重启mysql服务 (这一步很重庆大学)
windows 下 在劳务里找到 mysql 人己一视启
linux 下 restart mysql 进程

(3) 查看mysql的日志
找到日志文件的职责
应用命令:
# mysqlbinlog 日志文件路径

日志分析
a. end_log_pos 日志文件中操作 所处的义务
b. TIMESTAMP 操作所处的年华点
c. 依据地点两点进展数据库的过来

(4) 按时间戳/地点苏醒
按时间回复
# mysqlbinlog –stop-datetime=”2015-01-14 18:23:43″
d:\binlog\mylog000001 | mysql -uroot -psa
按任务苏醒
# mysqlbinlog –start-position=”112″ d:\binlog\mylog000001 | mysql
-uroot -psa

【10、配置优化】

壹 、端口号更改

假诺要设定多少个mysql 在同等服务器上采纳,必要更改端口号
假使不使用3306,则须求在mysql_connect连接函数使用的时候带上
端口号

贰 、更改最重庆接数(mysql.ini/mysql.conf)

max_connections = 100 (最佳范围100-1000)

叁 、开启查询缓存

query_cache_size = 15M

④ 、针对不一致的斯特林发动机设置不一致的缓存大小

myisam —> key_buffer_size
innodb —> innodb_additonal_new_pool_size = 64M
innodb_buffer_pool_size = 1G

伍 、要是服务器内部存款和储蓄器超越4G,可考虑动用陆九位操作系统和 陆12人mysql服务器

http://www.bkjia.com/Mysql/967203.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/967203.htmlTechArticlemysql优化笔记 【mysql优化部分】 优化大约思路: a.
表的规划合理化(符合3NF) b. 添加适当的目录(index)
mysql的目录大概分为四类: 普通索引、…

相关文章