三、sql语句的优化

3.1 优化sql语句的一般步骤

explain 或是desc （解析sql 语句）查看sql语句执行情况

慢查询日志

通过慢查询日志找出select语句（查找是不是需要加索引和影响行数） 之后在配和explain 或是desc

先看影响行数，然后看是否需要加索引，以及索引是否需要用得上？

bin_log 日志和慢查询日志一定要开启。

1.1 通过show status 命令了解各种sql的执行频率

格式：mysql>show [session|globa] status;

其中：session(默认)表示当前连接，global 表示自数据库启动至今

mysql>show status;

mysql>show global status;

mysql>show status like “com_%”;(com_insert%、com_select%、com_update%、com_delete%)

mysql>show global status like ‘com_%’;

修改表的引擎为innodb

mysql>alter table demo2 engine=innodb;

mysql>show create table demo2;

连接数据库的次数

mysql>show status like “connections”;

查看慢查询日志是否开启

show variables like “slow_queries”;

当查询时间大于规定的时间，

1.2、定位执行效率较低的sql语句

1）explain select * from table where id=1000\G;

2）desc select * from table where id=1000; 

3.2 索引问题

Mysql如何使用

2、使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用：如下

3、如果对大的文本进行搜索，使用全文索引而不使用like”%…%”

4、如果列明是索引，使用column_name is null 将使用索引。使用column_name is not null 将不使用索引如下：

存在索引但不是用索引

（1）如果mysql 估计使用索引比全表扫描更慢，则不适用索引。列如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好

mysql>select * from table_name where key_part1>1 and key_part1<90;

(2)r如果使用memory/heap表并且where条件中不使用“=”进行索引列，那么不会用到索引，heap表只有在“=”的条件下会使用索引。

（3）用or分割开的条件，如果or前的条件中给的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

mysql>show index from sales\G;

where and 或是or 中的字段都要加索引，否则索引会失效（重点是or）

如果列类型是字符串，但在查询时把一个数值型常量赋值给一个字符型的列名name,那么虽然在name列上有索引，但是也没有用到。

mysql>explain select * from company2 where name =294\G

查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代了一个行被索引值读的次数。

Handler_read_rnd_next的值高则意味着查询运行低效，并且该建立索引补救。

mysql>show status like “Handler_read%”;

3.3 两个简单使用的优化方法

对于大多数开发人员来说，可能只希望掌握一些简单使用的优化方法，对于更多更复杂的优化，更倾向于交给作业DBA来做。

3.3.1 定期分析表和检查表

ANALYZE [LOCAL | NO_WARITE_TO_BINLOG] TABLE tal_name[,tabl_name]……

本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生产正确的行计划。

定期优化表

优化表的语法格式：

OPTIMEIZE [LOCAL | NO_WRITH_TO_BINLOG] TABLE tb1_name [, tb1_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期的优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。

3.4 常用sql的优化

 大批量插入数据

   当用load命令导入数据的时候，适当设置可以提高导入的速度。

？ infile

?  outifle

导出数据

select name from t1 into outfile “test.txt”; 

导入数据

load data infile ’test.txt’ into  table t1(name);

? load data

    对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

    alter table tb1_name disable keys

    loading the data 

    alter table tb1_name enable keys

disable keys 和enable keys 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度。注意：对InnoDB表无效。

3.5、优化group by语句的优化

 如果查询包含group by但用户想要避免排序结果的损耗，则可以使用order by null来禁止排序：

如下没有使用order by null 来禁止排序 

3.6、优化嵌套查询（里面的能用到索引，外面的没用到索引）