MySQL Advanced Learning : explication détaillée de la façon de créer des index efficaces et appropriés

Cet article est une étude avancée de MySQL. Il vous donnera une compréhension détaillée de la façon de créer un index plus approprié. J'espère qu'il vous sera utile !

Je ne connais pas l'importance de l'indexation lorsqu'il y a plus de données dans la bibliothèque, et encore moins l'importance des index appropriés lorsqu'il y a plus de données dans la bibliothèque. Cet article présente comment créer un index efficace et approprié. [Recommandations associées : Tutoriel vidéo MySQL]

1. Lorsque vous utilisez des colonnes d'index pour interroger, essayez de ne pas utiliser d'expressions et placez le calcul dans la couche métier au lieu de la couche de base de données

Les résultats des deux SQL affichés ci-dessous. sont les mêmes, mais les plans d'exécution des deux SQL sont différents. L'efficacité de l'index dans le type est bien inférieure à celle de l'acteur_id+4 dans const où l'expression affecte le plan d'exécution. pour expliquer pour une explication détaillée

2. Essayez d'utiliser des requêtes de clé primaire au lieu d'autres index. Les requêtes de clé primaire ne provoqueront pas de requêtes de retour de table.

Toutes nos tables ont essentiellement des clés primaires, donc dans le développement normal, utilisez des index s'ils peuvent être utilisés, et utilisez des index de clé primaire s'ils peuvent être utilisés.

3. Utilisez l'index de préfixe

