水平分表

最新推荐文章于 2024-06-13 21:38:35 发布

huizhang.

最新推荐文章于 2024-06-13 21:38:35 发布

阅读量6.2k

点赞数 2

分类专栏： mysql php 文章标签：分表水平分割 mysql 数据库

本文链接：https://blog.csdn.net/qq_34908844/article/details/79862381

版权

php 同时被 2 个专栏收录

132 篇文章 0 订阅

订阅专栏

mysql

13 篇文章 0 订阅

订阅专栏

概述

当一个表的数据很大，比如200G，这时太大，我们只靠索引也不好使了，这时我们需要分表和分区处理。分表有两种形式（水平分表和垂直分表）。

一、水平分表

　　核心思想：把一个大表，分割N个小表，小表和大表结构一样，只是把数据分散到不同的表中。

简单例子：比如说是通过ID直接登录（例如QQ号），可以直接使用下面的例子

这里写图片描述

每次登录验证的时候只要把传过来的Id除3取模，根据模可以找到对应的表，然后再去对应的表做查询操作，以下为php的相关操作，注册以及登录。

<?php
 2     //接收参数
 3     //测试的参数有：Pwd，Name， Email
 4     extract($_POST);
 5 
 6     //检查是否为空
 7     if(empty($Pwd) || empty($Name) || empty($Email)){
 8         die('参数不能为空！');
 9     }
10 
11     //连接数据库
12     $link = mysql_connect('127.0.0.1:3306','root', 'root');
13     if(!$link){
14         die('数据库连接失败！');
15     }
16 
17     //选择数据库:这个数据库有：uuid表（产生Id的表）、User0表（存放模为0的用户信息）、User1表（存放模为1的用户信息）和User2表（存放模为2的用户信息）
18     mysql_select_db('test');
19 
20     $sql = 'INSERT INTO uuid VALUES(null)';
21     if(mysql_query($sql, $link)){
22         //获取刚刚插入的Id
23         $id = mysql_insert_id();
24 
25         //根据 Id%3 来确定该新用户存放的表
26         $table_name = 'User'.$Id%3;
27 
28         $pwd = md5($Pwd);
29         $sql = "INSERT INTO $table_name VALUES($id, $Name, $pwd, $Email )";
30 
31         if(mysql_query($sql, $link)){
32             echo '注册成功！';
33         }else{
34             echo '注册失败！';
35         }
36     }

 1 <?php
 2     header("Content-Type:text/html;charset:utf-8");
 3     
 4     //接收参数:Id, Pwd
 5     extract($_POST);
 6 
 7     //判断是否为空
 8     if(empty($Id) || empty($Pwd)){
 9         die('参数不能为空！');
10     }
11 
12     //连接数据库
13     $link = mysql_connect('127.0.0.1:3306', 'root', 'root');
14     if(!$link){
15         die('连接失败！');
16     }
17 
18     //选择数据库
19     mysql_select_db('test');
20 
21     $table_name = 'User'.$Id%3;
22     $sql = "SELECT * FROM $table_name WHERE Id = $Id";
23     $rst = mysql_query($sql, $link);
24 
25     if($row = mysql_fetch_assoc($rst)){
26         $db_pwd = $row['Pwd'];
27         
28         if($db_pwd == md5($Pwd)){
29             echo '登录成功！';
30         }else{
31             echo '用户名或者密码错误！';
32         }
33     }else{
34         echo 'Id错误！';
35     }

通过邮箱登录，通过邮箱对表进行分割

这里写图片描述

邮箱基本原理都是和Id差不多，就是要通过一个算法把md5字符串转成十进制的数，然后再取模，以下为把十六进制的md5字符串转成十进制的函数。

这里写图片描述

mysql 按日期水平分表之后的数据查询方式以及分页策略

问题描述

mysql数据库在数据量较大的情况下，对数据表进行水平分表，按照年份，如下：

data_2013
data_2014
data_2015
…………

目前的解决方案

在这种情况下的数据查询我暂时的解决方案是对每个数据库进行循环查询，然后返回每个数据表符合查询条件的数据，并且将查询到的数据合并到一个数组中，渲染到模板：

