Mysql relational database indexing principle

The index of the database is B+tree structure

The primary key is an aggregated index. The other indexes are non-aggregated indexes, as shown in the following figure

Elasticsearch inversion index principle

Both comparisons

For inverted indexes, there are two cases:

  1. Full-text retrieval based on word segmentation

This situation is the strength of ES and a total disaster for mysql relational databases

Because after es segmentation, each word can use FST high speed to find the position of the inverted index, and quickly obtain the document ID list

Mysql can only search the entire table for middle words (if not the first few characters)

  1. Precise retrieval

In this case I think there’s not much difference between the two, in some cases mysql might be faster

If mysql’s non-aggregated index uses an overwrite index and does not need to return to the table, the speed may be faster

Es or through FST to find the location of the inverted index structure and get the document id list, according to document id get the document and according to the relevant points for sorting, but there are still a killer es, namely natural distributed makes in front of the large amount of data can be shard to reduce the scale of each shard retrieval, parallel retrieval and can promote efficiency

With filter, you can skip retrieval directly and cache directly