qu'est-ce que l'indice MySQL

Dans MySQL, l'indice fait référence à un « indice d'optimisation de requête », qui incitera l'optimiseur à générer un plan d'exécution pour l'optimisation d'une certaine manière, rendant l'instruction SQL de l'utilisateur plus flexible. L'indice peut être basé sur la séquence de connexion et la méthode de ; la table, les chemins d'accès, le parallélisme et d'autres règles ont un effet sur les instructions DML (Data Manipulation Language).

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Nous pouvons ajouter un commentaire lors de l'exploitation de tables, de champs ou d'index pour améliorer la lisibilité du code afin que les autres puissent rapidement comprendre le code. Il s'agit d'une astuce pour les personnes qui utilisent la base de données. De la même manière, il existe une astuce appelée indice. une invite à la base de données.

Qu'est-ce qu'un indice

un indice fait référence à un "indice d'optimisation de requête", qui incitera l'optimiseur à optimiser d'une certaine manière, rendant votre instruction SQL plus flexible, ce qui rendra votre requête plus rapide, bien sûr, cela peut être plus lent, tout dépend de votre compréhension de l'optimiseur et de votre compréhension du scénario.

Nous savons que lors de l'exécution d'une instruction SQL, MySQL générera un plan d'exécution, et un indice indique à l'optimiseur de requêtes de générer le plan d'exécution de la manière que nous lui disons.

Hint peut agir sur les instructions DML (Data Manipulation Language) en fonction de l'ordre de connexion de la table, de la méthode, du chemin d'accès, du parallélisme et d'autres règles.

Force index FORCE INDEX

SELECT * FROM tbl FORCE INDEX (FIELD1) …Ignorer l'index IGNORE INDEXSELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …

Fermez le tampon de requête SQL_NO_CACHE
• SELECT SQL_NO_CACHE field1, field2 FROM tbl;

  Lorsque vous devez interroger des données en temps réel et que la fréquence n'est pas élevée, vous Vous pouvez envisager de désactiver le tampon, c'est-à-dire que peu importe si ce SQL a déjà été exécuté, MySQL ne regardera pas dans le tampon.
  

SELECT * FROM tbl FORCE INDEX (FIELD1) …

• 忽略索引 IGNORE INDEX
  SELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …

• 关闭查询缓冲 SQL_NO_CACHE
  SELECT SQL_NO_CACHE field1, field2 FROM tbl;
  需要查询实时数据且频率不高时，可以考虑把缓冲关闭，即不论此SQL是否曾被执行，MySQL都不会在缓冲区中查找。

• 强制查询缓冲 SQL_CACHE
  SELECT SQL_CACHE * FROM tbl;
  功能同上一条相反，但仅在my.ini中的query_cache_type设为2时起作用。

• 优先操作 HIGH_PRIORITY
  HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
  SELECT HIGH_PRIORITY * FROM tbl;

• 滞后操作 LOW_PRIORITY
  LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
  update LOW_PRIORITY tbl set field1= where field1= …

• 延时插入 INSERT DELAYED
  INSERT DELAYED INTO tbl set field1= …
  指客户端提交插入数据申请，MySQL返回OK状态却并未实际执行，而是存储在内存中排队，当mysql有空余时再插入。
  一个重要的好处是，来自多个客户端的插入请求被集中在一起，编写入一个块，比独立执行许多插入要快很多。
  坏处是，不能返回自增ID，以及系统崩溃时，MySQL还未来得及被插入的数据将会丢失。

• 强制连接顺序 STRAIGHT_JOIN
  SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE …
  由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按tbl、tbl2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

不常用

• 强制使用临时表 SQL_BUFFER_RESULT
  SELECT SQL_BUFFER_RESULT * FROM tbl WHERE …
  当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

• 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
  SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1;
  Forcer la mise en mémoire tampon des requêtes SQL_CACHE

  SELECT SQL_CACHE * FROM tbl;🎜La fonction est à l'opposé de la précédente, mais elle ne fonctionne que lorsque query_cache_type dans my.ini est défini sur 2 . 🎜
