大量数据快速导入mysql的方法

请使用 MySQL8.0 以下,否则 MyISAM 引擎不支持分区,如果用 Innodb 引擎,导入速度和建索引速度将大打折扣,我使用的是 MySQL5.7。

下载 mysql shell 工具
mysql shell 下载地址: https://downloads.mysql.com/archives/shell/ 

修改配置文件

my.ini 配置文件添加以下两行


[mysqld]
secure_file_priv=''
local_infile=on

同时如果原先已有以下两行,请先注释掉(关闭二进制日志,加快导入速度)

#binlog_format=mixed
#log-bin=binlog

建立数据库和表 

#建立数据库
CREATE DATABASE kuzi;

use kuzi;

#建 weibo 表,分 16 个区
CREATE TABLE `weibos`  (
`phone` BIGINT(20) DEFAULT NULL,`uid` BIGINT(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ASCII PARTITION BY HASH(uid) PARTITIONS 16;

#建 qq 表,分 26 个区
CREATE TABLE `qqs`  (
`qq` BIGINT(20) DEFAULT NULL,`phone` BIGINT(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ASCII PARTITION BY HASH(qq) PARTITIONS 26;

运行 mysqlsh 导入数据

cmd 命令行进入 mysql shell 的 bin 目录运行  mysqlsh.exe,接着输入如下命令

#连接数据库,会提示输入密码
\c root@127.0.0.1:3306

#选择刚刚建的库
\use kuzi

#导入 WB 数据,把 D:/0/weibo_2019_5e.txt 修改成你的文件路径
util.importTable("D:/0/weibo_2019_5e.txt",{"schema":"kuzi","table":"weibos","fieldsTerminatedBy":"\t","showProgress":true,"threads":8,bytesPerChunk: "1G",maxRate: "2G"});

#上面执行完后,再导入 QQ 数据
util.importTable("D:/0/qq_6.9_8e_update.txt",{"schema":"kuzi","table":"qqs","fieldsTerminatedBy":"----","showProgress":true,"threads":8,bytesPerChunk: "1G",maxRate: "2G"});

该方法导入应该是最快的了,我笔记本机械硬盘导入两个数据,WB 用了 5 分钟,QQ 用了 7 分钟

建立索引

use kuzi;

# WB 的索引
ALTER TABLE `kuzi`.`weibos`  ADD INDEX uid_phone (uid,phone);

# QQ 的索引
ALTER TABLE `kuzi`.`qqs`  ADD INDEX qq_phone (qq,phone);

这一步就比较久了,WB耗时 45 分钟,QQ 耗时 90 分钟

© 版权声明
THE END
喜欢就支持以下吧
点赞0
分享
评论 共41条
  • wangjianjilei

    昵称

  • 取消
    昵称
    • wangjianjilei
    • joker0
      Hello, just wanted to tell you, I liked this blog post. Tedda Poul Rockie
      6月前回复
    • wangjianjilei
    • erotik izle0
      Thank you for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again. Athena Pail Kayle
      6月前回复
    • wangjianjilei
    • porno0
      Hello Dear, are you genuinely visiting this site regularly, if so afterward you will without doubt obtain fastidious know-how. Marcille Germayne Kilk
      6月前回复
    • wangjianjilei
    • erotik0
      Terrific article! That is the type of info that are meant to be shared around the internet. Judi Huntley Virendra
      6月前回复
    • wangjianjilei
    • sikis0
      I think this is a real great blog. Really looking forward to read more. Fantastic. Shena Mallory Britton
      6月前回复
    • wangjianjilei
    • sikis0
      Merely wanna input that you have a very decent web site , I the design and style it actually stands out. Roxane Paulo Blockus
      6月前回复
    • wangjianjilei
    • sikis0
      Hi there, I enjoy reading through your post. I like to write a little comment to support you. Aline Linus Emanuel
      6月前回复
    • wangjianjilei
    • erotik0
      Some genuinely nice and utilitarian information on this internet site, also I conceive the style and design has superb features. Myriam Neel Winsor
      6月前回复
    • wangjianjilei
    • sikis0
      Hi, after reading this awesome post i am as well delighted to share my knowledge here with colleagues. Corry Morrie Ludovick
      6月前回复
    • wangjianjilei
    • sikis0
      Im thankful for the blog post. Much thanks again. Want more. Cathlene Clay Foy
      6月前回复
    • wangjianjilei
    • sikis0
      Really appreciate you sharing this article. Much thanks again. Fantastic. Mable Gustavo Redvers
      6月前回复
    • wangjianjilei
    • sikis0
      This is my first time visit at here and i am really happy to read everthing at one place. Marnie Phillipp Bussy
      6月前回复
    • wangjianjilei
    • web-dl0
      Heya i am for the first time here. I found this board and I to find It truly useful & it helped me out a lot. Kelcey Arturo Jacy
      6月前回复
    • wangjianjilei
    • torrent0
      Amazing! Its actually remarkable paragraph, I have got much clear idea concerning from this piece of writing. Ursulina Salem Lachish
      6月前回复
    • wangjianjilei
    • ucretsiz0
      I like this post, enjoyed this one appreciate it for putting up. Carlynn Byrann Litt
      6月前回复
    • wangjianjilei
    • bedava0
      Buenas noches agradezco por compartir este ritual, tengo una duda respecto a que debo hacer con la carta gracias. Bellina Gardie Schnurr
      6月前回复
    • wangjianjilei
    • torrent0
      You made some decent points there. I looked on the internet for the subject matter and found most persons will approve with your site. Deidre Steve Casandra
      6月前回复
    • wangjianjilei
    • bluray0
      Fine way of describing, and nice piece of writing to take information regarding my presentation subject, which i am going to present in school. Joscelin Neall Stefanie
      6月前回复
    • wangjianjilei
    • bluray0
      What a data of un-ambiguity and preserveness of precious familiarity concerning unpredicted feelings. Georgianna Devy Grassi
      6月前回复
    • wangjianjilei
    • download0
      I got what you intend,saved to bookmarks, very nice web site. Dyanne Den Malissa
      6月前回复
    • wangjianjilei
    • torrent0
      I really like it when people get together and share views. Great site, keep it up. Nicolette Fransisco Vaenfila
      6月前回复
    • wangjianjilei
    • ucretsiz0
      You made some good points there. I looked on the internet for the issue and found most individuals will agree with your website. Marleah Pail Simeon
      6月前回复
    • wangjianjilei
    • ucretsiz0
      I blog frequently and I genuinely thank you for your content. Bobby Cheston Doig
      6月前回复
    • wangjianjilei
    • download0
      Using chalk, sketch out the image of a ladder, then, starting at the bottom, step in and step out from each square until you have made it to the top. Loutitia Yorgo Mannuela
      6月前回复
    • wangjianjilei
    • bedava0
      Well I really liked reading it. This article procured by you is very useful for good planning. Fernandina Gaby Donnell
      4月前回复
    • wangjianjilei
    • yenilmezler0
      Well I really enjoyed reading it. This information offered by you is very constructive for good planning. Jacquelin Hayden Burnside
      4月前回复
    • wangjianjilei
    • netflix0
      I am another one that is planning OAS at 65 and CPP at 70. Prefer to have a larger amount that is fixed amount with some indexation rather than taking on investment risk. Other assets can be used before 70. This method maximizes estate value as expected on have longevity on my side. Blake Augie Roselin
      4月前回复
    • wangjianjilei
    • dublaj0
      Now I am going to do my breakfast, when having my breakfast coming yet again to read further news. Cinderella Alfons Accalia
      4月前回复
    • wangjianjilei
    • dublaj0
      I believe that is one of the so much vital information for me. And i am satisfied reading your article. But should remark on few general things, The website style is wonderful, the articles is actually great : D. Good process, cheers| Kaile Dimitri Fagan
      3月前回复
    • wangjianjilei
    • online0
      Hi my loved one! I wish to say that this article is awesome, nice written and include almost all vital infos. I would like to peer more posts like this. Marcille Austin Andrej
      3月前回复
    • wangjianjilei
    • direk0
      Very informative information. I plan to share this with friends and family. I also plan to visit the web site you gave us. Really liked the picture of the hand reaching for the credit card! Rosabel Augustus Clapper
      3月前回复
    • wangjianjilei
    • torrent0
      Stunning quest there. What occurred after? Thanks! Diana Elroy Heger
      3月前回复
    • wangjianjilei
    • online0
      Fine way of explaining, and good post to get information regarding my presentation subject matter, which i am going to deliver in college. Ulrike Tait Sherar
      3月前回复
    • wangjianjilei
    • diziler0
      This is a conversation between a man and a woman who are afraid to take the risk of a relationship. The man is having the conversation with himself about going forward with a relationship or expecting the woman to wait. Nata Sky Schinica
      3月前回复
    • wangjianjilei
    • dublaj0
      Here are several of the sites we advocate for our visitors Shena Nicol Hawk
      3月前回复
    • wangjianjilei
    • turkce0
      Lo que plantea es precisamente la ruta correcta. Muchas gracias por su comentario. Saludos cordiales, Sandra Travis Jolda
      3月前回复
    • wangjianjilei
    • turkce0
      This post is truly a pleasant one it helps new web viewers, who are wishing for blogging. Ebony Hallsy Carlyle
      3月前回复
    • wangjianjilei
    • turkce0
      Hi there. I found your web site by means of Google at the same time as searching for a related subject, your website got here up. It looks great. I have bookmarked it in my google bookmarks to come back then. Salome Alva Alexandria
      3月前回复
    • wangjianjilei
    • turkce0
      I just could not go away your site before suggesting that I actually enjoyed the standard info a person supply in your guests? Is going to be back continuously to investigate cross-check new posts Fernande Ernie Amalburga
      3月前回复
    • wangjianjilei
    • turkce0
      Thanks for sharing your thoughts about aaxll. Regards Zilvia Bartel Bozovich
      3月前回复
    • wangjianjilei
    • turkce0
      Good day! This is my first comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading through your articles. Can you suggest any other blogs/websites/forums that go over the same topics? Thanks! Colline Augusto Langer
      3月前回复