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

ruochen

原帖由 yj1804 于 2008-8-14 17:09 发表

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

你换个看下查询的时间，和这个对比下
主要也就是buffer/cache和sort的几个参数咯

yj1804

不带任何索引的情况下, 有测试过, 这个查询基本不可用

另外我测试过了不带时间条件的语句, 时间上差不多
mysql> SELECT * FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232237999;
4484 rows in set (19.38 sec)

ruochen

还真的奇怪了

yj1804

下面是执行语句
SELECT * FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232238888;
前后的show status的结果. 左边是执行前的结果, 后边是执行后的结果
mysql> show status;                         mysql> SHOW STATUS;                          
+----------------------------+-----------+  +----------------------------+-------------+
| Variable_name              | Value     |  | Variable_name              | Value       |
+----------------------------+-----------+  +----------------------------+-------------+
| Aborted_clients            | 65        |  | Aborted_clients            | 65          |
| Aborted_connects           | 0         |  | Aborted_connects           | 0           |
| Binlog_cache_disk_use      | 0         |  | Binlog_cache_disk_use      | 0           |
| Binlog_cache_use           | 0         |  | Binlog_cache_use           | 0           |
| Bytes_received             | 125       |  | Bytes_received             | 367         |
| Bytes_sent                 | 235       |  | Bytes_sent                 | 417834      |
| Com_admin_commands         | 0         |  | Com_admin_commands         | 0           |
| Com_alter_db               | 0         |  | Com_alter_db               | 0           |
| Com_alter_event            | 0         |  | Com_alter_event            | 0           |
| Com_alter_table            | 0         |  | Com_alter_table            | 0           |
| Com_analyze                | 0         |  | Com_analyze                | 0           |
| Com_backup_table           | 0         |  | Com_backup_table           | 0           |
| Com_begin                  | 0         |  | Com_begin                  | 0           |
| Com_call_procedure         | 0         |  | Com_call_procedure         | 0           |
| Com_change_db              | 1         |  | Com_change_db              | 1           |
| Com_change_master          | 0         |  | Com_change_master          | 0           |
| Com_check                  | 0         |  | Com_check                  | 0           |
| Com_checksum               | 0         |  | Com_checksum               | 0           |
| Com_commit                 | 0         |  | Com_commit                 | 0           |
| Com_create_db              | 0         |  | Com_create_db              | 0           |
| Com_create_event           | 0         |  | Com_create_event           | 0           |
| Com_create_function        | 0         |  | Com_create_function        | 0           |
| Com_create_index           | 0         |  | Com_create_index           | 0           |
| Com_create_table           | 0         |  | Com_create_table           | 0           |
| Com_create_user            | 0         |  | Com_create_user            | 0           |
| Com_dealloc_sql            | 0         |  | Com_dealloc_sql            | 0           |
| Com_delete                 | 0         |  | Com_delete                 | 0           |
| Com_delete_multi           | 0         |  | Com_delete_multi           | 0           |
| Com_do                     | 0         |  | Com_do                     | 0           |
| Com_drop_db                | 0         |  | Com_drop_db                | 0           |
| Com_drop_event             | 0         |  | Com_drop_event             | 0           |
| Com_drop_function          | 0         |  | Com_drop_function          | 0           |
| Com_drop_index             | 0         |  | Com_drop_index             | 0           |
| Com_drop_table             | 0         |  | Com_drop_table             | 0           |
| Com_drop_user              | 0         |  | Com_drop_user              | 0           |
| Com_execute_sql            | 0         |  | Com_execute_sql            | 0           |
| Com_flush                  | 0         |  | Com_flush                  | 0           |
| Com_grant                  | 0         |  | Com_grant                  | 0           |
| Com_ha_close               | 0         |  | Com_ha_close               | 0           |
| Com_ha_open                | 0         |  | Com_ha_open                | 0           |
| Com_ha_read                | 0         |  | Com_ha_read                | 0           |
| Com_help                   | 0         |  | Com_help                   | 0           |
| Com_insert                 | 0         |  | Com_insert                 | 0           |
| Com_insert_select          | 0         |  | Com_insert_select          | 0           |
| Com_kill                   | 0         |  | Com_kill                   | 0           |
| Com_load                   | 0         |  | Com_load                   | 0           |
| Com_load_master_data       | 0         |  | Com_load_master_data       | 0           |
| Com_load_master_table      | 0         |  | Com_load_master_table      | 0           |
| Com_lock_tables            | 0         |  | Com_lock_tables            | 0           |
| Com_optimize               | 0         |  | Com_optimize               | 0           |
| Com_preload_keys           | 0         |  | Com_preload_keys           | 0           |
| Com_prepare_sql            | 0         |  | Com_prepare_sql            | 0           |
| Com_purge                  | 0         |  | Com_purge                  | 0           |
| Com_purge_before_date      | 0         |  | Com_purge_before_date      | 0           |
| Com_rename_table           | 0         |  | Com_rename_table           | 0           |
| Com_repair                 | 0         |  | Com_repair                 | 0           |
| Com_replace                | 0         |  | Com_replace                | 0           |
| Com_replace_select         | 0         |  | Com_replace_select         | 0           |
| Com_reset                  | 0         |  | Com_reset                  | 0           |
| Com_restore_table          | 0         |  | Com_restore_table          | 0           |
| Com_revoke                 | 0         |  | Com_revoke                 | 0           |
| Com_revoke_all             | 0         |  | Com_revoke_all             | 0           |
| Com_rollback               | 0         |  | Com_rollback               | 0           |
| Com_savepoint              | 0         |  | Com_savepoint              | 0           |
| Com_select                 | 2         |  | Com_select                 | 5           |
| Com_set_option             | 0         |  | Com_set_option             | 0           |
| Com_show_binlog_events     | 0         |  | Com_show_binlog_events     | 0           |
| Com_show_binlogs           | 0         |  | Com_show_binlogs           | 0           |
| Com_show_charsets          | 0         |  | Com_show_charsets          | 0           |
| Com_show_collations        | 0         |  | Com_show_collations        | 0           |
| Com_show_column_types      | 0         |  | Com_show_column_types      | 0           |
| Com_show_create_db         | 0         |  | Com_show_create_db         | 0           |
| Com_show_create_event      | 0         |  | Com_show_create_event      | 0           |
| Com_show_create_table      | 0         |  | Com_show_create_table      | 0           |
| Com_show_databases         | 0         |  | Com_show_databases         | 0           |
| Com_show_engine_logs       | 0         |  | Com_show_engine_logs       | 0           |
| Com_show_engine_mutex      | 0         |  | Com_show_engine_mutex      | 0           |
| Com_show_engine_status     | 0         |  | Com_show_engine_status     | 0           |
| Com_show_errors            | 0         |  | Com_show_errors            | 0           |
| Com_show_events            | 0         |  | Com_show_events            | 0           |
| Com_show_fields            | 0         |  | Com_show_fields            | 0           |
| Com_show_grants            | 0         |  | Com_show_grants            | 0           |
| Com_show_keys              | 0         |  | Com_show_keys              | 0           |
| Com_show_master_status     | 0         |  | Com_show_master_status     | 0           |
| Com_show_new_master        | 0         |  | Com_show_new_master        | 0           |
| Com_show_open_tables       | 0         |  | Com_show_open_tables       | 0           |
| Com_show_plugins           | 0         |  | Com_show_plugins           | 0           |
| Com_show_privileges        | 0         |  | Com_show_privileges        | 0           |
| Com_show_processlist       | 0         |  | Com_show_processlist       | 0           |
| Com_show_slave_hosts       | 0         |  | Com_show_slave_hosts       | 0           |
| Com_show_slave_status      | 0         |  | Com_show_slave_status      | 0           |
| Com_show_status            | 1         |  | Com_show_status            | 2           |
| Com_show_storage_engines   | 0         |  | Com_show_storage_engines   | 0           |
| Com_show_tables            | 0         |  | Com_show_tables            | 0           |
| Com_show_triggers          | 0         |  | Com_show_triggers          | 0           |
| Com_show_variables         | 0         |  | Com_show_variables         | 0           |
| Com_show_warnings          | 0         |  | Com_show_warnings          | 0           |
| Com_slave_start            | 0         |  | Com_slave_start            | 0           |
| Com_slave_stop             | 0         |  | Com_slave_stop             | 0           |
| Com_stmt_close             | 0         |  | Com_stmt_close             | 0           |
| Com_stmt_execute           | 0         |  | Com_stmt_execute           | 0           |
| Com_stmt_fetch             | 0         |  | Com_stmt_fetch             | 0           |
| Com_stmt_prepare           | 0         |  | Com_stmt_prepare           | 0           |
| Com_stmt_reset             | 0         |  | Com_stmt_reset             | 0           |
| Com_stmt_send_long_data    | 0         |  | Com_stmt_send_long_data    | 0           |
| Com_truncate               | 0         |  | Com_truncate               | 0           |
| Com_unlock_tables          | 0         |  | Com_unlock_tables          | 0           |
| Com_update                 | 0         |  | Com_update                 | 0           |
| Com_update_multi           | 0         |  | Com_update_multi           | 0           |
| Com_xa_commit              | 0         |  | Com_xa_commit              | 0           |
| Com_xa_end                 | 0         |  | Com_xa_end                 | 0           |
| Com_xa_prepare             | 0         |  | Com_xa_prepare             | 0           |
| Com_xa_recover             | 0         |  | Com_xa_recover             | 0           |
| Com_xa_rollback            | 0         |  | Com_xa_rollback            | 0           |
| Com_xa_start               | 0         |  | Com_xa_start               | 0           |
| Compression                | OFF       |  | Compression                | OFF         |
| Connections                | 642       |  | Connections                | 649         |
| Created_tmp_disk_tables    | 0         |  | Created_tmp_disk_tables    | 0           |
| Created_tmp_files          | 22        |  | Created_tmp_files          | 22          |
| Created_tmp_tables         | 0         |  | Created_tmp_tables         | 0           |
| Delayed_errors             | 0         |  | Delayed_errors             | 0           |
| Delayed_insert_threads     | 0         |  | Delayed_insert_threads     | 0           |
| Delayed_writes             | 0         |  | Delayed_writes             | 0           |
| Flush_commands             | 1         |  | Flush_commands             | 1           |
| Handler_commit             | 0         |  | Handler_commit             | 0           |
| Handler_delete             | 0         |  | Handler_delete             | 0           |
| Handler_discover           | 0         |  | Handler_discover           | 0           |
| Handler_prepare            | 0         |  | Handler_prepare            | 0           |
| Handler_read_first         | 0         |  | Handler_read_first         | 0           |
| Handler_read_key           | 0         |  | Handler_read_key           | 3           |
| Handler_read_next          | 0         |  | Handler_read_next          | 3516        |
| Handler_read_prev          | 0         |  | Handler_read_prev          | 0           |
| Handler_read_rnd           | 0         |  | Handler_read_rnd           | 0           |
| Handler_read_rnd_next      | 0         |  | Handler_read_rnd_next      | 0           |
| Handler_rollback           | 0         |  | Handler_rollback           | 0           |
| Handler_savepoint          | 0         |  | Handler_savepoint          | 0           |
| Handler_savepoint_rollback | 0         |  | Handler_savepoint_rollback | 0           |
| Handler_update             | 0         |  | Handler_update             | 0           |
| Handler_write              | 0         |  | Handler_write              | 0           |
| Key_blocks_not_flushed     | 0         |  | Key_blocks_not_flushed     | 0           |
| Key_blocks_unused          | 453697    |  | Key_blocks_unused          | 453648      |
| Key_blocks_used            | 463920    |  | Key_blocks_used            | 463920      |
| Key_read_requests          | 204502016 |  | Key_read_requests          | 204502349   |
| Key_reads                  | 52056     |  | Key_reads                  | 52105       |
| Key_write_requests         | 48210695  |  | Key_write_requests         | 48210695    |
| Key_writes                 | 1451916   |  | Key_writes                 | 1451916     |
| Last_query_cost            | 0.000000  |  | Last_query_cost            | 3274.799000 |
| Max_used_connections       | 102       |  | Max_used_connections       | 102         |
| Not_flushed_delayed_rows   | 0         |  | Not_flushed_delayed_rows   | 0           |
| Open_files                 | 1009      |  | Open_files                 | 1009        |
| Open_streams               | 0         |  | Open_streams               | 0           |
| Open_table_definitions     | 497       |  | Open_table_definitions     | 497         |
| Open_tables                | 512       |  | Open_tables                | 512         |
| Opened_files               | 250313    |  | Opened_files               | 250319      |
| Opened_tables              | 0         |  | Opened_tables              | 0           |
| Prepared_stmt_count        | 3         |  | Prepared_stmt_count        | 4           |
| Qcache_free_blocks         | 0         |  | Qcache_free_blocks         | 0           |
| Qcache_free_memory         | 0         |  | Qcache_free_memory         | 0           |
| Qcache_hits                | 0         |  | Qcache_hits                | 0           |
| Qcache_inserts             | 0         |  | Qcache_inserts             | 0           |
| Qcache_lowmem_prunes       | 0         |  | Qcache_lowmem_prunes       | 0           |
| Qcache_not_cached          | 0         |  | Qcache_not_cached          | 0           |
| Qcache_queries_in_cache    | 0         |  | Qcache_queries_in_cache    | 0           |
| Qcache_total_blocks        | 0         |  | Qcache_total_blocks        | 0           |
| Questions                  | 173172    |  | Questions                  | 173558      |
| Rpl_status                 | NULL      |  | Rpl_status                 | NULL        |
| Select_full_join           | 0         |  | Select_full_join           | 0           |
| Select_full_range_join     | 0         |  | Select_full_range_join     | 0           |
| Select_range               | 0         |  | Select_range               | 0           |
| Select_range_check         | 0         |  | Select_range_check         | 0           |
| Select_scan                | 0         |  | Select_scan                | 0           |
| Slave_open_temp_tables     | 0         |  | Slave_open_temp_tables     | 0           |
| Slave_retried_transactions | 0         |  | Slave_retried_transactions | 0           |
| Slave_running              | OFF       |  | Slave_running              | OFF         |
| Slow_launch_threads        | 0         |  | Slow_launch_threads        | 0           |
| Slow_queries               | 0         |  | Slow_queries               | 1           |
| Sort_merge_passes          | 0         |  | Sort_merge_passes          | 0           |
| Sort_range                 | 0         |  | Sort_range                 | 0           |
| Sort_rows                  | 0         |  | Sort_rows                  | 0           |
| Sort_scan                  | 0         |  | Sort_scan                  | 0           |
| Table_locks_immediate      | 8292      |  | Table_locks_immediate      | 8376        |
| Table_locks_waited         | 32        |  | Table_locks_waited         | 32          |
| Tc_log_max_pages_used      | 0         |  | Tc_log_max_pages_used      | 0           |
| Tc_log_page_size           | 0         |  | Tc_log_page_size           | 0           |
| Tc_log_page_waits          | 0         |  | Tc_log_page_waits          | 0           |
| Threads_cached             | 0         |  | Threads_cached             | 0           |
| Threads_connected          | 21        |  | Threads_connected          | 21          |
| Threads_created            | 641       |  | Threads_created            | 648         |
| Threads_running            | 1         |  | Threads_running            | 1           |
| Uptime                     | 5959      |  | Uptime                     | 6028        |
+----------------------------+-----------+  +----------------------------+-------------+
190 rows in set (0.03 sec)                  190 rows in set (0.03 sec)

