mysql - masalah pengoptimuman sql, antara lebih baik daripada dalam?

Question

Saya melihat maklumat di Internet dan berkata:

in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

Answer 1

Nilai berterusan sudah tentu antara, yang mengurangkan penghuraian, dan jika julat dalam melebihi nombor tertentu, keseluruhan jadual akan dipadamkan secara lalai 9 atau lebih atau kurang dilupakan

Jawapan tambahan untuk komen di bawah:
Dalam senarai penuh, ia bergantung kepada situasi di atas adalah ringkasan kasar dari ingatan yang lebih serius tapi angka ni nisbah, lebih kurang 25%-35%. Kalau nak tanya berapa harganya, maaf sebab tahap saya terhad dan saya tidak boleh mengesahkannya tanpa membaca kod sumber. Dan nisbah kira-kira 30 tidak bermakna imbasan jadual penuh mesti dilakukan, kerana mysql juga mempunyai imbasan indeks, bermakna jika kandungan yang dipilih boleh didapati dalam indeks anda, sudah tentu ia tidak akan mengimbas keseluruhan jadual, seperti berikut Dalam contoh, pilih id dari ttt di mana id dalam (..); dan pilih * dari ttt di mana id dalam (...); nilai id, ia masih akan menjadi imbasan kunci utama, dan Situasi berikut ialah situasi peratusan ini situasi antara. Mengapa ia baik? Selain mengurangkan penghuraian untuk akses berterusan pada segmen indeks, terdapat juga satu situasi ialah apabila mendapatkan semula data melalui pengalamatan cakera, sebahagian daripada data berhampiran nilai pertama akan dibaca secara lalai ( terdapat algoritma kebarangkalian bahawa apabila sekeping data diambil, data berhampirannya juga adalah sangat besar masa, menggunakan nilai berterusan antara antara adalah sesuai

mysql> select * from ttt;
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | I17021234001      |
|  2 | IC17031234002     |
|  3 | C17041234003      |
|  4 | IAsEw1234001      |
|  5 | I17021234001A2    |
|  6 | IC17031234002A2   |
|  7 | C17041234003A2    |
|  8 | IAsEw1234001A2    |
|  9 | I17021234001A2    |
| 10 | IC17031234002A2   |
| 11 | C17041234003A2    |
| 12 | IAsEw1234001A2    |
| 13 | I17021234001A2A2  |
| 14 | IC17031234002A2A2 |
| 15 | C17041234003A2A2  |
| 16 | IAsEw1234001A2A2  |
| 17 | I17021234001A2    |
| 18 | IC17031234002A2   |
| 19 | C17041234003A2    |
| 20 | IAsEw1234001A2    |
+----+-------------------+
20 rows in set (0.00 sec)

mysql> show create table ttt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttt   | CREATE TABLE `ttt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from ttt where id in (1,2,3,4,5,6);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ttt   | ALL  | PRIMARY       | NULL | NULL    | NULL |   20 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from ttt where id in (1,2,3,4,5);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | ttt   | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from ttt where id in (1,2,3);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | ttt   | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from ttt where id in (1,2,3,4,5,6,7);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ttt   | ALL  | PRIMARY       | NULL | NULL    | NULL |   20 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from ttt where id in (1,2,3,4,5,6,7);
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ttt   | index | PRIMARY       | PRIMARY | 4       | NULL |   20 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select name from ttt where id in (1,2,3,4,5,6,7);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ttt   | ALL  | PRIMARY       | NULL | NULL    | NULL |   20 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Answer 2

Mengikut struktur penyimpanan indeks B-tree dalam pangkalan data, alamat fizikal yang menunjuk kepada data disimpan dalam nod daun, dan alamat fizikal ini disusun apabila terdapat indeks berkelompok.

如果是连续数值，between在找到第一个匹配值后，则直接从该地址往后搜索，直到最后一个元素为止。这样就不会对后续值进行索引扫描，因此速度快了。

对于in操作，不大清楚，但是估计应该会对全索引进行扫描吧。

Answer 3

EXPLAIN mysql 语句 Tengok output

Answer 4

Apabila menggunakan antara, anda hanya perlu memadankan sempadan atas dan bawah, jadi ia akan menjadi lebih cepat setiap masuk mesti dibaca semula, yang akan menyebabkan imbasan jadual penuh.