Rumah >pangkalan data >tutorial mysql >Membawa anda melalui indeks MySQL
Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql Ia terutamanya memperkenalkan beberapa isu dalam bab mysql lanjutan, termasuk apa itu indeks, pelaksanaan asas indeks, dsb. Mari kita bincangkan bersama di bawah. Sila lihat, semoga ia membantu semua orang.
Pembelajaran yang disyorkan: tutorial video mysql
MySQL, istilah yang biasa dan tidak dikenali, seawal Semasa kami belajar Javaweb, kami menggunakan pangkalan data MySQL Pada peringkat itu, MySQL nampaknya hanya satu perkara yang baik untuk menyimpan data Apabila menyimpan, kami memasukkan semuanya, dan apabila bertanya, kami juga membuta tuli seluruh jadual (tanpa sedikit maklumat).
Kami sentiasa menipu diri sendiri dan orang lain dengan berfikir bahawa kami boleh mengoptimumkan melalui aspek lain Kami enggan menghadapi MySQL Advanced dan sebaliknya mempelajari sesuatu yang kelihatan lebih maju " perkara, pelajari Redis untuk berkongsi tekanan MySQL, pelajari perisian tengah seperti MyCat, dan laksanakan replikasi tuan-hamba, pemisahan baca-tulis, sub-pangkalan data dan sub -mejaTunggu. (Saya bercakap tentang melo, betul)
Apabila tiba masa untuk membuat persediaan untuk temuduga, saya mendapati bahawa saya tidak tahu semua soalan tentang MySQL dalam soalan temuduga~
Mengenai middleware canggih yang saya pelajari, saya bertanya Dapatkan hampir sangat sedikit! ! Saya hanya tahu cara menggunakannya. Semasa menulis resume saya, saya hanya boleh menulis "pemahaman" xxx middleware dengan lemah...
Sudah tentu, belajar MySQL Advanced Chapter bukanlah hanya untuk Temuduga, dalam projek sebenar, pengoptimuman kawasan ini adalah sangat penting Selepas mengalami downtime pelayan, saya hanya boleh diam...
Mari kita mulakan dari sekarang, masih terlambat untuk pergi ke darat. ! ! Mengambil kesempatan daripada emas tiga dan perak empat, tambah mata pengetahuan MySQL Advanced Chapter dan mulakan perjalanan MySQL Advanced Chapter dari aspek berikut
Adalah disyorkan untuk pergi melalui direktori bar sisi Cari bahagian yang membantu anda Antaranya, dengan awalan emoji ialah bahagian utama Jika anda rasa ia membantu anda, editor akan meneruskan untuk menambah baik artikel ini dan lajur MySQL.
Takrifan rasmi MySQL bagi indeks ialah: Indeks (indeks) ialah struktur data (tersusun) yang membantu MySQL memperoleh data dengan cekap. Indeks ditambahkan pada medan dalam jadual pangkalan data sebagai mekanisme untuk meningkatkan kecekapan pertanyaan. Selain data, sistem pangkalan data juga mengekalkan struktur data yang memenuhi algoritma carian tertentu Struktur data ini merujuk (menunjukkan) data dalam beberapa cara, supaya algoritma carian lanjutan boleh dilaksanakan pada struktur data ini indeks. Seperti yang ditunjukkan dalam rajah di bawah:
Malah, secara ringkasnya, indeks ialah struktur data yang diisih
Sebelah kiri ialah Jadual data mempunyai jumlah dua lajur dan tujuh rekod Yang paling kiri ialah alamat fizikal rekod data (perhatikan bahawa rekod bersebelahan secara logik tidak semestinya bersebelahan secara fizikal pada cakera). Untuk mempercepatkan carian Col2, anda boleh mengekalkan pepohon carian binari seperti yang ditunjukkan di sebelah kanan Setiap nod mengandungi nilai kunci indeks dan penunjuk ke alamat fizikal rekod data yang sepadan. , jadi Anda boleh menggunakan carian binari untuk mendapatkan data yang sepadan dengan cepat.
Tetapi sebenarnya, Kami tidak menggunakan Binary Search Tree untuk menyimpan data dalam MySQL Mengapa?
Anda tahu, dalam pepohon carian binari, nod di sini hanya boleh menyimpan satu keping data dan nod sepadan dengan blok cakera dalam MySQL, jadi kami membaca satu blok cakera setiap kali , hanya satu keping data boleh diperolehi, yang sangat tidak cekap, jadi kami akan memikirkan untuk menggunakan struktur seperti B-tree untuk menyimpannya.
Indeks dilaksanakan dalam lapisan enjin storan MySQL, bukan dalam lapisan pelayan. Oleh itu, indeks setiap enjin storan tidak semestinya sama, dan tidak semua enjin menyokong semua jenis indeks.
Enjin storan MyISAM, InnoDB dan Memory menyokong pelbagai jenis indeks
|
enjin INNODB | enjin MYISAM | Enjin MEMORI | ||||||||||||||||||||
Indeks BTREE | Disokong | Disokong | Sokongan | ||||||||||||||||||||
Indeks HASH | Tiada Sokongan | Tidak menyokong | Sokongan | ||||||||||||||||||||
Indeks R-tree | Tidak disokong | Disokong | Tidak disokong | ||||||||||||||||||||
Teks Penuh | Disokong selepas versi 5.6 | Disokong | Tidak disokong |
Apa yang biasanya kita panggil indeks, melainkan dinyatakan sebaliknya, merujuk kepada indeks yang disusun dalam struktur B-tree (pokok carian berbilang hala, tidak semestinya binari). Antaranya, indeks berkelompok, indeks kompaun, indeks awalan dan indeks unik semuanya menggunakan indeks pepohon B secara lalai, secara kolektif dipanggil indeks.
Pokok carian seimbang berbilang hala, pesanan m (m-garpu) BTREE memenuhi:
ceil bermaksud pembulatan, ceil(2.3)=3
Disebabkan oleh ke-3 tertib, ia hanya boleh menjadi 2 pada kebanyakan nod, jadi 26 dan 30 bersama-sama pada permulaan, dan kemudian 85 akan mula berpecah 30 akan menjadi kedudukan atas di tengah, 26 akan kekal, dan 85 akan pergi ke kanan
, iaitu: kedudukan atas di tengah , dan kemudian Bahagian kiri kekal di nod lama, dan sebelah kanan pergi ke nod baharu
. Apabila 70 dalam gambar dimasukkan semula, 70 kebetulan berada di kedudukan tengah, kemudian 62 dikekalkan, dan 85 dibahagikan kepada nod baharu. Selepas naik ke atas, ia perlu berpecah
Anda boleh terus berpecah ke atas, perkara yang sama berlaku
Berbanding dengan pepohon carian binari, ketinggian/kedalaman lebih rendah dan kecekapan pertanyaan semula jadi lebih tinggi.Kelebihan perbandingan
POKOK B
Pokok B mempunyai dua jenis nod: nod dalaman (juga dipanggilnod indeks
) danKecekapan pertanyaan B Tree adalah lebih stabil
. Memandangkan hanya nod daun B Tree menyimpan maklumat utama, menanya sebarang kunci memerlukan pergi dari akar ke daun, jadi ia lebih stabil. Anda hanya perlu melintasi nod daun untuk mencapai lintasan keseluruhan pokok. Beralih daripada
pokok carian binari kepada B-tree
BTREE INDEX:
Pengenalan Inisialisasi
Jika anda ingin mencari data item 29, maka blok cakera 1 mula-mula akan dimuatkan dari cakera ke memori, dan IO akan berlaku pada masa ini . Gunakan carian binari dalam ingatan untuk menentukan bahawa 29 adalah antara 17 dan 35, dan kunci penunjuk P2 bagi blok cakera 1. Masa memori boleh diabaikan kerana ia sangat singkat (berbanding dengan IO cakera alamat penuding P2 blok cakera 1 ke blok cakera 3 dimuatkan dari cakera ke dalam memori IO kedua berlaku di antara 26 dan 30. Penunjuk P2 blok cakera 8 dimuatkan ke dalam memori melalui penunjuk IO ketiga berlaku Pada masa yang sama, memori berlalu Carian binari mencapai 29 dan menamatkan pertanyaan, menghasilkan sejumlah tiga IO.
Situasi sebenar ialah pokok B 3 lapisan boleh mewakili berjuta-juta data Jika berjuta-juta carian data hanya memerlukan tiga IO, peningkatan prestasi akan menjadi besar Jika tiada indeks, setiap data Jika IO berlaku untuk setiap item, sejumlah berjuta-juta IO akan diperlukan. Jelas sekali, kosnya sangat tinggi.
Dalam InnoDB, jadual disimpan dalam bentuk indeks mengikut susunan kunci utama Jadual yang disimpan dengan cara ini dipanggil jadual tersusun indeks. Dan kerana kami nyatakan sebelum ini, InnoDB menggunakan model indeks B-tree, jadi data disimpan dalam B-tree.
Setiap indeks sepadan dengan pokok B dalam InnoDB.
Katakan, kita mempunyai jadual dengan lajur kunci utama sebagai ID, terdapat medan k dalam jadual, dan terdapat indeks pada k.
Pernyataan penciptaan jadual bagi jadual ini ialah:
mysql> create table T( id int primary key, k int not null, name varchar(16), index (k))engine=InnoDB; 复制代码
Nilai (ID,k) bagi R1~R5 dalam jadual ialah (100,1), (200,2 ), (300,3) masing-masing ), (500,5) dan (600,6), contoh rajah skematik kedua-dua pokok adalah seperti berikut:
Tidak sukar untuk melihat dari rajah bahawa mengikut nod daun Kandungan, jenis indeks dibahagikan kepada indeks kunci utama dan indeks kunci bukan utama.
Lajur kunci utama jadual data menggunakan indeks kunci utama dan dicipta secara lalai Inilah sebabnya, sebelum kami mempelajari pengindeksan, guru sering menyuruh kami membuat pertanyaan berdasarkan kunci utama Ia akan menjadi lebih cepat Ternyata kunci utama itu sendiri diindeks. Nod daun
indeks kunci utama menyimpan keseluruhan baris data. Dalam InnoDB, indeks kunci utama juga dipanggil indeks berkelompok (indeks berkelompok). Kandungan nod daun
indeks tambahan ialah nilai kunci utama. Dalam InnoDB, indeks tambahan juga dipanggil indeks kedua (indeks kedua).
Seperti yang ditunjukkan di bawah:
Mengikut struktur indeks di atas, mari kita bincangkan soalan: Apakah perbezaan antara pertanyaan berdasarkan indeks kunci primer dan indeks tambahan ?
indeks tertutup-iaitu, lajur indeks mengandungi Semua data kami kepada dipersoalkan.
Pada masa yang sama, indeks sekunder dibahagikan kepada jenis berikut (langkau sahaja buat masa ini, kami akan mengetahui lebih lanjut mengenainya kemudian):
Apa yang dipanggil pushdown. , seperti namanya, sebenarnya Tunda operasi pemulangan jadual kami , MySQL tidak akan membenarkan kami memulangkan jadual dengan mudah, kerana ia sangat membazir. Apakah maksudnya? Pertimbangkan contoh berikut.
Kami telah menubuhkan indeks komposit (nama, status, alamat), yang juga disimpan mengikut medan ini, sama seperti gambar:
Pokok indeks kompaun (hanya menyimpan lajur indeks dan Kunci utama digunakan untuk mengembalikan jadual)
|
status | alamat | id (kunci utama) | ||||||||||||
Xiaomi 1 | 0 | 1 | 1 | ||||||||||||
Xiaomi 2 | 1 | 1 | 2 |
我们执行这样一条语句:
SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ; 复制代码
这种先在自身索引树上判断是否满足其他的where条件,不满足则直接pass掉,不进行回表的操作,就叫做索引下推。
所谓最左前缀,可以想象成一个爬楼梯的过程,假设我们有一个复合索引:name,status,address,那这个楼梯由低到高依次顺序是:name,status,address,最左前缀,要求我们不能出现跳跃楼梯的情况,否则会导致我们的索引失效:
这两个尽管where中字段的顺序不一样,第二个看起来越级了,但实际上效果是一样的
其实是因为我们MySQL有一个Optimizer(查询优化器),查询优化器会将SQL进行优化,选择最优的查询计划来执行。
因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。
比如:
我们创建了三个单列索引,name,status,address
当我们where中根据status和address两个字段来查询时,数据库只会选择最优的一个索引,不会所有单列索引都使用。
最优的索引:具体是指所查询表中,辨识度最高(所占比例最少)的索引列,比如此处address中有一个辨识度很高的 '西安市'数据;
也就是说,我们尽量创建复合索引,而不是单列索引
创建复合索引: CREATE INDEX idx_name_email_status ON tb_seller(name,email,status); 就相当于 对name 创建索引 ; 对name , email 创建了索引 ; 对name , email, status 创建了索引 ; 复制代码
假设我们有这么一个表,id为主键,没有创建索引:
CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB 复制代码
如果要在此处建立复合索引,我们要遵循什么原则呢?
如果我们建立索引(age,name),由于最左前缀原则,我们这个索引能实现的是根据age,根据age和name查询,并不能单纯根据name查询(因为跳跃了),为了实现我们的需求,我们还得再建立一个name索引;
而如果我们通过调整顺序,改成(name,age),就能实现我们的需求了,无需再维护一个name索引,这就是通过调整顺序,可以少维护一个索引。
我们有两种方案:
这两种方案都能实现我们的需求,这个时候我们就要考虑空间了,name字段是比age字段大的,显然方案1所耗费的空间是更小的,所以我们更倾向于方案1。
默认会为主键创建索引--primary
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name(index_col_name,...) index_col_name : column_name[(length)][ASC | DESC] 复制代码
结尾加上\G,可以变成竖屏显示
select index from tbl_name\G; 复制代码
drop INDEX index_name on tbl_name ; 复制代码
1). alter table tb_name add primary key(column_list); 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL 2). alter table tb_name add unique index_name(column_list); 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次) 3). alter table tb_name add index index_name(column_list); 添加普通索引, 索引值可以出现多次。 4). alter table tb_name add fulltext index_name(column_list); 该语句指定了索引为FULLTEXT, 用于全文索引 复制代码
show status like 'Handler_read%'; -- 查看当前会话索引使用情况 show global status like 'Handler_read%'; -- 查看全局索引使用情况 复制代码
Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。
Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)。
Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。
Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。
Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
这里我们还扩展了索引下推,是一个十分重要的知识点,需要仔细回味。
推荐学习:mysql视频教程
Atas ialah kandungan terperinci Membawa anda melalui indeks MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!