记 mysql 分区 导入导出

记录背景

• 上线的有个表的数据已经达到千万数据量了，但是业务需求一直很紧，不能对这块进行做够的开发，所以只能出一个下策，对这个表进行分区，分区的意思就是在逻辑上是一张表，但是在物理存储上是多个文件存储的，多个文件进行存储，在查询的时候速度有相应的加快，这个可以出查分区的好处，不是本文的重点不在此处进行展开。
• 因为是线上数据，操作需谨慎，所以打算将文件down下来，再本地做一次分区操作，看是否出错，然后找个夜深人静的时候，偷偷上线。。哈哈

导出数据

• 导出数据格式

    SELECT fields INTO OUTFILE 'file_name'
        [{FIELDS | COLUMNS} 字段
            [TERMINATED BY 'string'] 字段之间分隔符号
            [[OPTIONALLY] ENCLOSED BY 'char'] 字段被包含在char中间
            [ESCAPED BY 'char'] 忽略字段里出现的char
        ]
        [LINES
            [STARTING BY 'string'] 忽略开头是string的行
            [TERMINATED BY 'string'] 行分隔符
        ]
    FROM test_table;

• 导出的时候查看 show variables like 'secure_file_priv'; 如果有显示 就是说明导出的路径必须在这个下面 如果为“” 则就是没有限制

• 例子

select * into outfile '/home/tomcat_admin/XXX.csv' 
  fields terminated by ','
  enclosed by '"' 
  lines terminated by '\n' 
from quiz_question;

• 出现的问题 write/read permission 权限不足的问题，说我们现在的用户组没有权限读写这个目录， 这个情况,我们的运维大佬给了一个方法 就是 导出到/tmp 这个目录下面去 这个tmp目录是所有人都有权限的。

导入数据

• 导入数据的格式

load data [low_priority] [local] infile'file_name txt' [replace | ignore]
    into table tbl_name
    [fields
    [terminated by't']
    [OPTIONALLY] enclosed by '']
    [escaped by'\' ]]
    [lines terminated by'n']
    [ignore number lines]
    [(col_name, )]

• 这里有个补充 ignore replace 正常
  
  • ignore 忽略掉重复的
  • replace 有重复的进行替换 ，取决与唯一索引的问题
• 例子

LOAD DATA local INFILE '/tmp/XXX.csv' IGNORE INTO TABLE 
quiz_question_old FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n' ;

• error

  • 执行这个句的时候 告诉 ERROR 1197 ERROR 1197 (HY000) at line 2: Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

  • 原因是导入的数据 2G 毕竟很大，而mysql把load data 当作是一个事务进行处理 导致缓存不够了。。

  • 解决方法：

    • 加大缓存(导入不是常有事 没有必要改配置)

    • 分而治之 将我们要导入的文件进行拆分成多分进行批次导入即可

      • wc -l XXX.csv
        wc命令用来计算数字。利用wc指令我们可以计算文件的Byte数、字数或是列数，若不指定文件名称，或是所给予的文件名为“-”，则wc指令会从标准输入设备读取数据。
        -c或–bytes或——chars：只显示Bytes数；
        -l或——lines：只显示列数；
        -w或——words：只显示字数。

      • split命令可以将一个大文件分割成很多个小文件，有时需要将文件分割成更小的片段，比如为提高可读性，生成日志等
        -b：值为每一输出档案的大小，单位为 byte。
        -C：每一输出档中，单行的最大 byte 数。
        -d：使用数字作为后缀。
        -l：值为每一输出档的列数大小。
        -a： length来指定后缀的长度
        split -l 5000000 XXXX.csv -d -a 1 XXXX_

导入数据

• mysql 表分区 就是逻辑上还是一张表 但是物理上已经是多份文件进行存储
• 因为没有很强的逻辑，而且是自增的，就按照range 方法进行分区了
• 分区例子

//添加区分
ALTER TABLE XXXX PARTITION BY RANGE (xx)(
 PARTITION p0 VALUES LESS THAN (100000),
 PARTITION p1 VALUES LESS THAN (200000),
 PARTITION p2 VALUES LESS THAN (300000),
 PARTITION p3 VALUES LESS THAN (400000),
 PARTITION p4 VALUES LESS THAN (500000),
 PARTITION p5 VALUES LESS THAN MAXVALUE
)

• 按照数据 10w 进行分区，最后一个是达到最大，然后如果最后一个分区size超过了10w，这个时候就要分解分区了

//分解分区
ALTER TABLE XXXX  REORGANIZE PARTITION p5 INTO
(
PARTITION p5 VALUES LESS THAN (501000),
PARTITION p6 VALUES LESS THAN MAXVALUE
);