Introduction to mysql memory Engine

Introduction to mysql database memory Engine? Application Scenario: the database needs to be queried dynamically at a high speed, which is more dynamic than memcached .? How to view the engine of your current database. Mysqlshowengines; the result is in the attachment .?? HEAP Introduction: (heap is actually a memory engine .)

Introduction to mysql database memory Engine? Application Scenario: the database needs to be queried dynamically at a high speed, which is more dynamic than memcached. ? How to view the engine of your current database. Mysql show engines; the result is in the attachment .? ? HEAP Introduction: (heap is actually a memory engine .)

Introduction to the memory engine of mysql

MysqlDatabaseMemoryEngine Introduction

?

Application Scenario: the database needs to be queried dynamically at a high speed, which is more dynamic than memcached.

?

How to view the engine of your current database.

Mysql> show engines;

The result is in the attachment.

?

? HEAP Introduction: (heap is actually a memory engine .)

HEAP allows only temporary tables in memory. The HEAP is faster than MYISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data.

??????????????????

Features:

???????? 1.2 Storage features of the MEMORY Engine

The data and index data of the MEMORY engine are stored in the MEMORY. Therefore, the file system only has a separate table definition file, for example, the MEMMORY engine table: t_memory, which is only available in the database directory: the t_memory.frm file stores all its data in the memory. Except that the table definition information has a corresponding object file stored on the disk, as long as the mysqld service does not exist, all the data in the table is lost.

The DELETE operation on the MEMORY engine table does not actually DELETE the data, but marks it as the deletion status. Only the new record INSERT and the same table can be reused, in addition, rebuilding, resetting, deleting tables, and restarting the mysqld service will release the memory occupied by the deleted data.

1.3 Support for Data Types

1>. All types except BLOB, TEXT and their variants are supported;

2>. Use a fixed-length field type, that is, VARCHAR (50) is equivalent to CHAR (50 );

3>. Supports creating auto-incrementing sequence fields;

4>. The field can be NULL, and the index can also contain NULL fields;

1.4 index support

1.5 What is the storage space range ?? The storage space owned by the MEMORY storage engine depends on the value of the global variable max_heap_table_size.

? Usage:

Create table t (I INT) ENGINE = MYISAM;
Note: MySQL of earlier versions uses TYPE instead of ENGINE (for example, TYPE = MYISAM ). MySQL 5.1 supports this syntax for backward compatibility, but TYPE is currently ignored, and ENGINE is the first usage.

? Test:

Ii. Trigger knowledge points

2.1 syntax

When the data in the table changes through SQL (INSERT, UPDATE, DELETE) Statements, the program that can capture this change is a commonly used trigger, which is similar to a stored procedure, programs with special functions.

2.1.1 create

CREATE [DEFINER = {user | CURRENT_USER}]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name for each row trigger_stmt

2.1.2 Delete

Drop trigger [if exists] [schema_name.] trigger_name

2.2 Restrictions

1>. A trigger program with the same name cannot exist in a database;

2>. There cannot be two or more triggers to respond to the same event on the same table;

3>. The trigger cannot display the call execution, nor can it display and PASS Parameters like a function or stored procedure;

4>. The VALUE obtained by the keyword OLD. column_name cannot be modified using the SET command, but the VALUE obtained by the keyword NEW can be modified using the set new. column_name = VALUE method;

5>. The processing part of the trigger cannot contain transaction keywords, such as ROLLBACK;

6>. the table where the trigger is created. If transactions are supported, the trigger will also be affected by whether the transaction is successful or failed, and whether the trigger program is successfully executed or fails, it also affects whether the transaction execution is successful or failed. if the transaction is not supported, the rollback operation of the transaction is not supported;

3. Simulated business scenarios

1. A large amount of data UPDATE operations that meet the requirements of a table, and a short response time;

2. ensure data security;

3. To meet the above requirements without additional development costs, you need to read data from non-MEMORY engine tables to MEMORY engine tables;

4. Only UPDATE business operations are performed on data in the MEMORY engine table;

5. Update Data in the InnoDB Engine table by using a trigger;

The test procedure is as follows:

1>. Create two tables for testing. The storage engines are MEMORY and InnoDB respectively.

