MySQL数据库性能优化之分区分表分库

MySQL数据库性能优化之分区分表分库,第1张

分表是分散数据库压力的好方法。

分表,最直白的意思,就是将一个表结构分为多个表,然后,可以再同一个库里,也可以放到不同的库。

当然,首先要知道什么情况下,才需要分表。个人觉得单表记录条数达到百万到千万级别时就要使用分表了。

分表的分类

**1、纵向分表**

将本来可以在同一个表的内容,人为划分为多个表。(所谓的本来,是指按照关系型数据库的第三范式要求,是应该在同一个表的。)

分表理由:根据数据的活跃度进行分离,(因为不同活跃的数据,处理方式是不同的)

案例:

对于一个博客系统,文章标题,作者,分类,创建时间等,是变化频率慢,查询次数多,而且最好有很好的实时性的数据,我们把它叫做冷数据。而博客的浏览量,回复数等,类似的统计信息,或者别的变化频率比较高的数据,我们把它叫做活跃数据。所以,在进行数据库结构设计的时候,就应该考虑分表,首先是纵向分表的处理。

这样纵向分表后:

首先存储引擎的使用不同,冷数据使用MyIsam 可以有更好的查询数据。活跃数据,可以使用Innodb ,可以有更好的更新速度。

其次,对冷数据进行更多的从库配置,因为更多的 *** 作时查询,这样来加快查询速度。对热数据,可以相对有更多的主库的横向分表处理。

其实,对于一些特殊的活跃数据,也可以考虑使用memcache ,redis之类的缓存,等累计到一定量再去更新数据库。或者mongodb 一类的nosql 数据库,这里只是举例,就先不说这个。

**2、横向分表**

字面意思,就可以看出来,是把大的表结构,横向切割为同样结构的不同表,如,用户信息表,user_1,user_2等。表结构是完全一样,但是,根据某些特定的规则来划分的表,如根据用户ID来取模划分。

分表理由:根据数据量的规模来划分,保证单表的容量不会太大,从而来保证单表的查询等处理能力。

案例:同上面的例子,博客系统。当博客的量达到很大时候,就应该采取横向分割来降低每个单表的压力,来提升性能。例如博客的冷数据表,假如分为100个表,当同时有100万个用户在浏览时,如果是单表的话,会进行100万次请求,而现在分表后,就可能是每个表进行1万个数据的请求(因为,不可能绝对的平均,只是假设),这样压力就降低了很多很多。

延伸:为什么要分表和分区

日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。

什么是分表?

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去 *** 作它。

什么是分区?

分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候 *** 作的还是大表名字,db自动去组织分区的数据。

**MySQL分表和分区有什么联系呢?**

1、都能提高mysql的性高,在高并发状态下都有一个良好的表现。

2、分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。

3、分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。

4、表分区相对于分表, *** 作方便,不需要创建子表。

我们知道对于大型的互联网应用,数据库单表的数据量可能达到千万甚至上亿级别,同时面临这高并发的压力。Master-Slave结构只能对数据库的读能力进行扩展,写 *** 作还是集中在Master中,Master并不能无限制的挂接Slave库,如果需要对数据库的吞吐能力进行进一步的扩展,可以考虑采用分库分表的策略。

**1、分表**

在分表之前,首先要选中合适的分表策略(以哪个字典为分表字段,需要将数据分为多少张表),使数据能够均衡的分布在多张表中,并且不影响正常的查询。在企业级应用中,往往使用org_id(组织主键)做为分表字段,在互联网应用中往往是userid。在确定分表策略后,当数据进行存储及查询时,需要确定到哪张表里去查找数据,

数据存放的数据表 = 分表字段的内容 % 分表数量

**2、分库**

分表能够解决单表数据量过大带来的查询效率下降的问题,但是不能给数据库的并发访问带来质的提升,面对高并发的写访问,当Master无法承担高并发的写入请求时,不管如何扩展Slave服务器,都没有意义了。我们通过对数据库进行拆分,来提高数据库的写入能力,即所谓的分库。分库采用对关键字取模的方式,对数据库进行路由。

