Apakah sintaks indeks MySQL

Takrif indeks

Indeks ialah struktur data tersusun yang membantu MySQL memperoleh data dengan cekap Ini ialah takrifan Rasmi MySQL. Untuk meningkatkan kecekapan pertanyaan, indeks ialah mekanisme yang ditambahkan pada medan dalam jadual pangkalan data. 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.

Kelebihan indeks

• Percepatkan kelajuan carian dan isih, kurangkan kos IO pangkalan data dan penggunaan CPU

• Dengan mencipta indeks yang unik, anda boleh memastikan keunikan setiap baris data dalam jadual pangkalan data.

Kelemahan indeks

1. Indeks sebenarnya jadual, yang menyimpan kunci utama dan medan indeks dan mata kepada entiti Rekod kelas itu sendiri perlu menduduki ruang

2. Walaupun ia meningkatkan kecekapan pertanyaan, untuk penambahan, pemadaman dan pengubahsuaian, setiap kali jadual ditukar, indeks perlu dikemas kini tambahan: secara semula jadi perlu berada dalam pepohon indeks Padamkan nod baharu: Rekod yang ditunjuk dalam pepohon indeks mungkin menjadi tidak sah, yang bermaksud bahawa banyak nod dalam pepohon indeks ini adalah perubahan tidak sah: menunjuk ke bagi nod dalam pepohon indeks mungkin perlu ditukar

Tetapi sebenarnya, kami tidak menggunakan pohon carian binari untuk menyimpan dalam MySQL.

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.

Struktur indeks

Indeks dilaksanakan dalam lapisan enjin storan MySQL, bukan dalam lapisan pelayan. Oleh itu, indeks mungkin berbeza antara enjin storan, dan tidak semua enjin menyokong semua jenis indeks.

• Indeks BTREE : Jenis indeks yang paling biasa, kebanyakan indeks menyokong indeks B-tree.

• Indeks HASH: hanya disokong oleh enjin Memori, senario penggunaannya mudah.

• Indeks R-tree (indeks ruang) : Indeks spatial ialah jenis indeks khas enjin MyISAM, terutamanya digunakan untuk jenis data geospatial, biasanya kurang digunakan , tiada pengenalan khas akan dibuat.

• Teks penuh (indeks teks penuh) : Indeks teks penuh juga merupakan jenis indeks khas MyISAM, terutamanya digunakan untuk indeks teks penuh menyokongnya bermula dari versi Mysql5.6 Indeks teks penuh.

Enjin storan MyISAM, InnoDB dan Memori menyokong pelbagai jenis indeks

索引	INNODB引擎	MYISAM引擎	MEMORY引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

Indeks

enjin INNODB

enjin MYISAM

Enjin MEMORI

Indeks BTREE

Disokong

Sokongan

Sokongan

Indeks HASH

Tidak disokong

Tidak disokong

Disokong

R- indeks pokok

Tidak disokong

Disokong

Tiada Sokongan

Teks Penuh

Disokong selepas versi 5.6

Disokong

Tidak disokong

Indeks yang biasa kami rujuk, melainkan dinyatakan secara eksplisit, disusun menggunakan struktur pepohon B+ (pokok carian berbilang hala, tidak semestinya binari). Indeks berkelompok, indeks majmuk, indeks awalan dan indeks unik yang dipanggil indeks semuanya menggunakan indeks pepohon B+ secara lalai.

BTREE

Pokok carian seimbang berbilang hala, pesanan m (m-fork) BTREE memenuhi:

• Setiap nod boleh mempunyai paling banyak m kanak-kanak. Nombor: ceil(m/2) hingga m Bilangan kata kunci: ceil(m/2)-1 hingga m-1

ceil bermaksud membulatkan, siling bermakna membulatkan (2.3)=3

Memasukkan kes kata kunci

Dijamin tidak memusnahkan sifat-sifat m-order B-tree

Disebabkan 3 Pesanan hanya boleh mempunyai 2 nod paling banyak, jadi 26 dan 30 adalah bersama pada mulanya, dan kemudian 85 akan mula berpecah 30 akan menjadi kedudukan tengah atas, 26 akan kekal, dan 85 akan pergi ke kanan
, iaitu: kedudukan tengah Naik , kemudian kekal di nod lama di sebelah kiri, dan pergi ke nod baharu

di sebelah kanan Pisahkan nod baharu keluar

Selepas ia mencapai tahap atas, ia perlu dibelah semula

Teruskan sahaja membelah ke atas , perkara yang sama

Kelebihan Perbandingan

Berbanding dengan pepohon carian binari, ketinggian/kedalaman lebih rendah dan kecekapan pertanyaan semula jadi lebih tinggi.

B+TREE

• B+ tree mempunyai dua jenis nod: nod dalaman (juga dipanggil nod indeks) dan Nod daun . Nod dalaman ialah nod bukan daun. Nod dalaman tidak menyimpan data, hanya indeks dan data disimpan dalam nod daun.

