MYSQL分表
水平分割:根据一列或多列数据的值把数据行放到两个独立的表中。
水平分割通常在下面的情况下使用。
表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度。
表中的数据本来就有独立性,例如表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。
需要把数据存放到多个介质上。
水平分割会给应用增加复杂度,它通常在查询时需要多个表名,查询所有数据需要union操作。在许多数据库应用中,这种复杂性会超过它带来的优点,因为只 要索引关键字不大,则在索引用于查询时,表中增加两到三倍数据量,查询时也就增加读一个索引层的磁盘次数。
2垂直分割:把主码和一些列放到一个表,然后把主码和另外的列放到另一个表中。
如果一个表中某些列常用,而另外一些列不常用,则可以采用垂直分割,另外垂直分割可以使得数据行变小,一个数据页就能存放更多的数据,在查询时就会减少I/O次数。其缺点是需要管理冗余列,查询所有数据需要join操作。
Mysql常见水平分表方案
根据经验,mysql表数据一般达到百万级别,查询效率会很低,容易造成表锁,甚至堆积很多连接,直接挂掉;水平分表能够很大程度较少这些压力。
1.按时间分表
这种分表方式有一定的局限性,当数据有较强的实效性,如微博发送记录、微信消息记录等,这种数据很少有用户会查询几个月前的数据,如就可以按月分表。
2.按区间范围分表
一般在有严格的自增id需求上,如按照user_id水平分表:
table_1 user_id从1~100w
table_2 user_id从101~200w
table_3 user_id从201~300w
…
3.hash分表
通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表。
按如下分10张表:
[代码]php代码:
function get_hash_table($table, $userid)
{
s t r = c r c 32 ( str = crc32( str=crc32(userid);
if ($str < 0) {
h a s h = " 0 " . s u b s t r ( a b s ( hash = "0" . substr(abs( hash="0".substr(abs(str), 0, 1);
} else {
h a s h = s u b s t r ( hash = substr( hash=substr(str, 0, 2);
}
return $table . “_” . $hash;
}
echo get_hash_table(‘message’, ‘user18991’); //结果为message_10
echo get_hash_table(‘message’, ‘user34523’); //结果为message_13
另外,介绍我现在就是采用简单的取模分表:
[代码]php代码:
/**
- @param string $table_name 表名
- @param int $user_id 用户id
- @param int $total 分表总数
- @link http://www.phpddt.com
*/
function hash_table($table_name, $user_id, $total)
{
return KaTeX parse error: Expected group after '_' at position 15: table_name . '_̲' . ((user_id % $total) + 1);
}
echo hash_table(“artice”, 1234, 5); //artice_5
echo hash_table(“artice”, 3243, 5); //artice_4
4.利用merge存储引擎分表
感觉merge存储引擎类似sql中union的感觉,但是查询效率不高。
如下举例,拥有1000w记录的old_user表分表:
(1)创建new_user表使用merge存储引擎
[代码]sql代码:
mysql> CREATE TABLE IF NOT EXISTS user1
(
-> id
int(11) NOT NULL AUTO_INCREMENT,
-> name
varchar(50) DEFAULT NULL,
-> sex
int(1) NOT NULL DEFAULT ‘0’,
-> PRIMARY KEY (id
)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS user2
(
-> id
int(11) NOT NULL AUTO_INCREMENT,
-> name
varchar(50) DEFAULT NULL,
-> sex
int(1) NOT NULL DEFAULT ‘0’,
-> PRIMARY KEY (id
)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO user1
(name
, sex
) VALUES(‘张映’, 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user2
(name
, sex
) VALUES(‘tank’, 1);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS new_user
(
-> id
int(11) NOT NULL AUTO_INCREMENT,
-> name
varchar(50) DEFAULT NULL,
-> sex
int(1) NOT NULL DEFAULT ‘0’,
-> INDEX(id)
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select id,name,sex from new_user;
±—±-------±----+
| id | name | sex |
±—±-------±----+
| 1 | 张映 | 0 |
| 1 | tank | 1 |
±—±-------±----+
2 rows in set (0.00 sec)
mysql> INSERT INTO new_user
(name
, sex
) VALUES(‘tank2’, 0);
Query OK, 1 row affected (0.00 sec)
mysql> select id,name,sex from user2
-> ;
±—±------±----+
| id | name | sex |
±—±------±----+
| 1 | tank | 1 |
| 2 | tank2 | 0 |
±—±------±----+
2 rows in set (0.00 sec)
(2)我old_user数据进行分表:
[代码]sql代码:
1 INSERT INTO user1(user1.id,user1.name,user1.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id <= 5000000
2 INSERT INTO user2(user2.id,user2.name,user2.sex) SELECT (user.id,user.name,user.sex)FROM old_user where user.id > 10000000
php + mysql 实现分表功能
为了减轻对海量数据访问时对数据库的压力我们可以这样做
1:先对数据库先进行分表(提供分库分表规则和路由规则(RouteRule简称RR))
2:对数据库分库
3:引入集群(Group)的概念,保证数据的高可用性,高安全性,解决单点问题;
4:引入负载均衡策略(LoadBalancePolicy简称LB);
5:引入读/写分离,提高数据的查询速度;
6:基于云端的分布式数据存储
我今天在php下对mysql进行了一下分表,我采用的是水平分表法。值得我们注意的是在mysql中我们使用的insert into newtabname select * from oldtabname where x between??a and b (需要先自己建表)语法和在 sql server 中使用insert into 的时候一样。或者使用 create table newtabname(select * from oldtabname).newtabname是不需要我们去手动创建的。
下面提供我的一段源码,支持两种方案
<php
分表
c o n = m y s q l c o n n e c t ( ′ l o c a l h o s t ′ , ′ r o o t ′ , ′ r o o t ′ ) ; m y s q l s e l e c t d b ( " t e s t " , con = mysql_connect('localhost','root','root'); mysql_select_db("test", con=mysqlconnect(′localhost′,′root′