有另外一個不同,但是可以參考的問題.

https://ithelp.ithome.com.tw/questions/10188078

接著我們來做些測試觀察.

create table ithelpmy190924a (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, pad1 CHAR(100) NOT NULL
);

create table ithelpmy190924b (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, pad1 CHAR(100) NOT NULL
, pad2 text
);

-- 載入資料
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE ithelpmy190924a(pad1);
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE ithelpmy190924b(pad1);

Query OK, 235886 rows affected (3.11 sec)
Records: 235886  Deleted: 0  Skipped: 0  Warnings: 0

觀察空間大小

SELECT table_name  
     , data_length / 1024 AS 'data(K)'
     , row_format
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name LIKE 'ithelpmy190924%'
 ORDER BY table_name;

+-----------------+------------+------------+
| TABLE_NAME      | data(K)    | ROW_FORMAT |
+-----------------+------------+------------+
| ithelpmy190924a | 31296.0000 | Dynamic    |
| ithelpmy190924b | 31296.0000 | Dynamic    |
+-----------------+------------+------------+
2 rows in set (0.01 sec)



接著再放入一些資料到 ithelpmy190924b pad2

update ithelpmy190924b
   set pad2 = pad1
 where rand() <= 0.05;

Query OK, 11856 rows affected (0.51 sec)
Rows matched: 11856  Changed: 11856  Warnings: 0

analyze table ithelpmy190924b;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| miku1.ithelpmy190924b | analyze | status   | OK       |
+-----------------------+---------+----------+----------+

SELECT table_name  
     , data_length / 1024 AS 'data(K)'
     , row_format
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name LIKE 'ithelpmy190924%'
 ORDER BY table_name;

+-----------------+------------+------------+
| TABLE_NAME      | data(K)    | ROW_FORMAT |
+-----------------+------------+------------+
| ithelpmy190924a | 31296.0000 | Dynamic    |
| ithelpmy190924b | 31296.0000 | Dynamic    |
+-----------------+------------+------------+

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings

TEXT and BLOB columns are implemented differently in NDB; each row in a TEXT column is 
made up of two separate parts. One of these is of fixed size (256 bytes), 
and is actually stored in the original table. 
The other consists of any data in excess of 256 bytes, which is stored in a hidden table. 
The rows in this second table are always 2000 bytes long. 
This means that the size of a TEXT column is 256 
if size <= 256 (where size represents the size of the row); 
otherwise, the size is 256 + size + (2000 × (size − 256) % 2000). 

有 hidden table, 也就是說, 有額外的空間來做處理.

select max(length(pad1))
     , max(length(pad2))
  from ithelpmy190924b;

+-------------------+-------------------+
| max(length(pad1)) | max(length(pad2)) |
+-------------------+-------------------+
|                24 |                22 |
+-------------------+-------------------+
1 row in set (0.13 sec)

在上面載入資料均未超過 256 bytes.
MySQL 8 String Function 文件中 OCTET_LENGTH() is a synonym for LENGTH().

接著來將 pad2 再填充一些資料.

update ithelpmy190924b
   set pad2 = rpad(pad1, 1256, 'pad')
 where pad2 is not null;

Query OK, 11856 rows affected (1.63 sec)
Rows matched: 11856  Changed: 11856  Warnings: 0

analyze table ithelpmy190924b;

SELECT table_name  
     , data_length / 1024 AS 'data(K)'
     , row_format
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name LIKE 'ithelpmy190924%'
 ORDER BY table_name;
 
+-----------------+------------+------------+
| TABLE_NAME      | data(K)    | ROW_FORMAT |
+-----------------+------------+------------+
| ithelpmy190924a | 31296.0000 | Dynamic    |
| ithelpmy190924b | 73328.0000 | Dynamic    |
+-----------------+------------+------------+

果然變胖了...

在以前比較舊的資料庫, 一些不常使用的欄位, 因為處理時,還是有佔空間的疑慮(這方面
你可以自行安排測試,相信會很精彩),所以會另外建立一個table, 與基本table做一對一對應.
再建立 view, 當需要查完整資料時,查view,當查主要資料時,查基本 table.
經過上面的觀察,現在比較新的資料庫,使用了 text 型態,會將額外的部分,另外存放.
在背景中,幫我們處理掉了.

另外說明一下, 在 Postgresql中是將文字型態資料另外放,基本table中實際上是放指標.
與 MySQL 的作法,各有千秋.
以上供你參考.期待你做的測試.

理論上,
如果不是SELECT * 基本上是沒有影響,
反而是筆數的影響更大,
一般是建議只SELECT需要的欄位就好,
也比較不會影響讀取的速度.

select 絕對要養成好習慣
只讀需要得欄位就好

曾經菜鳥的時候
前輩整個案子用select *
上線前離職
那一週直接崩潰.....

照理來說 如果沒有類似text型態的欄位是還好