Adakah pesanan medan penting dalam indeks berbilang lajur dalam MySQL?

Question

Saya tahu kepentingan indeks dan cara pesanan sertai mengubah prestasi. Saya telah membaca banyak perkara yang berkaitan dengan indeks berbilang lajur tetapi tidak menemui jawapan kepada soalan saya.

Saya ingin tahu sama ada susunan yang dinyatakan penting jika saya melakukan indeks berbilang lajur. Sangkaan saya tidak, dan enjin akan menganggap mereka sebagai satu kumpulan di mana pesanan itu tidak penting. Tetapi saya mahu mengesahkannya.

Sebagai contoh, dari laman web mysql (http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Dalam apa jua keadaan, adakah perkara berikut lebih baik atau setara, dan adakah ia memberi manfaat?

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

Khususnya:

INDEX name (last_name,first_name)

Perbandingan

INDEX name (first_name,last_name)

Answer 1

Dua indeks adalah berbeza. Ini benar dalam MySQL dan pangkalan data lain. MySQL menerangkan perbezaan dalam dokumentasi .

Pertimbangkan dua indeks:

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

Kedua-duanya hendaklah memohon sama rata kepada:

where last_name = XXX and first_name = YYY

idx_lf adalah optimum untuk:

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl akan paling sesuai untuk situasi berikut:

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

Untuk banyak kes seperti ini, boleh menggunakan dua indeks, tetapi satu daripadanya adalah optimum. Sebagai contoh, pertimbangkan untuk menggunakan pertanyaan idx_lf:

where first_name = XXX order by last_name

MySQL boleh menggunakan idx_lf untuk membaca keseluruhan jadual dan kemudian menapis selepas order by. Saya tidak fikir ini adalah pilihan pengoptimuman dalam amalan (untuk MySQL), tetapi ia mungkin berlaku dalam pangkalan data lain.

Answer 2

Apabila membincangkan indeks berbilang lajur, saya menggunakan analogi buku telefon. Buku telefon pada asasnya ialah indeks dengan nama akhir dahulu dan kemudian nama pertama. Jadi susunan isihan ditentukan oleh "lajur" mana yang didahulukan. Carian dibahagikan kepada kategori berikut:

1. Jika anda mencari orang yang mempunyai nama keluarga Smith, anda boleh mencari mereka dengan mudah kerana buku itu diisih mengikut nama keluarga.

2. Jika anda mencari seseorang bernama John, buku telefon tidak berguna kerana John tersebar di seluruh buku. Anda perlu mengimbas keseluruhan buku telefon untuk mencarinya.

3. Jika anda mencari orang yang mempunyai nama keluarga tertentu Smith dan nama pertama John, buku ini akan membantu kerana anda akan mendapati Smith disusun bersama-sama dan dalam Smiths, John juga ditemui dalam susunan yang disusun.

Jika anda mempunyai buku telefon yang diisih mengikut nama pertama dan kemudian mengikut nama keluarga, pengisihan buku telefon akan membantu anda dalam kes #2 dan #3 di atas, tetapi tidak dalam kes #1 .

Ini menerangkan kes mencari nilai yang tepat, tetapi bagaimana jika anda mencari mengikut julat nilai? Katakan anda ingin mencari semua orang yang nama pertamanya ialah John dan nama keluarga mereka bermula dengan "S" (Smith, Saunders, Staunton, Sherman, dll.). Johns diisih mengikut "J" dalam setiap nama akhir, tetapi jika anda mahu semua Johns dengan semua nama akhir bermula dengan "S", Johns tidak dikumpulkan bersama. Mereka tersebar semula, jadi anda akhirnya perlu mengimbas semua nama yang nama keluarganya bermula dengan "S." Walau bagaimanapun, jika buku telefon itu disusun mengikut nama pertama dan kemudian dengan nama keluarga, anda akan mendapati bahawa semua John akan dikumpulkan bersama, dan kemudian dalam John, semua nama keluarga "S" akan dikumpulkan bersama.

Jadi susunan lajur dalam indeks berbilang lajur pastinya penting. Sejenis pertanyaan mungkin memerlukan susunan lajur tertentu bagi indeks. Jika anda mempunyai berbilang jenis pertanyaan, anda mungkin memerlukan berbilang indeks untuk membantunya, dengan lajur dalam susunan yang berbeza.

Anda boleh membaca pembentangan saya Really How to Design an Index untuk mengetahui lebih lanjut, atau menonton video.