面试题：MySQL索引列类型选择对性能的影响

Question

在MySQL中，如果要为一个经常用于比较和范围查询的列创建索引，在整数类型（如INT、BIGINT）和字符串类型（如VARCHAR）中，你会优先选择哪种类型，并说明理由。如果选择字符串类型，怎样优化其索引性能？

Answer 1

1. 优先选择整数类型
   • 理由：
     • 存储效率高：整数类型（如INT占用4字节，BIGINT占用8字节）相比字符串类型（如VARCHAR除了存储字符串内容外，还需要额外字节记录长度等信息），在存储上更加紧凑，占用更少的磁盘空间和内存，这使得索引结构可以在有限的空间内存储更多的索引项，提高索引的查找效率。
     • 比较速度快：整数类型在进行比较和范围查询时，计算相对简单。CPU对整数的比较操作可以直接通过硬件指令高效完成，而字符串比较需要逐字符进行比较，涉及更多的计算步骤，在大数据量的情况下，整数类型的比较速度优势明显，从而加快查询速度。
2. 如果选择字符串类型优化索引性能的方法：
   • 前缀索引：
     • 做法：对于较长的字符串，可以创建前缀索引。例如，对于VARCHAR(255)类型的列，如果全部内容作为索引，不仅占用空间大，查询性能也不一定好。可以只对前n个字符创建索引，例如CREATE INDEX idx_column_name ON table_name(column_name(10));这里对前10个字符创建索引。
     • 优点：这样可以显著减少索引的存储空间，同时在很多情况下，前缀索引就足以满足大部分查询需求，提高查询效率。
   • 覆盖索引：
     • 做法：设计查询语句和索引时，尽量让索引包含查询所需的所有列。例如，如果查询语句是SELECT column1, column2 FROM table_name WHERE column3 = 'value';那么可以创建一个包含column3、column1和column2的复合索引CREATE INDEX idx_complex ON table_name(column3, column1, column2);
     • 优点：这样查询时，MySQL可以直接从索引中获取所需数据，而无需回表操作（从索引找到数据对应的行，再到数据文件中读取整行数据），大大提高查询性能。
   • 避免使用函数操作：
     • 做法：在查询条件中，避免对字符串列使用函数操作。例如，不要使用SELECT * FROM table_name WHERE UPPER(column_name) = 'VALUE';这样的查询。
     • 原因：对列使用函数操作会导致索引失效，MySQL无法利用索引快速定位数据，只能进行全表扫描，大大降低查询效率。
   • 保持字符集一致性：
     • 做法：确保表、列以及索引使用相同的字符集。例如，都使用utf8mb4字符集。
     • 原因：不同字符集在存储和比较时规则不同，如果字符集不一致，可能会导致索引比较时出现问题，影响查询性能。