MySQL ALGORITHM=INSTANT加字段失败，如何查询实际行大小？

尝试使用ALGORITHM=INSTANT为contacts表添加字段时失败，报错：

Column can't be added or dropped with ALGORITHM=INSTANT as either max possible row size already crosses max permissible row size or may cross it after add. Try ALGORITHM=INPLACE/COPY

该表通过data_length/rows计算出的avg_row_size为72字节，表内有超3亿行，但无法查询实际行大小，且SELECT COUNT(1) FROM contacts这类查询会超时。表结构如下：

CREATE TABLE `contacts` (
   `id` int NOT NULL AUTO_INCREMENT,
   `first_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `last_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `account_id` int DEFAULT NULL,
   `created_by_id` int DEFAULT NULL,
   `updated_by_id` int DEFAULT NULL,
   `created_at` datetime NOT NULL,
   `updated_at` datetime NOT NULL,
   `introduced_date` datetime DEFAULT NULL,
   `dob` date DEFAULT NULL,
   `gender` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `campaign_id` int DEFAULT NULL,
   `contact_source_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `title` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `contact_source_description` text COLLATE utf8mb3_unicode_ci,
   `prospect_account_id` int DEFAULT NULL,
   `active_demand_email_id` int DEFAULT NULL,
   `is_anonymous` tinyint(1) DEFAULT '1',
   `claimed_on` datetime DEFAULT NULL,
   `claimed_by_id` int DEFAULT NULL,
   `is_deleted` tinyint(1) DEFAULT '0',
   `deleted_at` datetime DEFAULT NULL,
   `seniority` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `bio` text COLLATE utf8mb3_unicode_ci,
   `avatar_file_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `avatar_content_type` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `avatar_file_size` int DEFAULT NULL,
   `avatar_updated_at` datetime DEFAULT NULL,
   `ad_api_key` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
   `only_log_marketing_emails` tinyint(1) DEFAULT '0',
   `classify_incoming_emails` tinyint(1) DEFAULT '1',
   `create_email_contacts` tinyint(1) DEFAULT '0',
   `outbound_call_id_phone_id` int DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `index_contacts_on_first_name_and_last_name_and_id` (`first_name`,`last_name`,`id`),
   KEY `index_contacts_on_prospect_account_and_account_and_names` (`prospect_account_id`,`account_id`,`first_name`,`last_name`),
   KEY `index_contacts_on_account_id` (`account_id`),
   KEY `index_contacts_on_prospect_account_id` (`prospect_account_id`),
   KEY `index_contacts_on_first_name` (`first_name`),
   KEY `index_contacts_on_last_name` (`last_name`),
   KEY `index_contacts_on_prospect_account_and_anonymous` (`prospect_account_id`,`is_anonymous`),
   KEY `index_contacts_on_account_and_anonymous` (`account_id`,`is_anonymous`),
   KEY `index_contacts_on_ad_api_key` (`ad_api_key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=557781942 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

针对大表场景，推荐以下高效方法，避免全表扫描超时：

1. 计算理论最大行大小（报错核心判断依据） #

MySQL判断ALGORITHM=INSTANT是否可行，主要看最大可能行大小（所有可变字段取最大值时的总字节数）。通过INFORMATION_SCHEMA.COLUMNS计算：

SELECT 
  SUM(
    CASE 
      WHEN DATA_TYPE IN ('varchar', 'text') THEN 
        IF(CHARACTER_SET_NAME = 'utf8mb3', CHARACTER_MAXIMUM_LENGTH * 3, CHARACTER_MAXIMUM_LENGTH * 4)
      WHEN DATA_TYPE = 'int' THEN 4
      WHEN DATA_TYPE = 'tinyint' THEN 1
      WHEN DATA_TYPE = 'datetime' THEN 8
      WHEN DATA_TYPE = 'date' THEN 3
      ELSE 0
    END
  ) AS max_possible_row_size_bytes
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database_name' 
  AND TABLE_NAME = 'contacts';

• 替换your_database_name为实际数据库名称
• utf8mb3字符集每个字符最多占3字节，utf8mb4则为4字节
• 该值直接决定添加字段后是否超出InnoDB行大小限制（默认16KB页对应约14KB行大小上限）

2. 采样查询实际平均行大小 #

对大表随机采样部分行，计算真实平均行大小，无需全表扫描：

SELECT 
  AVG(
    LENGTH(CONCAT_WS('', id, first_name, last_name, account_id, created_by_id, updated_by_id, created_at, updated_at, introduced_date, dob, gender, campaign_id, contact_source_name, title, contact_source_description, prospect_account_id, active_demand_email_id, is_anonymous, claimed_on, claimed_by_id, is_deleted, deleted_at, seniority, bio, avatar_file_name, avatar_content_type, avatar_file_size, avatar_updated_at, ad_api_key, only_log_marketing_emails, classify_incoming_emails, create_email_contacts, outbound_call_id_phone_id))
  ) AS avg_actual_row_size_bytes
FROM contacts 
ORDER BY RAND() 
LIMIT 1000;

• CONCAT_WS自动忽略NULL值，LENGTH计算拼接后字符串的字节数
• 采样1000行即可得到接近真实的平均行大小，查询速度快
• 若ORDER BY RAND()仍慢，可改用主键范围采样（如WHERE id BETWEEN 1000000 AND 1001000）

3. 利用InnoDB元数据统计平均行大小 #

通过INFORMATION_SCHEMA.INNODB_TABLESPACES获取维护好的表空间数据，无需扫描表：

SELECT 
  (DATA_SIZE + INDEX_SIZE) / ROWS AS avg_total_row_size_bytes,
  DATA_SIZE / ROWS AS avg_data_row_size_bytes
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE NAME = 'your_database_name/contacts';

• DATA_SIZE为数据区大小，INDEX_SIZE为索引区大小，两者之和是表总占用空间
• 该统计是InnoDB实时维护的元数据，查询瞬间完成

内容的提问来源于stack exchange，提问作者mtande