{{ it.name }}
{{ it.text }}
作者:胡呈清
爱可生 DBA 团队成员,擅长故障分析、性能优化,个人博客:https://www.jianshu.com/u/a95ec11f67a8,欢迎讨论。
select country.* from country join city on country.code=city.country_code \and population>20000000;+---------+----------+| code | name |+---------+----------+| 1 | china || 1 | china |+---------+----------+2 rows in set (0.00 sec)
select * from country where code in \(select country_code from city where population>20000000);+------+---------+| code | name |+------+---------+| 1 | china |+------+---------+1 row in set (0.00 sec)
select * from Countrywhere Country.code IN (select City.Country from City where City.Population > 7*1000*1000) and Country.continent='Europe'
第二种方法在物化表中查找数据时可以使用主键进行查找,因此叫做:Materialization-lookup。
select * from Country where Country.code IN (select City.Country \from City where City.Population > 7*1000*1000);+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+| 1 | PRIMARY | <subquery2> | ALL | NULL | NULL | NULL | NULL | 15 | || 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | world.City.Country | 1 | || 2 | MATERIALIZED | City | range | Population,Country | Population | 4 | NULL | 15 | Using index condition |+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
select * from Country where Country.code IN (select City.Country \from City where City.Population > 1*1000*1000) ;+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+| 1 | PRIMARY | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | || 1 | PRIMARY | <subquery2> | eq_ref | auto_key | auto_key | 3 | func | 1 | || 2 | MATERIALIZED | City | range | Population,Country | Population | 4 | NULL | 238 | Using index condition |+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
select dept_name from departments where dept_no in \(select min(dept_no) from dept_emp where emp_no<10020 group by dept_no);+----+-------------+-------------+-------+-----------------+-----------+---------+------+-----------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |+----+-------------+-------------+-------+-----------------+-----------+---------+------------------------------------------------------------------+| 1 | PRIMARY | departments | index | NULL | dept_name | 42 | 9 | Using where; Using index || 2 | SUBQUERY | dept_emp | range | PRIMARY,dept_no | PRIMARY | 4 | 21 | Using where; Using index; Using temporary; Using filesort |+----+-------------+-------------+-------+-----------------+-----------+---------+------+-----------------------------------------------------------+