mysql索引优化中的列数据类型选择与匹配

varchar(255)不一定比varchar(64)慢，因实际存储可变，但索引比较仍按最大长度预留空间；int比bigint更优，因4字节宽度使索引页容纳键值更多、树高更低、i/o更少。

为什么 VARCHAR(255) 不一定比 VARCHAR(64) 慢，但 INT 比 BIGINT 更值得优先考虑

索引性能不只看“有没有”，更取决于「数据类型宽度」和「比较开销」。mysql 在 B+ 树索引中逐行比较键值，类型越宽、越复杂，CPU 比较越慢，缓存命中率越低。

• VARCHAR 实际存储长度可变，但索引排序和比较时仍需按最大声明长度预留空间（尤其在 utf8mb4 下，1 个字符最多占 4 字节）
• INT 固定 4 字节，BIGINT 固定 8 字节 —— 同样数量的索引页，前者能容纳约 2 倍多的键值，减少树高和磁盘 I/O
• 对 enum 或 SET 类型建索引要格外小心：内部转为整数比较，但查询时若用字符串字面量，可能触发隐式类型转换，导致索引失效

WHERE 条件中的类型强制转换会让索引完全失效

常见于字段定义为 VARCHAR，但查询时传入数字（如 WHERE user_id = 123），或反过来：字段是 INT，却用字符串查询（WHERE status = '1'）。MySQL 会尝试将列转为目标类型，从而跳过索引查找路径。

• 用 EXPLAIN 查看 type 是否为 ALL 或 index，同时检查 Extra 列是否含 using where; Using index —— 若只有 Using where，大概率发生了类型转换
• 显式保持一致：WHERE user_id = '123'（当 user_id 是 VARCHAR）或 WHERE status = 1（当 status 是 TINYINT）
• 避免在索引列上套函数或表达式，例如 WHERE UPPER(name) = 'JOHN'，即使 name 有索引也用不上

日期类字段该用 date 还是 DATETIME？timestamp 的隐式行为很危险

三者都支持索引，但语义与存储差异直接影响查询效率与结果正确性。

• DATE 占 3 字节，适合仅需年月日的场景（如生日、合同生效日）；DATETIME 占 8 字节，范围大（1001–9999），无时区转换，适合记录精确时间点（如订单创建时间）
• TIMESTAMP 占 4 字节，但写入时自动转为 UTC 存储，读取时再转回 session 时区 —— 若应用跨多个时区且未统一处理，WHERE created_at > '2024-01-01' 可能因时区偏移返回意外结果
• 范围查询（如 BETWEEN）对 DATE 和 DATETIME 效果接近，但 TIMESTAMP 的自动转换逻辑会让执行计划更难预测，尤其配合 CONVERT_TZ() 时

json 字段加索引必须用虚拟列 + 函数索引，否则等于没建

MySQL 5.7+ 支持对 JSON 字段建索引，但不能直接在 json_col 上建，必须提取路径值到虚拟列，再对该列建索引。

ALTER table orders  ADD COLUMN customer_id_virtual INT AS (json_unquote(json_extract(data, '$.customer_id'))) STORED, ADD INDEX idx_customer_id (customer_id_virtual);

• 漏掉 STORED 关键字会导致无法索引（MySQL 要求虚拟列必须是 STORED 才能建索引）
• json_extract() 返回的是 JSON 字符串，需用 json_unquote() 去掉双引号，否则查 123 会匹配不到 "123"
• 虚拟列本身不额外占用行存储空间，但索引结构照常生成 —— 所以仍需评估该路径是否高频查询，避免为低频字段堆砌索引

类型宽度、比较一致性、时区语义、JSON 提取方式 —— 这些不是“选对了就完事”的配置项，而是每次写 CREATE TABLE 或 WHERE 条件时都得下意识核对的细节。