如何高效查询大型MySQL表中首字符非ASCII字母的条目？

现有写法的核心问题 #

你目前用到的两种写法性能都达不到大表要求，原因如下：

• SELECT * FROM companiesWHEREname NOT REGEXP '^[a-z]+'：REGEXP/RLIKE属于正则匹配，MySQL优化器无法将其转换为索引范围查询，必然触发全表扫描，数据量越大耗时越长。
• SELECT * FROM companiesWHEREidNOT IN (SELECTidFROMcompaniesWHEREname RLIKE '^[a-z]');：除了正则本身的全表扫描问题，还多了一次子查询全表扫描+结果集匹配的开销，性能比第一种写法差一倍以上，且如果子查询返回的id存在NULL值，会直接导致整个查询结果为空，存在正确性隐患。

高性能实现方案 #

方案1：改写为索引友好的范围查询（零改造成本首选） #

MySQL的字符串比较按字符集排序规则按位匹配，首字符的范围可以直接用普通比较符覆盖，完全可以用到name字段的普通B-tree索引：

SELECT * FROM `companies` 
WHERE `name` < 'a' OR `name` >= '{'

原理：ASCII编码中a对应十进制97，z对应122，{是紧邻z的下一个可打印字符，只要字符串首字符不在a-z范围内，要么小于a要么大于等于{，前缀匹配的范围查询可以直接命中索引。
注意：如果你的字符集排序规则是大小写敏感的，需要匹配大写A-Z的话，调整条件为WHERE name < 'A' OR (name > 'Z' AND name < 'a') OR name >= '{'即可；如果是大小写不敏感的排序规则（比如常用的utf8mb4_general_ci），上面的小写范围已经自动覆盖大写情况。

方案2：函数索引（兼容MySQL5.7+） #

如果不想调整查询逻辑，也可以给首字符的提取结果建函数索引，查询时直接走索引：

-- 建立函数索引
CREATE INDEX idx_name_first_char ON `companies` (LEFT(`name`,1));

-- 查询语句
SELECT * FROM `companies`
WHERE LEFT(`name`,1) NOT BETWEEN 'a' AND 'z';

方案3：冗余字段（千万级以上超高频查询首选） #

如果表数据量过千万、该查询频率很高，可以新增冗余字段专门存储首字符，用最低的维护成本换最高的查询性能：

-- 新增自动维护的生成列并建索引
ALTER TABLE `companies` 
ADD COLUMN `name_first_char` char(1) GENERATED ALWAYS AS (LEFT(`name`,1)) STORED,
ADD INDEX idx_name_first_char(`name_first_char`);

-- 查询语句
SELECT * FROM `companies` 
WHERE `name_first_char` NOT BETWEEN 'a' AND 'z';

千万级表实测性能对比 #

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