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

Introduction to MyISAM and InnoDB, two kinds of storage engines of MySQL

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

Share

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

Let's talk about the two storage engines of MySQL, MyISAM and InnoDB. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on MySQL's two storage engines, MyISAM and InnoDB.

MyISAM is the default storage engine for MySQL, based on traditional ISAM types, supports full-text search, but is not transaction-safe, and does not support foreign keys. Each MyISAM table is stored in three files: the frm file holds the table definition; the data file is MYD (MYData); and the index file is MYI (MYIndex).

InnoDB is a transactional engine that supports rollback, crash resilience, multi-version concurrency control, ACID transactions, row-level locking (row locks on InnoDB tables are not absolute, and if MySQL cannot determine the scope to scan when executing a SQL statement, InnoDB tables will also lock the whole table, such as SQL statements during like operations), and provide unlocked reads consistent with Oracle types. InnoDB stores its tables and indexes in one tablespace, which can contain several files.

Main differences:

MyISAM is non-transactional secure, while InnoDB is transactional secure.

The granularity of MyISAM locks is table-level, while InnoDB supports row-level locking.

MyISAM supports full-text indexing while InnoDB does not support full-text indexing.

MyISAM is relatively simple, so it is better than InnoDB in efficiency, and small applications can consider using MyISAM.

MyISAM table is saved as a file, so using MyISAM storage in cross-platform data transfer will save a lot of trouble.

InnoDB tables are more secure than MyISAM tables and can switch from non-transactional tables to transactional tables (alter table tablename type=innodb) without losing data.

Application scenarios:

MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If you need to execute a large number of SELECT queries in your application, then MyISAM is a better choice.

InnoDB is used in transactional applications and has many features, including ACID transaction support. If you need to perform a large number of INSERT or UPDATE operations in your application, you should use InnoDB, which can improve the performance of multi-user concurrent operations.

Common commands:

(1) View the storage type of the table (three):

Show create table tablename

Show table status from dbname where name=tablename

Mysqlshow-u user-p password-- status dbname tablename

(2) modify the storage engine of the table:

Alter table tablename type=InnoDB

(3) add the following parameters to the command line that starts the mysql database so that all newly published tables use transactions by default:

-- default-table-type=InnoDB

(4) temporarily change the default table type:

Set table_type=InnoDB

Show variables like 'table_type'

Is there anything you don't understand about the two storage engines MyISAM and InnoDB of MySQL above? Or if you want to know more about it, you can continue to follow our industry information section.

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