mysql查询优化技巧

时间:2021-07-14 00:14:44

索引优化,查询优化,查询缓存,服务器设置优化,操作系统和硬件优化,应用层面优化(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);