SQL优化-20231016
数据结构
数据库的表和索引缺一不可
表
特点： 无序，插入速度快，查找速度慢
索引（B+Tree）
特点：有序，插入速度慢，查找速度快
查找的效率比较，如果按照读取的数据块来计算？
测试数据
TABLE_OWNER TABLE_NAME INDEX_NAME COLUMNS INDEX_TYPE UNIQUENESS STATUS PARTITIONED VISIBILITY HEIGHT TABLESPACE_NAME LAST_ANALYZED
--------------- ------------------------- ------------------------------ ------------------------------ ---------- ---------- -------- ------------ ---------- ------ --------------- --------------------
SCOTT TEST1 IDX_TEST_NAME OBJECT_NAME NORMAL NONUNIQUE VALID NO VISIBLE 2 SYSTEM 16-OCT-23
SCOTT TEST1 IDX_TEST_OWNER OWNER NORMAL NONUNIQUE VALID NO VISIBLE 2 SYSTEM 16-OCT-23
单表查询
SQL> select count(*), count(distinct owner), count(distinct object_name) from test1;
COUNT(*) COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_NAME)
---------- -------------------- --------------------------
13766 15 10663
场景一： 返回少量数据
select * from test1 where object_name like 'TEST%';
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 11 | 2277 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 11 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'TEST%')
filter("OBJECT_NAME" LIKE 'TEST%')
Note
-----
- dynamic sampling used for this statement (level=2)
场景二： 返回大量数据
select * from test1 where owner = 'SYS';
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10966 | 2216K| 51 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 10966 | 2216K| 51 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
返回少量数据才用得到索引，返回大量数据用不到索引。如果是count(*)呢？
索引用不到的地方：https://www.modb.pro/db/71517
两表关联查询
表与表之间关联的方式，一般有四种，nestloop，hash，merge，cartesian。
nestloop：嵌套循环
hash：hash连接
merge：排序合并
cartesian：错误的计划，缺少条件，或者表没有数据或仅有一行数据
select /*+use_nl(a,b)*/a.*, b.* from emp1 a, dept1 b where a.deptno = b.deptno;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 1638 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT1 | 4 | 120 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP1 | 4 | 348 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
select /*+use_hash(a,b)*/a.*, b.* from emp1 a, dept1 b where a.deptno = b.deptno;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 1638 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT1 | 4 | 120 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP1 | 15 | 1305 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
select /*+use_merge(a,b)*/a.*, b.* from emp1 a, dept1 b where a.deptno = b.deptno;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 6 (34)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 1638 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 120 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT1 | 4 | 120 | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 15 | 1305 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP1 | 15 | 1305 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
select a.*, b.* from emp1 a, dept1 b where a.deptno = 10 and b.dname = 'SALES';
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 351 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 3 | 351 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT1 | 1 | 30 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 3 | 261 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP1 | 3 | 261 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
一些错误的sql写法
表使用隐式关联，容易漏条件
sql结构不清晰，大小写不规范
sql里面一些magic number
sql里面全左关联，不考虑实际业务逻辑 （外关联性能问题，结果问题）
最近的几个sql优化分析（mysql）
示例1
desc t1;
desc t2;
desc t3;
select * from t1 limit 10;
select * from t2 limit 10;
select * from t3 limit 10;
explain select a.*, b.* from t1 a, t2 b where a.name1 = b.name1 and b.name2 like 'jane%';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| 1 | SIMPLE | b | NULL | ALL | idx_t2 | NULL | NULL | NULL | 1000 | 11.11 | Using where |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
explain select a.*, b.* from t1 a, t3 b where a.name1 = b.name1 and b.name2 like 'jane%';
+----+-------------+-------+------------+------+---------------+--------+---------+---------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+---------------+-------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where |
| 1 | SIMPLE | b | NULL | ref | idx_t3 | idx_t3 | 403 | hello.a.name1 | 1 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+---------------+-------+----------+-------------+
示例2
select * from t5 where id2 > 1000 limit 1,5;
5 rows in set (0.02 sec)
select * from t5 where id2 > 1000 limit 5000,5;
5 rows in set (1.09 sec)
select * from t5 where id2 > 1000 limit 50000,5;
5 rows in set (10.27 sec)
select * from t5 a, (select id1 from t5 where id2 > 1000 limit 1,5) b where a.id1 = b.id1;
5 rows in set (0.00 sec)
select * from t5 a, (select id1 from t5 where id2 > 1000 limit 5000,5) b where a.id1 = b.id1;
5 rows in set (0.01 sec)
select * from t5 a, (select id1 from t5 where id2 > 1000 limit 50000,5) b where a.id1 = b.id1;
5 rows in set (0.02 sec)
explain select * from t5 where id2 > 1000 limit 50000,5;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+----------------------------------+
| 1 | SIMPLE | t5 | NULL | range | idx_t4_id2 | idx_t4_id2 | 4 | NULL | 25000 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+-------+----------+----------------------------------+
explain select * from t5 a, (select id1 from t5 where id2 > 1000 limit 50000,5) b where a.id1 = b.id1;
+----+-------------+------------+------------+--------+---------------+------------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------------+---------+-------+-------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 25000 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.id1 | 1 | 100.00 | NULL |
| 2 | DERIVED | t5 | NULL | range | idx_t4_id2 | idx_t4_id2 | 4 | NULL | 25000 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+--------+---------------+------------+---------+-------+-------+----------+--------------------------+
示例3
select id1,id2,id3 from t7 where id2 in (
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99,
101,103,105,107,109,111,113,115,117,119,121,123,125,127,129,131,133,135,137,139,141,143,145,147,149,151,153,155,157,159,161,163,165,167,169,171,173,
175,177,179,181,183,185,187,189,191,193,195,197,199,201,203,205,207,209,211,213,215,217,219,221,223,225,227,229,231,233,235,237,239,241,243,245,247,
249,251,253,255,257,259,261,263,265,267,269,271,273,275,277,279,281,283,285,287,289,291,293,295,297,299,301,303,305,307,309,311,313,315,317,319,321,
323,325,327,329,331,333,335,337,339,341,343,345,347,349,351,353,355,357,359,361,363,365,367,369,371,373,375,377,379,381,383,385,387,389,391,393,395,
397,399,401,403,405,407,409,411,413,415,417,419,421,423,425,427,429,431,433,435,437,439,441,443,445,447,449,451,453,455,457,459,461,463,465,467,469,
471,473,475,477,479,481,483,485,487,489,491,493,495,497,499,501,503,505,507,509,511,513,515,517,519,521,523,525,527,529,531,533,535,537,539,541,543,
545,547,549,551,553,555,557,559,561,563,565,567,569,571,573,575,577,579,581,583,585,587,589,591,593,595,597,599,601,603,605,607,609,611,613,615,617,
619,621,623,625,627,629,631,633,635,637,639,641,643,645,647,649,651,653,655,657,659,661,663,665,667,669,671,673,675,677,679,681,683,685,687,689,691,
693,695,697,699,701,703,705,707,709,711,713,715,717,719,721,723,725,727,729,731,733,735,737,739,741,743,745,747,749,751,753,755,757,759,761,763,765,
767,769,771,773,775,777,779,781,783,785,787,789,791,793,795,797,799,801,803,805,807,809,811,813,815,817,819,821,823,825,827,829,831,833,835,837,839,
841,843,845,847,849,851,853,855,857,859,861,863,865,867,869,871,873,875,877,879,881,883,885,887,889,891,893,895,897,899,901,903,905,907,909,911,913,
915,917,919,921,923,925,927,929,931,933,935,937,939,941,943,945,947,949,951,953,955,957,959,961,963,965,967,969,971,973,975,977,979,981,983,985,987,
989,991,993,995,997,999,1001,1003,1005,1007,1009,1011,1013,1015,1017,1019,1021,1023,1025,1027,1029,1031,1033,1035,1037,1039,1041,1043,1045,1047,1049,
1051,1053,1055,1057,1059,1061,1063,1065,1067,1069,1071,1073,1075,1077,1079,1081,1083,1085,1087,1089,1091,1093,1095,1097,1099,1101,1103,1105,1107,1109,
1111,1113,1115,1117,1119,1121,1123,1125,1127,1129,1131,1133,1135,1137,1139,1141,1143,1145,1147,1149,1151,1153,1155,1157,1159,1161,1163,1165,1167,1169,
1171,1173,1175,1177,1179,1181,1183,1185,1187,1189,1191,1193,1195,1197,1199,1201,1203,1205,1207,1209,1211,1213,1215,1217,1219,1221,1223,1225,1227,1229,
1231,1233,1235,1237,1239,1241,1243,1245,1247,1249,1251,1253,1255,1257,1259,1261,1263,1265,1267,1269,1271,1273,1275,1277,1279,1281,1283,1285,1287,1289,
1291,1293,1295,1297,1299,1301,1303,1305,1307,1309,1311,1313,1315,1317,1319,1321,1323,1325,1327,1329,1331,1333,1335,1337,1339,1341,1343,1345,1347,1349,
1351,1353,1355,1357,1359,1361,1363,1365,1367,1369,1371,1373,1375,1377,1379,1381,1383,1385,1387,1389,1391,1393,1395,1397,1399,1401,1403,1405,1407,1409,
1411,1413,1415,1417,1419,1421,1423,1425,1427,1429,1431,1433,1435,1437,1439,1441,1443,1445,1447,1449,1451,1453,1455,1457,1459,1461,1463,1465,1467,1469,
1471,1473,1475,1477,1479,1481,1483,1485,1487,1489,1491,1493,1495,1497,1499,1501,1503,1505,1507,1509,1511,1513,1515,1517,1519,1521,1523,1525,1527,1529,
1531,1533,1535,1537,1539,1541,1543,1545,1547,1549,1551,1553,1555,1557,1559,1561,1563,1565,1567,1569,1571,1573,1575,1577,1579,1581,1583,1585,1587,1589,
1591,1593,1595,1597,1599,1601,1603,1605,1607,1609,1611,1613,1615,1617,1619,1621,1623,1625,1627,1629,1631,1633,1635,1637,1639,1641,1643,1645,1647,1649,
1651,1653,1655,1657,1659,1661,1663,1665,1667,1669,1671,1673,1675,1677,1679,1681,1683,1685,1687,1689,1691,1693,1695,1697,1699,1701,1703,1705,1707,1709,
1711,1713,1715,1717,1719,1721,1723,1725,1727,1729,1731,1733,1735,1737,1739,1741,1743,1745,1747,1749,1751,1753,1755,1757,1759,1761,1763,1765,1767,1769,
1771,1773,1775,1777,1779,1781,1783,1785,1787,1789,1791,1793,1795,1797,1799,1801,1803,1805,1807,1809,1811,1813,1815,1817,1819,1821,1823,1825,1827,1829,
1831,1833,1835,1837,1839,1841,1843,1845,1847,1849,1851,1853,1855,1857,1859,1861,1863,1865,1867,1869,1871,1873,1875,1877,1879,1881,1883,1885,1887,1889,
1891,1893,1895,1897,1899,1901,1903,1905,1907,1909,1911,1913,1915,1917,1919,1921,1923,1925,1927,1929,1931,1933,1935,1937,1939,1941,1943,1945,1947,1949,
1951,1953,1955,1957,1959,1961,1963,1965,1967,1969,1971,1973,1975,1977,1979,1981,1983,1985,1987,1989,1991,1993,1995,1997,1999
);
1000 rows in set (0.06 sec)
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t7 | NULL | ALL | NULL | NULL | NULL | NULL | 99251 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
select a.id1,a.id2,a.id3 from t7 a, t6 b where a.id2 = b.id;
1000 rows in set (0.06 sec)
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 99251 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+
select id1,id2,id3 from t7 where
id2=1 or id2=3 or id2=5 or id2=7 or id2=9 or id2=11 or id2=13 or id2=15 or id2=17 or id2=19 or
id2=21 or id2=23 or id2=25 or id2=27 or id2=29 or id2=31 or id2=33 or id2=35 or id2=37 or id2=39 or
id2=41 or id2=43 or id2=45 or id2=47 or id2=49 or id2=51 or id2=53 or id2=55 or id2=57 or id2=59 or
id2=61 or id2=63 or id2=65 or id2=67 or id2=69 or id2=71 or id2=73 or id2=75 or id2=77 or id2=79 or
id2=81 or id2=83 or id2=85 or id2=87 or id2=89 or id2=91 or id2=93 or id2=95 or id2=97 or id2=99 or
id2=101 or id2=103 or id2=105 or id2=107 or id2=109 or id2=111 or id2=113 or id2=115 or id2=117 or id2=119 or
id2=121 or id2=123 or id2=125 or id2=127 or id2=129 or id2=131 or id2=133 or id2=135 or id2=137 or id2=139 or
id2=141 or id2=143 or id2=145 or id2=147 or id2=149 or id2=151 or id2=153 or id2=155 or id2=157 or id2=159 or
id2=161 or id2=163 or id2=165 or id2=167 or id2=169 or id2=171 or id2=173 or id2=175 or id2=177 or id2=179 or
id2=181 or id2=183 or id2=185 or id2=187 or id2=189 or id2=191 or id2=193 or id2=195 or id2=197 or id2=199 or
id2=201 or id2=203 or id2=205 or id2=207 or id2=209 or id2=211 or id2=213 or id2=215 or id2=217 or id2=219 or
id2=221 or id2=223 or id2=225 or id2=227 or id2=229 or id2=231 or id2=233 or id2=235 or id2=237 or id2=239 or
id2=241 or id2=243 or id2=245 or id2=247 or id2=249 or id2=251 or id2=253 or id2=255 or id2=257 or id2=259 or
id2=261 or id2=263 or id2=265 or id2=267 or id2=269 or id2=271 or id2=273 or id2=275 or id2=277 or id2=279 or
id2=281 or id2=283 or id2=285 or id2=287 or id2=289 or id2=291 or id2=293 or id2=295 or id2=297 or id2=299 or
id2=301 or id2=303 or id2=305 or id2=307 or id2=309 or id2=311 or id2=313 or id2=315 or id2=317 or id2=319 or
id2=321 or id2=323 or id2=325 or id2=327 or id2=329 or id2=331 or id2=333 or id2=335 or id2=337 or id2=339 or
id2=341 or id2=343 or id2=345 or id2=347 or id2=349 or id2=351 or id2=353 or id2=355 or id2=357 or id2=359 or
id2=361 or id2=363 or id2=365 or id2=367 or id2=369 or id2=371 or id2=373 or id2=375 or id2=377 or id2=379 or
id2=381 or id2=383 or id2=385 or id2=387 or id2=389 or id2=391 or id2=393 or id2=395 or id2=397 or id2=399 or
id2=401 or id2=403 or id2=405 or id2=407 or id2=409 or id2=411 or id2=413 or id2=415 or id2=417 or id2=419 or
id2=421 or id2=423 or id2=425 or id2=427 or id2=429 or id2=431 or id2=433 or id2=435 or id2=437 or id2=439 or
id2=441 or id2=443 or id2=445 or id2=447 or id2=449 or id2=451 or id2=453 or id2=455 or id2=457 or id2=459 or
id2=461 or id2=463 or id2=465 or id2=467 or id2=469 or id2=471 or id2=473 or id2=475 or id2=477 or id2=479 or
id2=481 or id2=483 or id2=485 or id2=487 or id2=489 or id2=491 or id2=493 or id2=495 or id2=497 or id2=499 or
id2=501 or id2=503 or id2=505 or id2=507 or id2=509 or id2=511 or id2=513 or id2=515 or id2=517 or id2=519 or
id2=521 or id2=523 or id2=525 or id2=527 or id2=529 or id2=531 or id2=533 or id2=535 or id2=537 or id2=539 or
id2=541 or id2=543 or id2=545 or id2=547 or id2=549 or id2=551 or id2=553 or id2=555 or id2=557 or id2=559 or
id2=561 or id2=563 or id2=565 or id2=567 or id2=569 or id2=571 or id2=573 or id2=575 or id2=577 or id2=579 or
id2=581 or id2=583 or id2=585 or id2=587 or id2=589 or id2=591 or id2=593 or id2=595 or id2=597 or id2=599 or
id2=601 or id2=603 or id2=605 or id2=607 or id2=609 or id2=611 or id2=613 or id2=615 or id2=617 or id2=619 or
id2=621 or id2=623 or id2=625 or id2=627 or id2=629 or id2=631 or id2=633 or id2=635 or id2=637 or id2=639 or
id2=641 or id2=643 or id2=645 or id2=647 or id2=649 or id2=651 or id2=653 or id2=655 or id2=657 or id2=659 or
id2=661 or id2=663 or id2=665 or id2=667 or id2=669 or id2=671 or id2=673 or id2=675 or id2=677 or id2=679 or
id2=681 or id2=683 or id2=685 or id2=687 or id2=689 or id2=691 or id2=693 or id2=695 or id2=697 or id2=699 or
id2=701 or id2=703 or id2=705 or id2=707 or id2=709 or id2=711 or id2=713 or id2=715 or id2=717 or id2=719 or
id2=721 or id2=723 or id2=725 or id2=727 or id2=729 or id2=731 or id2=733 or id2=735 or id2=737 or id2=739 or
id2=741 or id2=743 or id2=745 or id2=747 or id2=749 or id2=751 or id2=753 or id2=755 or id2=757 or id2=759 or
id2=761 or id2=763 or id2=765 or id2=767 or id2=769 or id2=771 or id2=773 or id2=775 or id2=777 or id2=779 or
id2=781 or id2=783 or id2=785 or id2=787 or id2=789 or id2=791 or id2=793 or id2=795 or id2=797 or id2=799 or
id2=801 or id2=803 or id2=805 or id2=807 or id2=809 or id2=811 or id2=813 or id2=815 or id2=817 or id2=819 or
id2=821 or id2=823 or id2=825 or id2=827 or id2=829 or id2=831 or id2=833 or id2=835 or id2=837 or id2=839 or
id2=841 or id2=843 or id2=845 or id2=847 or id2=849 or id2=851 or id2=853 or id2=855 or id2=857 or id2=859 or
id2=861 or id2=863 or id2=865 or id2=867 or id2=869 or id2=871 or id2=873 or id2=875 or id2=877 or id2=879 or
id2=881 or id2=883 or id2=885 or id2=887 or id2=889 or id2=891 or id2=893 or id2=895 or id2=897 or id2=899 or
id2=901 or id2=903 or id2=905 or id2=907 or id2=909 or id2=911 or id2=913 or id2=915 or id2=917 or id2=919 or
id2=921 or id2=923 or id2=925 or id2=927 or id2=929 or id2=931 or id2=933 or id2=935 or id2=937 or id2=939 or
id2=941 or id2=943 or id2=945 or id2=947 or id2=949 or id2=951 or id2=953 or id2=955 or id2=957 or id2=959 or
id2=961 or id2=963 or id2=965 or id2=967 or id2=969 or id2=971 or id2=973 or id2=975 or id2=977 or id2=979 or
id2=981 or id2=983 or id2=985 or id2=987 or id2=989 or id2=991 or id2=993 or id2=995 or id2=997 or id2=999 or
id2=1001 or id2=1003 or id2=1005 or id2=1007 or id2=1009 or id2=1011 or id2=1013 or id2=1015 or id2=1017 or id2=1019 or
id2=1021 or id2=1023 or id2=1025 or id2=1027 or id2=1029 or id2=1031 or id2=1033 or id2=1035 or id2=1037 or id2=1039 or
id2=1041 or id2=1043 or id2=1045 or id2=1047 or id2=1049 or id2=1051 or id2=1053 or id2=1055 or id2=1057 or id2=1059 or
id2=1061 or id2=1063 or id2=1065 or id2=1067 or id2=1069 or id2=1071 or id2=1073 or id2=1075 or id2=1077 or id2=1079 or
id2=1081 or id2=1083 or id2=1085 or id2=1087 or id2=1089 or id2=1091 or id2=1093 or id2=1095 or id2=1097 or id2=1099 or
id2=1101 or id2=1103 or id2=1105 or id2=1107 or id2=1109 or id2=1111 or id2=1113 or id2=1115 or id2=1117 or id2=1119 or
id2=1121 or id2=1123 or id2=1125 or id2=1127 or id2=1129 or id2=1131 or id2=1133 or id2=1135 or id2=1137 or id2=1139 or
id2=1141 or id2=1143 or id2=1145 or id2=1147 or id2=1149 or id2=1151 or id2=1153 or id2=1155 or id2=1157 or id2=1159 or
id2=1161 or id2=1163 or id2=1165 or id2=1167 or id2=1169 or id2=1171 or id2=1173 or id2=1175 or id2=1177 or id2=1179 or
id2=1181 or id2=1183 or id2=1185 or id2=1187 or id2=1189 or id2=1191 or id2=1193 or id2=1195 or id2=1197 or id2=1199 or
id2=1201 or id2=1203 or id2=1205 or id2=1207 or id2=1209 or id2=1211 or id2=1213 or id2=1215 or id2=1217 or id2=1219 or
id2=1221 or id2=1223 or id2=1225 or id2=1227 or id2=1229 or id2=1231 or id2=1233 or id2=1235 or id2=1237 or id2=1239 or
id2=1241 or id2=1243 or id2=1245 or id2=1247 or id2=1249 or id2=1251 or id2=1253 or id2=1255 or id2=1257 or id2=1259 or
id2=1261 or id2=1263 or id2=1265 or id2=1267 or id2=1269 or id2=1271 or id2=1273 or id2=1275 or id2=1277 or id2=1279 or
id2=1281 or id2=1283 or id2=1285 or id2=1287 or id2=1289 or id2=1291 or id2=1293 or id2=1295 or id2=1297 or id2=1299 or
id2=1301 or id2=1303 or id2=1305 or id2=1307 or id2=1309 or id2=1311 or id2=1313 or id2=1315 or id2=1317 or id2=1319 or
id2=1321 or id2=1323 or id2=1325 or id2=1327 or id2=1329 or id2=1331 or id2=1333 or id2=1335 or id2=1337 or id2=1339 or
id2=1341 or id2=1343 or id2=1345 or id2=1347 or id2=1349 or id2=1351 or id2=1353 or id2=1355 or id2=1357 or id2=1359 or
id2=1361 or id2=1363 or id2=1365 or id2=1367 or id2=1369 or id2=1371 or id2=1373 or id2=1375 or id2=1377 or id2=1379 or
id2=1381 or id2=1383 or id2=1385 or id2=1387 or id2=1389 or id2=1391 or id2=1393 or id2=1395 or id2=1397 or id2=1399 or
id2=1401 or id2=1403 or id2=1405 or id2=1407 or id2=1409 or id2=1411 or id2=1413 or id2=1415 or id2=1417 or id2=1419 or
id2=1421 or id2=1423 or id2=1425 or id2=1427 or id2=1429 or id2=1431 or id2=1433 or id2=1435 or id2=1437 or id2=1439 or
id2=1441 or id2=1443 or id2=1445 or id2=1447 or id2=1449 or id2=1451 or id2=1453 or id2=1455 or id2=1457 or id2=1459 or
id2=1461 or id2=1463 or id2=1465 or id2=1467 or id2=1469 or id2=1471 or id2=1473 or id2=1475 or id2=1477 or id2=1479 or
id2=1481 or id2=1483 or id2=1485 or id2=1487 or id2=1489 or id2=1491 or id2=1493 or id2=1495 or id2=1497 or id2=1499 or
id2=1501 or id2=1503 or id2=1505 or id2=1507 or id2=1509 or id2=1511 or id2=1513 or id2=1515 or id2=1517 or id2=1519 or
id2=1521 or id2=1523 or id2=1525 or id2=1527 or id2=1529 or id2=1531 or id2=1533 or id2=1535 or id2=1537 or id2=1539 or
id2=1541 or id2=1543 or id2=1545 or id2=1547 or id2=1549 or id2=1551 or id2=1553 or id2=1555 or id2=1557 or id2=1559 or
id2=1561 or id2=1563 or id2=1565 or id2=1567 or id2=1569 or id2=1571 or id2=1573 or id2=1575 or id2=1577 or id2=1579 or
id2=1581 or id2=1583 or id2=1585 or id2=1587 or id2=1589 or id2=1591 or id2=1593 or id2=1595 or id2=1597 or id2=1599 or
id2=1601 or id2=1603 or id2=1605 or id2=1607 or id2=1609 or id2=1611 or id2=1613 or id2=1615 or id2=1617 or id2=1619 or
id2=1621 or id2=1623 or id2=1625 or id2=1627 or id2=1629 or id2=1631 or id2=1633 or id2=1635 or id2=1637 or id2=1639 or
id2=1641 or id2=1643 or id2=1645 or id2=1647 or id2=1649 or id2=1651 or id2=1653 or id2=1655 or id2=1657 or id2=1659 or
id2=1661 or id2=1663 or id2=1665 or id2=1667 or id2=1669 or id2=1671 or id2=1673 or id2=1675 or id2=1677 or id2=1679 or
id2=1681 or id2=1683 or id2=1685 or id2=1687 or id2=1689 or id2=1691 or id2=1693 or id2=1695 or id2=1697 or id2=1699 or
id2=1701 or id2=1703 or id2=1705 or id2=1707 or id2=1709 or id2=1711 or id2=1713 or id2=1715 or id2=1717 or id2=1719 or
id2=1721 or id2=1723 or id2=1725 or id2=1727 or id2=1729 or id2=1731 or id2=1733 or id2=1735 or id2=1737 or id2=1739 or
id2=1741 or id2=1743 or id2=1745 or id2=1747 or id2=1749 or id2=1751 or id2=1753 or id2=1755 or id2=1757 or id2=1759 or
id2=1761 or id2=1763 or id2=1765 or id2=1767 or id2=1769 or id2=1771 or id2=1773 or id2=1775 or id2=1777 or id2=1779 or
id2=1781 or id2=1783 or id2=1785 or id2=1787 or id2=1789 or id2=1791 or id2=1793 or id2=1795 or id2=1797 or id2=1799 or
id2=1801 or id2=1803 or id2=1805 or id2=1807 or id2=1809 or id2=1811 or id2=1813 or id2=1815 or id2=1817 or id2=1819 or
id2=1821 or id2=1823 or id2=1825 or id2=1827 or id2=1829 or id2=1831 or id2=1833 or id2=1835 or id2=1837 or id2=1839 or
id2=1841 or id2=1843 or id2=1845 or id2=1847 or id2=1849 or id2=1851 or id2=1853 or id2=1855 or id2=1857 or id2=1859 or
id2=1861 or id2=1863 or id2=1865 or id2=1867 or id2=1869 or id2=1871 or id2=1873 or id2=1875 or id2=1877 or id2=1879 or
id2=1881 or id2=1883 or id2=1885 or id2=1887 or id2=1889 or id2=1891 or id2=1893 or id2=1895 or id2=1897 or id2=1899 or
id2=1901 or id2=1903 or id2=1905 or id2=1907 or id2=1909 or id2=1911 or id2=1913 or id2=1915 or id2=1917 or id2=1919 or
id2=1921 or id2=1923 or id2=1925 or id2=1927 or id2=1929 or id2=1931 or id2=1933 or id2=1935 or id2=1937 or id2=1939 or
id2=1941 or id2=1943 or id2=1945 or id2=1947 or id2=1949 or id2=1951 or id2=1953 or id2=1955 or id2=1957 or id2=1959 or
id2=1961 or id2=1963 or id2=1965 or id2=1967 or id2=1969 or id2=1971 or id2=1973 or id2=1975 or id2=1977 or id2=1979 or
id2=1981 or id2=1983 or id2=1985 or id2=1987 or id2=1989 or id2=1991 or id2=1993 or id2=1995 or id2=1997 or id2=1999;
1000 rows in set (3.07 sec)
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t7 | NULL | ALL | NULL | NULL | NULL | NULL | 99251 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
使用in这种写法，数据库会将in里面的所有元素做成一张临时的hash表，然后拿主表的的数据去跟这个hash表进行匹配。 而使用or的写法，数据库会拿主表的数据去跟or里面的每一个元素进行匹配。就相当于前者使用hash连接，而后者使用嵌套循环。
常用hint
explain select /*+bnl(t7,t6)*/ t7.id1,t7.id2,t7.id3 from t7 join t6 on (t7.id2 = t6.id); // 用hash join
explain select /*+no_bnl(t7,t6)*/ t7.id1,t7.id2,t7.id3 from t7 join t6 on (t7.id2 = t6.id); // 不要用hash join
explain select /*+bnl(t7,t6) join_order(t7,t6)*/ t7.id1,t7.id2,t7.id3 from t7 join t6 on (t7.id2 = t6.id); // t7驱动t6
explain select /*+bnl(t7,t6) join_order(t6,t7)*/ t7.id1,t7.id2,t7.id3 from t7 join t6 on (t7.id2 = t6.id); // t6驱动t7
mysql常用hint https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
index 使用某个索引
no_index 不适用某个所应
bnl 使用hash连接
no_bnl 不使用hash连接
join_order 表的连接顺序
oracle常用hint https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005
use_hash 使用hash连接
use_nl 使用嵌套循环
use_merge 使用排序合并
leading 表的连接顺序
full 不使用任何索引
index 使用某个索引
no_index 不使用某个索引
思考： 假设a表是小表一万行，b表是大表一亿行。a跟b关联走嵌套循环，你说a做驱动表快还是b做驱动表快？
