MySQL Storage engine MyISAM Learning

MyISAM Storage Engine Features:
1. Do not support transactions
2. Table-level locking (locks the entire table on update, whose index mechanism is a table-level index, although it can make the implementation cost of locking very small, but also greatly reduces its concurrency performance)

3. Read and Write blocking: not only blocking the read when writing, MyISAM will also block the write while reading, but the read itself does not block the other read.
4. Only the index is cached: MyISAM can significantly improve access performance by Key_buffer_size cache indexes to reduce disk IO, but this buffer caches the index only and does not cache the data.

5. Read faster and consume less resources

6. Foreign KEY constraints are not supported, but full-text indexing is supported

Production business scenarios for the MyISAM engine
1. Businesses that do not require transaction support (columns such as transfer will not work, recharge payment)

2. Generally read the data more than the application, read and write are frequent scenes are not suitable, read more or write more are suitable
3. Read and write concurrent access to a relatively low business (read-only write high concurrency can also)

4. Data modification of relatively small business (blocking issues)

5. Read-oriented business

6. Data consistency requirements are not very high business (transaction not supported)
7. Small and medium-sized website parts of the business will use

MyISAM Engine Tuning Essentials

1. Set the appropriate index (caching mechanism)

2. Adjust read and write priorities to ensure that important operations are prioritized based on real-world requirements

3. Enable deferred insertion improves bulk write performance (reduces write frequency, as many data writes at once)

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

5. Decomposition of large long-time operations, reducing the blocking time of a single operation

6. Reduce concurrency (reduce access to MySQL), and some high-concurrency scenarios are queued by app queuing mechanism

7. For database data that is relatively static (infrequently changed), making full use of the query cache or memcached cache service can greatly improve efficiency
   Query_cache_size=2m
   query_cache_limit=1m
   query_cache_min_res_unit=2k

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

9. You can use InnoDB for master-slave synchronization and use the MyISAM engine from the library (not recommended).

MySQL Storage engine MyISAM Learning