Schema and data type optimization

Schema and data type optimization

1. Select the optimized data type

You should try to use a smaller data type that can store your data. Smaller data types are usually faster and consume less disk, memory, and cache.

2, Simple and best

The integer is better than the character type, two examples, you should use MySQL built-in types to store dates instead of strings; use shaping to store IP addresses. Because the collation and collation of the characters are complex.

3, try to avoid using NULL.

Because nullable columns make the comparison of index and index statistics and values more complex.

4, Integer type

Integer types are divided into: tinyint, smallint, mediumint, int, bigint. MySQL can make a width of integer type, but it is meaningless for most applications, and int (1) and int (20) are the same for storage and computation.

5. Real type

because the CPU does not support direct computation of decimal, MySQL has implemented the decimal calculation in the Mysql5.0 version above. Decimal requires additional overhead and is only used when precise calculations are required.

Consider using bigint instead of decimal, assuming that the financial data to be stored is accurate to a very high of 1, you can multiply all the amounts by 1 million, and then store the results in bigint, so that you can avoid the problem of inaccurate floating-point arithmetic and decimal accurate calculation.

6. String type

varchar stores variable long strings.

When column updates are rare, use a varchar store when a complex character set such as UTF-8 is used.

The char type is fixed-length, stored with char when storing MD5 values, fixed-length values, or frequently changed data, because it is not easy to fragment.

How the data is stored depends on the storage engine.

7 , the paradigm of the schema can also bring benefits. Normalized update operations are usually faster than inverse paradigms. Normalization has little or no duplication of data. The disadvantage of normalization is the need for correlation, which may invalidate some index policies. Both the paradigm and the inverse paradigm have their merits and demerits, which are often mixed in practice.

8 , faster reading, slower writing.

9 , Counter table

If you save a counter in a table, you may experience concurrency problems when you update the counters? What to do, can be designed to 100 lines, each update counter randomly select a row to update, statistics when the sum can be calculated.

Ten , speed alter TABLE speed of operation.

Schema and data type optimization