MySQL Basics (iii) storage engine and lock

Concept of storage Engine:

A relational database table is a data structure for storing and organizing information that can be understood as a table of rows and columns, a variety of different table structures mean that different types of data are stored, and there are differences in the processing of data, and for MySQL, it provides many types of storage engines. Depending on the data processing requirements, different storage engines can be selected to maximize MySQL performance

MySQL Common storage Engine: (SHOW ENGINES; View MySQL-supported storage engines)

innodb:mysql5.5 is the most widely used storage engine in the future, using the default storage engine

Features of the InnoDB:

1. Support Security recovery after crash

2, InnoDB support row-level lock and foreign key constraints

3. Support Business

4. Support clustered index and secondary index

5. Support Hot Backup

6, support row-level lock

7, especially suitable for processing multiple concurrent requests, based on MVCC implementation

Data files:

InnoDB data is stored in the table space:

Types of two table spaces:

1, all InnoDB table data and indexes exist in a file, tablespace files are defined in the data directory

Data file name: Ibdata1,ibdata2,...

This table space format is used by default, but this is a very flawed approach and is not recommended for use

2. Each table uses a separate tablespace file to store data and indexes

Innodb_file_per_teble=on#指定使用第二种表空间格式

Using a standalone tablespace file, each additional table adds two data files

Data files:

table name. IBD: Used to store data and indexes

table name. frm: Used to store table definitions and properties

myisam:mysql5.5 previously used by default storage engine

features of the MyISAM:

1, support full-text indexing, compression

2. Do not support transactions

3, can only support table-level lock

4. No support for post-crash security recovery

5. Support Warm Backup

Application scenario: Read and write less environment (such as: Read and write separate from the library), and if need to use MyISAM can consider using ARIA instead

ARIA supports post-crash security recovery

MyISAM not create a table and generate three data files

Data files: In the database directory

Table name. frm: Used to store table definitions and properties

The table name. MYD: Used to store data

The table name. MYI: For storing indexes

Blackhole (Black hole engine):

does not actually store data, generally used only to record binary log files, multi-use and Cascade replication

Memory-based storage engine:

Memory storage engine, RAM as storage medium. Improves database performance, but when mysqld crashes, all memory data is lost

Usage scenarios:

1. The target data is small and is accessed very frequently

2. If the data is temporary and needs to be immediately available, memory storage engine can be

3. If the data stored in the memory table is suddenly lost, it will not adversely affect the online service

MySQL Lock:

The concept of Lock:

In a database, data is a resource that is shared by many users. How to ensure consistency and validity of data concurrent access is a problem that all databases must solve, lock conflict is also an important factor that affects the performance of database concurrent access.

Level of Lock:

Table-level Lock: Low overhead, lock fast, no deadlock, locking force is high, the probability of lock conflict is highest, the concurrency is lowest

Row-level locks: high overhead, slow locking, deadlock, low locking force, lowest probability of lock conflict, highest concurrency

MySQL itself implements table-level locks at the database level, and the lock on the storage engine can be invoked only by the storage engine, and the user is not authorized to operate

Manual Lock:

Grammar

LOCK TABLES tbl_name ock_type read| WRITE#加锁

UNLOCK TABLES#解锁

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1932021

MySQL Basics (iii) storage engine and lock