MySQL Samsung index settings

Three points to consider when MySQL InnoDB indexing is set

1, single-line access is very slow. Single-line access requires random disk I/O, and the random I/O efficiency of mechanical disks including SSDs is very low. The server reads the data from the disk file is loaded in blocks (block), in order to read a specific piece of data, need to load the entire data block of the data, so in order to improve efficiency as much as possible to read several adjacent data (how to understand adjacent, we later specifically discussed).

2. Accessing the range data sequentially is fast. There are two main reasons why first, sequential I/O does not require multiple disk seeks, and is much more efficient than random I/O (even SSDs). Second, if the server is able to read the data sequentially (such as the order of the combined indexes and the order by at the time of the query), then no additional sort operations are required, and the group by query does not need to sort and aggregate the rows by group.

3, index coverage query is very fast. If an index file includes all of the automatic queries, the storage engine does not need to go back to the data file to make a table query, thus avoiding a large number of single-line accesses.

Based on the above analysis, if an index satisfies the following three conditions, we call this index a Samsung index:

1. Index put related records together (to be expanded)

2. If the data order of the index and the arrangement of the query in the same way

3. The columns in the index contain all the columns needed in the query

MySQL Samsung index settings