需要 怎么样调整,现在的slow query 不少啊myisam 表,mysql> show global status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 610        | 
| Aborted_connects                  | 0          | 
| Binlog_cache_disk_use             | 0          | 
| Binlog_cache_use                  | 0          | 
| Bytes_received                    | 4084206342 | 
| Bytes_sent                        | 670517952  | 
| Com_admin_commands                | 336        | 
| Com_alter_db                      | 0          | 
| Com_alter_table                   | 600        | 
| Com_analyze                       | 0          | 
| Com_backup_table                  | 0          | 
| Com_begin                         | 0          | 
| Com_change_db                     | 277        | 
| Com_change_master                 | 0          | 
| Com_check                         | 0          | 
| Com_checksum                      | 0          | 
| Com_commit                        | 38811      | 
| Com_create_db                     | 0          | 
| Com_create_function               | 0          | 
| Com_create_index                  | 2          | 
| Com_create_table                  | 300        | 
| Com_dealloc_sql                   | 0          | 
| Com_delete                        | 3154       | 
| Com_delete_multi                  | 308        | 
| Com_do                            | 0          | 
| Com_drop_db                       | 0          | 
| Com_drop_function                 | 0          | 
| Com_drop_index                    | 1          | 
| Com_drop_table                    | 300        | 
| Com_drop_user                     | 0          | 
| Com_execute_sql                   | 0          | 
| Com_flush                         | 3          | 
| Com_grant                         | 0          | 
| Com_ha_close                      | 0          | 
| Com_ha_open                       | 0          | 
| Com_ha_read                       | 0          | 
| Com_help                          | 0          | 
| Com_insert                        | 1473283    | 
| Com_insert_select                 | 241        | 
| Com_kill                          | 0          | 
| Com_load                          | 0          | 
| Com_load_master_data              | 0          | 
| Com_load_master_table             | 0          | 
| Com_lock_tables                   | 417        | 
| 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                      | 113        | 
| Com_savepoint                     | 0          | 
| Com_select                        | 7251068    | 
| Com_set_option                    | 958234     | 
| Com_show_binlog_events            | 0          | 
| Com_show_binlogs                  | 1          | 
| Com_show_charsets                 | 35         | 
| Com_show_collations               | 680        | 
| Com_show_column_types             | 0          | 
| Com_show_create_db                | 0          | 
| Com_show_create_table             | 1639       | 
| Com_show_databases                | 96         | 
| Com_show_errors                   | 0          | 
| Com_show_fields                   | 2473       | 
| Com_show_grants                   | 0          | 
| Com_show_innodb_status            | 0          | 
| Com_show_keys                     | 388        | 
| 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              | 340        | 
| Com_show_slave_hosts              | 0          | 
| Com_show_slave_status             | 0          | 
| Com_show_status                   | 3050       | 
| Com_show_storage_engines          | 0          | 
| Com_show_tables                   | 224        | 
| Com_show_triggers                 | 1637       | 
| Com_show_variables                | 699        | 
| Com_show_warnings                 | 0          | 
| Com_slave_start                   | 0          | 
| Com_slave_stop                    | 0          | 
| Com_stmt_close                    | 897837     | 
| Com_stmt_execute                  | 912317     | 
| Com_stmt_fetch                    | 0          | 
| Com_stmt_prepare                  | 897837     | 
| Com_stmt_reset                    | 0          | 
| Com_stmt_send_long_data           | 0          | 
| Com_truncate                      | 0          | 
| Com_unlock_tables                 | 417        | 
| Com_update                        | 384930     | 
| 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                       | 797270     | 
| Created_tmp_disk_tables           | 3486       | 
| Created_tmp_files                 | 9          | 
| Created_tmp_tables                | 138203     | 
| Delayed_errors                    | 0          | 
| Delayed_insert_threads            | 0          | 
| Delayed_writes                    | 0          | 
| Flush_commands                    | 1          | 
| Handler_commit                    | 46311      | 
| Handler_delete                    | 285850     | 
| Handler_discover                  | 0          | 
| Handler_prepare                   | 0          | 
| Handler_read_first                | 67265      | 
| Handler_read_key                  | 26894285   | 
| Handler_read_next                 | 398816483  | 
| Handler_read_prev                 | 111644361  | 
| Handler_read_rnd                  | 530421     | 
| Handler_read_rnd_next             | 70476818   | 
| Handler_rollback                  | 10         | 
| Handler_savepoint                 | 0          | 
| Handler_savepoint_rollback        | 0          | 
| Handler_update                    | 828571     | 
| Handler_write                     | 15495286   | 
| Key_blocks_not_flushed            | 4294967291 | 
| Key_blocks_unused                 | 447202     | 
| Key_blocks_used                   | 463920     | 
| Key_read_requests                 | 100174317  | 
| Key_reads                         | 1272938    | 
| Key_write_requests                | 16085485   | 
| Key_writes                        | 3340404    | 
| Last_query_cost                   | 0.000000   | 
| Max_used_connections              | 101        | 
| Not_flushed_delayed_rows          | 0          | 
| Open_files                        | 22         | 
| Open_streams                      | 0          | 
| Open_tables                       | 64         | 
| Opened_tables                     | 6014       | 
| Questions                         | 12717693   | 
| Rpl_status                        | NULL       | 
| Select_full_join                  | 294        | 
| Select_full_range_join            | 0          | 
| Select_range                      | 36644      | 
| Select_range_check                | 10969      | 
| Select_scan                       | 24964      | 
| Slow_launch_threads               | 0          | 
| Slow_queries                      | 276889     | 
| Sort_merge_passes                 | 6          | 
| Sort_range                        | 2543       | 
| Sort_rows                         | 109214     | 
| Sort_scan                         | 1256       | 
| Table_locks_immediate             | 2428657    | 
| Table_locks_waited                | 2035       | 
| Tc_log_max_pages_used             | 0          | 
| Tc_log_page_size                  | 0          | 
| Tc_log_page_waits                 | 0          | 
| Threads_cached                    | 0          | 
| Threads_connected                 | 6          | 
| Threads_created                   | 797269     | 
| Threads_running                   | 1          | 
| Uptime                            | 1724087    | 
+-----------------------------------+------------+