🎜🎜Opération prioritaire HIGH_PRIORITY🎜HIGH_PRIORITY peut être utilisée dans les opérations de sélection et d'insertion pour faire savoir à MYSQL que cette opération est prioritaire. 🎜SELECT HIGH_PRIORITY * FROM tbl;🎜🎜🎜Opération en retard LOW_PRIORITY🎜LOW_PRIORITY peut être utilisée dans les opérations d'insertion et de mise à jour pour informer MySQL que cette opération est en retard. 🎜update LOW_PRIORITY tbl set field1=where field1= …🎜🎜🎜Insertion retardée INSERT DELAYED🎜INSERT DELAYED INTO tbl set field1= …🎜fait référence au client Soumettez l'application pour insérer des données. MySQL renvoie le statut OK mais n'est pas réellement exécuté. Au lieu de cela, elle est stockée dans la mémoire et mise en file d'attente, puis insérée lorsque MySQL est libre. 🎜Un avantage important est que les demandes d'insertion de plusieurs clients sont regroupées et écrites dans un bloc, ce qui est beaucoup plus rapide que d'effectuer plusieurs insertions indépendamment. 🎜L'inconvénient est que l'ID auto-incrémenté ne peut pas être renvoyé, et lorsque le système plante, les données que MySQL n'a pas encore eu le temps d'insérer seront perdues. 🎜🎜🎜Forcer la séquence de connexion STRAIGHT_JOIN🎜SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE…🎜Il ressort de l'instruction SQL ci-dessus que STRAIGHT_JOIN est utilisé pour forcer MySQL doit appuyer sur tbl, tbl2 Joindre les tables de manière séquentielle. Si vous pensez qu'il est plus efficace de rejoindre votre propre ordre que l'ordre recommandé par MySQL, vous pouvez utiliser STRAIGHT_JOIN pour déterminer l'ordre de connexion. 🎜

Pas couramment utilisé

