Mysql在LONGTEXT字段上作like操作的消耗是怎样的
发布时间：2021-12-17 09:30:43 所属栏目：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操作的消耗是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。 （编辑：随州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
