加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mysql在LONGTEXT字段上作like操作的消耗是啥样的

发布时间:2023-07-14 13:22:22 所属栏目:MySql教程 来源:
导读:这篇文章将为大家详细讲解有关Mysql在LONGTEXT字段上作like操作的消耗是怎样的,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。# Mysql 5140 @ RHEL 5u4 X86_64

这篇文章将为大家详细讲解有关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操作的消耗是怎样的就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章