MySQL 在between 上的索引问题

mchdba

个人觉得你的表建立不是很恰当.从你的sql写法就可以看出来

mchdba

grantguan 发表于 2013-7-24 12:28
这个需求正常，一个ip table,输入随便一个ip,定位这个ip 来自哪个国家，ip 表存放数据start_ip,end_ip,转 ...

where 3533 between ip.number_start and ip.number_end;这种写法很少见到，换成  ip.number_start < 3533  and ip.number_end >3533 ; 试试看呢？

grantguan

mchdba 发表于 2013-7-24 12:03
where 3533 between ip.number_start and ip.number_end;这种写法很少见到，换成  ip.number_start < 353 ...

这个表是IP的标准库，从Maxmind 购买的，
最开始采用ip.number_start <= 3533 and ip.number_end>=3533的写法，也是一样

mchdba

grantguan 发表于 2013-7-24 13:07
这个表是IP的标准库，从Maxmind 购买的，
最开始采用ip.number_start =3533的写法，也是一样

嗯,明白了,今天长知识了,还可以那样写 between and,挺好的.

grantguan

看样子和参数值处于索引中的位置关系很大，3百万的表到150万就开始full table scan了，闲来无事，做一个测试：


50万的时候还走索引


120万的时候还在走索引


到150万的时候，彻底full scan 了


砖家们，求解决方案

jinguanding

CREATE TABLE `ip` (
  `id` bigint(20) NOT NULL,
  `address_start` varchar(16) NOT NULL,
  `address_end` varchar(16) NOT NULL,
  `number_start` bigint(20) NOT NULL,
  `number_end` bigint(20) NOT NULL,
  `country_code` varchar(3) NOT NULL,
  `city_id` bigint(20) NOT NULL,
  PRIMARY KEY (`number_start`,`number_end`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1.你的字符串类型 应该设计为 整型，这样性能更好
2.你的数据分布也有关系；
3.你的查询范围跨度很大，那么则可能导致无法走索引

grantguan

jinguanding 发表于 2013-7-24 16:44
CREATE TABLE `ip` (
  `id` bigint(20) NOT NULL,
  `address_start` varchar(16) NOT NULL,

反复测试得到终极解决方案，创建2个index
index1 on(number_start,number_end)
index2 on(number_end,number_start)
同样的column但是不一样的顺序，这样测试下来无论参数值在哪一个范围，都可以避免full scan, 最慢也是0.2sec，快了几倍，算是交差了,
真是奇葩，还好这是一个静态表。

mchdba

grantguan 发表于 2013-7-24 18:03
反复测试得到终极解决方案，创建2个index
index1 on(number_start,number_end)
index2 on(number_end,n ...

这个方案值得借鉴,谢谢分享.

jinguanding

grantguan 发表于 2013-7-24 17:03
反复测试得到终极解决方案，创建2个index
index1 on(number_start,number_end)
index2 on(number_end,n ...

但是你这样成本上去了～～ INSERT 就可怕了

grantguan

jinguanding 发表于 2013-7-24 17:38
但是你这样成本上去了～～ INSERT 就可怕了

静态表，只有select。