Pemahaman mendalam tentang indeks dalam MySQL (penggunaan, pengelasan, kaedah pemadanan)

Artikel ini akan memberi anda pemahaman yang mendalam tentang indeks dalam MySQL, dan memperkenalkan kelebihan, kegunaan, klasifikasi, istilah teknikal dan kaedah padanan indeks saya harap ia akan membantu anda!

Untuk pembangunan lanjutan, kita selalunya perlu menulis beberapa SQL yang kompleks, jadi untuk mengelakkan penulisan SQL yang tidak cekap, kita perlu memahami beberapa pengetahuan asas pengindeksan. Melalui pengetahuan asas ini kita boleh menulis SQL yang lebih cekap. [Cadangan berkaitan: tutorial video mysql]

01 Kelebihan pengindeksan

• ialah sangat berkurangan Jumlah data yang perlu diimbas oleh pelayan, iaitu jumlah IO
• Bantu pelayan mengelakkan pengisihan dan jadual sementara (cuba elakkan pengisihan fail, tetapi gunakan pengisihan indeks)
• Tukar IO rawak kepada IO berjujukan

02 Penggunaan indeks

• Cepat cari baris yang sepadan dengan klausa where
• Jika boleh Apabila memilih antara berbilang indeks, MySQL biasanya akan menggunakan indeks yang mencari baris paling sedikit
• Jika jadual mempunyai indeks berbilang lajur, pengoptimum boleh menggunakan mana-mana awalan paling kiri bagi indeks untuk mencari baris
• apabila Apabila terdapat sambungan jadual, dapatkan semula data baris daripada jadual lain
• Cari nilai min dan max ​​lajur indeks tertentu
• Jika pengisihan atau pengumpulan boleh dilakukan dengan awalan paling kiri indeks, maka Jadual diisih dan dikumpulkan
• Dalam sesetengah kes, pertanyaan boleh dioptimumkan untuk mendapatkan semula nilai data tanpa perlu mencari baris data

03 Klasifikasi Indeks

Indeks yang dicipta oleh pangkalan data secara lalai adalah untuk kunci unik

• Indeks kunci utama (unik dan tidak kosong)
• Indeks unik (satu-satunya boleh Kosong)
• Indeks biasa (indeks medan biasa)
• Indeks teks penuh ( biasanya dibina pada varchar, char, jenis teks, tetapi jarang digunakan)
• Indeks gabungan ( Indeks ditubuhkan oleh berbilang perkataan)

04 Syarat indeks teknikal

1 Kembali ke jadual

Medan nama ialah indeks biasa daripada pokok-B lajur nama, dan kemudian cari data akhir daripada pokok-B kunci utama Ini ialah pulangan jadual. (Nod daun indeks kunci utama menyimpan semua data lajur, tetapi secara amnya semua nod daun menyimpan ID kunci primer yang sepadan)

Seperti yang ditunjukkan dalam rajah: struktur indeks yang ditubuhkan mengikut nama dalam jadual penggunaan SQL select * from use where name='sun' mula-mula akan mencari kunci utama Id=2 yang sepadan dengan matahari melalui nama indeks bukan kunci utama, dan kemudian mencari keseluruhan data baris dalam indeks kunci utama melalui id=2 dan mengembalikannya adalah pulangan jadual.

2. Meliputi indeks

Anda boleh bertanya perkara yang anda perlukan pada bukan- indeks kunci utama Medan yang tidak perlu dikembalikan ke jadual untuk membuat pertanyaan semula dipanggil indeks meliputi.

Seperti yang ditunjukkan di atas dalam indeks nama, sql ialah select id,name from user where name ="1" Nilai id sudah tersedia dalam indeks bukan kunci utama dalam langkah pertama, jadi tidak perlu bertanya. data baris dalam indeks kunci utama berdasarkan ID.

3 Padanan paling kiri

Dalam indeks gabungan, padankan sebelah kiri dahulu, dan kemudian teruskan padan ke belakang; , dalam jadual pengguna, terdapat Indeks bersama yang terdiri daripada nama dan umur, pilih * daripada pengguna di mana nama="Mr. Ji" dan umur = 18 adalah konsisten dengan padanan paling kiri dan boleh digunakan sebagai indeks. Walau bagaimanapun, pilih * daripada pengguna di mana umur = 18 tidak mematuhi dan indeks ini tidak boleh digunakan.

Sambungan;

Bagaimana untuk mencipta indeks jika ia adalah dua SQL berikut?

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

Disebabkan prinsip padanan paling kiri: Hanya indeks gabungan perlu dicipta Nama umur boleh menjadi

Bagaimana jika ia adalah tiga persegi berikut

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

Buat nama indeks umur dan umur, atau cipta nama umur dan nama indeks, kedua-duanya baik-baik saja.

Malah, nama umur dan umur adalah lebih baik, kerana indeks juga perlu disimpan secara berterusan, menempati ruang cakera dan memori apabila membaca, nama umur dan nama umur sama, tetapi membandingkan nama dan umur secara berasingan, umur pasti mengambil lebih sedikit ruang dan nama lebih panjang (semakin besar indeks, lebih banyak masa IO)

Perhatian! Perhatikan! Perhatikan! :

Apabila membaca banyak artikel, saya sering melihat beberapa contoh ralat padanan paling kiri:

Jika indeks ialah gabungan indeks umur nama, sql ialah select * from user where age = 18 and name="纪先生"Ramai orang berpendapat bahawa ini tidak boleh diindeks, tetapi ia sebenarnya mungkin. Pengoptimum mysql akan mengoptimumkan jujukan pelarasan dan melaraskannya kepada name="Mr. Ji" dan umur = 18

4 >Gunakan maklumat indeks sebanyak mungkin dalam indeks gabungan untuk mengurangkan bilangan pulangan jadual sebanyak mungkin

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引时非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。

更多编程相关知识，请访问：编程视频！！

Atas ialah kandungan terperinci Pemahaman mendalam tentang indeks dalam MySQL (penggunaan, pengelasan, kaedah pemadanan). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!