搜索
mysql数据库优化技术：a.表的设计合理(符合3NF);b.添加适当的索引(index)：普通索引，主键索引，唯一索引，全文索引、(空间索引)；c.分表技术(水平、垂直);d.读写分离e.存储过程。提高速度的原因：f.对mysql配置优化(配置最大并发数，调整缓存大小)g.mysql服务硬件升级;h.定时清除不需要的数据，定时进行碎片整理(myisam)。表的设计：3NF标准：范式是1-2-3，这样递增的。1NF：记录具有原子性，列的信息不可分割。只要数据库是关系型数据库，就自动满足1NF.数据库分类：关系数据库：主流数据库非关系数据库：面向对象，集合等NoSql数据库：面向文档，速度快。2NF:表中的记录是唯一的，就满足了，通常我们用一个主键来实现主键：不含业务逻辑，自增长，3NF：表中不要有冗余数据，如果表的信息能被推倒出来，就不应该设计一个字段。实际中：没有冗余的数据不一定是最好的，我们在实际开发中可以反3NF设计一张表。案例分析：在表1对N的情况下，为了满足对速度的要求，可能会在1方设计一些字段，提高速率。sql优化：如何在一个大项目中定位慢查询语句。①了解mysql状态，学会如何去查询(mysql运行时间/一共执行了多少次dml/dql语句/showstatus查询出了300多个状态showstatuslike‘uptime‘查询启动时间showstatuslike‘con_select’查看执行了多少次查询，update/delete/insert以此类推特别说明：show[seeion|global]statuslike.....session:会话状态，就是本次回话的状态global:表示从启动mysql服务开始一直以来的状态showstatuslike‘connections’查询当前的连接数显示目前慢查询的次数：showstatuslike‘slow_queries’②如何去定位慢查询默认情况下，10S是一个慢查询。这个值可以修改，我们现在修改一下其为0.5秒，showvariableslike‘long_query_time’可以显示当前慢查询的时间。setlong_query_time=0.5;可以修改慢查询的时间。构建大表：400万条记录。--存储过程构建。大表的记录要不同才有意义，否则会和真实的相差很大。然后我们建表，创建函数，创建存储过程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;whileindosetreturn_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));seti=i+1;endwhile;returnreturn_str;end$$createfunctionrand_num()returnsint(5)begindeclareiintdefault0;seti=floor(10+rand()*500);returni;end$$//创建一个存储过程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$$showstatuslike‘slow_queries’(此处慢查询不仅仅指查询语句，也包括执行dml语句)我们知道了慢查询的次数，但是我们不知道是那条，所以要启动日志记录功能：在默认情况下，mysql不会记录慢查询，需要在启动的时候指定慢查询才可以。bin\mysqld.exe--safe-mode--slow-query-log我们安全模式启动数据库：日志文件：默认这个文件放在：C:\ProgramData\MySQL\MySQLServer5.5\data在my.ini文件中有：等启动之后我们会发现在目录下多了一个日志文件：至此我们已经以安全模式(写日志的模式)启动了。查询：日志文件这样看来，这个日志里面记录了我们的东西，慢查询。优化问题：通过explain语句可以分析mysql如何执行你的sql语句。1.建立索引：索引的种类：主键索引，全文索引，唯一索引，普通索引添加索引：(1.1)主键索引添加：当把一张表的某一列设为主键的时候，该键就是主键索引了altertable表名addprimarykey（列名）；给emp表添加主键索引：之后我们会看见数据文件发生了大小上的变化：我们去查询，发现速度快了好多，超快。如果数据上了千亿，可能速度会慢下来。为什么添加索引会变块？？？？？？二叉树算法，索引文件。原理示意图:(1.2)普通索引添加：先创建表，然后创建。alterindex索引名on表(列)(1.3)创建全文索引：案例：fulltextCREATETABLEarticles(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*FROMarticlesWHEREMATCH(title,body)AGAINST('database');在mysql中fulltext索引只针对myisam生效这个索引只针对中文索引生效，--sphinx是中文索引技术。使用方法：match(字段名)against（关键字）;全文索引是一个叫做停止词，全文索引只对比较生僻的词语：如下：可以得出一点结论：(1.4)唯一索引：当某一列被指定为unique约束的时候，这列就是一个唯一索引。可以为空。null可以为多个但是，‘’不可以为多个(这个是空串的意思，要是有两个空串，那么他们是相同的，所以就违反了唯一的规则)。第一种方式，建表的时候直接unique第二种方式，在创建表后，再创建。createuniqueindex索引名on表名(列名)。2.查询索引：desc表名该方法可以看到索引，但让人郁闷的是不能显示索引的名称;showindex(ex)from表名\g(显示格式好看)：showkeysfrom表名\g3.删除索引：altertable表名dropindex索引名；(普通索引)altertable表名dropprimarykey;(主键索引)4.修改索引：先删除在重新创建。索引分析：①占用磁盘空间②dql快了，但是dml就会变慢(增删改会开销时间去维护索引文件)。目前算法是BTREE。权衡索引：建索引的要求：1.在where子句中经常使用，2.该字段的内容是有多个，3.变化不能太频繁。案例分析：使用：1.对于创建的多列索引，只要查询条件使用了最左边的，索引一般都会被使用到。如果我们：select*fromdeptwhereloc=’aaa’;那么它不一定会使用到索引;如果我们：select*fromdeptwheredname=’aaa’;2.like的注意事项：关键字的最前面，不能有%或者\,将放弃索引。如果一定要使用变化值得，则考虑使用全文索引。explain指令详解：在执行sql以前，我们可以通过这条指令了解sql的执行详情，这样有助于优化sql语句。3.条件中有or关键字的，是不会使用到索引的。换言之，要是or关键字所有字段都是索引，那么就会使用索引。or关键字的速度是相当的低，所以建议不要使用。4.如果列是字符串类型，则要用单引号引起来。5.如果全文扫描表比索引快的的话，它就会选择全文扫描，而放弃索引。查看索引的使用率：showstatuslike‘Handler_read%’;这个值越大，说明索引使用率越高。了解内容注意事项：sql语句优化小技巧：groupby语句：,分组查询，默认分分组后，还会排序，可能会使速度变慢，select*fromdeptgroupbydeptno\G很明显，此处的Extra说