MySQL database performance optimization-storage engine Selection

Author: Sky. jian Copyright Disclaimer link: isky000.comdatabasemysql-performance-tuning-storage-engine | del. icio. us | Twitterit | HostedOnDreamHost can get a discount through my discount code iMySQLer MySQL database performance optimization SQL optimization, which is MySQL database performance

Author: Sky. jian Copyright Disclaimer link: http://isky000.com/database/mysql-performance-tuning-storage-engine | del. icio. us | Twitter it | Hosted On DreamHost can get a discount through my discount code iMySQLer MySQL database performance optimization-SQL optimization, which is MySQL database performance

Author: Sky. Jian copyright notice
Link: http://isky000.com/database/mysql-performance-tuning-storage-engine | del. icio. us | Twitter it |
Hosted On DreamHost can pass my discount codeIMySQLerGet discount

MySQL database performance optimization-SQL Optimization, This isMySQL database performance optimization topicsThe fifth article in the series:MySQL database performance optimization-storage engine Selection

The storage engine of MySQL may be the most distinctive of all relational database products, not only can multiple storage engines be used at the same time, in addition, plug-ins are used between each storage engine and MySQL.

Because the features of various storage engines vary greatly, this article mainly describes how to select an appropriate storage engine to deal with different business scenarios.

• MyISAM
  • Features
    1. Transactions not supported: MyISAM storage engine does not support transactions. Therefore, business scenarios requiring transactions cannot be used.
    2. Table-level locking: the locking mechanism is table-level indexing, which can reduce the implementation cost of locking but also greatly reduce its concurrency performance.
    3. Read/write blocking: not only does MyISAM block write during write, but the read itself does not block other reads.
    4. Only cache indexes: MyISAM can use the key_buffer cache to greatly improve access performance and reduce disk I/O. However, this cache area only caches indexes instead of data.
  • Applicable scenarios
    1. No transaction support (not supported)
    2. Relatively low concurrency (locking mechanism problems)
    3. Relatively few data changes (blocking problem)
    4. Read-oriented
    5. Data Consistency requirements are not very high
  • Best practices
    1. Index as much as possible (Cache Mechanism)
    2. Adjust the read/write priority to ensure that important operations are given priority based on actual needs.
    3. Enable delayed insertion to improve Writing Performance in large batches
    4. Try to write the insert data to the end in sequence to reduce blocking.
    5. Break down large operations to reduce the blocking time of a single operation
    6. Reduce the number of concurrency. In some high-concurrency scenarios, queuing mechanism is implemented through applications.
    7. Making full use of Query Cache can greatly improve access efficiency for relatively static data.
    8. MyISAM's Count is especially efficient only when scanning the whole table. Actual data access is required for the count with other conditions.
• InnoDB
  • Features
    1. Good transaction support: supports four transaction isolation levels and multi-version read
    2. Row-Level Lock: The full table scan will still be a table Lock Based on the index. Pay attention to the impact of the GAP lock.
    3. Read/write blocking is related to the transaction isolation level.
    4. Highly Efficient Caching: data can be cached as well as indexes.
    5. The entire table and primary key are stored in clusters to form a Balance Tree.
    6. All Secondary indexes Save the primary key information.
  • Applicable scenarios
    1. Requires transaction support (with good transaction features)
    2. Row-level locking can adapt to high concurrency, but it is necessary to ensure that the query is completed through the index.
    3. Scenarios with frequent data updates
    4. High data consistency requirements
    5. The memory size of the hardware device is large. The good cache capability of InnoDB can be used to improve memory utilization and minimize disk IO.
  • Best practices
    1. The primary key should be as small as possible to avoid extra space burden on the Secondary index.
    2. Avoid full table scan because table locks are used.
    3. Cache all indexes and data as much as possible to improve response speed
    4. When large volumes of small inserts, try to control the transaction by yourself instead of using autocommit for automatic commit.
    5. Reasonably set the innodb_flush_log_at_trx_commit parameter value and do not excessively pursue security.
    6. Avoid Primary Key Update, because this will bring a lot of data movement
• NDBCluster
  • Features
    1. Distributed: the distributed storage engine. It can be composed of multiple NDBCluster storage engines, which respectively store part of the overall data.
    2. Transactions supported: Like Innodb, transactions supported
    3. It can be different from mysqld on one host: It can be separated from mysqld on an independent host, and then communicate with mysqld over the network.
    4. Memory demand is huge: new versions of indexes and indexed data must be stored in memory, and all data and indexes of old versions must exist in memory
  • Applicable scenarios
    1. High concurrency requirements
    2. The response to a single request is not very critical
    3. The query is simple and the filtering conditions are relatively fixed. Each request has a small amount of data and you do not want to perform horizontal Sharding on your own.
  • Best practices
    1. Make the query as simple as possible to avoid data transmission across nodes
    2. To meet the computing performance of SQL nodes as much as possible. A larger cluster SQL node will obviously have redundant Data nodes.
    3. Use the 10-ge network environment to connect nodes to reduce the latency of data transmission at the network layer.

Note: The above three storage engines are currently the mainstream storage engines, and other storage engines such as Memory, Merge, CSV, and Archive are rarely used, I will not analyze them one by one here. If you are interested, I will try again later.