18 SQL优化--其他SQL的优化

定期分析表,检查表,优化表:

 Analyze [local | no_write_to_binlog] table table_name [,table_name1] ;本语句用于分析和存储表的关键字分布，执行一次分析表，在分析期间使用一个读取锁定。这对于MyISAM,BDB和InnoDB表有作用，对于MyiSAM本语句相当于myisamchk -a .

分析表：mysql> analyze table xz_article;

 

 

检查表的语法  check  table table_name [,table_name1] [option] .....  Option={quick | fast | medium | extended | changed }

检查表主要是检查一个或多个表是否有错误。Check table 对Myisam和InnoDB表有作用。

检查表： mysql> check table xz_article;

 

 

优化表语法 optimize [local | no_write_to_binlog] table table_name [,table_name1] .. ;

如果已经删除了表的一大部分，或者对可变列(varchar,blob或text)进行了大修改，则需要使用optimize优化，这个命令可以将表中的空间碎片进行合并，并且可以消除由删除或更新造成的空间浪费。但optimize命令只对myisam,bdb,innodb表有用。

注：analyze,check,optimize在执行期间会自动将表锁定，因此一定要在数据库不繁忙的时候执行操作。

 

 

 

 

 

常用的SQL的优化

前面记录的都是与查询有关的优化，现在主要记录其他一些insert,group by等sql语句的优化。

 

1.大批量插入数据

使用load命令导入数据 

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db.my_table;

Disable keys 和 Enable keys用来打开和关闭Myisam表非唯一索引的更新，在导入大量数据到一个非空的Myisam表时，通过这2个命令可以提高导入效率。

正确的导入方法是(只对Myisam有效):  

1.先关闭keys: alter table  my_table disable keys ;

2.导入数据: LOAD DATA INFILE 'data.txt' INTO TABLE db.my_table;

3.开启 keys: alter table my_table enable keys ; 

 

对于InnodB导入大量数据时，因为innodb类型是按主键顺序保存的，所以有以下几种方法提高效率：

1.当导入的文件数据按主键顺序存储的时候比不按主键顺序存储的效率高。

2.在导入前执行set unique_checks = 0 关闭唯一性校验，导入结束后执行 set unique_checks = 1 恢复唯一校验。

3.在导入前可以执行 set autocommit = 0 关闭自动提交，导入结束后开启set autocommit = 1 。

 

 

优化insert语句

 

优化Group By语句

因为使用group by 分组后会自动进行排序，所以在使用order by 也就没什么意义了，如果不需要排序造成的消耗，可以使用order by null 解决。如： explain select * from table group by col1 order by null\G;

结论是如果不加order by null,extra则是Using filesoft，加了则是Using temporary。(filesoft文件排序是最耗费时间的)

 

优化Order By 语句

有以下几种情况不能使用索引：

1.select * from table order by col1 desc,col2 asc ;

Order by 字段混合了desc和asc

   2.select * from table where col1 = constant order by col2;

用于查询的关键字和order by中使用的不同

3.select * from table order by col1,col2 ;

对不同的关键字使用order by

 

 

优化嵌套查询

一般使用join来代替子查询

 

Mysql优化Or条件

对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件必须都要使用到索引。

 

 

使用SQL提示

Use Index:在查询语句表面后面添加Use index来提供希望Mysql去参考的索引列表，就可以让mysql不再考虑其他可用的索引。

如：explain select * from table use index (ind_email) where email = '[e-mail]' \G ;

 

 

Ignore Index:在查询的时候可以让Mysql忽略索引，用法同Use index。

如：explain select * from table ignore index(ind_email) where email = '[e-mail]' \G;

 

 

Force Index:在查询时，可以强制Mysql使用某个索引进行扫描。

如:explain select * from table where id > 0 \G ;因为条件是 id > 0 ，所以不会执行索引，但是我们可以用force index强制加上索引，用法如上。