提高MySQL查询速度那些实用但常被忽视的小技巧分享

很多人一提到数据库优化，就会立刻想到加索引、分库分表这些“大招”，这些方法确实有效，但它们往往工程浩大，或者需要深厚的专业知识，在日常开发中，有很多简单易行、容易被忽略的小技巧，能在不进行大规模改动的情况下，显著提升查询速度，这些技巧的关键在于，它们是从“避免做傻事”和“让数据库工作得更轻松”的角度出发的。

*第一点，谨慎使用SELECT ，只获取你需要的列。**

这是一个老生常谈的问题，但至今仍然有大量开发者习惯性地使用SELECT *，根据数据库设计原则，当你使用SELECT *时，数据库需要读取整行数据，包括那些你根本用不上的TEXT、BLOB等大字段，这会导致几个问题：增加了网络传输的数据量，尤其是在应用程序与数据库服务器分开部署时，这个开销非常可观，如果表很宽（列很多），数据库需要从磁盘读取更多的数据到内存，这挤占了本可以用于缓存其他热门数据的宝贵内存空间，一个具体的例子是，如果你的用户表有头像（二进制大对象）和个人简介（长文本）等字段，但在展示用户列表时根本不需要这些信息，那么使用SELECT id, username, email会比SELECT *快得多，也轻量得多,MySQL官方文档在优化查询部分也多次建议明确指定需要的列。

第二点，善用LIMIT分页，尤其是避免大偏移量的OFFSET。

我们在做分页查询时，最常写的语句可能是SELECT ... LIMIT 1000, 20，意思是跳过前1000条，取接下来的20条，当偏移量很小的时候，这没有问题，但当偏移量达到数万甚至数十万时，数据库的实际操作是：先需要读取并排序（如果有序的话）100020条数据，然后抛弃前1000条，最后返回20条，这个“跳过”的动作成本非常高，一个很实用的技巧是使用“游标分页”或“基于值的分页”，如果你是按ID倒序排列，上一页的最后一条记录的ID是5000，那么下一页的查询可以写成SELECT ... WHERE id < 5000 ORDER BY id DESC LIMIT 20，这样，数据库可以利用ID上的索引直接定位到ID小于5000的位置开始扫描，效率极高，完全避免了巨大的偏移计算，许多高性能应用，如社交媒体信息流,都采用这种方式。

第三点，用EXPLAIN看看你的查询到底是怎么执行的。

EXPLAIN命令可能是MySQL提供给开发者最强大的免费诊断工具，但很多人觉得它输出复杂就懒得用，其实你不需要完全理解所有输出项，只需要关注几个关键点就够了，在你的SQL语句前加上EXPLAIN然后执行，重点关注“type”列和“Extra”列，type”显示的是“ALL”，那意味着全表扫描，这是需要警惕的信号，通常意味着你需要为查询条件添加索引，Extra”列出现了“Using filesort”或“Using temporary”，说明MySQL正在执行昂贵的文件排序或创建临时表，对于大数据集来说这很慢，这时你就需要考虑是否可以优化排序字段的索引，或者重构查询，养成在编写重要查询前用EXPLAIN验证一下的习惯,能帮你提前发现很多性能陷阱。

第四点，批量操作代替循环逐条操作。

在应用程序中，有时我们需要插入或更新多条数据，一个常见的反模式是在程序循环中，一次次地执行单条INSERT或UPDATE语句，每一次数据库操作都伴随着网络round-trip和SQL解析的开销，正确的做法是尽量使用批量操作，插入多条数据时，使用INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6)...的形式，将成百上千条数据合并为一条SQL语句，对于更新，也可以使用CASE WHEN语句或批量更新框架来减少交互次数，这种方式的提升是数量级的,因为它极大地减少了网络和解析的损耗。

第五点，为查询创建合适的复合索引，而不仅仅是单列索引。

大家都知道为经常用于查询条件的列建索引，但常常忽略了复合索引（也叫多列索引）的威力，复合索引的顺序至关重要，它遵循“最左前缀”原则，举个例子，如果你经常同时按country和city来查询用户，那么一个(country, city)的复合索引，会比分别建country索引和city索引效果要好得多，因为数据库可以一次性利用这个索引定位到特定国家下的特定城市，而如果你只查询city，这个复合索引是无法被使用的（因为不满足最左前缀），分析你的高频查询模式，设计合理的复合索引,能让索引的效力倍增。

第六点，处理NULL值和默认值时的索引考量。

在数据库中，NULL是一个特殊的值，它意味着“未知”，对于包含NULL值的列，索引的行为会有所不同，在某些情况下，如果一个列经常用于WHERE column IS NULL或WHERE column IS NOT NULL的查询，并且该列的值大部分是NULL或大部分不是NULL，那么为这个列建立索引可能是有效的，但反过来，如果一个列有默认值（比如0或空字符串），并且查询条件通常是WHERE column = default_value，那么如果这个默认值占据了数据的绝大多数，数据库优化器可能会认为全表扫描比走索引更划算，因为索引扫描后还需要回表查询大量数据，了解你的数据分布,对于决定是否对这类列建索引很有帮助。

第七点，避免在索引列上使用函数或表达式。

这是一个容易无意中犯的错误，你有一个create_time的索引，是DATETIME类型，如果你这样查询：WHERE DATE(create_time) = '2023-10-01'，那么这个索引大概率会失效，因为数据库需要对每一行的create_time值应用DATE()函数后才能做比较，这就导致了全表扫描，正确的写法是使用范围查询：WHERE create_time >= '2023-10-01 00:00:00' AND create_time < '2023-10-02 00:00:00'，这样，数据库就可以高效地使用create_time上的索引了，同样，对索引列进行数学运算、字符串拼接等操作,都会导致索引失效。

数据库优化并非总是需要“大刀阔斧”，从这些编码细节和习惯入手，关注查询语句本身的质量，往往能以最小的代价获得意想不到的性能提升，最重要的是培养一种性能意识，在写下每一行SQL时,都思考一下数据库在背后需要为此付出多少工作量。