韩顺平mysql优化笔记

Mysql数据库的优化技术对mysql优化时一个综合性的技术，主要包括a:表的设计合理化(符合3NF)b:添加适当索引(index)[四种:普通索引、主键索引、唯一索引unique、全文索引]c:分表技术(水平分割、垂直分割)d:读写[写:update/delete/add]分离e:存储过程[模块化编程，可以提高速度]f:对mysql配置优化[配置最大并发数my.ini,调整缓存大小，默认为100，max_connection=1000]g:mysql服务器硬件升级h:定时的去清除不需要的数据,定时进行碎片整理(MyISAM)什么样的表才是符合3NF(范式)表的范式，是首先符合1NF,才能满足2NF,进一步满足3NF1NF:即表的列的具有原子性,不可再分解，即列的信息，不能分解,只有数据库是关系型数据库(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’;（mysql数据库启动了多长时间）showstautslike‘com_select’showstautslike‘com_insert’...类推updatedelete（显示数据库的查询，更新，添加，删除的次数）☞show[session|global]statuslike....如果你不写[session|global]默认是session会话，指取出当前窗口的执行，如果你想看所有(从mysql启动到现在，则应该global)//显示到mysql数据库的连接数showstatuslike‘connections’;//显示慢查询次数showstatuslike‘slow_queries’;②如何去定位慢查询构建一个大表(400万)-存储过程构建默认情况下，mysql认为10秒才是一个慢查询.修改mysql的慢查询.showvariableslike‘long_query_time’;//可以显示当前慢查询时间（默认当前的session的慢查询时间）mysqlshowglobalvariableslike'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'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declarereturn_strvarchar(255)default'';declareiintdefault0;whileindosetreturn_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))begindeclareiintdefault0;#setautocommit=0把autocommit设置成0setautocommit=0;repeatseti=i+1;insertintoempvalues((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());untili=max_numendrepeat;commit;end$$#调用刚刚写好的函数,1800000条记录,从100001号开始callinsert_emp(100001,4000000);③这时我们如果出现一条语句执行时间超过1秒中，就会统计到.④如果把慢查询的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文件中记录的位置#Pathtothedatabaserootdatadir=C:/DocumentsandSettings/AllUsers/ApplicationData/MySQL/MySQLServer5.5/Data/⑤测试,可以看到在日志中就记录下我们的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%’;【不会使用到全文索引】mysqlexplainselect*fromarticleswherebodylike'mysql%'\G*********************