for($i = 0;$i<=$n;$i++)
            {
                //对日期时间进行处理
                if($i == 0) $map['d.collected_time'] = array('EGT',$start_date);
                if($i == $n) $map['d.collected_time'] = array('ELT',$end_date);

                $data = M('data_'.($start_year+$i));
//                dump($map);
                //对数据总量进行统计
                $count += $data
                    ->join('monitor_point p ON d.point_id = p.point_id')
                    ->join('hydro h ON h.hydro_id = p.hydro_id')
                    ->join('monitor_type t ON d.monitor_type_id = t.monitor_type_id')
                    ->join('agency a ON p.agency_id = a.id')
                    ->table('data_'.($start_year+$i).' d')
                    ->field('d.*,p.*,t.*,a.*,h.*')
                    ->where($map)
                    ->count();
                //不同年份的数据进行组合
                $page = new \Think\MyPage($count,30);
                $this->page = $page->show();
                $result_data[$i] = $data
                    ->join('monitor_point p ON d.point_id = p.point_id')
                    ->join('hydro h ON h.hydro_id = p.hydro_id')
                    ->join('monitor_type t ON d.monitor_type_id = t.monitor_type_id')
                    ->join('agency a ON p.agency_id = a.id')
                    ->table('data_'.($start_year+$i).' d')
                    ->field('d.*,p.*,t.*,a.*,h.*')
                    ->where($map)
                    ->limit($page->firstRow.','.$page->listRows)
                    ->select();
//                echo $data->_sql();
                //先检查是否是数组再进行添加或者合并操作。
//                $result_data[$i]是一个未知元素数量的一维数组，需要对其循环并添加到新的数组中去。
                 $result_data_num = count($result_data[$i]);
                for($j = 0;$j<$result_data_num;$j++)
                {
                    array_push($years_data,$result_data[$i][$j]);
                }
            }

使用的是thinkphp框架。但是上述的解决方案问题在于，返回每个数据表的前30条数据，如果要查询2013、2014两年的数据，他就会返回每个年份的前30条数据，总共60条。而且在显示的时候是先显示13年的，然后显示14年的，按照我们的查询方式，这个方式也确实没有问题，但是它是不符合我们的问题解决方法的。

新问题

通过谷歌搜索“mysql 水平分表之后按年份查询”找到一种解决办法：

SELECT d.*,p.*,t.*,a.*,h.* FROM data_2013 d 
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
 WHERE ( d.point_id IN ('16') ) AND ( d.collected_time >= '2013-01-01+00:00' )
UNION
SELECT d.*,p.*,t.*,a.*,h.* FROM data_2014 d 
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
WHERE ( d.point_id IN ('16') ) AND ( d.collected_time <= '2014-01-24+09:50' )
LIMIT 0,30

但是这个解决方案在于他在分页方面是在最后限制的，也就是两条查询语句只能用一个限制语句，现在需要一个好的分页策略。
对于上述两种解决方案，其实都是需要解决分页的问题.

目前正在尝试中的方案

select * from 
(SELECT d2013.* FROM data_2013 d2013
WHERE ( d2013.point_id IN ('16') ) AND ( d2013.collected_time <= '2014-01-24+09:50' ) 
UNION
SELECT d2014.* FROM data_2014 d2014 
WHERE ( d2014.point_id IN ('16') ) AND ( d2014.collected_time >= '2013-01-01+00:00' )
) d
INNER JOIN monitor_point p ON d.point_id = p.point_id 
INNER JOIN hydro h ON h.hydro_id = p.hydro_id 
INNER JOIN monitor_type t ON d.monitor_type_id = t.monitor_type_id 
INNER JOIN agency a ON p.agency_id = a.id 
ORDER BY d.collected_time 
LIMIT 0,30

最终的解决方案

在for循环中，对需要查询的年份构建子查询，然后将每次查询的sql语句组合成为一个数组(array_push)，最后用implode(’ union ‘,$union_sql)用union组合成为总的sql语句，然后，照着上面给出的sql语句，将总的子查询语句添加进去，再加入排序、分页等~很美妙~虽然今早6.30就被38°的太阳刺眼到睡不着，早早过来做，用了一上午做好的……
最后的分页控制

$years_data = $union_model->table('('.$subQuery.') d')
                ->field('d.*)
                ->limit($page->firstRow.','.$page->listRows)
                ->order('d.collected_time')
                ->select();

优缺点

1水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。
　　水平分割通常在下面的情况下使用。
　　•表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
　　•表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
　　•需要把数据存放到多个介质上。
水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。　　

参考地址

http://www.dewen.net.cn/q/17048/mysql+%E6%8C%89%E6%97%A5%E6%9C%9F%E6%B0%B4%E5%B9%B3%E5%88%86%E8%A1%A8%E4%B9%8B%E5%90%8E%E7%9A%84%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2%E6%96%B9%E5%BC%8F%E4%BB%A5%E5%8F%8A%E5%88%86%E9%A1%B5%E7%AD%96%E7%95%A5

还有一个链接。。找不到了，如有侵权请留言。

huizhang.

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
1
评论
水平分表

概述当一个表的数据很大，比如200G，这时太大，我们只靠索引也不好使了，这时我们需要分表和分区处理。分表有两种形式（水平分表和垂直分表）。一、水平分表　　核心思想：把一个大表，分割N个小表，小表和大表结构一样，只是把数据分散到不同的表中。简单例子：比如说是通过ID直接登录（例如QQ号），可以直接使用下面的例子每次登录验证的时候只要把传过来的Id...
复制链接

扫一扫