Tens data volume MySQL optimization strategy (i)

Table structure optimization 1. Using stand-alone table spaces

A stand-alone tablespace refers to a data structure of a InnoDB table

Exclusive tablespace: Each table will be generated in a separate file way for storage, each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.

2. Partition table

The partitioned table is a coarse-grained, easy-to-use indexing strategy for big data filtering scenarios. The most suitable scenario is when you do not have a suitable index, and you have a full table scan of several of the partitioned tables. or only one partition table and index are hotspots, And this partition and index can all be stored in memory. Limit the number of single-table partitions to no more than 150, and note that some of the details that cause partition filtering are not possible, and the partitioning table has no advantage over queries of individual records and needs to be aware of the performance of such queries.

Depending on the characteristics of the table structure, single table can be partitioned according to certain conditions, for example, if the data of a table is inserted in chronological order, then it can be partitioned according to the timestamp field.

3. In-Memory database

Consider storing data in an in-memory database to speed up data queries.

SQL Optimization 1. Enforce (ignore) index policies under multi-conditional queries

Force index

SELECT * from TABLE1 Force INDEX (FIELD1) ...

The above SQL statements use only the indexes built on FIELD1, not the indexes on other fields.

Ignore Indexes IGNORE Index

SELECT * from TABLE1 IGNORE INDEX (FIELD1, FIELD2) ...

In the above SQL statement, the indexes on FIELD1 and FIELD2 in the TABLE1 table are not used.

In conjunction with a partitioned table, the commonly enforced index is the partitioning criteria field for the partitioned table.

2. Minimize the association of tables use a single table for querying

If a table is associated with a large amount of data, the amount of Cartesian product that is generated is too large. The calculation of conditional filtering is time consuming.

Tens data volume MySQL optimization strategy (i)