Shulou(Shulou.com)06/01 Report--

Because of the unique features and implementation details of Mysql, the impact on performance is obvious, because it is critical to do a good job in designing the Mysql database. For database design, we have to mention the type selection of table fields. Because there are so many data types supported by Mysql, how to choose the right data type is very important to achieve high performance. Regardless of the type of data to be stored, we need to consider it according to some database design principles.

Thoughts on the selection of data types

Smaller ones are usually better (in general, you should use the smallest data type that stores the data correctly as much as possible. )

Why?

(1) because smaller data types are generally faster, because they take up less disk, memory, and CPU cache, and they require shorter CPU cycles for processing.

(2) make sure that the range of values you need to store is not underestimated, which is smaller relative to the maximum range of values of the data type.

(3) if you are not sure which data type is the best, choose the smallest type that you think will not exceed the range.

Just keep it simple (operations with simple data types usually require shorter CPU cycles. )

Why? Here are a few examples of why.

(1) Integers are cheaper than string operations because string sets and proofreading rules (collation rules) are more complex than integers.

(2) the date and time should be stored using the built-in type of Mysql (date,time,datatime).

(3) the storage of IP addresses should be int.

Try to avoid NULL (null value)

Why?

(1) many tables contain columns that can be NULL, even if the program does not need to save NULL, because the default property of the column is NULL. In general, it is best to specify the column NOT NULL unless you really need to store the NULL.

(2) if the query contains columns that can be NULL, it is difficult for Mysql to optimize, because the columns of NULL make the index, index statistics and value comparison more complex. Columns that are NULL will use more storage space, and special handling is required in Mysql. When NULL-capable columns are indexed, each index record requires an extra byte, which may even cause a fixed-size index to become a variable-size index in MyISAM.

(3) generally speaking, changing the column that can be NULL to NOTNULL brings little performance improvement, so if you plan to index on the column, you should try to avoid designing columns that can be NULL. (one exception is that in InnoDB, separate bits (bit) are used to store null values, so it is space efficient for sparse data. )

Summary

When selecting a data type for a column, the first step is to determine the appropriate large type (number, string, time, and so on), which is usually simple, so the next step is to select a specific type.

Many Mysql data types can store the same type of data, but the length and range of storage, the precision allowed, or the physical space (disk and memory space) required are different. Different subtypes of the same large type of data sometimes have some special behaviors and attributes. For example, both DATATIME and TIMESAMP columns can store the same type of data (time and date) and are accurate to seconds, while TIMESTAMP uses only half the storage space of DATATIME and has special automatic update capabilities depending on the time zone. In addition, the time frame allowed by TIMESTAMP is much smaller, and sometimes its special capabilities become obstacles, which are all things we developers need to consider.

Integer type

There are two types of numbers: integers (whole number) and real numbers (real number).

If you store integers, you can use these integer types: TINNYINT (8), SMALLINT (16), MEDIUMINT (24), INT (32), BIGINT (64).

The integer type has an optional UNSIGNED attribute, which means that negative values are not allowed, which can roughly double the upper limit of positive numbers.

For example, the storage range of TINYINT UNSIGNED is 027255, while the storage range of TINYINT is-12700128.

Signed and unsigned types use the same storage space and have the same function.

Therefore, the appropriate type can be selected according to the actual situation.

Your choice determines how Mysql stores data in memory and disk.

Integers generally choose 64-bit BIGINT integers, even in 32-bit environments. (with the exception of some aggregate functions, which are calculated using DECIMAL or DOUBLE)

Mysql can specify the width for the integer type.

For example, INT (11) is meaningless for most applications: it does not limit the legal range of values, but only specifies the number of characters that Mysql's interactive tools (such as the Mysql command line client) use to display characters. For storage and computing, INT (1) and INT (20) are the same.

Some third-party storage engines (such as Infobright) sometimes have custom storage formats and compression schemes that do not necessarily use the common Mysql built-in engine.

Real number type

Real numbers are numbers with decimal parts.

Not only do they store decimal parts in the future, but they can also use DECIMAL to store integers larger than BIGINT. Mysql supports both precise and imprecise types. The DECIMAL type is used to store exact decimals.

Precise operations are supported in Mysql5.0 or later, while exceptions (mainly due to loss of precision) can occur when floating-point operations are used in Mysql4.1 and earlier versions. Both FLOAT and DECIMAL types can specify progress.

For DECIMAL columns, you can specify the maximum number of digits allowed before and after the decimal point, which affects the space consumption of the column. There are many ways to specify the precision required by FLOAT (floating point) columns, which will cause Mysql to quietly select different data types, or to choose values when storing, but these precision is often non-standard, so it is generally recommended to specify only data types rather than precision.

Because of the extra space and computational overhead, you should try to use DECIMAL only when accurately calculating decimals. For example, store financial data, but if the amount of data is relatively large, you can consider using BIGINT instead of DECIMAL, multiplying the monetary units that need to be stored by the corresponding multiples according to the decimal places. The FLOAT and DOUBLE types support approximate calculations using standard floating-point operations.

String type

Mysql supports a variety of string types, each of which has many variants. VARCHAR and CHAR are the two main string types.

