Mysql大表优化方案.

最新推荐文章于 2024-05-16 16:27:40 发布

深蓝水域

最新推荐文章于 2024-05-16 16:27:40 发布

阅读量1.2k

点赞数 1

分类专栏：大数据文章标签： mysql 数据优化存储

本文链接：https://blog.csdn.net/whylazy/article/details/53405498

版权

大数据专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1 问题描述

Mysql 5.5版本默认使用的是共享表空间，即所有数据数据都存放在一个文件（ibdata1 ）之中，导致这个文件越来越大，尤其是日志统计系统，增长速度尤为明显，短时间内就可能达到几十G，上百G大小，即使清空了日志表，这个文件也不会收缩，造成存储资源极大的浪费，更为严重的是，大文件的读写操作和维护非常麻烦，因此需要把共享表空间变成独立表空间，让一个表对应一个数据文件，再将大表进行拆分。
Mysql 5.6及以上版本默认是独立表空间，一个表对应一个数据文件，5.7以上甚至做到每个表分区对应一个文件，对于大数据的支持效果更好，如果可以的话建议升级到高版本的mysql，以下是比较稳妥安全的方案。

2 解决方案·初级

2.1 备份数据库

考虑到当前日志数据已经非常庞大，且服务器空间不足，所以先清空大表数据，再进行备份。

Truncate table t1;
Truncate table t2;
"${MYSQL_HOME}/bin/mysqldump" -P3306 -uroot –p'123' –all-databases –add-drop-table > /home/data/backup.sql

2.2 修改配置文件my.cnf

停止数据库服务，修改配置文件my.cnf，使用独立表空间模式

[mysqld]
# 更改数据目录
basedir=/home/mysql/mysql-5.5.40-linux2.6-x86_64
#datadir=/home/data/mysql-5.5.40
datadir=/home/data/mysql-test
# 修改配置为独立表空间
innodb_file_per_table=1
# 启用计划任务
event_scheduler=ON

2.3 还原数据库

复制旧的数据目录，根据实际情况，只需要复制这几个文件目录mysql/,performance_schema/以及所有的数据库目录，如bufferdb/, test/，例如，

[root@localhost bin]# ll /home/data/mysql-test/
总用量 16
drwx------. 2 mysql mysql 4096 11月  1 10:28 bufferdb
drwx------. 2 mysql mysql 4096 11月  1 10:28 mysql
drwx------. 2 mysql mysql 4096 11月  1 10:28 performance_schema
drwx------. 2 mysql mysql 4096 11月  1 10:28 test

启动数据库，执行还原命令

./mysql < /home/data/backup.sql

2.4 设置定时清理任务

方法一：使用mysql定时任务
– 启用计划任务，下次服务器重启需要重新设定

SET GLOBAL event_scheduler = 1;
show variables like '%event%'

– 修改my.cnf, 永久启用

event_scheduler=ON

– 定时每天凌晨3点执行

delimiter |

CREATE EVENT `e_test`
ON SCHEDULE EVERY 1 DAY
STARTS '2016-11-01 03:03:00'
DO
BEGIN
-- 删除十天前的数据，以时间删除的，注意建立时间索引
delete from demoTable where addtime < DATE_ADD(CURDATE(), INTERVAL -10 DAY);
commit;
-- 收缩表，效率低，请留意观察，如果新的insert操作可以复用delete的空间则无需执行收缩表操作！！！
optimize table demoTable;
END |

delimiter ;

3 解决方案·中级

增加一个新的服务器，它有更快的运算速度，更大的存储空间，作为数据运算服务器，这个方案是在初级方案之的，它需要从前端数据库抽取数据到本地，完成统计运算后，把结果返回给前端数据库。

3.1 编写shell脚本

由于这是在多台服务器上进行操作，仅仅依靠SQL是不够的，需要一些系统级别的命令支持，所以改用shell脚本去操作，详见如下脚本，sln-mysql-backup.sh

#!/bin/bash
#by ydh V2.0 2016-11-02
set -e

