Mysql数据库的优化技术
对mysql优化时一个综合性的技术,主要包括
a: 表的设计合理化(符合3NF)
b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
c: 分表技术(水平分割、垂直分割)
d: 读写[写: update/delete/add]分离
e: 存储过程 [模块化编程,可以提高速度]
f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ] Linux /etc/my.cnf
g: mysql服务器硬件升级
h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
重点:定位慢查询,mysql索引优化,mysql备份
3NF
1NF 关系型数据库自动满足
2NF 表中记录唯一(通常设置一个主键实现)
3NF 没有数据冗余,若表中字段有一对多的关系,考虑分表
反3NF : 但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是: 在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。
//常用命令
show status
常用的:
show status like ‘uptime’ ; //mysql服务工作时间
show stauts like ‘com_select’ ;//select 查询次数
show [session|global] status like (session表示只针对当前窗口,global从mysql 服务启动到现在)
show status like ‘connections’; // 试图连接MySQL服务器的次数
//定位慢查询
set global log_slow_queries=on;//mysql中启用慢查询日志
show variables like "%slow%";//查看是否开启,文件日志保存路径
show variables like ‘long_query_time’; //查看慢查询时间设置值
set long_query_time=0.1; //重新设置为0.1s
show status like ‘slow_queries’; //显示慢查询次数
测试时,可以看到在日志中查看mysql慢sql语句.
优化问题.
explain 查看mysql指令实际的执行情况
添加索引
四种索引(主键索引/唯一索引/全文索引/普通索引)
数据库表3个文件,(数据表结构,数据,索引)
1. 添加
1.1主键索引添加
当创建表,将某个字段设为主键的时候,该字段,就是主键索引。
alter table 表名 add primary key (列名);//可后添加
1.2普通索引
一般表创建后,再创建
create index xx on表 (列1,列名2);
1.3 全文索引
主要是针对对varchar,text的检索,全文索引针对MyISAM有用.(中文使用sphinx)
停止词如一些常用词和字符,不会创建
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
ALTER TABLE articles ADD FULLTEXT INDEX fulltext_article(title,body);//可以后创建
select * from articles where match(title,body) against(‘关键字’); //用法
1.4唯一索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);
unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复.
主键字段,不能为NULL,也不能重复.
②在创建表后,再去创建唯一索引
create unique index 索引名 on 表名 (列表..);
2. 查询索引
show index(es) from 表名\G
show keys from 表名\G
3. 删除
alter table 表名 drop index 索引名;
4. 修改 //先删除,再重新创建.
索引创建注意
满足以下条件的字段,才应该创建索引.
Where条件下经常使用,该字段内容多样,不频繁变化
索引使用注意
//使用索引
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列
explain select * from dept where loc='aaa'\G
//复合索引,查询条件时,使用最左边的列。
//like查询,请使用 ‘aaa%’,不要使用‘%aaa’或 ‘_aaa’(考虑sphinx)
//or 查询,所有条件的字段带索引,才使用索引,(避免使用or)
select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
//字段类型为字符串,查询时一定要用引号引起来,(添加时,字符串必须’’)
//不经常出现,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
//如何查看索引使用的情况:
show status like ‘Handler_read%’;
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
//sql语句的小技巧
Group by分组查询后,还会进行内部排序,order by null可以防止排序.
//可使用左外连接代替子查询,因为使用join,MySQL不需要在内存中创建临时表。
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno; [左外连接,更ok!]
//选择mysql的存储引擎
在开发中,我们经常使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,考虑使用,比如 bbs 中的 发帖表,回复表.
INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
//如果数据库的存储引擎是myisam, 删除数据后,数据文件大小没有变化,记住要定时进行碎片整理
// mysql命令行,对myisam进行整理,linux下 /etc/my.cnf 找到datadir路径查看清理效果
handler_read_rnd_next:这个值越高,说明查询低效。
对mysql优化时一个综合性的技术,主要包括
a: 表的设计合理化(符合3NF)
b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
c: 分表技术(水平分割、垂直分割)
d: 读写[写: update/delete/add]分离
e: 存储过程 [模块化编程,可以提高速度]
f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ] Linux /etc/my.cnf
g: mysql服务器硬件升级
h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
重点:定位慢查询,mysql索引优化,mysql备份
3NF
1NF 关系型数据库自动满足
2NF 表中记录唯一(通常设置一个主键实现)
3NF 没有数据冗余,若表中字段有一对多的关系,考虑分表
反3NF : 但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是: 在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。
//常用命令
show status
常用的:
show status like ‘uptime’ ; //mysql服务工作时间
show stauts like ‘com_select’ ;//select 查询次数
show [session|global] status like (session表示只针对当前窗口,global从mysql 服务启动到现在)
show status like ‘connections’; // 试图连接MySQL服务器的次数
//定位慢查询
set global log_slow_queries=on;//mysql中启用慢查询日志
show variables like "%slow%";//查看是否开启,文件日志保存路径
show variables like ‘long_query_time’; //查看慢查询时间设置值
set long_query_time=0.1; //重新设置为0.1s
show status like ‘slow_queries’; //显示慢查询次数
测试时,可以看到在日志中查看mysql慢sql语句.
优化问题.
explain 查看mysql指令实际的执行情况
添加索引
四种索引(主键索引/唯一索引/全文索引/普通索引)
数据库表3个文件,(数据表结构,数据,索引)
1. 添加
1.1主键索引添加
当创建表,将某个字段设为主键的时候,该字段,就是主键索引。
alter table 表名 add primary key (列名);//可后添加
1.2普通索引
一般表创建后,再创建
create index xx on表 (列1,列名2);
1.3 全文索引
主要是针对对varchar,text的检索,全文索引针对MyISAM有用.(中文使用sphinx)
停止词如一些常用词和字符,不会创建
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
ALTER TABLE articles ADD FULLTEXT INDEX fulltext_article(title,body);//可以后创建
select * from articles where match(title,body) against(‘关键字’); //用法
1.4唯一索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);
unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复.
主键字段,不能为NULL,也不能重复.
②在创建表后,再去创建唯一索引
create unique index 索引名 on 表名 (列表..);
2. 查询索引
show index(es) from 表名\G
show keys from 表名\G
3. 删除
alter table 表名 drop index 索引名;
4. 修改 //先删除,再重新创建.
索引创建注意
满足以下条件的字段,才应该创建索引.
Where条件下经常使用,该字段内容多样,不频繁变化
索引使用注意
//使用索引
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列
explain select * from dept where loc='aaa'\G
//复合索引,查询条件时,使用最左边的列。
//like查询,请使用 ‘aaa%’,不要使用‘%aaa’或 ‘_aaa’(考虑sphinx)
//or 查询,所有条件的字段带索引,才使用索引,(避免使用or)
select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
//字段类型为字符串,查询时一定要用引号引起来,(添加时,字符串必须’’)
//不经常出现,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
//如何查看索引使用的情况:
show status like ‘Handler_read%’;
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
//sql语句的小技巧
Group by分组查询后,还会进行内部排序,order by null可以防止排序.
//可使用左外连接代替子查询,因为使用join,MySQL不需要在内存中创建临时表。
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno; [左外连接,更ok!]
//选择mysql的存储引擎
在开发中,我们经常使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,考虑使用,比如 bbs 中的 发帖表,回复表.
INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
//如果数据库的存储引擎是myisam, 删除数据后,数据文件大小没有变化,记住要定时进行碎片整理
// mysql命令行,对myisam进行整理,linux下 /etc/my.cnf 找到datadir路径查看清理效果
handler_read_rnd_next:这个值越高,说明查询低效。