MySQL Database design specification

Mysql Database Design specification

1 database All objects are underlined in lowercase letters (because MySQL is case-sensitive)

2 Disable the use of the mysql keyword

3 the column name and data type of the associated column are maintained as much as possible, such as the user ID of the order table user_id and the user ID user_id in the shopping cart table ( Because if different, implicit conversions are made to the associated query, which may invalidate the index on the column )

4 using innodb as the default storage engine

5 Unified using the utf-8 character set,mysql uses a utf-8 character, which consumes three bytes of memory. If varchar can occupy up to a maximum of one byte

6 All tables and fields need to be annotated, and if there is an enumeration meaning, you need to indicate the meaning of the field value as 1 is in progress,2 is completed, etc.

7 The amount of single-table data is best controlled within the million line

what are the advantages and disadvantages of the 8 UUID as a primary key?

9 commonly indexed columns where the clause appears when the order By,group by distinct column appears join Association column appears

principles of establishing a joint index

1 degree of differentiation (the ratio of the number of unique values to the total number of rows) the highest column is placed on the leftmost side of the federated index

2 in the case of a small difference in the first, we should try to put the column with the length of the field on the sitting side

3 avoiding redundant indexes and repeating indexes

try not to use foreign key constraints, foreign key relationships are recommended to be implemented in business code ( because foreign keys degrade performance )

Select the minimum data type that meets your storage needs, such as:

1 converts a string to a numeric type store because the int type takes up less space, (turns IP address into shaping) Inet_aton (' 255.255.255.255 ') = 4294967295, while MySQL also provides reverse function Inet_ntoa (4294 967295) = ' 255.255.255.255 '

2 non-negative select unsigned storage, as this can store more valid numbers

3 The N in VARCHAR (n) represents the number of characters rather than the number of bytes

13 Avoid using types such as Text,blob, which can make SQL performance worse, but it doesn't mean that you can't have two types of users

14 Avoid is the enum type of MySQL, the enumeration itself is a string type, but the internal storage is stored using an integer type, so only 65,535 values can be stored, which is an advantage of the enumeration type, the string is the shaping store

However, there is a flaw in the enumeration type, which requires an ALTER statement to modify the enum value, and the enum needs to have a shape-to-string and a sort operation in order by, which is one of the disadvantages of string shaping. In addition, it is forbidden to use shape- worthy enumeration types

as far as possible, the column is defined as not null, and If you increase the index in such a column,thenull column needs more space to hold the column as a null state value. Therefore, it takes up more index space, the index space is larger, the indexes are more inefficient, and secondly, it needs special handling when comparing and calculating.

do not use string to store date and time the first unusable time function additional strings require more space to store

The sum amount is calculated using the decimal type because this type does not lose precision when calculating

Use precompiled statements as much as possible, first to avoid SQL injection, second to use execution plan multiple times

19 to avoid implicit conversions, SQL implicit conversions typically occur in the where statement when the column type and the parameter type are inconsistent is prone to diet conversions such as where id= ' 11 ' implicit conversion of the primary key ID may result in an invalid index

avoid pre-fuzzy queries and full-fuzzy queries this will not use the index, and the post-fuzzy query will use the index

use the left join and not exists to optimize the not in operation not in the /c12> operation will invalidate the index

ban select *

To avoid using subqueries, you can use the associated query to optimize because the subquery is less efficient first the temporary table returned by the subquery is not indexed either in memory temp table or disk temp table, if the subquery returns a large amount of data it will undoubtedly reduce the SQL performance, the number of temporary tables produced by the last subquery consumes io and the CPU resources produce a lot of slow queries,

To avoid even too many tables,MySQL itself can be linked to a maximum of 5 table query, not more than the recommended list, Association tables too many will appear more associated with the cache, some associated operations will also produce temporary table temporary table is non-indexed, can degrade performance

merging multiple alter operations on the alter Operation will block the table

use in instead of or, the in operation will use the index effectively

The use of the Order by rand () recommendation program to obtain random values is prohibited

Avoid function conversions and calculations in the where clause, so that the index cannot be used

can be used to optimize down

where date (create_time) = ' 20160601 ' can be optimized to

where create_time>= ' 20160601 ' and Create_time < ' 20160602 '

when there is clearly no repetition worth, use Union all instead of Union,Union Will put all the data in a temporary table and then go back to the operation. This deduplication causes a sharp drop in SQL performance when the amount of data is large , and union all does not redo. such as merging multiple types of order operations because an order belongs to only one type, you can use Union all now.

- try to make the data hot and cold separated reduce the width of the table, such as user tables, commonly used fields User name passwords, etc. belongs to thermal data, and Birthday Sex et cetera belongs to cold data , so we also split the cold data into user extension tables achieve the effect of separation

MySQL Database design specification