经过测试网站打开缓慢与php无直接关系

smyz · 发表于 2013-3-13 22:38:18

wdlth 发表于 2013-3-13 22:31
看来有点索引不对，发show status和表结构出来看看。

SELECT * FROM typecho_contents WHERE (`status` = 'publish' ) AND (`type` = 'post' ) ORDER BY RAND() ASC LIMIT 30

这样随机排序效率很不好　

大胡子 · 发表于 2013-3-13 22:41:14

wdlth 发表于 2013-3-13 22:31
看来有点索引不对，发show status和表结构出来看看。

| Com_purge_before_date | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 64 |
| Com_show_variables | 1 |
| 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_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 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 | 36 |
| Created_tmp_disk_tables | 48 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 151 |
| 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 | 4 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 669 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 642 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| 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 | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14497 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 2 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 46 |
| Open_streams | 0 |
| Open_table_definitions | 23 |
| Open_tables | 23 |
| Opened_files | 287 |
| Opened_table_definitions | 23 |
| Opened_tables | 99 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16768400 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 64 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 100 |
| Questions | 100 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 12 |
| 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 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 42 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
| Uptime | 19366 |
| Uptime_since_flush_status | 19366 |
+-----------------------------------+----------+
291 rows in set (0.00 sec)

复制代码

上面的复制不到。。。

大胡子 · 发表于 2013-3-13 22:42:38

smyz 发表于 2013-3-13 22:30
大胡子。。。优化下php的SQL语句。。。

应该怎么写

我汗求爆照姐姐？

smyz · 发表于 2013-3-13 22:53:29

大胡子发表于 2013-3-13 22:42
应该怎么写我汗求爆照姐姐？

有些东西并非都要塞给mysql处理啊。
有内存的话利用内存，php来处理很快很快就能响应了。

查id与标题出来用php随机，不用order by。
或是php先随机30id数再去查mysql也不用order by。

估计速度会提高N倍……

ohmyga · 发表于 2013-3-13 22:56:20

SELECT * FROM `typecho_contents`
WHERE status ='publish' AND type='post' AND id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `typecho_contents`)))
ORDER BY id LIMIT 30;

复制代码

wdlth · 发表于 2013-3-13 22:57:56

本帖最后由 wdlth 于 2013-3-13 23:00 编辑

大胡子发表于 2013-3-13 22:41
上面的复制不到。。。

SHOW INDEX FROM typecho_contents，不过我认为key_buffer_size那个设得好像太大了，还有Key_reads和Key_read_requests怎么都是0，你用InnoDB？
当然最好还是有KVDB，加个memcached的吧。

大胡子 · 发表于 2013-3-13 23:02:52

smyz 发表于 2013-3-13 22:53
有些东西并非都要塞给mysql处理啊。
有内存的话利用内存，php来处理很快很快就能响应了。

太难了搞不定。。。

大胡子 · 发表于 2013-3-13 23:05:54

wdlth 发表于 2013-3-13 22:57
SHOW INDEX FROM typecho_contents，不过我认为key_buffer_size那个设得好像太大了，还有Key_reads和Key_ ...

没有我没开InnoDB

wdlth · 发表于 2013-3-13 23:18:46

大胡子发表于 2013-3-13 23:05
没有我没开InnoDB

难道是没索引的……

imtu · 发表于 2013-3-13 23:21:03

帮顶

		自动登录	找回密码
密码			注册