Mysql database 3 --- database storage engine

Prodigal son Tang Shao 2022-02-13 07:29:07 阅读数:658

mysql database database storage engine

mysql database 3— Database storage engine

1. The concept of storage engine

Plug in storage engine is Mysql One of the most important features in , Users can choose how to store data and index according to the needs of the application , Whether to use things
etc. , To improve the overall functionality of your app . These different technologies and related functions are in MySQL It's called the storage engine ,MySQL Default branch
Multiple storage engines , To meet the needs of databases in different fields , Users can select different storage engines , Improve application efficiency , Provide flexible
Storage , Users can also customize and use the storage engine according to their own needs .

2. Look at the database storage engine

1. View status information about the server's storage engine

 command :show engines;

Find out the results :

 The meaning of each column
1.Engine: The name of the storage engine .
2.Support: The level of server support for the storage engine
(1)YES: The engine is supported and active
(2)DEFAULT: This engine is supported and is the default engine
(3)NO: Engine not supported
(4)DISABLED: Support Engine , But it has been disabled
3.Comment: A brief description of the storage engine .
4.Transactions: Whether the storage engine supports transactions .
5.XA: Does the storage engine support it XA Business
6.Savepoints: Does the storage engine support savepoints .

2. View the storage engines currently supported by the system

 command :show variables like '%storage_engine%';

Find out the results :


PS:

1.default_storage_engine Represents a permanent table (permanent tables) The default storage engine for .

2.default_tmp_storage_engine Represents the default storage engine for temporary tables .

3. Creation and modification of storage engine

When creating a new table , If you do not specify a storage engine , Then the system will use the default storage engine ,MySQL 5.5 The previous default storage engine was MyISAM
, 5.5 Then it changed to InnoDB, If you need to modify the storage engine, you can do the following

1. Yes mysql Configuration file modification

stay mysql Folder location found my.ini file , And make changes ; Put the original default-storage-engine=INNODB Change it to default-storage-engine=MyISAM; And restart the database .

2. Specify when creating a table

1. Create table ’test1‘
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM default CHARSET=utf8
2. Create table ’test2‘
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

3. Modify table storage engine

-- Modify table test1 The storage engine is innoDB
alter table test1 ENGINE = innoDB;
-- View the table creation statement 
show create table test1

4. Common storage engine differences

 Insert picture description here
Except that the table above lists , Focus on MyISAM and InnoDB difference
1. Main foreign key
MyISAM : I won't support it
InnoDB: Support
2. Business
MyISAM: I won't support it
InnoDB: Support
3. Row table lock
MyISAM Table locks Operating one record will also lock the whole table Not suitable for highly concurrent operations
InnoDB Row lock In operation , Lock only one row , It doesn't affect anything else Suitable for high concurrency operations
4. cache
MyISAM : Cache index only , Don't cache data
InnoDB: Not only cache index , Also cache real data , High memory requirements , And memory size has a decisive impact on performance
5. concerns
MyISAM performance
InnoDB: Business
6. Default installation
MyISAM: yes
InnoDB: yes

data
InnoDB: Not only cache index , Also cache real data , High memory requirements , And memory size has a decisive impact on performance
5. concerns
MyISAM performance
InnoDB: Business
6. Default installation
MyISAM: yes
InnoDB: yes

summary :InnoDB And MyISAM The biggest difference : One is to support affairs , The second is the use of row level lock .

copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729052405.html