mysql5.1 myisam表锁表严重,有好的办法吗?
本帖最后由 todayhero 于 2011-05-06 13:53 编辑
下面是某一天的三个锁表严重情况的前部分SQL语句.表类型是MYISAM.
数据库被几台集群的WEB SERVER并发访问的.
下面是某一天的三个锁表严重情况的前部分SQL语句.表类型是MYISAM.
数据库被几台集群的WEB SERVER并发访问的.
QUOTE:
| select * from jiuder_group where gid=2021 |
| select * from jiuder_group where gid=2021 |
| select * from jiuder_group where gid=2021 |
| select * from jiuder_group where gid=2021 |
| SELECT * FROM jiuder_order WHERE is_pay=1 AND uid ='14' |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| select * from jiuder_group where gid=2021 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304574477', trade_no='2011050563137053', buyer_email= |
| select * from jiuder_group where gid=2021 |
| UPDATE jiuder_group SET bought=bought+1,residue=residue-1 WHERE gid='2021' |
| SELECT * FROM jiuder_order WHERE uid='711033' AND gid=2205 AND is_pay=1 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.yijia.com', buynum='1', uid='711033', use |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT goodsnum,sussessnum,bought,sustime FROM jiuder_group WHERE gid='2141' |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| |
| select sum(buynum-return_num) from `jiuder_order` where 1 and status=4 AND is_pay=1 AND isdelete=0 A |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.yijia.com', buynum='1', uid='711033', use |
| SELECT * FROM jiuder_order WHERE uid='570781' AND gid=2135 AND is_pay=1 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid
| select * from jiuder_group where gid=2021 |
| select * from jiuder_group where gid=2021 |
| select * from jiuder_group where gid=2021 |
| SELECT * FROM jiuder_order WHERE is_pay=1 AND uid ='14' |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| select * from jiuder_group where gid=2021 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304574477', trade_no='2011050563137053', buyer_email= |
| select * from jiuder_group where gid=2021 |
| UPDATE jiuder_group SET bought=bought+1,residue=residue-1 WHERE gid='2021' |
| SELECT * FROM jiuder_order WHERE uid='711033' AND gid=2205 AND is_pay=1 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.yijia.com', buynum='1', uid='711033', use |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT goodsnum,sussessnum,bought,sustime FROM jiuder_group WHERE gid='2141' |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| |
| select sum(buynum-return_num) from `jiuder_order` where 1 and status=4 AND is_pay=1 AND isdelete=0 A |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.yijia.com', buynum='1', uid='711033', use |
| SELECT * FROM jiuder_order WHERE uid='570781' AND gid=2135 AND is_pay=1 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid
QUOTE:
| select uid from jiuder_order where gid='1915' and is_pay=1 and isworker=0 and status=1 limit 0,20 |
| select uid from jiuder_order where gid='1915' and is_pay=1 and isworker=0 and status=1 limit 0,20 |
| select * from jiuder_group where gid=1913 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.go.cn', buynum='2', uid='696143', usernam |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE gid=2109 and status=1 and is_pay=1 and isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| UPDATE jiuder_group SET bought=bought+2,residue=residue-2 WHERE gid='2189' |
| SELECT * FROM jiuder_order WHERE uid='711035' AND gid=2021 AND is_pay=1 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304574806', trade_no='2011050593412810', buyer_email= |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 and (username = '15201587609' or usermobile= |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| select uid from jiuder_order where gid='1915' and is_pay=1 and isworker=0 and status=1 limit 0,20 |
| select * from jiuder_group where gid=1913 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| select * from jiuder_group where gid=1913 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.go.cn', buynum='2', uid='696143', usernam |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT * FROM jiuder_order WHERE gid=2109 and status=1 and is_pay=1 and isworker=0 |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| INSERT INTO jiuder_order SET selectbank='', his_url='www.1000tuan.com', buynum='1', uid='711057', |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| UPDATE jiuder_group SET bought=bought+2,residue=residue-2 WHERE gid='2189' |
| SELECT * FROM jiuder_order WHERE uid='711035' AND gid=2021 AND is_pay=1 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304574806', trade_no='2011050593412810', buyer_email= |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 and (username = '15201587609' or usermobile= |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
QUOTE:
| UPDATE jiuder_order SET is_pay='1', paytime='1304579140' WHERE orderid='721217' |
| UPDATE jiuder_order SET selectbank='', buynum='20', uid='711467', username='椋樻负鐨勪汉鐢焈澶栨?|
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='393935' AND gid=1025 AND is_pay=1 |
| SELECT gid,miniurl,rsimg FROM jiuder_group WHERE cityid='29' and starttime <=1304579342 AND endtime |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='393935' AND gid=1025 AND is_pay=1 |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711093' AND gid=2109 AND is_pay=1 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304579269' WHERE orderid='721229' |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='123199' AND gid=2109 AND is_pay=1 |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711617' AND gid=2199 AND is_pay=1 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT sql_buffer_result a.gid,a.ptypeid,a.cityname,a.Delivery,a.hot_area,a.cityid,a.supplierid,a.go |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.residue,b.goodsname,b.conpshowname FROM jiuder_order a LEFT JOIN jiuder_group b ON a.gi |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711355' AND gid=2175 AND is_pay=1 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| |
| select count(*) from `jiuder_order` where 1 AND supplierid=503 AND isworker=0 AND isdelete=0 AND is |
| SELECT a.*,b.ptypeid,b.goodsname,b.goodspic,b.imgdir,b.costprice,b.discount,b.spare,b.saleprice,b.s |
| |
| SELECT a.*,b.saleprice,b.ptypeid,b.cityid,b.goodsname,b.howusecoup FROM jiuder_order a LEFT JOIN ji |
| select sum(buynum) from `jiuder_order` where 1 and status=1 AND is_pay=1 AND isdelete=0 AND gid=2107 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT a.*,b.ptypeid,b.goodsname,b.goodspic,b.imgdir,b.costprice,b.discount,b.spare,b.saleprice,b.s |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711605' AND gid=2081 AND is_pay=1 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT count(orderid) as total FROM jiuder_order WHERE uid='674665' |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| UPDATE jiuder_order SET selectbank='', buynum='20', uid='711467', username='椋樻负鐨勪汉鐢焈澶栨?|
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='393935' AND gid=1025 AND is_pay=1 |
| SELECT gid,miniurl,rsimg FROM jiuder_group WHERE cityid='29' and starttime <=1304579342 AND endtime |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='393935' AND gid=1025 AND is_pay=1 |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711093' AND gid=2109 AND is_pay=1 |
| UPDATE jiuder_order SET is_pay='1', paytime='1304579269' WHERE orderid='721229' |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='123199' AND gid=2109 AND is_pay=1 |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711617' AND gid=2199 AND is_pay=1 |
| SELECT uid,gid,isworker,orderid,isdelete,buynum,goodsname,username,usermobile,ordertime,paytime,is_p |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT sql_buffer_result a.gid,a.ptypeid,a.cityname,a.Delivery,a.hot_area,a.cityid,a.supplierid,a.go |
| SELECT * FROM jiuder_order WHERE 1 AND isworker=0 |
| SELECT a.*,b.residue,b.goodsname,b.conpshowname FROM jiuder_order a LEFT JOIN jiuder_group b ON a.gi |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711355' AND gid=2175 AND is_pay=1 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT a.*,b.gid,b.goodsname,b.miniurl,b.endtime,b.residue,b.sussessnum,b.bought,b.ptypeid FROM jiud |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| |
| select count(*) from `jiuder_order` where 1 AND supplierid=503 AND isworker=0 AND isdelete=0 AND is |
| SELECT a.*,b.ptypeid,b.goodsname,b.goodspic,b.imgdir,b.costprice,b.discount,b.spare,b.saleprice,b.s |
| |
| SELECT a.*,b.saleprice,b.ptypeid,b.cityid,b.goodsname,b.howusecoup FROM jiuder_order a LEFT JOIN ji |
| select sum(buynum) from `jiuder_order` where 1 and status=1 AND is_pay=1 AND isdelete=0 AND gid=2107 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT a.*,b.ptypeid,b.goodsname,b.goodspic,b.imgdir,b.costprice,b.discount,b.spare,b.saleprice,b.s |
| SELECT sum(buynum) as total FROM jiuder_order WHERE uid='711605' AND gid=2081 AND is_pay=1 |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
| SELECT count(orderid) as total FROM jiuder_order WHERE uid='674665' |
| SELECT SQL_BUFFER_RESULT gid,ptypeid,Delivery,cityid,goodsname,miniurl,imgdir,goodspic,thumbnail,rsi |
作者: todayhero 发布时间: 2011-05-06
本帖最后由 todayhero 于 2011-05-06 14:08 编辑
初步打算,转为INNODB表类型,innodb大并发环境下,行锁,处理能力更高.
程序上要是能开启memcached效果也会好很多.
初步打算,转为INNODB表类型,innodb大并发环境下,行锁,处理能力更高.
程序上要是能开启memcached效果也会好很多.
作者: todayhero 发布时间: 2011-05-06
select怎么会造成锁表呢？
作者: 909413335 发布时间: 2011-05-06