Storage engine selection for MySQL database performance optimization

MyISAM

　

1. Features

Transaction not supported: The MyISAM storage engine does not support transactions, so business scenarios that are required for transactions cannot be used

Table-level locking: The locking mechanism is a table-level index, which can make the implementation cost of the lock small but also significantly reduces its concurrency performance

Read/write blocking: Not only does blocking read when writing, MyISAM also blocks writes while reading, but reading itself does not block additional reads

Only the index is cached: MyISAM can reduce disk IO by Key_buffer caching to greatly improve access performance, but this buffer caches only the index, not the data

2. Applicable scenarios

No transaction support required (not supported)

Relatively low concurrency (locking mechanism issues)

Relatively few data modifications (blocking issues)

To read the main

Data consistency requirements are not very high

3. Best practices

Try to index (caching mechanism)

Adjust read and write priorities to ensure important operations are prioritized based on actual requirements

Enable deferred insertion for improved high-volume write performance

Try to keep the insert data written to the tail in order to reduce blocking

Decomposition of large operations to reduce blocking time for individual operations

Reduce concurrency, some high concurrency scenarios are queued by application

For relatively static data, full use of the query cache can greatly improve access efficiency

The count of MyISAM is particularly efficient when full-table scans, and count with other conditions requires actual data access

InnoDB

1. Features

Good transactional support: Supports 4 transaction isolation levels, supports multiple versions of Read

Row-level locking: Through the index implementation, the full table scan will still be a table lock, pay attention to the impact of Gap lock

Read-write blocking is related to transaction isolation level

Very efficient caching features: ability to cache indexes and cache data

The entire table and primary key are stored in cluster form, forming a balanced tree

All Secondary index will hold the primary key information

2. Applicable scenarios

Transaction support required (with good transactional characteristics)

Row-level locking is good for high concurrency, but needs to ensure that queries are done by indexing

More frequent scenarios for data updates

High Data consistency requirements

Hardware device memory is large, can take advantage of InnoDB better cache capacity to improve memory utilization, reduce disk IO as much as possible

3. Best practices

The primary key is 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

In large batches of small inserts, try to control your own transactions instead of using autocommit auto-commit

Set Innodb_flush_log_at_trx_commit parameter value rationally, do not pursue security excessively

Avoid primary key updates, as this can result in a lot of data movement

Ndbcluster

1. Features

Distributed: A distributed storage engine that can be composed of multiple Ndbcluster storage engines that are part of a cluster that holds overall data

Support transactions: As with InnoDB, support transactions

Can not be a host with mysqld: Can and mysqld separate from the separate host, and then through the network and MYSQLD communication interaction

Huge memory requirements: The new version index and the indexed data must be in memory, the old version all data and indexes must exist in memory

2. Applicable scenarios

have very high concurrency requirements

The response to a single request is not very critical

Simple query, more fixed filter conditions, less data per request, and do not want to level sharding

3. Best practices

Make queries as simple as possible, avoiding cross-node transmission of data

As much as possible to meet the SQL node's computational performance, a larger cluster SQL node will significantly redundant data nodes

Using Gigabit network environment interconnection between nodes to reduce the delay of data in the network layer transmission process

Note: The above three storage engine is the current relatively mainstream storage engine, there are other similar, such as: Memory,merge,csv,archive and other storage engine usage scenarios are relatively small, here is not analyzed, if there are friends interested in the back to add it.

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

This article is from the "Dream to Reality" blog, please be sure to keep this source http://lookingdream.blog.51cto.com/5177800/1881666

Storage engine selection for MySQL database performance optimization