mysql 数据库占cpu到100%要如何解决 如何定位那些sql语句 建立索引呢。我不是太会,请详细说明一下解决过程!谢谢!

解决方案 »

  1.   

    show processlists 看一下。
      

  2.   


    先show full processlist; 看看是哪些用户哪些sql占用了100%的资源!
      

  3.   

    看慢查询 
    show  processlist 看看哪些sql在运行
      

  4.   

    我怕慢查询影响cpu的性能。而且不能及时发现问题。 所以就没有开启。我用了show processlist 看了一下。以下是截图。请问要如何才能找到有问题的sql语句。我知道一个看不出问题。特地截取了两个图。 现在流量稍微下来了点点。稍微好点了。但是问题没有根本解决,等到流量一多,cpu还是被mysqld-nt.exe撑死了。
      

  5.   

    csdn论坛要如何添加截图附件呢。 刚刚使用, 不是很熟练。
      

  6.   

    以下是show processlist的内容。 mysql> show processlist;
    +--------+------+----------------+------+---------+------+-------+------------------+
    | Id     | User | Host           | db   | Command | Time | State | Info             |
    +--------+------+----------------+------+---------+------+-------+------------------+
    | 595688 | root | localhost:4681 | NULL | Query   |    0 | NULL  | show processlist |
    +--------+------+----------------+------+---------+------+-------+------------------+
    1 row in set (0.00 sec)mysql> show processlist;
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | Id     | User | Host           | db      | Command | Time | State                | Info
                                        |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | 595688 | root | localhost:4681 | NULL    | Query   |    0 | NULL                 | show processlist
                                        |
    | 595731 | root | localhost:4724 | ecshopn | Query   |    0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    | 595734 | root | localhost:4727 | ecshopn | Query   |    0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    3 rows in set (0.00 sec)mysql> show processlist;
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | Id     | User | Host           | db      | Command | Time | State                | Info
                                        |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | 595688 | root | localhost:4681 | NULL    | Query   |    0 | NULL                 | show processlist
                                        |
    | 595740 | root | localhost:4733 | ecshopn | Query   |    1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    2 rows in set (0.00 sec)mysql> show processlist;
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | Id     | User | Host           | db      | Command | Time | State                | Info
                                        |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | 595688 | root | localhost:4681 | NULL    | Query   |    0 | NULL                 | show processlist
                                        |
    | 595899 | root | localhost:4892 | ecshopn | Query   |    0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    2 rows in set (0.00 sec)mysql> show processlist;
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | Id     | User | Host           | db      | Command | Time | State                | Info
                                        |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | 595688 | root | localhost:4681 | NULL    | Query   |    0 | NULL                 | show processlist
                                        |
    | 595923 | root | localhost:4916 | ecshopn | Query   |    0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    | 595924 | root | localhost:4917 | ecshopn | Query   |    0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, g.goods_
    img, g.shop_price, oi.order_status, |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    3 rows in set (0.00 sec)mysql> show processlist;
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | Id     | User | Host           | db      | Command | Time | State                | Info
                                        |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    | 595688 | root | localhost:4681 | NULL    | Query   |    0 | NULL                 | show processlist
                                        |
    | 595930 | root | localhost:4923 | ecshopn | Sleep   |    0 |                      | NULL
                                        |
    | 595931 | root | localhost:4924 | ecshopn | Query   |    1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    | 595933 | root | localhost:4926 | ecshopn | Query   |    1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
    _status, SUM(og.goods_number) as go |
    +--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
    ------------------------------------+
    4 rows in set (0.00 sec)mysql>