Mysql 在LONGTEXT字段上作like操作的消耗;

时间：2010-09-29 来源：Steven1981

# Mysql 5140 @ RHEL 5u4 X86_64

# 先提供一些表的信息:
===================================================================
[email protected] : trac_apsara 17:18:46> show create table wiki G
*************************** 1. row ***************************
Table: wiki
Create Table: CREATE TABLE `wiki` (
`name` longtext COLLATE utf8_bin,
`version` int(11) DEFAULT NULL,
`time` bigint(20) DEFAULT NULL,
`author` longtext COLLATE utf8_bin,
`ipnr` longtext COLLATE utf8_bin,
`text` longtext COLLATE utf8_bin,
`comment` longtext COLLATE utf8_bin,
`readonly` int(11) DEFAULT NULL,
KEY `wiki_time_idx` (`time`),
KEY `name_ver_ind` (`name`(200),`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

[email protected] : trac_apsara 17:19:04> select count(*) from wiki;
+----------+
| count(*) |
+----------+
| 76514 |
+----------+
1 row in set (0.03 sec)

[email protected] : trac_apsara 17:19:08> select count(distinct name ) from wiki;
+-----------------------+
| count(distinct name ) |
+-----------------------+
| 40369 |
+-----------------------+
1 row in set (0.59 sec)

[email protected] : 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条件之差。

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

[email protected] : trac_apsara 17:25:39> reset query cache ;
Query OK, 0 rows affected (0.00 sec)

[email protected] : 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)

[email protected] : trac_apsara 17:26:12> reset query cache ;
Query OK, 0 rows affected (0.00 sec)

[email protected] : 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模糊查询操作，也很费时间；