MySQL Study Notes (4) Performance Optimization 2

MySQL Study Notes (iv) Performance Optimization 21. Efficient Model Design

(1) moderately redundant queries to minimize join

Take out some data from other tables and put it in your own table. For example, in the publishing information table, not only the user ID is saved, but also a nickname is saved.

(2) Vertical Split of large fields

Split the field into another table, such as some large fields,ArticleContent, product introduction, and infrequently accessed Data

(3) Horizontal Split of large tables

Put all the data in the table into a single table. For example, place the top information in a single table because you need to access

(4) quasi-Real-Time Optimization of statistical tables

Regular statistics, such as point ranking.

2. Reasonable Data Types

1. Store Data with a smaller data type;

2. Accelerate data comparison with appropriate data types, such as replacing floating-point numbers with Integers

3. Standardized object naming

in general, I personally suggest you pay attention to the following aspects:
1. The database and table names should be the same as the service module names.
, when DBA maintains related database objects, related technologies (or non-technical
techniques) during Program development by new developers) the relationship between business logic and data can be easily understood.
2. for a type of table serving the same submodule, try to use the submodule name (or part of words) as the prefix or suffix;
adding a prefix or suffix to a table of the same type of function allows users who view and use the table to quickly think of the corresponding function based on the name of the relevant object
, and related services. Both in terms of maintenance and use will bring great convenience.
3. The table name should contain words that correspond to the stored data as much as possible.
This is especially important for new employees. to familiarize themselves with the data as soon as possible and learn about the related business as soon as possible, it is very helpful to quickly locate the data corresponding to
tables in the database.
4. Keep the field name consistent with the actual data.
I think readers and friends should be very clear about this, each table has many fields corresponding to different data attributes. To clarify the meaning of each field, in addition to the complete and standardized description documents, clearly and reasonably named field names are also a useful supplement to
and are more direct.
5. The index name should contain all index key field names or abbreviations whenever possible, and the order of each field name in the index name should be consistent with the index order in the index key
, try to include a prefix or suffix similar to idx or ind, with the table name
its object type is index, and it can also contain the name of the table to which the index belongs;
the biggest advantage of doing so is that DBAs can directly and clearly understand the large part of the index through the index name during maintenance.
.
6. Other objects such as constraints should also contain the names of their tables or other objects as much as possible, with their respective relationships as table names