MySQL作为常用的关系型数据库，数据查询是其核心使用场景之一，查询效率的高低直接决定了业务系统的运行流畅度。想要实现高效的数据查询，需要从多个层面进行调整和优化，而非单一维度的修改。
合理设计和使用索引
索引是提升MySQL查询效率最有效的手段之一，它类似于书籍的目录，可以帮助数据库快速定位到需要的数据行，避免全表扫描。
索引设计原则
- 优先为查询条件、连接条件、排序和分组字段创建索引
- 避免创建过多冗余索引，每个索引都会占用额外的存储空间，还会降低写入性能
- 尽量选择区分度高的字段作为索引，比如用户表的用户ID比性别字段更适合做索引
- 联合索引要遵循最左前缀原则，比如联合索引是
(a,b,c)，那么查询条件包含a、a和b、a和b和c时才能命中索引
索引使用示例
假设有一张用户表user，结构如下：
-- 创建用户表 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL COMMENT '用户名', `age` int(11) DEFAULT NULL COMMENT '年龄', `create_time` datetime DEFAULT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
如果经常需要根据用户名查询用户信息，可以为username字段创建普通索引：
-- 为username字段创建索引 CREATE INDEX idx_username ON `user`(`username`);
如果经常需要按年龄查询并排序，可以创建联合索引：
-- 创建age和create_time的联合索引 CREATE INDEX idx_age_create_time ON `user`(`age`,`create_time`);
优化SQL查询语句
即使有合适的索引，编写不合理的SQL语句也会导致查询效率低下，需要遵循以下编写规范。
避免使用SELECT *
查询时尽量指定需要的字段，不要使用SELECT *，这样可以减少数据传输量，也避免查询不需要的字段导致无法命中覆盖索引。
低效写法：
SELECT * FROM `user` WHERE `age` = 20;
高效写法：
SELECT `id`,`username`,`age` FROM `user` WHERE `age` = 20;
避免使用函数或表达式对索引字段进行计算
如果在查询条件中对索引字段使用函数或者运算，会导致索引失效，触发全表扫描。
低效写法（假设create_time有索引）：
-- 对索引字段使用函数，索引失效 SELECT * FROM `user` WHERE YEAR(`create_time`) = 2024;
高效写法：
-- 范围查询可以命中索引 SELECT * FROM `user` WHERE `create_time` >= '2024-01-01 00:00:00' AND `create_time` < '2025-01-01 00:00:00';
合理使用连接查询代替子查询
子查询很多时候性能不如连接查询，尤其是嵌套子查询，会产生临时表，增加查询开销。
低效子查询写法：
SELECT * FROM `user` WHERE `id` IN (SELECT `user_id` FROM `order` WHERE `amount` > 100);
高效连接查询写法：
SELECT u.* FROM `user` u INNER JOIN `order` o ON u.`id` = o.`user_id` WHERE o.`amount` > 100;
分析查询执行计划
当查询效率不符合预期时，可以通过EXPLAIN命令查看查询的执行计划，定位性能瓶颈。
使用方法是在查询语句前加上EXPLAIN关键字：
EXPLAIN SELECT `id`,`username` FROM `user` WHERE `age` = 20 ORDER BY `create_time` DESC;
执行后会返回如下关键字段，需要重点关注：
| 字段名 | 含义 | 优化建议 |
|---|---|---|
| type | 访问类型，性能从好到坏为system>const>eq_ref>ref>range>index>ALL | 尽量避免出现ALL（全表扫描），尽量达到ref及以上级别 |
| key | 实际使用的索引 | 如果为NULL说明没有使用索引，需要检查索引设计 |
| rows | 预估扫描的行数 | 行数越少说明查询效率越高 |
| Extra | 额外信息 | 避免出现Using filesort、Using temporary，说明需要优化排序或临时表 |
其他优化建议
- 控制单表数据量，当单表数据超过千万级时，可以考虑分库分表
- 避免使用
LIKE '%关键词%'这样的模糊查询，会导致索引失效，如果必须使用可以考虑全文索引 - 合理设置
innodb_buffer_pool_size参数，让更多的数据和索引缓存在内存中，减少磁盘IO - 定期清理无用数据，避免表数据过度膨胀影响查询性能