mysql索引问题

sunsetfreedom 2010-09-28 03:01:05
我有两张表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的索引,各位达人有什么好的建议没?
...全文
126 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2010-09-29
  • 打赏
  • 举报
 回复
`Table` `Non_unique` `Key_name` `Seq_in_index` `Column_name` `Collation` `Cardinality` `Sub_part` `Packed` `Null`
'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)

不过这个语句逻辑上与你的语句并不相等，它不会列重复匹配的记录。
sunsetfreedom 2010-09-29
  • 打赏
  • 举报
 回复
这几个字段都是 ip转int unsigned ,为的是比较IP大小.table1中存放的是ip地址的范围和对应的地址,现在要找出table2中存放的IP对应的地址:
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比较有没有建议...
ACMAIN_CHM 2010-09-29
  • 打赏
  • 举报
 回复
建议把创建相关索引后的 show index 贴出来，还有你的EXPLAIN的结果。
另外建议不要做任何修改，否则别人会对你的字段名，表名混淆反而引起错误理解。
feixianxxx 2010-09-29
  • 打赏
  • 举报
 回复
这几个字段什么类型的...

sunsetfreedom 2010-09-29
  • 打赏
  • 举报
 回复
table1中有38W条记录,不清楚mysql是怎么预算的.table2(intIp) 索引:'IDX_table2_intIp'

分别创建
create index xxx1 on table1(start);
create index xxx1 on table1(end);
这个我试过,没啥用,如果给加个条件start = 'xxx'这样就会走索引,速度就很快,不知道有没有一个万用字符啥的玩意,就像like '%'一样的玩意,这样就能控制走索引了..异想天开了有点....
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
还有你的table1中不过才197条记录啊。
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
另外没有看到你的 table2（column）的索引。
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
分别创建
create index xxx1 on table1(start);
create index xxx1 on table1(end);

应该会提高一点儿。
从本质上来说，数据不会快，应该你的查询条件过于宽松。 另外假设 table1中每条件记录都符合table2的这个a.start <= b.colum and a.end>=b.column条件，则等同于一个笛卡尔积。
sunsetfreedom 2010-09-28
  • 打赏
  • 举报
 回复
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_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只有几百条,但是会时时增加.请高手们帮我看看.
zuoxingyu 2010-09-28
  • 打赏
  • 举报
 回复
a.start ,b.colum ,a.end


都加上索引。
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
如果希望在这里讨论的话，则需要你贴出

show index from table1;
show index from table2;
explain select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
select a.* from table1 a, table2 b where a.start <= b.colum and a.end>=b.column;

这种语句，没有办法，你必须选择其中一张表进行全表扫描，然后对另一张表进行索引上的优化。
至于先哪个表进行全表扫描，要具体看你的表中数据的分布。


sunsetfreedom 2010-09-28
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 acmain_chm 的回复:]
b.column 加索引吧。 无法避免的其中一个表必须是全表扫描。
[/Quote]

b.column已经加上了,而且也走索引...难道真没办法?查询范围数据都很慢么?
sunsetfreedom 2010-09-28
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 rucypli 的回复:]
你这出来的结果集都快相当于笛卡尔积了
[/Quote]
不会啊,start<end的,如果我在table1表上建一个无关的字段 type 默认值为1,然后在这个字段上建索引,关联两张表的时候加上条件: a.type = '1' ,这样确实能大大减少查询时间,时间还是不符合要求,而且type是所有字段都是1,总觉得很别扭
ACMAIN_CHM 2010-09-28
  • 打赏
  • 举报
 回复
b.column 加索引吧。 无法避免的其中一个表必须是全表扫描。
rucypli 2010-09-28
  • 打赏
  • 举报
 回复
你这出来的结果集都快相当于笛卡尔积了
MySQL性能优化之MySQL索引精讲
课程对MySQL索引做了深度的讲解，对b+tree做了深度剖析，及索引的应用。   如果大家想系统学习MySQL，可关注我们的课程：企业级MySQL系列教程-贯穿课程讲解MySQL性能优化+原理+实战 点此直达好课=>...
一文搞懂MySQL索引（清晰明了）
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 MySQL中常用的索引结构（索引底层的数据结构）有：B-TREE ，B+TREE ，HASH 等。 MySQL 的索引有两种分类方式：逻辑分类和物理...
MySQL索引性能优化分析
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
Mysql 索引问题-日期索引使用
我的索引是日期类型的，首先想到的是mysql对日期类型的索引的处理机制是不是不同，在where条件里试了几种，发现效果都差不多， where dayid >= ‘20161121’ where dayid >= ‘2016-11-21’ where dayid ...
浅谈MySQL中IN与索引
首先查询MySQL索引问题可以使用EXPLAIN EXPLAIN：explain 命令获取 select 语句的执行计划，通过 explain我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间...
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章