Indeks MySQL terkawal

Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan isu berkaitan tentang indeks mysql, termasuk pembahagian indeks mengikut fungsi logik, pembahagian mengikut pelaksanaan fizikal dan pembahagian mengikut bidang individu Isu jenis indeks seperti pembahagian nombor, saya harap ia dapat membantu semua orang.

Pembelajaran yang disyorkan: tutorial mysql

Dalam pengoptimuman SQL, indeks adalah bahagian penting, yang boleh meningkatkan kecekapan pertanyaan Ia membawa lompatan kualitatif, tetapi indeks tidak mahakuasa, dan reka bentuk indeks yang tidak munasabah malah boleh memperlahankan kecekapan pertanyaan.

Definisi indeks

Indeks ialah struktur data yang direka khusus untuk membantu SQL memperoleh data dengan cekap Contoh biasa ialah indeks serupa dengan jadual kandungan buku, yang boleh dengan cepat cari nilai tertentu Cari dan cari, dengan itu mempercepatkan kecekapan pertanyaan data. Malah, indeks juga jadual ini menyimpan kunci utama dan medan indeks dan menunjuk kepada rekod jadual entiti (serupa dengan penunjuk).

Kelebihan dan keburukan indeks

Kelebihan

• Indeks sangat mengurangkan jumlah data yang perlu diimbas oleh pelayan
• Indeks boleh Membantu pelayan mengelakkan pengisihan dan jadual sementara
• Indeks boleh menukar IO rawak kepada IO berurutan
• Indeks sangat penting untuk InnoDB (yang menyokong kunci peringkat baris pada indeks), InnoDB sahaja mengakses tupel yang perlu diakses Mengunci dan mengindeks boleh mengurangkan bilangan tupel yang diakses oleh InnoDB. Jika pertanyaan tidak boleh menggunakan indeks, MySQL akan melakukan imbasan jadual penuh dan mengunci setiap tuple, tidak kira sama ada ia sebenarnya diperlukan.

Kelemahan

• Walaupun indeks sangat meningkatkan kelajuan pertanyaan, ia juga akan mengurangkan kelajuan mengemas kini jadual. Kerana semasa mengemas kini jadual, MySQL bukan sahaja perlu menyimpan data, tetapi juga menyimpan fail indeks. Oleh itu, biasanya tidak disyorkan untuk menggunakan indeks untuk medan yang dikemas kini dengan kerap.
• Pengindeksan mengambil ruang cakera.
• Jika lajur data mengandungi banyak kandungan berulang, kesan pengindeksan untuknya akan menjadi sangat lemah. Sifat ini dipanggil selektiviti indeks: nisbah nilai indeks unik kepada jumlah bilangan rekod dalam jadual data. Semakin tinggi selektiviti indeks, semakin tinggi kecekapan pertanyaan. Contohnya, jika anda membuat indeks pada medan jantina, satu juta keping data hanya boleh dilakukan untuk lelaki dan wanita Pemilihan indeks adalah satu dalam 500,000 dan kesan pengindeksan adalah sangat lemah
• Untuk yang sangat kecil. jadual, indeks tidak bermakna Besar, imbasan jadual penuh ringkas adalah lebih cekap dalam kebanyakan kes.

Oleh itu, hanya lajur data yang paling kerap ditanya dan paling kerap diisih harus diindeks. Jumlah bilangan indeks dalam jadual data yang sama dalam MySQL adalah terhad kepada 16.

Jenis indeks

Dibahagikan mengikut logik berfungsi

Dibahagikan mengikut logik berfungsi, indeks terutamanya dibahagikan kepada indeks biasa, indeks unik, indeks kunci primer dan teks penuh indeks

Indeks Normal

Indeks yang paling asas, ia tidak mempunyai sekatan. Satu-satunya tugas indeks biasa (indeks yang ditakrifkan oleh kata kunci KEY atau INDEX) adalah untuk mempercepatkan akses kepada data. Oleh itu, indeks hanya perlu dibuat untuk lajur data yang paling kerap muncul dalam keadaan pertanyaan (WHERE lajur = ...) atau keadaan isihan (ORDER BY lajur).

Terdapat tiga cara untuk mencipta indeks biasa.

# 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	INDEX idx_username (username) );

Padam indeks

DROP INDEX idx_username ON user_tbl;

Lihat indeks

SHOW INDEX FROM user_tbl;

Indeks unik

Ia adalah sama seperti Indeks biasa sebelumnya adalah serupa, kecuali indeks biasa membenarkan lajur data yang diindeks mengandungi nilai pendua. Nilai lajur indeks unik mestilah unik, tetapi nilai nol dibenarkan. Dalam kes indeks komposit, gabungan nilai lajur mestilah unik.

