Apakah situasi di mana MySQL tidak sesuai untuk membina indeks dan kegagalan indeks?

Kesimpulan

Kes khusus diterangkan secara terperinci di bawah

Senario yang tidak sesuai untuk pengindeksan:

• Ia tidak disyorkan untuk mencipta jadual dengan jumlah data yang kecil Indeks

• Adalah tidak disyorkan untuk membuat indeks pada medan dengan jumlah data pendua yang banyak (serupa dengan: medan jantina)

• Ia tidak disyorkan untuk jadual yang memerlukan kemas kini yang kerap Buat indeks

• Medan yang tidak digunakan mengikut tempat, kumpulan mengikut, susunan mengikut tidak diindeks

• Jangan tentukan indeks berlebihan

Senario kegagalan indeks:

• Syarat penapis menggunakan tidak sama dengan (!=, )

• Penggunaan syarat penapis tidak batal

• Gunakan fungsi atau pengiraan pada medan indeks

• Apabila menggunakan indeks bersama, diperlukan Memenuhi "peraturan awalan kiri terbaik", jika tidak, ia akan menjadi tidak sah

• Apabila penukaran jenis digunakan, indeks akan juga tidak sah

• Apabila menggunakan pertanyaan julat Kadangkala, sesetengah medan indeks bersama menjadi tidak sah (di mana umur >18)

• Dalam medan seperti, jika ia bermula dengan %, indeks menjadi tidak sah (di mana nama seperti ‘%abc’)

• Apabila menggunakan atau untuk membuat pertanyaan, medan bukan indeks muncul sebelum dan selepas atau , dan indeks tidak sah

• Set aksara jadual dan pustaka tidak konsisten, dan pulangan Membawa kepada kegagalan indeks

Pengetahuan titik:

• Adalah tidak disyorkan untuk mempunyai lebih daripada 6 indeks untuk setiap jadual (ia mengambil ruang dan mengurangkan kelajuan kemas kini jadual)

• Keputusan muktamad ialah sama ada untuk menggunakan indeks atau pengoptimum

• Pengoptimum akan mengira kos pertanyaan berdasarkan jumlah data, versi pangkalan data dan bacaan pemilihan data Perbandingan untuk memutuskan sama ada untuk menggunakan indeks

• Apabila menubuhkan indeks, tetapkan medan yang memerlukan padanan julat pada penghujung indeks untuk mengelakkan ketidaksahihan

• Apabila mencipta jadual, tetapkan medan kepada bukan nol dan tetapkan nilai lalai Apabila anda perlu mencari rekod tanpa nilai, anda boleh menggunakan di mana xxx = nilai lalai Menggunakan bukan nol akan menyebabkan indeks gagal

• Apabila mencari pada halaman, sila gunakan padanan kabur kiri atau teks penuh (seperti '%abc')

• Untuk medan dengan kebolehtapisan yang lebih baik, binanya di hadapan daripada indeks bersama. Dengan cara ini, lebih banyak data boleh ditapis terlebih dahulu

Senario di mana pengindeksan tidak disyorkan

Senario 1: Jadual dengan kurang data

Apabila terdapat sedikit data Apabila tiba masanya, kelebihan indeks tidak jelas, kerana enjin penyimpanan pangkalan data juga sangat pantas Berbanding dengan perlu menanyakan indeks sebelum melakukan operasi pengembalian jadual, prestasi pertanyaan langsung mungkin lebih tinggi, jadi jadual dengan data yang agak kurang Ia tidak disyorkan untuk membina indeks

Senario 2: Medan dengan banyak data pendua

Serupa dengan medan jantina, hanya terdapat dua nilai berbeza "lelaki" dan "perempuan", jadi separuh daripada data dalam indeks ialah Separuh daripada data untuk "lelaki" ialah "perempuan", jadi penubuhan indeks tidak boleh melakukan pertanyaan pantas, dsb., jadi tidak disyorkan untuk membuat indeks pada lajur dengan jumlah data pendua yang banyak

Senario 3: Jadual yang kerap dikemas kini (kemas kini /padam/sisipkan)

Kerana apabila data dikemas kini dalam jadual, indeks juga perlu dikekalkan dengan sewajarnya Jika jadual perlu ditambah, dipadam dan diubah suai dengan kerap dalam masa terdekat, ia akan mengambil banyak masa untuk mengekalkan indeks , ia tidak disyorkan cipta indeks. Anda boleh memadamkan indeks apabila operasi kemas kini yang kerap diperlukan dan membina semula indeks selepas kemas kini selesai

Senario 4: Medan tidak digunakan (di mana/kumpulan mengikut/pesan mengikut)

Tidak perlu mengindeks medan selepas tempat/kumpulan mengikut/susun mengikut kerana indeks tidak akan digunakan

Senario 5: Jangan tentukan indeks berlebihan

create index username_password_address on xiao(username,password,address);
-- 如果建立了第一个索引，那么就没有必要建立第二个索引
create index username on xiao (username);
--第二个索引就是冗余索引，因为第一个已经是先根据username排序的索引
--也就是第二个索引的功能完全可以由第一个索引实现

Di sini kerana nama pengguna As medan pertama indeks bersama pertama, indeks diisih mengikut nama pengguna Jika nama pengguna adalah sama, ia diisih mengikut kata laluan dan alamat Oleh itu, fungsi menggunakan lajur nama pengguna sebagai indeks sahaja direalisasikan. Indeks kedua adalah berlebihan

