Mysql在LONGTEXT字段上作like操作的消耗是怎樣的

這篇文章將為大家詳細講解有關Mysql在LONGTEXT字段上作like操作的消耗是怎樣的，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

# Mysql 5140 @ RHEL 5u4 X86_64 

# 先提供一些表的信息:
===================================================================
root@127.0.0.1 : 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)

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模糊查詢操作，也很費時間；

關于Mysql在LONGTEXT字段上作like操作的消耗是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。