Kardinaliti Tinggi lwn. Kardinaliti Rendah: Lajur Mana Yang Harus Didahulukan dalam Indeks Julat?

Penyusunan Lajur Kardinaliti Tinggi dalam Indeks Julat

Dalam reka bentuk pangkalan data, apabila mencipta indeks pada berbilang lajur, susunan lajur tersebut boleh memberi kesan ketara kepada prestasi untuk pertanyaan julat. Khususnya, untuk pertanyaan yang melibatkan kedua-dua lajur kardinaliti tinggi dan lajur kardinaliti rendah, susunan lajur optimum dalam indeks boleh bertentangan dengan intuitif.

Senario:

Pertimbangkan jadual dengan skema berikut:

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Jadual ini menyimpan metadata fail, dengan kira-kira 1 juta baris. Lajur masa fail kebanyakannya mempunyai nilai yang berbeza, menunjukkan kardinaliti tinggi, manakala lajur ext mempunyai bilangan nilai terhingga, menunjukkan kardinaliti rendah.

Pertanyaan:

Pertanyaan berikut digunakan untuk mendapatkan semula maklumat fail berdasarkan kedua-dua ext dan masa fail:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

Pilihan Indeks Terbaik:

Timbul persoalan tentang indeks mana, fe atau ef, yang lebih baik untuk pertanyaan ini. Secara intuitif, seseorang mungkin menganggap bahawa indeks dengan lajur kardinaliti tinggi (masa fail) dahulu akan lebih cekap. Walau bagaimanapun, analisis menunjukkan bahawa perkara sebaliknya adalah benar.

Penjelasan:

Menggunakan arahan EXPLAIN, kita boleh memeriksa pelan pertanyaan untuk pilihan indeks yang berbeza:

-- Force index on fe (filetime first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(fe)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

-- Force index on ef (ext first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(ef)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

Keputusan menunjukkan bahawa indeks ef, dengan lajur ext cardinaliti rendah didahulukan, menunjukkan prestasi yang lebih baik daripada fe.

Analisis lanjut menggunakan surih Pengoptimum mendedahkan bahawa dengan indeks fe, pengoptimum menganggarkan mengimbas sejumlah besar baris (16684) untuk menapis pada nilai ext. Dengan indeks ef, ia boleh menggunakan kedua-dua lajur indeks dengan cekap dan mendapatkan hanya baris yang berkaitan (538).

Kesimpulan:

Apabila mencipta indeks komposit untuk menyokong julat pertanyaan, adalah dinasihatkan untuk meletakkan lajur yang terlibat dalam ujian kesamarataan (dalam kes ini, samb) dahulu, tanpa mengira kardinalitinya. Ini membolehkan indeks digunakan dengan lebih berkesan dalam pelan pelaksanaan pertanyaan, menghasilkan prestasi yang lebih baik.

Atas ialah kandungan terperinci Kardinaliti Tinggi lwn. Kardinaliti Rendah: Lajur Mana Yang Harus Didahulukan dalam Indeks Julat?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!