数据存放的数据库=分库字段的内容%数据库的数量

**3、即分表又分库**

数据库分表可以解决单表海量数据的查询性能问题,分库可以解决单台数据库的并发访问压力问题。

当数据库同时面临海量数据存储和高并发访问的时候,需要同时采取分表和分库策略。一般分表分库策略如下:

中间变量 = 关键字%(数据库数量*单库数据表数量)

库 = 取整(中间变量/单库数据表数量)

表 = (中间变量%单库数据表数量)

实例:

1、分库分表

很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响性能,分库与分表是一个很不错的解决途径,也就是性能优化途径,现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢,同事的做法是将其散列到100个表中,分别从members0到members99,然后根据mid分发记录到这些表中,牛逼的代码大概是这样子:

复制代码 代码如下:

<?php

for($i=0$i<100$i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members

"

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

"

}

?>

2、不停机修改mysql表结构

同样还是members表,前期设计的表结构不尽合理,随着数据库不断运行,其冗余数据也是增长巨大,同事使用了下面的方法来处理:

先创建一个临时表:

/*创建临时表*/

CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表结构为新结构,接着使用上面那个for循环来导出数据,因为1000万的数据一次性导出是不对的,mid是主键,一个区间一个区间的导,基本是一次导出5万条吧,这里略去了

接着重命名将新表替换上去:

