MySQL性能优化（1）_mysql

mysql性能优化（一）

<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性能优化（1）_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)