mysql高级特性之数据分区

Mysql  /  houtizong 发布于 3年前   160

mysql高级特性  1 以存储引擎的角度分析，分区表和物理表没有区别。是按照一定的规则将数据分别存储的逻辑设计。器底层是由多个物理字表组成。    2 分区的原理     分区表由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们可以直接访问各个分区。存储引擎管理分区的各个底层 表和管理普通表一样(所有底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。 从存储引擎角度来看，底层表和一个普通表没有任何的不同，存储引擎也无需知道这是一个普通表还是一个分区表。 在分区表中 索引已经失去作用，添加索引只会增加服务器的负担。如果发现某一些索引仍会起作用那也只是短暂的 回光返照。随着数据规模的增加，这些索引必然会成为负担。   3 分区表的类型    1 根据范围进行分区2 根据实践间隔分区3 mysql还支持键值、哈希、列表分区，这其中还支持子分区。4 还可以根据数学模函数来进行分区。  4 如何使用分区表    在分区表中，索引在空间和维护上的消耗巨大，尤其是当表非常大的时候，特别不希望使用索引，即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机IO，应用程序也随之僵死。有时候也可以通过一两个索引解决一些问题，不过多数情况下，索引不会有任何作用，这时候有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存中。这里需要再陈述一遍，在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表查询所有符合条件的记录，如果数据量巨大，这将产生大量随机IO，随之，数据库的响应时间将大到不可接受。另外，索引维护(磁盘空间、IO操作)的代价也非常高。这正是分区所要做的事情。理解分区时还可以将其当做索引的最初形态，以待价非常小的方式定位到需要的数据在那一片区域。在这片区域中，你可以做顺序扫描，可以建索引，还可以将数据都缓存到内存等。因为分区无需额外的数据结构记录每个分区有哪些数据，分区不需要精确定位每条数据的位置，也就无需额外的数据结构，只需要一个简单的表达式就可以表达每个分区存放的是什么数据。为了保证大数据量的可扩展性，一般有下面两个策略：全量扫描数据，不需要任何索引。 可以使用简单的分区方式存放表，不需要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用where条件，将需要的数据限制在少数分区 中，则效率是非常高的。使用该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全都在磁盘上，因为内存相对很小 数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。 索引数据，并分离热点。  如果某部分数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，  让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。     5 分区的陷阱     前面介绍的分区策略都基于两个非常重要的假设：查询都能够过滤掉很多额外的分区、分区本身不会带来很多额外的代价。    事实是这两个假设在某些场景下会有问题。1)null值会使分区过滤无效  mysql分区时会创建第一个分区，用于存放分区列为null 或非法的记录。  例如 按照 partition by year(order_date) 来分区，那么order_date为null 或者是一个非法的值得时候，  记录都会被放入第一个分区。假设存在下面的查询 where order_date between 2012-09-08 and 2012-08-07  那么这个查询会检查2012这个分区，同时他还会检查第一个分区，检查第一个分区是因为year接受的参数  可能是null或者非法数值，如果是Null或者非法的话就会被放入第一个分区。    同时，如果第一个分区非常大，特别是使用 全量扫描数据，不使用索引的策略时，代价会非常大。    为了避免这种情况，我们可以创建一个无用的第一个分区，例如上面的例子可以使用  partition p_nulls values less than (0) 来创建第一个分区，如果插入的数据都是有效的，那么  第一个分区就是空的，即使需要检测第一个分区，代价也会非常小。    在mysql 5.5就不需要这个技巧了，因为可以直接使用列本身而不是基于列的函数进行分区。  partition by range columns(order_date).   2) 分区列和索引列不匹配    如果定义的索引咧和分区列不匹配，会导致查询无法进行分区过滤。假如在b列上建立索引，在a列上进行分区。因为每个分区都有其独立的索引，所以扫描列a上的索引就需要扫描每一个分区内对应的索引。要避免这种情况，就要避免建立和分区不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。   3) 选择分区的成本可能很高    对于使用范围来分区的情况来说，查询的成本可能会非常高。这一行属于哪个分区，这些符合查询条件的行分布在那些区找到这样的回答的代价可能会非常高。随着分区数的增长，成本会越来越高。在按行写入大量数据的时候，每写入一行数据到范围分区的表时，都需要扫描分区定义列表来找到合适的分区。可以通过限制分区的数量来缓解这类问题，对于大多数系统来说100个分区是没有问题的。其他的分区类型，比如键分区和哈希分区，则没有这样的问题。 4) 打开并锁住所有底层表的成本可能很高    当查询访问分区表的时候，mysql需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对于一些本身操作非常快的查询，比如根据主键查找单行，会带来明显的额外开销。我们可以通过使用批量操作的方式来降低这个操作带来的开销。同时还需要限制分区的个数。 5) 维护分区的成本可能很高