Note: the way the Mysql storage engine stores CHAR or VARCHAR values may be different in memory and on disk, so the values read by the Mysql server from the storage engine may need to be converted to a different storage format.

The VARCHAR type is used to store variable-length strings and is the most common string data type.

VARCHAR saves more space than a fixed-length type because it uses only the necessary space (the shorter the string, the less space).

VARCHAR needs one or two extra bytes to record the length of the string.

VARCHAR saves storage space, so it is helpful for performance.

Here are some scenarios where VARCHAR is suitable for use:

(1) the maximum length of a string column is much larger than the average length.

(2) columns are rarely updated, so fragmentation is not a problem.

(3) A complex character set such as UTF-8 is used, and each character is stored with a different number of bytes.

The CHAR type is fixed in length. (Mysql always allocates enough space based on the defined string length.)

CHAR is suitable for storing very short strings, or all values are close to the same length.

Similar to VARCHAR and CHAR are BINARY and VARBINARY, which store binary strings.

Note: the space overhead of using VARCAHR (5) and VARCHAR (200) to store "hello" is the same, so what is the advantage of using shorter columns? (it turns out to have a great advantage.)

Longer columns consume more memory because Mysql usually allocates fixed-size blocks of memory to hold internal values. In particular, it can be particularly bad when sorting or manipulating with temporary tables in memory. It's just as bad when sorting using disk temporary tables.

Note: in the final analysis, the best strategy is to allocate only the space you really need.

BLOB and TEXT types

Both BLOB and TEXT are string data types designed to store large amounts of data, using binary and character storage, respectively.

In fact, they belong to two different data type families: string types are TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

Binary types are TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB

ENUM Typ

You can use ENUM instead of string types. In many cases, it is recommended to use enumerated columns instead of commonly used string types.

(1) enumerated columns can store some non-repeating strings into a predefined collection.

(2) Mysql is very compact in storing enumerations and is compressed into one or two bytes according to the number of list values.

(3) Mysql internally saves the position of each value in the list as an integer and saves the "lookup table" of the "number-string" mapping relationship in the .frm file of the table.

Note: one surprising thing is that enumerated fields are sorted by internally stored integers rather than defined strings.

Note: the worst thing about enumerations is that the list of strings is fixed, and you must use ALTER TABLE to add or remove strings, so using enumerations is not a good idea for a series of strings that may change in the future, unless you accept that you can only add elements at the end of the list.

Note: enumerated columns have some overhead because Mysql saves each enumerated value as an integer and must be looked up before it can be converted to a string.

Date and time type

There are many types of Mysql to hold date and time values, such as YEAR and DATE.

The minimum time granularity that Mysql can store is seconds (MariaDB supports microsecond event types). But Mysql can also use microsecond-level granularity for temporary operations.

Most of the time there is no substitute for the type, so there is no question of what is the best choice.

The only question then is what needs to be done to save the date and time.

DATETIME

(1) this type can hold a wide range of values, from 1001 to 9999, with an accuracy of seconds.

(2) DATETIME encapsulates the time and date into integers in the format YYYYMMDDHHMMSS, regardless of the time zone.

(3) DATETIME uses 8 bytes of storage space.

TIMESTAMP

(1) the TIMESTAMP type holds the number of seconds since midnight on January 1, 1970, which is the same as the UNIX timestamp.

(2) TIMESTAMP uses only 4 bytes of storage space, so its range is much smaller than DATETIME.

(3) the value displayed by TIMESTAMP depends on the time zone.

Comparison of DATETIME and TIMESTAMP:

(1) by default, if no value for the first TIMESTAMP column is specified when inserting, Mysql sets the value of this column to the current time. (this is a feature that DATETIME does not have)

(2) when inserting a row of records, Mysql also updates the value of the first TIMESTAMP column by default.

(3) the TIMESTAMP column defaults to NOT NULL, which is different from other data types.

Summary

(1) in addition to special behavior, you should usually use TIMESTAMP as much as possible, because it is more efficient than DATETIME space.

(2) generally speaking, it is not recommended to save UNIX timestamps as integer values, which will not bring any benefits. Saving timestamps in integer format is usually not convenient to deal with.

(3) if you need to store date and time values with a granularity smaller than seconds, you can use BIGINT type to store microsecond timestamps, or use DOUBLE to store decimal parts after seconds, or you can use MariaDB instead of Mysql.

Bit data type

BIT defines a field that contains a single bit, and BIT (2) stores 2 bits, with a maximum length of 64 bits.

Note: it is generally recommended to use the BIT type with caution, and it is best to avoid it for most applications.

Select identifier

It is important to choose the appropriate data type for the identifier (identity column).

Generally speaking, it is more likely to compare identity columns with other values, or to find other columns through identity columns.

When choosing the type of identity column, you need to consider not only the storage type, but also how Mysql performs calculations and comparisons on this type.

Once you have selected a type, make sure that the same type is used in all associated tables.

On the premise that the range of values can be met and room for future growth is reserved, the smallest data type should be selected.

Note: integers are usually the best choice for identifying columns because they are fast and can use AUTO_INCREMENT. Note: ENUM and SET are the worst choices; avoid using strings as identity columns if possible, because they are space-consuming and usually slower than numeric classes.

These are the details of the understanding of MySQL data types, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.