当您在MySQL或PostgreSQL中拥有TEXT字段时，是否应该将其放在单独的表中? [英] When you have a TEXT field in MySQL or PostgreSQL, should you put it in a separate table?

问题描述

我听说，如果您有一个带有TEXT列的表，该表将容纳大量文本数据，那么将列移到一个单独的表中并通过JOIN将该记录移到基本记录中对于性能而言是更好的选择. /p>

这是真的吗?为什么?

解决方案

不适用于PostgreSQL，来自手册:

非常长的值也存储在后台表中，以免干扰快速访问较短的列值.

因此，大字符列(例如，没有指定大小限制的TEXT或VARCHAR)存储在远离主表数据的位置.因此，PostgreSQL具有内置的放入单独的表"优化.如果您使用的是PostgreSQL，请合理安排表并将数据布局留给PostgreSQL.

我不知道MySQL或其他RDBM如何安排数据.

此优化背后的原因是，数据库通常会将每一行的数据保存在磁盘上的连续块中，以减少查找何时需要读取或更新该行的问题.如果一行中有TEXT(或其他可变长度类型)列，则该行的大小是可变的，因此需要逐行进行更多工作.类比是访问链表中的内容与访问数组之间的区别.使用链表，您必须一次读取三个元素才能到达第四个元素，而使用数组则只需从开始偏移3 * element_size个字节，然后一步就可以了.

I've heard that if you have a table with a TEXT column that will hold a large chunk of text data, it's better for performance to move that column into a separate table and get it via JOINs to the base record.

Is this true, and if so why?

解决方案

Not with PostgreSQL, from the manual:

Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values.

So a large character column (such as TEXT or VARCHAR without a specified size limit) is stored away from the main table data. So, PostgreSQL has your "put it in a separate table" optimization built in. If you're using PostgreSQL, arrange your table sensibly and leave the data layout to PostgreSQL.

I don't know how MySQL or other RDBMs arrange their data.

The reason behind this optimization is that the database will usually keep the data for each row in contiguous blocks on disk to cut down on seeking when the row needs to be read or updated. If you have a TEXT (or other variable length type) column in a row then the size of the row is variable so more work is needed to go from row to row. An analogy would be the difference between accessing something in a linked list versus accessing an array; with a linked list, you have to read three elements one at a time to get to the fourth one, with an array you just offset 3 * element_size bytes from the beginning and you're there in one step.

这篇关于当您在MySQL或PostgreSQL中拥有TEXT字段时，是否应该将其放在单独的表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！