mysql 如何使用 mysql 如何使用分区扫描

转载

mob64ca14089531 2024-01-11 16:47:10

文章标签 mysql 如何使用分区表 mysql to_days partition 文章分类 MySQL 数据库

测试mysql扫描分区和分区索引

先做一些测试，指定分区字段，查看执行计划。

测试环境数据库版本为mysql5.7
创建分区表

CREATE TABLE employes (
     id INT NOT NULL ,
     fname VARCHAR (30),
     lname VARCHAR (30),
     hired DATE NOT NULL DEFAULT '1970-01-01' ,
     separated DATE NOT NULL DEFAULT '9999-12-31' ,
     job_code INT NOT NULL ,
     store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
     PARTITION p0 VALUES LESS THAN (6),
     PARTITION p1 VALUES LESS THAN (11),
     PARTITION p2 VALUES LESS THAN (16),
     PARTITION p3 VALUES LESS THAN (21),
     PARTITION p4 VALUES LESS THAN (26),
     PARTITION p5 VALUES LESS THAN (31),
     PARTITION p6 VALUES LESS THAN (36),
     PARTITION p7 VALUES LESS THAN (41),
     PARTITION p8 VALUES LESS THAN (46),
     PARTITION p9 VALUES LESS THAN (51),
     PARTITION p10 VALUES LESS THAN (56),
     PARTITION p11 VALUES LESS THAN (61),
     PARTITION p12 VALUES LESS THAN (66),
     PARTITION p13 VALUES LESS THAN (71),
     PARTITION p14 VALUES LESS THAN (76),
     PARTITION p15 VALUES LESS THAN (81),
     PARTITION p16 VALUES LESS THAN (86),
     PARTITION p17 VALUES LESS THAN (91),
     PARTITION p18 VALUES LESS THAN MAXVALUE
);

过滤条件为分区键时的执行计划

mysql> explain  select store_id from employes where store_id=17;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employes | p3         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql>  explain  select store_id from employes  where store_id>=5;
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions                                                        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employes | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--在分区建上套函数，执行计划便不能分别走哪个分区
mysql> explain  select store_id from employes where left(store_id,1)=1;
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions                                                        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employes | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+-------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

用to_days函数创建以时间为分区键的分区表

CREATE TABLE employeees (
     id INT NOT NULL ,
     fname VARCHAR (30),
     lname VARCHAR (30),
     hired DATE NOT NULL DEFAULT '1970-01-01' ,
     separated DATE NOT NULL DEFAULT '9999-12-31' ,
     job_code INT NOT NULL ,
     store_id INT NOT NULL
)
PARTITION BY RANGE (TO_DAYS(hired))
(PARTITION p1 VALUES LESS THAN (738156) ,
 PARTITION p2 VALUES LESS THAN (738157) ,
 PARTITION p3 VALUES LESS THAN MAXVALUE 
 );

--分区键有to_days函数的情况下，分区字段不加函数，可以分辨到具体的分区
mysql> explain select * from employeees where hired='1970-01-01';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employeees | p1         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

#在分区键有to_days函数的情况下，分区字段用substr，mysql不可用分辨到具体的分区
mysql> explain select * from employeees where SUBSTR(hired,1,10)='1970-01-01';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employeees | p1,p2,p3   | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

分区表和索引使用关键点：

1.mysql分区表仅支持本地索引

2.partitions可以看到sql走了哪些分区

3.当需要查询的分区较多时，还是会走指定分区（下面的案例条件为>=‘分区字段的值’，没有走索引，我以为是分区扫描太多了。实际上分区是分区，分区索引是分区索引，两个概念）

4.用to_days函数时，字段不加任何函数可以走分区。用其他函数不能走到具体分区

5.分区扫描必须只定分区键字段。当然sql也可以指定某分区扫描，这种情况比较少

分区表sql优化案例

查到一个慢sql，该sql仅扫描了一个分区表，并且过滤条件中包含分区键

其中sql关键部分截取如下

FROM lzltab WHERE SUBSTR(lzl_time,1,10) >= DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) GROUP BY vendor_name,interface_name,SUBSTR(lzl_time,1,10)

执行计划

+----+-------------+------------------+------+---------------+------+---------+------+----------+----------------------------------------------+

|  1 | PRIMARY     | <derived2>       | ALL  | NULL          | NULL | NULL    | NULL | 43290542 | Using filesort                               |

|  2 | DERIVED     | <derived3>       | ALL  | NULL          | NULL | NULL    | NULL | 43290542 | Using filesort                               |

|  3 | DERIVED     | lzltab | ALL  | NULL          | NULL | NULL    | NULL | 43290542 | Using where; Using temporary; Using filesort |

+----+-------------+------------------+------+---------------+------+---------+------+----------+---------------------------------------------

当前实例数据库版本为mysql5.6，执行计划没有展示partitions信息，是看不到扫描了哪些分区的

该sql也没有走索引

从开始的测试结果来看，该sql应该是全扫描所有分区的

表结构截取部分如下

PRIMARY KEY (`ID`,`lzl_time`),

  KEY `index_interfaceid` (`lzl_time`,`INTERFACE_ID`,`RESPONSE_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT

/*!50100 PARTITION BY RANGE (TO_DAYS(lzl_time))

(PARTITION p20201231 VALUES LESS THAN (738156) ENGINE = InnoDB,

 PARTITION p20210101 VALUES LESS THAN (738157) ENGINE = InnoDB,

 PARTITION p20210102 VALUES LESS THAN (738158) ENGINE = InnoDB,

分区字段是lzl_time，跟sql的过滤条件字段是一致的，区别在于函数不同。分区键为TO_DAYS(lzl_time)，sql条件为SUBSTR(lzl_time,1,10)

sql没有走索引的根本原因是索引在lzl_time上，sql过滤条件为SUBSTR(lzl_time,1,10)。在索引字段上加任何函数都不能走索引

而mysql5.7及以下不支持函数索引(该实例mysql版本为5.6），所以只能改写sql

其中

WHERE SUBSTR(lzl_time,1,10) >= DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY)

等价于

WHERE lzl_time >= DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY)

sql改写后，可以走索引，sql运行时间在1s以内

+----+-------------+------------------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+

| id | select_type | table            | type  | possible_keys     | key               | key_len | ref  | rows | Extra                                        |

+----+-------------+------------------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+

|  1 | PRIMARY     | <derived2>       | ALL   | NULL              | NULL              | NULL    | NULL |    2 | Using filesort                               |

|  2 | DERIVED     | <derived3>       | ALL   | NULL              | NULL              | NULL    | NULL |    2 | Using filesort                               |

|  3 | DERIVED     | lzltab | range | index_interfaceid | index_interfaceid | 5       | NULL |    1 | Using where; Using temporary; Using filesort |

+----+-------------+------------------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+

mysql5.6还看不出sql扫描了哪些分区，这个sql从测试上来，理论上可以仅需要扫描一个分区，走该分区的本地索引index_interfaceid

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。