MySQL性能跟踪语句教程.doc

MySQL性能跟踪语句教程 PHP程序开发快，运行快，技术本身学习快。嵌入于HTML：因为PHP可以被嵌入于HTML语言，它相对于其他语言。简单，实用性强，更适合初学者。以下是为大家搜索MySQL性能跟踪语句教程，希望能给大家带来帮助!更多精彩内容请及时关注我们! 1、不过版本要在5.0.37之后 手册上介绍：(SHOWPROFILESandSHOWPROFILEwereaddedinMySQL5.0.37.) Important PleasenotethattheSHOWPROFILEandSHOWPROFILESfunctionalityispartoftheMySQL5.0CommunityServeronly. 2、变量profiling是用户变量，每次都得重新启用。 以下是我做的一些实验。数据很明显，就不多解释了。 mysqlusetest Databasechanged mysqlsetprofiling=1; QueryOK,0rowsaffected(0.00sec) mysqlshowtables; +-+ |Tables_in_test| +-+ |bag_item| |bag_user| |score| |t| +-+ 4rowsinset(0.03sec) mysqlselectcount(*)fromt; +-+ |count(*)| +-+ |2097152| +-+ 1rowinset(0.74sec) mysqlshowprofiles; +-+-+-+ |Query_ID|Duration|Query| +-+-+-+ |1|0.02717000|showtables| |2|0.74770100|selectcount(*)fromt| +-+-+-+ 2rowsinset(0.00sec) mysqlshowprofileforquery2; +-+-+ |Status|Duration| +-+-+ |(initialization)|0.000004| |checkingquerycacheforquery|0.000044| |Openingtables|0.000012| |Systemlock|0.000017| |Tablelock|0.00003| |init|0.000013| |optimizing|0.000008| |statistics|0.000013| |preparing|0.000011| |executing|0.000006| |Sendingdata|0.747313| |end|0.000014| |queryend|0.000006| |storingresultinquerycache|0.000006| |freeingitems|0.000012| |closingtables|0.000009| |loggingslowquery|0.000183| +-+-+ 17rowsinset(0.00sec) mysqlshowprofileblockio,cpuforquery2; +-+-+-+-+-+-+ |Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out| +-+-+-+-+-+-+ |(initialization)|0.000004|0|0|0|0| |checkingquerycacheforquery|0.000044|0|0|0|0| |Openingtables|0.000012|0|0|0|0| |Systemlock|0.000017|0|0|0|0| |Tablelock|0.00003|0|0|0|0| |init|0.000013|0|0|0|0| |optimizing|0.000008|0|0|0|0| |statistics|0.000013|0|0|0|0| |preparing|0.000011|0|0|0|0| |executing|0.000006|0|0|0|0| |Sendingdata|0.747313|0.746887|0|0|0| |end|0.000014|0|0|0|0| |queryend|0.000006|0|0|0|0| |storingresultinquerycache|0.000006|0|0|0|0| |freeingitems|0.000012|0|0|0|0| |closingtables|0.000009|0|0|0|0| |loggingslowquery|0.000183|0|0|0|0| +-+-+-+-+-+-+ 17rowsinset(0.00sec) mysqlinsertintot(username)selectusernamefromt; QueryOK,2097152rowsaffected(34.17sec) Records:2097152Duplicates:0Warnings:0 mysqlshowprofiles; +-+-+-+ |Query_ID|Duration|Query| +-+-+-+ |1|0.02717000|showtables| |2|0.74770100|selectcount(*)fromt| |3|0.00004200|showprifileforquery2| |4|34.30410100|insertintot(username)selectusernamefromt| +-+-+-+ 4rowsinset(0.00sec) mysqlshowprofilecpu,blockio,memory,swapsforquery4; +-+-+-+-+-+-+-+ |Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|Swaps| +-+-+-+-+-+-+-+ |(initialization)|0.000038|0|0|0|0|0| |checkingpermissions|0.000016|0|0|0|0|0| |Openingtables|0.000014|0|0|0|0|0| |Systemlock|0.000007|0|0|0|0|0| |Tablelock|0.000013|0|0|0|0|0| |init|0.000015|0|0|0|0|0| |optimizing|0.000006|0|0|0|0|0| |statistics|0.000012|0|0|0|0|0| |preparing|0.000011|0|0|0|0|0| |Creatingtmptable|0.000029|0|0|0|0|0| |executing|0.000005|0|0|0|0|0| |Copyingtotmptable|1.262877|1.24981|0.012998|0|0|0| |convertingHEAPtoMyISAM|0.384814|0.187971|0.19797|0|0|0| |Copyingtotmptableondisk|1.417069|1.203817|0.191971|0|0|0| |Sendingdata|31.104185|13.965877|0.843872|0|0|0| |end|0.000017|0|0|0|0|0| |removingtmptable|0.134872|0|0.029995|0|0|0| |end|0.000026|0|0|0|0|0| |queryend|0.000006|0|0|0|0|0| |freeingitems|0.000011|0|0|0|0|0| |closingtables|0.000009|0|0|0|0|0| |loggingslowquery|0.000049|0|0|0|0|0| +-+-+-+-+-+-+-+ 22rowsinset(0.00sec) mysqlselectcount(*)fromt; +-+ |count(*)| +-+ |4194304| +-+ 1rowinset(1.51sec) mysqlshowprofiles; +-+-+-+ |Query_ID|Duration|Query| +-+-+-+ |1|0.02717000|showtables| |2|0.7