数据适合行时的VARCHAR vs TEXT性能 [英] VARCHAR vs TEXT performance when data fits on row
本文介绍了数据适合行时的VARCHAR vs TEXT性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
mysql> desc temp1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| value | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
mysql> desc temp2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| value | text | YES | | NULL | |
+-------+------+------+-----+---------+-------+
255-每行"a"字符(在两个表中)
mysql> select * from temp1 limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| value |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> select * from temp2 limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| value |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
查询表1:
select count(*) from temp1 where value like '%a';
查询表2:
select count(*) from temp2 where value like '%a';
统计信息:
No of records---temp1(varchar)---temp2(text)
2097152---------6.08(sec)--------6.91(sec)
4194304---------12.42(sec)-------13.66(sec)
8388608---------25.08(sec)-------28.03(sec)
16777216--------52.82(sec)-------56.88(sec)
33554432--------1(min)50.17(sec)-1(min)59.36(sec)
我的问题:如何解释执行速度的差异?
My question: How can the difference in execution speed be explained?
两个表中的行内容相同.
The rows contents are same in both tables.
VarChar和Text列仅在其超出行大小时才将内容保持为offPage.因此，两个表的内容都将是我的page size(16kb)的内联数据.那么造成此查询执行时间差异的原因是什么.
As I understood VarChar and Text columns keep contents offPage only when it exceeds row size. So both tables contents will be inline data for my page size(16kb). Then what was the reason for this query execution time difference.
注意:两个表列均未索引
Note: Both table column is not indexed
Row Format - DYNAMIC
Collation - UTF8mb3
Character set - utf8_general_ci
Storage engine - innodb
Mysql - 5.7
参考链接: https://stackoverflow.com/a/48301727/5431418
更新: 现在我尝试在两个表中使用5000个字符('a')进行相同的流程，结果差异很大.
Update: Same flow now I tried with 5000 characters ('a') in both tables the result difference is high.
2097152---------1(min)53.63(sec)--------2(min)4.66(sec)
更新2: 现在我尝试在两个表中使用2个字符('a')的相同流程仍然存在性能差异
Update 2: Same flow now I tried with 2 characters ('a') in both tables still there is a performance difference
添加表格状态:
mysql> select * FROM information_schema.tables WHERE table_schema = "db67006db" and table_name = 'temp1';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | db67006db | temp1 | BASE TABLE | InnoDB | 10 | Dynamic | 30625036 | 315 | 9659482112 | 0 | 0 | 425721856 | NULL | 2019-09-23 20:20:17 | NULL | NULL | utf8_general_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.01 sec)
mysql> select * FROM information_schema.tables WHERE table_schema = "db67006db" and table_name = 'temp2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | db67006db | temp2 | BASE TABLE | InnoDB | 10 | Dynamic | 30922268 | 315 | 9753853952 | 0 | 0 | 425721856 | NULL | 2019-09-23 20:20:12 | NULL | NULL | utf8_general_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
推荐答案
关于存储，InnoDB将在很多情况下处理VARCHAR和TEXT. 两者都内联存储时相同.但是，当从中获取数据时 InnoDB，服务器将在所有VARCHAR列之前分配空间 查询执行.虽然TEXT列的空间只会分配 如果实际读取它们，则动态内存分配需要花费时间.
With respect to storage, InnoDB will handle VARCHAR and TEXT much the same when both stored inline. However, when fetching the data from InnoDB, the server will allocate space for all VARCHAR columns before query execution. While space for TEXT columns will only be allocated if they are actually read, where DYNAMIC memory allocation takes time.
https://forums.mysql.com/read .php?24,645115,645164#msg-645164
这篇关于数据适合行时的VARCHAR vs TEXT性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
