introduce

In our last mysql overview, we looked at the architecture of mysql, which mentioned the storage engine. It plays a very important role in mysql. It is responsible for creating, reading, and updating data, that is, data I/O operations.

Most importantly, it is a pluggable module, which means that there is no adaptation for the upper layer application when we switch storage engines.

There are many kinds of storage engines, and all have their own features, like our common InnoDB transaction support, MyISAM speed and so on.

Storage engine usage

As a plug-in module, we can naturally add and uninstall storage engines, and specify storage engines when creating tables.

Loading a Storage Engine

Mysql communicates with the storage engine through the defined API interface, so we just need to implement the corresponding API interface, which can be loaded at mysql runtime.

Mysql has a storage engine for Example, and we can see that it needs to implement the following API:

Once we have implemented these methods, we can compile it as a shared library and load it in using the following statement

INSTALL PLUGIN example SONAME 'example.so';
Copy the code

Note that the plugin file must be in the mysql plugin directory and have INSERT privileges on the Plugin table.

Uninstalling a Storage Engine

To uninstall the storage engine, it is relatively simple

UNINSTALL PLUGIN example;
Copy the code

When uninstalled, the corresponding tables will not be accessible, so make sure that the storage engine tables are still in use!

View and use storage engines

When we have loaded the storage engine, we can use the following command to view it:

show engines;
Copy the code

If we want to use the corresponding storage engine, we can use the following method:

  • CREATE TABLE t1 (id INT) ENGINE = INNODB;
  • SET default_storage_engine=INNODB;
  • ALTER TABLE t ENGINE = InnoDB;

ALTER TABLE will copy the old TABLE to the new TABLE and lock it during this time.

Storage engine types and characteristics

There are many kinds of storage engines implemented in mysql, and each storage engine has its own characteristics. Let’s take a look at what storage engines mysql supports.

InnoDB: it is the default storage engine of mysql. It can implement ACID transactions, and can commit, roll back, restore data, and protect user data well. It also supports row-level locking, clustered indexes, and foreign key constraints, making it a complete storage engine.

MyISAM is the original storage engine of mysql. It occupies a small space and can store data quickly, but does not support transactions. It provides table-level lock granularity and is suitable for configured or read-only applications.

Memory: Data is stored in Memory, providing fast access, but should be used sparingly, since data is lost in the event of a power outage.

CSV: A text file with comma-separated values and no index. But compatibility is good, can exchange data with other programs.

Other storage engines are used less frequently and are no longer mentioned,

Storage engine differences

Storage engines have their own characteristics, and their differences can be roughly summarized as follows:

  • concurrency: Granularity control of locks varies. Some provide table locks, some provide row locks, and some even provide snapshot reads.
  • The transaction: Some storage engines support it, some do not. Not every application design requires transactions.
  • Referential integrity: Indicates whether a foreign key is supported. If a foreign key exists, the association is strong and the association check is performed before deletion.
  • storage: Tables and indexes are stored differently. MyISAM stores three files, while InnoDB stores all tables in the same data file.
  • Index support: Some clustered indexes store data directly on leaf nodes, and some only store Pointers to data areas in leaf nodes.

conclusion

Plug-in storage engines give us a variety of choices. There are no specific rules for which storage engine to use, only current requirements analysis to choose. For full-text indexing, you can choose MyISAM or InnoDB. For data integrity or transaction support, you need InnoDB.

Of course, most of the time, we will directly choose the default storage engine InnoDB, after all, in the face of Internet projects can change at any time, large and complete is our best choice.

Interested friends can search the public account “Read new technology”, pay attention to more pushed articles. Thank you for your support! Read new technology, read more new knowledge.