MySQL使用RAM中的索引;为什么磁盘运行？ [英] MySQL using indexes in RAM; why are the disks running?

问题描述

我有800M的索引，并告诉MySQL使用1500M的RAM。启动MySQL后，在Windows 7 x64上使用1000M。

我要执行此查询：

  SELECT oo。 FROM table o 
 LEFT JOIN表oo ON（oo.order = o.order AND oo.type =SHIPPED）
 WHERE o.type =ORDERED和oo.type IS NULL 
  

这会找到所有尚未发货的料品。执行计划告诉我：

照片http ：//i56.tinypic.com/23s6b11.png

我的索引是：

• type_order ：多个索引类型和订单


• order_type 以 order type



因此MySQL应使用索引 type_order ，然后使用 order_type 索引选出几个条目。我期望只有大约1000个非运输的项目，所以这个查询应该真的很快，但它不是。 。

我做错了什么？

解决方案

查询说 SELECT sometable。* ，因此对于1000个匹配的行，将有1000次从表中获取所有字段。是否 WHERE 部分索引完全加载到ram或不会只会帮助一些。仍然必须检索数据字段。奇怪的是，它们分散在磁盘上。所以，当然磁盘将做一千个小读取。

I have indices of 800M and told MySQL to use 1500M of RAM. After starting MySQL it uses 1000M on Windows 7 x64.

I want to execute this query:

SELECT oo.* FROM table o 
LEFT JOIN table oo ON (oo.order = o.order  AND oo.type="SHIPPED") 
WHERE o.type="ORDERED" and oo.type IS NULL

This finds all items not yet shipped . The execution plan tells me this:

photo http://i56.tinypic.com/23s6b11.png

My indices are:

• type_order: Multiple index with type and order
• order_type with order as first index value, followed by type

So MySQL should use the index type_order from RAM and then pick out the few entries with the order_type index. I'm expecting only about 1000 non shipped items, so this query should be really fast, but it isn't. Disks are going crazy....

What am I doing wrong?

解决方案

The query says SELECT sometable.*, so for 1000 matching rows, there will be 1000 fetches of all the fields from the table. Whether the WHERE part indexes are fully loaded into ram or not would only help some. The data fields still have to be retrieved. Odds are, they are scattered all over the disk. So, of course the disk(s) will be doing a thousand small reads.

这篇关于MySQL使用RAM中的索引;为什么磁盘运行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！