Apakah indeks awalan dalam mysql

Dalam mysql, indeks awalan ialah jenis indeks khas yang mengindeks beberapa aksara pertama teks jenis indeks ini boleh mengurangkan saiz indeks ke tahap tertentu dan boleh memproses beberapa data operasi pertanyaan khusus dengan lebih cekap. Biasanya, panjang indeks hendaklah sesingkat mungkin, kerana indeks pendek boleh mengurangkan saiz indeks, tetapi jika panjang indeks terlalu pendek, kegagalan indeks mungkin berlaku, menyebabkan kecekapan pertanyaan yang lebih perlahan, apabila menggunakan indeks awalan melakukan ini, anda perlu memilih panjang indeks yang sesuai mengikut situasi tertentu.

Persekitaran pengendalian tutorial ini: sistem windows7, versi mysql8, komputer Dell G3.

1. Apakah itu indeks awalan

MySQL ialah sistem pengurusan pangkalan data hubungan yang digunakan secara meluas yang mempunyai kecekapan dan kecekapan tinggi dalam penyimpanan dan pengambilan data. Dalam MySQL, indeks ialah struktur data yang digunakan untuk mempercepatkan operasi pertanyaan, yang boleh meningkatkan kelajuan dan kecekapan pertanyaan. Indeks awalan ialah jenis indeks khas dalam MySQL, yang boleh mengendalikan beberapa operasi pertanyaan tertentu dengan lebih cekap.

Apa yang dipanggil indeks awalan, secara terang-terangan, adalah untuk mengindeks beberapa aksara pertama teks ( secara khusus adalah beberapa aksara untuk ditentukan semasa mencipta indeks ), contohnya , 10 aksara pertama nama produk Untuk membina indeks, indeks yang dibuat dengan cara ini akan menjadi lebih kecil dan kecekapan pertanyaan akan menjadi lebih pantas!

Indeks awalan bermaksud hanya mengindeks bahagian awalan rentetan, bukan keseluruhan rentetan. Jenis indeks ini boleh mengurangkan saiz indeks ke tahap tertentu, contohnya, jika anda mempunyai jadual yang mengandungi sepuluh juta rentetan, setiap rentetan adalah 100 aksara, jika anda menggunakan rentetan lengkap sebagai indeks, maka indeks Saiz akan menjadi sangat besar dan kecekapan pertanyaan akan menjadi sangat rendah. Tetapi jika anda hanya mengindeks 10 aksara pertama rentetan, saiz indeks akan dikurangkan dengan ketara dan kecekapan pertanyaan akan dipertingkatkan dengan ketara.

Ia agak serupa dengan menggunakan fungsi Kiri pada medan dalam Oracle untuk mencipta indeks fungsi, kecuali indeks awalan MySQL secara automatik melengkapkan pemadanan secara dalaman semasa pertanyaan dan tidak perlu menggunakan fungsi Kiri.

Dalam MySQL, menggunakan indeks awalan memerlukan menentukan panjang indeks. Biasanya, panjang indeks hendaklah sesingkat mungkin, kerana indeks pendek boleh mengurangkan saiz indeks Walau bagaimanapun, jika panjang indeks terlalu pendek, kegagalan indeks mungkin berlaku, mengakibatkan kecekapan pertanyaan yang lebih perlahan. Oleh itu, apabila menggunakan indeks awalan, anda perlu memilih panjang indeks yang sesuai mengikut situasi tertentu.

2. Mengapa menggunakan indeks awalan

Sesetengah pelajar mungkin bertanya, mengapa tidak mengindeks seluruh medan?

Secara umumnya, apabila jumlah data dalam medan tertentu terlalu besar dan pertanyaan adalah sangat kerap, menggunakan indeks awalan boleh mengurangkan saiz fail indeks dengan berkesan, membolehkan setiap halaman indeks menyimpan lebih banyak nilai indeks , sekali gus meningkatkan kelajuan pertanyaan indeks.

Sebagai contoh, beberapa nama kedai pelanggan sangat panjang dan ada yang sangat pendek Jika indeks dibina berdasarkan liputan penuh, ruang storan indeks mungkin sangat besar diindeks Banyak daripadanya dicipta, malah ruang storan indeks jauh lebih besar daripada ruang storan jadual data , jadi untuk medan jenis ini dengan teks panjang, kita boleh memintas beberapa aksara pertama untuk dibina indeks Pada tahap tertentu, Ia bukan sahaja dapat memenuhi keperluan kecekapan pertanyaan data, tetapi juga menjimatkan ruang penyimpanan indeks.

Tetapi sebaliknya, indeks awalan juga mempunyai kekurangannya. Indeks awalan tidak boleh digunakan untuk ORDER BY dan GROUP BY dalam MySQL, dan ia juga tidak boleh digunakan untuk pengimbasan liputan agak panjang, dan beberapa aksara pertama adalah sama Pada masa ini, kelebihan indeks awalan tidak lagi jelas, dan tidak perlu mencipta indeks awalan.

Jadi ini kembali kepada konsep selektiviti

tentang indeks!

Mengenai selektiviti indeks jadual pangkalan data, saya akan menerangkannya dalam artikel berasingan Anda hanya perlu ingat satu perkara:

Semakin tinggi selektiviti indeks, semakin tinggi kecekapan pertanyaan . , kerana selektiviti adalah tinggi Indeks membolehkan MySQL menapis lebih banyak baris semasa mencari, dan kelajuan pertanyaan data lebih pantas!

Apabila beberapa digit pertama kandungan medan tertentu sangat boleh dibezakan, menggunakan indeks awalan pada masa ini boleh mencapai prestasi kos yang sangat tinggi dari segi prestasi pertanyaan dan storan ruang.

Kemudian persoalannya ialah, bagaimana hendak membuat indeks awalan?

3. Cara membuat indeks awalan

Cara untuk mencipta indeks awalan adalah sangat mudah.

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

Parameter

bermaksud panjang awalan Ia biasanya disahkan dengan kaedah berikut: prefix_length

Langkah pertama ialah untuk mengira Perbezaan semua lajur medan.

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

Langkah kedua ialah mengira panjang awalan yang paling serupa dengan diskriminasi keseluruhan lajur

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增！

四、使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

【相关推荐：mysql视频教程】

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