• Kunci dalam nod dalaman disusun mengikut urutan dari kecil ke besar Untuk kunci dalam nod dalaman, semua kunci dalam pepohon kiri adalah lebih kecil daripada Ia, kekunci dalam subpokok kanan lebih besar daripada atau sama dengannya. Rekod dalam nod daun juga disusun mengikut saiz kunci.

• Setiap nod daun menyimpan penunjuk ke nod daun bersebelahan, dan nod daun itu sendiri disambungkan mengikut urutan dari kecil ke besar mengikut saiz kata kunci .

• Nod induk menyimpan indeks elemen pertama anak kanan.

Kelebihan perbandingan

• Kecekapan pertanyaan B+Treelebih 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 melintasi keseluruhan pokok.

B+Tree dalam MySQL

Struktur data indeks MySql mengoptimumkan B+Tree klasik. Berdasarkan B+Tree yang asal, penunjuk senarai terpaut yang menghala ke nod daun bersebelahan (struktur keseluruhan adalah serupa dengan senarai terpaut berganda) ditambah untuk membentuk B+Tree dengan penuding berjujukan , yang meningkatkan kecekapan capaian selang waktu.

Pelajar yang berhati-hati dapat melihat bahawa apakah perbezaan terbesar antara gambar ini dan gambar rajah pepohon carian binari kami?

• Beralih daripada pokok carian binari kepada B-tree , perubahan ketara ialah satu nod boleh menyimpan berbilang data, yang bersamaan dengan satu blok cakera Boleh menyimpan berbilang data, sangat mengurangkan masa IO kami! !

Rajah struktur indeks B+Tree dalam MySQL:

Rajah pepohon carian binari:

Prinsip indeks

Indeks BTree:

Pengenalan permulaan

Yang biru muda dipanggil blok cakera, anda boleh melihat setiap Blok cakera mengandungi beberapa item data (ditunjukkan dalam warna biru tua) dan penunjuk (ditunjukkan dalam warna kuning)
Contohnya, blok cakera 1 mengandungi item data 17 dan 35, termasuk penunjuk P1, P2 dan P3
P1 bermakna kurang daripada 17 blok cakera , P2 mewakili blok cakera antara 17 dan 35, P3 mewakili blok cakera lebih daripada 35.

• Data sebenar wujud dalam nod daun iaitu 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. `

• Nod bukan daun tidak menyimpan data sebenar, tetapi hanya menyimpan item data yang membimbing arah carian Contohnya, 17 dan 35 sebenarnya tidak wujud dalam jadual data. `

Proses carian

Jika anda ingin mencari item data 29, maka blok cakera 1 mula-mula akan dimuatkan daripada 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 IO berlaku untuk setiap item, sejumlah jutaan IO akan diperlukan. Jelas sekali, kosnya sangat, sangat tinggi.

Klasifikasi indeks

Jadual tersusun indeks ialah jadual yang disimpan dalam susunan kunci utama sebagai indeks. Kaedah ini sesuai untuk enjin InnoDB. Memandangkan InnoDB menggunakan model indeks pepohon B+, data disimpan dalam pepohon B+.

Setiap indeks sepadan dengan pepohon 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), (500,5) dan (600,6), contoh rajah dua pokok adalah seperti berikut:

Ia tidak sukar untuk dilihat daripada rajah bahawa mengikut kandungan nod daun , Jenis indeks dibahagikan kepada indeks kunci utama dan indeks kunci bukan utama.

Indeks kunci 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

indeks tambahan ialah nilai kunci utama. Dalam InnoDB, indeks tambahan juga dipanggil indeks sekunder (indeks sekunder).

Seperti yang ditunjukkan di bawah:

• Indeks kunci utama menyimpan keseluruhan baris data

• bantu Indeks hanya menyimpan sendiri, dan kunci utama id digunakan untuk pertanyaan jadual

Mengikut struktur indeks di atas, mari kita bincangkan soalan: Apakah perbezaan antara pertanyaan berdasarkan indeks kunci primer dan indeks sekunder?

• Jika pernyataan dipilih * daripada T dengan ID=500, iaitu kaedah pertanyaan kunci utama, anda hanya perlu mencari pepohon B+ ID;

• Jika penyataan dipilih * daripada T dengan k=5, iaitu kaedah pertanyaan indeks biasa, anda perlu mencari pokok indeks k dahulu, dan dapatkan nilai ID 500, dan kemudian Cari sekali dalam pepohon indeks ID . Proses ini dipanggil Kembali ke Jadual.

Dalam erti kata lain, pertanyaan berdasarkan indeks tambahan perlu mengimbas satu lagi pokok indeks. Oleh itu, kita harus cuba menggunakan pertanyaan kunci utama dalam aplikasi kita.

Melainkan data yang kami ingin tanya kebetulan wujud pada pokok indeks kami, dalam kes ini kami memanggilnya indeks tertutup-iaitu, lajur indeks mengandungi Semua data kami kepada dipersoalkan.

