mysql优化笔记.docx
- 文档编号:27134597
- 上传时间:2023-06-27
- 格式:DOCX
- 页数:30
- 大小:538.16KB
mysql优化笔记.docx
《mysql优化笔记.docx》由会员分享,可在线阅读,更多相关《mysql优化笔记.docx(30页珍藏版)》请在冰豆网上搜索。
mysql优化笔记
◆Mysql数据库的优化技术<大型网站优化技术>
对mysql优化时一个综合性的技术,主要包括
a:
表的设计合理化(符合3NF)
b:
添加适当索引(index)[四种:
普通索引、主键索引、唯一索引unique、全文索引]
c:
分表技术(水平分割、垂直分割)
d:
读写[写:
update/delete/add]分离
e:
存储过程[模块化编程,可以提高速度]数据库的三层结构:
f:
对mysql配置优化[配置最大并发数my.ini,调整缓存大小]
g:
mysql服务器硬件升级
h:
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name
[USINGindex_type]
ONtbl_name(index_col_name,...)
◆什么样的表才是符合3NF(范式)
表的范式,是首先符合1NF,才能满足2NF,进一步满足3NF
1NF:
即表的列的具有原子性,不可再分解,即列的信息,不能分解,只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sqlserver),就自动的满足1NF
☞数据库的分类
关系型数据库:
mysql/oracle/db2/informix/sysbase/sqlserver
非关系型数据库:
(特点:
面向对象或者集合)
NoSql数据库:
MongoDB(特点是面向文档)
2NF:
表中的记录是唯一的,就满足2NF,通常我们设计一个主键来实现idprimarykey;
3NF:
即表中不要有冗余数据,就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放.比如下面的设计就是不满足3NF:
显示推导处理
反3NF:
但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。
具体做法是:
在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。
降低范式就是增加字段,允许冗余。
案例:
◆Sql语句本身的优化
问题是:
如何从一个大项目中,迅速的定位执行速度慢的语句.(定位慢查询)
1首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete../当前连接)
showstatus
常用的:
showstatuslike‘uptime’;
showstautslike‘com_select’showstautslike‘com_insert’...类推updatedelete
☞show[session|global]statuslike....如果你不写[session|global]默认是session会话,指取出当前窗口的执行,如果你想看所有(从mysql启动到现在,则应该global)
showstatuslike‘connections’;
//显示慢查询次数
showstatuslike‘slow_queries’;
2如何去定位慢查询
构建一个大表(400万)-> 存储过程构建
默认情况下,mysql认为10秒才是一个慢查询.
●修改mysql的慢查询.
showvariableslike‘long_query_time’;//可以显示当前慢查询时间
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$$
createfunctionrand_string(nINT)
returnsvarchar(255)#该函数会返回一个字符串
begin
#chars_str定义一个变量chars_str,类型是varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declarechars_strvarchar(100)default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declarereturn_strvarchar(255)default'';
declareiintdefault0;
whilei 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 seti=i+1; insertintoempvalues((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); untili=max_num endrepeat; commit; end$$ #调用刚刚写好的函数,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%’ 正确的用法是: select*fromarticleswherematch(title,body)against(‘database’);【可以】 ☞说明: 1.在mysql中fulltext索引只针对myisam生效 2.mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文 3.使用方法是match(字段名..)against(‘关键字’) 4.全文索引一个叫停止词,因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词. 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操作有影响,变慢 ◆在哪些列上适合添加索引? 总结: 满足以下条件的字段,才应该创建索引. a: 肯定在where条经常使用b: 该字段的内容不是唯一的几个值(sex)c: 字段内容不是频繁变化. ◆使用索引的注意事项 把dept表中,我增加几个部门: altertabledeptaddindexmy_ind(dname,loc);//dname左边的列,loc就是右边的列 说明,如果我们的表中有复合索引(索引作用在多列上),此时我们注意: 1,对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。 explainselect*fromdeptwhereloc='aaa'\G 就不会使用到索引 2,对于使用like的查询,查询如果是‘%aaa’不会使用到索引 ‘aaa%’会使用到索引。 比如: explainselect*fromdeptwherednamelike'%aaa'\G 不能使用索引,即,在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.事务安全 2.查询和添加速度 3.支持全文索引 4.锁机制 5.外键MyISAM不支持外键,INNODB支持外键.(在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致) Memory存储,比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用memory,速度极快. ◆如果你的数据库的存储引擎是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..]>文件路径 比如: 把temp数据库备份到d: \temp.bak mysqldump–uroot–proottemp>d: \temp.bak 如果你希望备份是,数据库的某几张表 mysqldump–uroot–prottempdept>d: \temp.dept.bak 如何使用备份文件恢复我们的数据. mysql控制台 sourced: \temp.dept.bak 2使用定时器来自定完成 把备份数据库的指令,写入到bat文件,然后通过任务管理器去定时调用bat文件. mytask.bat内容是: C: \myenv\mysql5.5.27\bin\mysqldump-uroot-proottempdept>d: \temp.dept.bak ☞如果你的mysqldump.exe文件路径有空格,则一定要使用“”包括. 把mytask.bat做成一个任务,并定时调用在2: 00调用一次 步骤任务计划->增加一个任务,选中你的mytask.bat文件,最后配置: 测试ok 现在问题是,每次都是覆盖原来的备份文件,不利用我们分时间段进行备份,我们可以这样处理;示意图: 代码是: mytask2.bat内容: C: \myenv\php-5.3.5\php.exeC: \myenv\apache\htdocs\mytask.php mytask.php代码: php //定时备份我们的数据库文件 date_default_timezone_set('PRC'); $bakfilename=date("YmdHis",time()); $command="C: \myenv\mysql5.5.27\bin\mysqldump-uroot-proottempdept>d: \\{$bakfilename}"; exec($command); 最后测试ok! 作用是,写一个数据库,数据库中有三张表,然后每天2: 00备份一次,文件名以时间来命名.测试. ◆使用PHP完成定时发送邮件的功能 1看一个实际的需求 2设计一张邮件表 createtablemaillist (idintunsignedprimarykeyauto_increment, gettervarchar(64)notnulldefault'', sendervarchar(64)notnulldefault'', titlevarchar(32)notnulldefault'', contentvarchar(2048)notnulldefault'', sendtimeintunsignednotnulldefault0, flagtinyintunsignednotnulldefault0)engine=myisamcharsetutf8; insertintomaillistvalues(null,'hsp@','hanshunping@','hello100','abchello',unix_timestamp()+10*3600,0); insertintomaillistvalues(null,'hsp@','h
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 优化 笔记