mysql优化技术学习笔记.docx
- 文档编号:11569028
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:32
- 大小:3.25MB
mysql优化技术学习笔记.docx
《mysql优化技术学习笔记.docx》由会员分享,可在线阅读,更多相关《mysql优化技术学习笔记.docx(32页珍藏版)》请在冰豆网上搜索。
mysql优化技术学习笔记
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优化:
如何在一个大项目中定位慢查询语句。
1了解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’
2如何去定位慢查询
默认情况下,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;
whilei setreturn_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1)); seti=i+1; endwhile; returnreturn_str; end$$ createfunctionrand_num() returnsint(5) begin declareiintdefault0; seti=floor(10+rand()*500); returni; end$$ //创建一个存储过程 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$$ 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)创建全文索引: 案例: fulltext 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*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表名\g 3.删除索引: 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说明排序了,这样在大表中,时间就有开销,如果我们希望不要排序,那么应该: select*fromdeptgroupbydnameorderbynull\G 这样就减去了一定的时间开销,防止了排序。 Ø尽量使用连接查询来代替子查询。 join select*fromdept,empwheredept.deptno=emp.deptno 换成: select*fromdeptleftjoinempondept.deptno=emp.deptno. 存储引擎: myisam存储: 如果表对事物要求不是很高,同时是以查询和添加为主的,对安全新要求也不高,我们可以考虑myisam存储引擎。 bbs发帖表、回复表。 innodb存储: 对事务要求高,数据文件重要,建议使用innodb。 账户表,订单表。 memory存储: 数据变化频繁,不需要入库,同时又平凡的查询和修改,可以考虑使用memory引擎。 他们之间的区别: 简单的说: memory存储: 速度快。 但是当重启mysql服务的时候,数据全部丢失,它的数据不入库。 deciaml->float 如果数据库的存储引擎是myisam,就要定时进行碎片整理。 数据虽然删了,但它实际存在于数据库文件中,所以要进行碎片整理。 指令: optimizetable表名; PHP定时完成数据库备份 1手动备份数据库(表)方法: cmd控制台: mysqldump–uroot–proot数据库名>文件路径 例如: mysqldump–uroot-proottemp>d: \temp.bak 当出现问题的时候,到mysql控制台: sourced: \temp.dept.bak 2定时完成: 定时器的使用。 方案一: 把备份数据库的指令,写入到bat文件中,然后通过任务管理器去定时的调用bat文件 原理图: mytesk.bak文件内容: "D: \ProgramFiles\MyPHPEnv\mysql\bin\mysqldump"-uroot-proottempdept>d: \temp.dept.bak 测试ok, 现在我们需要把其加到任务管理器,临时两点调用。 见图解。 现在的问题是: 文件每次生成的回避覆盖掉。 方案二: 通过php程序去搞定。 写成php程序,myteak.php //定时备份我们的数据库 date_default_timezone_set('PRC'); $backfilename=date("YmdHis",time()); //echo$backfilename; $command='"D: \ProgramFiles\MyPHPEnv\mysql\bin\mysqldump"-uroot-proottempdept>d: \\'.$backfilename.'.bak'; //echo$command; exec($command); 然后写一个bat文件 内容: "D: \ProgramFiles\MyPHPEnv\php\php.exe" "D: \ProgramFiles\MyPHPEnv\apache\htdocs\Mysql\mytask.php" 这句话的意思就是,在控制台下用php.exe程序去执行我们刚才写的mytask.php程序。 这样,我们将mytask2.bat添加到windows任务计划里面的话,就会产生不会不同文件名的备份文件(文件名是备份时间的年月日时分秒,这样也利于管理和查看) 原理图全: 使用php完成定时发送邮件的功能: 建立表: maillist createtablemaillist (idintunsignedprimarykeyauto_increment, gettervarchar(64)notnulldefault'', sendervarchar(64)notnulldefault'', titlevarchar(32)notnulldefault'', contentvarchar(2048)notnulldefault'', sendtimeintunsignednotnulldefault0, flagtinyintunsignednotnulldefault0)engine=myisamcharsetutf8 insertintomaillistvalues(null,'1024577735@','2411790494@','hello100','abc,hello',unix_timestamp()+10*3600,0); insertintomaillistvalues(null,'24117904941024577738@','1024577735@','hello200','abc,hello200',unix_timestamp()+10*3600,0); 先实现怎样去检索那些邮件该发送了: 每隔一定时间去检索是否有邮件发送了。 模拟发送: mail()是PHP用来发送邮件的函数,PHPMailer类,可以直接使用。 要正确使用PHPMailer发送邮件,需要满足如下条件: 第一,电脑是联网的。 第二,搭建自己的smtp邮件服务器 示意图: get方: smtp/pop3: 可以接受和发送邮件。 send方: 转发的原理: 搭建自己的邮件服务器: 设置->邮箱域名 邮件服务器设置成功了,我们现在设置自己的账号,使用版本只能设置5个账号。 , 发送邮件的代码: php require('./PHPMailer/class.phpmailer.php'); $mailer=newPHPMailer(); $cont=<< ; EMAIL; //设置一些属性 $mailer->CharSet='utf-8'; $mailer->ContentType='text/html'; $mailer->Encoding='base64'; $mailer->From='zhanghui@10.135.160.134'; $mailer->FromName='张辉'; $mailer->Subject='张辉,你好'; $mailer->Body=$cont; //设置语言包 $mailer->SetLanguage('zh_cn'); $mailer->AddAddress('2411790494@','zhanghui'); if($mailer->Send()){ echo'发送ok'; }else{ echo'fail'; } 然后在php.ini文件中: 加上自己的邮件域名 之后重启apache 然后发送,成功了,邮件也受到了。 哈哈。 开心了。 表的分割技术: 当一个表的数据海量的时候,我们需要分割了。 //传统法 //使用内存缓存 //分表技术 水平原理图: 核心就是找到分表的标准。 功能1.添加用户 功能2.验证用户 建表,来测试分割技术: php $conn=mysql_connect("localhost","root","root"); if(! $conn){ die("连接失败"); } mysql_select_db('temp'); //获取uuid,,作为qq号码 $sql="insertintouuidvalues(null)"; if(mysql_query($sql,$conn)){ $uuid=mysql_insert_id(); $tablename='qqlogin'.$uuid%3; $sql='insertinto'.$tablename."values('$uuid','aaa','aaa')"; if(mysql_query($sql,$conn)){ echo'添加用户成功'; } }else{ die("添加失败"); } 垂直分割: 把某个表某些字段,这些字段在查询的时候并不是经常关心的,但是数据量又很大,建议把这些字段单独提取出来,放到另外一张表中,从而提高效率。 读写分离: 补充讲解增量备份: 步骤: 1配置my.ini文件 重启mysql 我们的目录下: 有来着两个文件: 可以使用mysqlbinlog来查看备份文件的路径: 在mysql的bin目录下,或者加入环境变量后: 详细解释: 除了select,其他都有记录。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 优化 技术 学习 笔记