Pada masa yang sama, indeks sekunder dibahagikan kepada jenis berikut (langkau sebentar, kami akan mengetahui lebih lanjut mengenainya kemudian):

• Kunci Unik: Indeks unik juga merupakan kekangan. Data pendua tidak boleh muncul dalam lajur atribut indeks unik, tetapi data dibenarkan menjadi NULL. Jadual membolehkan penciptaan berbilang indeks unik. Selalunya, tujuan mewujudkan indeks unik adalah untuk keunikan data dalam lajur atribut, bukannya untuk kecekapan pertanyaan.

• Indeks Biasa (Indeks): Satu-satunya fungsi indeks biasa ialah untuk menanya data dengan pantas. dan membenarkan Data diduakan dan NULL.

• Indeks awalan (Awalan): Indeks awalan hanya terpakai pada data jenis rentetan. Indeks awalan mencipta indeks pada beberapa aksara pertama teks Berbanding dengan indeks biasa, data yang dibuat adalah lebih kecil kerana hanya beberapa aksara pertama diambil.

• Indeks Teks Penuh (Teks Penuh): Indeks teks penuh digunakan terutamanya untuk mendapatkan maklumat kata kunci dalam data teks besar Ia adalah jenis yang digunakan oleh enjin carian teknologi pangkalan data. Sebelum Mysql5.6, hanya enjin MYISAM yang menyokong indeks teks penuh Selepas 5.6, InnoDB juga menyokong indeks teks penuh

Sambungan--Penolak Indeks

Tekanan yang dipanggil, seperti namanya, sebenarnya menangguhkan operasi pemulangan jadual kami tidak akan membenarkan kami lakukannya dengan mudah, kerana ia sangat membazir. Apakah maksudnya? Pertimbangkan contoh berikut.

Kami menubuhkan indeks komposit (nama, status, alamat), yang juga disimpan mengikut medan ini, sama seperti gambar:

Pokok indeks kompaun (hanya menyimpan lajur indeks dan The kunci utama digunakan untuk mengembalikan jadual)

name	status	address	id(主键)
小米1	0	1	1
小米2	1	1	2

nama

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' ;
复制代码

1. 首先我们在复合索引树上，找到了第一个以小米开头的name -- 小米1

2. 此时我们不着急回表(回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断status是否=1，此时status=0，我们直接就不回表了，直接继续找下一个以小米开头的name

1. 找到第二个-- 小米2，判断status=1，则根据id=2去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的where条件，不满足则直接pass掉，不进行回表的操作，就叫做索引下推。

最左前缀原则

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效

   

2. 出现跳跃的情况

• 直接第一层name都不走，当然都失效

  

• 走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有name成功）

  

• 同时，这个顺序并不是由我们where中的排列顺序决定，比如： where name='小米科技' and status='1' and address='北京市' where status='1' and name='小米科技' and address='北京市'

这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

其实是因为我们MySQL有一个Optimizer（查询优化器），查询优化器会将SQL进行优化，选择最优的查询计划来执行。

• 关于这个查询优化器，后续文章我们也会谈谈MySQL的逻辑架构与存储引擎

索引设计原则

针对表

1. 查询频次高，且数据量多的表

针对字段

1. 最好从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

其他原则

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

比如：

我们创建了三个单列索引，name，status，address

当我们where中根据status和address两个字段来查询时，数据库只会选择最优的一个索引，不会所有单列索引都使用。

最优的索引：具体是指所查询表中，辨识度最高(所占比例最少)的索引列，比如此处address中有一个辨识度很高的 '西安市'数据；

1. 使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

2. 利用最左前缀，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引

也就是说，我们尽量创建复合索引，而不是单列索引

创建复合索引:
	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
复制代码

如果要在此处建立复合索引，我们要遵循什么原则呢？

通过调整顺序，可以少维护一个索引

• 比如我们的业务需求里边，有如下两种查询方式： 根据name查询 根据name和age查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据age，根据age和name查询，并不能单纯根据name查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个name索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个name索引，这就是通过调整顺序，可以少维护一个索引。

考虑空间->短索引

• 比如我们的业务需求里边，有以下两种查询方式： 根据name查询 根据age查询 根据name和age查询

我们有两种方案：

1. 建立联合索引(name,age)，建立单列索引：age索引。

2. 建立联合索引(age,name)，建立单列索引：name索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name字段是比age字段大的，显然方案1所耗费的空间是更小的，所以我们更倾向于方案1。

何时建立索引

1. where中的查询字段

2. 查询中与其他表关联的字段，比如外键

3. 排序的字段

4. 统计或分组的字段

何时达咩索引

1. 表中数据量很少

2. 经常改动的表

3. 频繁更新的字段

4. 数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为O(logn)退化了）

索引相关语法

创建索引

默认会为主键创建索引--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：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

Atas ialah kandungan terperinci Apakah sintaks indeks MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!