大量数据快速导入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 [email protected]: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
分享
评论 共28条
  • wangjianjilei

    昵称

  • 取消

    请填写用户信息:

    • wangjianjilei
    • joker0
      Hello, just wanted to tell you, I liked this blog post. Tedda Poul Rockie
    • 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
    • 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
    • wangjianjilei
    • bahis0
      Way cool! Some very valid points! I appreciate you writing this write-up and also the rest of the website is also very good. Fernande Roley Mesics
    • wangjianjilei
    • erotik0
      Terrific article! That is the type of info that are meant to be shared around the internet. Judi Huntley Virendra
    • wangjianjilei
    • sikis0
      I think this is a real great blog. Really looking forward to read more. Fantastic. Shena Mallory Britton
    • 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
    • wangjianjilei
    • sikis0
      Hi there, I enjoy reading through your post. I like to write a little comment to support you. Aline Linus Emanuel
    • 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
    • wangjianjilei
    • bahis0
      Thank you Lisa for sharing your experiences and perspective. Geraldine Euell Bounds
    • wangjianjilei
    • sikis0
      Hi, after reading this awesome post i am as well delighted to share my knowledge here with colleagues. Corry Morrie Ludovick
    • wangjianjilei
    • bahis0
      If you like epic adventures, there is the God of War. Faythe Arel Greenburg
    • wangjianjilei
    • sikis0
      Im thankful for the blog post. Much thanks again. Want more. Cathlene Clay Foy
    • wangjianjilei
    • sikis0
      Really appreciate you sharing this article. Much thanks again. Fantastic. Mable Gustavo Redvers
    • wangjianjilei
    • sikis0
      This is my first time visit at here and i am really happy to read everthing at one place. Marnie Phillipp Bussy
    • wangjianjilei
    • bahis0
      I really like your writing style, great information, thankyou for posting : D. Carrie Christiano Quartus
    • 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
    • wangjianjilei
    • torrent0
      Amazing! Its actually remarkable paragraph, I have got much clear idea concerning from this piece of writing. Ursulina Salem Lachish
    • wangjianjilei
    • ucretsiz0
      I like this post, enjoyed this one appreciate it for putting up. Carlynn Byrann Litt
    • wangjianjilei
    • bedava0
      Buenas noches agradezco por compartir este ritual, tengo una duda respecto a que debo hacer con la carta gracias. Bellina Gardie Schnurr
    • 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
    • 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
    • wangjianjilei
    • bluray0
      What a data of un-ambiguity and preserveness of precious familiarity concerning unpredicted feelings. Georgianna Devy Grassi
    • wangjianjilei
    • download0
      I got what you intend,saved to bookmarks, very nice web site. Dyanne Den Malissa
    • wangjianjilei
    • torrent0
      I really like it when people get together and share views. Great site, keep it up. Nicolette Fransisco Vaenfila
    • 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
    • wangjianjilei
    • ucretsiz0
      I blog frequently and I genuinely thank you for your content. Bobby Cheston Doig
    • 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