Kết quả là, các bảng InnoDB phù hợp (và đôi khi vượt quá) thực hiện các bảng MyISAM. InnoDB bảng được hoàn toàn di động giữa các hành và kiến trúc khác nhau, và, vì bản chất giao dịch của họ, họ luôn ở trong một nhà nước thống nhất (MySQL làm cho họ thậm chí còn mạnh mẽ hơn bằng cách kiểm tra cho tham nhũng và sửa chữa chúng khi khởi động). | Chapter 4 Using Joins Subqueries and Views 91 1003 126 56 1005 34 48 1176 56 132 1175 132 56 1018 34 87 ------------- -------- -------- 29 rows in set sec PART I The EXISTS operator is most often used with correlated subqueries subqueries that use fields from the outer query in their clause s . Such a reference by a subquery to a field in its enclosing query is called an outer reference. When an outer reference appears within a subquery MySQL has to reevaluate the subquery once for every record generated by the outer query and therefore test the subquery as many times as there are records in the outer query s result set. Here s an example of a correlated subquery mysql SELECT FROM route AS r - WHERE IN - SELECT - FROM flight AS f flightdep AS fd - WHERE - AND - AND BETWEEN 00 00 AND 04 00 - RouteID - - From To Distance Duration Status - - 1133 - - 74 126 6336 470 1 - 1141 126 201 3913 320 1 - - - - - 2 rows in set sec In this case because the inner query contains a reference to a field in the outer query MySQL cannot run the inner query only once. Rather it has to run it over and over once for every record in the outer table substitute the value of the named field from that record in the subquery and then decide whether to include that outer record in the final result set on the basis of whether the corresponding subquery returns a result set. This is obviously expensive in terms of performance and so outer references should be avoided unless absolutely necessary. For situations where an outer reference is unavoidable the EXISTS operator comes in handy as a filter for the outer query s result set. Here s an example which prints those routes for which no flights exist mysql SELECT FROM route AS r - WHERE NOT EXISTS - SELECT 1 FROM flight AS f - WHERE 92 Part I Usage - RouteID From To Distance Duration Status 1167 92 56 777 70 0 1071 132 72 505 65 0 - 2 rows in set
