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,其他都有记录。