Penciptaan indeks unik adalah serupa dengan indeks biasa:

#创建索引
CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表结构
ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 创建表的时候直接指定
CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	UNIQUE idx_username (username) 
);

Indeks kunci utama

Ia ialah indeks unik khas yang tidak dibenarkan nilai NULL. Jadual hanya boleh mempunyai satu kunci utama, yang biasanya dibuat pada masa yang sama apabila jadual dibuat.

CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	PRIMARY KEY(ID) 
);

Serupa dengannya ialah indeks kunci asing Jika kekangan kunci asing ditakrifkan untuk medan kunci asing, MySQL akan menentukan indeks dalaman untuk membantu dirinya sendiri dengan cara yang paling cekap kekangan utama.

Indeks teks penuh

Dalam artikel sebelumnya sintaks asas MySQL, kami mengatakan bahawa jika LIKE % digunakan pada mulanya, indeks akan menjadi tidak sah, jadi apabila kita perlu Jika anda memerlukan carian kabur (seperti SUKA '%hello%'), anda perlu menggunakan pengindeksan teks penuh Perlu diingat bahawa Innodb hanya menyokong pengindeksan teks penuh selepas versi 5.6.

Penciptaan dan pemadaman indeks teks penuh:

# 创建的两种方法
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 删除的两种方法
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

Sintaks menggunakan indeks teks penuh untuk pemadanan kabur penuh ialah:

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx');
# 比如对 user_tbl 的 user_name 字段加了全文索引
# 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%';
SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

Gunakan semak penjelasan untuk mencari indeks teks penuh Berkuatkuasa.

Dibahagikan dengan pelaksanaan fizikal

Dibahagikan dengan pelaksanaan fizikal, ia biasanya boleh dibahagikan kepada indeks berkelompok dan indeks tidak berkelompok.

聚集索引（clustered index）

存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。

聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。

需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。

非聚集索引（non-clustered index）

非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。

非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。

按字段个数划分

按字段个数可以把索引分为单一索引和联合索引。

单一索引

索引字段只有一列时为单一索引，上述所有索引都是单一索引。

联合索引

将多个字段组合在一起创建的索引叫联合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原则

建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：

usernname,city,age
usernname,city
usernname

为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的最左匹配原则，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。

对于 (x,y,z)，只有在以下查询条件联合索引会生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

对于其他情况，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不会匹配联合索引，索引失效，注意对于 WHERE x = 1 AND z = 1，联合索引会对 x 生效，但是对 z 不生效。

可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。

要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

顺便提一下，可以用 explain 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。

如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：

1. MySQL 优化器根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。

按索引结构划分

不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。

使用总结

接下来我们来简单总结一下在什么场景下推荐使用索引。

推荐使用

• WHERE, GROUP BY, ORDER BY 子句中的字段

• 多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。

  联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那么联合索引应该设计为 (a,b,c)，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。

• 多张表 JOIN 的时候，对表连接字段创建索引。

• Apabila terdapat medan dalam SELECT yang tiada dalam indeks, nilai kunci utama yang memenuhi syarat akan disoal melalui indeks terlebih dahulu, dan kemudian semua medan dalam SELECT akan disoal melalui kunci utama 回表, menjejaskan kecekapan. Oleh itu, jika terdapat sangat sedikit kandungan dalam SELECT, untuk mengelakkan daripada mengembalikan jadual, anda boleh menambah semua medan dalam SELECT ke indeks bersama Ini adalah konsep indeks lebar. Walau bagaimanapun, perlu diingatkan bahawa jika terdapat terlalu banyak medan indeks, kos penyimpanan dan penyelenggaraan indeks juga akan meningkat.

Tidak disyorkan atau kegagalan indeks

• Jadual dengan volum data yang kecil

• Medan dengan banyak data pendua

• Medan yang kerap dikemas kini

• Jika fungsi atau pengiraan ungkapan digunakan untuk medan indeks , kegagalan indeks

• innodb ATAU keadaan tidak mencipta indeks untuk semua keadaan, kegagalan indeks

• lebih besar daripada kurang daripada syarat <code>>, indeks Sama ada ia berkuat kuasa bergantung pada nisbah bilangan hit Jika bilangan hits adalah besar, indeks akan berkuat kuasa Jika bilangan hits adalah kecil, indeks akan gagal 🎜> tidak sama dengan syarat

, dan indeks akan gagal

• !=Nilai LIKE bermula dengan

  , indeks tidak sah

• %Pembelajaran yang disyorkan:

  tutorial video mysql

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