In the age of needing to support mobile/tablet applications as well as regular desktop browser access, the popularity and effectiveness of a website depends heavily on its usability and performance. A slow accessing website can lead to loss of visitors or potential customers and business failure, and IT training recognizes that a reasonably fast accessing website will determine whether or not a visitor will use the products or services offered by the website.

Websites with large-scale databases always require proper attention, configuration, optimization, tuning and maintenance to ensure that the site loads quickly. This article will discuss how to optimize MySQL databases with massive amounts of data.

Choosing InnoDB as a storage engine

Databases with large products require high reliability and concurrency, and InnoDB, as the default MySQL storage engine, is a better choice than MyISAM.

Optimizing the database structure

Organizing the database schema, tables, and fields to reduce I/O overhead, keeping related items together, and planning ahead so that performance can be kept high as the amount of data grows.

Data tables should be designed to take up as little space as possible, and table primary keys should be as short as possible.

For InnoDB tables, the column in which the primary key resides is replicated in every secondary index entry, so a short primary key can save a lot of space if there are many secondary indexes.

Create only the indexes you need to improve query performance. Indexes help with retrieval, but they increase the execution time of insert and update operations.

InnoDB's ChangeBuffering Feature

InnoDB provides a configuration for changebuffering that reduces the disk I/O required to maintain the secondary indexes. large-scale databases may encounter a large number of table operations and a large amount of I/O to ensure that the secondary indexes remain up to date. When the relevant page is not inside the buffer pool, InnoDB's changebuffer will change the cache to the auxiliary index entry, thus avoiding time-consuming I/O operations due to the inability to immediately read the page from disk. When the page is loaded into the buffer pool, the cached changes are merged and the updated page is later flushed to disk. This improves performance and is available for MySQL 5.5 and later.