🎜🎜🎜Forcer l'utilisation de la table temporaire SQL_BUFFER_RESULT🎜SELECT SQL_BUFFER_RESULT * FROM tbl WHERE…🎜Lorsque nous interrogeons les données dans le jeu de résultats Dans de nombreux cas, vous pouvez forcer le jeu de résultats dans une table temporaire via l'option SQL_BUFFER_RESULT, afin que le verrou de la table MySQL puisse être libéré rapidement (afin que d'autres instructions SQL puissent interroger ces enregistrements), et cela peut être. utilisé depuis longtemps. Fournir aux clients de grands ensembles d’enregistrements. 🎜🎜🎜Groupe utilisant des tables temporaires SQL_BIG_RESULT et SQL_SMALL_RESULT🎜SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1 ;🎜Effectif pour les instructions SELECT, indique à MySQL d'optimiser GROUP BY et How utiliser le tri par table temporaire pour les requêtes DISTINCT signifie que le jeu de résultats est très petit et peut être trié directement sur la table temporaire en mémoire ; sinon, s'il est très volumineux, il doit être trié à l'aide d'une table temporaire sur disque. 🎜

SQL_CALC_FOUND_ROWS
Ce n'est pas réellement une invite de l'optimiseur, et cela n'affecte pas le plan d'exécution de l'optimiseur, mais cela fera que l'ensemble de résultats renvoyé par mysql inclura le nombre total de lignes affectées par cette opération, qui doit être combinée avec FOUND_ROWS() Utilisation combinée. FOUND_ROWS() 联用。
SQL_CALC_FOUND_ROWS 通知MySQL将本次处理的行数记录下来； FOUND_ROWS() 用于取出被记录的行数，可以应用到分页场景。
一般的分页写法为：先查总数，计算页数，再查询某一页的详情。
SELECT COUNT(*) from tbl WHERE …
SELECT * FROM tbl WHERE … limit m,n
但借助SQL_CALC_FOUND_ROWS，可以简化成如下写法：
SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;
SELECT FOUND_ROWS();
第二条SELECT将返回第一条SELECT不带limit时的总行数，如此只需执行一次较耗时的复杂查询就可同时得到总行数。

LOCK IN SHARE MODE、 FOR UPDATE
同样的，这俩也不是优化提示，是控制SELECT语句的锁机制，只对行级锁有效，即InnoDB支持。

扩展知识：

概念和区别

SELECT ... LOCK IN SHARE MODE添加的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，其他session可读取记录，亦可继续添加IS锁，但无法修改，直到这个加锁的session done(否则直接锁等待超时)。

SELECT ... FOR UPDATE 添加的是IX锁(意向排它锁)，即符合条件的rows上都加了排它，其他session无法给这些记录添加任何S锁或X锁。如果不存在一致性非锁定读的话，则其他session是无法读取和修改这些记录的，但innodb有非锁定读(快照读不需要加锁)。
因此，for update的加锁方式只是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

应用场景

LOCK IN SHARE MODE的适用于两张存在关系的表的写场景，以mysql官方例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，从业务角度讲，此时直接insert一条child_id=100记录到child表是存在风险的，因为insert的同时可能存在parent表执行了删除c_child_id=100的记录，业务数据有不一致的风险。正确方法是先执行select * from parent where c_child_id=100 lock in share mode，锁定parent表的这条记录，然后执行insert into child(child_id) values (100)SQL_CALC_FOUND_ROWS demande à MySQL d'enregistrer le nombre de lignes traitées cette fois ; FOUND_ROWS() est utilisé pour récupérer le nombre de lignes enregistrées et peut être appliqué aux scénarios de pagination.

La manière générale d'écrire une pagination est la suivante : vérifiez d'abord le nombre total, calculez le nombre de pages, puis vérifiez les détails d'une certaine page.

SELECT COUNT(*) from tbl WHERE …SELECT * FROM tbl WHERE … limit m,nMais avec l'aide de SQL_CALC_FOUND_ROWS, cela peut être simplifié en Il s'écrit comme suit :

SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;🎜SELECT FOUND_ROWS();🎜Le deuxième SELECT renverra Le premier SELECT est le nombre total de lignes sans limite. De cette façon, il vous suffit d'exécuter une requête complexe et longue pour obtenir le nombre total de lignes. en même temps. 🎜🎜🎜🎜VERROUILLAGE EN MODE PARTAGE, POUR MISE À JOUR🎜De même, ces deux-là ne sont pas des conseils d'optimisation. Il s'agit du mécanisme de verrouillage qui contrôle l'instruction SELECT. Ils ne sont efficaces que pour les verrous au niveau des lignes, qui sont pris en charge par InnoDB. 🎜🎜🎜Développez vos connaissances :🎜🎜Concepts et différences🎜🎜SELECT ... LOCK IN SHARE MODE ajoute le verrou IS (verrouillage partagé intentionnel), c'est-à-dire que les verrous partagés sont ajoutés aux lignes qualifiées. Les autres sessions peuvent lire les enregistrements et continuer à ajouter des verrous IS, mais ne peuvent pas être modifiées. jusqu'à ce que la session verrouillée soit terminée (sinon, l'attente du verrouillage direct expire). 🎜🎜SELECT ... FOR UPDATE Ce qui est ajouté est le verrouillage IX (verrouillage exclusif d'intention), c'est-à-dire que l'exclusivité est ajoutée aux lignes qui remplissent les conditions. Les autres sessions ne peuvent pas ajouter de verrou S ou S. verrouiller sur ces enregistrements. S'il n'y a pas de lecture non verrouillable cohérente, les autres sessions ne peuvent pas lire et modifier ces enregistrements, mais innodb a une lecture non verrouillable (la lecture d'instantané ne nécessite pas de verrouillage). 🎜Par conséquent, la méthode de verrouillage for update bloque uniquement select...lock in share mode plus que la méthode lock in share mode Query mode. ne bloque pas la lecture des instantanés. 🎜🎜Scénarios d'application🎜🎜LOCK IN SHARE MODE convient à l'écriture de scénarios dans lesquels deux tables ont une relation. En prenant l'exemple officiel de MySQL, une table est une table enfant. L'une est la table parent. Supposons qu'une certaine colonne child_id de la table enfant soit mappée à la colonne c_child_id de la table parent. D'un point de vue commercial, il est risqué d'insérer directement un enregistrement child_id=100 dans la table enfant à ce stade. , car la table parent peut exister en même temps que l'insertion. Après la suppression de l'enregistrement avec c_child_id=100, il existe un risque d'incohérence dans les données métier. La méthode correcte consiste à exécuter d'abord select * from parent which c_child_id=100 lock in share mode, à verrouiller cet enregistrement dans la table parent, puis à exécuter insert into child(child_id) values ​​​​​(100) code>. 🎜🎜【Recommandation associée : 🎜tutoriel vidéo mysql🎜】🎜

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!