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

MySQL database storage engine

MySQL database is a kind of relational database, the data is stored in a table in the database, each table may be related, each row in the table is related, and each column is irrelevant. This is the basic way to store relational databases.

MySQL storage engine is an optional storage mode for the reliability, relevance, reading and writing of data storage under different conditions.

At present, there are mainly the following MySQL storage engines, MyISAM,InnoDB,BdB,Memory and so on. These storage engines are different in the following aspects, such as locking level, processing mode, storage mode and indexing skills.

The methods of displaying storage engine and changing storage engine under mysql are very simple.

Show engines; displays the supported

Alter table tablename engine = whichengine; modify storage engine

Create table tablename (id int,name char (15)) engine = whichengine; change when new

Differences between common storage engines:

1 > MyISAM: its predecessor is ISAM, which is an extension of ISAM and supports index and field management functions that ISAM does not support. It is characterized by table locking, can handle highly concurrent read and write operations, and is the default storage engine.

2 > InnoDB and BdB: these two storage engines are the only storage engines that support transaction processing. The difference is that InnoDB is row-locked and BDB is page-locked.

3 > Memory: you can tell from the name that this is a storage mode that uses memory to store data temporarily. Read and write efficiency goes without saying, but all data will be lost when the database is closed.

Three locking levels:

1 > Table locking: in order to prevent multiple users from updating a table at the same time, the table lock will lock the entire table when the data is updated, so it will cause a lot of delay in multi-user access, so the concurrency is poor, but the overhead is low.

2 > Row locking: because of the low concurrency of table locking, row locking occurs. Locking each row of the database when accessing the database allows users who visit different rows to access the same table at the same time, but it is expensive and can cause deadlocks. (for the analysis of why deadlocks occur, here is a blog link that says a lot about http://www.cnblogs.com/bamboos/p/3532150.html)

3 > Page locking: what exists between row locking and table locking is page locking, which is a compromise.

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.