High-performance Mysql -- Schema and data type optimization; high-performance Mysql Schema

Source: Internet
Author: User

High-performance Mysql -- Schema and data type optimization; high-performance Mysql Schema

Good logical design and the foundation of High Performance for physical designers
1. Select an Optimized Data Type
Smaller is usually better
CPU cycle required for smaller disk, memory, CPU cache, and processing
Easy to use
The operation requires less CPU cycles. For example, an integer type is cheaper than an integer type operation, making the character set and verification rules more complex than an integer type. You should use the built-in Mysql type instead of string to store the date and time, and use an integer to store the IP address.
Avoid NULL
In general, it is best to specify the column as not null unless you really need to store the null value. If the query contains null columns, it is more difficult for Mysql to optimize. The null columns are more complex in index, index statistics, and value comparison. When a column that can be null is indexed, each index record requires an additional byte. In Myisam, a fixed-size index may even become a variable-size index. Generally, the performance improvement of a column that can be null is relatively small. If you plan to create an index on a column, try to avoid designing a column that can be null. Of course, there are also exceptions: InnoDB uses a separate bit to store null values, so it has a good space efficiency for sparse data (many null values.
1. Integer type
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64
The integer type can be UNSIGNED, indicating that the value cannot be a negative number, which can double the online positive number.
Mysql can specify the width for the positive data type. For storage computation, int (1) and int (20) are the same.
2. Real Number Type
A real number is a number with a decimal part. The decimal type is used to store precise decimals.
Float uses 4 bytes for storage, and double uses 8 bytes for storage.
3. string type
Varchar and char Types
The varchar type is used to store variable-length strings, which saves more space than the fixed-length type. If the Mysql table is created using row_format = fixed, the fixed-length storage is used for each row, which is a waste of time. Varchar requires one or two additional bytes to record the length of the string. If the maximum length of a column is less than or equal to 255 bytes, only one byte is used. Otherwise, two bytes are used. Varchar saves storage space, but because the rows are variable, the row may become longer during update. In the following situations, varchar is suitable:
The maximum length of a string column is much larger than the average length, and the column is rarely updated, so fragmentation is not a problem; using a complex character set like a UTF-8, each character is stored in different bytes.
InnoDB can store too long varchar as blob
The char type is fixed: Mysql always allocates enough space according to the defined String Length. Char is suitable for storing short strings, or all values are close to the same length. For example, char is very suitable for storing the MD5 value of the password, because it is a fixed length value. Char, which is frequently changed, 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, char (1) is used to store values of only Y and N. If a single-byte character set is used (the string length is defined as not the number of bytes but the number of characters, multi-byte characters require more storage space) only one byte, but varchar (1) requires two bytes, because there is an additional record Length byte.
Blob and text
They are designed to store large amounts of data. They are stored in binary and character formats. Mysql cannot index all their lengths.
Use enumerative type instead of string type
Enumerative stores non-repeated 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 types
Datetime
The precision is second. It encapsulates the date and time into the YYYYMMDDHHMMSS integer and uses 8 bytes of storage space irrelevant to the time zone.
Timestamp
The timestamp is the same as the Unix timestamp. It only uses 4 bytes of storage space, so its range is much smaller than that of datetime, 1970-2038. Mysql provides from_unixtime () the function converts a 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 characters, which is rarely used.
Set
To save many true/false values, consider merging these columns into a set data type.
6. Select an identifier
The ID column may also be used as a foreign key in another table. Therefore, when selecting a data type for the ID column, you should select the same type as the corresponding column in the associated table. The minimum data type should be selected when the value requirements can be met and the future growth space is reserved.
String type
If possible, avoid using the string type as the identification class because they consume space and are generally slower than the numeric type.
If the UUID value is stored, the "-" symbol should be removed, or it is better to use the unhex () function to convert the UUID value to a 16-byte number and store it in a binary (16) column, you can use the hex () function to format it in hexadecimal format.
7. Special Data Types
IPv4 we often use the varchar (15) column to store IP addresses. However, they are actually 32-bit unsigned integers, not strings. The decimal point is divided into four segments to facilitate reading. Therefore, you should use an unsigned integer to store IP addresses. Mysql provides the inet_aton () and inet_ntoa () functions to convert between the two methods.
Ii. cache tables and Summary Tables
We use a cache table to store tables that can obtain data from other tables in the schema. For example:
Count the number of messages sent by a website within 24 hours. It is impossible to maintain a real-time accurate counter on a very busy website. As an alternative solution, a summary table can be generated hourly, which is much more efficient than maintaining real-time counters.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.