目录
MySQL 对于千万级别的大表要怎么优化？如果已经达到了亿级别呢？
5、配置分片表t_im_msg全局自增主键（本地时间戳方式）
5.2 修改 sequence_time_conf.properties 文件配置
2.3 插入测试库添加msg_sharding_date值（并发更新脚本）
4.2 后台执行脚本 import_im_msg.sh 导入所有数据
5.2 后台执行脚本 import_im_msg.sh 导入所有数据
5.3 与线上数据同步，实现双写持续更新数据，待开发后续更新业务
写在最前：
MySQL 对于千万级别的大表要怎么优化？如果已经达到了亿级别呢？
千万级别，对于 MySQL 实际上确实不是什么压力，innodb 的存储引擎，使用的是 B+ 数存储结构，千万级别的数量，基本上也就是三到四层的搜索，如果有合适的索引，性能基本上也不是问题。
但经常出现的情况是，业务上面的增长，举个例子如用户聊天记录存储表，数据量还会继续增长，为了应对这方面的问题而必须要做扩展，此时可能首先需要考虑的就是分表策略。
当然分表，可能还有其它几个原因，比如表变大了，千万级的数据库，为了减少运维成本，降低风险，就想到了通过分表来解决问题，这都是比较合适的。
分表的话，还是要根据具体的业务逻辑等方面来做。
从一般运维的角度来看，什么情况下需要考虑分库分表呢？
1、能不分就不分
MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，可以去做其它力所能及的事情，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了。
2、数据量太大，正常的运维影响正常的业务访问
这里说的运维，举个栗子：
（1）对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据，网络传输占用50MB的时候，需要20000秒才能传输完毕，因此整个过程中的维护风险都是高于平时的。1T的数据的备份，也会占用大量的磁盘IO，如果是SSD还好，当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘，则在不限流的情况下去执行xtrabackup，该实例基本不可用。
（2）对数据表的修改。如果某个表过大，对此表做DDL的时候，MySQL会锁住全表，这个时间可能很长，在这段时间业务不能访问此表，影响甚大。在此操作过程中的所有时间，都可以看做是风险时间。把数据表切分，总量减小，有助于改善这种风险。
（3）整个表热点，数据访问和更新频繁，经常有锁等待，又没有能力去修改源码，降低锁的粒度，那么只会把其中的数据物理拆开，用空间换时间，变相降低访问压力。
3、表设计不合理
4、某些数据表出现无穷增长
很好举例子，就如下面具体的案例用户聊天记录存储表，还比如各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微博的feed的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。拆分的标准很多，按用户的，按时间的，按用途的，等等。。。
5、安全性和可用性的考虑
这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不要影响到100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，这样整体的可用性也会提升。
6、业务耦合性考虑
这个跟上面有点类似，主要是站在业务的层面上考虑，比如飞猪火车票业务和天猫业务是完全无关的业务，虽然每个业务的数据量可能不太大，放在一个 MySQL 实例中完全没问题，但是很可能飞猪火车票业务的 DBA 或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，天猫业务的人员虽然技术很优秀，工作也很努力，照样被老板敲脑瓜。解决的办法很简单:惹不起，躲得起，把业务拆分到不同的实例上。
加点佐料：借一波典故（三国迷）
《三国演义》第一回：“话说天下大势，分久必合，合久必分。”其实在实践中，有时候可能你原本要分，后来又发现分了还得合，分分合合，完全是现实的需求，随需求而变才是王道，而 DBA 的价值也能在此体现，或分或合的情况太多。
用户聊天记录存储表概况：
库大小：82G
表大小：71G
AUTO_INCREMENT：1788396067
表行数：158313898
一、表优化
1、原建表语句
mysql> show create table t_im_msg_1 \G;
*************************** 1. row ***************************
Table: t_im_msg_1
Create Table: CREATE TABLE `t_im_msg_1` (
`msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fromid` bigint(20) unsigned NOT NULL,
`toid` bigint(20) unsigned NOT NULL DEFAULT '0',
`chatfrom` bigint(20) unsigned NOT NULL,
`chatto` bigint(20) unsigned NOT NULL DEFAULT '0',
`group_id` int(11) DEFAULT '0' COMMENT '群组id',
`shop_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`pub_id` int(11) DEFAULT '0' COMMENT '消息类型：0表示普通类型，1表示平台客服',
`channel_id` tinyint(4) DEFAULT '1' COMMENT '区分平台：1表示美丽说，2表示higo',
`msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`ext` text COMMENT '扩展信息json_encode',
`source` char(10) NOT NULL DEFAULT 'web' COMMENT '信息来源',
`type` varchar(16) DEFAULT '' COMMENT '消息类型',
`is_read` tinyint(4) DEFAULT '0',
`source_ip` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_del` tinyint(4) DEFAULT '0' COMMENT '消息状态：1表示删除，0表示正常',
PRIMARY KEY (`msg_id`),
KEY `fromto` (`chatfrom`,`chatto`,`is_read`,`msg_id`),
KEY `fromto2` (`fromid`,`toid`,`is_read`,`msg_id`),
KEY `tofrom` (`chatto`,`chatfrom`,`is_read`,`msg_id`),
KEY `tofrom2` (`toid`,`fromid`,`is_read`,`msg_id`),
KEY `group` (`group_id`,`msg_id`),
KEY `ctime` (`ctime`,`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户聊天记录存储表';2、新建表语句
mysql> show create table t_im_msg_wf \G;
*************************** 1. row ***************************
Table: t_im_msg_wf
Create Table: CREATE TABLE `t_im_msg` (
`msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fromid` bigint(20) unsigned NOT NULL,
`toid` bigint(20) unsigned NOT NULL DEFAULT '0',
`chatfrom` bigint(20) unsigned NOT NULL,
`chatto` bigint(20) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) DEFAULT '0' COMMENT '群组id',
`shop_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`pub_id` int(10) DEFAULT '0' COMMENT '消息类型：0表示普通类型，1表示平台客服',
`channel_id` tinyint(4) DEFAULT '2' COMMENT '区分平台：1表示美丽说，2表示higo',
`msg` text COMMENT '消息内容',
`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`msg_sharding_date` int(10) NOT NULL COMMENT '消息分区日期',
`ext` text COMMENT '扩展信息json_encode',
`source` varchar(10) NOT NULL DEFAULT 'web' COMMENT '信息来源',
`type` varchar(16) DEFAULT '' COMMENT '消息类型',
`is_read` tinyint(4) DEFAULT '0' COMMENT '消息状态：1表示已读，0表示未读',
`source_ip` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_del` tinyint(4) DEFAULT '0' COMMENT '消息状态：1表示删除，0表示正常',
PRIMARY KEY (`msg_id`),
KEY `idx_chatfrom` (`chatfrom`),
KEY `idx_chatto` (`chatto`),
KEY `idx_fromid` (`fromid`),
KEY `idx_toid` (`toid`),
KEY `idx_group_id` (`group_id`),
KEY `idx_ctime` (`ctime`),
KEY `idx_msg_date` (`msg_sharding_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户聊天记录存储表';3、优化项
3.1 优化索引
1> 对于 innodb 存储引擎，复合索引本身属于非聚簇索引，索引本身包含主键，如果再加上主键就额外增加了索引长度，降低了更新性能，索引剔除复合索引中的 msg_id。
2> 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;对于消息查询（跟开发确认查询条件）不区分is_read，因此剔除is_read将索引变为窄索引，设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;
3> 删除重复索引tofrom和tofrom 2，虽然查询需求消息的发送者和接收者转换了，也只需要 where 条件调整一下顺序而已，无需额外添加索引，况且还是更新频繁的消息存储表。
4> 注意事项：
-
(1) 对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高；
-
(2) 何时是用复合索引根据where条件建索引是极其重要的一个原则；
-
(3) 注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中；
-
(4) 如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间。
5> 删除所有复合索引，针对消息查询对 chatfrom、chatto、fromid、toid 分别建立索引以优化索引空间。
3.2 添加消息分区日期字段
即分区键msg_sharding_date，设置为 int 类型，存储格式如 20190510，提高查询速率
3.3 修改 channel_id 的默认值为 2，即默认平台为YYYY
根据业务需求变化确认更新
二、按自然月分片测试
写在最前：
月数据量：4915810（2018-12）
年数据量：47873090（2018）
1、建库建表
CREATE DATABASE if not exists higo_im$1-132（暂时规划 2015-2025）
CREATE TABLE `t_im_msg` (
`msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fromid` bigint(20) unsigned NOT NULL,
`toid` bigint(20) unsigned NOT NULL DEFAULT '0',
`chatfrom` bigint(20) unsigned NOT NULL,
`chatto` bigint(20) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) DEFAULT '0' COMMENT '群组id',
`shop_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`pub_id` int(10) DEFAULT '0' COMMENT '消息类型：0表示普通类型，1表示平台客服',
`channel_id` tinyint(4) DEFAULT '2' COMMENT '区分平台：1表示美丽说，2表示higo',
`msg` text COMMENT '消息内容',
`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`msg_sharding_date` int(10) NOT NULL COMMENT '消息分区日期',
`ext` text COMMENT '扩展信息json_encode',
`source` varchar(10) NOT NULL DEFAULT 'web' COMMENT '信息来源',
`type` varchar(16) DEFAULT '' COMMENT '消息类型',
`is_read` tinyint(4) DEFAULT '0' COMMENT '消息状态：1表示已读，0表示未读',
`source_ip` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_del` tinyint(4) DEFAULT '0' COMMENT '消息状态：1表示删除，0表示正常',
PRIMARY KEY (`msg_id`),
KEY `idx_chatfrom` (`chatfrom`),
KEY `idx_chatto` (`chatto`),
KEY `idx_fromid` (`fromid`),
KEY `idx_toid` (`toid`),
KEY `idx_group_id` (`group_id`),
KEY `idx_ctime` (`ctime`),
KEY `idx_msg_date` (`msg_sharding_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户聊天记录存储表';2、建库建表脚本
#!/bin/bash
user='root'
passwd='LeHe3306@hiGo2025'
host='127.0.0.1'
port=2025
function mysql_conn01(){
mysql -u${user} -p${passwd} -h${host} -P${port}
}
int=1
while(( $int<=132 ))
do
echo "${int}"
db="higo_im${int}"
echo "CREATE DATABASE if not exists ${db};" | mysql_conn01
tb="/data/soft/wufei/higo_im/t_im_msg.sql"
cat ${tb} | mysql -u${user} -p${passwd} -h${host} -P${port} -D${db}
let "int++"
done3、mycat 配置
3.1 修改 schema.xml 文件
<schema name="higo_im_test" checkSQLschema="false" >
<table name="t_im_msg" primaryKey="msg_id" dataNode="higo_im$1-132" rule="sharding-by-month" />
</schema>
<dataNode name="higo_im$1-132" dataHost="localhost1" database="higo_im$1-132" />3.2 修改 server.xml 文件
<user name="mycat_inf">
<property name="password">MYCATinf#^hiGo3LAQ7</property>
<property name="schemas">higo_im_test</property>
</user>3.3 修改 rule.xml 文件
<tableRule name="sharding-by-month">
<rule>
<columns>msg_sharding_date</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyyMMdd</property>
<property name="sBeginDate">20150101</property>
<property name="sEndDate">20251231</property>
</function>
<!--
columns：标识将要分片的表字段
algorithm:分片函数
dateFormat:分片日期格式
sBeginDate:开始日期
sEndDate:结束日期（必须要创建结束日期，循环计算数据具体在哪个分片上，虽然不能用 >= 或者 <= ，但是可以用 between...and 和 in，如果没有结束日期将无法查询时间段只能扫描所有表，后期可灵活调整）
-->4、重启 mycat查看状态
5、配置分片表t_im_msg全局自增主键（本地时间戳方式）
ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)
即换算成十进制为18位数的long类型，每毫秒可以并发12位二进制的累加。
5.1 修改配置文件 server.xml 配置
<property name="sequnceHandlerType">2</property>
<!-- 注：sequnceHandlerType 需要配置为2，表示使用本地时间戳生成sequence -->5.2 修改 sequence_time_conf.properties 文件配置
# sequence depend on TIME
WORKID=01 #0-31任意整数
DATAACENTERID=01 #0-31任意整数
# 多个 mycat 节点下,每个 mycat配置的 WORKID，DATAACENTERID 不同，组成唯一标识，总共支持 32*32=1024 种组合5.3 在 schema.xml 文件中配置
增加表 t_im_msg，msg_id 为主键，在 higo_im$1-132 分片上，分片方式为 sharding-by-month
<schema name="higo_im_test" checkSQLschema="false" >
<table name="t_im_msg" primaryKey="msg_id" autoIncrement="true" dataNode="higo_im$1-132" rule="sharding-by-month" />
</schema>5.4 重启 mycat
5.5 插入数据测试(暂不支持主键为null)
mysql> insert into t_im_msg(fromid,toid,chatfrom,chatto,group_id,shop_id,msg,ctime,msg_sharding_date,ext,source,type,source_ip) values(283099513902353978,0,1201367170,0,5166,0,'','2015-01-09 11:03:35','20150109','{"buy":"林阳刚买了 韩国 呼吸SUM37苏秘 呼吸惊喜水份套盒 6 只安瓶 清爽补水 收毛孔 ！","buy_goodsid":"176204663976998975"}','pandora','higo_buy',169082962);三、导出数据
1、导出语句
坑 1：mysqldump 为了加快导入导出，默认把数据都缩减在一行里面。查看和修改不方便，为此，使用了--skip-extended-insert 选项来使导出的数据，是多行插入形式的（mycat不支持多行插入 insert into tab_a(c1,c2) values(v1,v2),(v11,v21)… ）。
坑 2：timestamp 数据类型导出时会有时区问题，导出机器默认为东八区时区（+08:00），而 mysqldump 导出时的TIME_ZONE=‘+00:00‘ ,这样导出的数据和表里看到的差了8小时。如果想在数据导出时不默认进行时区的转换，可以在导出语句中增加参数--skip-tz-utc，这样导出的timestamp数据和在表里看到的时候是一样的。
mysqldump -uroot -pLeHe3306@hiGo2025 -h127.0.0.1 -P3215 --skip-extended-insert im t_im_msg_1 --skip-tz-utc > /tmp/t_im_msg_1.sql2、数据处理
2.1 删除 msg_id
# sed -ri 's/[0-9]+,//' [文件]2.2 按数据对应字段修改插入语句
修改测试库 msg_sharding_date 字段默认值为 0
修改 [INSERT INTO `t_im_msg_1` VALUES]为[INSERT INTO t_im_msg_1(fromid,toid,chatfrom,chatto,group_id,shop_id,msg,ctime,ext,source,type,source_ip) VALUES]
# sed -i s/\`t_im_msg_1\`/t_im_msg_1\(fromid,toid,chatfrom,chatto,group_id,shop_id,pub_id,channel_id,msg,ctime,ext,source,type,is_read,source_ip,is_del\)/g[文件]2.3 插入测试库添加msg_sharding_date值（并发更新脚本）
#!/bin/bash
# 数据库配置参数
user_inf='root'
passwd_inf='LeHe3306@hiGo2025'
host_inf='127.0.0.1'
port_inf='2025'
db_inf='im'
# 数据库连接
function mysql_inf01(){
mysql -u${user_inf} -p${passwd_inf} -h${host_inf} -P${port_inf} -D${db_inf} -N
}
# import_im_msg_file='/data/soft/wufei/higo_im/t_im_msg_1.sql'
# echo "show tables;" | mysql_inf01
# echo "source ${import_im_msg_file}" | mysql_inf01
echo "###############################################"
# 最大并发数
Thread_num=2000
# 命名管道文件
Tmp_fifo=/tmp/$$.fifo
# 创建命名管道文件
mkfifo ${Tmp_fifo}
# 用文件句柄(大于最大并发数Thread_num)打开管道文件
# 创建文件描述符，以可读（<）可写（>）的方式关联管道文件，这时候文件描述符2001就有了有名管道文件的所有特性
exec 2001<> ${Tmp_fifo}
# 关联后的文件描述符拥有管道文件的所有特性,所以这时候管道文件可以删除，我们留下文件描述符来用就可以了
rm -rf ${Tmp_fifo}
# 控制并发数
for i in `seq ${Thread_num}`
do
# 向管道中放入最大并发数个行，供下面read读取
echo >&2001
done
echo "select msg_id from t_im_msg_1 where msg_sharding_date=0 order by msg_id asc;" | mysql_inf01 | while read msg_id
do
# 通过文件句柄读取行，当行取尽时，停止下一步（并发）
read -u 2001
{
echo "update t_im_msg_1 set msg_sharding_date=date_format(ctime,'%Y%m%d') where msg_id=${msg_id};" | mysql_inf01
# if [ $? -eq 0 ];then
echo "msg_id:${msg_id}" >> /data/soft/wufei/higo_im/import_im_msg_1.success
# else
# echo "update t_im_msg_1 set msg_sharding_date=date_format(ctime,'%Y%m%d') where msg_id=${msg_id};" >> /data/soft/wufei/higo_im/import_im_msg_1.error
# fi
# 一个并发执行后要想管道中在加入一个空行，供下次使用
echo >&2001
}&
done
wait
# 关闭文件描述符的读
exec 2001<&-
# 关闭文件描述符的写
exec 2001>&-
echo "##########UPDATE SUCCESSFUL!!!##########" >> /data/soft/wufei/higo_im/import_im_msg_1.success3、测试库导出数据做进一步处理
3.1 导出数据
# head -n 和 tail -n 查看文件并删除注释行只留 INSERT 语句
# mysqldump -uroot -pLeHe3306@hiGo2025 -h127.0.0.1 -P2025 --skip-extended-insert --set-gtid-purged=OFF -t im t_im_msg_1 --skip-tz-utc > /data/soft/wufei/higo_im_mycat/t_im_msg_1.sql3.2 删除 msg_id
# sed -ri 's/[0-9]+,//'[文件]3.3 插入的字段必须包含分片字段
修改 [INSERT INTO `t_im_msg_1` VALUES]为[INSERT INTO t_im_msg(fromid,toid,chatfrom,chatto,group_id,shop_id,msg,ctime,msg_sharding_date,ext,source,type,source_ip) VALUES]
# sed -i s/\`t_im_msg_1\`/t_im_msg\(fromid,toid,chatfrom,chatto,group_id,shop_id,pub_id,channel_id,msg,ctime,msg_sharding_date,ext,source,type,is_read,source_ip,is_del\)/g[文件]4、导入数据到测试 mycat
4.1 取一小部分数据测试验证导入 mycat
4.2 后台执行脚本 import_im_msg.sh 导入所有数据（通过 mycat 导入）
#!/bin/bash
user_inf='wufei'
passwd_inf='wufei'
host_inf='127.0.0.1'
port_inf='3025'
db_inf='higo_im_test'
tb_inf='t_im_msg'
# MYCAT连接
function mysql_inf01(){
mysql -u${user_inf} -p${passwd_inf} -h${host_inf} -P${port_inf} -D${db_inf}
}
import_im_msg_file='/data/soft/wufei/higo_im_mycat/t_im_msg_1.sql'
# echo "show tables;" | mysql_inf01
echo "source ${import_im_msg_file}" | mysql_inf014.3 执行！
nohup /bin/bash /data/soft/wufei/higo_im_mycat/import_im_msg.sh > /data/soft/wufei/higo_im_mycat/error.log 2>&1 &4.4 同开发测试及调优
5、部署线上环境，导入数据到线上 mycat
5.1 取一小部分数据测试验证导入 mycat
#!/bin/bash
user_im='wufei'
passwd_im='wufei'
host_im='127.0.0.1'
port_im='3215'
db_im='higo_im'
tb_im='t_im_msg'
# MYCAT连接
function mysql_im01(){
mysql -u${user_im} -p${passwd_im} -h${host_im} -P${port_im} -D${db_im}
}
import_im_msg_file='/data/soft/wufei/t_im_msg_1.sql'
# echo "show tables;" | mysql_im01
echo "source ${import_im_msg_file}" | mysql_im015.2 后台执行脚本 import_im_msg.sh 导入所有数据
nohup /bin/bash /data/soft/wufei/import_im_msg.sh /data/soft/wufei/error.log 2>&1 &5.3 与线上数据同步，实现双写持续更新数据，待开发后续更新业务
扫一扫
1604
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
