关于sql子查询优化的一个问题

超级呆子 发布于 2013/02/24 21:23
阅读 336
收藏 1

来自《mysql必知必会》里面的例子


表结构如下:Create Table: CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers
` (`cust_id`)


Create Table: CREATE TABLE `orderitems` (
  `order_num` int(11) NOT NULL,
  `order_item` int(11) NOT NULL,
  `prod_id` char(10) NOT NULL,
  `quantity` int(11) NOT NULL,
  `item_price` decimal(8,2) NOT NULL,
  PRIMARY KEY  (`order_num`,`order_item`),
  KEY `fk_orderitems_products` (`prod_id`),
  CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders
` (`order_num`),
  CONSTRAINT `fk_orderitems_products` FOREIGN KEY (`prod_id`) REFERENCES `produc
ts` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

两个sql语句如下:

explain select * from orders force index(primary) where order_num in( select order_num from orderitems where prod_id='TNT2' );


explain select * from orders force index(primary) where order_num in( 20005,20007 );

第二个sql只是把 20005,20007给硬编码了,explain的结果却是range,但是第一个却是全表扫描,即使强制指定索引也不行,这是为什么呢?



加载中
0
jsyang888
jsyang888

如果prod_id=‘TNT2’记录的order_num占整个orders表30%左右,索引反而快不了。

不知道是否是这关系


0
Rewrite
Rewrite
select * from orders force index(primary) exists ( select order_num from orderitems where prod_id='TNT2' and orders.order_num = orderitems.order_num); 子查询优化会被自动优化成这样的,他就会扫描整张表了。 可以试试 select orders.* from orders force index(primary) inner join orderitems using(order_num) where prod_id='TNT2' 关于此类子查询优化 最好对比内连接,存在,子查询三者,方法论证请使用基准测试选出最好的结果,因为个别id的快速不是整体数据的快速
0
jsyang888
jsyang888

引用来自“Rewrite”的答案

select * from orders force index(primary) exists ( select order_num from orderitems where prod_id='TNT2' and orders.order_num = orderitems.order_num); 子查询优化会被自动优化成这样的,他就会扫描整张表了。 可以试试 select orders.* from orders force index(primary) inner join orderitems using(order_num) where prod_id='TNT2' 关于此类子查询优化 最好对比内连接,存在,子查询三者,方法论证请使用基准测试选出最好的结果,因为个别id的快速不是整体数据的快速

1)恩,能inner join尽量先inner join,再干话,是很多sql 优化良方

2) 数据库设计者(不管是oracle 或 mysql),当index 无法变快时都会让index 失效(反正要扫描整表或表的大部分,index 没有意义,还让index工作干嘛)

返回顶部
顶部