MySQL优化经验: 使用连接代替关联子查询
一、什么是关联子查询
看一个例子，以下是一个商品清单表，包含的列有商品编号、商品名称、商品类型和商品价格


如果我们想要选取出各商品种类中高于该商品种类的平均销售单价的商品，可以用如下代码实现：
SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM product AS p2
                      WHERE p1.product_type =p2.product_type
                      GROUP BY product_type);

该语句中WHERE字段中包含了一个子查询，子查询中使用到了主查询中的p1表，这种子查询被称为关联子查询。如果按照正常的先执行子查询，再执行主查询的方法执行显然无法识别p1表，因此关联子查询的执行顺序和正常SQL语句是不同的。
二、关联子查询语句的执行顺序
在《简单易懂教你学会SQL关联子查询》这篇博客中，博主详细阐述了关联子查询的执行顺序。在阅读本文前建议读者先理解关联子查询的执行顺序。

总结如下：

首先执行不带WHERE的主查询
根据主查询结果，依次将每项的product_type代入子查询，匹配product_type并求均价
将子查询结果再与主查询结合执行完整的SQL语句

可以看出，关联子查询是一类特殊的查询，其执行逻辑不同。

我们再审视一遍这种执行顺序会发现其中的问题。

由于主查询的p1表中有很多商品具有同样的product_type，而每一项都要去匹配子查询，找到该product_type的均价，因此，子查询做了很多次重复的计算，product_type和其均价应该只算一次即可，因此可以对此作出优化。
优化方案
综上，product_type和每个类型的均价应该只计算一次，可以使用JOIN的方式，将子查询作为一个子表在执行FROM的时候与原表做内连接，这样可以控制每个商品类型的均价只计算一次，代码如下：
SELECT p1.product_type, product_name, sale_price
  FROM product AS p1,
      (SELECT product_type, AVG(sale_price) AS avg_price
			    FROM product
				 GROUP BY product_type) AS p2
 WHERE p1.product_type = p2.product_type
   AND p1.sale_price > p2.avg_price;

数据来源:datawhale SQL组队学习

   
我们都知道，用exists代替in可以提高sql语句的执行效率，例如如下两个例子：

检索部门所在地为 NEW YORK’的员工信息。
使用IN
 select * from scott.emp
 where deptno in (
  select deptno from scott.dept where loc='NEW YORK'）;
使用 exists
 select * from scott.emp
 where  exists (
  select 1 from scott.dept where scott.dept.deptno=scott.emp.deptno and  loc='NEW YORK'）;
注意，这里出现了一个特殊用法select 1 ？

比如说，使用select 1 from table的结果是临时得到1列（列的值为1），其行数为表的记录数（行数），如果配合exists 语句则可以快速查询结果是否存在，而结果的具体数据不涉及到。

就像我上述提供的例子，它只查询验证dept表的字段deptno和emp的字段deptno是否有相等的情况，并且loc=‘NEW YORK’，而不需要知道dept表和emp表哪些记录存在那样的情况，也不需要知道相等情况下其他字段的值。在应用中，效率比select *  快。

扩展：    select 1 from table;与select anycol(目的表集合中的任意一行） from table;与select * from table 从作用上来说是没有差别的，都是查看是否有记录，一般是作条件查询用的。select 1 from 中的1是一常量（可以为任意数值），查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

条件中有or

如果条件中有or，其中一个字段是索引字段，另一个是非索引字段，此时索引不会被使用，全表扫描。


如果条件中有or，左右是两个不同的独立索引字段，此时两个索引字段都会用。



但其实完全可以用union来代替：

     select * from xd_student where id = 2  
     union   
     select * from xd_student where room = 401   


如果条件中有or，用的都是同一个索引字段，索引有用。



但其实完全可以用in来代替or：

     select * from xd_student where  id  in (2,4);

使用like
如果以%开头，索引不能用；以%结束，索引能用但是不用。


列类型是字符串，没有使用引号
只有加链引号才会用索引。


索引字段表达式


【补充】

查看索引的使用情况
show status like ‘Handler_read%’;

handler_read_key:这个值越大说明使用索引查询到的次数越多
handler_read_rnd_next:这个值越高，说明查询低效

我想创建一个JDBC PreparedStatement,如：
SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC
哪一号？是文字和第二？是一个参数.我可以使用CHAR(63)代替’？’但我认为额外的函数调用会减慢SQL执行速度.有没有办法逃脱第一次？
编辑：
下面的代码测试dkatzel的断言是什么？字符串中的字符不被视为标记：
public class Test {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE TABLE Links(URL VARCHAR(255) PRIMARY KEY,pageId BIGINT)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar?baz',1)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar/baz',1)");
stmt.close();
PreparedStatement ps = conn
.prepareStatement("SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ":" + rs.getInt(2));
}
rs.close();
ps.close();
conn.close();
}
}
输出：
http://foo.bar/baz:0
http://foo.bar?baz:15
似乎dkatzel是正确的.我搜索了JDBC Spec并且找不到任何提及的那个？如果参数标记在引号内,则会被忽略,但我发现的少数PreparedStatement解析器实现(MySql,c-JDBC,H2)似乎都将单引号中的文本排除在考虑范围内作为参数标记.