High Performance MySQL summary (one data type optimization)

Select the optimized data type

MySQL supports a very high number of data types, and choosing the right data type is critical to gaining performance. No matter what type of data you store, here are a few simple principles that can help you make a better choice of menial jobs.

The smallest is usually better

In general, you should try to use the smallest data type that correctly stores your data. The smallest data types are usually faster because they consume less disk, memory, and CPU cache, and require less CPU cycles to process.

But make sure you don't underestimate the range of values that need to be stored, because increasing the range of data types in multiple places in the schema is a very time-consuming and painful operation. If you cannot determine which data type is best, select the smallest type that you think will not exceed the range.

It's simple.

Operations of simple data types typically require less CPU cycles. For example, an integer is less expensive than a word Fu Cao, because character sets and collation rules (collations) make character comparisons more complex than integral types. Here are two examples: you should use MySQL built-in types (date,datetime, etc.) instead of strings to store dates and times, and the other is to store IP addresses with integer types.

Try to avoid null

Many tables contain nullable columns, even if the application does not need to save NULL, because nullable is the default property of the column. It is generally preferable to specify column NOT NULL unless you really need to store a null value.

If the query contains nullable values, it is more difficult for MySQL to optimize because nullable column-like indexes, index statistics, and value comparisons are more complex. Nullable columns use more storage space, and special handling is required in MySQL. When a nullable column is indexed, an extra byte is required for each index record, and in MyISAM it may even cause a fixed-size index (for example, an index of only one integer column) to become a variable-size index.

It is generally possible to change a null column to NOT NULL to bring a smaller performance boost, so it is not necessary to first look up and modify this in the existing schema, unless you are sure that this will cause problems. However, if you plan to index on the column, you should try to avoid the design of nullable columns (I think: can be null is not necessarily do not go index, there are too many baseless assertion on the internet, self-test can come to a conclusion)

Of course there are exceptions, for example, it is worth mentioning that InnoDB uses separate bits (bit) to store null values, so for sparse data (many values are null, only a few rows of columns have non-null values) have a good space efficiency. But this does not apply to MyISAM.

MySQL basic data type integer type

If you store integers, you can use these kinds of integer types: tinyint,smallint,mediumint,int,bigint. Use 8,16,24,32,64 bit storage space, respectively. They can store values ranging from -2 (N-1) to 2 (N-1)-1, where N is the number of bits of storage space.

An integer type has an optional unsifned property, which means that negative numbers are not allowed, which can roughly increase the upper limit of a positive number by a factor. For example, Inyint unsigned can store a range of 0~255, while the tinyint storage range is -128~127.

Signed and unsigned types use the same storage space and have the same performance, so you can choose the right type based on the actual situation.

MySQL can specify widths for integer types, such as int (11), which makes no sense for most applications: it does not limit the legal scope of a value, but only specifies how many of the MySQL interactive tools (such as the MySQL command-line client) are used to display the number of characters. For storage and Computation, int (1) and int (20) are the same.

Real type

A real number is one with a fractional part. However, they are not just for storing fractional parts; You can also use decimal to store integers larger than bigint. MySQL supports both exact types and imprecise types.

The float and double types support approximate calculations using standard floating-point operations. If you need to know how floating-point arithmetic is calculated, you need the specific implementation of the floating-point number of the platform used by the Institute.

The decimal type is used to store exact decimals. In Mysql5.0 and later versions, the decimal type supports accurate calculations. Mysql4.1 and the earliest versions use floating-point arithmetic to achieve decimal calculations. This will cause some strange problems due to the loss of precision. In these versions of MySQL, decimal is just a "storage type".

Because the CPU does not support direct computation of decimal, the MySQL server itself implements a high-precision calculation of Deciaml in Mysql5.0 and later versions. In contrast, the CPU directly supports native floating-point calculations, so floating-point operations are significantly faster.

There are many ways to specify the precision required for floating-point columns, which allows MySQL to quietly select different data types, or to trade off values when stored. These precision definitions are non-standard, so we recommend specifying only the data type and not specifying the precision.

Floating-point types typically use less space than decimal when they store values of the same range. Float uses 4 bytes of storage. Double uses 8 bytes, which has a higher precision and greater range than float. As with the integer type, only the storage type can be selected; MySQL uses double to act as a type for internal floating-point calculations.

Because additional space and computational overhead are required, you should try to use decimal--, for example, to store financial data only when you are making accurate calculations of decimals. However, when the data volume is relatively large, consider using bigint instead of decimal, and the currency units that need to be stored are multiplied by the number of decimal places. Suppose you want to store financial data to one out of 10,000 points. You can multiply all the amounts by 1 million, and then store the results in bigint, which avoids the problem of inaccurate floating-point storage calculations and the high cost of decimal precision.

String type

MySQL supports a variety of strings, and there are many variants of each type. These types have changed greatly in versions 4.1 and 5.0, making the situation more complicated.

