setreturn_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
seti=i+1;
endwhile;
returnreturn_str;
end$$
如果希望在程序中使用,是Ok!
创建一个存储过程
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$$
#调用刚刚写好的函数,1800000条记录,从100001号开始
callinsert_emp(100001,4000000);
3这时我们如果出现一条语句执行时间超过1秒中,就会统计到.
4如果把慢查询的sql记录到我们的一个日志中
在默认情况下,我们的mysql不会记录慢查询,需要在启动mysql时候,指定记录慢查询才可以
bin\mysqld.exe--safe-mode--slow-query-log[mysql5.5可以在my.ini指定](安全模式启动,数据库将操作写入日志,以备恢复)
bin\mysqld.exe–log-slow-queries=d:
/abc.log[低版本mysql5.0可以在my.ini指定]
先关闭mysql,再启动,如果启用了慢查询日志,默认把这个文件放在
my.ini文件中记录的位置
#Pathtothedatabaseroot
datadir="C:
/DocumentsandSettings/AllUsers/ApplicationData/MySQL/MySQLServer5.5/Data/"
5测试,可以看到在日志中就记录下我们的mysql慢sql语句.
优化问题.
通过explain语句可以分析,mysql如何执行你的sql语句,这个工具的使用放一下,一会说.
添加索引【小建议:
】
◆四种索引(主键索引/唯一索引/全文索引/普通索引)
1.添加
1.1主键索引添加
当一张表,把某个列设为主键的时候,则该列就是主键索引
createtableaaa
(idintunsignedprimarykeyauto_increment,
namevarchar(32)notnulldefaul‘’);
这是id列就是主键索引.
如果你创建表时,没有指定主键索引,也可以在创建表后,在添加,指令:
altertable表名addprimarykey(列名);
举例:
createtablebbb(idint,namevarchar(32)notnulldefault‘’);
altertablebbbaddprimarykey(id);
1.2普通索引
一般来说,普通索引的创建,是先创建表,然后在创建普通索引
比如:
createtableccc(
idintunsigned,
namevarchar(32)
)
createindex索引名on表(列1,列名2);
1.3创建全文索引
全文索引,主要是针对文件,文本的检索,比如文章,全文索引针对MyISAM有用.
创建:
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*fromarticleswherebodylike‘%mysql%’;【不会使用到全文索引】
mysql>explainselect*fromarticleswherebodylike'mysql%'\G
***************************1.row***************************
id:
1
select_type:
SIMPLE
table:
articles
type:
ALL
possible_keys:
NULL
key:
NULL
key_len:
NULL
ref:
NULL
rows:
6
Extra:
Usingwhere
1rowinset(0.00sec)
证明:
explainselect*fromarticleswherebodylike‘%mysql%’
正确的用法是:
select*fromarticleswherematch(title,body)against(‘database’);【可以】
mysql>explainselect*fromarticleswherematch(title,body)against('mysql')\G
***************************1.row***************************
id:
1
select_type:
SIMPLE
table:
articles
type:
fulltext
possible_keys:
title
key:
title
key_len:
0
ref:
NULL
rows:
1
Extra:
Usingwhere
1rowinset(0.00sec)
☞说明:
1.在mysql中fulltext索引只针对myisam生效
2.mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文
3.使用方法是match(字段名..)against(‘关键字’)
4.全文索引:
停止词,因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.比如(a,b,mysql,the)
mysql>selectmatch(title,body)against('database')fromarticles;(输出的是每行和database的匹配度)
+----------------------------------------+
|match(title,body)against('database')|
+----------------------------------------+
|0.6554583311080933|
|0|
|0|
|0|
|0.6626645922660828|
|0|
+----------------------------------------+
6rowsinset(0.01sec)
1.4唯一索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
createtableddd(idintprimarykeyauto_increment,namevarchar(32)unique);
这时,name列就是一个唯一索引.
unique字段可以为NULL,并可以有多NULL,但是如果是具体内容,则不能重复,
但是不能存有重复的空字符串’’
.
主键字段,不能为NULL,也不能重复.
②在创建表后,再去创建唯一索引
createtableeee(idintprimarykeyauto_increment,namevarchar(32));
createuniqueindex索引名on表名(列表..);
2.查询索引
desc表名【该方法的缺点是:
不能够显示索引名.】
showindex(es)from表名
showkeysfrom表名
3.删除
altertable表名dropindex索引名;
如果删除主键索引。
altertable表名dropprimarykey[这里有一个小问题]
4.修改
先删除,再重新创建.
◆为什么创建索引后,速度就会变快?
原理示意图:
.
◆索引使用的注意事项
索引的代价:
1.占用磁盘空间
2.对dml操作有影响,变慢
◆在哪些列上适合添加索引?
总结:
满足以下条件的字段,才应该创建索引.
a:
肯定在where条件经常使用b:
该字段的内容不是唯一的几个值(sex)c:
字段内容不是频繁变化.
◆使用索引的注意事项
把dept表中,我增加几个部门:
altertabledeptaddindexmy_ind(dname,loc);//dname左边的列,loc就是右边的列
说明,如果我们的表中有复合索引(索引作用在多列上),此时我们注意:
1,对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
explainselect*fromdeptwhereloc='aaa'\G
就不会使用到索引
2,对于使用like的查询,查询如果是‘%aaa’不会使用到索引
‘aaa%’会使用到索引。
比如:
explainselect*fromdeptwherednamelike'%aaa'\G
不能使用索引,即,在like查询时,关键的‘关键字’,最前面,不能使用%或者_这样的字符.,如果一定要前面有变化的值,则考虑使用全文索引->sphinx.
3.如果条件中有or,即使其中有条件带索引也不会使用。
换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or关键字
select*fromdeptwheredname=’xxx’orloc=’xx’ordeptno=45
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。
否则不使用索引。
(添加时,字符串必须’’),也就是,如果列是字符串类型,就一定要用‘’把他包括起来.
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
explain可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令.
◆如何查看索引使用的情况:
showstatuslike‘Handler_read%’;
大家可以注意:
handler_read_key:
这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:
这个值越高,说明查询低效。
◆sql语句的小技巧
1.在使用groupby分组查询是,默认分组后,还会排序,可能会降低速度.
比如:
在groupby后面增加orderbynull就可以防止排序.
2.有些情况下,可以使用连接来替代子查询。
因为使用join,MySQL不需要在内存中创建临时表。
select*fromdept,empwheredept.deptno=emp.deptno;[简单处理方式]
select*fromdeptleftjoinempondept.deptno=emp.deptno;[左外连接,更ok!
]
◆如何选择mysql的存储引擎
在开发中,我们经常使用的存储引擎myisam/innodb/memory
myisam存储:
如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎.,比如bbs中的发帖表,回复表.
INNODB存储:
对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
问MyISAM和INNODB的区别
1.事务安全(MyISAM不支持事务和外键,INNODB支持事务和外键)
2.查询和添加速度(MyISAM批量插入速度快)
3.支持全文索引(MyISAM支持全文索引,INNODB不支持全文索引)
4.锁机制(MyISAM是表锁,innodb是行锁)
5.外键MyISAM不支持外键,INNODB支持外键.(在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致)
Memory存储,比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用memory,速度极快.(如果mysql重启的话,数据就不存在了)
◆如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理
举例说明:
createtabletest100(idintunsigned,namevarchar(32))engine=myisam;
insertintotest100values(1,’aaaaa’);
insertintotest100values(2,’bbbb’);
insertintotest100values(3,’ccccc’);
我们应该定义对myisam进行整理
optimizetabletest100;
mysql_query(“optimizetables$表名”);
技术就是窗户纸.->经常和技术好的人交流
◆PHP定时完成数据库的备份
1手动备份数据库(表的)方法
cmd控制台:
mysqldump–uroot–proot数据库[表名1表名2..]>