高性能mysql 第7章 mysql高级特性之分区表

分区表:

分区表是一个独立的逻辑表,底层通过多个物理表实现。

mysql实现分区表的方式是对底层表的封装。这意味着没有全局索引,索引是建立在底层的每个表上的(跟ORACLE不一样)。

用到分区表的几种情况:

  • 数据量非常大,无法全部放到内存中。
  • 只有部分数据是热点数据,其他数据是历史数据。

限制:

  • 一个表只能有1024个分区,作者在100个以下是稳定的,太多会有性能问题。
  • 分区表无法使用外键约束。
  • PARTITION BY RANGE分区表达式必须是整数或者返回整数的表达式。
  • PARTITIONED BY RANGE COLUMNS分区可以接受整形,日期型,字符型作为分区参数。
  • 所有的分区都必须使用相同的存储引擎。

会出现的问题:

  • 如果使用PARTITION BY RANGE,所有在分区表达式上的null值(本身就是null或者使用一个非法值的时候)会被放在第一个分区。在做查询的时候,不仅会查询where所在的分区,还会查询第一个null所在的分区。如果第一个分区很大,那么将会有性能隐患。解决方法,把第一个分区的数据量减少。或者做一个空分区为第一个分区。

附上PARTITION BY RANGE例子:

  1. CREATE TABLE employees (
  2. id INT NOT NULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATE NOT NULL DEFAULT '1970-01-01',
  6. separated DATE NOT NULL DEFAULT '9999-12-31',
  7. RANGE Partitioning
  8. 3001
  9. job_code INT NOT NULL,
  10. store_id INT NOT NULL
  11. )
  12. PARTITION BY RANGE (store_id) (
  13. PARTITION p0 VALUES LESS THAN (6),
  14. PARTITION p1 VALUES LESS THAN (11),
  15. PARTITION p2 VALUES LESS THAN (16),
  16. PARTITION p3 VALUES LESS THAN (21)
  17. )
  • 分区列和索引列不匹配。例如在a列上建立了分区,在b列上建立了索引,在使用b=?进行检索的时候,如果没有a的条件来限制要扫描的分区,那么将要在每个分区上查找b索引。如果分区比较多,也是性能隐患。

 

EXPLAIN PARTITIONS 查看使用了哪些分区

  1. CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
  2. PARTITION BY RANGE(id)
  3. (
  4. PARTITION p0 VALUES LESS THAN (3),
  5. PARTITION p1 VALUES LESS THAN (7),
  6. PARTITION p2 VALUES LESS THAN (9),
  7. PARTITION p3 VALUES LESS THAN (11)
  8. );
  9. (1, 'desk organiser', '2003-10-15'),
  10. (2, 'CD player', '1993-11-05'),
  11. Obtaining Information About Partitions
  12. 3044
  13. (3, 'TV set', '1996-03-10'),
  14. (4, 'bookcase', '1982-01-10'),
  15. (5, 'exercise bike', '2004-05-09'),
  16. (6, 'sofa', '1987-06-05'),
  17. (7, 'popcorn maker', '2001-11-22'),
  18. (8, 'aquarium', '1992-08-04'),
  19. (9, 'study desk', '1984-09-16'),
  20. (10, 'lava lamp', '1998-12-25');
  21. mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G
  22. *************************** 1. row ***************************
  23. id: 1
  24. select_type: SIMPLE
  25. table: trb1
  26. partitions: p0,p1,p2,p3
  27. type: ALL
  28. possible_keys: NULL
  29. key_len: NULL
  30. ref: NULL
  31. rows: 10
  32. Extra: Using filesort
  33. mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
  34. *************************** 1. row ***************************
  35. id: 1
  36. select_type: SIMPLE
  37. table: trb1
  38. partitions: p0,p1
  39. type: ALL
  40. possible_keys: NULL
  41. key_len: NULL
  42. ref: NULL
  43. rows: 10
  44. Extra: Using where

 

posted on 2017-06-10 15:41  张小贱1987  阅读(277)  评论(0编辑  收藏  举报

导航