MySQL查询优化指南：从分页到关联，全面提升性能
在开发基于MySQL数据库的应用时，查询性能的优化是绕不开的关键话题。优化得当，系统响应迅速，用户体验绝佳；反之，则可能导致系统卡顿，甚至影响业务正常运转。今天，就带大家深入MySQL查询优化的世界，从分页查询、关联查询，到in和exists的运用、count(*)查询，以及数据类型选择，全方位探索优化技巧。
分页查询优化
在业务系统中，分页查询极为常见。假设有如下员工表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
常见的分页查询语句可能是select * from employees limit 10000,10;，看似只取10条记录，实则先读取10010条，再抛弃前10000条，查询大表靠后数据时效率极低。
根据自增且连续主键排序的分页查询
对于自增且连续主键的表，如select * from employees limit 90000,5;，可优化为select * from employees where id > 90000 limit 5;。对比执行计划，优化后的SQL走索引，扫描行数大幅减少，效率更高。但此方法有前提条件：主键自增且连续，结果按主键排序。若主键不连续或原SQL按非主键字段排序，改写后结果可能不一致。
根据非主键字段排序的分页查询
当按非主键字段排序分页时，如select * from employees ORDER BY name limit 90000,5;，执行计划显示未使用name字段索引。原因是扫描整个索引并查找无索引行的成本高于全表扫描。优化关键是减少排序时返回的字段，先查出主键，再根据主键查对应记录：
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
优化后执行时间减半以上，且从filesort排序变为索引排序。
Join关联查询优化
假设有两张相似的表t1和t2：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
并插入了一定数量的数据。MySQL表关联有两种常见算法。
嵌套循环连接（NLJ）算法
NLJ算法逐行从驱动表读取数据，根据关联字段在被驱动表中查找匹配行，再合并结果。例如select * from t1 inner join t2 on t1.a = t2.a;，执行计划可判断驱动表和被驱动表，优化器一般选小表做驱动表。若被驱动表关联字段无索引，NLJ算法性能低，MySQL会选择BNL算法 。
基于块的嵌套循环连接（BNL）算法
BNL算法将驱动表数据读入join_buffer，再扫描被驱动表与join_buffer中的数据对比。如select * from t1 inner join t2 on t1.b = t2.b;，Extra中Using join buffer (Block Nested Loop)表明使用该算法。若驱动表数据量过大，join_buffer放不下，会分段处理，增加扫描次数。被驱动表关联字段无索引时，BNL算法磁盘扫描次数少，内存计算快，因此MySQL通常会选择它；若有索引，则NLJ算法性能更优。
关联SQL的优化策略
- 关联字段加索引：让MySQL在
join操作时优先选择NLJ算法 ，提升性能。 - 小表驱动大表：明确小表时，可用
straight_join固定连接驱动方式，如select * from t2 straight_join t1 on t2.a = t1.a;。但straight_join仅适用于inner join，使用时需谨慎，多数情况下MySQL优化器比人为指定更靠谱。 - 正确定义小表：决定驱动表时，应按各自条件过滤后，计算参与
join字段的总数据量，数据量小的表作为驱动表。
in和exists优化
优化原则是小表驱动大表。当B表数据集小于A表时，in优于exists：
select * from A where id in (select id from B)
当A表数据集小于B表时，exists优于in：
select * from A where exists (select 1 from B where B.id = A.id)
需注意，EXISTS (subquery)只返回TRUE或FALSE，子查询中SELECT *可用SELECT 1替换；其实际执行过程可能经过优化；EXISTS子查询也可用JOIN代替，具体需具体分析。
count(*)查询优化
执行count查询时，不同写法执行效率有差异。字段有索引时，count(*)≈count(1)>count(字段)>count(主键 id)；字段无索引时，count(*)≈count(1)>count(主键 id)>count(字段) 。count(1)比count(字段)快，因为无需取出字段。count(*)经过优化，按行累加，效率高，无需用其他方式替代。
常见优化方法有：
- 查询MySQL维护的总行数：
myisam存储引擎表的总行数存储在磁盘，查询快；innodb存储引擎表因MVCC机制需实时计算。 - 使用
show table status：可获取表总行数估计值，性能高。 - 维护到Redis：插入或删除数据时同步维护Redis中表总行数，但难以保证事务一致性。
- 增加计数表：在同一事务中维护计数表，确保数据准确。
MySQL数据类型选择优化
选择合适数据类型对性能至关重要，一般分两步：确定大类型（数字、字符串、时间、二进制），再确定具体类型（有无符号、取值范围、变长定长等）。尽量用小数据类型，字段定义为NOT NULL，避免NULL。
数值类型
数值类型如TINYINT、SMALLINT等各有特点和适用场景。例如，无负数的整形数据（如ID号）建议用UNSIGNED无符号类型扩大容量；用TINYINT代替ENUM、BITENUM、SET ；避免指定整数显示宽度；DECIMAL适合保存准确度要求高且用于计算的数据，使用时注意长度设置；优先用整形存储和运算实数。
日期和时间类型
日期和时间类型包括DATE、TIME等。建议用DATE保存日期，用MySQL内建类型存储时间，而非字符串。TIMESTAMP和DATETIME可使用CURRENT_TIMESTAMP作为默认值，TIMESTAMP更节约空间但有时间上限，DATETIME与时区无关。
字符串类型
字符串类型有CHAR和VARCHAR等。字符串长度差异大用VARCHAR，长度接近用CHAR。避免用BLOB和TEXT，若使用可单独存表关联。BLOB存储二进制字符串与字符集无关，TEXT存储非二进制字符串与字符集相关，且二者都无默认值。
MySQL查询优化是个系统工程，涉及多种场景和方法。通过合理运用这些优化技巧，能显著提升数据库性能，为应用的稳定高效运行提供有力保障。希望大家在实际开发中不断实践，让MySQL数据库发挥出最大潜力。