## 检查输入日期(yyyy-MM-dd), 如果没有输入日期则默认为昨天
if [ $# -lt 1 ]; then
    LASTDAY=$(date -d last-day +%Y-%m-%d)
else
    LASTDAY=$1
fi
# 判断时间是否合法或者大于当前系统时间
temp=$(date +%Y-%m-%d)
if [ $LASTDAY \> $temp ];then
    echo "时间参数[$LASTDAY]，输入不正确，或大于当前系统时间！正确示例：$0 2016-01-01"
    exit 2
fi
echo $LASTDAY
echo "本地mysql安装路径"
MYSQL_HOME=/home/mysql/mysql-5.5.40-linux2.6-x86_64
echo "删除本地十天前的数据"
"$MYSQL_HOME/bin/mysql" -uroot bufferdb -e"delete from t_click_log_bak where click_time < DATE_ADD('$LASTDAY', INTERVAL -10 DAY)";
echo "删除远程数据库半年前的数据"
"$MYSQL_HOME/bin/mysql" -h192.168.1.102 -uroot -p888888 demo -e"delete from t_click_log_bak where click_time < DATE_ADD('$LASTDAY', INTERVAL -180 DAY)"
echo "删除远程数据库昨天的数据"
"$MYSQL_HOME/bin/mysql" -h192.168.1.102 -uroot -p888888 demo -e"delete from t_click_log_bak where click_time>= '$LASTDAY' and click_time < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)"
echo "用数据泵把本地昨天的数据推送到远程数据库"
"$MYSQL_HOME/bin/mysqldump" -t -uroot bufferdb t_click_log_bak --where=" click_time >=  '$LASTDAY' and click_time < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)" | "/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysql" -h192.168.1.102 -uroot -p888888 demo
echo "调用远程数据库统计存储过程"
#"$MYSQL_HOME/bin/mysql" -h192.168.1.102 -uroot -p888888 demo -e"call myProcedure"
echo "删除本地昨天的统计数据"
#"$MYSQL_HOME/bin/mysql" -uroot bufferdb -e"delete from t_statist where click_time >=  '$LASTDAY' and click_time < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)";
echo "将远程数据库统计数据拉取到本地"
#"$MYSQL_HOME/bin/mysql" -h192.168.1.102 -uroot -p888888 demo t_statist --where=" click_time >=  '$LASTDAY' and click_time < DATE_ADD('$LASTDAY', INTERVAL 1 DAY)" | "/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysql" -uroot -p888888 t_statist

3.2 设置系统定时任务

[root@localhost bin]# crontab -e
#增加定时任务，凌晨3点执行
00 3 * * * /home/myshell/sln-mysql-backup.sh
[root@localhost bin]# service crontabd restart

4 解决方案·高级

4.1 表优化

索引，如同一本书的目录，无论书的内容有多少多厚，它的目录也就几页而已，看一眼目录就能翻到我们想看的章节，索引也是如此。然而索引并非越多越好，每个索引用到一定的存储空间，每条记录的增删改都会需要每个索引进行维护，请看如下例子
建表一

CREATE TABLE `t_click_log_bak` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(32) DEFAULT NULL,
  `optype` varchar(32) DEFAULT NULL,
  `click_time` datetime DEFAULT NULL,
  `page_a` varchar(32) DEFAULT NULL,
  `page_b` varchar(32) DEFAULT NULL,
  `element` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accessIndex_uid` (`uid`),
  KEY `accessIndex_optype` (`optype`),
  KEY `accessIndex_page_a` (`page_a`),
  KEY `accessIndex_page_b` (`page_b`),
  KEY `ndx_access_ut` (`uid`,`click_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表二

CREATE TABLE `t_click_log_bak` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(32) DEFAULT NULL,
  `optype` varchar(32) DEFAULT NULL,
  `click_time` datetime DEFAULT NULL,
  `page_a` varchar(32) DEFAULT NULL,
  `page_b` varchar(32) DEFAULT NULL,
  `element` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accessIndex_uid` (`uid`),
  KEY `accessIndex_datetime` (`click_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表三

CREATE TABLE `t_click_log_bak` (
  `id` int(11) NOT NULL,
  `uid` varchar(32) DEFAULT NULL,
  `optype` varchar(32) DEFAULT NULL,
  `click_time` datetime DEFAULT NULL,
  `page_a` varchar(32) DEFAULT NULL,
  `page_b` varchar(32) DEFAULT NULL,
  `element` varchar(32) DEFAULT NULL,
  KEY `accessIndex_datetime` (`click_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表一，是最糟糕的，恨不得每个字段都加个索引，而那些索引缺极少使用，却导致数据库的插入、修改、删除付出了高昂的代价，可能是几十、上千倍的时间消耗。就像一本小说，有了章节目录，你还指望出版社按人物名，地点名给你编排个目录吗？

建表二，仅有主键、时间、用户ID作为索引，一般而言这是最好的了，就像小说三要素，地点、时间、人物，显然是最常用的。

建表三，仅有时间索引，这是出于实际项目的考虑，这个表非常之大，总容量几十亿，日增长千万左右，增加一个索引，可能会每天额外增加几十上百M的索引存储，每次插入删除操作会慢上几分，几十分钟！所以只需要时间索引就够了，且每个查询都应该根据时间进行过滤！！！

还有一点，运算库的大表不需要ID主键，因为的运算库的数据来源是前端库，ID由前端自动增长序列生成，如果前端重置ID自增长起始数，就会导致该表违反主键唯一约束，插入错误。而且日志表中的自增长ID基本毫无用处！！！

下面在数据库中演示，感受一下

-- 三个索引，主键、用户ID、时间索引
LOAD DATA INFILE '/home/data/OF2014-02-01.csv' into table t_click_log_bak; 
mysql> LOAD DATA INFILE '/home/data/OF2014-02-01.csv' into table bufferdb.t_click_log_bak; 
Query OK, 1275008 rows affected (56.28 sec)
Records: 1275008  Deleted: 0  Skipped: 0  Warnings: 0

-- 减少用户ID索引后，保留主键、时间索引
drop index accessIndex_uid on t_click_log_bak;
mysql> LOAD DATA INFILE '/home/data/OF2014-02-01.csv' into table bufferdb.t_click_log_bak; 
Query OK, 1275008 rows affected (24.09 sec)
Records: 1275008  Deleted: 0  Skipped: 0  Warnings: 0
-- 删除操作
[SQL] delete from t_click_log_bak where click_time >= '2014-02-16';
受影响的行: 154664
时间: 10.924s
-- 仅保留时间索引
mysql> LOAD DATA INFILE '/home/data/OF2014-02-01.csv' into table bufferdb.t_click_log_bak; 
Query OK, 1275008 rows affected (19.80 sec)
Records: 1275008  Deleted: 0  Skipped: 0  Warnings: 0
-- 删除操作
[SQL] delete from t_click_log_bak where click_time >= '2014-02-16';
受影响的行: 154664
时间: 2.299s

综上，如果无特别需求，日志表中仅保留时间索引即可。

4.2 读写操作分离

解决方案·中级里面，用的是MySQL数据泵直接把一个数据库中的数据推送到另一个数据库，这是最方便的，然而存在一个问题，这个方案需要同时占用两个数据库资源（DB1，DB2），那么整个方案的消耗为MAX（DB1，DB2），也就是两个数据库中消耗最多的就是整个方案的消耗，而且任意一个数据读写失败，整个方案就需要重做。另外，一般情况下，把数据写入文件要比写入数据库要快很多，所需存储也要小很多。这并不难理解，因为写入数据库，不仅要写数据文件，还要格式化数据和维护索引以及其他数据库日志之类信息等额外操作。所以，最佳方案就是将两个数据库读写分离开来，看如下示例
数据总量1275008

1、数据泵，前端数据库对数据库，60秒

"/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysqldump" -t -uroot bufferdb t_click_log_bak --where=" click_time >= '2014-02-01'" | "/home/mysql/mysql-5.5.40-linux2.6-x86_64/bin/mysql" -h192.168.1.102 -uroot -p888888 demo

2、数据泵，前端数据库》SQL文件》导入文件，总共47秒

"D:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" -t -uroot -p888888 demodb t_click_log_bak --where=" click_time >= '2014-02-01'" > D:\2014-02-01.sql
15s
./mysql -uroot bufferdb < /home/data/2014-02-01.sql
32s

3、LOAD DATA INFILE语句，前端数据库》CSV文件》装载文件，30秒

select * from demodb.t_click_log_bak where click_time >= '2014-02-01' into outfile 'D:\\OF2014-02-01.csv';
[SQL] select * from demodb.t_click_log_bak where click_time >= '2014-02-01' into outfile 'D:\\OF2014-02-01.csv';
受影响的行: 1275008
时间: 5.874s
mysql> LOAD DATA INFILE '/home/data/OF2014-02-01.csv' into table bufferdb.t_click_log_bak; 
Query OK, 1275008 rows affected (24.09 sec)
Records: 1275008  Deleted: 0  Skipped: 0  Warnings: 0

显然，3最快！效率是一方面，另一方面是对前端数据库（业务数据库）的保护，前端数据库只需要导出数据到一个临时文件就可以，后面的操作都不会对前端数据库造成任何影响。
但是这个方案的一个缺点是，维护比较麻烦，不仅要分别操作两个数据库，还要维护生成的临时数据文件。考虑到实际服务器的存储情况和维护成本，读写分离仅作为参考！

深蓝水域

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
3
评论
Mysql大表优化方案.

1 问题描述Mysql 5.5版本默认使用的是共享表空间，即所有数据数据都存放在一个文件（ibdata1 ）之中，导致这个文件越来越大，尤其是日志统计系统，增长速度尤为明显，短时间内就可能达到几十G，上百G大小，即使清空了日志表，这个文件也不会收缩，造成存储资源极大的浪费，更为严重的是，大文件的读写操作和维护非常麻烦，因此需要把共享表空间变成独立表空间，让一个表对应一个数据文件，再将大表进行拆分。
复制链接

扫一扫