求教 大数据量表 查询

Question

某表约有5千万条记录 字段ip有索引
 mysql> SELECT count(*)
    -> FROM `newDB`
    -> WHERE ip = '192.186.1.1';
+----------+
| count(*) |
+----------+
|  2014558 |
+----------+
1 row in set (1 min 8.87 sec) 做次带条件查询 所用时间太长 如何能高效的得出匹配数。

Answer 1

有几点可以进行优化。1. 创建（IP）字段的索引 create index xx on `newDB`(id);

Answer 2

2. 修改表，把IP字段改为INT unsigned。 然后再在IP上创建索引。查询的时候使用 SELECT count(*)  FROM `newDB` WHERE ip = INET_ATON('192.186.1.1');

Answer 3

楼上的方法不错,ip应该使用int unsigned保存!一般情况下,字符串索引效率比int的差!!

Answer 4

斑竹太彪悍了。。又学一招。贴出参考手册的东东。
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'

Answer 5

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

Answer 6

我弄了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: 0mysql> insert into testvarchar(ip,若干其他字段) select ip,若干其他字段 from testvarchar;
Query OK, 579238 rows affected (3 min 10.01 sec)
Records: 579238  Duplicates: 0  Warnings: 0mysql> insert into testint(ip,若干其他字段) select ip,若干其他字段 from testint;
Query OK, 1158476 rows affected (5 min 26.66 sec)
Records: 1158476  Duplicates: 0  Warnings: 0mysql> 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类型有没有类似特别的优化方式？

Answer 7

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

Answer 8

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)

Answer 9

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

Answer 10

贴出你的 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');

Answer 11

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(*)的效率高

Answer 12

COUNT(*) 肯定会比 COUNT(IP)高，这个不需要置疑。但你的INT的比CHAR的慢，应该哪儿有问题。

Answer 13

从实验上看似乎类型匹配的速度快？从char中找char比较直接?