Bagaimana untuk menggunakan indeks MySQL dengan baik? Artikel berikut akan berkongsi dengan anda beberapa perkara yang anda mesti tahu jika anda ingin menggunakan indeks MySQL dengan baik. Saya harap ia akan membantu anda!
Artikel ini akan membincangkan cara menggunakan indeks MySQL dengan baik.
Untuk menerangkan dengan lebih baik, saya mencipta jadual user_innodb dengan enjin storan InnoDB dan memulakan data 500W dalam kelompok. Ia mengandungi id kunci utama, medan nama (nama), medan jantina (jantina, 0,1 mewakili jantina berbeza), medan nombor telefon mudah alih (telefon) dan indeks bersama dicipta untuk medan nama dan telefon.
CREATE TABLE `user_innodb` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `gender` tinyint(1) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, PRIMARY KEY (`id`)， INDEX IDX_NAME_PHONE (name, phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Indeks boleh meningkatkan kecekapan pertanyaan dengan sangat berkesan Memandangkan ia sangat bagus, bolehkah saya mencipta indeks untuk setiap medan? ? Saya menasihati anda untuk tidak bersikap impulsif.
Semuanya mempunyai dua sisi, dan indeks tidak terkecuali. Penggunaan indeks yang berlebihan akan menyebabkan kita kehilangan ruang dan masa.
Indeks ialah pokok B Setiap kali anda membuat indeks, anda perlu mencipta pokok B Ia adalah halaman data. Setiap halaman data akan menduduki 16KB ruang cakera secara lalai Setiap B-tree akan mengandungi banyak halaman data. Jadi, buat sejumlah besar indeks dan ruang cakera anda akan digunakan dengan cepat.
Anda boleh menggunakan "kuasa wang" untuk menyelesaikan kos ruang, tetapi kami mungkin tidak berdaya tentang kos masa.
Penyelenggaraan senarai terpaut
Saya akan mengambil indeks kunci utama sebagai contoh Rekod dalam setiap nod pepohon B indeks kunci utama adalah mengikut tertib dari primer yang kecil ke yang besar nilai utama. Disambungkan menggunakan senarai terpaut sehala. Seperti yang ditunjukkan dalam rajah di bawah:
Jika saya mahu memadamkan rekod dengan id kunci utama 1 sekarang, ia akan memusnahkan pengisihan rekod dalam tiga data halaman, dan saya perlu memadamkan tiga halaman data Rekod di dalamnya disusun semula, dan perkara yang sama berlaku untuk operasi sisipan dan pengubahsuaian.
Nota: Biar saya ingatkan anda bahawa operasi pemadaman tidak akan menyusun semula rekod dalam halaman data dengan serta-merta, tetapi akan menandakan rekod yang dipadamkan dengan tanda pemadaman sehingga tiba masanya, rekod dialih keluar daripada senarai terpaut, tetapi ia akan sentiasa melibatkan penyelenggaraan pengisihan, yang pasti akan memakan prestasi.
Andaikan jadual ini mempunyai 12 medan dan kami menetapkan indeks untuk semua 12 medan jadual ini. Jika kami memadamkan 1 rekod, kami perlu melibatkan N halaman data bagi 12 penyisihan rekod dalaman dan penyelenggaraan.
Apa yang lebih teruk ialah apabila anda menambah, memadam atau mengubah suai rekod, ia boleh mencetuskan kitar semula dan pemisahan halaman data. Masih mengambil gambar di atas sebagai contoh, jika saya memadam rekod dengan id 13, maka data halaman 124 tidak perlu lagi wujud dan akan dikitar semula oleh enjin storan InnoDB jika saya memasukkan rekod dengan id 12, jika ruang daripada data halaman 32 tidak mencukupi Untuk menyimpan rekod ini, InnoDB perlu melakukan pemisahan halaman. Kita tidak perlu mengetahui butiran kitar semula halaman dan pemisahan halaman, tetapi kita boleh bayangkan betapa rumitnya operasi ini.
Jika anda mencipta indeks untuk setiap medan, anda boleh bayangkan kehilangan prestasi yang disebabkan oleh semua operasi penyelenggaraan indeks ini.
Pelan pertanyaan
Sebelum melaksanakan pernyataan pertanyaan, pengoptimum pertanyaan MySQL akan mengoptimumkan pernyataan pertanyaan berdasarkan kos dan menjana pelan pelaksanaan. Jika terlalu banyak indeks dicipta, pengoptimum akan mengira kos carian setiap indeks, yang akan mengambil terlalu banyak masa semasa proses analisis dan akhirnya menjejaskan kecekapan pelaksanaan pernyataan pertanyaan.
Saya akan ulang apa itu pulangan jadual, Kita boleh mencari nod daun dalam pokok B melalui indeks sekunder, tetapi kandungan nod daun dalam indeks sekunder tidak lengkap, hanya nilai lajur indeks dan nilai kunci primer. Kita perlu mengambil nilai kunci utama dan pergi ke nod daun indeks berkelompok (indeks kunci utama) untuk mendapatkan rekod pengguna yang lengkap Proses ini dipanggil pulangan jadual.
Dalam gambar di atas, saya mengambil nama indeks sekunder sebagai contoh, dan hanya melukis nod daun indeks sekunder dan nod daun indeks berkelompok, mengetepikan dua nod bukan daun pokok B.
Tiga baris yang memanjang dari nod daun indeks sekunder mewakili operasi pemulangan jadual.
Kos mencari nod daun indeks sekunder berdasarkan medan nama masih agak kecil, atas dua sebab:
但是二级索引叶子节点中的主键id的排布就没有任何规律了，毕竟name索引是对name字段进行排序的。进行回表的时候，极有可能出现主键id所在的记录在聚簇索引叶子节点中反复横跳的情况（正如上图中回表的3条线表示的那样），也就是随机IO。如果目标数据页恰好在内存中的话效果倒也不会太差，但如果不在内存中，还要从磁盘中加载一个数据页的内容（16KB）到内存中，这个速度可就太慢了。
是不是说完了回表的代价之后，我会给出一种更高效的搜索方式？不是，回表已经是一种比较高效的搜索方式了，我们需要做的就是尽量地减少回表操作带来的损耗，总结起来就是两点：
接下来先给大家介绍两个与回表相关的重要概念，这两个概念涉及到的方法也是索引使用原则的一部分，因为比较重要，在这里我把这两个概念先解释给大家听。
想一下，如果非聚簇索引的叶子节点上有你想要的所有数据，是不是就不需要回表了呢？比如我为name和phone字段创建了一个联合索引，如下图：
如果我们恰好只想搜索name、phone以及主键字段，
SELECT id, name, phone FROM user_innodb WHERE name = "蝉沐风";
可以直接从叶子节点获取所有数据，根本不需要回表操作。
我们把索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。
还是拿name和phone的联合索引为例，我们要查询所有name为「蝉沐风」，并且手机尾号为6606的记录，查询SQL如下：
SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";
由于联合索引的叶子节点的记录是先按照name字段排序，name字段相同的情况下再按照phone字段排序，因此把%加在phone字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有name字段可以使用索引进行快速比较和过滤。正常情况下查询过程是这个样子的：
InnoDB使用联合索引查出所有name为蝉沐风的二级索引数据，得到3个主键值：3485，78921，423476；
拿到主键索引进行回表，到聚簇索引中拿到这三条完整的用户记录；
InnoDB把这3条完整的用户记录返回给MySQL的Server层，在Server层过滤出尾号为6606的用户。
如下面两幅图所示，第一幅图表示InnoDB通过3次回表拿到3条完整的用户记录，交给Server层；第二幅图表示Server层经过phone LIKE "%6606"条件的过滤之后找到符合搜索条件的记录，返给客户端。
值得我们关注的是，索引的使用是在存储引擎中进行的，而数据记录的比较是在Server层中进行的。现在我们把上述搜索考虑地极端一点，假如数据表中10万条记录都符合name='蝉沐风'的条件，而只有1条符合phone LIKE "%6606"条件，这就意味着，InnoDB需要将99999条无效的记录传输给Server层让其自己筛选，更严重的是，这99999条数据都是通过回表搜索出来的啊！关于回表的代价你已经知道了。
现在引入索引下推。准确来说，应该叫做索引条件下推（Index Condition Pushdown，ICP），就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。ICP是在MySQL5.6之后完善的功能。
再回顾一下，我们第一步已经通过name = "蝉沐风"在联合索引的叶子节点中找到了符合条件的3条记录，而且phone字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历，筛选出尾号为6606的记录，找到主键值为78921的记录，最后只需要进行1次回表操作即可找到符合全部条件的1条记录，返回给Server层。
很明显，使用ICP的方式能有效减少回表的次数。
另外，ICP是默认开启的，对于二级索引，只要能把条件甩给下面的存储引擎，存储引擎就会进行过滤，不需要我们干预。
查看一下当前ICP的状态：
SHOW VARIABLES LIKE 'optimizer_switch';
执行以下SQL语句，并用EXPLAIN查看一下执行计划，此时的执行计划是Using index condition
EXPLAIN SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";
然后关闭ICP
SET optimizer_switch="index_condition_pushdown=off";
再查看一下ICP的状态
再次执行查询语句，并用EXPLAIN查看一下执行计划，此时的执行计划是Using where
EXPLAIN SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";
注：即使满足索引下推的使用条件，查询优化器也未必会使用索引下推，因为可能存在更高效的方式。
由于之前我给name字段创建了索引，导致一直没有使用索引下推，EXPLAIN语句显示使用了name索引，而不是name和phone的联合索引；删除name索引之后，才获得上述截图的效果。大家做实验的时候需要注意。
到目前为止大家应该清楚了索引和回表带来的性能问题，讲这些自然不是为了恐吓大家让大家远离索引，相反，我们要以正确的方式积极拥抱索引，最大限度降低其带来的负面影响，放大其优势。如何用好索引，从两个方面考虑：
拿我们文章开始创建的联合索引为例，该联合索引的B+树数据页内的记录首先按照name字段进行排序，name字段相同的情况下，再按照phone字段进行排序。
所以，如果我们直接使用phone字段进行搜索，无法利用索引的顺序性。
EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311";
EXPLAIN可以查看搜索语句的执行计划，其中，possible_keys列表示在当前查询中，可能用到的索引有哪一些；key列表示实际用到的索引有哪一些。
但是一旦加上name的搜索条件，就会使用到联合索引，而且不需要在意name在WHERE子句中的位置，因为查询优化器会帮我们优化。
EXPLAIN SELECT * FROM user_innodb WHERE phone = "13203398311" AND name = '蝉沐风';
MySQL在使用反向查询（!=, <>, NOT LIKE）的时候无法使用索引，会导致全表扫描，覆盖索引除外。
EXPLAIN SELECT * FROM user_innodb WHERE name != '蝉沐风';
当使用name LIKE '%沐风'或者name LIKE '%沐%'这两种方式都会使索引失效，因为联合索引的B+树数据页内的记录首先按照name字段进行排序，这两种搜索方式不在意name字段的开头是什么，自然就无法使用索引，只能通过全表扫描的方式进行查询。
EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '%沐风';
但是使用通配符结尾就没有问题
EXPLAIN SELECT * FROM user_innodb WHERE name LIKE '蝉沐%';
如果不是单纯使用索引列，而是对索引列做了其他操作，例如数值计算、使用函数、（手动或自动）类型转换等操作，会导致索引失效。
EXPLAIN SELECT * FROM user_innodb WHERE LEFT(name,3) = '蝉沐风';
MySQL8.0新增了函数索引的功能，我们可以给函数作用之后的结果创建索引，使用以下语句
ALTER TABLE user_innodb ADD KEY IDX_NAME_LEFT ((left(name,3)));
再次执行EXPLAIN语句，此时索引生效
EXPLAIN SELECT * FROM user_innodb WHERE id + 1 = 1100000;
换一种方式，单独使用id，就能高效使用索引：
EXPLAIN SELECT * FROM user_innodb WHERE id = 1100000 - 1;
例1
user_innodb中的phone字段为varchar类型，实验之前我们先给phone字段创建个索引
ALTER TABLE user_innodb ADD INDEX IDX_PHONE (phone);
随便搜索一个存在的手机号，看一下索引是否成功
EXPLAIN SELECT * FROM user_innodb WHERE phone = '13203398311';
可以看到能使用到索引，现在我们稍微修改一下，把phone = '13203398311'修改为phone = 13203398311，这意味着我们将字符串的搜索条件改成了整形的搜索条件，再看一下还会不会使用到索引：
EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;
显示索引失效。
例2
我们再看一个例子，主键id类型是bigint，但是在搜索条件中我估计使用字符串类型：
EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';
总结
稍微总结一下这个问题，当索引字段类型为字符串时，使用数字类型进行搜索不会用到索引；而索引字段类型为数字类型时，使用字符串类型进行搜索会使用到索引。
要搞明白这个问题，我们需要知道MySQL的数据类型转换规则是什么。简单地说就是MySQL会自动将数字转化为字符串，还是将字符串转化为数字。
一个简单的方法是，通过SELECT '10' > 9的结果来确定MySQL的类型转换规则：
mysql> SELECT '10' > 9; +----------+ | '10' > 9 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
上面的执行结果为1，说明MySQL遇到类型转换时，会自动将字符串转换为数字类型，因此对于例1:
EXPLAIN SELECT * FROM user_innodb WHERE phone = 13203398311;
就相当于
EXPLAIN SELECT * FROM user_innodb WHERE CAST(phone AS signed int) = 13203398311;
也就是对索引字段使用了函数，按照前文的介绍，对索引使用函数是不会使用到索引的。
对于例2:
EXPLAIN SELECT * FROM user_innodb WHERE id = '1099999';
就相当于
EXPLAIN SELECT * FROM user_innodb WHERE id = CAST('1099999' AS unsigned int);
没有在索引字段添加任何操作，因此能够使用到索引。
使用OR连接的查询语句，如果OR之前的条件列是索引列，但是OR之后的条件列不是索引列，则不会使用索引。举例：
EXPLAIN SELECT * FROM user_innodb WHERE id = 1099999 OR gender = 0;
上面总结了一些索引失效的场景，这些经验的总结往往对SQL的优化很有益处，但同时需要注意的是这些经验并非金科玉律。
比如使用<>查询时，在某些时候是可以用到索引的：
EXPLAIN SELECT * FROM user_innodb WHERE id <> 1099999;
最终是否使用索引，完全取决于MySQL的优化器，而优化器的判定依据就是cost开销（Cost Base Optimizer），优化器并非基于具体的规则，也不是基于语义，就是单纯地执行开销小的方案罢了。所以在·EXPLAIN·的结果中你会看到possible_keys一列，优化器会把这里边的索引都试一遍（是不是又加深了对不能随便创建索引的认识呢？），然后选一个开销最小的，如果都不太行，那就直接全表扫描好了。
而cost开销，和数据库版本、数据量等都有关系，因此如果想更精准地提升索引功能性，拥抱EXPLAIN吧！
之前讲过的索引覆盖和索引下推都可以作为索引创建的原则，就是在创建索引的时候，尽量发挥索引覆盖和索引下推的优势。
尽量避免上述提及到的索引可能失效的情况的出现，同样是索引的使用原则。
除此之外，再给大家介绍一些。
先来看一下列的离散度公式：COUNT(DISTINCT(column_name)) / COUNT(*)，列的不重复值的个数与所有数据行的比例。简而言之，如果列的重复值越多，列的离散度越低。重复值越少，离散度就越高。
举个例子，gender（性别）列只有0、1两个值，列的离散度非常低，假如我们为该列创建索引，我们会在二级索引中搜索到大量的重复数据，然后进行大量回表操作。大量回表哈？你懂了吧。
不要为重复值多的列创建索引
我们只为出现在WHERE子句中的列或者出现在ORDER BY和GROUP BY子句中的列创建索引即可。仅出现在查询列表中的列不需要创建索引。
用2条SQL语句来说明这个问题：
1. SELECT * FROM user_innodb WHERE name = '蝉沐风' AND phone = '13203398311';
2. SELECT * FROM user_innodb WHERE name = '蝉沐风';
语句1和语句2都能够使用索引，这带给我们的一个索引设计原则就是：
不要为联合索引的第一个索引列单独创建索引
因为联合索引本身就是先按照name列进行排序，因此联合索引对name的搜索是有效的，不需要单独为name再创建索引了。也正因为此
建立联合索引的时候，一定要把最常用的列放在最左边
如果一个字符串格式的列占用的空间比较大（就是说允许存储比较长的字符串数据），为该列创建索引，就意味着该列的数据会被完整地记录在每个数据页的每条记录中，会占用相当大的存储空间。
对此，我们可以为该列的前几个字符创建索引，也就是在二级索引的记录中只会保留字符串的前几个字符。比如我们可以为phone列创建索引，索引只保留手机号的前3位：
ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));
然后执行下面的SQL语句：
EXPLAIN SELECT * FROM user_innodb WHERE phone = '1320';
由于在IDX_PHONE_3索引中只保留了手机号的前3位数字，所以我们只能定位到以132开头的二级索引记录，然后在遍历所有的这些二级索引记录时再判断它们是否满足第4位数为0的条件。
当列中存储的字符串包含的字符较多时，为该字段建立前缀索引可以有效节省磁盘空间
因为可能涉及到数据页分裂的情况，会影响性能。
【相关推荐：mysql视频教程】
Atas ialah kandungan terperinci Bagaimana untuk menggunakan indeks MySQL dengan baik? Anda mesti tahu perkara-perkara ini!. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
