MYSQL OR gegen IN-Leistung
Ich frage mich, ob es in Bezug auf die Leistung Unterschiede zwischen den folgenden Punkten gibt
SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)
SELECT ... FROM ... WHERE someFIELD between 0 AND 5
SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ...
oder optimiert MySQL die SQL auf dieselbe Weise wie Compiler den Code optimieren?
BEARBEITEN: Die Variable AND wurde aus dem in den Kommentaren angegebenen Grund in OR geändert.
Ich musste das genau wissen, also habe ich beide Methoden verglichen. Ich habe immer festgestellt, dass IN viel schneller ist als OR.
Glauben Sie nicht Menschen, die ihre "Meinung" abgeben, die Wissenschaft dreht sich alles um Tests und Beweise.
Ich habe eine Schleife von 1000x den entsprechenden Abfragen ausgeführt (für Konsistenz habe ich sql_no_cache verwendet):
IN: 2.34969592094s
OR: 5.83781504631s
Update:
(Ich habe nicht den Quellcode für den ursprünglichen Test, wie vor 6 Jahren, obwohl er ein Ergebnis im selben Bereich wie dieser Test zurückgibt.)
In der Anfrage nach Beispielcode, um dies zu testen, ist hier der einfachste mögliche Anwendungsfall. Bei Verwendung von Eloquent zur Vereinfachung der Syntax wird das gleiche SQL-Equivalent ausgeführt.
$t = microtime(true);
for($i=0; $i<10000; $i++):
$q = DB::table('users')->where('id',1)
->orWhere('id',2)
->orWhere('id',3)
->orWhere('id',4)
->orWhere('id',5)
->orWhere('id',6)
->orWhere('id',7)
->orWhere('id',8)
->orWhere('id',9)
->orWhere('id',10)
->orWhere('id',11)
->orWhere('id',12)
->orWhere('id',13)
->orWhere('id',14)
->orWhere('id',15)
->orWhere('id',16)
->orWhere('id',17)
->orWhere('id',18)
->orWhere('id',19)
->orWhere('id',20)->get();
endfor;
$t2 = microtime(true);
echo $t."\n".$t2."\n".($t2-$t)."\n";
1482080514.3635
1482080517.3713
3.0078368186951
$t = microtime(true);
for($i=0; $i<10000; $i++):
$q = DB::table('users')->whereIn('id',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get();
endfor;
$t2 = microtime(true);
echo $t."\n".$t2."\n".($t2-$t)."\n";
1482080534.0185
1482080536.178
2.1595389842987
Ich habe auch einen Test für zukünftige Googler gemacht. Die Gesamtanzahl der zurückgegebenen Ergebnisse beträgt 7264 von 10000
SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000
Diese Abfrage hat 0.1239 Sekunden gedauert
SELECT * FROM item WHERE id IN (1,2,3,...10000)
Diese Abfrage hat 0.0433 Sekunden gedauert
IN ist 3 mal schneller als OR
Ich denke, dass das ZWISCHEN schneller sein wird, da es konvertiert werden sollte in:
Field >= 0 AND Field <= 5
Nach meinem Verständnis wird ein IN ohnehin in eine Reihe von OR Anweisungen konvertiert. Der Wert von IN ist die Benutzerfreundlichkeit. (Sie müssen nicht nur jeden Spaltennamen mehrmals eingeben, sondern können ihn auch einfacher mit der vorhandenen Logik verwenden. Sie müssen sich auch keine Gedanken über die UND/ODER-Priorität machen, da es sich bei IN um eine einzige Anweisung handelt. Mit einer Reihe von OR Anweisungen, müssen Sie sicherstellen, dass Sie sie mit Klammern umgeben, um sicherzustellen, dass sie als eine Bedingung ausgewertet werden.)
Die einzig richtige Antwort auf Ihre Frage lautet PROFILIEREN SIE IHRE FRAGEN. Dann wissen Sie, was in Ihrer speziellen Situation am besten funktioniert.
Es hängt davon ab, was Sie tun. Wie groß ist der Bereich, wie ist der Datentyp (ich weiß, Ihr Beispiel verwendet einen numerischen Datentyp, Ihre Frage kann jedoch auch für viele verschiedene Datentypen gelten).
Dies ist eine Instanz, in der Sie die Abfrage auf beide Arten schreiben möchten. Bringen Sie es zum Laufen und verwenden Sie EXPLAIN, um die Ausführungsunterschiede herauszufinden.
Ich bin sicher, dass es eine konkrete Antwort darauf gibt, aber so würde ich praktisch die Antwort auf meine gegebene Frage finden.
Dies könnte hilfreich sein: http://forge.mysql.com/wiki/Top10SQLPerformanceTips
Grüße,
Frank
Ich denke, eine Erklärung für die Beobachtung von sunseeker ist, dass MySQL die Werte in der IN-Anweisung tatsächlich sortiert, wenn sie alle statische Werte sind und eine binäre Suche verwenden, was effizienter ist als die einfache Alternative OR. Ich kann mich nicht erinnern, wo ich das gelesen habe, aber Sunseekers Ergebnis scheint ein Beweis zu sein.
ODER wird am langsamsten sein. Ob IN oder BETWEEN schneller sind, hängt von Ihren Daten ab, aber ich würde erwarten, dass BETWEEN normalerweise schneller ist, da es leicht möglich ist, einen Bereich von einem Index zu nehmen (vorausgesetzt, SomeField ist indiziert).
Gerade als Sie dachten, es sei sicher ...
Was ist Ihr Wert von eq_range_index_dive_limit? Besitzen Sie mehr oder weniger Elemente in der IN-Klausel?
Dies wird keinen Benchmark beinhalten, wird aber ein wenig in die inneren Abläufe einfließen. Verwenden Sie ein Werkzeug, um zu sehen, was los ist - Optimizer Trace.
Die Abfrage: SELECT * FROM canada WHERE id ...
Bei einer OR von 3 Werten sieht ein Teil des Trace so aus:
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`canada`.`id` = 296172) or (`canada`.`id` = 295093) or (`canada`.`id` = 293626))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(296172, `canada`.`id`) or multiple equal(295093, `canada`.`id`) or multiple equal(293626, `canada`.`id`))"
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "id",
"ranges": [
"293626 <= id <= 293626",
"295093 <= id <= 295093",
"296172 <= id <= 296172"
],
"index_dives_for_eq_ranges": true,
"chosen": true
...
"refine_plan": [
{
"table": "`canada`",
"pushed_index_condition": "((`canada`.`id` = 296172) or (`canada`.`id` = 295093) or (`canada`.`id` = 293626))",
"table_condition_attached": null,
"access_type": "range"
}
]
Beachten Sie, wie der ICP ORs gegeben wird. Dieses impliziert, dass OR nicht in IN umgewandelt wird und InnoDB eine Reihe von =-Tests durch ICP durchführt. (Ich glaube nicht, dass es sich lohnt, MyISAM in Betracht zu ziehen.)
(Dies ist das 5.6.22-71.0-Protokoll von Percona; id ist ein sekundärer Index.)
Jetzt für IN () mit ein paar Werten
eq_range_index_dive_limit = 10; Es gibt 8 Werte.
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))"
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "id",
"ranges": [
"293626 <= id <= 293626",
"295093 <= id <= 295093",
"295573 <= id <= 295573",
"295588 <= id <= 295588",
"295810 <= id <= 295810",
"296127 <= id <= 296127",
"296172 <= id <= 296172",
"297148 <= id <= 297148"
],
"index_dives_for_eq_ranges": true,
"chosen": true
...
"refine_plan": [
{
"table": "`canada`",
"pushed_index_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))",
"table_condition_attached": null,
"access_type": "range"
}
]
Beachten Sie, dass die IN nicht in OR umgewandelt zu werden scheint.
Eine Randnotiz: Beachten Sie, dass die konstanten Werte sortiert wurden . Dies kann auf zwei Arten von Nutzen sein:
- Durch weniger Sprünge kann es zu einer besseren Zwischenspeicherung kommen, weniger E/A, um alle Werte zu erreichen.
- Wenn zwei ähnliche Abfragen von separaten Verbindungen stammen und diese sich in Transaktionen befinden, ist die Wahrscheinlichkeit höher, dass aufgrund überlappender Listen eine Verzögerung statt eines Deadlocks auftritt.
Schließlich IN () mit vielen Werten
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))"
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "id",
"ranges": [
"291752 <= id <= 291752",
"291839 <= id <= 291839",
...
"297196 <= id <= 297196",
"297201 <= id <= 297201"
],
"index_dives_for_eq_ranges": false,
"rows": 111,
"chosen": true
...
"refine_plan": [
{
"table": "`canada`",
"pushed_index_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))",
"table_condition_attached": null,
"access_type": "range"
}
]
Randbemerkung: Ich brauchte das wegen der Sperrigkeit der Spur:
@@global.optimizer_trace_max_mem_size = 32222;
Nachfolgend finden Sie Details zu 6 Abfragen, die MySQL 5.6 @SQLFiddle verwenden
Zusammenfassend beziehen sich die 6 Abfragen auf unabhängig indizierte Spalten, und es wurden 2 Abfragen pro Datentyp verwendet. Alle Abfragen führten zur Verwendung eines Index unabhängig von den verwendeten IN () oder ORs.
| ORs | IN()
integer | uses index | uses index
date | uses index | uses index
varchar | uses index | uses index
Ich wollte wirklich nur entlarvte Aussagen machen, dass OR bedeutet, dass kein Index verwendet werden kann. Das stimmt nicht. Indizes können in Abfragen mit OR als den sechs Abfragen in den folgenden Beispielen verwendet werden.
Mir scheint, dass viele die Tatsache ignoriert haben, dass IN () eine Syntaxverknüpfung für eine Menge von ORs ist. Im kleinen Maßstab sind die Unterschiede zwischen der Verwendung von IN () -v- OR extrem (infintessinal) marginal.
In größerem Maßstab ist IN () sicherlich bequemer, aber es entspricht einem Satz von OR Bedingungen logisch. Die Änderung der Umgebungsbedingungen für jede Abfrage ist daher am besten, wenn Sie Ihre Abfrage für Ihre Tabellen testen.
Zusammenfassung der 6 Erklärungspläne, alle "Indexzustand verwenden" (nach rechts scrollen)
Query select_type table type possible_keys key key_len ref rows filtered Extra
------------- --------- ------- --------------- ----------- --------- ----- ------ ---------- -----------------------
Integers using OR SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition
Integers using IN SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition
Dates using OR SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition
Dates using IN SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition
Varchar using OR SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition
Varchar using IN SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition
MySQL 5.6 Schema-Setup:
CREATE TABLE `myTable` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`aName` varchar(255) default NULL,
`aDate` datetime,
`aNum` mediumint(8),
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
ALTER TABLE `myTable` ADD INDEX `aName_idx` (`aName`);
ALTER TABLE `myTable` ADD INDEX `aDate_idx` (`aDate`);
ALTER TABLE `myTable` ADD INDEX `aNum_idx` (`aNum`);
INSERT INTO `myTable` (`aName`,`aDate`)
VALUES
("Daniel","2017-09-19 01:22:31")
,("Quentin","2017-06-03 01:06:45")
,("Chester","2017-06-14 17:49:36")
,("Lev","2017-08-30 06:27:59")
,("Garrett","2018-10-04 02:40:37")
,("Lane","2017-01-22 17:11:21")
,("Chaim","2017-09-20 11:13:46")
,("Kieran","2018-03-10 18:37:26")
,("Cedric","2017-05-20 16:25:10")
,("Conan","2018-07-10 06:29:39")
,("Rudyard","2017-07-14 00:04:00")
,("Chadwick","2018-08-18 08:54:08")
,("Darius","2018-10-02 06:55:56")
,("Joseph","2017-06-19 13:20:33")
,("Wayne","2017-04-02 23:20:25")
,("Hall","2017-10-13 00:17:24")
,("Craig","2016-12-04 08:15:22")
,("Keane","2018-03-12 04:21:46")
,("Russell","2017-07-14 17:21:58")
,("Seth","2018-07-25 05:51:30")
,("Cole","2018-06-09 15:32:53")
,("Donovan","2017-08-12 05:21:35")
,("Damon","2017-06-27 03:44:19")
,("Brian","2017-02-01 23:35:20")
,("Harper","2017-08-25 04:29:27")
,("Chandler","2017-09-30 23:54:06")
,("Edward","2018-07-30 12:18:07")
,("Curran","2018-05-23 09:31:53")
,("Uriel","2017-05-08 03:31:43")
,("Honorato","2018-04-07 14:57:53")
,("Griffin","2017-01-07 23:35:31")
,("Hasad","2017-05-15 05:32:41")
,("Burke","2017-07-04 01:11:19")
,("Hyatt","2017-03-14 17:12:28")
,("Brenden","2017-10-17 05:16:14")
,("Ryan","2018-10-10 08:07:55")
,("Giacomo","2018-10-06 14:21:21")
,("James","2018-02-06 02:45:59")
,("Colt","2017-10-10 08:11:26")
,("Kermit","2017-09-18 16:57:16")
,("Drake","2018-05-20 22:08:36")
,("Berk","2017-04-16 17:39:32")
,("Alan","2018-09-01 05:33:05")
,("Deacon","2017-04-20 07:03:05")
,("Omar","2018-03-02 15:04:32")
,("Thaddeus","2017-09-19 04:07:54")
,("Troy","2016-12-13 04:24:08")
,("Rogan","2017-11-02 00:03:25")
,("Grant","2017-08-21 01:45:16")
,("Walker","2016-11-26 15:54:52")
,("Clarke","2017-07-20 02:26:56")
,("Clayton","2018-08-16 05:09:29")
,("Denton","2018-08-11 05:26:05")
,("Nicholas","2018-07-19 09:29:55")
,("Hashim","2018-08-10 20:38:06")
,("Todd","2016-10-25 01:01:36")
,("Xenos","2017-05-11 22:50:35")
,("Bert","2017-06-17 18:08:21")
,("Oleg","2018-01-03 13:10:32")
,("Hall","2018-06-04 01:53:45")
,("Evan","2017-01-16 01:04:25")
,("Mohammad","2016-11-18 05:42:52")
,("Armand","2016-12-18 06:57:57")
,("Kaseem","2018-06-12 23:09:57")
,("Colin","2017-06-29 05:25:52")
,("Arthur","2016-12-29 04:38:13")
,("Xander","2016-11-14 19:35:32")
,("Dante","2016-12-01 09:01:04")
,("Zahir","2018-02-17 14:44:53")
,("Raymond","2017-03-09 05:33:06")
,("Giacomo","2017-04-17 06:12:52")
,("Fulton","2017-06-04 00:41:57")
,("Chase","2018-01-14 03:03:57")
,("William","2017-05-08 09:44:59")
,("Fuller","2017-03-31 20:35:20")
,("Jarrod","2017-02-15 02:45:29")
,("Nissim","2018-03-11 14:19:25")
,("Chester","2017-11-05 00:14:27")
,("Perry","2017-12-24 11:58:04")
,("Theodore","2017-06-26 12:34:12")
,("Mason","2017-10-02 03:53:49")
,("Brenden","2018-10-08 10:09:47")
,("Jerome","2017-11-05 20:34:25")
,("Keaton","2018-08-18 00:55:56")
,("Tiger","2017-05-21 16:59:07")
,("Benjamin","2018-04-10 14:46:36")
,("John","2018-09-05 18:53:03")
,("Jakeem","2018-10-11 00:17:38")
,("Kenyon","2017-12-18 22:19:29")
,("Ferris","2017-03-29 06:59:13")
,("Hoyt","2017-01-03 03:48:56")
,("Fitzgerald","2017-07-27 11:27:52")
,("Forrest","2017-10-05 23:14:21")
,("Jordan","2017-01-11 03:48:09")
,("Lev","2017-05-25 08:03:39")
,("Chase","2017-06-18 19:09:23")
,("Ryder","2016-12-13 12:50:50")
,("Malik","2017-11-19 15:15:55")
,("Zeph","2018-04-04 11:22:12")
,("Amala","2017-01-29 07:52:17")
;
.
update MyTable
set aNum = id
;
Abfrage 1:
select 'aNum by OR' q, mytable.*
from mytable
where aNum = 12
OR aNum = 22
OR aNum = 27
OR aNum = 32
OR aNum = 42
OR aNum = 52
OR aNum = 62
OR aNum = 65
OR aNum = 72
OR aNum = 82
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| aNum by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| aNum by OR | 22 | Donovan | 2017-08-12T05:21:35Z | 22 |
| aNum by OR | 27 | Edward | 2018-07-30T12:18:07Z | 27 |
| aNum by OR | 32 | Hasad | 2017-05-15T05:32:41Z | 32 |
| aNum by OR | 42 | Berk | 2017-04-16T17:39:32Z | 42 |
| aNum by OR | 52 | Clayton | 2018-08-16T05:09:29Z | 52 |
| aNum by OR | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 |
| aNum by OR | 65 | Colin | 2017-06-29T05:25:52Z | 65 |
| aNum by OR | 72 | Fulton | 2017-06-04T00:41:57Z | 72 |
| aNum by OR | 82 | Brenden | 2018-10-08T10:09:47Z | 82 |
Abfrage 2:
select 'aNum by IN' q, mytable.*
from mytable
where aNum IN (
12
, 22
, 27
, 32
, 42
, 52
, 62
, 65
, 72
, 82
)
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| aNum by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| aNum by IN | 22 | Donovan | 2017-08-12T05:21:35Z | 22 |
| aNum by IN | 27 | Edward | 2018-07-30T12:18:07Z | 27 |
| aNum by IN | 32 | Hasad | 2017-05-15T05:32:41Z | 32 |
| aNum by IN | 42 | Berk | 2017-04-16T17:39:32Z | 42 |
| aNum by IN | 52 | Clayton | 2018-08-16T05:09:29Z | 52 |
| aNum by IN | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 |
| aNum by IN | 65 | Colin | 2017-06-29T05:25:52Z | 65 |
| aNum by IN | 72 | Fulton | 2017-06-04T00:41:57Z | 72 |
| aNum by IN | 82 | Brenden | 2018-10-08T10:09:47Z | 82 |
Abfrage 3:
select 'adate by OR' q, mytable.*
from mytable
where aDate= str_to_date("2017-02-15 02:45:29",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2018-03-10 18:37:26",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2017-05-20 16:25:10",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2018-07-10 06:29:39",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2017-07-14 00:04:00",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2018-08-18 08:54:08",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2018-10-02 06:55:56",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2017-04-20 07:03:05",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2018-03-02 15:04:32",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2017-09-19 04:07:54",'%Y-%m-%d %h:%i:%s')
OR aDate = str_to_date("2016-12-13 04:24:08",'%Y-%m-%d %h:%i:%s')
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| adate by OR | 47 | Troy | 2016-12-13T04:24:08Z | 47 |
| adate by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
| adate by OR | 44 | Deacon | 2017-04-20T07:03:05Z | 44 |
| adate by OR | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 |
| adate by OR | 10 | Conan | 2018-07-10T06:29:39Z | 10 |
| adate by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| adate by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
Abfrage 4:
select 'adate by IN' q, mytable.*
from mytable
where aDate IN (
str_to_date("2017-02-15 02:45:29",'%Y-%m-%d %h:%i:%s')
, str_to_date("2018-03-10 18:37:26",'%Y-%m-%d %h:%i:%s')
, str_to_date("2017-05-20 16:25:10",'%Y-%m-%d %h:%i:%s')
, str_to_date("2018-07-10 06:29:39",'%Y-%m-%d %h:%i:%s')
, str_to_date("2017-07-14 00:04:00",'%Y-%m-%d %h:%i:%s')
, str_to_date("2018-08-18 08:54:08",'%Y-%m-%d %h:%i:%s')
, str_to_date("2018-10-02 06:55:56",'%Y-%m-%d %h:%i:%s')
, str_to_date("2017-04-20 07:03:05",'%Y-%m-%d %h:%i:%s')
, str_to_date("2018-03-02 15:04:32",'%Y-%m-%d %h:%i:%s')
, str_to_date("2017-09-19 04:07:54",'%Y-%m-%d %h:%i:%s')
, str_to_date("2016-12-13 04:24:08",'%Y-%m-%d %h:%i:%s')
)
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| adate by IN | 47 | Troy | 2016-12-13T04:24:08Z | 47 |
| adate by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
| adate by IN | 44 | Deacon | 2017-04-20T07:03:05Z | 44 |
| adate by IN | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 |
| adate by IN | 10 | Conan | 2018-07-10T06:29:39Z | 10 |
| adate by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| adate by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
Abfrage 5:
select 'name by OR' q, mytable.*
from mytable
where aname = 'Alan'
OR aname = 'Brian'
OR aname = 'Chandler'
OR aname = 'Darius'
OR aname = 'Evan'
OR aname = 'Ferris'
OR aname = 'Giacomo'
OR aname = 'Hall'
OR aname = 'James'
OR aname = 'Jarrod'
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| name by OR | 43 | Alan | 2018-09-01T05:33:05Z | 43 |
| name by OR | 24 | Brian | 2017-02-01T23:35:20Z | 24 |
| name by OR | 26 | Chandler | 2017-09-30T23:54:06Z | 26 |
| name by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
| name by OR | 61 | Evan | 2017-01-16T01:04:25Z | 61 |
| name by OR | 90 | Ferris | 2017-03-29T06:59:13Z | 90 |
| name by OR | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 |
| name by OR | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 |
| name by OR | 16 | Hall | 2017-10-13T00:17:24Z | 16 |
| name by OR | 60 | Hall | 2018-06-04T01:53:45Z | 60 |
| name by OR | 38 | James | 2018-02-06T02:45:59Z | 38 |
| name by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
Abfrage 6:
select 'name by IN' q, mytable.*
from mytable
where aname IN (
'Alan'
,'Brian'
,'Chandler'
, 'Darius'
, 'Evan'
, 'Ferris'
, 'Giacomo'
, 'Hall'
, 'James'
, 'Jarrod'
)
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| name by IN | 43 | Alan | 2018-09-01T05:33:05Z | 43 |
| name by IN | 24 | Brian | 2017-02-01T23:35:20Z | 24 |
| name by IN | 26 | Chandler | 2017-09-30T23:54:06Z | 26 |
| name by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
| name by IN | 61 | Evan | 2017-01-16T01:04:25Z | 61 |
| name by IN | 90 | Ferris | 2017-03-29T06:59:13Z | 90 |
| name by IN | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 |
| name by IN | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 |
| name by IN | 16 | Hall | 2017-10-13T00:17:24Z | 16 |
| name by IN | 60 | Hall | 2018-06-04T01:53:45Z | 60 |
| name by IN | 38 | James | 2018-02-06T02:45:59Z | 38 |
| name by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
Ich wette, sie sind gleich, Sie können einen Test ausführen, indem Sie Folgendes tun:
schleife über die "in (1,2,3,4)" 500-mal und sehen, wie lange es dauert. überlaufen Sie die Version "= 1 oder = 2 oder = 3 ..." 500 Mal und sehen Sie, wie lange sie läuft.
sie könnten auch einen Join-Weg versuchen, wenn someField ein Index ist und Ihr Tisch groß ist, könnte er schneller sein ...
SELECT ...
FROM ...
INNER JOIN (SELECT 1 as newField UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) dt ON someFIELD =newField
Ich habe die oben beschriebene Join-Methode auf meinem SQL Server ausprobiert, und sie ist fast identisch mit der in (1,2,3,4). Beide führen zu einer gruppierten Indexsuche. Ich bin nicht sicher, wie MySQL damit umgehen wird.
Die akzeptierte Antwort erklärt nicht den Grund.
Nachfolgend sind die zitierten Ausgaben von High Performance MySQL, 3rd Edition.
In vielen Datenbankservern ist IN () nur ein Synonym für mehrere OR - Klauseln, da die beiden logisch gleichwertig sind. Nicht so in MySQL, das die Werte in der IN () - Liste sortiert und anhand einer schnellen binären Suche prüft, ob ein Wert in der Liste enthalten ist. Dies ist O (Log n) in der Größe der Liste, während eine äquivalente Reihe von OR - Klauseln O(n) in der Größe der Liste ist (d. H. Viel langsamer für große Listen).
Soweit ich weiß, wie der Compiler diese Art von Abfragen optimiert, ist die Verwendung der IN-Klausel effizienter als die Verwendung mehrerer Klauseln OR. Wenn Sie Werte haben, bei denen die Klausel BETWEEN verwendet werden kann, ist dies noch effizienter.
2018: IN ist schneller. Aber > = && <= ist noch schneller als IN.
Hier ist mein Benchmark .
Ich weiß, dass, solange Sie einen Index für Field haben, das BETWEEN es verwendet, um schnell ein Ende zu finden und dann zum anderen zu gelangen. Das ist am effizientesten.
Jedes EXPLAIN, das ich gesehen habe, zeigt "IN (...)" und "... OR ...", um austauschbar und gleichermaßen (in) effizient zu sein. Was Sie erwarten würden, da der Optimierer nicht wissen kann, ob er ein Intervall umfasst oder nicht. Es entspricht auch einer UNION ALL SELECT-Anweisung für die einzelnen Werte.