MySQL集锦4-优化

1、步骤
通过慢查询日志，查到sql语句
通过解析SQL语句查看影响行数
如果影响行数特别多，判断是否需要加索引

mysql ?使用
? contents

2、show [global] status;
engine=myisa

show status like 'com_insert%';
show status like 'com_update%';
show status like 'com_delete%';
show status like 'com_select%';

create table t1( id int not null auto_increment primary key, name varchar(30)) engine=myisam charset=utf8;
create table t2 like t1;

insert into t1(name) values('user1'),('user2'),('user3');
show status like 'com_insert%';
insert into t1(name) values('user4'),('user5'),('user6');
show status like 'com_insert%';

engine=innodb

show status like 'innodb_rows_inserted%';
show status like 'innodb_rows_updated%';
show status like 'innodb_rows_deleted%';
show status like 'innodb_rows_read%';
create table t3( id int not null auto_increment primary key, name varchar(30)) engine=myisam charset=utf8;

其他

connections 连接mysql的数量
uptime 服务器已经工作的秒数
slow_queries;慢查询的次数
show variables like '%slow%';
#log_slow_queries    | OFF 

3、定位执行效率较低的SQL语句
通过慢查询日志查

explain select * from table where id=1000;
desc select * from table where id=1000;

[img]http://dl2.iteye.com/upload/attachment/0101/2813/c86b2bc4-8a8f-39cf-902c-5b30ff09fa40.jpg[/img]

[img]http://dl2.iteye.com/upload/attachment/0101/2828/1ad90162-0188-33ec-a237-3adecbd3d500.jpg[/img]

4、索引优化
select * from t1 where t1.name like '%3';
select * from t1 where t1.name like 'user%';
%放在前面的时候索引可能会用不上

[img]http://dl2.iteye.com/upload/attachment/0101/2832/81cd3ae4-357c-3619-ac6c-4ab77594f2f5.jpg[/img]

desc select * from t1 where name is null\G
可以用到索引

以下2条sql语句，如果想用到索引，id、name两个字段必须同时拥有索引，否则索引无效
select * from t1 where id=1 and name='user1';
select * from t1 where id=1 or name='user7';

数据结构不匹配的时候不会使用索引
select * from t1 where name=1;

5、优化表
a、检查一个或多个表是否正确
check table t1,t2;
b、优化表
optimize 可以将表中的空间碎片进行合并

6、常用的SQL优化
a、大批量插入数据
如果当前备份的数据库数据量很大，
usr/bin/mysqldump -uroot -p1 test -l -F > /tmp/test.sql
car /tmp/test.sql |more
会保存大量的表结构、数据插入等语句
====================优化=======================================================
使用infile、outfile
/var/run/mysqld/mysqld.sock
导出t1的name列到文件/tmp/test.txt
select name from t1 into outfile '/tmp/test.txt';
导入
load data infile '/tmp/test.txt' into table t1(name);