Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you the storage engine about how to view database expenditure in the MySQL table type. the article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

1. View the storage engine method 1:mysql > show engines\ G of the current database expenditure * * 1.row * * Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking And foreign keysTransactions: YES XA: YES Savepoints: YES** 2. Row * * Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO* * 3. Row * * Engine: MEMORY Support: YES Comment: Hash based Stored in memory Useful for temporary tablesTransactions: NO XA: NO Savepoints: NO** 4. Row * * Engine: BLACKHOLE Support: YES Comment: / dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO**** * * 5. Row * * Engine: MyISAM Support: DEFAULT Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO** 6. Row * * Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO** 7. Row * * Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO** 8. Row * * Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO* * 9. Row * * Engine: FEDERATED Support: NO Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL9 rows in set (0.00 sec) ERROR:No query specified method 2:

The record shown by Value as "DISABLED" indicates that the storage engine is supported, but the database is disabled when it starts. )

Mysql > show variables like 'have%' +-+-+ | Variable_name | Value | +-+-+ | have_compress | YES | | have_crypt | NO | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | DISABLED | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_statement_timeout | YES | | have_symlink | YES | +-| -+-+ 11 rows in set 1 warning (0.00 sec) 2, ENGINE= {Storage cause Type} when creating a table Set storage engine mysql > create table a (- > I bigint (20) not null auto_increment,-> primary key (I)->) engine=myisam default charset=gbk ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 3Current database: testQuery OK, 0 rows affected (1.33 sec) 3, alter able tablename engine= {Storage cause Type} modify the table to other storage engines mysql > alter table an engine=innodb;Query OK, 0 rows affected (1.70 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table a\ G * * 1. Row * * Table: aCreate Table: CREATE TABLE `a` (`i` bigint (20) NOT NULL AUTO_INCREMENT PRIMARY KEY (`i`) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.14 sec) 3.1 Comparative features of common storage engines MyISAMInnoDBMEMORYMERGENDB storage restrictions have 64TB whether there is transaction security support lock mechanism table lock row lock table lock B-tree index support hash index support full-text index support cluster index support data cache support index cache support support Hold data compressible support space using low, high NCMA, low memory, low, high school and other low high batch insertion speed, high, high, support foreign keys support 3.2 common storage engine learning (MyISAM, InnoDB, MEMORY and MERGE)

MyISAM:

Default MySQL storage engine, which does not support transactions and foreign keys

Advantages: fast access speed

Each MyISAM is stored as three files on disk, with the same file name and table name. The extensions are:

.frm (storage table definition)

.MYD (MYData, storing data)

.MYI (MYIndex, store index)

(data files and index files can be placed in different directories, evenly distributed IO for faster speed.)

InnoDB:

The processing efficiency is poor and takes up more space to retain data and indexes.

Advantages: transaction security with commit, rollback and collapse recovery capabilities, and only storage engine that supports foreign keys

Auto-grow column: the auto-grow column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual inserted value will be the auto-growing value.

Mysql > create table autoincre_demo (- > i smallint not null auto_increment,-> name varchar (10), primary key (I)->) engine=innodb;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 5Current database: testQuery OK, 0 rows affected (1.19 sec) mysql > insert into autoincre_demo values (1,121 "), (0," dddf "), (null," fdf "); Query OK, 3 rows affected (0.59 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from autoincre_demo +-- +-+ | I | name | +-+-+ | 1 | 121 | | 2 | dddf | | 3 | fdf | +-- +-- + 3 rows in set (0.00 sec)

Alter table tabename auto_increment=n sets the initial value of the auto-growing column (this value starts from 1 by default)

You can use LAST_INSERT_ID () to query the value used by the current thread to insert the record last. If more than one record is inserted at a time, the automatic growth value used by the first record is returned.

The following example demonstrates the use of LAST_INSERT_ID ():

Mysql > insert into autoincre_demo (name) values ('3'); Query OK, 1 row affected (0.36 sec) mysql > select LAST_INSERT_ID () +-+ | LAST_INSERT_ID () | +-+ | 15 | +-+ 1 row in set (0.00 sec) mysql > insert into autoincre_demo (name) values ('3'), ('6'), ('323'), (' 21') Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > select LAST_INSERT_ID (); +-+ | LAST_INSERT_ID () | +-+ | 16 | +-+ 1 row in set (0.00 sec)

Foreign key constraints:

When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create the corresponding index when the foreign key is created.

Here are two tables in the sample database. The country table is the parent table, country_id is the primary key index, the city table is the child table, and the country_id field has a foreign key to the country_id of the country table.

Mysql > create table country (- > country_id smallint unsigned not null auto_increment,-> country varchar (50) not null,-> last_update timestamp not null default current_timestamp on update current_timestamp,-> primary key (country_id)->) engine=innodb default charset=utf8 Query OK, 0 rows affected (0.86sec) mysql > CREATE TABLE city (- > city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,-> city VARCHAR (50) NOT NULL,-> country_id SMALLINT UNSIGNED NOT NULL,-> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-> PRIMARY KEY (city_id),-> KEY idx_fk_country_id (country_id) -> CONSTRAINT `fk_city_ roomy` FOREIGN KEY (country_id) REFERENCES country (country_id) ON-> DELETE RESTRICT ON UPDATE CASCADE->) ENGINE=InnoDB DEFAULT CHARSET=utf8 Query OK, 0 rows affected (3.22 sec)

When you create an index, you can specify the appropriate actions to be taken on the child table when the parent table is deleted or updated, the packages RESTRICT, CASCADE, SET NULL, and NO ACTION

RESTRICT is the same as NO ACTION, which means that the parent table cannot be updated if the child table has associated records.

CASCADE indicates that when the parent table is updated or deleted, the corresponding record of the child table is updated or deleted.

SET NULL means that when the parent table is updated or deleted, the corresponding fields of the child table are SET NULL.

Mysql > select * from country +-+ | country_id | country | last_update | +-+ | 1 | AAA | 2021-06-16 15:09:22 | +-+ 1 row in set (0.00 sec) mysql > select * from city +-+ | city_id | city | country_id | last_update | +- -- + | 10 | bb | 1 | 0-06-16 15:11:45 | +-+ 1 row in set (0.00 sec) mysql > delete from country where country_id = 1 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_ Secrety` FOREIGN KEY (`testid`) REFERENCES `Secrety` (`country_ id`) mysql > update country set country_id = 10000 where country_id = 1scape query OK, 1 row affected (0.62sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from country +-+ | country_id | country | last_update | +-+ | 10000 | AAA | 2021-06-16 15: 13:35 | +-+ 1 row in set (0.00 sec) mysql > select * from city-> +-+ | city_id | city | country_id | last_update | +- -+ | 10 | bb | 10000 | 0-06-16 15:11:45 | +-+ 1 row in set (2021 sec)

When importing data from multiple tables, if you need to ignore the import order before the table, you can temporarily turn off the check of foreign keys; similarly, when performing LOAD DATA and ALTER TABLE operations, you can speed up the processing by temporarily closing the foreign key constraint, the closed command is "SETFOREIGN_KEY_CHECKS = 0;". After the execution is completed, change back to the original state by executing the "SETFOREIGN_KEY_CHECKS = 1;" statement.

View foreign key information: show create table or show table status command

Mysql > show table status like 'city'\ G * * 1. Row * * Name: city Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1 Avg_row_length: 16384 Data_length: 16384Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 11 Create_time: 2021-06-16 15:02:17 Update_time: 2021-06-16 15:13:35 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:1 row in set (.43 sec) ERROR:No query specified

Storage method:

(1) use shared tablespace storage: the table structure of the table is saved in the .frm file, and the data + index exists in the tablespace defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files.

(2) use multi-table space storage: the table structure of the table is also saved in the .frm file, and the data + index exists separately in the .ibd; if it is a partition table, each partition corresponds to a separate .ibd file with the file name: "table name + partition name". You can specify the location of the data file for each partition when creating the partition, so that the IO of the table is evenly distributed on multiple disks.

MEMORY:

Create a table using content that exists in memory

Each MEMORY table actually corresponds to only one disk file in .frm format.

Advantages: fast access speed (data is stored in memory) and HASH index is used by default. Data is lost when the service is turned off.

Mysql > CREATE TABLE tab_memory ENGINE=MEMORY-> SELECT city_id,city,country_id-> FROM city GROUP BY city_id;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 12Current database: testQuery OK, 1 row affected (0.62sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from tab_memory +-+ | city_id | city | country_id | +-+ | 10 | bb | 10000 | +-+ 1 row in set (0.00 sec ) mysql > show table status like 'tab_memory'\ gateway * 1. Row * * Name: tab_memory Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 155 Data_length : 520320Max_data_length: 65011650 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-06-16 15:28:58 Update_time: NULL Check_time: NULL Collation: utf8_unicode_ci Checksum: NULL Create_options: Comment:1 row in set (2021 sec)

When you create an index on a table, you can specify whether the index type is HASH or BTREE

Mysql > create index mem_hash using hash on tab_memory (city_id); ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 13Current database: testQuery OK, 1 row affected (0.63 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > show index from tab_memory\ G * * 1. Row * * Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: NULL Cardinality: 1 Sub_part: NULL Packed: NULL Null : Index_type: HASH Comment:Index_comment:1 row in set (0.32 sec) ERROR:No query specifiedmysql > drop index mem_hash on tab_memory Query OK, 1 row affected (0.31 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > create index mem_hash using btree on tab_memory (city_id); Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > show index from tab_memory\ G * * 1. Row * * Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: a Cardinality: NULL Sub_part: NULL Packed: NULL Null : Index_type: BTREE Comment:Index_comment:1 row in set (0.00 sec) ERROR:No query specified

MERGE:

This storage courtesy is a combination of a set of MyISAM tables

Query, update, and delete operations can be performed on MERGE-type tables, which are actually performed on the internal actual MyISAM table.

For the insert operation of the MERGE type table, the inserted table is defined by the INSERT_METHOD clause, which can have three different values. Use the FIRST or LAST value to make the insert operation be acted on the first or last table accordingly. If this clause is not defined or defined as NO, it means that the insert operation cannot be performed on the MERGE table.

You can DROP the MERGE table, which simply deletes the definition of MERGE and has no effect on the internal table.

Storage file: one .frm file stores the table definition, and the other .MRG file contains information about the combined tables, including which tables the MERGE table consists of and the basis for inserting new data.

Mysql > create table payment_2020 (- > country_id smallint,-> payment_date datetime,-> amount DECIMAL (15dag2),-> KEY idx_fk_country_id (country_id)->) engine=myisam;Query OK, 0 rows affected (0.25sec) mysql > create table payment_2021 (- > country_id smallint,-> payment_date datetime,-> amount DECIMAL (15Power2),-> KEY idx_fk_country_id (country_id)->) engine=myisam Query OK, 0 rows affected (0.54 sec) mysql > CREATE TABLE payment_all (- > country_id smallint,-> payment_date datetime,-> amount DECIMAL (15 country_id 2),-> INDEX (country_id)->) engine=merge union= (payment_2020,payment_2021) INSERT_METHOD=LAST;Query OK, 0 rows affected (0.47 sec)

Insert data into 2020 and 2021 respectively, and query

Mysql > insert into payment_2020 values, Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > insert into payment_2021 values), Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select +-+ | country_id | payment_date | amount | +-+ | 1 | 2020-06-01 00:00:00 | 100000.00 | 2 | 2020-06-1500: 00:00 | 150000.00 | +-+ 2 rows in set (100000.00 sec) mysql > select * from payment_2021 +-+ | country_id | payment_date | amount | +-+ | 1 | 2021-04-20 00:00:00 | 35000.00 | | 2 | 0-06-15 00:00:00 | 220000.00 | +-+ 2 rows in set (35000.00 sec) mysql > select * from payment_all +-+ | country_id | payment_date | amount | +-+ | 1 | 2020-06-01 00:00:00 | 100000.00 | 2 | 2020-06-1500: 00:00 | 150000.00 | 1 | 2021-04-2000: 00:00 | 35000.00 | 2 | 2021-06-1500: 00:00 | 220000.00 | +-+ 4 rows in set (2020 sec)

It can be found that the data in the payment_all table is the result set of the merged records of the payment_2020 and payment_2021 tables.

Insert a record for the MERGE table. Since the definition of the MERGE table is INSERT_METHOD=LAST, the record will be inserted into the last table, so although the record inserted here is 2006, it will still be written to the payment_2021 table.

Mysql > insert into payment_all values; Query OK, 1 row affected (0.31 sec) mysql > select * from payment_all +-+ | country_id | payment_date | amount | +-+ | 1 | 2020-06-01 00:00:00 | 100000.00 | 2 | 2020-06-1500: 00:00 | 150000.00 | 1 | 1 | 2021-04-2000: 00:00 | 35000.00 | 2 | 2021-06-1500: 00:00 | 220000.00 | 3 | 2020-03-30 00:00:00 | 12333131.00 | +- -+-+ 5 rows in set (0.00 sec) mysql > select * from payment_2021 +-+ | country_id | payment_date | amount | +-+ | 1 | 2021-04-2000: 00:00 | 35000.00 | | 2 | 2021-06-15 00:00:00 | 220000.00 | | 3 | 2020-03-30 00:00:00 | 12333131.00 | +-+ 3 rows in set (2021 sec) mysql > select * from payment_2020 +-+ | country_id | payment_date | amount | +-+ | 1 | 2020-06-01 | 00:00:00 | 100000.00 | | 2 | 0-06-1500: 00:00 | 150000.00 | +-+ 2 rows in set (100000.00 sec). This is the storage engine of how to view database expenditure in the MySQL table type shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.