mycat分析.docx
- 文档编号:16115569
- 上传时间:2023-07-10
- 格式:DOCX
- 页数:28
- 大小:22.80KB
mycat分析.docx
《mycat分析.docx》由会员分享,可在线阅读,更多相关《mycat分析.docx(28页珍藏版)》请在冰点文库上搜索。
mycat分析
1结合Mycat日志,分析select*fromtravelrecordorderbyidlimit100000,100的执行过程,解释下当limitM,N中的M非常大的情况下,为什么查询结果会变慢很多
1.1mycat控制命令台显示,explain出来走了所有的节点
mysql>explainselect*fromtravelrecordorderbyidlimit100000,100;
+-----------+--------------------------------------------------------+
|DATA_NODE|SQL|
+-----------+--------------------------------------------------------+
|dn1|SELECT*FROMtravelrecordORDERBYidLIMIT0,100100|
|dn2|SELECT*FROMtravelrecordORDERBYidLIMIT0,100100|
|dn3|SELECT*FROMtravelrecordORDERBYidLIMIT0,100100|
+-----------+--------------------------------------------------------+
3rowsinset(0.01sec)
mysql>
看后台mycat.log日志:
02/1417:
52:
30.103DEBUG[$_NIOREACTOR-0-RW](ServerQueryHandler.java:
56)-ServerConnection[id=3,schema=TESTDB,host=127.0.0.1,user=test,txIsolation=3,autocommit=true,schema=TESTDB]explainselect*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
30.103DEBUG[$_NIOREACTOR-0-RW](EnchachePool.java:
76)-SQLRouteCachemisscache,key:
TESTDBselect*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
30.105DEBUG[$_NIOREACTOR-0-RW](EnchachePool.java:
59)-SQLRouteCacheaddcache,key:
TESTDBselect*fromtravelrecordorderbyidlimit1000000,100value:
select*fromtravelrecordorderbyidlimit1000000,100,route={
1->dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
2->dn2{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
3->dn3{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
}
02/1417:
52:
33.880DEBUG[$_NIOREACTOR-0-RW](ServerQueryHandler.java:
56)-ServerConnection[id=3,schema=TESTDB,host=127.0.0.1,user=test,txIsolation=3,autocommit=true,schema=TESTDB]select*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
33.881DEBUG[$_NIOREACTOR-0-RW](EnchachePool.java:
76)-SQLRouteCachemisscache,key:
TESTDBselect*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](EnchachePool.java:
59)-SQLRouteCacheaddcache,key:
TESTDBselect*fromtravelrecordorderbyidlimit1000000,100value:
select*fromtravelrecordorderbyidlimit1000000,100,route={
1->dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
2->dn2{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
3->dn3{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
}
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](NonBlockingSession.java:
113)-ServerConnection[id=3,schema=TESTDB,host=127.0.0.1,user=test,txIsolation=3,autocommit=true,schema=TESTDB]select*fromtravelrecordorderbyidlimit1000000,100,route={
1->dn1{SELECT*
FROMtravelrecord
ORDERBYid
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](NonBlockingSession.java:
113)-ServerConnection[id=3,schema=TESTDB,host=127.0.0.1,user=test,txIsolation=3,autocommit=true,schema=TESTDB]select*fromtravelrecordorderbyidlimit1000000,100,route={
1->dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
2->dn2{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
3->dn3{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100}
}rrs
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:
82)-executemutinodequeryselect*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:
97)-hasdatamergelogic
02/1417:
52:
33.882DEBUG[$_NIOREACTOR-0-RW](PhysicalDBPool.java:
452)-selectreadsourcehostM1fordataHost:
wgq_idc_mon_1_12
02/1417:
52:
33.883DEBUG[$_NIOREACTOR-0-RW](PhysicalDBPool.java:
452)-selectreadsourcehostM1fordataHost:
wgq_idc_mon_1_12
02/1417:
52:
33.883DEBUG[$_NIOREACTOR-0-RW](PhysicalDBPool.java:
452)-selectreadsourcehostM1fordataHost:
wgq_idc_mon_1_12
02/1417:
52:
33.883DEBUG[$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:
171)-receivedokresponse,executeResponse:
falsefromMySQLConnection[id=19,lastTime=1455443553868,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=false,threadId=220,charset=latin1,txIsolation=3,autocommit=true,attachment=dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@b47af37,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.884DEBUG[$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:
241)-onrowendreseponseMySQLConnection[id=25,lastTime=1455443553868,user=root,schema=db3,oldshema=db3,borrowed=true,fromSlaveDB=false,threadId=227,charset=latin1,txIsolation=3,autocommit=true,attachment=dn3{SELECT*
FROMtravelrecord
02/1417:
52:
33.883DEBUG[$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:
171)-receivedokresponse,executeResponse:
falsefromMySQLConnection[id=19,lastTime=1455443553868,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=false,threadId=220,charset=latin1,txIsolation=3,autocommit=true,attachment=dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@b47af37,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.884DEBUG[$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:
241)-onrowendreseponseMySQLConnection[id=25,lastTime=1455443553868,user=root,schema=db3,oldshema=db3,borrowed=true,fromSlaveDB=false,threadId=227,charset=latin1,txIsolation=3,autocommit=true,attachment=dn3{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.884DEBUG[$_NIOREACTOR-3-RW](DataMergeService.java:
94)-fieldmetadatainf:
[ID=ColMeta[colIndex=0,colType=8],DAYS=ColMeta[colIndex=4,colType=3],TRAVELDATE=ColMeta[colIndex=2,colType=10],USER_ID=ColMeta[colIndex=1,colType=253],FEE=ColMeta[colIndex=3,colType=246]]
02/1417:
52:
33.884DEBUG[$_NIOREACTOR-1-RW](NonBlockingSession.java:
229)-releaseconnectionMySQLConnection[id=25,lastTime=1455443553868,user=root,schema=db3,oldshema=db3,borrowed=true,fromSlaveDB=false,threadId=227,charset=latin1,txIsolation=3,autocommit=true,attachment=dn3{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.885DEBUG[$_NIOREACTOR-1-RW](PhysicalDatasource.java:
403)-releasechannelMySQLConnection[id=25,lastTime=1455443553868,user=root,schema=db3,oldshema=db3,borrowed=true,fromSlaveDB=false,threadId=227,charset=latin1,txIsolation=3,autocommit=true,attachment=null,respHandler=null,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.886DEBUG[$_NIOREACTOR-3-RW](NonBlockingSession.java:
229)-releaseconnectionMySQLConnection[id=19,lastTime=1455443553868,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=false,threadId=220,charset=latin1,txIsolation=3,autocommit=true,attachment=dn1{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@b47af37,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.886DEBUG[$_NIOREACTOR-3-RW](PhysicalDatasource.java:
403)-releasechannelMySQLConnection[id=19,lastTime=1455443553868,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=false,threadId=220,charset=latin1,txIsolation=3,autocommit=true,attachment=null,respHandler=null,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.886DEBUG[$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:
241)-onrowendreseponseMySQLConnection[id=42,lastTime=1455443553868,user=root,schema=db2,oldshema=db2,borrowed=true,fromSlaveDB=false,threadId=234,charset=latin1,txIsolation=3,autocommit=true,attachment=dn2{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.886DEBUG[$_NIOREACTOR-3-RW](NonBlockingSession.java:
229)-releaseconnectionMySQLConnection[id=42,lastTime=1455443553868,user=root,schema=db2,oldshema=db2,borrowed=true,fromSlaveDB=false,threadId=234,charset=latin1,txIsolation=3,autocommit=true,attachment=dn2{SELECT*
FROMtravelrecord
ORDERBYid
LIMIT0,1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@f35a686,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.886DEBUG[$_NIOREACTOR-3-RW](PhysicalDatasource.java:
403)-releasechannelMySQLConnection[id=42,lastTime=1455443553868,user=root,schema=db2,oldshema=db2,borrowed=true,fromSlaveDB=false,threadId=234,charset=latin1,txIsolation=3,autocommit=true,attachment=null,respHandler=null,host=110.254.11.111,port=3317,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]
02/1417:
52:
33.887DEBUG[BusinessExecutor5](DataMergeService.java:
296)-preparemppmergeresultforselect*fromtravelrecordorderbyidlimit1000000,100
02/1417:
52:
33.887DEBUG[BusinessExecutor5](MultiNodeQueryHandler.java:
324)-lastpacketid:
8
路由里面是先查limit0,1000000条,然后再查出limit0,1000100条记录,在这个基础上查出100条(preparemppmerge
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mycat 分析