php - 求这个sql怎么优化下

Question

sql语句是：
explain SELECT bp.amount / (select count(*) from yd_order_product where order = bp.order and product = bp.product) as amount,s.costprice as price,s.store,b.type from yd_batch_product as bp left join yd_order_product as op on op.order = bp.order and op.product = bp.product left join yd_batch as b on bp.batch = b.id left join yd_order as o on bp.order = o .id left join yd_stock as s on bp.product = s.id where o.deleted = '0' and s.forbidden = '0' and b.createdDate between '2015-10-13 00:00:00' and '2017-04-30 23:59:59' and s.store in (1,2,3,4,5,6) and s.chainID = 1

请问下这个第一行的type值为什么会是ALL

Answer 1

@TroyLiu, votre suggestion est que trop d'index sont créés et que l'idée d'établir un index combiné commande/produit/lot/montant semble erronée.

L'idée principale de l'optimisation SQL est que la table Big Data peut filtrer les données en fonction des conditions de l'instruction Where existante. Dans cet exemple :
la ​​table yd_batch_product doit être la plus grande, mais dans l'instruction Where, il y en a. aucune condition de filtrage, ce qui entraîne une mauvaise efficacité d’exécution.

La raison pour laquelle "Utilisation temporaire ; Utilisation du tri de fichiers" apparaît est que les champs du groupe par ne sont pas complètement dans la plage de champs de la première table yd_batch_product.

Suggestions :
1. yd_batch/yd_order/yd_stock utilise LEFT JOIN, mais il existe des conditions de filtre pour ces champs de table dans l'instruction Where, donc c'est équivalent à INNER JOIN, donc la méthode de connexion peut être modifiée en INNER JOIN, donc mysql Lorsque vous jugez le plan d'exécution, vous pouvez non seulement interroger d'abord la table yd_batch_product.
2. Créez un index sur le champ createDate de la table yd_batch pour voir s'il y a un changement dans la première table du pilote dans le plan d'exécution. Si la condition de createDate est très filtrante, la première table du plan d'exécution doit être yd_batch. .
3. La table yd_stock, comment combiner store et chainID pour obtenir un filtrage fort, peut créer un index combiné et déterminer si le plan d'exécution a changé de la même manière qu'à l'étape précédente.

Bien sûr, la situation la plus idéale est de partir de l'activité et des données, de connaître les conditions sur la table yd_batch_product qui peuvent filtrer une grande quantité de données, puis de créer un index basé sur cette condition. Par exemple, le champ createDate de la table yd_batch possède également des champs redondants similaires sur la table yd_batch_product, et ce champ est utilisé pour créer un index.

---

Suggestions après la mise à jour de la question :
1. TYPE=ALL indique qu'une analyse complète de la table est utilisée. Bien que createDate ait déjà créé un index, l'évaluation de la base de données estime que l'utilisation d'une analyse complète de la table est moins chère et est généralement considérée comme normale (la plage). de createDate Plus grand, comprenant plus d'un an de données, plus de 10 000 éléments au total). Si vous souhaitez utiliser un index, vous pouvez réduire la plage en quelques jours et voir s'il y a des changements dans le plan d'exécution. Bien entendu, vous pouvez également utiliser des invites pour forcer l’utilisation d’index, mais l’efficacité n’est pas nécessairement élevée.
2. La table yd_order_product est interrogée deux fois, en particulier la sous-requête select dans le plan d'exécution, qui doit être évitée autant que possible.

Answer 2

Tout d'abord, la quantité de données dans la table yd_batch_product est très importante et l'index n'est pas créé correctement, ce qui fait que la requête entière n'utilise pas l'index.
On peut le voir à partir de la colonne clé correspondante du tableau bp est NULL.

Référence d'optimisation :

1. Créez un index conjoint sur la colonne commande/produit/lot/montant pour la table yd_batch_product. Faites attention à la commande de l'index conjoint (le montant est également ajouté à l'index afin d'utiliser l'index de couverture)

.

2. S'il n'y a pas d'autres requêtes pour yd_batch_product, envisagez de supprimer tous les index à colonne unique de la table.

3. La table yd_stock est indexée conjointement sur la colonne interdit/store/chainID (notez l'ordre d'indexation).

La table

4. yd_order crée un index de colonne unique sur la colonne supprimée.

5. La table yd_batch crée un index de colonne unique sur la colonne createDate.

Sur la base de l'analyse actuelle, l'optimisation ci-dessus peut être effectuée.
Comme il n'y a pas de fiche technique pour les tests, veuillez me corriger s'il y a des erreurs.