Souvent, nos index sont en fait des chaînes, et de longues chaînes apparaîtront inévitablement, ce qui fera que l'index prendra trop de place et réduira son efficacité. Surtout pour les longues colonnes telles que blob, text et varchar. À l'heure actuelle, la façon de résoudre ce problème n'est pas d'utiliser la valeur complète du champ comme index, mais uniquement de prendre la première moitié (la sélectivité de l'index de préfixe sélectionné est proche de celle de la colonne entière). Cela peut réduire considérablement l'espace de l'index, améliorant ainsi l'efficacité. L'inconvénient est que cela réduit la sélectivité de l'index.

Sélectivité de l'index : le rapport entre les valeurs d'index uniques et le nombre total d'enregistrements de la table de données (#T), allant de 1/#T à 1. Plus la sélectivité de l'index est élevée, plus l'efficacité des requêtes est élevée, car les données sont hautement différenciées et davantage de lignes peuvent être filtrées. La sélectivité de l'indice unique est de 1 et ses performances sont les meilleures.

Par exemple, dans le champ email de la table des employés de l'entreprise, les suffixes email d'une entreprise sont tous les mêmes, comme xxxx@qq.com En fait, la seule utilisation efficace de l'e-mail comme index est la partie xxxx, car @. qq.com est le même, ce qui n'a aucun sens pour l'index. Oui, évidemment, seul xxxx est utilisé comme index, et sa sélectivité est la même que celle de la valeur entière. Cependant, utiliser xxxx comme index réduira évidemment l'index. espace.

Ci-dessous nous avons pris comme exemple la table des employés (voir la fin de l'article pour la structure et les données de la table)

Nous prenons le champ email comme exemple pour indexer :

L'adresse email de ces données est en réalité un mobile numéro de téléphone +@qq.com à titre d'exemple, en fait Les 11 premiers chiffres et les suivants sont tous identiques. J'utilise le SQL suivant pour voir le calcul de sélectivité de ces données (prendre respectivement les 10, 11, 12 premiers).

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

D'après la figure ci-dessus, nous pouvons voir que la sélectivité du top 10, du top 11 et du top 12 est respectivement de 0,14, 1,0 et 1,0. À la 11ème position, la sélectivité de l'indice est la plus élevée de 1, donc là. Il n'est pas nécessaire de tout utiliser. En tant qu'index, l'espace d'index est augmenté.

-- 创建前缀索引
alter table employee add key(email(11));

Nous pouvons également utiliser le nombre pour calculer la fréquence des statistiques (moins il y a d'occurrences, plus le taux de répétition est faible et plus la sélectivité est grande)

-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;

4. Utilisez l'analyse d'index pour trier

Nous avons souvent un tri pour. selon vos besoins, utilisez order by, mais order by affecte les performances. Il trie les données en les chargeant dans la mémoire. Si la quantité de données est trop importante et ne peut pas être stockée dans la mémoire, elle ne peut être traitée que plusieurs fois. Cependant, l'index lui-même est ordonné et il est plus facile d'effectuer le tri directement via l'index.

L'analyse de l'index lui-même est rapide, car il vous suffit de passer d'un enregistrement d'index à l'enregistrement suivant, mais si l'index ne peut pas couvrir toutes les colonnes requises pour la requête, vous devez revenir à la table à chaque fois que vous analysez un enregistrement d'index Interroge une fois la ligne correspondante, ce qui est essentiellement une E/S aléatoire. Par conséquent, la lecture des données dans l’ordre de l’index est généralement plus lente qu’une analyse séquentielle de table complète.

MySQL peut utiliser le même index pour satisfaire à la fois le tri et la recherche de lignes. Veuillez envisager de créer un tel index si possible.

Seulement lorsque l'ordre des colonnes d'index est complètement cohérent avec l'ordre de la clause order by et que le sens de tri (ordre arrière ou avant) de toutes les colonnes est le même, MySQL peut utiliser l'index pour trier les résultats. Si la requête doit être liée à plusieurs tables, le tri par index ne peut être utilisé que lorsque les champs de la clause order by proviennent tous de la première table. L'ordre par requête doit également satisfaire le préfixe le plus à gauche de l'index combiné, sinon le tri par index ne peut pas être utilisé.

En fait, il y a deux points principaux auxquels il faut prêter attention lors du développement :

• Les champs dans la condition Where et les champs classés par peuvent être des index combinés et satisfaire le préfixe le plus à gauche.
• L'ordre des champs triés par doit être cohérent. Desc et asc ne peuvent pas exister.

5. Union all, in, ou peut all utiliser des index, mais il est recommandé d'utiliser in

Comme ci-dessus, union all sera exécuté deux fois, tandis que in et ou une seule fois. En même temps, on voit que les plans d'exécution de or et in sont les mêmes,

mais on regarde leur temps d'exécution. Comme indiqué ci-dessous, utilisez set profiling=1 pour voir l'heure détaillée et utilisez show profiles pour afficher l'heure spécifique. L'image ci-dessous montre que l'heure de or est de 0,00612000 et l'heure de in est de 0,00022800. L'écart est toujours très grand (les données de la table de test n'ont que 200 lignes)set profiling=1可以看到详细时间，使用show profiles 查看具体时间。下图看出or的时间0.00612000，in的时间0.00022800，差距还是很大的(测试的表数据只有200行)

union all: 查询分为了两阶段，其实还有一个union，在平时开发中必须使用到union的时候推荐使用union all，因为union中多出了distinct去重的步骤。所以尽量用union all。

6. 范围列可以用到索引

范围的条件：>,>=,

范围列可以用到索引，但是范围列后面的列就无法用到索引了(索引最多用于一个范围列)

比如一个组合索引age+name 如果查询条件是where age>18 and name="纪"后面的name是用不到的索引的。

曾经面试被问到不等于是否能够走某个索引，平时没有注意过也没有回答成功，这次亲自做个实验，关于结论请看文末。

7. 强制类型转换会全表扫描

我在employee表中定义了mobile字段是varchar类型且建立索引，我分别用数字和字符串查询.

看看结果: 两者type是不一样的，而且只有字符串才用到索引。

如果条件的值的类型和表中定义的不一致，那么mysql会强制进行类型转换，但是结果是不会走索引，索引在开发中我们需要根据自己定义的类型输入对应的类型值。

8. 数据区分度不高，更新频繁的字段不宜建立索引

• 索引列更新会变更B+树的，频繁更新的会大大降低数据库性能。
• 类似于性别这类(只有男女，或者未知)，不能有效过滤数据。
• 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)

9. 创建索引的列不允许为null，可能会得到不符合预期的结果

也就是建立索引的字段尽量不要为空，可能会有些意想不到的问题，但是实际工作中也不太可能不为空，所以根据实际业务来处理吧，尽量避免这种情况。

10. 当需要进行表连接的时候，最好不要超过三张表

表连接其实就是多张表循环嵌套匹配，是比较影响性能的， 而且需要join的字段数据类型必须一致，提高查询效率。关于表连接原理后面专门写一篇吧。

11. 能使用limit的时候尽量使用limit。

limit的作用不是仅仅用了分页，本质作用是限制输出。

limit其实是挨个遍历查询数据，如果只需要一条数据添加 limit 1的限制，那么索引指针找到符合条件的数据之后就停止了，不会继续向下判断了，直接返回。如果没有limit，就会继续判断。

但是如果分页取1万条后的5条limit 10000,10005

union all : La requête est divisée en deux étapes. En fait, il existe également une union, qui doit être utilisée dans le développement quotidien. Il est recommandé d'utiliser union all lors de l'union, car il y a une étape supplémentaire de déduplication distincte dans l'union. Essayez donc d'utiliser l'union all.

6. Les colonnes de plage peuvent être utilisées pour l'indexation

Conditions de plage : >,>=,

Les colonnes de plage peuvent être utilisées. Index, mais les colonnes après la colonne de plage ne peuvent pas utiliser l'index (l'index peut être utilisé pour au plus une colonne de plage)

