• 分区列和索引列不匹配。例如在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高级特性之分区表

标签：possible   ati   历史   type   内存   ssi   lamp   asp   code