查询性能优化

慢查询基础：优化数据访问

查询的最基本的原因是 访问的数据太多。可以通过减少访问的数据两进行优化；具体步骤如下:

确认应用程序是否检索大量超过需要是数据–访问行列太多 查询不需要的记录:会返回所有值再筛选总是取出全部列重复查询相同的数据：例如再用户评论中–不断地重复执行相同的查询多表关联时返回全部列表:例如 # 返回三个表的全部数据列 SELECT * FROM sakila.actor INNER JOIN sakila.film_actor USING(actor_id) INNER JOIN sakila.film USING(film_id) WHERE sakila.film.title= 'ACademy Dinosaur'; # 改进后的写法如下 SELECT sakila.actor.* FROM sakila.actor MySQL服务器层是否在分析大量超过需要的数据行

衡量查询开销的三个指标如下:

响应时间:服务时间(查询时间)+排队时间(资源中断的时间)扫描的行数:返回的行数访问类型

一般MySQL能够使用如下三种方式引用WHERE条件，从好到坏依次为:

使用WHERE条件来过滤不匹配的记录再，存储引擎层完成使用覆盖扫描(Using index),服务器层完成从数据表中返回数据(Using Where),MySQL服务器层完成，先读出数据然后进行过滤。

一般的优化方法:

使用覆盖扫描改变库表结构重写复杂查询

重构查询的方式

使用多个简单查询代替一个复杂查询切分查询:将大查询切分成小查询，例如再进行DELETE时，使用多个小查询，避免一次锁住过多的数据、占满整个事务日志。例如 # 原始查询 DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) # 循环优化为: rows_affected=0 do { rows_affected=do_query( "DELETE FROM messages WHERE created <DATE_SUB(NOW(),INTERVAL 3 MONTH)" ) }while rows_affected > 0 分解关联查询:对每一个表进行一次单表查询，然后将结果再应用程序中进行关联。例如： # 原始语句 SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.tag_id=post.id WHERE tag.tag='mysql'; # 分解成如下查询语句 SELECT * FROM tag WHERE tag='mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id IN (123,456,9098,8904);

优点如下：

让缓存的效率更高。已经缓存的内容，就不必再继续读取，可以直接跳过查询分解后，执行单个查询可以减少锁的竞争在应用层做关联，可以更容易对数据库进行拆分，共容易做到高性能和可扩展查询本身效率也可能会有所提升。使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，可能比随机的关联要更加高效，可以减少冗余记录的查询。避免数据库层的重复数据访问。在应用层中实现了哈希关联，而不是使用MySQL的陶杰循环关联。某些场景哈希关联的效率要高很多。