Par exemple, un index combiné âge+nom si la condition de requête est où âge>18 et name="Ji" est un index inutilisé. <p></p>

On m'a demandé une fois lors d'une interview si cela signifiait que je pouvais utiliser un certain index. Je n'y ai pas prêté attention ou n'y ai pas répondu avec succès. Cette fois, j'ai fait une expérience moi-même. Veuillez consulter la fin de l'article. la conclusion.

7. La conversion de type forcée analysera la table entière

J'ai défini le champ mobile dans la table des employés comme type varchar et créé un index. Utilisez des nombres et des chaînes pour interroger.

---

Regardez les résultats : les deux types sont différents et seules les chaînes utilisent des index.

🎜Si le type de la valeur de la condition est incohérent avec celui défini dans le tableau, alors mysql forcera la conversion de type, mais le résultat ne sera pas indexé. Lors du développement de l'index, nous devons saisir la valeur de type correspondante en fonction du type. défini par nous-mêmes. 🎜🎜🎜

8. La distinction des données n'est pas élevée et les champs fréquemment mis à jour ne doivent pas être indexés. Les mises à jour des colonnes d'index modifieront l'arborescence B+ et les mises à jour fréquentes réduiront considérablement les performances de la base de données. 🎜🎜Semblable au sexe (uniquement masculin et féminin, ou inconnu), les données ne peuvent pas être filtrées efficacement. 🎜🎜Généralement, un index peut être créé lorsque la distinction est supérieure à 80 %. Pour la distinction, count(distinct(column name))/count(*) peut être utilisé🎜🎜

9. Pour créer un index, les colonnes ne doivent pas être nulles et vous pouvez obtenir des résultats inattendus🎜🎜C'est-à-dire que les champs indexés ne doivent pas être vides autant que possible. Il peut y avoir des problèmes inattendus, mais dans le travail réel, cela peut se produire. Il est peu probable qu'il ne soit pas vide, donc selon le réel, laissez les affaires s'occuper et essayez d'éviter cette situation. 🎜

10. Lorsqu'une connexion de table est requise, il est préférable de ne pas dépasser trois tables 🎜🎜La connexion de table est en fait une correspondance d'imbrication de boucles de plusieurs tables, ce qui affecte les performances. les types de données des champs qui doivent être joints doivent être cohérents pour améliorer l'efficacité des requêtes. Écrivons plus tard un article spécial sur le principe de la connexion des tables. 🎜

11. Essayez d'utiliser la limite lorsque vous le pouvez. 🎜🎜La fonction de limite n'est pas seulement la pagination, sa fonction essentielle est de limiter la sortie. 🎜🎜limit parcourt en fait les données de la requête une par une. Si vous n'avez besoin que d'une seule donnée et ajoutez la limite de limit 1, alors le pointeur d'index s'arrêtera après avoir trouvé les données qui remplissent les conditions, et ne continuera pas à juger à la baisse. Revenez directement. S'il n'y a pas de limite, le jugement se poursuivra. 🎜🎜Mais si vous recherchez et récupérez 5 éléments après 10 000 éléments limite 10 000,10005, vous devez être prudent. Il parcourra 10 000 éléments et récupérera 5 éléments, ce qui est très inefficace. Astuce : Si la clé primaire est séquentielle, vous pouvez obtenir des données paginées directement via la clé primaire. 🎜🎜12. Essayez de contrôler le nombre d'index à table unique dans 5🎜🎜La création/maintenance d'index coûte également de l'argent et prend de la place. Plus il y a d’index, mieux c’est. Les index doivent être utilisés de manière rationnelle. 🎜🎜13. Le nombre de champs dans un seul index combiné ne doit pas dépasser 5🎜🎜Plus il y a de champs, plus l'index sera grand et plus il occupera d'espace de stockage. 🎜🎜🎜Plus il y a d'index, mieux c'est, et les index n'ont pas besoin d'être conçus lorsque vous commencez à créer la table. Une optimisation prématurée ne sera pas un index efficace. Vous devez comprendre l'entreprise et faire des compromis statistiques en fonction de cela. SQL métier pertinent. Créez ensuite des index pertinents, afin que vous puissiez réfléchir plus attentivement et créer des index plus efficaces et efficients. 🎜🎜Ce qui précède sont les petits détails correspondant à l'optimisation de l'index, j'espère que cela pourra vous aider à écrire du swiss sql.🎜🎜🎜🎜Supplémentaire🎜🎜🎜🎜À propos de la question de savoir s'il faut indexer ou non🎜

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!