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

MyISAM

1. Characteristics

Transactions are not supported: the MyISAM storage engine does not support transactions, so business scenarios that require transactions cannot be used

Table-level locking: its locking mechanism is a table-level index, which can make the cost of locking very low, but also greatly reduce its concurrency performance.

Reads and writes block each other: not only does MyISAM block reads while writing, but MyISAM also blocks writes while reading, but reads themselves do not block other reads

Only indexes can be cached: MyISAM can use key_buffer caching to greatly improve access performance and reduce disk IO, but this cache only caches indexes, not data

two。 Applicable scenario

No transaction support is required (not supported)

Relatively low concurrency (locking mechanism issues)

Relatively few data modifications (blocking problem)

Give priority to reading

The requirement of data consistency is not very high.

3. Best practic

Index as much as possible (caching mechanism)

Adjust the priority of reading and writing to ensure that important operations are given priority according to actual needs.

Enabling delayed insertion to improve the performance of bulk writes

Operate sequentially as much as possible so that insert data are written to the tail to reduce blocking

Decompose large operations to reduce the blocking time of a single operation

Reduce the number of concurrency, and some high concurrency scenarios are queued through applications.

For relatively static data, making full use of Query Cache can greatly improve the access efficiency.

The Count of MyISAM is particularly efficient only when scanning full tables, and count with other conditions requires actual data access.

InnoDB

1. Characteristics

Good transaction support: support 4 transaction isolation levels and support multi-version reading

Row-level locking: through the index, the full table scan will still be a table lock, pay attention to the impact of gap locks

Read-write blocking is related to transaction isolation level

It has very efficient caching features: it can cache indexes as well as data.

The whole table and primary key are stored in Cluster to form a balance tree.

All Secondary Index will save primary key information

two。 Applicable scenario

Transaction support is required (with good transaction characteristics)

Row-level locking is well adapted to high concurrency, but you need to ensure that the query is done through the index

Scenarios where data is updated more frequently

High requirements for data consistency

The memory of the hardware device is large, so the better cache ability of InnoDB can be used to improve the memory utilization and reduce the disk IO as much as possible.

3. Best practic

The primary key should be as small as possible to avoid excessive space burden on Secondary index.

Avoid full table scans because table locks are used

Cache all indexes and data as much as possible to improve response speed

When inserting in large quantities and small ones, try to control the transaction yourself instead of using autocommit autocommit.

Set innodb_flush_log_at_trx_commit parameter values reasonably and don't pursue security excessively.

Avoid primary key updates, as this can lead to a lot of data movement

NDBCluster

1. Characteristics

Distributed: distributed storage engine, which can be clustered by multiple NDBCluster storage engines to store part of the overall data.

Supporting transactions: like Innodb, supporting transactions

Can be separated from mysqld on a separate host: can exist separately from mysqld on a separate host, and then communicate with mysqld through the network

The memory demand is huge: the new version of the index and the indexed data must be stored in memory, and all data and indexes of the old version must be stored in memory.

two。 Applicable scenario

Have very high concurrency requirements

The response to a single request is not very critical

The query is simple, the filtering conditions are relatively fixed, the amount of data per request is small, and you do not want to conduct horizontal Sharding by yourself.

3. Best practic

Make the query as simple as possible to avoid cross-node transmission of data

As far as possible to meet the computing performance of SQL nodes, larger cluster SQL nodes will obviously have more Data nodes.

Interconnect nodes in a 10 Gigabit network environment as much as possible to reduce the delay of data transmission at the network layer

Note: the above three storage engines are currently relatively mainstream storage engines, and other storage engines such as Memory,Merge,CSV,Archive are relatively rare, so we will not analyze them one by one here. If any friends are interested, please add them later.

This article is from http://www.2cto.com/database/201605/506253.html

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.