mysql select语句变慢

[ffyd2000]

哪位大侠指点一下,什么原因会使mysql select语句大面积地变慢?
最近几天mysql服务器的slow log里面突然出现大量的select记录query_time从3到20的都有,分布在不同的表和不同的库
有一条语句甚至有到query_time:47
最近变没有大面积改动数据库

[懒得去死]

1.索引没有适当的建立
2.给QUERY分配的内存不足。

[ffyd2000]

应该不是索引的问题,变慢最近几天才出现的,出现变慢情况的也不是新建的表
服务器配置是这样的
max_connections=1024
query_cache_size=16M
table_cache=256
tmp_table_size=9M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=64M
key_buffer_size=384M
read_buffer_size=1M
read_rnd_buffer_size=4M
sort_buffer_size=8M
long_query_time=2

[mpshun]

好想mysql某个表的数据行达到一定数量级后就会出现查询效率低下的问题.

[wangchunning45]

我能想到的原因如下：
1. 表的量级变大。随着表行数的增多，select的结果也越来越多，从而会使查询变慢。
2. 访问量突然增大。大量的并发访问也会使磁盘的I/O增加，从而降低读取速度。
3. 如果是MyISAM类型的表，可能存在锁表的问题。可能存在其它事务正在更新该表的情况，导致select等待，从而降低速度。

楼主可以把show status的结果贴出来，一起分析一下

[ffyd2000]

忙了几天其他项目
服务器的show status
Your MySQL connection id is 5917 to server version: 5.0.18-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show status ;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 91 |
| Bytes_sent | 79 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 5959 |

[ffyd2000]

| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 47 |
| Created_tmp_tables | 1 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 130 |
| Innodb_buffer_pool_pages_data | 135 |
| Innodb_buffer_pool_pages_dirty | 7 |
| Innodb_buffer_pool_pages_flushed | 340 |
| Innodb_buffer_pool_pages_free | 950 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 3 |
| Innodb_buffer_pool_pages_total | 1088 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 590670 |
| Innodb_buffer_pool_reads | 80 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 3735 |
| Innodb_data_fsyncs | 282 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 6377472 |
| Innodb_data_reads | 266 |
| Innodb_data_writes | 554 |
| Innodb_data_written | 11414016 |
| Innodb_dblwr_pages_written | 340 |
| Innodb_dblwr_writes | 48 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 464 |
| Innodb_log_writes | 119 |
| Innodb_os_log_fsyncs | 166 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 248832 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 4 |
| Innodb_pages_read | 131 |
| Innodb_pages_written | 340 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 493 |
| Innodb_rows_inserted | 58 |
| Innodb_rows_read | 243623 |
| Innodb_rows_updated | 57 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 343925 |
| Key_blocks_used | 10648 |
| Key_read_requests | 10237859 |
| Key_reads | 22097 |
| Key_write_requests | 4774 |
| Key_writes | 3587 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 14 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 115 |
| Open_streams | 0 |
| Open_tables | 69 |
| Opened_tables | 0 |
| Qcache_free_blocks | 1578 |
| Qcache_free_memory | 11973000 |
| Qcache_hits | 5687 |
| Qcache_inserts | 23447 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 384 |
| Qcache_queries_in_cache | 2766 |
| Qcache_total_blocks | 7274 |
| Questions | 59501 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 45350 |
| Table_locks_waited | 307 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 4 |
| Threads_connected | 6 |
| Threads_created | 35 |
| Threads_running | 2 |
| Uptime | 3709 |
+-----------------------------------+----------+
222 rows in set (0.02 sec)

[bjzhaoxiao]

Threads_running | 2
Connections | 5959
数据库连接没有关闭

[ffyd2000]

Connections | 5959
这个是指的当前连接数吗?
Connections

The number of connection attempts (successful or not) to the MySQL server.

[懒得去死]

你的慢查询语句不是有吗？怎么不去分析一下。