韩顺平版mysql优化详解

韩顺平版：mysql优化详解

Mysql数据库的优化技术对mysql优化时一个综合性的技术，主要包括a:表的设计合理化(符合3NF)b:添加适当索引(index)[四种:普通索引、主键索引、唯一索引unique、全文索引]c:分表技术(水平分割、垂直分割)d:读写[写:update/delete/add]分离e:存储过程[模块化编程，可以提高速度]f:对mysql配置优化[配置最大并发数my.ini,调整缓存大小]g:mysql服务器硬件升级h:定时的去清除不需要的数据,定时进行碎片整理(MyISAM)??什么样的表才是符合3NF(范式)表的范式，是首先符合1NF,才能满足2NF,进一步满足3NF?1NF:即表的列的具有原子性,不可再分解，即列的信息，不能分解,只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sqlserver)，就自动的满足1NF?数据库的分类关系型数据库:mysql/oracle/db2/informix/sysbase/sqlserver非关系型数据库:(特点:面向对象或者集合)NoSql数据库:MongoDB(特点是面向文档)??2NF:表中的记录是唯一的,就满足2NF,通常我们设计一个主键来实现?3NF:即表中不要有冗余数据,就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.比如下面的设计就是不满足3NF:???反3NF:但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。?案例：??Sql语句本身的优化?问题是：如何从一个大项目中，迅速的定位执行速度慢的语句.(定位慢查询)?????首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete../当前连接)?showstatus?常用的:showstatuslike‘uptime’;show?stautslike‘com_select’?showstautslike‘com_insert’...类推update?delete?show[session|global]statuslike....如果你不写?[session|global]默认是session会话，指取出当前窗口的执行，如果你想看所有(从mysql启动到现在，则应该global)?showstatuslike‘connections’;//显示慢查询次数showstatuslike‘slow_queries’;????如何去定位慢查询?构建一个大表(400万)->?存储过程构建?默认情况下，mysql认为10秒才是一个慢查询.?l?修改mysql的慢查询.?showvariableslike‘long_query_time’;//可以显示当前慢查询时间setlong_query_time=1;//可以修改慢查询时间??构建大表->大表中记录有要求,记录是不同才有用，否则测试效果和真实的相差大.?创建:CREATETABLEdept(/部门表/deptnoMEDIUMINT??UNSIGNED?NOTNULL?DEFAULT0,?/编号/dnameVARCHAR(20)?NOTNULL?DEFAULT"",/名称/locVARCHAR(13)NOTNULLDEFAULT""/地点/)ENGINE=MyISAMDEFAULTCHARSET=utf8;??CREATETABLEemp(empno?MEDIUMINTUNSIGNED?NOTNULL?DEFAULT0,/编号/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


