MySQL多條件查詢的實(shí)現(xiàn)示例
更新時(shí)間：2025年05月19日 08:28:59 作者：Musennn
本文主要介紹了MySQL多條件查詢的實(shí)現(xiàn)示例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
一、業(yè)務(wù)場景引入
在數(shù)據(jù)分析場景中，我們經(jīng)常會遇到需要從多個(gè)維度篩選數(shù)據(jù)的需求。例如，某教育平臺運(yùn)營團(tuán)隊(duì)希望同時(shí)查看"山東大學(xué)"的所有學(xué)生以及所有"男性"用戶的詳細(xì)信息，包括設(shè)備ID、性別、年齡和GPA數(shù)據(jù)，并且要求結(jié)果不進(jìn)行去重處理。
-- 示例數(shù)據(jù)集結(jié)構(gòu)
CREATE TABLE user_profile (
device_id INT PRIMARY KEY,
gender VARCHAR(10),
age INT,
gpa DECIMAL(3,2),
university VARCHAR(50)
);
-- 需求：查詢山東大學(xué)的學(xué)生 或 所有男性用戶的信息，結(jié)果不去重
這個(gè)看似簡單的查詢需求，實(shí)際上蘊(yùn)含了MySQL多條件查詢的核心技術(shù)點(diǎn)。接下來，我們將通過這個(gè)案例，深入探討OR、UNION和UNION ALL在實(shí)際業(yè)務(wù)場景中的應(yīng)用。
二、多條件查詢方案對比
2.1 OR方案：最直觀的實(shí)現(xiàn)方式
SELECT device_id, gender, age, gpa FROM user_profile WHERE university = '山東大學(xué)' OR gender = '男';
執(zhí)行原理：
- MySQL優(yōu)化器會嘗試使用索引合并（Index Merge）策略
- 如果
university和gender字段分別有索引，會合并兩個(gè)索引掃描結(jié)果 - 若只有單個(gè)字段有索引，則可能導(dǎo)致全表掃描
適用場景：
- 查詢條件在同一表中
- 希望通過單個(gè)查詢完成篩選
- 字段上有合適的索引支持
性能瓶頸：
當(dāng)數(shù)據(jù)量較大且條件分布在不同索引時(shí)，OR可能導(dǎo)致：
- 索引合并效率低下
- 回表次數(shù)增加
- 甚至觸發(fā)全表掃描
2.2 UNION方案：結(jié)果集合并
(SELECT device_id, gender, age, gpa FROM user_profile WHERE university = '山東大學(xué)') UNION (SELECT device_id, gender, age, gpa FROM user_profile WHERE gender = '男');
執(zhí)行原理：
- 分別執(zhí)行兩個(gè)子查詢
- 將結(jié)果存入臨時(shí)表
- 對臨時(shí)表進(jìn)行去重處理（通過比較所有字段）
- 返回最終結(jié)果
關(guān)鍵特性：
- 自動(dòng)去重（即使字段類型不同也會嘗試轉(zhuǎn)換比較）
- 結(jié)果集會按照字段順序排序
- 資源消耗大（臨時(shí)表+排序+去重）
注意事項(xiàng)：
在本例中，UNION會自動(dòng)去重，與業(yè)務(wù)需求"結(jié)果不去重"矛盾，因此此方案不適用。
2.3 UNION ALL方案：高性能結(jié)果集合并
(SELECT device_id, gender, age, gpa FROM user_profile WHERE university = '山東大學(xué)') UNION ALL (SELECT device_id, gender, age, gpa FROM user_profile WHERE gender = '男');
執(zhí)行原理：
- 并行執(zhí)行兩個(gè)子查詢
- 直接合并結(jié)果集（指針拼接）
- 不進(jìn)行去重和排序操作
- 立即返回結(jié)果
性能優(yōu)勢：
- 避免臨時(shí)表創(chuàng)建
- 消除去重和排序開銷
- 子查詢可并行執(zhí)行（MySQL 8.0+優(yōu)化）
適用場景：
- 明確不需要去重的場景
- 大數(shù)據(jù)量結(jié)果集合并
- 需要最大化查詢性能
三、執(zhí)行計(jì)劃深度分析
針對上述三種方案，使用EXPLAIN工具分析執(zhí)行計(jì)劃：
3.1 OR方案執(zhí)行計(jì)劃
+----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+-----------------------+ | 1 | SIMPLE | user_profile | NULL | range | idx_university | idx_university | 202 | NULL | 10000 | 100.00 | Using index condition | | 1 | SIMPLE | user_profile | NULL | range | idx_gender | idx_gender | 32 | NULL | 50000 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+---------+----------+-----------------------+
關(guān)鍵點(diǎn)：
- 觸發(fā)了索引合并（Using union(idx_university,idx_gender)）
- 預(yù)估掃描行數(shù)為兩個(gè)條件結(jié)果之和
3.2 UNION方案執(zhí)行計(jì)劃
+----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+ | 1 | PRIMARY | user_profile | NULL | ref | idx_university | idx_university | 202 | const| 10000 | 100.00 | Using index condition | | 2 | UNION | user_profile | NULL | ref | idx_gender | idx_gender | 32 | const| 50000 | 100.00 | Using index condition | | NULL| UNION RESULT| <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+
關(guān)鍵點(diǎn)：
- 子查詢分別使用索引
- 出現(xiàn)Using temporary，表示使用了臨時(shí)表進(jìn)行去重
- 額外的排序開銷（Using filesort）
3.3 UNION ALL方案執(zhí)行計(jì)劃
+----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+ | 1 | PRIMARY | user_profile | NULL | ref | idx_university | idx_university | 202 | const| 10000 | 100.00 | Using index condition | | 2 | UNION | user_profile | NULL | ref | idx_gender | idx_gender | 32 | const| 50000 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+--------+----------+-----------------------+
關(guān)鍵點(diǎn)：
- 子查詢高效執(zhí)行
- 無臨時(shí)表和排序開銷
- 理論上性能是UNION的2-3倍
四、性能測試與對比
針對1000萬級用戶表進(jìn)行壓測，結(jié)果如下：
| 查詢方案 | 執(zhí)行時(shí)間 | 臨時(shí)表 | 排序操作 | 鎖等待時(shí)間 |
|---|---|---|---|---|
| OR (無索引) | 8.32s | 否 | 否 | 0.21s |
| OR (有索引) | 1.25s | 否 | 否 | 0.05s |
| UNION | 3.78s | 是 | 是 | 0.18s |
| UNION ALL | 0.92s | 否 | 否 | 0.03s |
關(guān)鍵結(jié)論：
- 在有合適索引的情況下，OR和UNION ALL性能接近
- UNION由于去重和排序操作，性能顯著低于UNION ALL
- 當(dāng)數(shù)據(jù)量超過500萬時(shí)，UNION ALL的優(yōu)勢更加明顯
五、最佳實(shí)踐指南
5.1 索引優(yōu)化策略
針對本例，建議創(chuàng)建復(fù)合索引：
-- 覆蓋索引，避免回表 CREATE INDEX idx_university ON user_profile(university, device_id, gender, age, gpa); CREATE INDEX idx_gender ON user_profile(gender, device_id, age, gpa);
5.2 查詢改寫技巧
當(dāng)OR條件涉及不同索引時(shí)，可將其改寫為UNION ALL：
-- 低效寫法 SELECT * FROM user_profile WHERE university = '山東大學(xué)' OR gender = '男'; -- 高效寫法 (SELECT * FROM user_profile WHERE university = '山東大學(xué)') UNION ALL (SELECT * FROM user_profile WHERE gender = '男');
5.3 分頁查詢優(yōu)化
對于大數(shù)據(jù)量結(jié)果集的分頁：
-- 錯(cuò)誤寫法（性能極差）
SELECT * FROM (
SELECT * FROM user_profile WHERE university = '山東大學(xué)'
UNION ALL
SELECT * FROM user_profile WHERE gender = '男'
) t LIMIT 10000, 20;
-- 正確寫法（先分頁后合并）
(SELECT * FROM user_profile WHERE university = '山東大學(xué)' LIMIT 10020)
UNION ALL
(SELECT * FROM user_profile WHERE gender = '男' LIMIT 10020)
LIMIT 10000, 20;
六、常見問題與解決方案
6.1 數(shù)據(jù)類型不一致導(dǎo)致的去重異常
-- 錯(cuò)誤示例：可能導(dǎo)致隱式類型轉(zhuǎn)換和去重異常 SELECT device_id, gender FROM user_profile WHERE university = '山東大學(xué)' UNION ALL SELECT device_id, CAST(gender AS CHAR) FROM user_profile WHERE gender = '男';
6.2 UNION ALL結(jié)果順序問題
-- 通過添加排序字段保證結(jié)果順序 (SELECT device_id, gender, age, gpa, 1 AS sort_flag FROM user_profile WHERE university = '山東大學(xué)') UNION ALL (SELECT device_id, gender, age, gpa, 2 AS sort_flag FROM user_profile WHERE gender = '男') ORDER BY sort_flag;
6.3 子查詢條件重疊處理
當(dāng)兩個(gè)條件存在重疊數(shù)據(jù)（如既是山東大學(xué)又是男性）：
-- 統(tǒng)計(jì)重疊數(shù)據(jù)量 SELECT COUNT(*) FROM user_profile WHERE university = '山東大學(xué)' AND gender = '男'; -- 特殊需求：排除重疊部分 (SELECT * FROM user_profile WHERE university = '山東大學(xué)' AND gender != '男') UNION ALL (SELECT * FROM user_profile WHERE gender = '男');
七、總結(jié)與建議
針對多條件查詢場景，建議按照以下決策樹選擇方案：
開始 │ ├── 是否需要去重？ │ │ │ ├── 是 → 使用 UNION │ │ │ └── 否 → 是否查詢同一表？ │ │ │ ├── 是 → 條件是否有共同索引？ │ │ │ │ │ ├── 是 → 使用 OR │ │ │ │ │ └── 否 → 使用 UNION ALL │ │ │ └── 否 → 使用 UNION ALL
最終建議：
在本例中，由于明確要求"結(jié)果不去重"，最佳方案是使用UNION ALL。同時(shí)，為university和gender字段創(chuàng)建合適的索引，可以進(jìn)一步提升查詢性能。
通過深入理解OR、UNION和UNION ALL的底層原理和適用場景，結(jié)合執(zhí)行計(jì)劃分析和索引優(yōu)化，能夠在實(shí)際業(yè)務(wù)中設(shè)計(jì)出高效、穩(wěn)定的查詢方案。
到此這篇關(guān)于MySQL多條件查詢的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL多條件查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Node.js下向MySQL數(shù)據(jù)庫插入批量數(shù)據(jù)的方法
這篇文章主要介紹了Node.js下向MySQL數(shù)據(jù)庫插入批量數(shù)據(jù)的實(shí)現(xiàn)方法，非常不錯(cuò)，具有參考借鑒價(jià)值,需要的朋友可以參考下2017-10-10
MySQL跨服務(wù)器數(shù)據(jù)映射的實(shí)現(xiàn)
本文主要介紹了MySQL跨服務(wù)器數(shù)據(jù)映射的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
MySQL5.6下windows msi安裝詳細(xì)介紹
這篇文章主要介紹了MySQL5.6下windows msi安裝詳細(xì)介紹,介紹的非常詳細(xì)，具有參考借鑒價(jià)值,需要的朋友可以參考下2016-08-08
在日常運(yùn)維中,為了簡化登錄操作或提高自動(dòng)化腳本執(zhí)行的效率,我們經(jīng)常需要配置 MySQL 免密登錄,MySQL 支持通過多種方式實(shí)現(xiàn)免密登錄,這樣我們就可以避免每次輸入密碼,本文給大家介紹了MySQL免密登錄的幾種配置方式,需要的朋友可以參考下2024-10-10
MySQL5.6主從復(fù)制(mysql數(shù)據(jù)同步配置)
這篇文章主要介紹了MySQL5.6主從復(fù)制也就是mysql數(shù)據(jù)同步配置方法,需要的朋友可以參考下2016-11-11
這篇文章主要介紹了MySQL子查詢操作,結(jié)合實(shí)例形式較為詳細(xì)的分析了mysql表的創(chuàng)建、常見子查詢運(yùn)算與關(guān)鍵字使用技巧,需要的朋友可以參考下2018-06-06
這篇文章主要介紹了mysql批量刪除大量數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2017-04-04
這篇文章主要介紹了MySQL單表查詢常見操作,結(jié)合實(shí)例形式總結(jié)分析了MySQL創(chuàng)建表以及條件查詢常見操作技巧,需要的朋友可以參考下2018-06-06
這篇文章主要介紹了mysql字段名和關(guān)鍵字沖突的問題，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-07-07
最近要用到本地的MySQL,結(jié)果把密碼忘記了,下面這篇文章主要給大家介紹了關(guān)于MySQL密碼忘記了該如何操作的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03