Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

[ultra-simple] MySQL storage engine selection and configuration

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

[ultra-simple] introduction to the selection and configuration of MySQL storage engine

Data in MySQL is stored in files (or memory) using a variety of different technologies. Each technology uses different storage institutions, indexing techniques, locking levels, and ultimately provides a wide range of functions and capabilities. By selecting different technologies, additional speed or functionality can be obtained, thus improving the overall functionality of the application. These different technologies and related functions are called storage engines in MySQL.

Here is an introduction to two common storage engines in MySQL: MyISAM and InnoDB

MyISAM storage engine

MyISAM storage engine is the default storage engine before version 5.5 of MySQL relational database system. Its predecessor is ISAM,ISAM, which performs read operations very fast and does not occupy a lot of memory and storage resources. But it also has its shortcomings: ①, does not support transactions; ②, can not be fault-tolerant, that is, if the hard disk crashes, then the data file can not be repaired, unless all the real-time data is backed up frequently, through its replication feature, MySQL can support such backup applications.

The characteristics of MyISAM storage engine do not support transactions, and systems that need transaction support cannot use MyISAM as the form of table-level locking of storage engine. The whole table database is locked when data is updated, and the whole table database blocks each other during reading and writing, that is, it cannot be written while reading, and the cached index can be set through key_buffer_size when writing, so as to improve access performance and reduce the pressure on disk IO. MyISAM storage engine does not support foreign key constraints. Only full-text indexing is supported. Each MyISAM is stored as three files on disk:

.frm file storage table definition

.MYD (MYData) data file

.MYI (MYIndex) index file

Based on the characteristics of MyISAM, MyISAM is mainly suitable for places where non-highly concurrent reads and writes do not need to be carried out at the same time, such as electronic shopping malls.

InnoDB storage engine

InnoDB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The characteristics of the InnoDB storage engine support transactions and support four transaction isolation level row-level locking, but full table scanning will still be table-level locking read-write blocking related to transaction isolation level has a very efficient cache feature, can cache indexes, but also cache data tables and primary keys stored in clusters, that is, there are foreign key constraints to support partitions, tablespaces, similar to Oracle databases

Based on the characteristics of InnoDB, InnoDB is mainly suitable for places with high concurrency, such as forums, Weibo, banks, etc.

Enterprise chooses storage engine to lock type indexes based on fields and data types supported by storage engine, supports transaction processing, supports database and table storage engine view

1. View the storage engine currently used by the database

Mysql > use school; / / enter school database Database changedmysql > show engines / / check the storage engine used by school by default, here because I am using the MySQL5.7 version So the default is whether the InnoDB engine type can be represented as the current default engine +-whether it supports transactions. -+ | Engine | Support | Comment | | Transactions | XA | Savepoints | + -+ | InnoDB | DEFAULT | Supports transactions Row-level locking, and foreign keys | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | MEMORY | YES | Hash based, stored in memory Useful for temporary tables | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | | MyISAM | YES | MyISAM storage engine | NO | | CSV | | YES | CSV storage engine | NO | | ARCHIVE | YES | Archive storage engine | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | | | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | +-| -+ 9 rows in set (0.01 sec)

two。 View the storage engine that the table is using

①, using show table status; directly is shown below

Mysql > mysql > show table status from school where name='info' +-+ | Name | Engine | Version | Row_format | Rows | +-+ | info | InnoDB | 10 | Dynamic | 1 | +-+ 1 row in set (0.00 sec) / / omit part of the content

Here you can see that the storage engine used by info is InnoDB

②, enter the database where the table is located, and then use the show create command; the figure is as follows

Mysql > use school;Database changedmysql > show create table info +-+-+ | Table | Create Table | +-+-+ | info | CREATE TABLE "info" ("name" char (10) DEFAULT NULL "score" decimal (5 sec 2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+-+ 1 row in set (0.00 sec)

You can also see that the storage engine for info tables is InnoDB.

Modification of database storage engine

There are also four ways to modify it.

①, modify using the alter table command (here only for existing tables or databases) is shown below

Mysql > use school; / / enter school database Database changedmysql > show create table info / / View the storage engine currently in use +- -- + | Table | Create Table | +-+- -+ | info | CREATE TABLE "info "(" name "char (10) DEFAULT NULL "score" decimal (5 DEFAULT NULL 2) ENGINE=InnoDB DEFAULT CHARSET=utf8 | / / you can see the +-+-of the default InnoDB type used here. -+ 1 row in set (0.00 sec) mysql > alter table info engine=MyISAM / / use the alter table command to modify Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table info / / check again the type of storage engine used by the info table +-+- -- + | Table | Create Table | +-+- -+ | info | CREATE TABLE info ("name" char (10) DEFAULT NULL) "score" decimal (5 DEFAULT NULL 2) ENGINE=MyISAM DEFAULT CHARSET=utf8 | / / you can see here that the storage engine has been modified to MyISAM type +-+ -+ 1 row in set (0.00 sec)

②, modify the MySQL configuration file / etc/my.cnf, and specify the default-storage-engine option to set the default storage engine (here for subsequent newly created tables)

[root@localhost ~] # vim / etc/my.cnf... Omit [mysqld]... Omit pid-file = / usr/local/mysql/mysqld.piddefault-storage-engine=MyISAM / / add the specified default storage engine type MyISAMsocket = / usr/local/mysql/mysql.sock... After omitting the modification of the configuration file, we need to restart the MySQL service > [root@localhost ~] # systemctl restart mysqld.service. After restarting the MySQL service, we enter the database again and then create a new table. After viewing the storage engine mysql > create database test; / / create a new database testQuery OK, 1 row affected (0.01 sec) mysql > use test; / / enter the test database Database changedmysql > create table test (id int) / / create a test table Query OK, 0 rows affected (0.01 sec) mysql > show create table test / / View the storage engine of test table +-+-+ | Table | Create Table | +-+-+ | test | CREATE TABLE "test" ("id" int (11) DEFAULT NULL) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | / / you can see here that the storage engine is MyISAM +-+-- + 1 row in set (0.03 sec)

③, use engine to specify the default storage engine when creating tables using create table (specify by yourself when creating new tables)

Mysql > use school;Database changedmysql > create table zyc (id int) engine=InnoDB; / / create a new table zyc and specify the storage engine as InnoDBQuery OK, 0 rows affected (0.02 sec) mysql > show create table zyc / / View zyc storage engine +-+-+ | Table | Create Table | +-+-+ | zyc | CREATE TABLE "zyc" ("id") Int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | / / you can see here that the storage engine is InnoDB+-+--+1 row in set (0.00 sec)

④, use the mysql_convert_table_format command to convert the storage engine in batch, and the command format is as follows:

Mysql_convert_table_format-- user=root-- password= password-- socket=/temp/mysql.sock-- engine= engine library name table name

Since bloggers are using the MySQL5.7 version, and this command is available only for MySQL5.5, I won't go into details here. If you are interested, you can install a MySQL5.5 on your own.

This is the end of the introduction of MySQL's storage engine. Please look forward to: MySQL incremental backup, MySQL master-slave synchronization, MySQL read-write separation.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report