Lorsque je codais aujourd'hui, j'ai rencontré un problème SQL :
(Recommandation du didacticiel vidéo MySQL associé : "Tutoriel MySQL ")
Pour faire correspondre l'ID de la table A à l'ID de la table B, et interroger tout le contenu de la table B :
Avant optimisation :
MySQL [xxuer]> SELECT -> COUNT(*) -> FROM -> t_cmdb_app_version -> WHERE -> id IN (SELECT -> pid -> FROM -> t_cmdb_app_relation UNION SELECT -> rp_id -> FROM -> t_cmdb_app_relation); +----------+ | COUNT(*) | +----------+ | 266 | +----------+ 1 row in set (0.21 sec)
Après optimisation :
MySQL [xxuer]> SELECT -> count(*) -> FROM -> t_cmdb_app_version a -> INNER JOIN -> (SELECT -> pid -> FROM -> t_cmdb_app_relation UNION SELECT -> rp_id -> FROM -> t_cmdb_app_relation) b ON a.id = b.pid; +----------+ | count(*) | +----------+ | 266 | +----------+ 1 row in set (0.00 sec)
Voir la comparaison des plans d'exécution :
MySQL [xxuer]> explain SELECT -> COUNT(*) -> FROM -> t_cmdb_app_version -> WHERE -> id IN (SELECT -> pid -> FROM -> t_cmdb_app_relation UNION SELECT -> rp_id -> FROM -> t_cmdb_app_relation); +----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | PRIMARY | t_cmdb_app_version | index | NULL | PRIMARY | 4 | NULL | 659 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL | 383 | Using where | | 3 | DEPENDENT UNION | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL | 383 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+ 4 rows in set (0.00 sec)
MySQL [xxuer]> explain SELECT -> count(*) -> FROM -> t_cmdb_app_version a -> INNER JOIN -> (SELECT -> pid -> FROM -> t_cmdb_app_relation UNION SELECT -> rp_id -> FROM -> t_cmdb_app_relation) b ON a.id = b.pid; +----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 766 | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | b.pid | 1 | Using where; Using index | | 2 | DERIVED | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL | 383 | NULL | | 3 | UNION | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL | 383 | NULL | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+ 5 rows in set (0.00 sec)
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!