Technology Sharing | How MySQL Limits the Number of Records in a Table

Author: Yang Taotao

Senior database expert, specializing in MySQL for more than ten years. Good at backup and recovery related to open source databases such as MySQL, PostgreSQL, MongoDB, SQL tuning, monitoring operation and maintenance, and high-availability architecture design. Currently working at Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial companies.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

background

This article is also a question from a customer consultation: Can you control the number of records in a single table in a fixed number, for example, 1W, and prevent inserting new records or throwing errors directly?

Regarding this question, there is no simplified answer. For example, executing a command or simply setting a parameter cannot be a perfect solution. Next, I give some alternative solutions.

text

For databases, there are nothing more than two solutions to general problems, one is on the application side; the other is on the database side.

The first is on the database side (assuming that the table has a hard limit of 1W records):

1. Trigger solution:

The idea of the trigger is very simple. Before inserting a new record, check whether the number of table records reaches the limit. If the number is not reached, continue to insert; if the number is reached, insert a new record first, then delete the oldest record, or vice versa. also. In order to avoid scanning the total number of records in each test table, plan another table to be used as the counter of the current table. Before inserting, you only need to check the counter table. To achieve this requirement, two flip-flops and a counter table are required.

t1 is the table that needs to limit the number of records, t1_count is the counter table:

mysql:ytt_new>create table t1(id int auto_increment primary key, r1 int);
Query OK, 0 rows affected (0.06 sec)
   
mysql:ytt_new>create table t1_count(cnt smallint unsigned);
Query OK, 0 rows affected (0.04 sec)
   
mysql:ytt_new>insert t1_count set cnt=0;
Query OK, 1 row affected (0.11 sec)

You have to write two triggers, one is the insert action trigger:

DELIMITER $$

USE `ytt_new`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_insert`$$

CREATE
    /*!50017 DEFINER = 'ytt'@'%' */
    TRIGGER `tr_t1_insert` AFTER INSERT ON `t1` 
    FOR EACH ROW BEGIN
       UPDATE t1_count SET cnt= cnt+1;
    END;
$$

DELIMITER ;

The other is the delete action trigger:

DELIMITER $$

USE `ytt_new`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_delete`$$

CREATE
    /*!50017 DEFINER = 'ytt'@'%' */
    TRIGGER `tr_t1_delete` AFTER DELETE ON `t1` 
    FOR EACH ROW BEGIN
       UPDATE t1_count SET cnt= cnt-1;
    END;
$$

DELIMITER ;

Create 1W data for table t1, reaching the upper limit:

mysql:ytt_new>insert t1 (r1) with recursive tmp(a,b) as (select 1,1 union all select a+1,ceil(rand()*20) from tmp where a<10000 ) select b from tmp;
Query OK, 10000 rows affected (0.68 sec)
Records: 10000  Duplicates: 0  Warnings: 0

The counter table t1_count is recorded as 1W.

mysql:ytt_new>select cnt from t1_count;
+-------+
| cnt   |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

It is necessary to determine whether the counter table reaches the limit before inserting. If this limit is reached, the old records will be deleted first. I write a stored procedure to simply sort out the logic:

DELIMITER $$

USE `ytt_new`$$

DROP PROCEDURE IF EXISTS `sp_insert_t1`$$

CREATE DEFINER=`ytt`@`%` PROCEDURE `sp_insert_t1`(
    IN f_r1 INT
    )
BEGIN
      DECLARE v_cnt INT DEFAULT 0;
      SELECT cnt INTO v_cnt FROM t1_count;
      IF v_cnt >=10000 THEN
        DELETE FROM t1 ORDER BY id ASC LIMIT 1;
      END IF;
      INSERT INTO t1(r1) VALUES (f_r1);          
    END$$

DELIMITER ;

At this point, call the stored procedure to achieve:

mysql:ytt_new>call sp_insert_t1(9999);
Query OK, 1 row affected (0.02 sec)

mysql:ytt_new>select count(*) from t1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

The processing logic of this stored procedure can also continue to be optimized for one batch processing. For example, if the number of table records is doubled each time, the judgment logic becomes 2W before only inserting new records, and not deleting old records. When 2W records are reached, the old 1W records are deleted at once.

This scheme has the following shortcomings:

1. The record update of the counter table is triggered by insert/delete. If the table is truncate, the counter table does not trigger an update, and the data is inconsistent.
2. If you perform a drop operation on the table, the trigger is also deleted, you need to rebuild the trigger and reset the counter table.
3. Write to the table can only be a single entry like a stored procedure, not other entries.

Second, the partition table solution

Create a range partition. The first partition has 1W records, and the second partition is the default partition. After the number of table records reaches the limit, delete the first partition and readjust the partition definition.

The initial definition of the partition table:

mysql:ytt_new>create table t1(id int auto_increment primary key, r1 int) partition by range(id) (partition p1 values less than(10001), partition p_max values less than(maxvalue));
Query OK, 0 rows affected (0.45 sec)

Find if the first partition is full:

mysql:ytt_new>select count(*) from t1 partition(p1);
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

Delete the first partition and re-adjust the partition table:

mysql:ytt_new>alter table t1 drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql:ytt_new>alter table t1 reorganize partition p_max into (partition p1 values less than (20001), partition p_max values less than (maxvalue));
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

The advantages of this method are obvious:

1. The table insert entry can be very random, INSERT statements, stored procedures, and import files are all acceptable.
2. Deleting the first partition is a DROP operation, which is very fast.

But there are also disadvantages: table records cannot have gaps, if there are gaps, you have to change the partition table definition. For example, if the maximum value of partition p1 is changed to 20001, even if half of the records in this partition are not continuous, it will not affect the total number of records in the search partition.

Three, general table space solution

Calculate in advance how much disk space is required for 1W records of this table, and then divide a zone on the disk to store the data of this table.

Mount the partition and add it as an alternative directory (/tmp/mysql/) for the InnoDB tablespace.

mysql:ytt_new>create tablespace ts1 add datafile '/tmp/mysql/ts1.ibd' engine innodb;
Query OK, 0 rows affected (0.11 sec)
mysql:ytt_new>alter table t1 tablespace ts1;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

I roughly figured it out, it's not very accurate, so there may be a bit of error in the record, but the meaning is clear: wait for the report "TABLE IS FULL".

mysql:ytt_new>insert t1 (r1) values (200);
ERROR 1114 (HY000): The table 't1' is full

mysql:ytt_new>select count(*) from t1;
+----------+
| count(*) |
+----------+
|    10384 |
+----------+
1 row in set (0.20 sec)

After the table is full, remove the table space, empty the table, and insert new records.

mysql:ytt_new>alter table t1 tablespace innodb_file_per_table;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql:ytt_new>drop tablespace ts1;
Query OK, 0 rows affected (0.13 sec)

mysql:ytt_new>truncate table t1;
Query OK, 0 rows affected (0.04 sec)

The other is processing on the application side:

Table data can be cached on the application side in advance, and then written to the database side in batches after the limited number of records is reached, and the table can be cleared before writing to the database.

For example: the table t1 data is cached in the file t1.csv. When t1.csv reaches 1W rows, the database side clears the table data and imports t1.csv.

Concluding remarks

Before MySQL in the MyISAM era, the table attribute max_rows used to estimate the number of records in the table, but it is not a hard and fast rule, similar to what I wrote above, using a general table space to limit the number of table records; in the InnoDB era, there is no intuitive way , More is to rely on the methods listed above to solve this problem, which scheme to choose, still depends on the needs.