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

Introduction to the concept of Storage engine

Data in MySQL is stored in files using a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides different functions and capabilities These different technologies and supporting functions are called storage engines in MySQL, which is the way or format in which MySQL stores data in the file system-so the scenarios are not the same as the two storage engines commonly used in MySQL.

MyISAM

InnoDB

Storage engine: a basic driving force, storage technology: storage mechanism, indexing skills, locking level. Mysql storage engine is a component of mysql database server. One of the main advantages of using special storage for database execution is that it only needs to provide the features required by special applications, has less system overhead, and has more efficient and higher database performance. In mysql systems, the storage engine is on top of the file system and will be transferred to the storage engine before data is saved to the data file. Then it is stored according to the storage format of each storage engine.

The front and back end of the development language to extract and retrieve data, which needs to install the driver, the specific object is called the connector connector

Connection pool is called connection pool, which is similar to the so-called idle thread or process to connect, in which multiple connected objects are returned to the room to prepare for connection, save response time and optimize access efficiency.

Use threads or processes to connect, links need a media carrier, that is, sock communication files, to provide pid files, all threads need sock files to manage

Data is placed on disk and logically in file system. Management services is needed to manage data.

Sql interface support features, ddl,dml,view

Parser query function query,object privilege permission function

Optimizer: access paths

Buffers: cache, global global

Files logs log files: log files are the core lifeline of the database and are used for master-slave replication. Backup needs to be backed up separately.

Brief introduction of MyISAM myisam storage engine is the default storage engine before version 5.5 of Mysql relational database system. The predecessor is ISAMISAM, which is a well-defined and time-tested data table management method. When designing, it is considered that the number of times the database is queried is much greater than the number of updates.

The characteristics of ISAM: ISAM performs read operations quickly and does not take up a lot of memory and storage resources, but does not support transaction processing and is not fault-tolerant. ISAM values read, read > write.

MyISAM details the management of non-transactional tables in MyISAM, which is an extended format of ISAM that provides a large number of functions such as indexing and field management. MyISAM uses a table locking mechanism to optimize multiple concurrent read and write operations-unable to read when writing data, unable to write MyISAM to provide high-speed storage and retrieval, as well as full-text search capabilities, which are favored by web developers.

The characteristics of MyISAM do not support the form of transaction table-level locking. The whole table database is locked when the data is updated. The database blocks each other in the process of reading and writing.

Will block the reading of user data in the process of data writing

It will also block the user's data writing in the process of data reading. The cache index can be set through key_buffer_size to improve access performance and reduce the pressure on disk IO.

However, caching only caches index files, and does not cache data written or read separately by MsISAM storage engine data. MyISAM storage engine does not support foreign key constraints, but only supports full-text indexing. Each MyISAM is stored as three files on disk. The name of each file starts with the name of the table, and the extension indicates the file type.

MyISAM files stored on disk: .frm files that are used to store tables. The definition structure data file has a .MYD (MYData) index file extension .MYI (MYIndex).

Examples of production scenarios used by MyISAM

The company's business does not need the support of transactions.

Generally speaking, businesses that read / write more data unilaterally

The scenario where MyISAM storage engine reads and writes frequently is not suitable.

Use read-write concurrency to access relatively low services

Businesses with relatively few data modifications

A business that does not require very high data service consistency.

Server hardware resources are relatively poor-when using the old server using mysql, set the engine to MyISAM

InnoDB features support transactions: support four transaction isolation level row-level locking, but full table scan will still be table-level locking read-write blocking related to transaction isolation level has very efficient caching features: it can cache indexes, data tables and primary keys can be stored in clusters to support partitions and tablespaces, similar to oracle databases support foreign key constraints, full-text indexing is not supported before 5.5 Support for full-text indexing after version 5.5 still requires high hardware resources.

Analysis of InnoDB usage production scenario

Business needs transaction support

Row-level locking is well adapted to high concurrency, but make sure that the query is done through the index

Scenarios where business data are updated frequently, such as forums, Weibo, etc.

High consistency of business data is required, for example, banking

The memory of the hardware device is large, so the cache ability of Innodb exchange can be used to improve the advantages and disadvantages of memory utilization and reduce the pressure of IO.

Enterprises choose storage engine on the basis of

You need to consider what different core functions and application scenarios each storage engine provides

Supported fields and data types

All engines support common data types

But not all engines support other field types, such as binary objects

Lock type: different storage engines support different levels of locking table locking: MyISAM, InnoDB row locking: InnoDB

Support for indexing

Indexing can significantly improve performance when searching and replying to data in the database

Different storage engines provide different indexing techniques.

Some storage engines do not support indexing at all

Support for transaction processing

The transaction function provides reliability during updating and inserting information into the table

The storage engine can be selected according to whether the enterprise business supports transactions.

Configure the storage engine

After selecting the appropriate storage engine in the enterprise, you can modify it.

Modify steps to view the storage engine that the database can be configured to view the storage engine that the table is using

3. Configure the storage engine for the selected type

Use show engines to view the storage engines supported by the system

Method 1:show table status from library name where name=' table name'

Method 2:show create table table name

Switching storage engine requires switching table structure

\ G instead of semicolon to show the result vertically

Modify the storage engine

Example

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.