Mysql大表的水平切分

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#!/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!"