MySQL Schema and data type optimization, mysqlschema

MySQL Schema and data type optimization, mysqlschema

Select the Optimized Data Type:

1. Smaller ones are usually better:

In general, try to use the minimum data type that can properly store data. Smaller data types are usually faster, because they occupy less disk, memory, and cpu cache, and require less cpu cycles for processing.

2. Easy to use

Simple data type operations usually require less cpu cycles. For example, integer types are cheaper than character operations because character sets and collation rules make character comparison more complex than integer types. Note: The built-in mysql Data type should be used to store the time and date, instead of the string.

3. Avoid null

If a query contains a column that can be null, it is more difficult for Mysql to optimize it, because a column that can be null makes the index, index statistics, and values more complex. Columns that can be null use more storage space, which must be specially processed in mysql. When a column that can be null is indexed, each index record requires an additional byte.

Changing a column that can be null to not null usually results in a relatively small performance improvement. During optimization, there is no need to first find and modify the situation in the existing schema, unless it is determined that this will cause problems. However, if a column is indexed, avoid designing a column that can be null.

Integer type:

Integer types include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. Use 8, 16, 24, 32, and 64-bit buckets respectively. They can store values ranging from (n-1) to (n-1) to-1, where n is the number of digits in the bucket.

The integer type has an optional UNSIGNED attribute, indicating that negative values are not allowed, which can be roughly doubled as the upper limit of positive numbers. For example, tinyint unsigned can store 0 ~ 255, while the storage range of TINYINT is-128 ~ 127.

Real Number Type:

A real number is a number with a decimal part. Then, they not only store the fractional part, but also can use DECIMAL to store integers larger than BITINT. MYSQL supports both precise and inaccurate types.

The DECIMAL type is used to store precise decimals, because the double and float types may cause some data deviations due to loss of precision During computation. However, the DECIMAL data type cpu does not support direct computation. The cpu directly supports native floating point computation, so the floating point computation is obviously faster.

Because extra space and computing overhead are required, we recommend that you use DECIMAL only when accurately calculating DECIMAL places-for example, to store financial data. However, when the data volume is large, you can use BITINT instead of DECIMAL to multiply the unit of currency to be stored by the corresponding multiples of the DECIMAL digits.

String type (varchar and char)

• Varchar:

The Varchar type is used to store variable-length strings. It is the most common string type. It saves more space than the fixed-length type because it only uses the necessary space (for example, the shorter the string, the less space ). Therefore, varchar saves storage space and helps with performance. However, because the row is side-length, the update operation may make the row longer than the original one, which leads to additional operations. If the space occupied by a row increases and no more space is stored on the page. MyISAM splits rows into different fragments for storage, while InnoDB splits the pages so that the rows can be put into the pages.

Note: InnoDB is more flexible. It can store too long varchar as BLOB.

• Char type:

Char is suitable for storing short strings, or all values are close to the same length. For example, the char type is very suitable for storing the md5 value of the password, because it is a fixed length value. The user's ID card number and mobile phone number are also provided. For frequently changed data, char is also better than vachar, because fixed-length char types are not prone to fragmentation. For very short columns, char is more efficient than varchar in storage space. For example, if char (1) is used to store values of Y and N, if the single-byte character set requires only one byte, but varchar (1) requires two bytes, because there is an additional byte of record length.

The overhead between Varchar (5) and varchar (200) Storage 'Hello' is the same, but Varchar (5) has great advantages, because longer columns consume more memory, Mysql usually allocates fixed memory blocks to save internal values. Especially when using memory temporary tables for sorting and operations, it will be particularly bad. The sorting of temporary disk tables is also bad.

Time Type

DateTime and TimeStamp

• DateTime

This type can save a large range of values, from January 1, 1001 to January 1, 9999, with a precision of seconds. It encapsulates the date and time in an integer in the format of YYYYMMDDHHMMSS, regardless of the time zone. Uses 8 bytes of storage space.

• TimeStamp:

Stores the description since midnight, January 1, January 1, 1970, which is the same as the Unix timestamp. TimeStamp only uses 4 bytes of storage space, so its range is much smaller than that of DateTime; it can only represent from January 1, 1970 to January 1, 2038.

The value displayed by TimeStamp also depends on the time zone. Mysql servers, operating systems, and client connections all have time zone settings.

TimeStamp also has special attributes not available for DATETIME. By default, if the value of the first TimeStamp column is not specified during insertion, Mysql sets the value of this column to the current time. When a row of records is inserted, Mysql also updates the insert and update operations of the first TimeStamp column by default. The default value of the TimeStamp column is not null, which is different from other data types.
For more MySQL database-related technologies, please refer to Shanghai shangxue's MySQL cloud Database Service Architecture Exploration