mysql索引排序规则设置方法_mysqlCollation对索引影响

张开发
• 2026/4/20 20:23:58 • 15 分钟阅读

最新文章

推荐文章

相关文章

分享文章

mysql索引排序规则设置方法_mysqlCollation对索引影响
MySQL索引排序由字段定义的COLLATION决定而非查询级设置改字段COLLATION会改变B树物理顺序影响等价字符判定与索引使用ORDER BY加COLLATE子句通常导致Using filesort。MySQL 的 COLLATION 怎么影响索引排序索引排序行为不是由“单独设置的排序规则”决定的而是由字段定义时的 COLLATION 直接绑定。你改字段的 COLLATE索引就跟着按新规则排序不改哪怕查询里加 ORDER BY ... COLLATE xxx索引也大概率用不上。根本原因B 树索引的物理顺序依赖字段值的二进制比较结果而这个比较逻辑就来自 COLLATION。一旦字段 COLLATE 是 utf8mb4_0900_as_cs大小写敏感、重音敏感那索引里 “Apple” 和 “apple” 就是两个不同位置的键值换成 utf8mb4_0900_ai_ci它们就可能被归为等价排序位置也会变。COLLATION 是字段属性不是会话级或查询级开关建表时没显式指定会继承表默认 COLLATE表没设则继承数据库默认修改字段 COLLATE 会触发表重建ALGORITHMINPLACE 在部分场景下可用但非绝对怎么安全地改字段 COLLATION 并让索引生效直接 ALTER TABLE ... MODIFY COLUMN 改 COLLATE 很危险——如果字段上有索引MySQL 会先删旧索引、再建新索引期间该字段的查询可能全走全表扫描。更麻烦的是如果字段是主键或唯一索引的一部分还可能因重复值校验失败而报错比如原 _ci 下不区分大小写的 “A” 和 “a”在 _cs 下变成两个不同值违反唯一约束。先查清当前值分布SELECT DISTINCT BINARY col_name FROM t WHERE col_name IS NOT NULL; 看二进制是否真有重复用 ALTER TABLE ... ALTER COLUMN col_name SET COLLATION xxx8.0.30可避免重建但仅限于兼容的 collation 之间如 utf8mb4_0900_ai_ci → utf8mb4_0900_as_cs老版本必须 MODIFY COLUMN建议在低峰期操作并提前在测试库验证索引是否仍能用于 ORDER BY 和 WHERE为什么 ORDER BY col COLLATE utf8mb4_bin 不走索引因为优化器发现索引是按字段定义的 COLLATION 排的而你在查询里强行指定另一个 COLLATE意味着需要对每个索引项做实时转换再比较无法复用已有的有序结构。这时候 MySQL 通常放弃索引排序改用文件排序Using filesort。 arXiv Xplorer ArXiv 语义搜索引擎帮您快速轻松的查找保存和下载arXiv文章。

更多文章