《mysql优化技术学习笔记.doc》由会员分享，可在线阅读，更多相关《mysql优化技术学习笔记.doc（35页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、mysql 数据库优化技术：a. 表的设计合理(符合3NF);b. 添加适当的索引(index)：普通索引，主键索引，唯一索引，全文索引、(空间索引)；c. 分表技术(水平、垂直);d. 读写分离e. 存储过程。提高速度的原因：f. 对mysql配置优化(配置最大并发数，调整缓存大小)g. mysql服务硬件升级;h. 定时清除不需要的数据，定时进行碎片整理(myisam)。表的设计：3NF标准：范式是1-2-3，这样递增的。1NF：记录具有原子性，列的信息不可分割。只要数据库是关系型数据库，就自动满足1NF.数据库分类：关系数据库：主流数据库非关系数据库：面向对象，集合等NoSql数据库：面
2、向文档，速度快。2NF:表中的记录是唯一的，就满足了，通常我们用一个主键来实现主键：不含业务逻辑，自增长，3NF：表中不要有冗余数据，如果表的信息能被推倒出来，就不应该设计一个字段。实际中：没有冗余的数据不一定是最好的，我们在实际开发中可以反3NF设计一张表。案例分析：在表1对N的情况下，为了满足对速度的要求，可能会在1方设计一些字段，提高速率。sql优化：如何在一个大项目中定位慢查询语句。 了解mysql状态，学会如何去查询(mysql运行时间/一共执行了多少次dml/dql语句/show status 查询出了300多个状态show status like uptime查询启动时间show
3、 status like con_select查看执行了多少次查询，update/delete/insert以此类推特别说明：show seeion|global status like .session:会话状态，就是本次回话的状态global:表示从启动mysql服务开始一直以来的状态show status like connections查询当前的连接数显示目前慢查询的次数：show status like slow_queries 如何去定位慢查询默认情况下，10S是一个慢查询。这个值可以修改，我们现在修改一下其为0.5秒，show variables like long_query_
4、time可以显示当前慢查询的时间。set long_query_time=0.5;可以修改慢查询的时间。构建大表：400万条记录。-存储过程构建。大表的记录要不同才有意义，否则会和真实的相差很大。然后我们建表，创建函数，创建存储过程CREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/dname VARCHAR(20) NOT NULL DEFAULT , /*名称*/loc VARCHAR(13) NOT NULL DEFAULT /*地点*/) ENGINE=MyISAM DEFAULT
5、CHARSET=utf8 ;CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2) NOT NULL,/*薪水*/comm DECIMAL(7,2) NOT N
6、ULL,/*红利*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2) NOT NULL,hisal DECIMAL(17,2) NOT NULL)ENGINE=MyISAM DEFAULT CHARSET=utf8;测试数据INSERT INTO salgrade VALUES (1,700,12
7、00);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);为了存储过程能够正常执行，我们需要把命令执行结束符修改delimiter $create function rand_string(n INT) returns varchar(255) #该函数会返回一个字符串begin #chars_str定义一个变量
8、 chars_str,类型是 varchar(100),默认值abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ; declare i int default 0; while i sphinx是中文索引技术。使用方法：match(字段名) against（关键字）;全文索引
9、是一个叫做 停止词 ，全文索引只对比较生僻的词语：如下：可以得出一点结论：(1.4)唯一索引：当某一列被指定为unique约束的时候，这列就是一个唯一索引。可以为空。null可以为多个 但是 ，不可以为多个(这个是空串的意思，要是有两个空串，那么他们是相同的，所以就违反了唯一的规则)。第一种方式，建表的时候直接unique第二种方式，在创建表后，再创建。create unique index 索引名 on 表名 (列名)。2.查询索引：desc 表名 该方法可以看到索引，但让人郁闷的是不能显示索引的名称;show index(ex) from 表名 g(显示格式好看)： show keys f
10、rom 表名 g3.删除索引：alter table 表名 drop index 索引名；(普通索引)alter table 表名 drop primary key;(主键索引)4.修改索引：先删除在重新创建。索引分析：占用磁盘空间dql快了，但是dml就会变慢(增删改会开销时间去维护索引文件)。目前算法是BTREE。权衡索引：建索引的要求：1.在where子句中经常使用，2.该字段的内容是有多个，3.变化不能太频繁。案例分析：使用：1.对于创建的多列索引，只要查询条件使用了最左边的，索引一般都会被使用到。如果我们：select * from dept where loc=aaa;那么它不一定
11、会使用到索引;如果我们：select * from dept where dname=aaa;2. like的注意事项：关键字的最前面，不能有%或者,将放弃索引。如果一定要使用变化值得，则考虑使用全文索引。explain指令详解：在执行sql以前，我们可以通过这条指令了解sql的执行详情，这样有助于优化sql语句。3.条件中有or 关键字的，是不会使用到索引的。换言之，要是or关键字所有字段都是索引，那么就会使用索引。or关键字的速度是相当的低，所以建议不要使用。4.如果列是字符串类型，则要用单引号引起来。5.如果全文扫描表比索引快的的话，它就会选择全文扫描，而放弃索引。查看索引的使用率：sh
12、ow status like Handler_read%;这个值越大，说明索引使用率越高。了解内容 注意事项：sql语句优化小技巧： group by语句：,分组查询，默认分分组后，还会排序，可能会使速度变慢，select * from dept group by deptnoG很明显，此处的 Extra说明排序了，这样在大表中，时间就有开销，如果我们希望不要排序，那么应该：select * from dept group by dname order by nullG这样就减去了一定的时间开销，防止了排序。 尽量使用连接查询来代替子查询。joinselect * from dept,emp
13、where dept.deptno=emp.deptno换成：select * from dept left join emp on dept.deptno=emp.deptno.存储引擎：myisam存储：如果表对事物要求不是很高，同时是以查询和添加为主的，对安全新要求也不高，我们可以考虑myisam存储引擎。bbs发帖表、回复表。innodb存储：对事务要求高，数据文件重要，建议使用innodb。账户表，订单表。memory存储：数据变化频繁，不需要入库，同时又平凡的查询和修改，可以考虑使用memory引擎。他们之间的区别：简单的说：memory存储：速度快。但是当重启mysql服务的时候
14、，数据全部丢失，它的数据不入库。deciaml-float如果数据库的存储引擎是myisam，就要定时进行碎片整理。数据虽然删了，但它实际存在于数据库文件中，所以要进行碎片整理。指令：optimize table 表名;PHP定时完成数据库备份 手动备份数据库(表)方法：cmd控制台：mysqldump u rootproot 数据库名 文件路径例如：mysqldump u root-proot temp d:temp.bak当出现问题的时候，到mysql控制台：source d:temp.dept.bak 定时完成：定时器的使用。方案一：把备份数据库的指令，写入到bat文件中，然后通过任务管
15、理器去定时的调用bat文件原理图：mytesk.bak文件内容：D:Program FilesMyPHPEnvmysqlbinmysqldump -u root -proot temp dept d:temp.dept.bak测试ok，现在我们需要把其加到任务管理器，临时两点调用。见图解。现在的问题是：文件每次生成的回避覆盖掉。方案二：通过php程序去搞定。写成php程序，myteak.php/定时备份我们的数据库date_default_timezone_set(PRC);$backfilename=date(YmdHis,time();/echo $backfilename;$comman
16、d=D:Program FilesMyPHPEnvmysqlbinmysqldump -u root -proot temp dept d:.$backfilename.bak;/echo $command;exec($command);然后写一个bat文件内容：D:ProgramFilesMyPHPEnvphpphp.exeD:ProgramFilesMyPHPEnvapachehtdocsMysqlmytask.php这句话的意思就是，在控制台下用php.exe 程序去执行我们刚才写的mytask.php程序。这样，我们将mytask2.bat添加到windows任务计划里面的话，就会产生
17、不会不同文件名的备份文件(文件名是备份时间的年月日时分秒，这样也利于管理和查看)原理图全：使用php完成定时发送邮件的功能：建立表：maillistcreate table maillist(id int unsigned primary key auto_increment,getter varchar(64) not null default ,sender varchar(64) not null default ,title varchar(32) not null default ,content varchar(2048) not null default ,sendtime in
18、t unsigned not null default 0,flag tinyint unsigned not null default 0)engine=myisam charset utf8insert into maillist values(null,hello100,abc,hello,unix_timestamp()+10*3600,0);insert into maillist values(null,hello200,abc,hello200,unix_timestamp()+10*3600,0);先实现怎样去检索那些邮件该发送了：每隔一定时间去检索是否有邮件发送了。模拟发送：
19、mail()是PHP用来发送邮件的函数，PHPMailer类，可以直接使用。要正确使用PHPMailer发送邮件，需要满足如下条件：第一， 电脑是联网的。第二， 搭建自己的smtp邮件服务器示意图：get方：smtp/pop3:可以接受和发送邮件。send方：转发的原理：搭建自己的邮件服务器：设置-邮箱域名邮件服务器设置成功了，我们现在设置自己的账号，使用版本只能设置5个账号。，发送邮件的代码：?phprequire(./PHPMailer/class.phpmailer.php);$mailer=new PHPMailer();$cont=CharSet=utf-8;$mailer-Cont
20、entType=text/html;$mailer-Encoding=base64;$mailer-From=zhanghui10.135.160.134;$mailer-FromName=张辉;$mailer-Subject=张辉，你好;$mailer-Body=$cont;/设置语言包$mailer-SetLanguage(zh_cn);$mailer-AddAddress(,zhanghui);if($mailer-Send()echo 发送ok;elseecho fail;然后在php.ini文件中：加上自己的邮件域名之后重启apache然后发送，成功了，邮件也受到了。哈哈。开心了。表
21、的分割技术：当一个表的数据海量的时候，我们需要分割了。/传统法/使用内存缓存/分表技术水平原理图：核心就是找到分表的标准。功能1.添加用户功能2.验证用户建表，来测试分割技术：?php$conn=mysql_connect(localhost,root,root);if(!$conn)die(连接失败);mysql_select_db(temp);/获取uuid，作为qq号码$sql=insert into uuid values(null);if(mysql_query($sql,$conn)$uuid=mysql_insert_id();$tablename=qqlogin.$uuid%3;$sql=insert into .$tablename. values($uuid,aaa,aaa);if(mysql_query($sql,$conn)echo 添加用户成功;elsedie(添加失败);垂直分割：把某个表某些字段，这些字段在查询的时候并不是经常关心的，但是数据量又很大，建议把这些字段单独提取出来，放到另外一张表中，从而提高效率。读写分离：补充讲解增量备份：步骤：1配置my.ini文件重启mysql我们的目录下：有来着两个文件：可以使用mysqlbinlog来查看备份文件的路径：在mysql的bin目录下，或者加入环境变量后：详细解释：除了select，其他都有记录。