yj1804

mysql> SELECT IN_OCTETS FROM NPE_RECORD_2008081323 WHERE SOURCEIPV4ADDRESS=3232237777;
3207 rows in set (14.49 sec)

只返回一列, 也需要15秒左右

ruochen

3600w行里面取3207行
比率很小的，肯定得索引
看下手册，找索引相关的参数调试看看
肯定要sort的

yj1804

原帖由 ruochen 于 2008-8-14 17:40 发表
3600w行里面取3207行
比率很小的，肯定得索引
看下手册，找索引相关的参数调试看看
肯定要sort的

key_buffer, myisam_sort_buffer_size这些才参数也调了好几个值, 都没啥明显变化, 所以才来论坛求助,
有哪位兄弟有类似数据量的表么, 查询一下需要多长时间?

ohno

建议你试下 建立一个 包含3个列的索引， 而不是分开每个列来建立一个索引。

mysql 进行 select 只能使用一个索引。

还有不知道你 mysql 有没有打开查询缓存。

下面有篇文章 不知道对你有没有作用，  你先 建立一个 包含3个列的索引 看看效果先

下面这个文章 感觉作用不大
提高MySql查询性能 - 打开和设置查询缓存入门视频教程

ohno

问题描述：


我们要访问的表是一个非常大的表，四千万条记录，id是主键，program_id上建了索引。
执行一条SQL:

select * from program_access_log where program_id between 1 and 4000
这条SQL非常慢。
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000
但是这条SQL仍然很慢，速度比上面一条几乎没有提升。
Mysql处理50万条记录的表，条件字段还建了索引，这条语句应该是瞬间完成的。




问题分析：


这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。

问题解决方案：
1. 以program_id为条件对表进行分区
2. 分表处理，每张表的大小不超过内存的大小
然而，服务器用的是mysql5.0，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。
所以我们采取了第三种办法：

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000
现在program_id的范围远大于id的范围，id被当做主要索引进行查找，由于id是主键，所以查找的是连续50万条记录，速度和访问一个50万条记录的表基本一样

总结：
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！

yj1804

我按照SOURCEIPV4ADDRESS进行HASH分区, 分了64个区以后,性能提升非常明显
mysql> SELECT IN_OCTETS FROM NPE_RECORD_2008081421 WHERE SOURCEIPV4ADDRESS=3232237777;
3643 rows in set (0.70 sec)

mysql> SELECT * FROM NPE_RECORD_2008081421 WHERE SOURCEIPV4ADDRESS=3232242688;
3778 rows in set (0.08 sec)