我有两张表table1,table2,table2中有一个字段要和table1中的两个字段进行比较,语句是:
select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
不管是建 table1 表上的start,end 组合索引 还是start ,end的单个索引,或者是加上强制索引:
select a.* from table1 a force index(index_name), table2 b where a.start <= b.colum and a.end>=b.column;查询速度很慢...对索引知道的还比较少,都不会走table1的索引,各位达人有什么好的建议没?
select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
不管是建 table1 表上的start,end 组合索引 还是start ,end的单个索引,或者是加上强制索引:
select a.* from table1 a force index(index_name), table2 b where a.start <= b.colum and a.end>=b.column;查询速度很慢...对索引知道的还比较少,都不会走table1的索引,各位达人有什么好的建议没?
b.column已经加上了,而且也走索引...难道真没办法?查询范围数据都很慢么?
至于先哪个表进行全表扫描,要具体看你的表中数据的分布。
show index from table2;
explain select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
都加上索引。
`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
'table1', '1', 'IDX_table1_startend','1', 'startIp', 'A', '197', NULL, NULL, 'YES', 'BTREE', '', ''
'table1', '1', 'IDX_table1_startend','2', 'endIp', 'A', '197', NULL, NULL, 'YES', 'BTREE', '', ''SHOW INDEX FROM table2;
`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
'table2', '1', 'IDX_table2_intIp', '1', 'intIp', 'A', '143', NULL, NULL, 'YES', 'BTREE', '', ''
`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
'1', 'SIMPLE', 'b', 'index', 'IDX_table2_intIp', 'IDX_table2_intIp','5', NULL, '143', 'Using index'
'1', 'SIMPLE', 'a', 'ALL', 'IDX_table1_startend', NULL, NULL, NULL, '369307','Range checked for each record (index map: 0x1)'
这个是6楼高手要求贴出来的,现在是table1里面有38W条记录并且以后基本不变,table2只有几百条,但是会时时增加.请高手们帮我看看.
create index xxx1 on table1(start);
create index xxx1 on table1(end);应该会提高一点儿。
从本质上来说,数据不会快,应该你的查询条件过于宽松。 另外假设 table1中每条件记录都符合table2的这个a.start <= b.colum and a.end>=b.column条件,则等同于一个笛卡尔积。
create index xxx1 on table1(start);
create index xxx1 on table1(end);
这个我试过,没啥用,如果给加个条件start = 'xxx'这样就会走索引,速度就很快,不知道有没有一个万用字符啥的玩意,就像like '%'一样的玩意,这样就能控制走索引了..异想天开了有点....
另外建议不要做任何修改,否则别人会对你的字段名,表名混淆反而引起错误理解。
table2.colum 就是table2.intIp
下面是分别建 table1.startIp table1.endIp索引的相关信息:
show index from table1:
`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
'table1', '1', 'IDX_table1_start','1', 'startIp', 'A', '199', NULL, NULL, 'YES', 'BTREE', '', ''
'table1', '1', 'IDX_table1_end','1', 'endIp', 'A', '199', NULL, NULL, 'YES', 'BTREE', '', ''SHOW INDEX FROM table2;
`Table`, `Non_unique`, `Key_name`, `Seq_in_index`, `Column_name`, `Collation`, `Cardinality`, `Sub_part`, `Packed`, `Null`, `Index_type`, `Comment`, `Index_comment`
'table2', '1', 'IDX_table2_intIp', '1', 'intIp', 'A', '143', NULL, NULL, 'YES', 'BTREE', '', ''EXPLAIN SELECT a.* FROM table1 a, table2 b WHERE a.startIp <= b.intIp AND a.endIp >= b.intIp ;`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
'1', 'SIMPLE', 'b', 'index','IDX_table2_intIp', 'IDX_table2_intIp','5', NULL, '88', 'Using index'
'1', 'SIMPLE', 'a', 'ALL', 'IDX_table1_start,IDX_table1_end', NULL, NULL, NULL, '379392','Range checked for each record (index map: 0x3)' ;不知道各位对两个表中的IP比较有没有建议...
'table1' 1' IDX_table1_start' '1' startIp' A' 199' NULL NULL YES'
'table1' 1' IDX_table1_end' '1' endIp' A' 199' NULL NULL YES'
这个比较奇怪,表中有记录380,000条 ,但这380,000条中的startIp基本上都完全相同,其中只有199个不同值。如果情况真是这样。建议SQL语句试试:
select * from table1 a
where exists (select 1 from table2 where intIp between a.startIp and b.intIp)
不过这个语句逻辑上与你的语句并不相等,它不会列重复匹配的记录。