High performance Mysql--schema and data type optimization

A cornerstone of good logic design and high performance for physics designers
First, select the optimized data type
Smaller is usually better
Consumes less CPU cycles required for disk, memory, CPU caching, and processing
It's simple.
Operations require less CPU cycles, such as: integers are less expensive than character-based operations, making character transmitting integral types more complex for charset and proofing rules. You should use MySQL built-in types instead of strings to store the date and time, and the other is to store the IP address with an integer type.
Try to avoid null
It is generally best to specify column NOT NULL unless you really need to store a null value. If the query contains null columns, it is more difficult to optimize for MySQL, and the null column is more complex for indexes, index statistics, and value comparisons. When a nullable column is indexed, an extra byte is required for each index record, and in MyISAM it can even cause a fixed-size index to become a variable-size index. It is generally possible to bring a nullable column that is not NULL for a performance boost that is relatively small. If you plan to build indexes on columns, you should try to avoid designing nullable columns. Of course there are exceptions: InnoDB uses a separate bit to store null values, so there is a good space efficiency for sparse data (many values are null).
1, Integer type
TINYINT 8
SMALLINT 16
Mediumint 24
INT 32
BIGINT 64
An optional unsigned of integer type indicates that a negative number is not allowed, so that a positive number can be raised one more times.
MySQL can specify a width for positive types, and int (1) and int (20) are the same for storage calculations.
2. Real type
A real number is one with a fractional part. The decimal type is used to store exact decimals
Float uses 4 bytes of storage, double uses 8 bytes
3. String type
varchar and char types
The varchar type is used to store variable-length strings, he is more space-saving than the fixed-length type, and if MySQL tables are created using row_format=fixed, each row uses fixed-length storage, which can be a waste of time. VARCHAR uses 1 or 2 extra bytes to record the length of the string, and if the maximum length of the column is less than or equal to 255 bytes, then only 1 bytes are used, otherwise 2 bytes are used. VarChar saves storage space, but because the rows are mutable, the rows may become longer than they were when the update occurs, and it is appropriate to use varchar in the following situations:
The maximum length of a string column is much larger than the average length, and the column is not updated very often, so fragmentation is not an issue; a complex character set such as UTF-8 is used, each character is stored in a different number of bytes.
InnoDB can store too long varchar as blobs
The char type is fixed-length: MySQL always allocates enough space based on the length of the defined string. Char is suitable for storing very short strings, or all values are close to the same length. For example, char is ideal for storing the MD5 value of a password because it is a fixed-length value. For frequently changed data char is also better than varchar, because the fixed length is not prone to fragmentation. For very short columns, char is more efficient than varchar in storage space. For example: Use char (1) to store only Y and n values, if the single-byte character set (the string length definition is not the number of bytes, is the number of characters, multi-byte characters need more storage space) only need one byte, but varchar (1) requires 2 bytes, Because there is also an extra byte for record length.
Blob and text types
are designed for storing large data, and are stored in binary and character mode, respectively. MySQL cannot index all of their lengths.
Use an enumeration type instead of a string type
Enumeration can store some non-repeating strings into a predefined set.
CREATE TABLE Enum_test (e enum (' Fish ', ' apple ', ' dog ') not null);
INSERT into Enum_test (e) VALUES (' Fish '), (' Dog '), (' Apple ');
4. Date and Time type
Datetime
The precision is seconds, and he encapsulates the date and time into the Yyyymmddhhmmss integer, which is independent of the time zone using 8 bytes of storage space
Timestamp
He and Unix have the same timestamp, he only uses 4 bytes of storage space, so it is much smaller than the DateTime, 1970-2038,mysql provides the From_unixtime () function to convert the UNIX timestamp to a date, depending on the time zone.
5. Bit data type
Bit
You can use the bit column to store one or more true/false values in a column. The maximum length of a bit column is 64 bits, which is less used in practice.
Set
If you need to save many true/false values, consider merging these columns into a set data type.
6. Select identifiers
The identity column may also be used as a foreign key in another table, so when you select a data type for the identity column, you should choose the same type as the corresponding column in the associated table. You should choose the smallest data type if you can meet the requirements of the value and reserve future growth space.
String type
If possible, you should avoid using string types as identity classes because they are space-consuming and often slower than numeric types.
If you store UUID values, you should remove the "-" symbol, or better cook is to use the Unhex () function to convert the UUID value to 16-byte numbers, and stored in a binary (16) column, retrieved by the hex () function can be formatted in hexadecimal format.
7. Special data types
IPV4 we often use varchar (15) Columns to store IP addresses, however they are actually city 32-bit unsigned integers, not strings. The notation for dividing the decimal points into 4 paragraphs is just to make it easier for people to read. Therefore, the IP address should be stored with an unsigned integer. MySQL provides the Inet_aton () and Inet_ntoa () functions to convert between the two methods.
Ii. Cache tables and Summary tables
We use cache tables to represent stores of tables that can be relatively simple to fetch data from other tables in the schema. As an example:
If you want to count the number of messages sent within 24 hours of a site. In a very busy website it is impossible to maintain a real-time accurate counter. As a workaround, you can generate a summary table every hour, much more efficiently than maintaining a real-time counter.

High performance Mysql--schema and data type optimization