解决方案 »

  1.   

    MySQL 5.0.22-log         uptime 20 5:51:37      Fri Dec 18 16:35:08 2009__ Key _________________________________________________________________
    Buffer used   453.05M of 512.00M  %Used:  88.49
      Current     108.89M            %Usage:  21.27
    Write hit      78.74%
    Read hit       98.72%__ Questions ___________________________________________________________
    Total          12.91M     7.4/s
      DMS           9.19M     5.3/s  %Total:  71.21
      Com_          3.86M     2.2/s           29.93
      -Unknown    952.02k     0.5/s            7.38
      COM_QUIT    804.34k     0.5/s            6.23
    Slow 1 s      277.01k     0.2/s            2.15  %DMS:   3.01  Log:  ON
    DMS             9.19M     5.3/s           71.21
      SELECT        7.31M     4.2/s           56.64         79.53
      INSERT        1.48M     0.8/s           11.50         16.14
      UPDATE      394.05k     0.2/s            3.05          4.29
      DELETE        3.61k     0.0/s            0.03          0.04
      REPLACE           0       0/s            0.00          0.00
    Com_            3.86M     2.2/s           29.93
      set_option  978.54k     0.6/s            7.58
      stmt_execut 952.99k     0.5/s            7.38
      stmt_close  937.84k     0.5/s            7.27__ SELECT and Sort _____________________________________________________
    Scan           25.78k     0.0/s %SELECT:   0.35
    Range          38.82k     0.0/s            0.53
    Full join         312     0.0/s            0.00
    Range check    11.72k     0.0/s            0.16
    Full rng join       0       0/s            0.00
    Sort scan       1.38k     0.0/s
    Sort range      2.70k     0.0/s
    Sort mrg pass       6     0.0/s__ Table Locks _________________________________________________________
    Waited          2.04k     0.0/s  %Total:   0.08
    Immediate       2.59M     1.5/s__ Tables ______________________________________________________________
    Open               64 of   64    %Cache: 100.00
    Opened          6.07k     0.0/s__ Connections _________________________________________________________
    Max used          101 of  100      %Max: 101.00
    Total         804.66k     0.5/s__ Created Temp ________________________________________________________
    Disk table      3.50k     0.0/s
    Table         146.60k     0.1/s    Size:  32.0M
    File                9     0.0/s__ Threads _____________________________________________________________
    Running             1 of   38
    Cached              0 of    0      %Hit:      0
    Created       804.66k     0.5/s
    Slow                0       0/s__ Aborted _____________________________________________________________
    Clients           628     0.0/s
    Connects            0       0/s__ Bytes _______________________________________________________________
    Sent          788.05M   450.5/s
    Received        4.12G    2.4k/s
      

  2.   

    用mysqlsla 查看 主要 是这条sql语句很厉害,有75.86%
    以前没调整索引大小的时候这个比例是88%,现在好像降下来一点了Count         : 14.37k  (75.86%)
    Time          : 967006 s total, 67.302756 s avg, 2 s to 22138 s max  (54.62%)
      95% of Time : 40419 s total, 2.961316 s avg, 2 s to 7 s max
    Lock Time (s) : 531915 s total, 37.02081 s avg, 0 to 20884 s max  (40.59%)
      95% of Lock : 0 total, 0 avg, 0 to 0 max
    Rows sent     : 17 avg, 0 to 20 max  (83.54%)
    Rows examined : 3.60k avg, 12 to 4.62M max  (68.03%)
    Database      : tb
    Users         : 
            [email protected]  : 100.00% (1436 of query, 93.50% (17709) of all usersQuery abstract:
    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;explain Query :
    mysql> explain select iid,title,pic_path,price from tb_goods where nick='vivizone' order by lmsn desc limit 480,20 \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: tb_goods
             type: ref
    possible_keys: nickgood
              key: nickgood
          key_len: 32
              ref: const
             rows: 806
            Extra: Using where
    1 row in set (0.00 sec)
      

  3.   

    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;需要一个 (nick,lmsn) 的复合索引。
      

  4.   

    现在的索引就是这样| tb_goods |          1 | nickgood  |            1 | nick        | A         |       54177 |       10 | NULL   |      | BTREE      | NULL    | 
    | tb_goods |          1 | nickgood  |            2 | lmsn        | A         |      328451 |     NULL | NULL   |      | BTREE      | NULL    | 
      

  5.   

    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' 速度如何SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N 速度如何
      

  6.   


    75%左右slow query 都是 "SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N"SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' 速度应该比 "SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N" 好一点吧,
      

  7.   


     SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT M,N
    这里如果M比较大的话 出现slow query的可能性 也比较大Count         : 14.91k  (74.34%)
    Time          : 968333 s total, 64.953917 s avg, 2 s to 22138 s max  (54.56%)
      95% of Time : 41532 s total, 2.932637 s avg, 2 s to 7 s max
    Lock Time (s) : 531915 s total, 35.679836 s avg, 0 to 20884 s max  (40.59%)
      95% of Lock : 0 total, 0 avg, 0 to 0 max
    Rows sent     : 17 avg, 0 to 20 max  (10.29%)
    Rows examined : 3.48k avg, 12 to 4.62M max  (60.50%)
    Database      : tb
    Users         : 
            [email protected]  : 100.00% (14908) of query, 93.70% (18791) of all usersQuery abstract:
    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;Query sample:
    select iid,title,pic_path,price from tb_goods where nick='cinderella鏉庨噾濞?
                                     order by lmsn desc  
                                            limit 100,20;
      

  8.   

    你在lmsn字段上建立 索引试试,去掉复合索引,看看速度如何
      

  9.   


    单独的索引nick,lmsn 这样吗?现在表大概有520w rows ,改起来太慢了,这样能用到2个索引?
      

  10.   


    tb_goods |          1 | lmsn      |            1 | lmsn        | A         |        3085 |     NULL | NULL   |      | BTREElmsn 上的粒度 太小了吧,520w 行 不同的才3085个
      

  11.   

    独立索引不会比复合索引快,你的表是什么存储引擎?利用一下show profiles 来看一下具体每一步骤的时间消耗。
      

  12.   

    不是,只是一个取舍,如果只有LIMIT M,N慢,那么在lmsn字段上建立 索引,速度估计要快一些
      

  13.   

    不可能的,如果是 where a=?? order by b这种情况下,单独在 b 上创建索引,可以说是根本没有用的。 
    可以利用的索引应该是 (a,b) 或者 (a)从你的执行计划上看应该是没有问题了。现在需要调整的或者是系统参数,或者是其它的结构。利用一下show profiles 来看一下具体每一步骤的时间消耗。
      

  14.   

    在nick,lmsn分开建立索引会快些,因为where语句和排序是分开使用不同的字段。
    至于改起来慢,那只是一时,如果不改是长期慢。
    慢速sql语句:
    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N; 
      

  15.   

    show profiles 我的mysql 5.0.22 不支持啊
      

  16.   

    nick,lmsn分开建 ,2个索引都能用到,不是说 mysql只能用一个索引吗? 我的mysql 版本5.0.22
      

  17.   

    在nick,lmsn分开建立索引会快些,因为where语句和排序是分开使用不同的字段。
    不可能吧。试想一下,1000 个人,10种颜色的帽子。
    现在让你把戴红帽子的人挑出来按身高排序.
    你手上有两张表格 (索引)1. 颜色, 目前队列中的位置
    2. 身高, 目前队列中的位置
      

  18.   

    SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC limit 1看一下需要多久?
      

  19.   

    limit 1 的话,很快,一般在0.01左右
      

  20.   

    分别给出以下的时间消耗。SELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC limit 1
    0.01 sSELECT SQL_NO_CACHE  iid,title,pic_path,price FROM tb_goods WHERE nick='S' 
    ??? sSELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn
    ??? s SELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;
    ??? s
      

  21.   

    select SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 0,20; 0.01s
    select SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf'  limit 0,20; 0.07sselect SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 360,20; 0.20s
      

  22.   

    在slow query中有这个
    select  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;但是 我在mysql 终端里查询 这条 花的时间久很短(0.03s),不知道为什么?
      

  23.   

    类似这样的:select  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;一般2s的比较多,我设置的slow query time 是1s
      

  24.   

    select  SQL_NO_CACHE  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;多测试几下,看一下耗时多少。
      

  25.   

    第一次运行:20 rows in set (2.67 sec)
    后面多次执行 大概在0.02~0.03 sec
      

  26.   

    我估计这里 第一次去读索引了,所以可能导致速度慢MySQL 5.0.22-log         uptime 23 6:48:47      Mon Dec 21 17:32:18 2009__ Key _________________________________________________________________
    Buffer used   453.05M of 512.00M  %Used:  88.49
      Current      73.78M            %Usage:  14.41
    Write hit      75.43%
    Read hit       98.63%
    用mysqlreport 看 当前的key_buffer_size 只有73.78MB,太少了,我设置的是512MB
      

  27.   

    内存有多大?
    感觉要调整table_cache和key_buffer_size的大小吧转:(http://www.askwan.com/post/4/)
    table_cache指示表高速缓存的大小。当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容。一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值,即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个值的大小了。 在mysql默认安装情况下,table_cache的值在2G内存以下的机器中的值默认时256到512,如果机器有4G内存,则默认这个值是2048,但这决意味着机器内存越大,这个值应该越大,因为table_cache加大后,使得mysql对SQL响应的速度更快了,不可避免的会产生更多的死锁(dead lock),这样反而使得数据库整个一套操作慢了下来,严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断,找到最适合你维护的库的table_cache值,有人说:“性能优化是一门艺术”,这话一点没错。大凡艺术品,大都是经过千锤百炼,精雕细琢而成。
      

  28.   

    内存2G,server上还有其他东西 apache,tomcat,java ...
    table_cache 我没调整 现在还是64,不知道这个参数效果怎么样
      

  29.   

    检查一下你的key_buffer_size,table_open_cache 设置是多少?
      

  30.   


    key_buffer_size:512MB
    table_cache:256