Slow Query Basics: Optimize Data Access

查询太慢的一个最基本的原因就是：处理的数据太多了。

1. 看看你的应用获取的数据是否大于它需要的数据

2. 看看MySQL Server是否分析了大于它需要的数据

 

常见的错误

1.Fetching more rows than needed

2. Fetching all columns from a multitable join

3. Fetching all columns

4. Fetching the same data repeatedly

 

重建查询的方法

分解查询（任务）

比如清理表数据，如果一次清理得太多，那么很多行会被锁住，挡住其他查询等。所以把DELETE的任务分解成小块来执行能很明显提升性能。

mysql> 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)
LIMIT 10000")
} while rows_affected > 0

 

分解联表

如：

mysql> SELECT * FROM tag
-> JOIN tag_post ON tag_post.tag_id=tag.id
-> JOIN post ON tag_post.post_id=post.id
-> WHERE tag.tag='mysql';

可以分解成下面的：

mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

这样的优点有：

  • 缓存更有效
  • 减少锁表竞争
  • 方便数据库部署在不同的服务器上面
  • 单个的SQL效率更高，如IN（）要比联表来得快
  • 减少access的行，因为联表本身是反范式的，会重复查很多数据
  • 类似于hash联表的技术

 

查询执行的基础

1. 客户端向服务端发送SQL语句

2. 服务端先找缓存，如果有直接返回结果，否则进入下一步

3. 服务端编译、预处理和优化SQL

4. 查询执行引擎通过调用存储引擎的API来执行SQL

5. 服务端返回结果

 

The MySQL Client/Server Protocol

Mysql的C/S协议是half duplex的，就是说，服务端可以发送也可以接受信息，但是不能同时发送和接受信息。

客户端一次请求只发送一个包，max_allowed_packet 决定了服务端能接受的最大的包，如果你的请求太大了，服务器会报错并不再接受客户端的请求。

相反的，服务端能返回很多包给客户端，客户端必须要接受全部的服务端结果，而不能接受一部分后告诉服务端我不再需要了。这就是为什么SQL里加上LIMIT很重要。

也就是说，客户端不是从服务端“拉”数据，而是服务端把数据“推“过来，这个过程是被动的，不能被客户端停止的。

1 <?php
2 $link = mysql_connect('localhost', 'user', 'p4ssword');
3 $result = mysql_query('SELECT * FROM HUGE_TABLE', $link);
4 while ( $row = mysql_fetch_array($result) ) {
5 // Do something with result
6 }

举个例子，上面的代码看起来好像是你需要数据的时候再去fetch，但事实上，数据在调用mysql_query的时候已经在缓存里了，调用mysql_fetch_array时只是在和缓存对话。

 

Query states

使用SHOW FULL PROCESSLIST

Sleep: 等待客户端的请求

Query: 执行请求或是向客户端推送数据

Locked: 等待服务端给一个锁表的权限

Analyzing and statistics： 检查存储引擎的数据和优化查询

Copying to tmp table [ on disk ]: 将查询结果放入缓存表，UNION, GROUP BY都有可能导致

Sorting result

Sending data

 

The Query Cache

 

The Query Optimization Process

Reordering joins: 联表的顺序会被优化

Converting OUTER JOINs to INNER JOINs