mysql 处理大数据太慢_科研测序芯片数据处理，你用了MySql了吗

写在文前：在处理测序数据，或者仪器采集的大量数据时，常规操作费时费力。我们需要借助多个工具来实现快速处理，稍微有点学习成本(需要简单地了解数据结构，SQL，Shell，R，Python，Java以及大数据)，不过跟着农大芸来，事情就会简单很多。(省出来的时间你可以用来：看文献，看网课，追剧，花前月下...)

本次所需要的软件及编程语言如下

MySql 5.7navicat 15Shell 语言SQL 语言

正文：
盼望着，盼望着，关联分析的SNP测序数据回来了~

400M的txt格式文件~

于是我们试一下notepad ++ 打开看看：

算了，还是去Linux中查看下数据内容(Linux安装：不要虚拟机，不要双系统！在你的Windows上使用Linux如此简单)

$ less -S clean.hmp.txt

查看总行数(包括head)

$ less -S clean.hmp.txt | wc -l397741

实验需求：

矩阵第一列rs# 是SNP芯片编号，需要筛选出特定的芯片数据(500列)。即在近40万行数据中找到符合条件的几十个芯片数据。

解决：

下文提供2种方法，每种方法可以借鉴到其他需求中。若时间有限可直接跳转到Plan B

Plan A：

Excel中VlookUp函数。还好平时学习积累，第一时间想到日常数据分析工具。但是，这么大的文件仅Excel加载就已经崩溃，更何况再使用全文精确查找函数。忽然间又想到拆分(如果数据量小，大家可以采取此方法)，我们可以将不同染色体编号拆分出来，然后再使用VlookUp函数，这样会比直接使用减少更多运算量。

有童鞋可能会问：怎么拆分呢？要是使用Excel的筛选功能还是很慢呀！

莫慌，这里使用Linux中的awk可以快速切分(全过程6S搞定)：

# awk过滤数据, 写入chrom1.txt文件中. 其中NR==1{print}为输出表头, $3==1 {print}为过滤第三列即chrom=1$ less -S clean.hmp.txt |awk 'NR==1{print} $3==1 {print}' | less -S > chrom1.txt# 查看输出文件行数，与mysql查询相互印证(附图)$ less -S chrom1.txt | wc -l27510

有童鞋可能看到这两个查询出行数不同呀！需要解释下，chrom.txt包含表头，所以会多出一行来。拆分后的文件仅27M，Excel操作相比以前已经快了很多(Vlookup使用方法可百度，学习成本极低)，可是还是忍受不了，毕竟小麦染色体一共有21条，那么切分21个文件，然后每个再打开分析？一两个还行，太多了头就大了！

Plan B：

既然我们想要在40万行数据中筛选出来需要的几十个标记数据，那么使用数据库是最佳选择。

开始操作：

0、MySQL安装

我们这次使用云服务器进行实战操作，Ubuntu安装mysql简单易行，度娘不乏教程，此处省略。

1、在数据库中创建表及表结构

-- 主要代码 line2-line492 同line1CREATE TABLE `gene_chip`  (  `rs#` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `alleles` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `chrom` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `pos` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `strand` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `assembly#` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `center` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `protLSID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `assayLSID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `panelLSID` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `QCcode` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `line1` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  ...  `line493` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  INDEX `idx_gene`(`rs#`, `chrom`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、使用navicat工具连接数据库

3、数据导入

4、创建索引(该步骤可显著提升查询速度)

在漫长的上传过程(20分钟)结束后，我们首先要对数据进行校验，此处仅核对总行数即可。

因为该数据集只进行查询，而不进行insert、update等操作。所以我们建立索引，提升查询速度。sql命令如下：

# 在MySQL命令框下执行create index idx_gene on gene_chip(`rs#`,chrom);# 执行结果如下Query OK, 0 rows affected (6.69 sec)Records: 0  Duplicates: 0  Warnings: 0

创建索引完毕后，我们可以查看所建立的索引。

show index from gene_chip;+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| gene_chip |          1 | idx_gene |            1 | rs#         | A         |      368504 |     NULL | NULL   | YES  | BTREE      |         |               || gene_chip |          1 | idx_gene |            2 | chrom       | A         |      368504 |     NULL | NULL   | YES  | BTREE      |         |               |+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)

5、实现需求

我们查询10个标记来做测试，查询耗时0.16秒，sql如下：(数据脱敏，仅示例代码)

SELECT * FROM `gene_chip` WHERE `rs#` IN ('874','41314','061745','13705','41','24107','503','671','60751','9827')

5.1 查询数据导出

写在文末

清代彭端淑在《为学一首示子侄》说“天下事有难易乎？为之，则难者亦易矣；不为，则易者亦难矣。人之为学有难易乎？学之，则难者亦易矣；不学，则易者亦难矣。”