mysql longtext查询_Mysql 在LONGTEXT字段上作like操作的消耗;

root@127.0.0.1 : trac_apsara 17:19:04> select count(*) from wiki;

+----------+

| count(*) |

+----------+

| 76514 |

+----------+

1 row in set (0.03 sec)

root@127.0.0.1 : trac_apsara 17:19:08> select count(distinct name ) from wiki;

+-----------------------+

| count(distinct name ) |

+-----------------------+

| 40369 |

+-----------------------+

1 row in set (0.59 sec)

root@127.0.0.1 : trac_apsara 17:19:21> show variables like 'innodb_buffer%';

+-------------------------+------------+

| Variable_name | Value |

+-------------------------+------------+

| innodb_buffer_pool_size | 1073741824 |

+-------------------------+------------+

1 row in set (0.00 sec)

root@127.0.0.1 : trac_apsara 17:21:08> show table status like 'wiki' G

*************************** 1. row ***************************

Name: wiki

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 336009

Avg_row_length: 4458

Data_length: 1498120192

Max_data_length: 0

Index_length: 10551296

Data_free: 7340032

Auto_increment: NULL

Create_time: 2010-09-29 14:49:20

Update_time: NULL

Check_time: NULL

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment:

1 row in set (0.01 sec)

===================================================================

#下面我们来看一下SQL和数据：

## SQL1 :

SELECT w1.name,w1.time,w1.author,w1.text

FROM wiki w1,

(SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

WHERE w1.version = w2.ver AND w1.name = w2.name

AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

);

## SQL2 :

SELECT w1.name,w1.time,w1.author,w1.text

FROM wiki w1,

(SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

WHERE w1.version = w2.ver AND w1.name = w2.name

AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

###### OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

);

两个SQL仅一个WHERE条件之差。

root@127.0.0.1 : trac_apsara 17:24:08> explain SELECT w1.name,w1.time,w1.author,w1.text

-> FROM wiki w1,

-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

-> );

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 40369 | |

| 1 | PRIMARY | w1 | ref | name_ver_ind | name_ver_ind | 608 | w2.name,w2.ver | 3 | Using where |

| 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

3 rows in set (1.04 sec)

root@127.0.0.1 : trac_apsara 17:22:26> explain SELECT w1.name,w1.time,w1.author,w1.text

-> FROM wiki w1,

-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

-> # OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

-> );

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 40369 | |

| 1 | PRIMARY | w1 | ref | name_ver_ind | name_ver_ind | 608 | w2.name,w2.ver | 3 | Using where |

| 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |

+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+

3 rows in set (1.03 sec)

### 从执行计划来看，两个SQL一模一样； 处理的行数也是一样的；

root@127.0.0.1 : trac_apsara 17:25:39> reset query cache ;

Query OK, 0 rows affected (0.00 sec)

root@127.0.0.1 : trac_apsara 17:25:52> SELECT w1.name,w1.time,w1.author,w1.text

-> FROM wiki w1,

-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

-> # OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

-> );

Empty set (1.31 sec)

root@127.0.0.1 : trac_apsara 17:26:12> reset query cache ;

Query OK, 0 rows affected (0.00 sec)

root@127.0.0.1 : trac_apsara 17:26:15> SELECT w1.name,w1.time,w1.author,w1.text

-> FROM wiki w1,

-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2

-> WHERE w1.version = w2.ver AND w1.name = w2.name

-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'

-> OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'

-> );

13 rows in set (3.50 sec)

## 从执行时间来看,

## SQL1 : 3.50 sec , SQL2: 1.31 sec

## 从这里我们基本可以判断出来，MYSQL 用了 2.19 sec 在内存中处理40369次TEXT字段的LIKE模糊查询操作；

## 而从WIKI表INDEX查询40369次，却只用了1.31秒(可能更少)，当然数据已经在CACHE里。

## 我们以后做SQL可要注意了。不光是读硬盘会消耗时间，在内存中的LIKE模糊查询操作，也很费时间；