Senario kegagalan indeks

Senario 1: Lakukan operasi (fungsi, dll.) pada medan diindeks, mengakibatkan kegagalan indeks

Berikut ialah yang pertama langkah untuk umur Indeks telah dicipta, dan indeks umur digunakan dalam proses pertanyaan pertama, tetapi nilai kunci kedua adalah batal (indeks kegagalan kerana umur dikira selepas di mana semasa pertanyaan kedua). Komputer tidak tahu pengiraan yang sedang dilakukan, jadi ia akan mengira umur+1 dan membandingkannya dengan 1. Kegagalan indeks

adalah serupa dengan menggunakan fungsi concat() pada medan, yang akan menyebabkan kegagalan indeks

Senario 2: Menggunakan tidak sama dengan (di mana umur != 18)

Apabila menggunakan operasi yang setara, anda boleh mencari dalam indeks, tetapi jika ia adalah tidak sama, Kemudian anda perlu melintasi semua data, jadi

explain select * from xiaoyuanhao where age = 18;
explain select * from xiaoyuanhao where age != 18;
--这里是在age字段上建立了普通索引，第二个查询时候索引失效

tidak sah Senario 3: Penggunaan bukan nol indeks tidak sah

adalah sama dengan tidak sama jika anda menggunakan adalah bukan null, maka anda perlu melakukan semua Dalam operasi traversal data, indeks tidak sah, tetapi jika anda menggunakan adalah null, anda masih boleh menggunakan indeks

--这里是在age字段上建立了普通索引，第二个查询时候索引失效
explain select * from xiaoyuanhao where age is null;
--可以正常使用索引
explain select * from xiaoyuanhao where age is not null;
--索引失效

Senario 4: Peraturan awalan kiri yang optimum ialah tidak diikuti apabila menggunakan indeks bersama

CREATE INDEX age_classid_name ON student(age,classId,NAME);
EXPLAIN SELECT * FROM student WHERE classId = 30 AND NAME = 'xiaoyuanhao';
-- 因为没有使用age字段，所以没有准许最佳左前缀原则，索引失效

从这里可以看出是没有使用索引的(key = null)，因为创建的索引是先按照age进行排序，在age相同的情况下按照classId和name排序，如果在查询的时候需要直接按照classId进行排序查找，那么就无法使用该索引，即索引失效。

如果需要使用使用索引，那么就一定需要到联合索引的第一个字段age，案例如下

EXPLAIN SELECT * FROM student WHERE age = 10 AND NAME = 'xiaoyuanhao';
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 33 AND NAME = 'xiaoyuanhao';
--两者都是使用age字段索引，所以索引有效

场景五：类型转换导致索引失效

CREATE INDEX NAME ON student(NAME);
-- 这里的name字段是varchar类型
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 本次查询是可以使用索引的，因为类型都是一致的，都是字符串
EXPLAIN SELECT * FROM student WHERE NAME = 123;
-- 本次查询则无法使用索引，因为是将数字类型123转换为字符类型

没有发生类型转换，使用索引key = name

发生了类型转换，无法使用索引kye = null，索引失效

使用索引的时候一定需要保证数据类型是一致的，否则系统就需要进行转换，那么就无法使用索引

场景六：使用范围查询导致联合索引其他字段失效

create index age_classId_name on student (age,classId,name);
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId > 20 AND NAME = 'xiaoyuanhao';
-- 这里只能使用age,classId，索引的前两个字段
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 20 AND NAME = 'xiaoyuanhao';
-- 这里可以使用完整的索引，因为都是等值连接

在classId字段上使用范围查询，导致name字段失效，有效索引长度为63

使用的都是等值匹配，整个索引皆可用，有效索引长度为73

也就是在对于联合索引来说，如果在使用的时候是等值匹配，那么就可以重复的利用索引，如果不是等值匹配，那么该字段也是可以使用索引的，但是该字段右边的字段就将失效

建议在建立索引的时候将需要范围匹配的字段建立在索引的最后面

场景七：在使用like的时候，如果以%开头导致索引失效

EXPLAIN SELECT * FROM student WHERE NAME LIKE 'abc%';
-- 可以正常使用索引
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
-- 这里在like中，%在前面无法使用索引

key = name，使用了该索引，索引有效

key = null，索引失效

因为建立的索引实际上是按照整个字符串的从第一个开始进行比较排序的，所以在使用like的时候，也只能够重现进行比较，如果使用的是’%abc’，那么查询的就是以abc结尾的数据，无法使用索引

场景八：or前后出现非索引字段，索引失效

-- 该表中只有name字段上的索引
CREATE INDEX NAME ON student(NAME);
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 这里是可以使用name索引的
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao' OR classId = 1001;
-- 这个则无法使用索引，进行的是全表扫描

key = null，无法使用索引，or条件中出现非索引字段

因为如果name不等于’xiao’的时候那么就会继续判断classId是否等于1001，那么实际上还是会进行全表扫描，所以索引失效（也就是进行name判断的时候可以使用索引，但是在判断classId的时候又要全表扫描，那么优化器就直接进行全表扫描），但是如果or前后的字段都有索引了，那么就就会使用索引

Atas ialah kandungan terperinci Apakah situasi di mana MySQL tidak sesuai untuk membina indeks dan kegagalan indeks?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!