mysql分表场景分析与简单分表操作.docxVIP

mysql 分表场景分析与简单分表操作 为什么要分表 首先要知道什么情况下,才需要分表个人觉得单表记录条数达到百万到千万级别时就要使用分表了,分表的目的就在于此，减小数据库的负担，缩短查询时间. 表分割有两种方式： 水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。水平分割通常在下面的情况下使用: 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。 需要把数据存放到多个介质上。 水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要 union 操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大， 则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。 垂直分割：把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。 如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割 可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少 I/O 次数。 其缺点是需要管理冗余列，查询所有数据需要join 操作。 场景案例: 博客系统垂直分割: 文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。 而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。 所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。这样纵向分表后： 首先存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb ,可以有更好的更新速度。 其次，对冷数据进行更多的从库配置，因为更多的操作是查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。 其实，对于一些特殊的活跃数据，也可以考虑使用 memcache ,redis 之类的缓存，等累计到一定量再去更新数据库. 水平分割: 当博客的量达到很大时候，就应该采取横向分割来降低每个单表的压力，来提升性能。例如博客的冷数据表，假如分为 100 个表，当同时有 100 万个用户在浏览时，如果是单 表的话，会进行100 万次请求，而现在分表后，就可能是每个表进行1 万个数据的请求（因为，不可能绝对的平均，只是假设），这样压力就降低了很多很多。 使用 Merge 存储引擎展示水平分表实例： 查看 mysql 的存储引擎 mysql show engines \G; 现实场景模拟 第一步: 创建表 member DROP table IF EXISTS member; create table member( id bigint auto_increment primary key, name varchar(20), sex tinyint not null default 0 )ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 第二步:创建存储过程,插入百万数据 #如果存在已定义的存储过程 inserts,删除掉drop procedure IF EXISTS inserts; #自定义结束符delimiter // #创建存储过程 create procedure inserts() begin DECLARE i int; set i = 1; WHILE(i = 10) DO insert into member(name,sex) values(concat(name,i),i%2); SET i = i+1; END WHILE; end; #使用自定义结束符结束存储过程定义 // #还原结束符为; delimiter ; #调用存储过程call inserts(); MySQL 的语法默认使用分号;作为一条 SQL 语句结束的标志.可以使用 delimiter 命令将其修改成其他符号，如：delimiter // 表示以//作为提交符号. 为了演示分表,所以实例中插入 10 条数据模拟. 第三步:创建分表 #分表 1# DROP table IF EXISTS tb_member1; create table tb_member1( id bigint primary key auto_increment , name varchar(20), sex tinyint not null default 0 )ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=