请问一下一张表3600万数据, 有索引的时候查询还需要15秒左右,正常么?

yj1804

请问一下一张表3600万数据, 有索引的时候查询还需要15秒左右,正常么?
用explain看过执行计划, 的确有使用索引,
不知道有没有办法缩短查询时间?

使用的是5.1.23rc, 数据库引擎是MYISAM
硬件配置: 2 * INTEL 5030 CPU, 2G内存

mysql> DESC NPE_RECORD_2008081323;
+--------------------------+----------------------+------+-----+---------+-------+
| Field                    | Type                 | Null | Key | Default | Extra |
+--------------------------+----------------------+------+-----+---------+-------+
| DEVICE_IP                | int(10) unsigned     | YES  |     | NULL    |       |
| PROTOCOLIDENTIFIER       | tinyint(3) unsigned  | YES  |     | NULL    |       |
| APP_PROTOCOL             | tinyint(3) unsigned  | YES  |     | NULL    |       |
| SOURCEIPV4ADDRESS        | int(10) unsigned     | YES  | MUL | NULL    |       |
| DESTINATIONIPV4ADDRESS   | int(10) unsigned     | YES  |     | NULL    |       |
| SOURCETRANSPORTPORT      | smallint(5) unsigned | YES  |     | NULL    |       |
| DESTINATIONTRANSPORTPORT | smallint(5) unsigned | YES  |     | NULL    |       |
| NAT_SRC_IP               | int(10) unsigned     | YES  |     | NULL    |       |
| NAT_DST_IP               | int(10) unsigned     | YES  | MUL | NULL    |       |
| NAT_SRC_PORT             | smallint(5) unsigned | YES  |     | NULL    |       |
| NAT_DST_PORT             | smallint(5) unsigned | YES  |     | NULL    |       |
| IN_OCTETS                | int(10) unsigned     | YES  |     | NULL    |       |
| OUT_OCTETS               | int(10) unsigned     | YES  |     | NULL    |       |
| FLOW_CREATE_TIME         | int(10) unsigned     | NO   |     | NULL    |       |
| FLOW_DIE_TIME            | int(10) unsigned     | NO   | MUL | NULL    |       |
+--------------------------+----------------------+------+-----+---------+-------+

mysql> show index from NPE_RECORD_2008081323;+-----------------------+------------+--------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name           | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+--------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| NPE_RECORD_2008081323 |          1 | INDX_DIETIME       |            1 | FLOW_DIE_TIME     | A         |        3575 |     NULL | NULL   |      | BTREE      |         |
| NPE_RECORD_2008081323 |          1 | INDX_SOURCEIP      |            1 | SOURCEIPV4ADDRESS | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |
| NPE_RECORD_2008081323 |          1 | INDX_DESTINATIONIP |            1 | NAT_DST_IP        | A         |       20000 |     NULL | NULL   | YES  | BTREE      |         |
+-----------------------+------------+--------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637 AND FLOW_CREATE_TIME>1218639600 AND FLOW_DIE_TIME<1218643199;
+----------+
| COUNT(1) |
+----------+
|     3073 |
+----------+
1 row in set (14.05 sec)

[ 本帖最后由 yj1804 于 2008-8-14 15:15 编辑 ]

voxxu

explain一下，把结果贴上来

yj1804

mysql> explain  SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637 AND FLOW_CREATE_TIME>1218639600 AND FLOW_DIE_TIME<1218643199;
+----+-------------+-----------------------+------+----------------------------+---------------+---------+-------+------+-------------+
| id | select_type | table                 | type | possible_keys              | key           | key_len | ref   | rows | Extra       |
+----+-------------+-----------------------+------+----------------------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | NPE_RECORD_2008081323 | ref  | INDX_DIETIME,INDX_SOURCEIP | INDX_SOURCEIP | 5       | const | 2402 | Using where |
+----+-------------+-----------------------+------+----------------------------+---------------+---------+-------+------+-------------+
1 row in set (2.27 sec)

ohno

FLOW_CREATE_TIME  这列没有索引 ？

ruochen

从时间上来看
是很慢了

yj1804

原帖由 ohno 于 2008-8-14 16:24 发表
FLOW_CREATE_TIME  这列没有索引 ？

这一列原来是有索引, 但是对查询帮助不大, 因为条件上的时间范围包含了索引大部分值, mysql 不会选择使用这个索引

yj1804

# The following options will be passed to all MySQL clients
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set = utf8
skip-locking
#key_buffer = 384M
#key_buffer = 1024M
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
#read_buffer_size = 2M
read_buffer_size = 16M
read_rnd_buffer_size = 8M
net_buffer_length = 2K
thread_stack = 64K
#myisam_sort_buffer_size = 800M
#myisam_sort_buffer_size = 64M
myisam_sort_buffer_size = 512M
datadir=/home/mysql
server-id               =1
#tmp_table_size = 512M
#key_cache_block_size = 4K
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
default-character-set = utf8
[isamchk]
key_buffer = 8M
sort_buffer_size = 8M
[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout

ruochen

SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637

这个的记录数有多少？

yj1804

原帖由 ruochen 于 2008-8-14 17:05 发表
SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637

这个的记录数有多少？

mysql> SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637;
+----------+
| COUNT(1) |
+----------+
|     3095 |
+----------+
1 row in set (0.11 sec)
3232236637这个值, 已经查询过一次以后,后面的查询就很快了
但是换一个条件值, 又会比较慢, 这个是不是哪个缓冲区太小的缘故?

[ 本帖最后由 yj1804 于 2008-8-14 17:11 编辑 ]

ruochen

mysql> SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637;
+----------+
| COUNT(1) |
+----------+
|     3095 |
+----------+
1 row in set (0.11 sec)


mysql> SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637 AND FLOW_CREATE_TIME>1218639600 AND FLOW_DIE_TIME<1218643199;
+----------+
| COUNT(1) |
+----------+
|     3073 |
+----------+
1 row in set (14.05 sec)

这两个查询的记录数目差别很小的呀
多了两个条件
时间上就慢了这么多

是不是索引建立的太多了？


mysql> SELECT COUNT(1) FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232236637;
+----------+
| COUNT(1) |
+----------+
|     3095 |
+----------+
1 row in set (0.11 sec)
能不能在测试库上建立这个表
然后不建立任何的索引
看下这个查询的结果