mysql查询优化技巧

索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等等。这里记录的优化技巧更适合开发人员，都是从网络上搜集和整理的，主要是查询语句上的优化，其他层面上的优化技巧在此不做记录。

查询的开销指标：执行时间，检查的行数，返回的行数。

建立索引的几个准则：

(1)合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。

(2)索引越多，更新数据的速度越慢。

(3)尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持Transcation。

(4)当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。 

(5) 习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

 

一、count的优化

 (1). select count(*) from world.city where id > 5;

 (2). select (select count() from world.city) – count() from world.city where id <= 5; 

 1语句当行数超过11行的时候需要扫描的行数比2语句要多， 2语句扫描了6行，此种情况下，2语句比1语句更有效率。当没有where语句的时候直接select count(*)                 from world.city这样会更快，因为mysql总是知道表的行数。

二、索引字段上进行运算会使索引失效

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 

SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2

 

三、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此         需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists. 

 

四、合理使用EXISTS,NOT EXISTS子句

 

五、 能够用BETWEEN的就不要用IN

 

六、 能够用DISTINCT的就不用GROUP BY

 

七、尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定，阻止其他用户访问该表

 

八、程序中如果一次性对同一个表插入多条数据

比如以下语句： 

insert into person(name,age) values(‘xboy’, 14);

insert into person(name,age) values(‘xgirl’, 15);

insert into person(name,age) values(‘nia’, 19);

把它拼成一条语句执行效率会更高.

insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);