mysql优化技术学习笔记.doc

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语句/show status 查询出了300多个状态show status like uptime查询启动时间show 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_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 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 NULL,/*红利*/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,1200);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定义一个变量 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 < n do set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1); set i = i + 1; end while; return return_str; end $create function rand_num()returns int(5)begindeclare i int default 0;set i = floor(10+rand()*500);return i;end $/创建一个存储过程create procedure insert_emp(in start int(10),in max_num int(10)begindeclare i int default 0; #set autocommit =0 把autocommit设置成0 set autocommit = 0; repeat set i = i + 1; insert into emp values (start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num(); until i = max_num end repeat; commit; end $show status like slow_queries(此处慢查询不仅仅指查询语句，也包括执行dml语句)我们知道了慢查询的次数，但是我们不知道是那条，所以要启动日志记录功能：在默认情况下，mysql不会记录慢查询，需要在启动的时候指定慢查询才可以。binmysqld.exe - -safe-mode - -slow-query-log我们安全模式启动数据库：日志文件：默认这个文件放在：C:ProgramDataMySQLMySQL Server 5.5data在my.ini文件中有：等启动之后我们会发现在目录下多了一个日志文件：至此我们已经以安全模式(写日志的模式)启动了。查询：日志文件这样看来，这个日志里面记录了我们的东西，慢查询。优化问题：通过explain语句可以分析mysql如何执行你的sql语句。1. 建立索引：索引的种类：主键索引，全文索引，唯一索引，普通索引添加索引：(1.1)主键索引添加：当把一张表的某一列设为主键的时候，该键就是主键索引了alter table 表名 add primary key （列名）；给emp表添加主键索引：之后我们会看见数据文件发生了大小上的变化：我们去查询，发现速度快了好多，超快。如果数据上了千亿，可能速度会慢下来。为什么添加索引会变块？二叉树算法，索引文件。原理示意图:(1.2)普通索引添加：先创建表，然后创建。alter index 索引名 on 表 (列)(1.3)创建全文索引：案例：fulltext CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engine=myisam charset utf8;INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase .'), ('How To Use MySQL Well','After you went through a .'), ('Optimizing MySQL','In this tutorial we will show .'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. .'), ('MySQL vs. YourSQL','In the following database comparison .'), ('MySQL Security','When configured properly, MySQL .');如何使用呢：用法：SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');在mysql中fulltext索引只针对myisam生效这个索引只针对中文索引生效，->sphinx是中文索引技术。使用方法：match(字段名) against（关键字）;全文索引是一个叫做 停止词 ，全文索引只对比较生僻的词语：如下：可以得出一点结论：(1.4)唯一索引：当某一列被指定为unique约束的时候，这列就是一个唯一索引。可以为空。null可以为多个 但是 ，不可以为多个(这个是空串的意思，要是有两个空串，那么他们是相同的，所以就违反了唯一的规则)。第一种方式，建表的时候直接unique第二种方式，在创建表后，再创建。create unique index 索引名 on 表名 (列名)。2.查询索引：desc 表名 该方法可以看到索引，但让人郁闷的是不能显示索引的名称;show index(ex) from 表名 g(显示格式好看)： show keys from 表名 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;那么它不一定会使用到索引;如果我们：select * from dept where dname=aaa;2. like的注意事项：关键字的最前面，不能有%或者,将放弃索引。如果一定要使用变化值得，则考虑使用全文索引。explain指令详解：在执行sql以前，我们可以通过这条指令了解sql的执行详情，这样有助于优化sql语句。3.条件中有or 关键字的，是不会使用到索引的。换言之，要是or关键字所有字段都是索引，那么就会使用索引。or关键字的速度是相当的低，所以建议不要使用。4.如果列是字符串类型，则要用单引号引起来。5.如果全文扫描表比索引快的的话，它就会选择全文扫描，而放弃索引。查看索引的使用率：show 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 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服务的时候，数据全部丢失，它的数据不入库。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文件中，然后通过任务管理器去定时的调用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;$command='"D:Program FilesMyPHPEnvmysqlbinmysqldump" -u root -proot temp dept >d:'.$backfilename.'.bak'/echo $command;exec($command);然后写一个bat文件内容："D:ProgramFilesMyPHPEnvphpphp.exe""D:ProgramFilesMyPHPEnvapachehtdocsMysqlmytask.php"这句话的意思就是，在控制台下用php.exe 程序去执行我们刚才写的mytask.php程序。这样，我们将mytask2.bat添加到windows任务计划里面的话，就会产生不会不同文件名的备份文件(文件名是备份时间的年月日时分秒，这样也利于管理和查看)原理图全：使用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 int 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);先实现怎样去检索那些邮件该发送了：每隔一定时间去检索是否有邮件发送了。模拟发送：mail()是PHP用来发送邮件的函数，PHPMailer类，可以直接使用。要正确使用PHPMailer发送邮件，需要满足如下条件：第一， 电脑是联网的。第二， 搭建自己的smtp邮件服务器示意图：get方：smtp/pop3:可以接受和发送邮件。send方：转发的原理：搭建自己的邮件服务器：设置->邮箱域名邮件服务器设置成功了，我们现在设置自己的账号，使用版本只能设置5个账号。，发送邮件的代码：<?phprequire('./PHPMailer/class.phpmailer.php');$mailer=new PHPMailer();$cont=<<<EMAIL hello.world jadj!;EMAIL;/设置一些属性$mailer->CharSet='utf-8'$mailer->ContentType='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然后发送，成功了，邮件也受到了。哈哈。开心了。表的分割技术：当一个表的数据海量的时候，我们需要分割了。/传统法/使用内存缓存/分表技术水平原理图：核心就是找到分表的标准。功能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，其他都有记录。