首页 > 数据库技术 > 详细

MySQL性能优化(一)

时间:2014-08-04 14:33:27      阅读:533      评论:0      收藏:0      [点我收藏+]
<pre name="code" class="sql">性能优化一共有6个点:
    1.MySQL基础操作
    2.常用的SQL技巧
    3.SQL语句优化
    4.MySQL数据库优化
    5.Myisam表锁
    6.MySQL服务器优化

后期将逐步补全今天先分享下1
1.MySQL基础操作
	一:MySQL基础操作
		1:MySQL表复制
			复制表结构 + 复制表数据
			create table t3 like t1;  --创建一个和t1一样的表,用like(表结构也一样)
			insert into t3 select * from t1;  --t1的数据全部拿过来,注意是表结构一致才select* ,否则选择相应的的字段列插入
			
			create table t1(
				id int unsigned not null auto_increment primary key,
				name varchar(30)
			);
		2:MySQL索引(create不能创建主键索引,得用alter,建议全部用alter创建索引)
			*设置主键后默认就是主键索引
			一:alter table用来创建普通索引,unique索引或primary key索引
				普通索引:alter table t1 add index in_name(name)   --t1表中的那么字段添加索引名为in_name
				唯一索引:alter table t1 add unique(name)   --不给名字,默认是字段名
						  alter table t1 add unique un_name(name)
						  
				主键索引(自增才有意义):alter table t1 add primary key(id)
				         主键索引不是自增记得改成自增:alter table t1 modify id int unsigned not null auto_increment;
				
				查看:show index from t1;
				删除:alter table t1 drop index in_name;
				
				@*删除主键索引单独处理*
			二:alter table table_name drop index index_name
					alter型删除索引:alter table t1 drop index in_name;
					
					@*删除主键索引*:
					注意:删除主键索引的时候,如果你的主键索引是自增(如:id)删除是会报错的
						  实在想玩的话就把主键的auto_increment消掉
						  alter table t1 modify id int unsigned not null;
						  接着删除主键:alter table t1 drop primary key;
						  
						  改成自增:alter table t1 modify id int unsigned not null auto_increment;
				
			三:create index(不常用学习下,不能对主键索引操作,只能操作普通和唯一索引)
			   *创建普通索引:	create index in_name on t1(name);  --将t1表中的那么字段添加为普通索引
				查看索引:     	show index from t1;
				删除索引:      drop index in_name on t1;    --t1表中的in_name索引删除
			  
			  ->在做唯一索引之前字段不能有重复值,否则创建不成功
			   *创建唯一索引:  create unique index un_name on t1(name);
			
			四:删除create创建的索引:drop index
					drop index in_name on t1;
					
			
		3:MySQL视图
			定义:视图是一个虚拟表,其内容由查询定义,是根据建立视图的sql语句拿到的数据保存在一张表中而创建的表-视图
				  *根据从表里面拿出来的数据而创建出来的一张表
			创建视图:create view v_t1 as select * from t1 where id>4 and id<11;
					  
			作用:
				  如果 t1表里的数据某条记录被删除了,那么视图v_t1表的对应数据也会删除,类似主从(主表无则从无)
				  所以:视图也可以充当一个中间表:查数据的时候可以不去查主t1 去查视图表v_t1
				  *视图表示依赖于,创建时sql的表t_name,如果表t_name损坏的了(删除了),对应的视图将会发生错误不能使用 
			
			查看视图:show tables;
			删除视图:drop view v_t1;
			视图帮助信息:?view;
			
			
		4:MySQL内置函数
			字符串函数:
			select	concat("hello","word");    	   		 链接字串 ->hello world
					lcase("MYSQL")				   		 转换成小写
					ucase("mysql")				   		 转换成大写
					length("leyangjun")         		 string长度
					ltrim("   userName")          		 去除前端空格
					rtrim("userName   ")          		 去除后端空格
					repeat("linux",count)   		   	 重复count次( select repeat('d',2);重复输出2次dd)
					replace(str,search_str,replace_str)  在str中使用replace_str替换search_str
					substring(str,position[length])      从str的position开始,取length个字符串->substring 和 substr一样
					select substr("leyangjun",1,5);      从第一个开始取5个字符串
					space(count)           				 生成count(数字)个空格
			数学函数
				bin(decimal_number)         十进制转二进制(select bin(120);)
				ceiling(number2)            向上取整(select ceiling(10.10);--->11)
				floor(number2)            向下取整(select ceiling(10.10);--->10)
				Max(列)              取最大值
				MIN(列)              取最小值
				sqrt(number2)               开平方
				rand()                      返回0-1内的随机值
		
			日期函数:
				curdate();       返回当前日期
				curtime();       返回当前时间
				now();           返回当前的日期和时间
				unix_timestamp(date)   返回date的unix时间戳
				from_unixtime()        返回unix时间戳日期值
				week(date)             返回日期date为一年中的第几周
				year(date)             返回日期中的年份
				datediff(expr,expr2)   返回起始时间expr和结束时间expr2间隔天数select datediff("2014-08-03","2014-08-04");
		
		5:MySQL预处理语句
			一:设置一个预处理语句:prepare stmt1 from 'select * from t1 where id>?';
			二:设置一个变量:set @=i1;
			三:执行stmt1预处理:execute stmt1 using @i;
			
				设置@i=5
				set @i=5;
				execute stmt1 using @i;
			
			删除预处理:
				drop prepare stmt1;
			
			应用场景:比如你是老板我要看1,2,3,4,5,6、、、、12月份入职人员的情况
					  *就可以把SQL做成预处理,这样就不需要每次去请求MySQL直接传个值就可以(MySQL是将预处理的SQL储存起来,用的时候传值直接就执行,就不需要每次请求连接MySQL在重新执行)
			
		6:MySQL事务处理(增删改查后只要没有commit,全可以回滚)
		    *myisam引擎不支持事务,innodb(支持外键和事务)才支持事务
			修改表引擎方法:alter table t1 engine=innodb
			
			一:事务操作
				查看是否自动提交:select @@autocommit;	
				关闭自动提交
				set autocommit=0;
				delete from t1 where id>5;  数据只是临时删除,如果commit就真正的执行删除语句
				rollback;     只要没commit还原刚才删除的数据
				commit;
			
			二:还原点的使用:
				insert into t1 values("user4");
				savepoint p1;
				insert into t1 values("user5");
				savepoint p2;
				insert into t1 values("user6");
				savepoint p3;
				
				--3个数据已经插进去啦,能后你觉得user6不要,你就找到还原点 savepoint p2就行
				rollback to p2;    --还原到P2  user6不要
				commit;
				
				
			
		7:MySQL存储(可以认为是自定义的函数)
			创建一个存储:
				\d //
				create procedure p1()
				begin
				set @i=0;
				while @i<10 do
				insert into t2(name) values(concat("user",@i));        --这里可以做增删改查。。。都行
				set @i=@i+1;
				end while;
				end;
				//
			执行一个存储:
				\d ;
				call p1();
			查看存储:
				show procedure status;
				show create procedure p1\G   --查看p1存储的基本信息
		
		8:MySQL触发器(自动执行)
			*查询没有必要做触发器!:select * from t1 union select * from t2;
			一:增加触发器
				\d //
				创建一个名字为tg1的触发器,当向表中插入数据时,就向t2表中插入一条数据
				create trigger tg1 before insert on t1 for each row
				begin
				insert into t2(id) values(new.id);    --new.id 比如向t1表里面插入了id=4的    能后new.id=4 直接复制到这
				end//
				
				准备好t1 表 和 t1表
				向t1表中插入多条数据
			
			查看:show triggers;
			删除触发器:drop trigger t2;
			
			二:删除触发器(注意下:删除的时候2个表的值一定要对称,比如t1:1,2,3 t2:11,12,13这么删除是不行会报错,以一定要对称
							比如t1和t2表都有user1,这样删除就是没问题 )
				\d //
				create trigger tg2 before delete on t1 for each row
				begin delete from t2 where id=old.id;   --插入的时候里面没有这个值叫new.id  提前有的值叫old.id
				end//
				
			三:更改触发器:
				create trigger tg3 before update on t1 for each row
				begin update t2 set id=new.id where old.id;  --(update t1 set name="leyangjun"-new.id where name="zhangfei"-old.id)
				end//
			
		9:重排auto_increment值
			MySQL数据库自动增长的ID如何恢复:
				清空表的时候,不能用delete from tableName;
							  而是用truncate tableName;
				这样auto_increment就恢复成1了
				
				或者清空内容后直接用alter命令修改表:alter table tableName auto_increment=1;
				
				场景:
					1:t1表里面有id字段分别对应 1,2,3,4,5,6,7,8,9的记录,
				    2:能后我们delete删除,能后在插入数据,你会发现插入的时候是从10,11,12,13.....开始而不是1
					
					3:清空的时候我们执行下这个归档为1即可:
						alter table tableName auto_increment=1;
					4:能后在插入的时候就是从1开始的啦


MySQL性能优化(一),布布扣,bubuko.com

MySQL性能优化(一)

原文:http://blog.csdn.net/leyangjun/article/details/38366811

(0)
(0)
   
举报
评论 一句话评论(0
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!