哪个查询更好更高效 - mysql [英] which query is better and efficient - mysql

问题描述

我遇到了以不同的方式编写查询，如下所示
Type-I

I came across writing the query in differnt ways like shown below Type-I

SELECT JS.JobseekerID
         , JS.FirstName
         , JS.LastName
         , JS.Currency
         , JS.AccountRegDate
         , JS.LastUpdated
         , JS.NoticePeriod
         , JS.Availability
         , C.CountryName
         , S.SalaryAmount
         , DD.DisciplineName
         , DT.DegreeLevel 
    FROM Jobseekers JS 
INNER 
   JOIN Countries C 
      ON JS.CountryID = C.CountryID 
INNER 
   JOIN SalaryBracket S 
      ON JS.MinSalaryID = S.SalaryID 
INNER 
  JOIN DegreeDisciplines DD 
     ON JS.DegreeDisciplineID = DD.DisciplineID 
INNER 
  JOIN DegreeType DT 
     ON JS.DegreeTypeID = DT.DegreeTypeID 
WHERE
  JS.ShowCV = 'Yes'

Type-II

SELECT JS.JobseekerID
         , JS.FirstName
         , JS.LastName
         , JS.Currency
         , JS.AccountRegDate
         , JS.LastUpdated
         , JS.NoticePeriod
         , JS.Availability
         , C.CountryName
         , S.SalaryAmount
         , DD.DisciplineName
         , DT.DegreeLevel 
    FROM Jobseekers JS, Countries C, SalaryBracket S, DegreeDisciplines DD
         , DegreeType DT
    WHERE
           JS.CountryID = C.CountryID 
           AND JS.MinSalaryID = S.SalaryID 
           AND JS.DegreeDisciplineID = DD.DisciplineID 
           AND JS.DegreeTypeID = DT.DegreeTypeID 
           AND  JS.ShowCV = 'Yes'

我正在使用Mysql数据库

I am using Mysql database

这两个都很好，但我想知道

Both works really well, But I am wondering

1. 这是最好的做法， li>
   
2. 性能明智，更好的一个？（将数据库说成百万条记录）


3. $ b
4. 是否有任何工具可以检查哪个更好的查询？

提前感谢

推荐答案

1-这是一个没有脑子，使用类型I

1- It's a no brainer, use the Type I

2-类型II连接也称为隐式连接，而类型I称为显式连接。使用现代DBMS，正常查询不会有任何性能问题。但是我想用一些大的复杂的多连接查询，DBMS可能有隐式连接的问题。使用显式连接只能改善您的解释计划，所以结果更快！

2- The type II join are also called 'implicit join', whereas the type I are called 'explicit join'. With modern DBMS, you will not have any performance problem with normal query. But I think with some big complex multi join query, the DBMS could have issue with the implicit join. Using explicit join only could improve your explain plan, so faster result !

3-因此性能可能是一个问题，但最重要的是，可读性改善了进一步维护。显式连接解释什么是你想要在什么字段上加入，而隐式连接不显示如果你做连接或过滤器。 Where子句是用于过滤器，而不是用于连接！

3- So performance could be an issue, but most important maybe, the readability is improve for further maintenance. Explicit join explain exactly what you want to join on what field, whereas implicit join doesn't show if you make a join or a filter. The Where clause is for filter, not for join !

显式连接的一大亮点：外连接真的很讨厌使用隐式连接。

And a big big point for explicit join : outer join are really annoying with implicit join. It is so hard to read when you want multiple join with outer join that explicit join are THE solution.

4-执行计划是你需要的（查看文档）

4- Execution plan are what you need (See the doc)

某些重复项：

显式vs隐式SQL连接

SQL join：where clause vs. on clause

INNER JOIN ON VS WHERE子句

这篇关于哪个查询更好更高效 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！