Mengapa MySQL tidak menggunakan indeks pada bidang int yang digunakan sebagai boolean?

Agar MySQL dapat menggunakan indeks, Anda harus secara eksplisit membandingkan bidang int dengan suatu nilai (misalnya true, 1).

select * from myTable where myInt = true

Saya bukan ahli basis data, tetapi bukankah itu menggagalkan tujuan memiliki indeks di lapangan jika hanya ada dua kemungkinan nilai bidang tersebut?

Jika semua bidang dalam kolom yang diindeks adalah unik, maka mesin database dapat melakukan pemindaian indeks untuk menemukan baris yang relevan. Jika hanya ada dua nilai yang mungkin - maka saya tidak melihat tujuan mengindeks bidang itu. Mesin DB harus melakukan operasi yang sama seperti jika indeks tidak ada.

Mungkin MySQL tidak menampilkannya sebagai kunci yang mungkin karena mesin telah membuang gagasan untuk menggunakan indeks dalam rencana eksekusi?

Ada banyak faktor yang perlu dipertimbangkan.

Salah satu faktor yang tidak boleh dimasukkan ke dalamnya adalah notasi yang digunakan dalam soal. Jika kolomnya adalah boolean, maka kondisi ini harus diperlakukan sama oleh pengoptimal:

SELECT * FROM MyTable WHERE MyInt;

SELECT * FROM MyTable WHERE MyInt != 0;

SELECT * FROM MyTable WHERE MyInt IS TRUE;

SELECT * FROM MyTable WHERE MyInt = TRUE;

Mungkin ada formulasi lain yang setara. Yang pertama bukan SQL standar (walaupun jenis MyInt adalah BOOLEAN; yang lain adalah standar. Namun pengoptimal harus mengubah singkatan menjadi bentuk panjang yang sesuai dan kemudian berperilaku sama seolah-olah bentuk panjang itu ditulis oleh pengguna. (Jika pengoptimal tidak melakukan hal ini, bisa dibilang ada masalah dengan pengoptimal; kueri harus direduksi menjadi bentuk kanonik sebelum memutuskan cara memproses kueri. Namun, sering kali terdapat titik buta bahkan pada pengoptimal terbaik sekalipun (Mempelajari cara menghindari hal tersebut adalah suatu bentuk seni, dan secara inheren spesifik untuk DBMS.)

Pengoptimal menggunakan indeks ketika ia yakin indeks tersebut akan meningkatkan kinerja kueri. Jika indeks tidak meningkatkan kinerja, indeks akan diabaikan (jika pengoptimalnya bagus). Terkadang, hal ini bergantung pada apakah statistik indeks tersebut mutakhir.

Dalam sistem pergudangan data, sistem dapat dirancang dan dikonfigurasi untuk melakukan pemindaian tabel secara berurutan dengan sangat cepat; dalam sistem seperti itu, jika selektivitas suatu indeks sedemikian rupa sehingga penggunaannya akan menarik lebih dari 25% baris, sebenarnya akan lebih cepat untuk melakukan pemindaian tabel penuh daripada menggunakan indeks.

Pikirkan tentang itu. Saat membaca melalui indeks, DBMS harus melakukan setidaknya dua kali pembacaan; ia membaca informasi tentang baris dari halaman indeks, dan kemudian harus membaca baris dari halaman data.

Beberapa DBMS menyediakan tabel indeks saja. Semua data ada di indeks. DBMS lain menyediakan mekanisme sehingga Anda dapat mengatakan "indeks unik pada kolom A, B, C; namun, sertakan kolom D dan E juga dalam data". Lalu jika query membutuhkan data dari A, B, C, D atau E (atau kombinasi apapun) dan tidak ada pemfilteran pada kolom lain, DBMS hanya perlu memindai indeksnya, bukan halaman tabelnya juga.

Biasanya, Anda mendapatkan banyak baris indeks dalam satu halaman. Namun, untuk beberapa tabel, membaca indeks mungkin memerlukan membaca lebih banyak data daripada membaca baris. Pertimbangkan tabel pemetaan pola dasar banyak-ke-banyak yang berisi dua nilai ID bilangan bulat (4-byte). Hal ini memerlukan 8 byte per baris pada halaman data, namun indeks mungkin memerlukan overhead 4-8 byte (karena entri kunci indeks menyimpan dua nilai ID ditambah informasi yang diperlukan untuk menemukan baris terkait pada disk). Jadi, pemindaian indeks mungkin melibatkan I/O disk dua kali lebih banyak daripada pemindaian data, meskipun pemindaian indeks dilakukan 'hanya indeks'.

Ini hampir tidak menyentuh permukaan kemungkinan alasan untuk menggunakan atau tidak menggunakan indeks.

Bagi saya, format SQL pertanyaan Anda tampak salah. Apakah Anda mencari nilai kolom yang bukan nol? Ini harus menggunakan indeks:

select * from myTable where myInt is not null