MySQL聚簇与非聚簇索引查询性能巨大差异原因咨询

你的判断方向完全正确，这个巨大性能差的核心诱因就是表中存在的多个TEXT/BLOB大字段，导致聚簇索引和二级索引的扫描IO量差了两个数量级，具体逻辑如下：

• InnoDB的最小磁盘IO单位是16KB的页，不存在“读页内部分数据”的操作，只要需要访问页内的任何一条记录，就必须把整个页完整加载到内存。
• 聚簇索引（主键索引）的叶子节点存储的是完整行数据，不是单独存储主键值。你的表包含1个非空BLOB字段、4个TEXT字段，按照InnoDB的溢出页存储规则，当行总长度超过8KB（16KB页的一半）时，大字段会把超出部分存在溢出页，但每个大字段的前768字节依然会留在聚簇索引的数据页中，再加上c1、ts等其他列的内容，单条记录在聚簇索引页中占用的空间会非常大。你这张表总大小100GB，绝大部分空间都是聚簇索引的叶子页。
• 你创建的non_clustered_key是只包含id列的二级索引，这类索引的叶子节点仅存储「索引列值 + 主键值」，由于你的索引列本身就是主键id，单条索引记录仅占4字节（int类型长度），算上页开销单个16KB页可以存储近千条记录，整个二级索引的总大小通常只有几百MB，和100GB的聚簇索引体积差了上百倍。

很多人会误以为“查询只需要id列，走聚簇索引时也不需要读取其他列内容，所以速度应该和二级索引接近”，这个认知是错的：
聚簇索引不存在独立于行数据的“主键值存储区”，所有主键值和整行数据是混合存在同一个叶子页里的。你要按顺序遍历所有id值，就必须把聚簇索引所有的叶子页全部读一遍——哪怕你完全不需要页里存储的BLOB、TEXT、其他列内容，也没法跳过这些占了页99%空间的无关数据。而二级索引是完全独立的B树结构，和表中其他列的数据完全隔离，扫描时不需要碰任何大字段相关的存储内容，自然速度极快。

你可以执行下面的SQL直接查看两个索引的实际存储大小，二者的大小比值基本就是性能的差值倍数：

SELECT 
    index_name, 
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024 / 1024, 2) AS index_size_gb
FROM mysql.innodb_index_stats 
WHERE table_name = '100gb_table' 
AND stat_name = 'size';

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