varchar and char types are two of the most important string types. Unfortunately, it is difficult to explain precisely how these values are stored on disk and in memory, because of the specific implementation of this storage engine, the following description assumes that the storage engine used is InnoDB or MyISAM.

VARCHAR

The varchar type is used to store variable-length strings and is the most common string data type, which is more space-saving than a fixed-length type because it uses only the necessary space (for example, shorter strings use less space). In one case, if MySQL tables were created using row_format=fixed, each row would use fixed-length storage, which would be a waste of space.

VARCHAR uses 1 or 2 extra bytes to record the length of a string: if the maximum length of a column is small or equal to 255 bytes, only 1 bytes are used, otherwise 2 bytes are used. Assuming that the latin1 character set is used, a varchar (10) column requires 11 bytes of storage space. VARCHAR (1000) requires 1002 bytes because 2 bytes are required to store the length information.

VarChar saves storage space, so it is also useful for performance. However, because the rows are longer, you may make the rows longer than they were when you update, which leads to additional work if a row occupies more space and there is no more space within the page to store, in which case the different storage engines are handled differently. For example: MyISAM will split the rows into different fragments for storage, InnoDB will need to split the page so that the rows can be placed in the page.

In these cases, it is appropriate to use varchar: The maximum length of the string column is much larger than the average length, the column is not updated very often, so fragmentation is not a problem, and a complex character set such as UTF-8 is used, each character is stored in a different number of bytes.

CHAR

The char type is fixed-length: MySQL always allocates enough space based on the length of the defined string. When you store a char value, MySQL deletes all trailing spaces.

Char is suitable for storing very short strings, and 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. Char is also better than varchar for frequently changed data because the fixed-length char type is not prone to fragmentation. For very short columns, char is more efficient than varchar in storage space. For example, using char (1) to store only Y and n values, if a single byte character set takes only one byte, but varchar (1) requires two bytes, because there is an extra byte for the length of the record.

Types similar to char and varchar also have binary and varbinary, and they store binary strings. Binary strings are very similar to regular strings, but binary strings store things like bytecode instead of characters.

　　　　

Blob and text types

Are the string data types that are involved in storing large data, not binary and character-based storage.

In fact, they belong to different data type families: The string type is tinytext,smalltext,text,meduimtext,longtext; the corresponding binary type is Tinyblob,smallblob,blob, Meduimblob,longblob. A blob is a synonym for Smallblob, and text is a synonym for smalltext.

Unlike other types, MySQL treats each blob and text value as a separate object. The storage engine typically does special processing when it is stored. When the blob and text values are too large, InnoDB uses a dedicated "external" storage area for storage, where each value needs 1~4 bytes in the row to store a pointer, and then stores the actual values in the internal storage area.

MySQL cannot index a string of all lengths of BLOBs and text columns

Date and Time type

There is no substitute for most time types, so there is no problem with the best choice. The only question is what to do when you save the date and time. MySQL offers two similar date types: datetime and timestamp. For many applications, they can all work, but in some scenarios, one works better than the other.

Datetime

This type can hold a wide range of values, from 1001 to 9999, with a precision of seconds. It encapsulates the date and time in an integer formatted as YYYYMMDDHHMMSS, regardless of the time zone. Use 8 bytes of storage space.

TIMESTAMP

The timestamp type holds the number of seconds since January 1, 1970, which is the same as the Unix timestamp. Timestamp uses only 4 bytes of storage space, so its range is much smaller than datetime: it can only be represented from 1970 to 2038.

The values displayed by timestamp also depend on the time zone. Therefore, a timestamp with a value of 0 is displayed as "1969-12-31 19:00:00" in the Eastern U.S. time zone, which is 5 hours from GMT. It is important to emphasize this distinction: if multiple time zones store or access data, the behavior of Timestamp and datetime will be very different. The value provided by the former is related to the time zone, while the latter retains the date and time of the text representation.

Timestamp also has special properties that are not datetime. By default, if you do not specify the value of the first timestamp column when inserting, MySQL sets the value of this column to the current time. When inserting a row of records, MySQL defaults to updating the value of the first timestamp column (unless a value is explicitly specified in the UPDATE statement). You can configure the insert and update behavior of the timestamp column. Finally, timestamp Lieme considers not NULL, which is not the same as other data types.

In addition to special behavior, you should always use timestamp as much as possible, as it is more efficient than datetime space. Sometimes people store timestamps as integer values, but that doesn't bring any benefit. The format of saving timestamps with integers is often inconvenient, so we do not recommend this.

What if you want to store a date and time that is smaller than the second granularity? MySQL does not currently provide the appropriate data type, but can use its own storage format: You can use the bigint type to store a microsecond-level timestamp, or use a double to store the fraction of a second. Both of these ways are possible.

High Performance MySQL summary (one data type optimization)