求教 大数据量表 查询

[wminjay]

某表约有5千万条记录 字段ip有索引

 mysql> SELECT count(*)
    -> FROM `newDB`
    -> WHERE ip = '192.186.1.1';
+----------+
| count(*) |
+----------+
|  2014558 |
+----------+
1 row in set (1 min 8.87 sec) 

做次带条件查询 所用时间太长 如何能高效的得出匹配数。

[ACMAIN_CHM]

有几点可以进行优化。

1. 创建（IP）字段的索引 create index xx on `newDB`(id);

[ACMAIN_CHM]

2. 修改表，把IP字段改为INT unsigned。

然后再在IP上创建索引。

查询的时候使用

SELECT count(*) FROM `newDB` WHERE ip = INET_ATON('192.186.1.1');

[loveflea]

楼上的方法不错,ip应该使用int unsigned保存!

一般情况下,字符串索引效率比int的差!!

[wminjay]

先行谢过 明日测试

[angel21li]

学习了

[wminjay]

我弄了2个2百万数据的表做测试，count测试结果不太如意啊 结果如下

mysql> select count(*) from testint;
+----------+
| count(*) |
+----------+
|  2316952 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from testvarchar;
+----------+
| count(*) |
+----------+
|  2316952 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM testint WHERE ip = INET_ATON('192.168.1.22');
+----------+
| count(*) |
+----------+
|   114532 |
+----------+
1 row in set (2.35 sec)

mysql> SELECT count(*) FROM testvarchar WHERE ip = '192.168.1.22';
+----------+
| count(*) |
+----------+
|   114532 |
+----------+
1 row in set (0.40 sec)

mysql> SELECT count(*) FROM testint WHERE ip = INET_ATON('192.168.1.1');
+----------+
| count(*) |
+----------+
|    23324 |
+----------+
1 row in set (1.21 sec)

mysql> SELECT count(*) FROM testvarchar WHERE ip = '192.168.1.2';
+----------+
| count(*) |
+----------+
|    23324 |
+----------+
1 row in set (0.11 sec)

mysql> SELECT count(*) FROM testint WHERE ip = INET_ATON('192.168.1.3');
+----------+
| count(*) |
+----------+
|    97556 |
+----------+
1 row in set (3.42 sec)

mysql> SELECT count(*) FROM testvarchar WHERE ip = '192.168.1.3';
+----------+
| count(*) |
+----------+
|    97556 |
+----------+
1 row in set (0.50 sec)

下面是满意的结果

mysql> insert into testint(ip,若干其他字段) select ip,若干其他字段 from testint;
Query OK, 579238 rows affected (1 min 55.73 sec)
Records: 579238  Duplicates: 0  Warnings: 0

mysql> insert into testvarchar(ip,若干其他字段) select ip,若干其他字段 from testvarchar;
Query OK, 579238 rows affected (3 min 10.01 sec)
Records: 579238  Duplicates: 0  Warnings: 0

mysql> insert into testint(ip,若干其他字段) select ip,若干其他字段 from testint;
Query OK, 1158476 rows affected (5 min 26.66 sec)
Records: 1158476  Duplicates: 0  Warnings: 0

mysql> insert into testvarchar(ip,若干其他字段) select ip,若干其他字段 from testvarchar;
Query OK, 1158476 rows affected (7 min 20.85 sec)
Records: 1158476  Duplicates: 0  Warnings: 0

顺便 问下 对于 time类型或者 varchar类型有没有类似特别的优化方式？

[WWWWA]

IP什么类型？IP上建立索引没有？
COUNT(*)->COUNT(IP)试试

[wminjay]

testint 表 ip类型 int unsigned
testvarchar 表 ip类型 varchar（50）
有索引

mysql> SELECT count(ip) FROM testvarchar WHERE ip = '192.168.1.8';
+-----------------+
| count(ip) |
+-----------------+
|          174840 |
+-----------------+
1 row in set (0.85 sec)

mysql> SELECT count(ip) FROM testint WHERE ip = INET_ATON('192.168.1.8');
+-----------------+
| count(ip) |
+-----------------+
|          174840 |
+-----------------+
1 row in set (3.93 sec)

[wwwwb]

从你的对比中看出速度INT>VARCHAR
SELECT count(ip) FROM testvarchar WHERE ip = '192.168.1.8';
与
SELECT count(*) FROM testvarchar WHERE ip = '192.168.1.8';
速度对比如何

[wminjay]

我试试

[ACMAIN_CHM]

[Quote]testint 表 ip类型 int unsigned
testvarchar 表 ip类型 varchar（50）
有索引
SQL code

mysql> SELECT count(ip) FROM testvarchar WHERE ip = '192.168.1.8';
+-----------------+
| count(ip) |
+-----------------+
| 174840 |
+-----------------+
1 row in set (0.85 sec)

mysql> SELECT count(ip) FROM testint WHERE ip = INET_ATON('192.168.1.8');
+-----------------+
| count(ip) |
+-----------------+
| 174840 |
+-----------------+
1 row in set (3.93 sec)
[/Quote]

贴出你的 show index from testint ; show index from testvarchar ;
explain SELECT count(ip) FROM testvarchar WHERE ip = '192.168.1.8';
explain SELECT count(ip) FROM testint WHERE ip = INET_ATON('192.168.1.8');

[wminjay]

mysql> SELECT count(ip) FROM testvarchar WHERE ip = '192.168.1.20';
+-----------------+
| count(ip) |
+-----------------+
|          142408 |
+-----------------+
1 row in set (0.17 sec)

mysql> SELECT count(*) FROM testvarchar WHERE ip = '192.168.1.20';
+----------+
| count(*) |
+----------+
|   142408 |
+----------+
1 row in set (0.14 sec)

测试了其他几个ip 都是count(*)的效率高

[ACMAIN_CHM]

COUNT(*) 肯定会比 COUNT(IP)高，这个不需要置疑。

但你的INT的比CHAR的慢，应该哪儿有问题。

[SpiritInside]

从实验上看似乎类型匹配的速度快？

从char中找char比较直接?

[zuoxingyu]

斑竹太彪悍了。。又学一招。

贴出参考手册的东东。


INET_ATON(expr)
给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8比特地址。

mysql> SELECT INET_ATON('209.207.224.40');

-> 3520061480

产生的数字总是按照网络字节顺序。如上面的例子，数字按照 209×2563 + 207×2562 + 224×256 + 40 进行计算。

INET_ATON() 也能理解短格式 IP 地址：

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');

-> 2130706433, 2130706433

注释： 在存储由INET_ATON() 产生的值时，推荐你使用 INT UNSIGNED 列。假如你使用 (带符号) INT列, 则相应的第一个八位组大于127的IP 地址值会被截至 2147483647 (即, INET_ATON('127.255.255.255') 所返回的值)。请参见11.2节，“数值类型”。

INET_NTOA(expr)
给定一个数字网络地址 (4 或 8 比特),返回作为字符串的该地址的电地址表示。

mysql> SELECT INET_NTOA(3520061480);

-> '209.207.224.40'

[wwwwb]

将IP修改为INT unsigned，建立索引查询

[wminjay]

先结贴了 后续 还有问题 继续讨论