Create table 't_ memory '(

? 'Id' int unsigned not null AUTO_INCREMENT,

? 'Username' VARCHAR (40) not null,

? Primary key ('id '),

? Unique key 'idx _ username' ('username ')

) ENGINE = memory default charset = utf8? COLLATE 'utf8 _ general_ci ';

?

? Create table 't_ innodb '(

? 'Id' int unsigned not null AUTO_INCREMENT,

? 'Username' VARCHAR (40) not null,

? Primary key? ('Id '),

? Unique key 'idx _ username' ('username ')

) ENGINE = InnoDB default charset = utf8 COLLATE 'utf8 _ general_ci ';

2>. add several test data entries

Insert into t_innodb (username) value ('A'), ('asdfasdf '), ('q235423573sdf'), ('afdhfhswqertqw ');

Insert into t_memory SELECT * from t_innodb;

3>. Create a MEMORY engine-based table update trigger

DELIMITER &&

Create trigger tri_memory_update after update on t_memory FOR EACH ROW

BEGIN?

??? UPDATE t_innodb SET username = NEW. username WHERE username = OLD. username;

END &&

DELIMITER;

4>. Compare the data in two tables

Root @ localhost: test 03:58:25> select * from t_memory;

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

| Id | username ?????? |

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

|? 1 | ????????????? |?

|? 2 | asdfasdf ?????? |?

|? 3 | q235423573sdf? |?

|? 4 | afdhfhswqertqw |?

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

4 rows in set (0.00 sec)

?

Root @ localhost: test 03:58:32> select * from t_innodb;

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

| Id | username ?????? |

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

|? 1 | ????????????? |?

|? 4 | afdhfhswqertqw |?

|? 2 | asdfasdf ?????? |?

|? 3 | q235423573sdf? |?

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

4 rows in set (0.00 sec)

5>. Perform the update simulation operation on the MEMORY engine table.

UPDATE t_memory SET username = '769067806dfgh' where id = 1;

6>. Comparison of updated data

Root @ localhost: test 03:58:25> select * from t_memory;

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

| Id | username ?????? |

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

|? 1 | 769067806 dfgh? |?

|? 2 | asdfasdf ?????? |?

|? 3 | q235423573sdf? |?

|? 4 | afdhfhswqertqw |?

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

4 rows in set (0.00 sec)

Root @ localhost: test 03:58:32> select * from t_innodb;

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

| Id | username ?????? |

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

|? 1 | 769067806 dfgh? |?

|? 4 | afdhfhswqertqw |?

|? 2 | asdfasdf ?????? |?

|? 3 | q235423573sdf? |?

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

4 rows in set (0.00 sec)

?

7>. Restart the mysqld service and view and compare the data in two tables.

Root @ localhost: test 04:10:05> select * from t_innodb;

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

| Id | username ?????? |

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

|? 1 | 769067806 dfgh? |?

|? 4 | afdhfhswqertqw |?

|? 2 | asdfasdf ?????? |?

|? 3 | q235423573sdf? |?

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

4 rows in set (0.02 sec)

Root @ localhost: test 04:10:12> select * from t_memory;

Empty set (0.00 sec)

If you use MEMORY engine tables to support online services and use triggers or other programs similar to triggers to synchronize data to the entity table, we recommend the following from the perspective of MEMORY engine features and services:

1> in the MEMORY engine storage table, only numeric and date fields and TIMESTAMP fields are used. Do not use strings;

2> after the data in the memory engine table is deleted, use the statement alter table tablename ENGINE = MEMORY to release the MEMORY occupied by the deleted data;

3>. Only MEMORY engine tables are used to support UPDATE operations;

4> for data operations on MEMORY engine tables, it is best to perform operations based on the primary key so that the operation can be completed as quickly as possible without affecting the operations of other threads;

5>. Do not use MEMORY engine tables to support services with a large amount of data, such as 1 GB or more data;

6>. Clear unnecessary data in the MEMORY engine table on a regular basis to free up MEMORY;

7> set the value of the max_heap_table_size parameter properly. Consider the possible size of system resources and data volume;

8>. the MEMORY engine does not support transactions. For this reason, when using a trigger to complete data synchronization, consider the response time point (BEFOR/AFTER). Because operations may be frequent, we recommend that you use an engine that supports transactions for entity tables, such as the InnoDB engine;

?

?