MySQL条件查询与范围查询：从基础到进阶的完整指南

一、条件查询：WHERE子句的核心机制

1.1 基础比较运算符

MySQL条件查询的核心是WHERE子句，它通过逻辑表达式筛选符合条件的记录。基础比较运算符包括：

• =：精确匹配（如WHERE age = 25）
• <> 或 !=：不等于（如WHERE status <> 'inactive'）
• >、<、>=、<=：数值范围比较（如WHERE salary > 5000）
• BETWEEN：闭区间范围（如WHERE score BETWEEN 60 AND 90）

性能优化建议：对数值列使用BETWEEN比>= AND <=组合更高效，因为优化器能更好识别范围边界。

1.2 逻辑运算符组合

复杂条件可通过逻辑运算符组合：

• AND：同时满足多个条件（如WHERE age > 18 AND gender = 'M'）
• OR：满足任一条件（如WHERE department = 'IT' OR department = 'HR'）
• NOT：取反条件（如WHERE NOT is_deleted = 1）

实战技巧：使用括号明确优先级（如WHERE (age < 20 OR age > 60) AND status = 'active'），避免因运算符优先级导致的逻辑错误。

1.3 IN与NOT IN操作符

IN操作符简化多值匹配：

SELECT * FROM products 
WHERE category_id IN (1, 3, 5);

等价于：

SELECT * FROM products 
WHERE category_id = 1 OR category_id = 3 OR category_id = 5;

性能对比：当IN列表超过10个值时，MySQL会优化为临时表查询，但过长的列表（如1000+）仍可能导致性能下降。此时建议改用JOIN关联子查询。

二、范围查询：精准控制数据边界

2.1 BETWEEN的深度应用

BETWEEN不仅限于数值，还可用于日期和字符串：

-- 日期范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
-- 字符串范围查询（按字典序）
SELECT * FROM customers 
WHERE last_name BETWEEN 'A' AND 'M';

注意事项：日期范围查询需确保字段类型匹配，字符串范围查询可能因字符集不同产生意外结果。

2.2 LIKE与通配符的模糊匹配

LIKE操作符支持两种通配符：

• %：匹配任意长度字符（如WHERE name LIKE '张%'）
• _：匹配单个字符（如WHERE phone LIKE '138_1234'）

性能优化：

1. 避免前导通配符（如LIKE '%张'），这会导致全表扫描
2. 对高频查询的列创建函数索引（MySQL 8.0+支持）
3. 考虑使用全文索引替代LIKE进行长文本搜索

2.3 正则表达式的高级匹配

MySQL通过REGEXP或RLIKE支持正则表达式：

SELECT * FROM products 
WHERE product_name REGEXP '^[A-Z]';  -- 以大写字母开头

适用场景：复杂模式匹配（如邮箱格式验证、电话号码校验），但性能开销较大，建议仅在必要时使用。

三、组合查询：构建复杂筛选逻辑

3.1 多条件嵌套查询

通过嵌套WHERE子句实现复杂逻辑：

SELECT * FROM employees 
WHERE (department = 'Sales' AND hire_date > '2020-01-01')
   OR (department = 'Engineering' AND salary > 10000);

优化策略：

1. 将高选择性条件放在前面
2. 避免在嵌套条件中使用函数（如WHERE YEAR(hire_date) = 2023），这会导致索引失效

3.2 EXISTS与子查询

EXISTS通过子查询结果判断：

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.total > 1000
);

性能对比：当子查询结果集较小时，EXISTS比IN更高效；当子查询结果集较大时，IN可能表现更好。

四、性能优化：从查询到执行计划

4.1 索引利用分析

使用EXPLAIN分析查询执行计划：

EXPLAIN SELECT * FROM users 
WHERE age BETWEEN 20 AND 30 AND status = 'active';

关键指标：

• type：访问类型（ALL表示全表扫描，range表示范围扫描）
• key：实际使用的索引
• rows：预估扫描行数

4.2 索引设计原则

1. 复合索引顺序：将等值查询列放在前面，范围查询列放在后面

   ALTER TABLE users ADD INDEX idx_age_status (age, status);

2. 避免索引失效：不在索引列上使用函数或计算

   -- 错误示例：索引失效
   SELECT * FROM users WHERE YEAR(create_time) = 2023;
   -- 正确写法：使用范围查询
   SELECT * FROM users 
   WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

4.3 分页查询优化

大数据量分页时，避免使用LIMIT offset, size：

-- 低效写法（offset越大越慢）
SELECT * FROM logs ORDER BY create_time LIMIT 100000, 10;
-- 高效写法（使用索引列过滤）
SELECT * FROM logs 
WHERE create_time > '2023-01-01 12:00:00' 
ORDER BY create_time LIMIT 10;

五、实战案例：电商系统查询优化

案例1：价格区间查询

-- 原始查询（全表扫描）
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;
-- 优化后（使用索引）
ALTER TABLE products ADD INDEX idx_price (price);
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
ORDER BY price LIMIT 20;

案例2：多条件组合查询

-- 原始查询（索引未充分利用）
SELECT * FROM orders 
WHERE customer_id = 123 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status = 'completed';
-- 优化后（创建复合索引）
ALTER TABLE orders ADD INDEX idx_customer_date_status 
(customer_id, order_date, status);

六、常见误区与解决方案

误区1：过度使用OR条件

-- 低效写法
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Appliances';
-- 高效写法
SELECT * FROM products 
WHERE category IN ('Electronics', 'Appliances');

误区2：忽略NULL值处理

-- 错误示例：NULL值不会被包含
SELECT * FROM users WHERE phone IS NOT NULL;
-- 正确处理：明确NULL检查
SELECT * FROM users 
WHERE (phone IS NOT NULL AND phone <> '') 
   OR (phone IS NULL AND alternative_phone IS NOT NULL);

七、进阶技巧：JSON与地理空间查询

7.1 JSON字段查询

MySQL 5.7+支持JSON数据类型：

-- 查询JSON字段中的属性
SELECT * FROM products 
WHERE JSON_EXTRACT(specs, '$.color') = 'red';
-- 简写语法
SELECT * FROM products 
WHERE specs->>'$.color' = 'red';

7.2 地理空间查询

使用空间数据类型和函数：

-- 创建空间索引
ALTER TABLE stores ADD SPATIAL INDEX(location);
-- 查询附近商店（单位：度，约111km/度）
SELECT * FROM stores 
WHERE ST_Distance_Sphere(
    location, 
    POINT(116.404, 39.915)  -- 北京坐标
) < 5000;  -- 5公里内

八、总结与最佳实践

1. 索引优先：为WHERE、JOIN、ORDER BY涉及的列创建适当索引
2. 避免全表扫描：确保查询能利用索引范围扫描
3. 简化条件逻辑：用IN替代多个OR，用EXISTS替代复杂关联
4. 定期分析执行计划：使用EXPLAIN验证查询优化效果
5. 考虑数据分布：对高频查询条件建立复合索引

通过系统掌握MySQL条件查询与范围查询技术，开发者能够显著提升数据库查询效率，为应用系统提供稳定高效的数据支撑。在实际开发中，建议结合具体业务场景进行测试优化，建立适合的查询模式库。