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)鍵特性：
- 自動去重（即使字段類型不同也會嘗試轉(zhuǎn)換比較）
- 結(jié)果集會按照字段順序排序
- 資源消耗大（臨時(shí)表+排序+去重）
注意事項(xiàng)：
在本例中，UNION會自動去重，與業(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é)果集的分頁：
-- 錯誤寫法（性能極差）
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)致的去重異常
-- 錯誤示例：可能導(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)文章
這篇文章介紹了如何開啟mysql中的嚴(yán)格模式，有需要的朋友可以參考一下2013-09-09
增加索引就是增加一個(gè)索引文件，但是在使用過程中哪些情況增加索引無法達(dá)到預(yù)期的效果呢？感興趣的小伙伴們可以參考一下2021-06-06
最近為了解決mysql低版本的漏洞,這篇文章主要給大家介紹了關(guān)于MySQL如何從5.5升級到8.0的相關(guān)資料,主要使用的命令行升級,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
Mysql查詢很慢卡在sending data的原因及解決思路講解
今天小編就為大家分享一篇關(guān)于Mysql查詢很慢卡在sending data的原因及解決思路講解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-04-04
MySQL 把查詢結(jié)果更新或者插入到新表的操作方法
本文介紹MySQL中復(fù)制多條記錄到另一張表的兩種方式：INSERT插入查詢數(shù)據(jù)與UPDATE更新舊數(shù)據(jù),需確保字段數(shù)量和類型一致,案例演示從t2表向t1表遷移指定字段數(shù)據(jù),感興趣的朋友一起看看吧2025-07-07
MySQL 修改數(shù)據(jù)庫名稱的一個(gè)新奇方法
這篇文章主要介紹了MySQL 修改數(shù)據(jù)庫名稱的一個(gè)新奇方法,MySQL 修改數(shù)據(jù)庫名的一個(gè)變通方法,需要的朋友可以參考下2014-07-07
MySQL很早以前只能采用DOS式界面，后來雖然硬件支持圖形界面（平常的軟件操作界面），但是命令行界面（就是DOS界面）以它 簡單，高效，方便 的特色而被保留下來。這就是用DOS界面的原因。2016-05-05
MySQL錯誤“Specified key was too long; max key length is 1000 b
今天在為數(shù)據(jù)庫中的某兩個(gè)字段設(shè)置unique索引的時(shí)候，出現(xiàn)了Specified key was too long; max key length is 1000 bytes錯誤2010-08-08
本文詳細(xì)介紹了mysql字符集、字符序的概念與聯(lián)系，給大家分享了多種方式查看MYSQL支持的字符集。具體內(nèi)容詳情大家參考下本文2018-01-01
mysql的replace函數(shù)是一個(gè)非常方便的替換函數(shù),下面這篇文章主要給大家給大家介紹了關(guān)于MySQL中Replace語句用法的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-08-08