如何使用全文索引:错误用法:selectfromarticleswherebodylike‘%mysql%’;【不会使用到全文索引】证明:explain?selectfromarticleswherebodylike‘%mysql%’?正确的用法是:selectfromarticleswherematch(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，?对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。?explainselectfromdeptwhereloc=''aaa''\G就不会使用到索引?2，对于使用like的查询，查询如果是?‘%aaa’不会使用到索引??????‘aaa%’会使用到索引。?比如:explainselectfromdeptwherednamelike''%aaa''\G不能使用索引，即，在like查询时，关键的‘关键字’,最前面，不能使用%或者_这样的字符.，如果一定要前面有变化的值，则考虑使用全文索引->sphinx.?3.??????如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引,我们建议大家尽量避免使用or关键字?selectfromdeptwheredname=’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不需要在内存中创建临时表。?selectfromdept,empwheredept.deptno=emp.deptno;[简单处理方式]?selectfromdeptleftjoinempondept.deptno=emp.deptno;?[左外连接，更ok!]?如何选择mysql的存储引擎?在开发中，我们经常使用的存储引擎myisam/innodb/memorymyisam存储:如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用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定时完成数据库的备份??????手动备份数据库(表的)方法?cmd控制台:mysqldump–uroot–proot数据库[表名1表名2..]?>文件路径比如:把temp数据库备份到d:\temp.bakmysqldump–uroot–proottemp>d:\temp.bak如果你希望备份是，数据库的某几张表mysqldump–uroot–prottempdept>d:\temp.dept.bak?如何使用备份文件恢复我们的数据.mysql控制台sourced:\temp.dept.bak?②????使用定时器来自定完成?把备份数据库的指令，写入到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.phpmytask.php代码:?d:\\{$bakfilename}";???????exec($command);?最后测试ok!?作用是，写一个数据库,数据库中有三张表，然后每天2:00备份一次，文件名以时间来命名.测试.??使用PHP完成定时发送邮件的功能?????看一个实际的需求??????设计一张邮件表?createtablemaillist(idintunsignedprimarykeyauto_increment,gettervarchar(64)notnulldefault'''',sendervarchar(64)notnulldefault'''',titlevarchar(32)notnulldefault'''',contentvarchar(2048)notnulldefault'''',sendtimeintunsignednotnulldefault0,flagtinyintunsignednotnulldefault0)engine=myisam?charsetutf8;???insertintomaillistvalues(null,''[e-mail]'',''[e-mail]'',''hello100'',''abchello'',unix_tiwww.shanxiwang.netmestamp()+103600,0);insertintomaillistvalues(null,''[e-mail]'',''[e-mail]'',''hello200'',''abchello200'',unix_timestamp()+103600,0);???③????写代码1.??????怎样可以定时的去检索哪些邮件该发送.,只能每隔一定时间(1min)就看看哪些邮件该发送,mailtask.php??2.??????上面的代码是模拟发送邮件，看看如何真正发送邮件.在PHP中，有一个函数mail,是用于发送邮件，我们实际上可以使用PHPMailer类，我们使用他完成.?l?要正确的使用PHPMailer发送邮件，需要满足如下条件1.??????本身机器是可以联网的2.??????需要搭建自己的smtp邮件服务器->示意图?3.??????搭建自己的邮件服务器.?卸载.安装时傻瓜式的，一步一步的走ok配置：3.1选择access数据库3.23.3配置邮件服务器?点击设置->邮箱域名设置??点击设置->服务器设置设置一个账号(试用版本只能设置5个账号)?代码:CharSet=''utf-8'';$mailer->ContentType=''text/html'';//设置内容类型为html,这样charset才能发挥作用$mailer->Encoding=''base64'';??????//防止服务器中继时,服务器能接收的编码不一致,带来问题.$mailer->From=''shunping@192.168.1.152'';$mailer->FromName=''顺平'';$mailer->Subject=''一份问候,你好,韩顺平'';$mailer->Body=$cont;??//设置一下语言包$mailer->SetLanguage(''zh_cn'');??//增加收件人地址//$mailer->AddAddress(''[e-mail]'',''saozi'');?$mailer->AddAddress(''[e-mail]'',''shunping'');??if($mailer->Send()){???echo''发送okok'';}else{???echo''fail'';}?配置php.ini启用账号?shunping@192.168.1.152?[mailfunction];ForWin32only.;http://php.net/smtpSMTP=localhost;http://php.net/smtp-portsmtp_port=25?;ForWin32only.;http://php.net/sendmail-fromsendmail_from=?shunping@192.168.1.152?测试一把成功!??如何在linux下完成定时任务:linux如何备份.1.直接执行PHP脚本,需要在同一个服务器上执行.#crontab-e00/usr/local/bin/php/home/htdocs/phptimer.php2.通过HTTP请求来触发脚本,PHP文件允许不在同一服务器上#crontab-e00/usr/bin/wget-q-Otemp.txthttp://www.phptimer.com/phptimer.php上面是通过wget来请求PHP文件,PHP输出会保存在临时文件temp.txt中#crontab-e00/usr/bin/curl-otemp.txthttp://www.phptimer.com/phptimer.php上面是通过curl-o来请求PHP文件,PHP输出会保存在临时文件temp.txt中#crontab-e00lynx-dumphttp://www.phptimer.com/phptimer.php上面是通过Lynx文本浏览器来请求PHP文件??n?分表技术?分表技术有(水平分割和垂直分割)?当一张越来越大时候，即使添加索引还慢的话，我们可以使用分表以qq用户表来具体的说明一下分表的操作.思路如图：首先我创建三张表user0/user1/user2,然后我再创建uuid表，该表的作用就是提供自增的id,走代码:createtableuser0(idintunsignedprimarykey,namevarchar(32)notnulldefault'''',pwd?varchar(32)notnulldefault'''')engine=myisamcharsetutf8;?createtableuser1(idintunsignedprimarykey,namevarchar(32)notnulldefault'''',pwd?varchar(32)notnulldefault'''')engine=myisamcharsetutf8;?createtableuser2(idintunsignedprimarykey,namevarchar(32)notnulldefault'''',pwd?varchar(32)notnulldefault'''')engine=myisamcharsetutf8;??createtableuuid(idintunsignedprimarykeyauto_increment)engine=myisamcharsetutf8;编写addUser.php按时间.1.??????分表的标准是依赖业务逻辑(时间/地区/....)2.??????安装字符不同.a-z3.??????我们给用户提供的查询界面一定是有条件，不能让用户进行大范围.(世界)，如果需要的可以根据不同的规则，对应多套分表.4.??????检索时候，带分页条件，减少返回的数据.5.??????项目中，灵活的根据需求来考虑.??n?垂直分割?示意图:一句话:如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中，这种方式称为垂直分割.?