向ACMAIN_CHM提问

Question

-- Table "cytobandideo" DDLCREATE TABLE `cytobandideo` (
  `chrom` varchar(255) NOT NULL DEFAULT '',
  `chromStart` int(10) unsigned NOT NULL DEFAULT '0',
  `chromEnd` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) CHARACTER SET gbk NOT NULL DEFAULT '',
  `gieStain` varchar(255) CHARACTER SET gbk NOT NULL DEFAULT '',
  PRIMARY KEY (`chrom`(12),`chromStart`),
  UNIQUE KEY `chrom` (`chrom`(12),`chromEnd`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;-- Table "temp_3_new_0928" DDLCREATE TABLE `temp_3_new_0928` (
  `chrom` varchar(255) NOT NULL DEFAULT '',
  `start` varchar(255) DEFAULT NULL,
  `stop` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `name2` varchar(255) NOT NULL DEFAULT '',
  `tss` int(11) unsigned NOT NULL DEFAULT '0',
  `strand` char(1) NOT NULL DEFAULT '',
  `seq_id` varchar(255) DEFAULT NULL,
  `probeids` varchar(255) DEFAULT NULL,
  `txstart` int(11) unsigned NOT NULL DEFAULT '0',
  `txend` int(11) unsigned NOT NULL DEFAULT '0',
  `cdsstart` int(11) unsigned NOT NULL DEFAULT '0',
  `cdsend` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_all` (`chrom`,`start`,`stop`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;我的sql语句为：
 select cytobandideo.chrom,start,stop,temp_3_new_0928.name,name2,txstart as tss,strand,seq_id,probeids,txstart,txend,cdsstart,cdsend,cytobandideo.name as cytoband_name,cytobandideo.giestain from temp_3_new_0928,cytobandideo
  where cytobandideo.chrom=temp_3_new_0928.chrom and start<=50 and stop>=20我疑惑的是：为什么它要全表扫描我的大表temp_3_new_0928（28w条数据），而不是全表扫描我的小表cytobandideo呢？？？
语句分析如下：root@localhost : tss Tue Sep 28 10:37:24 2010>explain  select temp_3_new_0928.chrom,start,stop,temp_3_new_0928.name,name2,txstart as tss,strand,seq_id,probeids,txstart,txend,cdsstart,cdsend,cytobandideo.name as cytoband_name,cytobandideo.giestain from temp_3_new_0928,cytobandideo
    ->   where cytobandideo.chrom=temp_3_new_0928.chrom and start<=50 and stop>=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: temp_3_new_0928
         type: ALL
possible_keys: ix_all
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 289901
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: cytobandideo
         type: ref
possible_keys: PRIMARY,chrom
          key: PRIMARY
      key_len: 14
          ref: tss.temp_3_new_0928.chrom
         rows: 8
        Extra: Using where
2 rows in set (0.00 sec)索引：root@localhost : tss Tue Sep 28 10:49:21 2010>show index from cytobandideo;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cytobandideo |          0 | PRIMARY  |            1 | chrom       | A         |        NULL |       12 | NULL   |      | BTREE      |         | 
| cytobandideo |          0 | PRIMARY  |            2 | chromStart  | A         |         862 |     NULL | NULL   |      | BTREE      |         | 
| cytobandideo |          0 | chrom    |            1 | chrom       | A         |        NULL |       12 | NULL   |      | BTREE      |         | 
| cytobandideo |          0 | chrom    |            2 | chromEnd    | A         |         862 |     NULL | NULL   |      | BTREE      |         | 
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)root@localhost : tss Tue Sep 28 10:49:29 2010>show index from temp_3_new_0928;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| temp_3_new_0928 |          1 | ix_all   |            1 | chrom       | A         |          39 |     NULL | NULL   |      | BTREE      |         | 
| temp_3_new_0928 |          1 | ix_all   |            2 | start       | A         |      144950 |     NULL | NULL   | YES  | BTREE      |         | 
| temp_3_new_0928 |          1 | ix_all   |            3 | stop        | A         |      144950 |     NULL | NULL   | YES  | BTREE      |         | 
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)请指教我的本意是让查询分析器来全表扫描我的小表，然后再映射到我的大表时是通过索引的。。（也就是外层循环是小数据集，内层循环用索引），可是结果不符合。
你的理解如何？

Answer 1

在连接查询的时候，注意一下表的顺序。小表放前面，大表放后面。
A INNER JOIN B
查询的时候从A里取出一条记录，然后根据条件去B理查，得到结果后放入一个临时记录表里，然后再去A的第二条，直到扫描完A的所有记录。所以A会全表扫描，如果B的字段上有索引的话，B是不会全表扫描的。

Answer 2

zuoxingyu 大侠 和 ACMAIN_CHM大侠，帮我看下这个帖子
http://topic.csdn.net/u/20100928/17/7fd44f5a-c7ef-43d2-8807-58542bd690cf.html

Answer 3

你的两个表，优化顺序上应该先用temp_3_new_0928然后再去查cytobandideo这样才是最优化的，MYSQL的执行计划是正确的。只是你的索引并不理想。
应该是创建两个索引。create index xxx1 on temp_3_new_0928(start);
create index xxx2 on temp_3_new_0928(stop);

Answer 4

能不能告诉我为什么先用temp_3_new_0928然后再去查cytobandideo这样才是最优化的？？？？
外层表要全表扫描20多万次呀？？？如果是先查cytobandideo（小表），然后再查temp_3_new_0928（大表），那么只需要全表扫描几千次啊。
不知道我能否这样理解a ?

Answer 5

temp_3_new_0928(start,stop) 在这种条件中 start<=50 and stop>=20 是无法使用的，根据左匹配原则，temp_3_new_0928(start,stop)索引中只是start部分会被利用到。如果想继续了解为什么，建议你手工做个索引表，贴出来就明白了。

Answer 6

不能利用索引是因为<=,>=的缘故么？
我是真的不明白，我现在的索引不是左匹配么？
where条件为：chrom=chrom，start<常数，stop>常数啊，这不是左匹配么？（chrom，start，stop）
我要怎么才能让执行计划选择我的小表来做全表扫描呢？
你刚才说“优化顺序上应该先用temp_3_new_0928然后再去查cytobandideo这样才是最优化的，MYSQL的执行计划是正确的。
”，能不能告诉我为什么这样才是正确的呢？
谢谢

Answer 7

你可以用昨天 的 STRAIGHT_JOIN 选项以告诉MYSQL。这个建议在你举完楼上要求的这个例子后再讨论。

Answer 8

temp_3_new_0928表的记录     索引为（chrom,start,stop）
前十条记录：
chrom     start             stop
chr10 24022447 24022496
chr10 24022557 24022606
chr10 24022662 24022711
chr10 24022762 24022811
chr10 24022842 24022891
chr10 24023392 24023441
chr10 24023472 24023521
chr10 24023572 24023621
chr10 24023677 24023726
chr10 24023797 24023846
cytobandideo表
前十条记录：
chrom   chromstart       chromend
chr1 0         2300000
chr1 2300000         5300000
chr1 5300000         7100000
chr1 7100000         9200000
chr1 9200000         12600000
chr1 12600000 16100000
chr1 16100000 20300000
chr1 20300000 23800000
chr1 23800000 27800000
chr1 27800000 30000000 “把产生的索引文件的顺序写一下”，这个我还真不知道怎么写？
你可以给我举个例子嘛？谢谢。。
或者能告诉我一些资料，我自己去理解也行。  谢谢了其实我很想弄明白其中的索引是怎么搞的，连接原理，还有就是mysql怎么去选择执行计划？也看了一些有关优化的书籍，不过都是浅尝辄止的，
还有就是自己水平有限，请指教，谢谢

Answer 9

假设你的表中数据如下。_rowid 为记录的内部物理地址。并不显示。_rowid col1 start stop
1 A1 53 98
2 A2 9 18
3 A3 38 96
4 A4 72 55
5 A5 41 87
6 A6 35 32
7 A7 19 91
8 A8 26 64
9 A9 43 85
10 A10 38 6
11 A11 76 5
12 A12 6 78
13 A13 10 68
14 A14 84 62
15 A15 25 57
16 A16 29 54
17 A17 30 60
18 A18 34 32
create index xxx on table1(start,stop) 那么这个索引文件中的内容是什么呢？ 如下。
start stop _rowid
6 78 12
9 18 2
10 68 13
19 91 7
25 57 15
26 64 8
29 54 16
30 60 17
34 32 18
35 32 6
38 6 10
38 96 3
41 87 5
43 85 9
53 98 1
72 55 4
76 5 11
84 62 14
根据这个索引文件，类似于查字典。如果让你查 start> 40 你会怎么查？ 然后如果查  start> 40 and stop<10 的你又怎么查？

Answer 10

A INNER JOIN B
查询的时候从A里取出一条记录，然后根据条件去B理查，得到结果后放入一个临时记录表里，然后再去A的第二条，直到扫描完A的所有记录。狼头哥，2表连接查询的时候，用的是内循环方式，第一张表应该是全表扫描的，不管有没有索引，内循环的表如果有索引，直接根据索引找到记录，如果没有索引，全表扫描。我的理解更换了表顺序后，应该全表扫描的是小表了才对。看了你们上面的讨论，这个还没搞清楚。

Answer 11

您的检索条件是
cytobandideo.chrom=temp_3_new_0928.chrom and start<=50 and stop>=20而实际上start stop都是字符型,您使用的是数字来检索,这就需要转换类型,而如果要转换类型的话,在start stop上的索引就无法使用,所以 temp_3_new_0928 就要全表扫描, 筛选出符合条件的记录而筛选出的记录是没有索引的,刚好
cytobandideo的PRIMARY KEY (`chrom`(12),`chromStart`)中前12个字符的索引还是可以利用的(虽然仍然要过滤记录,但比什么索引都没有要好),所以cytobandideo排后边

Answer 12

貌似这种索引是：先按照start 升序排列，如果排列相同再按stop升序排列，对么？
如果是这样start> 40 and stop<10 ，我当然先找到start>40的记录啊，然后再在start>40的记录中查找
stop<10的记录啊。。 是么？
But，又像14楼所说的，2表连接查询的时候，用的是内循环方式，第一张表应该是全表扫描的，不管有没有索引，内循环的表如果有索引，直接根据索引找到记录，如果没有索引，全表扫描。
我想知道，关键是mysql如何选择执行计划，明明应该先全表扫描我的小表，再通过索引找大表（也就是内层循环），我认为这样才是最合理的啊？可是你说的是：
“优化顺序上应该先用temp_3_new_0928然后再去查cytobandideo这样才是最优化的，MYSQL的执行计划是正确的”我现在很糊涂哦搞晕了都

Answer 13

您可以看看实际的开销,哪个的last_query_cost小,mysql优化器一般就会使用哪个!select cytobandideo.chrom,start,stop,temp_3_new_0928.name,name2,txstart as tss,strand,seq_id,probeids,txstart,txend,cdsstart,cdsend,cytobandideo.name as cytoband_name,cytobandideo.giestain from temp_3_new_0928 STRAIGHT_JOIN cytobandideo on cytobandideo.chrom=temp_3_new_0928.chrom where start<=50 and stop>=20;show status like 'last_query_cost';select cytobandideo.chrom,start,stop,temp_3_new_0928.name,name2,txstart as tss,strand,seq_id,probeids,txstart,txend,cdsstart,cdsend,cytobandideo.name as cytoband_name,cytobandideo.giestain from cytobandideo STRAIGHT_JOIN temp_3_new_0928  on cytobandideo.chrom=temp_3_new_0928.chrom where start<=50 and stop>=20;show status like 'last_query_cost';

Answer 14

并没有先扫描小表，再查大表这种说法！ 虽然在大部分情况会是这种现象。但从优化的角度这个并不是依据。 很多情况下先大表是更好的选择。比如你的例子中，如果先cytobandideo表，862条记录，然后 for(i=0;i<862;i++) , check (select * from temp_3_new_0928 where chrom=cytobandideo[i].chrom and start<=50 and stop>=20和先从大表：temp_3_new_0928 144950记录。 select * from temp_3_new_0928  where start<=50  (仅start<=50 可以利用索引，stop>=20并无索引可利用) 这样利用索引  start<=50 可以进行筛选。这个速度很快，象查字典一样，直接可以定位到第一个，然后在索引上顺序读下去直到start>50 ,

Answer 15

如果start stop都是int型呢？

Answer 16

如果start stop都是int型呢？ 那就有可能使用start stop的索引哈!

Answer 17

好难理解啊。
select * from temp_3_new_0928 where start<=50 (仅start<=50 可以利用索引，stop>=20并无索引可利用，还有一个循环呢？？
我先理解一下。。

Answer 18

如果是都是int，那么就像14楼所说的那样么？
“更换了表顺序后，应该全表扫描的是小表了才对。”

Answer 19

还有  15 楼 loveflea 指出的， 你的字段类型不一样啊，先改成数字吧。否则索引无法使用。