{"id":1876,"date":"2020-07-01T07:35:55","date_gmt":"2020-06-30T23:35:55","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1876"},"modified":"2020-07-01T07:35:55","modified_gmt":"2020-06-30T23:35:55","slug":"mysql-8-0-%e5%a4%9a%e7%ba%bf%e7%a8%8b-load-data-infile","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1876","title":{"rendered":"MySQL 8.0 \u591a\u7ebf\u7a0b load data infile"},"content":{"rendered":"<div>\n<div>\n<div>\n<div>load data infile \u5bfc\u5165\u4e00\u4e2a\u5927\u7684\u6587\u4ef6\u5230MySQL\u8868\u4e2d\uff0c\u901a\u5e38\u662f\u4e00\u4e2a\u975e\u5e38\u8017\u65f6\u7684\u8fc7\u7a0b\uff0c\u56e0\u4e3a\u5904\u7406\u8fc7\u7a0b\u662f\u5355\u7ebf\u7a0b\u5e76\u4e14\u662f\u5355\u4e8b\u52a1\u7684\uff0c\u800c\u76ee\u524d\u7684\u786c\u4ef6\u6761\u4ef6\u5728\u6267\u884cload data infile\u65f6\uff0c\u8fdc\u8fdc\u6ca1\u6709\u8fbe\u5230\u74f6\u9888\u3002\u5728MySQL 8.0.17\u7248\u672c\uff0c\u5f15\u5165\u4e86\u4e00\u4e2a\u591a\u7ebf\u7a0bload data infile\u7684\u529f\u80fd\uff0c\u8be5\u529f\u80fd\u5b9e\u73b0\u5728MySQL Shell\u5de5\u5177\u4e2d\uff0c\u56e0\u6b64\u9700\u8981\u5148\u5b89\u88c5MySQL Shell\u3002\u4e0b\u9762\u6765\u5bf9\u6bd4\u4e00\u4e0b\u4f20\u7edf\u5355\u7ebf\u7a0bload data infile\u4e0e\u591a\u7ebf\u7a0bload data infile\u4e24\u79cd\u65b9\u5f0f\u7684\u6027\u80fd\u5dee\u5f02\u6709\u591a\u5927\u3002<\/div>\n<\/div>\n<\/div>\n<\/div>\n<article>\n<h4>\u6d4b\u8bd5\u73af\u5883\uff1a<\/h4>\n<ul>\n<li>4 core CPU<\/li>\n<li>8 GB RAM<\/li>\n<li>SSD Disk<\/li>\n<li>Centos 7<\/li>\n<\/ul>\n<p>\u5bfc\u5165\u7684\u6587\u4ef6load_data.csv\uff0c\u5927\u7ea68G\u5de6\u53f3\u3002<\/p>\n<h4>\u5355\u7ebf\u7a0b\u6d4b\u8bd5\uff1a<\/h4>\n<div>\n<pre>mysql &gt; load data infile \\'\/usr\/local\/mysql\/mysql-files\/load_data.csv\\'\ninto table single.single_load fields terminated by \\',\\' optionally\nenclosed by \\'\\\"\\' lines terminated by \\'\\\\n\\'\n(user_id,visible,name,size,latitude,longitude,timestamp,public,description,inserted);\nQuery OK, 14244516 rows affected, 0 warnings (39 min 35.5036 sec)\nRecords: 14244516 Deleted: 0 Skipped: 0 Warnings: 0\n<\/pre>\n<\/div>\n<p>\u4e00\u5171\u8017\u65f6 39 min 35.5036 sec\u3002<\/p>\n<h4>\u591a\u7ebf\u7a0b\u6d4b\u8bd5\uff1a<\/h4>\n<div>\n<pre>mysql shell &gt; util.importTable(\\\"\/usr\/local\/mysql\/mysql-files\/load_data.csv\\\",\n{schema: \\\"parallel\\\", table: \\\"parallel_load\\\", columns:\n[\\\"user_id\\\",\\\"visible\\\",\\\"name\\\",\\\"size\\\",\\\"latitude\\\",\\\"longitude\\\",\\\"timestamp\\\",\\\"public\\\",\\\"description\\\"\n,\\\"inserted\\\"], dialect: \\\"csv-unix\\\", skipRows: 0, showProgress: true,\nfieldsOptionallyEnclosed: true, fieldsTerminatedBy: \\\",\\\", linesTerminatedBy:\n\\\"\\\\n\\\",fieldsEnclosedBy: \\'\\\"\\',threads: 8, bytesPerChunk: \\\"1G\\\", maxRate: \\\"2G\\\"})\nImporting from file \\'\/usr\/local\/mysql\/mysql-files\/load_data.csv\\' to table\n`parallel`.`parallel_load` in MySQL Server at \/tmp%2Fmysql.sock using 8 threads\n[Worker01] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker02] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker03] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker04] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker05] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker06] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker07] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n[Worker08] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0\n100% (8.06 GB \/ 8.06 GB), 535.17 KB\/s\nFile \\'\/usr\/local\/mysql\/mysql-files\/load_data.csv\\' (8.06 GB) was imported in 6 min\n30.0411 sec at 18.81 MB\/s\nTotal rows affected in parallel.parallel_load: Records: 14244516 Deleted: 0 Skipped: 0\nWarnings: 0\n<\/pre>\n<\/div>\n<p>\u4e00\u5171\u8017\u65f6\uff1a6 min 30.0411 sec<\/p>\n<h4>\u7ed3\u679c\u5bf9\u6bd4\uff1a<\/h4>\n<p>\u4ece\u6d4b\u8bd5\u7ed3\u679c\u4e0a\u770b\uff0c\u5355\u7ebf\u7a0b\u8017\u65f6 39 min 35.5036 sec\uff0c\u591a\u7ebf\u7a0b\u8017\u65f66 min 30.0411 sec\uff0c\u591a\u7ebf\u7a0b\u6548\u7387\u5927\u7ea6\u662f\u5355\u7ebf\u7a0b\u76846\u500d\u3002<\/p>\n<h4>\u591a\u7ebf\u7a0bload data infile\u7684\u51e0\u4e2a\u53c2\u6570\uff1a<\/h4>\n<ul>\n<li>threads\uff0c\u6307\u5b9a\u7ebf\u7a0b\u6570<\/li>\n<li>bytesPerChunk\uff0c\u6bcf\u4e2achunk\u7684\u5927\u5c0f<\/li>\n<li>maxRate\uff0c\u7ebf\u7a0b\u6bcf\u79d2\u80fd\u591f\u5904\u7406\u7684\u6570\u636e\u6700\u5927\u9650\u5236<\/li>\n<\/ul>\n<h4>\u591a\u7ebf\u7a0bload data infile\u5185\u90e8\u5904\u7406\u8fc7\u7a0b\uff1a<\/h4>\n<ul>\n<li>\u5206\u6790\u8981\u5bfc\u5165\u7684\u6570\u636e\u6587\u4ef6<\/li>\n<li>\u5c06\u5927\u7684\u6570\u636e\u6587\u4ef6\u5206\u5272\u6210\u6570\u636e\u5757(chunk)<\/li>\n<li>\u591a\u7ebf\u7a0b\u5c06\u6570\u636e\u5757\u5bfc\u5165\u5230MySQL\u4e2d<\/li>\n<\/ul>\n<\/article>\n","protected":false},"excerpt":{"rendered":"<p>load data infile \u5bfc\u5165\u4e00\u4e2a\u5927\u7684\u6587\u4ef6\u5230MySQL\u8868\u4e2d\uff0c\u901a\u5e38\u662f\u4e00\u4e2a\u975e\u5e38\u8017\u65f6\u7684\u8fc7\u7a0b\uff0c\u56e0\u4e3a\u5904\u7406\u8fc7\u7a0b\u662f\u5355 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=1876\"> <span class=\"screen-reader-text\">MySQL 8.0 \u591a\u7ebf\u7a0b load data infile<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"load data infile \u5bfc\u5165\u4e00\u4e2a\u5927\u7684\u6587\u4ef6\u5230MySQL\u8868\u4e2d\uff0c\u901a\u5e38\u662f\u4e00\u4e2a\u975e\u5e38\u8017\u65f6\u7684\u8fc7\u7a0b\uff0c\u56e0\u4e3a\u5904\u7406\u8fc7\u7a0b\u662f\u5355&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1876"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1876"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1876\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1876"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1876"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1876"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}