{"id":2210,"date":"2013-12-04T13:36:40","date_gmt":"2013-12-04T05:36:40","guid":{"rendered":"http:\/\/kuki.idv.tw\/?p=2210"},"modified":"2013-12-04T13:36:40","modified_gmt":"2013-12-04T05:36:40","slug":"mysql-%e8%b3%87%e6%96%99%e5%ba%ab%e5%84%b2%e5%ad%98%e5%bc%95%e6%93%8e","status":"publish","type":"post","link":"https:\/\/www.kuki.idv.tw\/?p=2210","title":{"rendered":"MySQL \u8cc7\u6599\u5eab\u5132\u5b58\u5f15\u64ce"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>\u9019\u7bc7\u6587\u7ae0\u4e3b\u8981\u8a0e\u8ad6 Memory, MyISAM, InnoDB \u4e09\u7a2e\u5132\u5b58\u5f15\u64ce<\/p>\n<table>\n<tbody>\n<tr>\n<th>\u9805\u76ee<\/th>\n<th>MyISAM<\/th>\n<th>InnoDB<\/th>\n<th>Memory<\/th>\n<\/tr>\n<tr>\n<td>\u7a7a\u9593\u9650\u5236<\/td>\n<td>\u7121<\/td>\n<td>64TB<\/td>\n<td>\u8a18\u61b6\u9ad4<\/td>\n<\/tr>\n<tr>\n<td>transaction<\/td>\n<td>x<\/td>\n<td>\u6709<\/td>\n<td>x<\/td>\n<\/tr>\n<tr>\n<td>\u5927\u91cf Insert \u901f\u5ea6<\/td>\n<td>\u9ad8<\/td>\n<td>\u4f4e<\/td>\n<td>\u9ad8<\/td>\n<\/tr>\n<tr>\n<td>\u8a2d\u7f6e\u5916\u4f86\u9375<\/td>\n<td>x<\/td>\n<td>\u6709<\/td>\n<td>x<\/td>\n<\/tr>\n<tr>\n<td>\u9396\u5b9a\u5c64\u7d1a<\/td>\n<td>\u8cc7\u6599\u8868<\/td>\n<td>\u8cc7\u6599\u5217<\/td>\n<td>\u8cc7\u6599\u8868<\/td>\n<\/tr>\n<tr>\n<td>\u4e8c\u5143\u6a39\u7d22\u5f15<\/td>\n<td>\u6709<\/td>\n<td>\u6709<\/td>\n<td>\u4e0d\u77e5<\/td>\n<\/tr>\n<tr>\n<td>\u96dc\u6e4a\u7d22\u5f15<\/td>\n<td>x<\/td>\n<td>\u6709<\/td>\n<td>\u6709<\/td>\n<\/tr>\n<tr>\n<td>\u5168\u6587\u641c\u5c0b\u7d22\u5f15<\/td>\n<td>\u6709<\/td>\n<td>x<\/td>\n<td>x<\/td>\n<\/tr>\n<tr>\n<td>\u8cc7\u6599\u58d3\u7e2e<\/td>\n<td>\u6709<\/td>\n<td>x<\/td>\n<td>x<\/td>\n<\/tr>\n<tr>\n<td>\u8cc7\u6599\u5feb\u53d6<\/td>\n<td>x<\/td>\n<td>\u6709<\/td>\n<td>\u6709<\/td>\n<\/tr>\n<tr>\n<td>\u7d22\u5f15\u5feb\u53d6<\/td>\n<td>\u6709<\/td>\n<td>\u6709<\/td>\n<td>\u6709<\/td>\n<\/tr>\n<tr>\n<td>\u8a18\u61b6\u9ad4\u4f54\u7528<\/td>\n<td>\u4f4e<\/td>\n<td>\u9ad8<\/td>\n<td>\u4e2d<\/td>\n<\/tr>\n<tr>\n<td>\u78c1\u789f\u4f54\u7528<\/td>\n<td>\u4f4e<\/td>\n<td>\u9ad8<\/td>\n<td>x<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5c0d\u65bc MyISAM \u4f86\u8aaa\uff0c\u6700\u5927\u7684\u597d\u8655\u662f\u6210\u672c\u4f4e\uff0c\u800c\u4e14\u53ef\u4ee5 create views \uff0c\u9019\u662f\u5176\u4ed6\u5132\u5b58\u5f15\u64ce\u8fa6\u4e0d\u5230\u7684\uff0c\u4f46\u7f3a\u9ede\u5c31\u662f\u9396\u5b9a\u5c64\u7d1a\u4ee5 table \u70ba\u55ae\u4f4d\uff0c\u800c\u4e14\u4e0d\u652f\u63f4 transaction \uff0c\u9019\u4e9b\u5730\u65b9\u8f38\u7d66 InnoDB \u3002\u4e0d\u904e InnoDB \u4e5f\u662f\u6709\u7f3a\u9ede\u50cf\u662f\u4e0d\u652f\u63f4 FULLTEXT \u7684\u7d22\u5f15\uff0c\u4e14\u8a18\u61b6\u9ad4\u4f54\u7528\u591a\u3001\u78c1\u789f\u7a7a\u9593\u8017\u7528\u5927\u2026\u7b49\u7b49\u3002<\/p>\n<p>\u6211\u627e\u5230\u4e00\u7bc7\u6587\u7ae0\u91dd\u5c0d MyISAM, InnoDB \u548c Falcon \u4f86\u505a\u6bd4\u8f03\uff0c\u5728\u9019\u88e1\u9762 MyISAM\u548c InnoDB \u8868\u73fe\u90fd\u6c92\u6709\u5dee\u5f88\u591a\uff0c\u552f\u7368\u5728\u6e2c READ_PK_RANGE \u548c READ_KEY_POINT \u6642\u5019\uff0c MyISAM \u721b\u6389\u4e86(\u4e0d\u904e\u4e3b\u89d2\u5176\u5be6\u662f Falcon \u56e0\u70ba\u5b83\u88ab\u6253\u8db4\u4e86)\u3002\u539f\u56e0\u662f\uff1aThere MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system call MyISAM uses to access data and retrieving from OS cache is not scaled.<br \/>\n<a href=\"http:\/\/www.mysqlperformanceblog.com\/2007\/01\/08\/innodb-vs-myisam-vs-falcon-benchmarks-part-1\/\">InnoDB vs MyISAM vs Falcon benchmarks \u2013 part 1<\/a><\/p>\n<p>\u800c Memory \u5132\u5b58\u5f15\u64ce\u7684\u6700\u5927\u512a\u9ede\u5c31\u662f\u5feb\u3001\u5feb\u3001\u5f88\u5feb\u3001\u4e0d\u6703\u5c0d\u786c\u789f\u983b\u7e41\u8b80\u5beb\u3001\u4e26\u4e14\u7528 HASH \u96dc\u6e4a\u7d22\u5f15(\u4f46\u4e0d\u66c9\u5f97\u6709\u6c92\u6709 Btree \u4e8c\u5143\u6a39\u7d22\u5f15)\uff01\u53e6\u5916\u5b83\u6709\u500b\u7279\u6027\uff0c\u5c31\u662f\u6703\u5728\u786c\u789f\u5efa\u7acb\u4e00\u500b .frm \u6a94\uff0c\u76ee\u7684\u662f\u70ba\u4e86\u5b58\u8cc7\u6599\u8868\u7684 scheme \uff0c\u4f46\u662f\u6bcf\u4e00\u7b46 record \u9084\u662f\u5132\u5b58\u5728\u8a18\u61b6\u9ad4\u4e2d\uff0c\u9019\u4e5f\u610f\u5473\u8457\u5982\u679c\u65b7\u96fb\u6216\u662f\u95dc\u6a5f\uff0c\u8cc7\u6599\u5c31\u6703\u6d88\u5931\u4e0d\u898b\u3002<\/p>\n<p>\u5be6\u969b\u61c9\u7528\uff1a<\/p>\n<p>\u5047\u8a2d\u6211\u6709\u5169\u500b\u4e0d\u540c\u985e\u578b\u7684 Table \uff0c\u5206\u5225\u5132\u5b58 App Data \u548c Session \uff0c\u6211\u6709\u5927\u91cf\u9023\u7dda\uff0c\u5f9e\u4f3a\u670d\u5668\u4e0a\u7684\u7d00\u9304\u770b\u4f86\uff0c\u958b\u6a5f\u534a\u5929\u5de6\u53f3\uff0c\u7e3d\u5171\u8655\u7406\u8fd1\u4e5d\u767e\u842c\u500b\u9023\u7dda(\u9019\u662f\u5be6\u969b\u6578\u64da)\u3002<\/p>\n<table>\n<tbody>\n<tr>\n<th colspan=\"4\">\u7cfb\u7d71\u958b\u6a5f\u81f3\u73fe\u5728\u5171\u9032\u884c 8,944,853 \u6b21\u67e5\u8a62<\/th>\n<\/tr>\n<tr>\n<td>\u7e3d\u5171<\/td>\n<td>\u6bcf\u5c0f\u6642<\/td>\n<td>\u6bcf\u5206<\/td>\n<td>\u6bcf\u79d2<\/td>\n<\/tr>\n<tr>\n<td>8,945 k<\/td>\n<td>806.51 k<\/td>\n<td>12.73 k<\/td>\n<td>224.19<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u800c App Data \u7684\u8cc7\u6599\u8868\u4f5c\u7528\u548c Session \u8cc7\u6599\u8868\u500b\u4f5c\u7528\u5206\u5225\u5982\u4e0b\uff1a<\/p>\n<p>\u6bcf\u500b App \u555f\u52d5\u6642\uff0c\u90fd\u6703\u6709\u4e00\u500b Session ID\uff0c\u800c\u6bcf\u7b46 Session \u90fd\u88ab\u7576\u6210\u4e00\u7b46 Record Insert \u5230 Table \u4e2d\u505a\u7d00\u9304\uff0c\u7576 Session \u8d77\u59cb\/\u7d50\u675f\u7684\u6642\u5019\uff0c\u624d\u628a\u66f4\u65b0\u7684\u8cc7\u6599\u5beb\u5230 App Data \u4e2d\u3002<\/p>\n<p>\u5c0d\u65bc Session \u500b\u8cc7\u6599\u8868\u7684\u8655\u7406\uff0c\u6211\u63a1\u7528 Memory \u70ba\u5132\u5b58\u5f15\u64ce\uff0c\u56e0\u70ba Session \u6389\u4e86\u4e26\u4e0d\u53ef\u60dc\uff0c\u4f46\u537b\u53ef\u4ee5\u63db\u4f86\u6975\u4f73\u7684\u6548\u7387\uff0c\u800c App Data \u7684\u8cc7\u6599\u8868\uff0c\u6211\u5247\u662f\u63a1\u7528 InnoDB \uff0c\u96d6\u7136\u76f8\u8f03\u65bc MyISAM \u6703\u82b1\u4e0a\u66f4\u591a\u7684 Cost \u800c\u4e14\u6548\u7387\u8f03\u5dee\uff0c\u4f46\u662f\u4ed6\u63d0\u4f9b\u5f88\u597d\u7684\u9396\u5b9a(\u4ee5row\u70ba\u55ae\u4f4d)\u4ee5\u53ca\u5b89\u5168\u7684\u5fa9\u539f\u6a5f\u5236\uff0c\u53e6\u5916\u4e5f\u652f\u63f4\u5916\u4f86\u9375\u7684\u8a2d\u5b9a\u3002<\/p>\n<p>\u63a8\u85a6\u95b1\u8b80\u6587\u7ae0\uff1a<br \/>\n<a href=\"http:\/\/dev.mysql.com\/tech-resources\/articles\/storage-engine\/part_3.html\">MySQL Storage Engine Architecture, Part 3: Details and Comparison<\/a><br \/>\n<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/memory-storage-engine.html\">13.4. The MEMORY (HEAP) Storage Engine<\/a><br \/>\n<a href=\"http:\/\/www.softwareprojects.com\/resources\/programming\/t-mysql-storage-engines-1470.html\">MySQL Storage Engines<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; \u9019\u7bc7\u6587\u7ae0\u4e3b\u8981\u8a0e\u8ad6 Memory, MyISAM, InnoDB \u4e09\u7a2e\u5132\u5b58\u5f15\u64ce \u9805\u76ee MyISAM &hellip; <a href=\"https:\/\/www.kuki.idv.tw\/?p=2210\" class=\"more-link\">\u95b1\u8b80\u5168\u6587<span class=\"screen-reader-text\">\u3008MySQL \u8cc7\u6599\u5eab\u5132\u5b58\u5f15\u64ce\u3009<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2210"}],"collection":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2210"}],"version-history":[{"count":1,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2210\/revisions"}],"predecessor-version":[{"id":2211,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2210\/revisions\/2211"}],"wp:attachment":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}