mysql 数据库优化
包括
a.表的设计合理化(符合3NF)
b.添加适当索引(index[4种:普通索引 主键索引 唯一索引unique 全文索引])
c.分表技术(水平分割,垂直分割)
d.读写[写:update/delete/add]分离
e.存储过程[模块化编程 可以提高速度]
数据库的三层结构 orale MySQL db2 sql server
php程序通过dbms(数据库管理系统)操作数据库文件,数据库执行相关操作返回给dbms,然后再返回给PHP
dbms 首先编译PHP代码,然后执行操作,然后缓存结果,但是编译很费时间
直接编译耗时严重,所以可以直接编程写存储过程(分页存储/触发器)
PHP中执行 call proc('参数')调用存储过程 利于模块化编程
f.对MySQL配置优化[配置最大并发数 my.ini]
# 最大并发数
# 一般网站调整到 1000 左右
max_connections = 100
# 调整缓存大小
g.MySQL 服务器硬件升级
h.定时的去清除不需要的数据,并且定时进行碎片整理(尤其对搜索引擎是MyISAM)
数据库设计的三范式 3NF
表的范式,是首先符合 1NF
才能满足2NF
进一步满足3NF
1NF:
是对属性的原子性约束,要求属性(列)具有原子性,不可再分解
只要是关系型数据库都满足1NF
数据库的分类:
关系型数据库 :MySQL/Oracle/db2/informix/sysbase/sql server
非关系型数据库 :特点是面向对象或者集合的
NoSql数据库 :MongoDB(特点是面向文档)
2NF:
是对记录的唯一性约束,要求记录有唯一标识,即实体的唯一性
3NF:
是对字段冗余性的约束,要求字段没有冗余,即 表中不要有冗余数据
也就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放
反 3NF
实际上一定的冗余是允许的 就是反 3NF
在表的 1对N 情况下,为了提高效率,可能会在 1 这表中设计字段 提速
譬如 相册的浏览量
相册 字段 id name views
相片 字段 id name path views
这里的views字段,在两个表中都有,可以避免显示相册浏览量的时候再去查询计算相册内相片的浏览量总和
以此冗余提高查询效率
SQL优化的一般步骤
① 通过 show status 命令了解各种SQL的执行频率
② 定位执行效率较低的SQL语句 (重点select)
③ 通过 explain 分析低效率的SQL语句的执行情况
④ 确定问题并采取相应的优化措施
SQL语句本身的优化
问题:如果从一个大型项目中,迅速的定位执行速度慢的语句
① 首先了解MySQL数据库的一些运行状态如何查询 show status
比如想知道当前MySQL运行时间,一共执行了多少次select/update/delete 当前连接 等等
常用的
show status like 'uptime' 当前MySQL运行时间
show status like 'com_select' 当前MySQL执行了多少次查询
show status like 'com_insert' 当前MySQL执行了多少次添加
show status like 'com_update' 当前MySQL执行了多少次更新
show status like 'com_delete' 当前MySQL执行了多少次删除
show status 语法:
show [session|global] status like '';
如果不写 [session|global] 表示默认是 session 指取出当前窗口的执行情况
如果想看所有(mysql启动到现在)的情况 加上 global
show global status like 'com_insert';
show status like 'connections';试图连接MySQL的连接数
显示慢查询次数
show status like 'slow_queries';
② 如何去定位慢查询
构建一个大表(400万数据)->存储过程构建
默认情况下,MySQL认为 10秒钟 才是一个慢查询
修改MySQL的慢查询时间
// 显示慢查询时间
show variables like 'long_query_time';
// 修改慢查询时间
set long_query_time=2;// 即修改慢查询时间为2秒
构建大表->大表中记录有要求,记录是不同才有用,否则测试效果与真实的相差很大
为了存储过程能正常执行,需要修改命令执行结束符修改一下
语法 delimiter $$ $$表示修改后的结束符
当你想使用函数时,可以指向一个dual表,这个表是亚元表,就是个空表
select rand_string(6) from dual;
③ 这时我们如果出现一条语句执行时间超过1秒,就会被统计到
call insert_emp 执行存储过程这个也会被记录
④ 如果把慢查询的 SQL 语句记录到我们的一个日志中
在默认情况下,我们的MySQL不会记录慢查询,需要启动MySQL时,
指定记录慢查询才可以
bin/mysqld.exe --safe-mode --slow-query-log mysql5.5在my.ini指定
bin/mysqld.exe -log-slow-queries=d:/abc.log 低版本mysql5.0可以在my.ini指定
先关闭MySQL 再重新启动
如果启用了慢查询日志,默认存放在 my.ini 文件中记录的位置 即 datadir设置的目录
⑤ 测试,可以看到在日志中就记录下我们的MySQL慢sql语句
优化问题
通过 explain 语句可以分析,MySQL如何执行你的SQL语句
添加索引
四种索引 主键索引 唯一索引 全文索引 普通索引
1.添加
1.1 主键索引的添加
当一张表中,把某个列设为主键的时候,则该列就是主键索引
如果你创建表时,没有指定主键索引,也可以在创建表后添加索引
语句:alter table 表名 add primary key (列名);
1.2 普通索引
一般来说,普通索引的创建,是先创建表,然后再创建索引
语句:create index 索引名 on 表名 (列名);
1.3 全文索引
全文索引:主要是针对文件,文本的索引,比如文章
全文索引针对MyISAM有用
如何使用全文索引
错误用法:
select * from articles where body like '%mysql%';
这种方法是不会用到全文索引的
正确用法:
// title,body是全文索引,匹配database的句子
select * from articles where match(title,body) against('database');
说明:
1.在MySQL中fulltext索引只针对myISAM生效
2.针对英文生效,对中文需要sphinx(coreseek)技术处理
3.使用方法是match(字段名) against('关键字')
4.全文索引有一个停止词概念:
因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,
就不会创建,这些词,称为 停止词。
1.4 唯一索引
当表的某列被指定为unique约束,这列就是一个唯一索引
唯一索引的列可以为null,并且可以有多个
在创建表后,再去创建唯一索引
创建语法:create unique index 索引名 on 表名 (列名);
2.查询
① desc 表名 该方法缺点:不能够显示索引的名字
② show index(es) from 表名
③ show keys from 表名
3.删除
语法:alter table 表名 drop index 索引名;
主键索引删除:alter table 表名 drop primary key;
4.修改
先删除,再重新创建
索引注意事项:
索引占用磁盘空间
对dml(insert/update/delete)语句效率有影响
在哪些列上适合添加索引?
较频繁的作为查询条件字段创建索引
例如 select * from emp where empno=1;
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
例如 select * from emp where sex='男';
更新非常频繁的字段不适合创建索引
例如 select * from emp where logincount=1;
不会出现在where子句中字段不该创建索引
总结:满足以下条件的字段,才能创建索引
a.肯定在where条件中经常使用的
b.该字段的内容不是唯一的几个值
c.字段内容变化不能太频繁
使用索引的注意事项
alter table dept add index myind (dname,loc); // dname就是左边的列,loc是右边的列
下列情况有可能使用到索引
a.对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用
explain select * from dept where dname='aaa';
b.对于使用like的查询,查询条件如果是'%aaa'则不会使用到索引,'aaa%'会使用到索引
下列情况不会使用索引
a.如果条件中有or,即使其中有条件带索引也不会使用
换言之,就是要求使用的所有字段都创建索引,建议:尽量避免使用or关键字
b.对于多列索引,不是使用的第一部分,则不会使用索引
explain select * from dept where loc='aaa';// 多列索引时,loc为右边列,索引不会使用到
c.like查询是以%开头
如果一定要使用,则使用全文索引去查询
d.如果列类型是字符串,那一定要在条件中将数据使用引号引起来,否则不使用索引
e.如果MySQL估计使用全表扫描要比使用索引块,则不使用索引
explain select * from dept where loc='aaa';
explain 语句详解:
告诉我们MySQL将使用怎样的执行计划来优化query
id:1 查询序列号
select_type:SIMPLE 查询类型
table:dept 查询的表名
type:ALL 扫描的方式 all表示全表扫描
possible_keys:null 可能使用到的索引
key:null 实际使用的索引
key_len:null
ref:null
rows:10 该SQL语句扫描了多少行,可能得到结果数
Extra:Using where SQL语句的额外信息,比如排序方式filesort等等
select_type 类型
primary : 子查询中最外层查询
subquery : 子查询内层第一个select,结果不依赖于外部查询
dependent subquery : 子查询内层第一个select,依赖于外部查询
union : union语句中第二个select开始后面所有select
simple : 简单模式
union result : union中合并结果
type 类型
all : 完整的表扫描 通常不好
system : 表仅有一行(=系统表) 这是const联接类型的一个特例
const : 表最多有一个匹配行
extra 类型
no table : query语句中使用 from dual 或不含任何from子句
Using filesort : 当query中包含 order by 操作,而且无法利用索引完成排序
impossible WHERE noticed after reading const tables:Mysql query optimizer
通过收集统计信息不可能存在结果
Using temporary : 某些操作必须使用临时表,常见 group by ,order by
Using where : 不用读取表中所有信息,仅通过索引就可以获取所需数据
explain可以帮助我们在不真正执行某个SQL语句时,就知道MySQL怎样执行,利于我们去分析SQL指令
查看索引的使用情况
show status like 'Handler_read%';
handler_read_key:这个值越高越好,代表使用索引查询到的次数
handler_read_rnd_next:这个值越高,说明查询低效
SQL语句的小技巧
① 优化 group by 语句
默认情况下,MySQL对所有的group by col1,col2 进行排序,这与在查询中指定 order by col1,col2 类似
如果查询中包括 group by 但用户想尽量避免排序结果的消耗,则可以使用 order by null 禁止排序
② 有些情况下,可以使用连接来替代子查询
因为使用 join MySQL不需要在内存中创建临时表
③ 如果想要在含有 or 的查询语句中利用索引,则 or 之间的每个条件列都必须用到索引,
如果没有索引,则应该考虑增加索引。
如何选择MySQL的存储引擎
1.myISAM:
如果表对事务要求不高,同时是以查询和添加为主的。
比如 BBS中的发帖表,回复表
2.InnoDB:
对事务要求高,保存的数据都是重要数据
比如 订单表,账户表
3.Memory:
数据变化频繁,不需要入库同时又经常查询和修改
myISAM 与 InnoDB 主要区别
1.myisam 批量插入速度快,InnoDB慢,myisam插入数据时不排序
2.InnoDB支持事务
3.myisam支持全文索引
4.锁机制,myisam是表锁,InnoDB是行锁
5.myisam不支持外键,InnoDB支持外键
外键
classes表
create table classes(
id int unsigned not null auto_increment primary key,
name varchar(64) not null
)engine=innoDB;
insert into classes values (1,'aaa');
stu表
create table student(
id int unsigned not null auto_increment primary key,
name varchar(64) not null,
classid int unsigned not null,
foreign key (classid) references classes(id)
)engine=innoDB;
当设置了外键的时候,企图添加一个外键没有的数据,会报错,无法插入数据
insert into student values (1,'hello',1); 这个是正确的
insert into student values (1,'hello',2); 当classes表中id=2不存在时,这个是错误的
在PHP开发中,通常不设置外键,通常在程序中保持数据的一致性。
选择合适的数据类型
① 在精度要求高的应用中,建议使用定点数来存储数值,以保证数据的准确性。deciaml精度比float高,尽量使用
② 对于存储引擎是myisam的数据库,如果经常做删除和修改记录的操作,
要定时执行optimize table table_name;功能对表进行碎片整理。
③ 日期类型要根据实际需要选择能够满足应用的最小存储的早期类型
create table bbs (id int unsigned not null ,con varchar(1024) , pub_time int );
php备份数据库
① 手动备份数据库(表)的方法
cmd控制台
mysqldump -uroot -proot 数据库[表名1 表名2 ...] > 文件路径
例如 把 temp 数据库备份到 d:/temp.bak
mysqldump -uroot -proot temp > d:/temp.bak
如果希望备份数据库中某几张表