MySQL表分区的理论和实践

来源：互联网发布：青岛历史湿度数据查询编辑：程序博客网时间：2024/05/18 18:42

当面临大数据存储时，数据库的性能往往成为了瓶颈。
除了增加服务器做主从库之外，数据库自身也有很多需要优化的地方。
在减少查询范围的工作中，很多人采取了分表的方式。
比如建立用户表100个，分别为users_00到users_99。
很多公司都采取了这样做的方法，比如1亿数据，拆到每个表就是100万，查询会快很多。
分表法在物理上看，肯定是多表了，自然会快，但是后期很难扩展，比如要加一个user_100，以前的hash公式就废了
而且对用户不透明，PHP代码啥的要写一壶了。
除了上面那种分发，还有用merge表的，merge表对用户我个人认为这个东西只适合做数据仓库
因为首先它是MyISAM引擎，没有行级锁，其次是字段变更比较麻烦，维护起来比较复（其实我也没用过）。

然后说表分区。首先在物理层，全部是每个区分文件的。这样可以突破IO瓶颈。
这两天测试了以下，不同的分区锁表时不影响其它分区的查询。这样并发瓶颈也可以突破。
而且是InnoDB引擎，行锁很舒服。
最主要的是，除了在information schema库里面需要关注以下以外，代码和数据库维护方面完全不需要了，代码完全透明
不过这次我们所选择的针对于用户账户搜索的字段却不是透明的，我们需要对账户进行hash然后选择分区，这里就需要封装到Model里了
而且分区可以随时改，虽然目前不知道更改效率如何，但也是一条语句就能搞定的事情。

在确保了这些问题之后，确定使用分区来做。
假设我要做一张用户表，首先用垂直切，按照功能切成用户账户表，用户资料表，用户订单表等表。
虽然垂直分区可以使用，但MySQL5.5的版本还不能使用……基于使用稳定版的愿望，我还是使用垂直分表而不是垂直分区。
我的第六感告诉我，垂直分区+水平分区，很有可能带来笛卡尔积的计算复杂度，所以，作为PHPer的我还是用可控性较高的垂直分表来做。

垂直分表+水平分区这个问题确定后。
然后要确定分区算法。
MySQL自带的三种分别为range，list和hash
range感觉可以，list就算了，hash听起来比较酷。
range方法需要自己写边缘，对于分区比较少的时候，比较好用，如果分区多的话，便要写好长好长。
所以这里我采用hash的方法

建表语句如下

?
Create1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees (
    idINT NOT NULL,
    fnameVARCHAR(30),
    lnameVARCHAR(30),
    hiredDATE NOT NULL DEFAULT '1970-01-01',
    separatedDATE NOT NULL DEFAULT '9999-12-31',
    job_codeINT,
    store_idINT
)
PARTITION BYHASH(store_id)
PARTITIONS 4;

这是官方手册的语句，它的ID并不是主键，和计划有些出入……
不过，字段比较清晰的，第一个从句里面hash方法的参数是一个表达式，表达式的结果必须是整数，这里面表示以store_id作为参数进行分区、
这个表达式并不是乱写的，官方手册有下面一段话，关于如何提高效率和让每个区容量相近
In other words, the more closely the graph of the column value versus the value of the expression follows a straight line as traced by the equation y=cx where c is some nonzero constant, the better the expression is suited to hashing. This has to do with the fact that the more nonlinear an expression is, the more uneven the distribution of data among the partitions it tends to produce.线性关系的表达式更适合哈希算法，越紧接线性算法，每个分区的数据越平均。
In theory, pruning is also possible for expressions involving more than one column value, but determining which of such expressions are suitable can be quite difficult and time-consuming. For this reason, the use of hashing expressions involving multiple columns is not particularly recommended.表达式不要太复杂，否则效率低。
普通的哈希是取余计算 N = MOD(expr, num).
第二个从句表示分了四个区，默认是1,如果是0则会报错。这里面必须是正整数。
为了方便实践，我们自己建表

?
Create1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOTEXISTS `pusers` (
  `id`int(11) NOTNULL AUTO_INCREMENT,
  `fname`varchar(64) NOT NULL,
  `lname`varchar(64) NOT NULL,
  `signed`int(11) NOTNULL,
  PRIMARYKEY (`id`, `signed`)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BYHASH(signed)
PARTITIONS 10;

我用的加密算法如下，本来ascii直接加的算法全部是70-100还有0-10的结果，优化后是这个样子，差不多保持了每个分区的数据相近

?
encrypt1
2
3
4
5
6
7
8
9
10
protected function ecrypt($str)
{
    $str1= substr($str, 0, 1);
    $number= abs(ord($str1) - 97) % 4;
    $str2= substr($str, 1, 1);
    $number= $number * 25 + ord($str2) - 97;
    $number= $number % 100;
 
    return$number;
}

用压力测试ab插了100万数据，然后开始测试。
用signed+fname的效率是单纯搜fname的10倍左右（非explain，直接搜）。
不过这个倍率看起来不太美妙，因为名字我们没有加索引。
虽然在很多高级搜索的功能中，我们的确不能加太多索引，但为了测试，我一边插着数据一边加了索引

?
index1
ALTER TABLE pusers ADDINDEX fn (fname);

然后我explain里下面两条语句
mysql> explain select * from pusers where fname = 'Vmed';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | pusers | ref | fn | fn | 194 | const | 9 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+

mysql> explain select * from pusers where fname = 'Vmed' and signed = 95;
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | pusers | ref | fn | fn | 194 | const | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
可以看出，虽然都很快，都是用了const的索引……（是我数据量太少了啊），但是一个查寻了9行而另一个只查了一行。
然后是右模糊搜索，同样是索引。我们也只能这么做了，因为下图99的数字就是根据Pw而来的。
mysql> explain select * from pusers where fname LIKE 'Pwy%' and signed=99;
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | pusers | range | fn | fn | 194 | NULL | 114 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

我本来想，既然和索引差不多，那就直接用索引好了。
但是，索引本身扔到内存中是非常耗费内存的。
这个表现在有500W的数据，索引已经200多M了。当我们的服务器内存较小，表又很大的时候，分区确实是不错的选择。
mysql> select * from pusers where lname='Eywrodz';
+---------+-------+---------+--------+
| id | fname | lname | signed |
+---------+-------+---------+--------+
| 5999926 | Bzb | Eywrodz | 0 |
+---------+-------+---------+--------+
1 row in set (2.62 sec)

mysql> select * from pusers where lname='Eywrodz' and signed=0;
+---------+-------+---------+--------+
| id | fname | lname | signed |
+---------+-------+---------+--------+
| 5999926 | Bzb | Eywrodz | 0 |
+---------+-------+---------+--------+
1 row in set (0.09 sec)

在分区No0（第1分区）中，有18万数据，没有索引的lname字段来搜索，速度相差30倍

0 0