Mysql大表的水平切分

清华大佬耗费三个月吐血整理的几百G的资源,免费分享!....>>>

#!/bin/bash
user=""
password=""
database=""
table=""
rows=$(mysql -u$user -p$password $database -e "select DISTINCT EXTRACT(YEAR_MONTH from (ActionTime)) as 'month' from $table;")
#echo $rows
table_name=''
for tag in ${rows[@]}
do
  #echo "$tag"
  if [ $tag = "month" ];then
   continue
  fi
  table_name="$table_name""$table""_""$tag"" "; 
done
echo "${table_name}"


for table_i in ${table_name[@]}
do
  mysql -u$user -p$password $database << EOF 2>/dev/null
  CREATE TABLE $table_i(
   表结构
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

EOF
[ $? -eq 0 ] && echo "Created table $table_i" || echo "Table $table_i  already exist" 
  temp=${table_i##*_}
  startdate=${temp:0:4}'-'${temp:4}'-01'
  enddate=${temp:0:4}'-'${temp:4}'-31'
  echo "startdate=${startdate}, enddate=${enddate}" 
  
  total_rows=$(mysql -u$user -p$password $database -N -e "select count(*) from $table where ActionTime BETWEEN '$startdate' and '$enddate';")
  echo '总行数='"${total_rows}"
  page_size=1000
  page_num=0
  mod=`expr $total_rows % $page_size`
  echo '取摸='"${mod}"
  if [ $mod -gt 1 ];then
    page_num=`expr $total_rows / $page_size + 1`
  else
    page_num=`expr $total_rows / $page_size`
  fi
  echo '总页数='"${page_num}"

add_size=0
for((i=0;i<$page_num;i++))
do
  mysql -r$root -p$password $database << EOF 2>/dev/null
  INSERT INTO $table_i SELECT * FROM PageView WHERE $table.ActionTime BETWEEN '$startdate' AND '$enddate' LIMIT $add_size,$page_size;
EOF
add_size=`expr $add_size + $page_size`
done
done
echo "sharding $table successful!"