0029-0031
0029 MySql优化入门
1。mysql怎样优化?
数据库设计--三大范式
1. 原子性,属性不可再分割
2. 要有主键并且唯一(电商中主键不可以做订单号,订单号可以做主键)
3. 字段间不能有冗余
表的设计合理化(符合3NF)
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
SQL语句优化
分表技术(水平分割、垂直分割)
读写[写: update/delete/add]分离
存储过程 [模块化编程,可以提高速度]
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
mysql服务器硬件升级
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
SQL优化
1.如何在一个大项目中,迅速的定位执行速度慢的语句(定位慢查询) 慢查询,执行计划
mysql中在10s内没有按照规定返回结果,则叫慢查询,mysql会放到一个日志中
使用show status使用show status查看MySQL服务器状态信息
常用命令
--mysql数据库启动了多少时间 show status like 'uptime'; |
show stauts like 'com_select' show stauts like 'com_insert' ...类推 update delete(显示数据库的查询,更新,添加,删除的次数) |
show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话,指取出当前窗口的执行,如果你想看所有(从mysql 启动到现在,则应该 global) |
//显示到mysql数据库的连接数 show status like 'connections '; |
//显示慢查询次数 show status like 'slow_queries'; |
--查询慢查询时间
show variables like 'long_query_time';
--修改慢查询时间
set long_query_time=1; ---但是重启mysql之后,long_query_time依然是my.ini中的值
如何将慢查询定位到日志中
在默认情况下,我们的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 文件中记录的位置
#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
创建大量数据的存储过程
delimiter $$ create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 0; #set autocommit =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 $$ 执行存储过程 call insert_emp (100001,40000000);
索引
索引主要作用:提高查询效率二叉树-B+树
没有加索引时使用全局扫描--折半查找
索引分类
主键索引
唯一索引
普通索引
组合索引
全文索引--查找文章,一般查找大文件用搜索引擎solc和lucence
mysql数据库实现原理:B+树,先查所有数据,然后再折半。比他大的数据放右边,小的数据放左边
在什么场景下要加索引?
1.出现慢查询的时候,开始定位。肯定要加索引。一般是经常作为查找条件的需要加索引,但是像性别,已经唯一的不用加索引。频繁更新的不用加索引。不需要where条件的不用加索引
如果全文扫描比索引效率高,则不用加索引。
那些列上适合添加索引
① 查询作为查询条件字段应该创建索引
② 唯一性太差的字段不适合单独创建索引,即使频繁
Select * from emp where sex=’男’
③ 频繁更新字段,也不要定义索引。
④ 不会出现在where语句的字段不要创建索引
总结:满处一下条件的字段,才应该创建索引
① 肯定在where条件经常使用
② 该字段的内容不是唯一的几个值
③ 字段内容不是频繁变化
注意:
1.对于创建的多列索引,如果不是使用第一部分,则不会创建索引。
explain select * from dept where loc='aaa'\G 就不会使用到索引
2.模糊查询在like前面有百分号开头会失效。
3. 如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是,如果列是字符串类型,就一定要用 ‘’ 把他包括起来.
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
show status like ‘handler_read%’;
大家可以注意:
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高,说明查询低效。
创建了一个索引后,用explain select * from emp where empno=1;这种方式查看是否用了索引
0031 SQL语句调优
问:怎样进行sql数据调优?
1. 我们之前为了找到sql查询比较慢,首先配置慢查询,然后说怎样分析sql语句。
从索引到sql调优,通过explain查看执行计划,
然后说
① 使用group by 分组查询是,默认分组后,还会排序,可能会降低速度,
在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno 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!]
③ 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
最好不要给数据库留 NULL,尽可能的使用 NOT NULL 填充数据库.
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用 NULL。
不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL 也包含在内),都是占用 100 个字符的空间的,如果是 varchar 这样的变长字段, null 不占用空间。
可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询:
select id from t where num = 0
第二节 mysql数据引擎
使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表,回复表.
INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
MyISAM 和 INNODB的区别
1. 事务安全(MyISAM不支持事务,INNODB支持事务)
2. 查询和添加速度(MyISAM批量插入速度快)
3. 支持全文索引(MyISAM支持全文索引,INNODB不支持全文索引)
4. 锁机制(MyISAM时表锁,innodb是行锁)
5. 外键 MyISAM 不支持外键, INNODB支持外键. (在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致)
Memory 存储,比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用memory, 速度极快. (如果mysql重启的话,数据就不存在了)
数据备份
手动方式:
cmd控制台:
在环境变量中配置mysql环境变量
mysqldump –u -账号 –密码 数据库 [表名1 表名2..] > 文件路径
案例: mysqldump -u -root root test > d:\temp.sql
比如: 把temp数据库备份到 d:\temp.bak
mysqldump -u root -proot test > f:\temp.bak
如果你希望备份是,数据库的某几张表
mysqldump -u root -proot test dept > f:\temp.dept.sql
如何使用备份文件恢复我们的数据.
mysql控制台
source d:\temp.dept.bak
自动方式:
把备份数据库的指令,写入到 bat文件, 然后通过任务管理器去定时调用 bat文件.
mytask.bat 内容是:
@echo off F:\path\mysqlanzhuang\bin\mysqldump -u root -proot test dept > f:\temp.dept.sql |
创建执行计划任务执行脚本。