简介这份资源是一份手机号码归属地查询数据库以MySQL的SQL文件形式提供适合从事数据分析、营销系统开发、客户服务支撑的开发者与数据库学习者使用。包内共1个文件为phone_msg.sql压缩包约2.22MB导入MySQL后即可通过手机号字段关联查询对应的省份与城市信息数据覆盖较为完整可用于批量归属地匹配、区域用户统计等场景。已有267人学习下载。资源价值在于省去自行采集与整理号码段归属关系的成本可直接用于SQL查询练习、JOIN与GROUP BY等聚合分析实战也能作为数据脱敏、权限控制与个人信息合规使用的教学案例。需要提醒的是涉及个人敏感信息时应遵守《个人信息保护法》在合法合规前提下使用并做好加密存储与访问限制。1. 手机号归属地查询从一份 MySQL 数据表到可上线的接口手上有个用户表几十万行手机号运营要按省份做短信分流风控要按城市判断异常登录。你第一反应可能是调第三方 API但量一上来按次计费的成本和网络延迟都让人难受。这时候一份本地的手机号归属地 MySQL 数据表就成了刚需——标题里说的「非常全淘宝50元买的」本质就是一张覆盖号段、省份、城市、运营商、区号、邮编的映射表。它解决的是「离线、批量、零调用成本」的查询问题适合做后台批处理、数据清洗、用户画像补全的开发者。这篇不讲虚的从建表、导入、索引设计到查询优化把这条链路走通顺带把几个容易翻车的地方说清楚。2. 手机号归属地数据的结构号段、省份、城市怎么对应2.1 手机号前七位才是归属地的钥匙很多人以为手机号归属地是按前三位查的这是个常见误解。前三位只代表运营商比如 138 是移动真正决定省份和城市的是前七位。中国手机号是 11 位结构是「3 位网络识别号 4 位地区编码 4 位用户号码」。归属地库的核心就是那 4 位地区编码它和省份、城市一一对应。所以一张标准的归属地表主键或唯一索引应该建在号段前七位上而不是完整手机号。完整手机号有 11 位前七位相同意味着归属地相同用前七位做键能把数据量压缩到几十万行级别查询时也只需要截取前七位去匹配效率高得多。常见的数据表字段设计如下字段名类型说明idINT UNSIGNED AUTO_INCREMENT主键prefixCHAR(7)号段前七位唯一索引provinceVARCHAR(20)省份cityVARCHAR(30)城市operatorVARCHAR(20)运营商area_codeVARCHAR(6)区号post_codeVARCHAR(6)邮编这张表看起来简单但字段长度和字符集选错后面查询和存储都会出问题。province 和 city 用 utf8mb4 是稳妥的虽然归属地基本都是中文但有些城市名带生僻字utf8 三字节可能不够。prefix 用 CHAR(7) 而不是 VARCHAR因为长度固定CHAR 在索引里更紧凑。2.2 建表语句与索引策略直接上建表 SQL注意字符集和索引的写法CREATE TABLE phone_attribution ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, prefix CHAR(7) NOT NULL COMMENT 号段前七位, province VARCHAR(20) NOT NULL DEFAULT COMMENT 省份, city VARCHAR(30) NOT NULL DEFAULT COMMENT 城市, operator VARCHAR(20) NOT NULL DEFAULT COMMENT 运营商, area_code VARCHAR(6) NOT NULL DEFAULT COMMENT 区号, post_code VARCHAR(6) NOT NULL DEFAULT COMMENT 邮编, PRIMARY KEY (id), UNIQUE KEY uk_prefix (prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT手机号归属地库;这里有几个参数值得说。UNIQUE KEY uk_prefix是必须的因为号段不能重复重复了查询会返回多行业务层还得去重。ENGINEInnoDB不用犹豫MyISAM 虽然读快但不支持事务导入中途失败会留下脏数据。utf8mb4_general_ci排序规则对中文够用如果要做拼音排序再换utf8mb4_unicode_ci。导入数据时如果拿到的是 CSV 或 SQL 文件用LOAD DATA INFILE比逐条 INSERT 快一个数量级LOAD DATA LOCAL INFILE /path/to/phone_data.csv INTO TABLE phone_attribution FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (prefix, province, city, operator, area_code, post_code);LOCAL关键字允许从客户端读文件如果服务端和客户端不在同一台机器去掉 LOCAL 并把文件放到服务端 secure_file_priv 目录下。IGNORE 1 ROWS跳过 CSV 表头。导入前先把unique_checks关掉能再快一点导完再打开SET unique_checks 0; -- 执行 LOAD DATA SET unique_checks 1;注意关掉唯一性检查期间如果有重复号段导入不会报错但后续查询可能出问题。所以导入完成后要跑一次去重检查SELECT prefix, COUNT(*) AS cnt FROM phone_attribution GROUP BY prefix HAVING cnt 1;如果返回空说明数据干净。有重复的话用DELETE配合子查询清理保留 id 最小的那条。3. 查询接口怎么写从 SQL 到代码层的完整链路3.1 单条查询与批量查询的 SQL 差异单条查询很简单截取前七位去匹配SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix LEFT(13812345678, 7);LEFT函数在 MySQL 里对字符串操作很快但更好的做法是在应用层截取好再传进来避免数据库做函数计算导致索引失效。虽然LEFT用在等值查询的右侧不影响索引但养成习惯没坏处。批量查询是实际业务里更常见的场景。比如一次要查 1000 个手机号的归属地用IN比循环单查快得多SELECT prefix, province, city, operator FROM phone_attribution WHERE prefix IN (1381234, 1395678, 1501234, ...);但IN列表太长会撑爆 SQL 长度限制一般建议每批不超过 500 个。如果数据量再大用临时表 JOIN 的方式CREATE TEMPORARY TABLE tmp_prefix (prefix CHAR(7) PRIMARY KEY); INSERT INTO tmp_prefix VALUES (1381234), (1395678), ...; SELECT t.prefix, p.province, p.city, p.operator FROM tmp_prefix t LEFT JOIN phone_attribution p ON t.prefix p.prefix;临时表在会话结束时自动删除不会污染正式表。LEFT JOIN保证即使某个号段查不到也能返回行业务层可以标记为「未知归属地」。3.2 应用层封装Python 与 Java 的查询示例Python 用 pymysql 封装一个查询函数import pymysql def get_attribution(phone_number): prefix phone_number[:7] conn pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databasephone_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) try: with conn.cursor() as cursor: sql SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix %s cursor.execute(sql, (prefix,)) result cursor.fetchone() return result if result else {province: 未知, city: 未知, operator: 未知} finally: conn.close()这里用参数化查询%s而不是字符串拼接防止 SQL 注入。DictCursor让返回结果直接是字典省去手动映射字段。连接用完就关如果 QPS 高应该换成连接池比如 DBUtils 或 SQLAlchemy 的 pool。Java 用 JDBC 的写法类似public Attribution query(String phone) throws SQLException { String prefix phone.substring(0, 7); String sql SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, prefix); try (ResultSet rs ps.executeQuery()) { if (rs.next()) { Attribution attr new Attribution(); attr.setProvince(rs.getString(province)); attr.setCity(rs.getString(city)); attr.setOperator(rs.getString(operator)); attr.setAreaCode(rs.getString(area_code)); attr.setPostCode(rs.getString(post_code)); return attr; } } } return Attribution.unknown(); }PreparedStatement预编译 SQL既防注入又提升重复执行效率。dataSource用 HikariCP 或 Druid 都行连接池大小根据并发量调一般 10 到 20 个连接能扛住几百 QPS。3.3 缓存层Redis 把查询压到毫秒级MySQL 单表几十万行走唯一索引查询单次大概 1 到 3 毫秒。但如果 QPS 上千数据库连接池会成为瓶颈。加一层 Redis 缓存把热点号段的结果缓存起来能把响应压到 0.5 毫秒以内。缓存键用phone:attr:{prefix}值存 JSON 字符串过期时间设 7 天import json import redis r redis.Redis(host127.0.0.1, port6379, db0) def get_attribution_cached(phone_number): prefix phone_number[:7] cache_key fphone:attr:{prefix} cached r.get(cache_key) if cached: return json.loads(cached) result query_from_mysql(prefix) if result: r.setex(cache_key, 604800, json.dumps(result, ensure_asciiFalse)) return resultsetex的 604800 是 7 天秒数。ensure_asciiFalse让中文正常存储不然会变成\uXXXX转义。缓存穿透的问题——查一个不存在的号段每次都打到 MySQL——可以用空值缓存解决查不到也存一个{province: 未知}过期时间设短一点比如 1 小时。4. 避坑与排查导入、查询、性能的五个血泪教训4.1 导入时中文乱码查出来全是问号现象CSV 导入后province 和 city 字段显示为???或乱码。原因CSV 文件编码是 GBK而 MySQL 表是 utf8mb4LOAD DATA默认按表字符集解析文件导致中文被错误解码。解决导入前用iconv转码或者在LOAD DATA里指定字符集LOAD DATA LOCAL INFILE /path/to/phone_data.csv INTO TABLE phone_attribution CHARACTER SET gbk FIELDS TERMINATED BY , ...如果已经导入错了用ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4也救不回来只能清表重导。所以导入前先用file -i phone_data.csv确认编码别凭感觉。4.2 号段重复导致查询返回多行现象同一个手机号查出来两条记录省份还不一样。原因数据源本身有重复号段导入时没做唯一性校验或者unique_checks关掉后重复数据混进去了。解决先跑去重查询确认重复后清理DELETE t1 FROM phone_attribution t1 INNER JOIN phone_attribution t2 WHERE t1.prefix t2.prefix AND t1.id t2.id;这条 SQL 保留每个号段 id 最小的记录删掉其余的。执行前先SELECT确认影响行数别直接在生产库上跑。4.3 用 LIKE 模糊查询导致全表扫描现象查询变慢EXPLAIN显示typeALL。原因有人写WHERE prefix LIKE 138%虽然能走索引但如果写成LIKE %138%就废了。更常见的是直接拿完整手机号去查WHERE phone LIKE 1381234%而表里根本没有 phone 字段只能全表扫。解决永远用等值查询WHERE prefix 1381234别用 LIKE。如果业务需要按省份查给 province 单独建索引但注意区分度低全国就 34 个省份索引效果一般不如走缓存。4.4 连接池耗尽报错 Too many connections现象应用日志里频繁出现ERROR 1040 (HY000): Too many connections。原因每次查询都新建连接没关或者没复用连接数涨到 MySQL 的max_connections上限默认 151。解决用连接池Python 用 DBUtils.PooledDBJava 用 HikariCP。同时检查代码里有没有conn.close()漏掉的分支尤其是异常路径。临时可以调大max_connections但治标不治本SET GLOBAL max_connections 500;这个设置重启后失效要永久生效得改my.cnf里的max_connections。4.5 缓存与数据库不一致更新号段后查不到新数据现象数据表里更新了某个号段的归属地但接口返回的还是旧值。原因Redis 缓存没失效7 天过期时间太长数据变更后没主动删缓存。解决更新 MySQL 后立即删掉对应缓存键def update_attribution(prefix, province, city): # 更新 MySQL cursor.execute(UPDATE phone_attribution SET province%s, city%s WHERE prefix%s, (province, city, prefix)) conn.commit() # 删除缓存 r.delete(fphone:attr:{prefix})删缓存而不是更新缓存避免并发写导致脏数据。如果更新频繁考虑把过期时间缩短到 1 小时用时间换一致性。5. 进阶技巧用分区表和覆盖索引把查询再压一半数据量到千万级比如把物联网卡、虚拟号段都加进来单表 B 树深度增加查询会从 1 毫秒涨到 5 毫秒以上。这时候有两个优化方向分区表和覆盖索引。分区表按 prefix 首字母或省份做 HASH 分区把数据打散到不同物理文件ALTER TABLE phone_attribution PARTITION BY HASH(CRC32(prefix)) PARTITIONS 16;16 个分区每个分区大概几百万行B 树深度降下来查询更快。但分区表有坑唯一索引必须包含分区键所以uk_prefix得改成(prefix, id)或者直接去掉唯一约束靠应用层保证。我一般不建议在归属地这种场景用分区因为数据量还没大到那个程度维护成本反而高。更实用的优化是覆盖索引。如果查询只需要 province 和 city建一个联合索引ALTER TABLE phone_attribution ADD INDEX idx_prefix_cover (prefix, province, city, operator);这样查询SELECT province, city, operator FROM phone_attribution WHERE prefix 1381234时直接从索引里拿数据不用回表。EXPLAIN里Extra会显示Using index这就是覆盖索引生效的标志。代价是索引占空间写入稍慢但归属地库基本是读多写少划算。还有一个技巧是用MEMORY引擎做热数据表。把最近三个月查询频率最高的 10 万个号段放到内存表里查询先走内存表没有再查 InnoDB 表CREATE TABLE phone_attribution_hot ( prefix CHAR(7) NOT NULL, province VARCHAR(20) NOT NULL, city VARCHAR(30) NOT NULL, PRIMARY KEY (prefix) ) ENGINEMEMORY DEFAULT CHARSETutf8mb4;内存表重启后数据丢失所以要用定时任务从 InnoDB 表同步。查询逻辑改成先查 hot 表UNION ALL查主表或者应用层做两级查询。这个方案能把热点查询压到 0.1 毫秒但内存表不支持 TEXT/BLOB字段长度也有限制设计时注意。最后说个验证方法用BENCHMARK函数测查询性能或者开slow_query_log抓慢查询。我习惯在导入数据后跑一轮压测用mysqlslap模拟 100 并发查 1000 次看 P99 延迟。如果超过 10 毫秒就得检查索引和缓存了。这套方案我前后搭过三次每次踩的坑都差不多——编码、重复、索引失效。数据本身不复杂难的是把导入、查询、缓存、更新这条链路串稳。希望帮到你。本文还有配套的精品资源点击获取
手机号归属地查询：MySQL数据表设计与查询优化实战
简介这份资源是一份手机号码归属地查询数据库以MySQL的SQL文件形式提供适合从事数据分析、营销系统开发、客户服务支撑的开发者与数据库学习者使用。包内共1个文件为phone_msg.sql压缩包约2.22MB导入MySQL后即可通过手机号字段关联查询对应的省份与城市信息数据覆盖较为完整可用于批量归属地匹配、区域用户统计等场景。已有267人学习下载。资源价值在于省去自行采集与整理号码段归属关系的成本可直接用于SQL查询练习、JOIN与GROUP BY等聚合分析实战也能作为数据脱敏、权限控制与个人信息合规使用的教学案例。需要提醒的是涉及个人敏感信息时应遵守《个人信息保护法》在合法合规前提下使用并做好加密存储与访问限制。1. 手机号归属地查询从一份 MySQL 数据表到可上线的接口手上有个用户表几十万行手机号运营要按省份做短信分流风控要按城市判断异常登录。你第一反应可能是调第三方 API但量一上来按次计费的成本和网络延迟都让人难受。这时候一份本地的手机号归属地 MySQL 数据表就成了刚需——标题里说的「非常全淘宝50元买的」本质就是一张覆盖号段、省份、城市、运营商、区号、邮编的映射表。它解决的是「离线、批量、零调用成本」的查询问题适合做后台批处理、数据清洗、用户画像补全的开发者。这篇不讲虚的从建表、导入、索引设计到查询优化把这条链路走通顺带把几个容易翻车的地方说清楚。2. 手机号归属地数据的结构号段、省份、城市怎么对应2.1 手机号前七位才是归属地的钥匙很多人以为手机号归属地是按前三位查的这是个常见误解。前三位只代表运营商比如 138 是移动真正决定省份和城市的是前七位。中国手机号是 11 位结构是「3 位网络识别号 4 位地区编码 4 位用户号码」。归属地库的核心就是那 4 位地区编码它和省份、城市一一对应。所以一张标准的归属地表主键或唯一索引应该建在号段前七位上而不是完整手机号。完整手机号有 11 位前七位相同意味着归属地相同用前七位做键能把数据量压缩到几十万行级别查询时也只需要截取前七位去匹配效率高得多。常见的数据表字段设计如下字段名类型说明idINT UNSIGNED AUTO_INCREMENT主键prefixCHAR(7)号段前七位唯一索引provinceVARCHAR(20)省份cityVARCHAR(30)城市operatorVARCHAR(20)运营商area_codeVARCHAR(6)区号post_codeVARCHAR(6)邮编这张表看起来简单但字段长度和字符集选错后面查询和存储都会出问题。province 和 city 用 utf8mb4 是稳妥的虽然归属地基本都是中文但有些城市名带生僻字utf8 三字节可能不够。prefix 用 CHAR(7) 而不是 VARCHAR因为长度固定CHAR 在索引里更紧凑。2.2 建表语句与索引策略直接上建表 SQL注意字符集和索引的写法CREATE TABLE phone_attribution ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, prefix CHAR(7) NOT NULL COMMENT 号段前七位, province VARCHAR(20) NOT NULL DEFAULT COMMENT 省份, city VARCHAR(30) NOT NULL DEFAULT COMMENT 城市, operator VARCHAR(20) NOT NULL DEFAULT COMMENT 运营商, area_code VARCHAR(6) NOT NULL DEFAULT COMMENT 区号, post_code VARCHAR(6) NOT NULL DEFAULT COMMENT 邮编, PRIMARY KEY (id), UNIQUE KEY uk_prefix (prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT手机号归属地库;这里有几个参数值得说。UNIQUE KEY uk_prefix是必须的因为号段不能重复重复了查询会返回多行业务层还得去重。ENGINEInnoDB不用犹豫MyISAM 虽然读快但不支持事务导入中途失败会留下脏数据。utf8mb4_general_ci排序规则对中文够用如果要做拼音排序再换utf8mb4_unicode_ci。导入数据时如果拿到的是 CSV 或 SQL 文件用LOAD DATA INFILE比逐条 INSERT 快一个数量级LOAD DATA LOCAL INFILE /path/to/phone_data.csv INTO TABLE phone_attribution FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (prefix, province, city, operator, area_code, post_code);LOCAL关键字允许从客户端读文件如果服务端和客户端不在同一台机器去掉 LOCAL 并把文件放到服务端 secure_file_priv 目录下。IGNORE 1 ROWS跳过 CSV 表头。导入前先把unique_checks关掉能再快一点导完再打开SET unique_checks 0; -- 执行 LOAD DATA SET unique_checks 1;注意关掉唯一性检查期间如果有重复号段导入不会报错但后续查询可能出问题。所以导入完成后要跑一次去重检查SELECT prefix, COUNT(*) AS cnt FROM phone_attribution GROUP BY prefix HAVING cnt 1;如果返回空说明数据干净。有重复的话用DELETE配合子查询清理保留 id 最小的那条。3. 查询接口怎么写从 SQL 到代码层的完整链路3.1 单条查询与批量查询的 SQL 差异单条查询很简单截取前七位去匹配SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix LEFT(13812345678, 7);LEFT函数在 MySQL 里对字符串操作很快但更好的做法是在应用层截取好再传进来避免数据库做函数计算导致索引失效。虽然LEFT用在等值查询的右侧不影响索引但养成习惯没坏处。批量查询是实际业务里更常见的场景。比如一次要查 1000 个手机号的归属地用IN比循环单查快得多SELECT prefix, province, city, operator FROM phone_attribution WHERE prefix IN (1381234, 1395678, 1501234, ...);但IN列表太长会撑爆 SQL 长度限制一般建议每批不超过 500 个。如果数据量再大用临时表 JOIN 的方式CREATE TEMPORARY TABLE tmp_prefix (prefix CHAR(7) PRIMARY KEY); INSERT INTO tmp_prefix VALUES (1381234), (1395678), ...; SELECT t.prefix, p.province, p.city, p.operator FROM tmp_prefix t LEFT JOIN phone_attribution p ON t.prefix p.prefix;临时表在会话结束时自动删除不会污染正式表。LEFT JOIN保证即使某个号段查不到也能返回行业务层可以标记为「未知归属地」。3.2 应用层封装Python 与 Java 的查询示例Python 用 pymysql 封装一个查询函数import pymysql def get_attribution(phone_number): prefix phone_number[:7] conn pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databasephone_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) try: with conn.cursor() as cursor: sql SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix %s cursor.execute(sql, (prefix,)) result cursor.fetchone() return result if result else {province: 未知, city: 未知, operator: 未知} finally: conn.close()这里用参数化查询%s而不是字符串拼接防止 SQL 注入。DictCursor让返回结果直接是字典省去手动映射字段。连接用完就关如果 QPS 高应该换成连接池比如 DBUtils 或 SQLAlchemy 的 pool。Java 用 JDBC 的写法类似public Attribution query(String phone) throws SQLException { String prefix phone.substring(0, 7); String sql SELECT province, city, operator, area_code, post_code FROM phone_attribution WHERE prefix ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, prefix); try (ResultSet rs ps.executeQuery()) { if (rs.next()) { Attribution attr new Attribution(); attr.setProvince(rs.getString(province)); attr.setCity(rs.getString(city)); attr.setOperator(rs.getString(operator)); attr.setAreaCode(rs.getString(area_code)); attr.setPostCode(rs.getString(post_code)); return attr; } } } return Attribution.unknown(); }PreparedStatement预编译 SQL既防注入又提升重复执行效率。dataSource用 HikariCP 或 Druid 都行连接池大小根据并发量调一般 10 到 20 个连接能扛住几百 QPS。3.3 缓存层Redis 把查询压到毫秒级MySQL 单表几十万行走唯一索引查询单次大概 1 到 3 毫秒。但如果 QPS 上千数据库连接池会成为瓶颈。加一层 Redis 缓存把热点号段的结果缓存起来能把响应压到 0.5 毫秒以内。缓存键用phone:attr:{prefix}值存 JSON 字符串过期时间设 7 天import json import redis r redis.Redis(host127.0.0.1, port6379, db0) def get_attribution_cached(phone_number): prefix phone_number[:7] cache_key fphone:attr:{prefix} cached r.get(cache_key) if cached: return json.loads(cached) result query_from_mysql(prefix) if result: r.setex(cache_key, 604800, json.dumps(result, ensure_asciiFalse)) return resultsetex的 604800 是 7 天秒数。ensure_asciiFalse让中文正常存储不然会变成\uXXXX转义。缓存穿透的问题——查一个不存在的号段每次都打到 MySQL——可以用空值缓存解决查不到也存一个{province: 未知}过期时间设短一点比如 1 小时。4. 避坑与排查导入、查询、性能的五个血泪教训4.1 导入时中文乱码查出来全是问号现象CSV 导入后province 和 city 字段显示为???或乱码。原因CSV 文件编码是 GBK而 MySQL 表是 utf8mb4LOAD DATA默认按表字符集解析文件导致中文被错误解码。解决导入前用iconv转码或者在LOAD DATA里指定字符集LOAD DATA LOCAL INFILE /path/to/phone_data.csv INTO TABLE phone_attribution CHARACTER SET gbk FIELDS TERMINATED BY , ...如果已经导入错了用ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4也救不回来只能清表重导。所以导入前先用file -i phone_data.csv确认编码别凭感觉。4.2 号段重复导致查询返回多行现象同一个手机号查出来两条记录省份还不一样。原因数据源本身有重复号段导入时没做唯一性校验或者unique_checks关掉后重复数据混进去了。解决先跑去重查询确认重复后清理DELETE t1 FROM phone_attribution t1 INNER JOIN phone_attribution t2 WHERE t1.prefix t2.prefix AND t1.id t2.id;这条 SQL 保留每个号段 id 最小的记录删掉其余的。执行前先SELECT确认影响行数别直接在生产库上跑。4.3 用 LIKE 模糊查询导致全表扫描现象查询变慢EXPLAIN显示typeALL。原因有人写WHERE prefix LIKE 138%虽然能走索引但如果写成LIKE %138%就废了。更常见的是直接拿完整手机号去查WHERE phone LIKE 1381234%而表里根本没有 phone 字段只能全表扫。解决永远用等值查询WHERE prefix 1381234别用 LIKE。如果业务需要按省份查给 province 单独建索引但注意区分度低全国就 34 个省份索引效果一般不如走缓存。4.4 连接池耗尽报错 Too many connections现象应用日志里频繁出现ERROR 1040 (HY000): Too many connections。原因每次查询都新建连接没关或者没复用连接数涨到 MySQL 的max_connections上限默认 151。解决用连接池Python 用 DBUtils.PooledDBJava 用 HikariCP。同时检查代码里有没有conn.close()漏掉的分支尤其是异常路径。临时可以调大max_connections但治标不治本SET GLOBAL max_connections 500;这个设置重启后失效要永久生效得改my.cnf里的max_connections。4.5 缓存与数据库不一致更新号段后查不到新数据现象数据表里更新了某个号段的归属地但接口返回的还是旧值。原因Redis 缓存没失效7 天过期时间太长数据变更后没主动删缓存。解决更新 MySQL 后立即删掉对应缓存键def update_attribution(prefix, province, city): # 更新 MySQL cursor.execute(UPDATE phone_attribution SET province%s, city%s WHERE prefix%s, (province, city, prefix)) conn.commit() # 删除缓存 r.delete(fphone:attr:{prefix})删缓存而不是更新缓存避免并发写导致脏数据。如果更新频繁考虑把过期时间缩短到 1 小时用时间换一致性。5. 进阶技巧用分区表和覆盖索引把查询再压一半数据量到千万级比如把物联网卡、虚拟号段都加进来单表 B 树深度增加查询会从 1 毫秒涨到 5 毫秒以上。这时候有两个优化方向分区表和覆盖索引。分区表按 prefix 首字母或省份做 HASH 分区把数据打散到不同物理文件ALTER TABLE phone_attribution PARTITION BY HASH(CRC32(prefix)) PARTITIONS 16;16 个分区每个分区大概几百万行B 树深度降下来查询更快。但分区表有坑唯一索引必须包含分区键所以uk_prefix得改成(prefix, id)或者直接去掉唯一约束靠应用层保证。我一般不建议在归属地这种场景用分区因为数据量还没大到那个程度维护成本反而高。更实用的优化是覆盖索引。如果查询只需要 province 和 city建一个联合索引ALTER TABLE phone_attribution ADD INDEX idx_prefix_cover (prefix, province, city, operator);这样查询SELECT province, city, operator FROM phone_attribution WHERE prefix 1381234时直接从索引里拿数据不用回表。EXPLAIN里Extra会显示Using index这就是覆盖索引生效的标志。代价是索引占空间写入稍慢但归属地库基本是读多写少划算。还有一个技巧是用MEMORY引擎做热数据表。把最近三个月查询频率最高的 10 万个号段放到内存表里查询先走内存表没有再查 InnoDB 表CREATE TABLE phone_attribution_hot ( prefix CHAR(7) NOT NULL, province VARCHAR(20) NOT NULL, city VARCHAR(30) NOT NULL, PRIMARY KEY (prefix) ) ENGINEMEMORY DEFAULT CHARSETutf8mb4;内存表重启后数据丢失所以要用定时任务从 InnoDB 表同步。查询逻辑改成先查 hot 表UNION ALL查主表或者应用层做两级查询。这个方案能把热点查询压到 0.1 毫秒但内存表不支持 TEXT/BLOB字段长度也有限制设计时注意。最后说个验证方法用BENCHMARK函数测查询性能或者开slow_query_log抓慢查询。我习惯在导入数据后跑一轮压测用mysqlslap模拟 100 并发查 1000 次看 P99 延迟。如果超过 10 毫秒就得检查索引和缓存了。这套方案我前后搭过三次每次踩的坑都差不多——编码、重复、索引失效。数据本身不复杂难的是把导入、查询、缓存、更新这条链路串稳。希望帮到你。本文还有配套的精品资源点击获取
