韩顺平mysql优化笔记Word文件下载.docx
《韩顺平mysql优化笔记Word文件下载.docx》由会员分享,可在线阅读,更多相关《韩顺平mysql优化笔记Word文件下载.docx(33页珍藏版)》请在冰豆网上搜索。
即表中不要有冗余数据,就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放.比如下面的设计就是不满足3NF:
反3NF:
但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。
具体做法是:
在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。
降低范式就是增加字段,允许冗余。
案例:
◆Sql语句本身的优化
问题是:
如何从一个大项目中,迅速的定位执行速度慢的语句.(定位慢查询)
1首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete../当前连接)
showstatus
常用的:
showstatuslike‘uptime’;
(mysql数据库启动了多长时间)
showstautslike‘com_select’showstautslike‘com_insert’...类推updatedelete(显示数据库的查询,更新,添加,删除的次数)
☞show[session|global]statuslike....如果你不写[session|global]默认是session会话,指取出当前窗口的执行,如果你想看所有(从mysql启动到现在,则应该global)
//显示到mysql数据库的连接数
showstatuslike‘connections’;
//显示慢查询次数
showstatuslike‘slow_queries’;
2如何去定位慢查询
构建一个大表(400万)->
存储过程构建
默认情况下,mysql认为10秒才是一个慢查询.
●修改mysql的慢查询.
showvariableslike‘long_query_time’;
//可以显示当前慢查询时间(默认当前的session的慢查询时间)
mysql>
showglobalvariableslike'
long_query_time'
;
+-----------------+-----------+
|Variable_name|Value|
|long_query_time|10.000000|(但是重启mysql之后,long_query_time依然是my.ini中的值)
setlong_query_time=1;
//可以修改慢查询时间
构建大表->
大表中记录有要求,记录是不同才有用,否则测试效果和真实的相差大.
创建:
CREATETABLEdept(/*部门表*/
deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*编号*/
dnameVARCHAR(20)NOTNULLDEFAULT"
"
/*名称*/
locVARCHAR(13)NOTNULLDEFAULT"
/*地点*/
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
CREATETABLEemp
(empnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*编号*/
enameVARCHAR(20)NOTNULLDEFAULT"
/*名字*/
jobVARCHAR(9)NOTNULLDEFAULT"
/*工作*/
mgrMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,/*上级编号*/
hiredateDATENOTNULL,/*入职时间*/
salDECIMAL(7,2)NOTNULL,/*薪水*/
commDECIMAL(7,2)NOTNULL,/*红利*/
deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0/*部门编号*/
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
CREATETABLEsalgrade
(
gradeMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,
losalDECIMAL(17,2)NOTNULL,
hisalDECIMAL(17,2)NOTNULL
)ENGINE=MyISAMDEFAULTCHARSET=utf8;
测试数据
INSERTINTOsalgradeVALUES(1,700,1200);
INSERTINTOsalgradeVALUES(2,1201,1400);
INSERTINTOsalgradeVALUES(3,1401,2000);
INSERTINTOsalgradeVALUES(4,2001,3000);
INSERTINTOsalgradeVALUES(5,3001,9999);
为了存储过程能够正常执行,我们需要把命令执行结束符修改
delimiter$$
setgloballog_bin_trust_function_creators=TRUE;
#ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsde错误解决办法
createfunctionrand_string(nINT)
returnsvarchar(255)#该函数会返回一个字符串
begin
#chars_str定义一个变量chars_str,类型是varchar(100),默认值'
abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declarechars_strvarchar(100)default
'
declarereturn_strvarchar(255)default'
'
declareiintdefault0;
whilei<
ndo
setreturn_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
seti=i+1;
endwhile;
returnreturn_str;
end$$
如果希望在程序中使用,是Ok!
创建一个存储过程
createprocedureinsert_emp(instartint(10),inmax_numint(10))
begin
declareiintdefault0;
#setautocommit=0把autocommit设置成0
setautocommit=0;
repeat
insertintoempvalues((start+i),rand_string(6),'
SALESMAN'
0001,curdate(),2000,400,rand_num());
untili=max_num
endrepeat;
commit;
#调用刚刚写好的函数,1800000条记录,从100001号开始
callinsert_emp(100001,4000000);
3这时我们如果出现一条语句执行时间超过1秒中,就会统计到.
4如果把慢查询的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文件中记录的位置
#Pathtothedatabaseroot
datadir="
C:
/DocumentsandSettings/AllUsers/ApplicationData/MySQL/MySQLServer5.5/Data/"
5测试,可以看到在日志中就记录下我们的mysql慢sql语句.
优化问题.
通过explain语句可以分析,mysql如何执行你的sql语句,这个工具的使用放一下,一会说.
添加索引【小建议:
】
◆四种索引(主键索引/唯一索引/全文索引/普通索引)
1.添加
1.1主键索引添加
当一张表,把某个列设为主键的时候,则该列就是主键索引
createtableaaa
(idintunsignedprimarykeyauto_increment,
namevarchar(32)notnulldefaul‘’);
这是id列就是主键索引.
如果你创建表时,没有指定主键索引,也可以在创建表后,在添加,指令:
altertable表名addprimarykey(列名);
举例:
createtablebbb(idint,namevarchar(32)notnulldefault‘’);
altertablebbbaddprimarykey(id);
1.2普通索引
一般来说,普通索引的创建,是先创建表,然后在创建普通索引
比如:
createtableccc(
idintunsigned,
namevarchar(32)
)
createindex索引名on表(列1,列名2);
1.3创建全文索引
全文索引,主要是针对文件,文本的检索,比如文章,全文索引针对MyISAM有用.
创建:
CREATETABLEarticles(
idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
titleVARCHAR(200),
bodyTEXT,
FULLTEXT(title,body)
)engine=myisamcharsetutf8;
INSERTINTOarticles(title,body)VALUES
('
MySQLTutorial'
'
DBMSstandsforDataBase...'
),
HowToUseMySQLWell'
Afteryouwentthrougha...'
OptimizingMySQL'
Inthistutorialwewillshow...'
1001MySQLTricks'
1.Neverrunmysqldasroot.2....'
MySQLvs.YourSQL'
Inthefollowingdatabasecomparison...'
MySQLSecurity'
Whenconfiguredproperly,MySQL...'
);
如何使用全文索引:
错误用法:
select*fromarticleswherebodylike‘%mysql%’;
【不会使用到全文索引】
explainselect*fromarticleswherebodylike'
mysql%'
\G
***************************1.row***************************
id:
1
select_type:
SIMPLE
table:
articles
type:
ALL
possible_keys:
NULL
key:
key_len:
ref:
rows:
6
Extra:
Usingwhere
1rowinset(0.00sec)
证明:
explainselect*fromarticleswherebodylike‘%mysql%’
正确的用法是:
select*fromarticleswherematch(title,body)against(‘database’);
【可以】
explainselect*fromarticleswherematch(title,body)against('
mysql'
)\G
fulltext
title
0
☞说明:
1.在mysql中fulltext索引只针对myisam生效
2.mysql自己提供的fulltext针对英文生效->
sphinx(coreseek)技术处理中文
3.使用方法是match(字段名..)against(‘关键字’)
4.全文索引:
停止词,因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.比如(a,b,mysql,the)
selectmatch(title,body)against('
database'
)fromarticles;
(输出的是每行和database的匹配度)
+----------------------------------------+
|match(title,body)against('
)|
|0.6554583311080933|
|0|
|0.6626645922660828|
6rowsinset(0.01sec)
1.4唯一索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
createtableddd(idintprimarykeyauto_increment,namevarchar(32)unique);
这时,name列就是一个唯一索引.
unique字段可以为NULL,并可以有多NULL,但是如果是具体内容,则不能重复,
但是不能存有重复的空字符串’’
.
主键字段,不能为NULL,也不能重复.
②在创建表后,再去创建唯一索引
createtableeee(idintprimarykeyauto_increment,namevarchar(32));
createuniqueindex索引名on表名(列表..);
2.查询索引
desc表名【该方法的缺点是:
不能够显示索引名.】
showindex(es)from表名
showkeysfrom表名
3.删除
altertable表名dropindex索引名;
如果删除主键索引。
altertable表名dropprimarykey[这里有一个小问题]
4.修改
先删除,再重新创建.
◆为什么创建索引后,速度就会变快?
原理示意图:
◆索引使用的注意事项
索引的代价:
1.占用磁盘空间
2.对dml操作有影响,变慢
◆在哪些列上适合添加索引?
总结:
满足以下条件的字段,才应该创建索引.
肯定在where条件经常使用b:
该字段的内容不是唯一的几个值(sex)c:
字段内容不是频繁变化.
◆使用索引的注意事项
把dept表中,我增加几个部门:
altertabledeptaddindexmy_ind(dname,loc);
//dname左边的列,loc就是右边的列
说明,如果我们的表中有复合索引(索引作用在多列上),此时我们注意:
1,对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
explainselect*fromdeptwhereloc='
aaa'
\G
就不会使用到索引
2,对于使用like的查询,查询如果是‘%aaa’不会使用到索引
‘aaa%’会使用到索引。
explainselect*fromdeptwherednamelike'
%aaa'
不能使用索引,即,在like查询时,关键的‘关键字’,最前面,不能使用%或者_这样的字符.,如果一定要前面有变化的值,则考虑使用全文索引->
sphinx.
3.如果条件中有or,即使其中有条件带索引也不会使用。
换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or关键字
select*fromdeptwheredname=’xxx’orloc=’xx’ordeptno=45
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。
否则不使用索引。
(添加时,字符串必须’’),也就是,如果列是字符串类型,就一定要用‘’把他包括起来.
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
explain可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令.
◆如何查看索引使用的情况:
showstatuslike‘Handler_read%’;
大家可以注意:
handler_read_key:
这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:
这个值越高,说明查询低效。
◆sql语句的小技巧
1.在使用groupby分组查询是,默认分组后,还会排序,可能会降低速度.
在groupby后面增加orderbynull就可以防止排序.
2.有些情况下,可以使用连接来替代子查询。
因为使用join,MySQL不需要在内存中创建临时表。
select*fromdept,empwheredept.deptno=emp.deptno;
[简单处理方式]
select*fromdeptleftjoinempondept.deptno=emp.deptno;
[左外连接,更ok!
]
◆如何选择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重启的话,数据就不存在了)
◆如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理
举例说明:
createtabletest100(idintunsigned,namevarchar(32))engine=myisam;
insertintotest100values(1,’aaaaa’);
insertintotest100values(2,’bbbb’);
insertintotest100values(3,’ccccc’);
我们应该定义对myisam进行整理
optimizetabletest100;
mysql_query(“optimizetables$表名”);
技术就是窗户纸.->
经常和技术好的人交流
◆PHP定时完成数据库的备份
1手动备份数据库(表的)方法
cmd控制台:
mysqldump–uroot–proot数据库[表名1表名2..]>