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

The concept of storage engine:

Relational database tables are data structures used to store and organize information. Tables can be understood as tables composed of rows and columns. Different table structures mean that different types of data are stored, and there are differences in data processing. For mysql, it provides many types of storage engines, which can be selected according to the needs of data processing. In order to maximize the performance of mysql

Common storage engines in mysql: (SHOW ENGINES; to view storage engines supported by mysql)

The storage engine used by default in InnoDB:mysql5.5 is the most widely used storage engine at present.

Features of InnoDB:

1. Support safe recovery after crash

2. InnoDB supports row-level locks and foreign key constraints.

3. Support transactions

4. Support clustered index and secondary index

5. Support hot backup

6. Row-level locks are supported

7. It is especially suitable for handling multiple concurrent requests, which is based on MVCC.

Data file:

InnoDB data is stored in a tablespace:

Two types of tablespaces:

1. The data and indexes of all InnoDB tables exist in one file, and the tablespace files are defined in the data directory.

Data file name: ibdata1,ibdata2,...

This tablespace format is used by default, but it has great drawbacks and is not recommended

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

Innodb_file_per_teble=ON # specifies the second tablespace format

Using separate tablespace files, two data files are added for each additional table

Data file:

Table name .ibd: used to store data and indexes

Table name .frm: used to store table definitions and attributes

Storage engine previously used by MyISAM:mysql5.5 by default

Features of MyISAM:

1. Support full-text indexing and compression

2. Transactions are not supported

3. Only table-level locks are supported

4. Secure recovery after crash is not supported

5. Support warm backup

Applicable scenarios: environments with more reads and less writes (such as slave libraries in read-write separation), and if you need to use MyISAM, you can consider using Aria instead.

Aria support for secure recovery after crash

MyISAM did not create a table and generate three data files

Data files: located in the data directory

Table name .frm: used to store table definitions and attributes

Table name .MYD: used to store data

Table name .MYI: used to store indexes

BlackHole (black hole engine):

Do not actually store data, generally only used to record binary log files, multi-use with cascading replication

Memory (memory-based storage engine):

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

Use the scene:

1. The target data is small and accessed very frequently

two。 If the data is temporary and requires it to be available immediately, you can Memory the storage engine

3. If the data stored in the Memory table is suddenly lost, it will not have a negative impact on online services.

Mysql lock:

The concept of lock:

In a database, data is a resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting database concurrent access performance.

Level of lock:

Table-level lock: low overhead, fast locking; no deadlock; strong locking force, the highest probability of lock conflict and the lowest concurrency

Row-level lock: high overhead and slow locking; deadlock will occur; locking strength is small, lock conflict probability is the lowest, and concurrency is the highest.

Mysql itself implements table-level locks at the database level. The locks of the storage engine can only be called by the storage engine, and users do not have the right to operate.

Manually lock:

Grammar

LOCK TABLES tbl_name ock_type READ | WRITE # Lock

UNLOCK TABLES # unlock

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.