mysql-timing-to-table partitioning

1, partition

Specifically visible: http://blog.csdn.net/open_data/article/details/46893331

1, Partition type:

Range Partition: Assigns multiple rows to a partition based on column values that belong to a given contiguous interval.

List partitioning: Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.

Hash partition: A partition that is selected based on the return value of a user-defined expression that is evaluated using the column values of those rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value.

Key partitioning: Similar to partitioning by hash, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

2), why partition

To improve scalability, manageability, and database efficiency for large tables and tables with various access patterns.

Some of the benefits of partitioning include:

• More data can be stored than a single disk or file system partition.
• For data that has lost its meaning, it is often possible to delete those data by deleting the partitions associated with those data. Conversely, in some cases, the process of adding new data can be easily implemented by adding a new partition specifically for those new data. Other benefits that are typically related to partitioning include those listed below. These features in the MySQL partition are not yet implemented, but we have high priority in our priority list, and we want to include these features in the 5.1 production version.
• Some queries can be greatly optimized, mainly by the fact that the data that satisfies a given where statement can be guaranteed to exist in only one or more partitions, so that no other remaining partitions are found at the time of the lookup. Because partitions can be modified after the partition table has been created, the data can be re-organized when the partitioning scheme is first configured, to improve the efficiency of those common queries.
• Queries that involve aggregate functions such as SUM () and count () can be processed easily in parallel. A simple example of such a query such as "Select Salesperson_id, COUNT (orders) as Order_total from sales GROUP by salesperson_id;". By "parallelism," this means that the query can be performed concurrently on each partition, and the final result will only be obtained by totaling all partitions.
• Gain greater query throughput by dispersing data queries across multiple disks.

2, Event

Specifically visible:

http://blog.csdn.net/jesseyoung/article/details/35257527

Https://www.cnblogs.com/zoro-zero/p/6511203.html

2.1. View the open status of the event

Show variables like '%event_schedule% ';

2.2, change the event to Enabled

Set global Event_scheduler = on;

2.3 Viewing scheduled events

Show Processlist;

2.4 Viewing events

Show events;

2.5 Deleting events

Event if exists event_second;  

　　

Add a scheduled partition task

1), CREATE table and initialize partition (must have initialization partition)

DROP TABLE IF EXISTS ' Jmx_sta '; CREATE TABLE ' Jmx_sta ' (' Host ' varchar ( -) DEFAULT NULL COMMENT'Host', ' time ' TIMESTAMP NULL DEFAULT'0000-00-00 00:00:00',    `Object' VarChar ( +) DEFAULT NULL COMMENT'Object', ' attribute ' varchar ( +) DEFAULT NULL COMMENT'Properties', ' value ' DOUBLE, PRIMARY KEY (' Host ', ' time ', 'Object' , ' attribute ')) ENGINE=innodb charset=utf8partition by RANGE (Unix_timestamp (Time)) (PARTITION p20171231 VALUES less THAN (Unix_timestamp ('2017-12-31 23:59:59') , PARTITION p20180101 VALUES less THAN (Unix_timestamp ('2018-01-01 23:59:59') , PARTITION p20180102 VALUES less THAN (Unix_timestamp ('2018-01-02 23:59:59')));

2), add stored procedure

DELIMITER $ $USE ' nres ' $ $DROP PROCEDURE IF EXISTS ' create_partition_jmx_status ' $ $CREATE definer= ' iris ' @ '%' PROCEDURE ' create_partition_jmx_status ' () BEGIN/*The transaction rollback, actually put here does not have the function, ALTER table is implicitly commits, cannot rollback. */DECLARE EXIT HANDLER for SQLEXCEPTION ROLLBACK; START TRANSACTION;/*to the system table, isolate the maximum partition for this table, and get the maximum partition date. When you create a partition, the name is stored in a date format to facilitate later maintenance*/SELECT REPLACE (Partition_name,'P',"') into the @P12_Name from INFORMATION_SCHEMA. Partitions WHERE table_name='Jmx_sta'ORDER by Partition_ordinal_position DESC LIMIT1; SET @Max_date= DATE (Date_add (@P12_Name +0, INTERVAL1Day)) +0;/*Modify table, add a partition after the maximum partition, time range plus 1 days*/SET @s1=concat ('ALTER TABLE jmx_sta ADD PARTITION (PARTITION p', @Max_date,'VALUES Less THAN (to_days (" ", DATE (@Max_date)," ")))'); /*Output View Add partition statement*/SELECT @s1;    PREPARE stmt2 from @s1;    EXECUTE stmt2; Deallocate PREPARE stmt2;/*Remove the name of the smallest partition and remove it. Note: Deleting a partition also deletes the data in the partition, carefully*/    /*Select Partition_name to @P0_Name from INFORMATION_SCHEMA.    partitions where table_name= ' tb_3a_huandan_detail ' ORDER by partition_ordinal_position limit 1;    SET @s=concat (' ALTER TABLE tb_3a_huandan_detail DROP PARTITION ', @P0_Name);    PREPARE STMT1 from @s;    EXECUTE STMT1; Deallocate PREPARE STMT1; *//*Submit*/COMMIT; end$ $DELIMITER;

3, adding timed events

DELIMITER | | CREATE EVENT Partition_jmx_sta_perday     On SCHEDULE     1'2018-01-02 23:59:50'do     BEGIN    | | DELIMITER;

mysql-timing-to-table partitioning