首页 > 代码库 > 高性能mysql 第7章 mysql高级特性之分区表

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

2024-10-03 23:40:39   216人阅读

分区表：

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

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

 

 

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


联系
我们