/*这是个颇为经典的语句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members

就是这样,基本可以做到无损失,无需停机更新表结构,但实际上RENAME期间表是被锁死的,所以选择在线少的时候 *** 作是一个技巧。经过这个 *** 作,使得原先8G多的表,一下子变成了2G多。

右键单击“mysql”的“dos窗口”上面的 蓝色横条

选择编辑->标记->然后按住鼠标左键选中你要修改的语句

再去右击蓝色横条

选择编辑->复制就把这条你要修改的语句复制到剪贴板了

然后去记事本里粘贴修改!

在记事本里写好命令复制

然后右键单点“mysql”的“dos窗口”上面的 蓝色横条

编辑->粘贴

然后执行

当然这只是windows下的办法,要一次执行很多行代码的时候比较省事~

所谓按天,不过是日期精确到天而已。

错误的按日期分区例子

最直观的方法,就是直接用年月日这种日期格式来进行常规的分区:

mysql>  create table rms (d date)  

    ->  partition by range (d)  

    -> (partition p0 values less than ('1995-01-01'),  

    ->  partition p1 VALUES LESS THAN ('2010-01-01'))

上面的例子中,就是直接用"Y-m-d"的格式来对一个table进行分区,可惜想当然往往不能奏效,会得到一个错误信息:

ERROR 1064 (42000): VALUES value must be of same type as partition function near '),

partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3

上述分区方式没有成功,而且明显的不经济,老练的DBA会用整型数值来进行分区:

mysql> CREATE TABLE part_date1  

    ->      (  c1 int default NULL,  

    ->  c2 varchar(30) default NULL,  

    ->  c3 date default NULL) engine=myisam  

    ->      partition by range (cast(date_format(c3,'%Y%m%d') as signed))  

    -> (PARTITION p0 VALUES LESS THAN (19950101),  

    -> PARTITION p1 VALUES LESS THAN (19960101) ,  

    -> PARTITION p2 VALUES LESS THAN (19970101) ,  

    -> PARTITION p3 VALUES LESS THAN (19980101) ,  

    -> PARTITION p4 VALUES LESS THAN (19990101) ,  

    -> PARTITION p5 VALUES LESS THAN (20000101) ,  

    -> PARTITION p6 VALUES LESS THAN (20010101) ,  

    -> PARTITION p7 VALUES LESS THAN (20020101) ,  

    -> PARTITION p8 VALUES LESS THAN (20030101) ,  

    -> PARTITION p9 VALUES LESS THAN (20040101) ,  

    -> PARTITION p10 VALUES LESS THAN (20100101),  

    -> PARTITION p11 VALUES LESS THAN MAXVALUE )

Query OK, 0 rows affected (0.01 sec)

搞定?接着往下分析

mysql> explain partitions  

    -> select count(*) from part_date1 where  

    ->      c3> '1995-01-01' and c3 <'1995-12-31'\G  

*************************** 1. row ***************************  

           id: 1  

  select_type: SIMPLE  

        table: part_date1  

   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11  

         type: ALL  

possible_keys: NULL  

          key: NULL  

      key_len: NULL  

          ref: NULL  

         rows: 8100000  

        Extra: Using where  

1 row in set (0.00 sec)

万恶的mysql居然对上面的sql使用全表扫描,而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区,花了大量的篇幅来引诱俺走上歧路,过分。

正确的日期分区例子

mysql优化器支持以下两种内置的日期函数进行分区:

TO_DAYS()

YEAR()

看个例子:

mysql> CREATE TABLE part_date3  

    ->      (  c1 int default NULL,  

    ->  c2 varchar(30) default NULL,  

    ->  c3 date default NULL) engine=myisam  

    ->      partition by range (to_days(c3))  

    -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),  

    -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,  

    -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,  

    -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,  

    -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,  

    -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,  

    -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,  

    -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,  

    -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,  

    -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,  

    -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),  

    -> PARTITION p11 VALUES LESS THAN MAXVALUE )

Query OK, 0 rows affected (0.00 sec)

以to_days()函数分区成功,我们分析一下看看:

mysql> explain partitions  

    -> select count(*) from part_date3 where  

    ->      c3> date '1995-01-01' and c3 <date '1995-12-31'\G  

*************************** 1. row ***************************  

           id: 1  

  select_type: SIMPLE  

        table: part_date3  

   partitions: p1  

         type: ALL  

possible_keys: NULL  

          key: NULL  

      key_len: NULL  

          ref: NULL  

         rows: 808431  

        Extra: Using where  

1 row in set (0.00 sec)

可以看到,mysql优化器这次不负众望,仅仅在p1分区进行查询。在这种情况下查询,真的能够带来提升查询效率么?下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比:

mysql> select count(*) from part_date3 where  

    ->      c3> date '1995-01-01' and c3 <date '1995-12-31'  

+----------+  

| count(*) |  

+----------+  

|   805114 |  

+----------+  

1 row in set (4.11 sec)  

   

mysql> select count(*) from part_date1 where  

    ->      c3> date '1995-01-01' and c3 <date '1995-12-31'  

+----------+  

| count(*) |  

+----------+  

|   805114 |  

+----------+  

1 row in set (40.33 sec)

可以看到,分区正确的话query花费时间为4秒,而分区错误则花费时间40秒(相当于没有分区),效率有90%的提升!所以我们千万要正确的使用分区功能,分区后务必用explain验证,这样才能获得真正的性能提升。

注意:

在mysql5.1中建立分区表的语句中,只能包含下列函数:

ABS()

CEILING() and FLOOR() (在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型),例如

mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6) -> ) ERROR 1491 (HY000): The PARTITION function returns the wrong type   mysql> CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6) -> ) Query OK, 0 rows affected (0.01 sec)  

DAY()  

DAYOFMONTH()  

DAYOFWEEK()  

DAYOFYEAR()  

DATEDIFF()  

EXTRACT()  

HOUR()  

MICROSECOND()  

MINUTE()  

MOD()  

MONTH()  

QUARTER()  

SECOND()  

TIME_TO_SEC()  

TO_DAYS()  

WEEKDAY()  

YEAR()  

YEARWEEK()


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/6182647.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-17
下一篇 2023-03-17

发表评论

登录后才能评论

评论列表(0条)

保存