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

This article introduces the knowledge of "how to build a MySQL high-performance table". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, select the optimized data type

There are many data types supported by MySQL, and choosing the right data type is critical to achieving high performance.

Smaller ones are usually better.

Smaller data types are generally faster because they take up less disk, memory, and CPU cache, and require fewer CPU cycles to process.

Just keep it simple.

Operations with simple data types usually require fewer CPU cycles. For example, integers are cheaper than character operations because character sets and proofreading rules (collations) make character comparisons more complex than integers.

Try to avoid NULL

If the query contains columns that can be NULL, it is more difficult for MySQL to optimize, because columns that can be NULL make indexing, index statistics, and value comparisons more complex. Columns that are NULL will use more storage space, and special handling is required in MySQL. When a column that can be NULL is indexed, each index record requires an extra byte, which may even cause a fixed-size index (such as an index with only one integer column) to become a variable-size index in MyISAM.

Of course, there are exceptions, for example, InnoDB uses separate bits (bit) to store null values, so it has good space efficiency for sparse data.

1. 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: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Use 8pr 16,24,32 and 64-bit storage space, respectively.

The integer type has an optional UNSIGNED attribute, which means that negative values are not allowed, which roughly doubles the upper limit of positive numbers. For example, TINYINT. The storage range for UNSIGNED is 0255while the storage range for TINYINT is-128127.

Signed and unsigned types use the same storage space and have the same performance, so you can choose the appropriate type according to the actual situation.

Your choice determines how MySQL stores data in memory and disk. However, integer computation generally uses 64-bit BIGINT integers, even in 32-bit environments. The exception is some aggregate functions, which are calculated using DECIMAL or DOUBLE.

MySQL can specify a width for an integer type, such as INT (11), which is meaningless for most applications: it does not limit the legal range of values, but simply specifies the number of characters used by some of MySQL's interactive tools, such as the MySQL command line client. For storage and computing, INT (1) and INT (20) are the same.

two。 Real number type

Real numbers are numbers with decimal parts. However, not only do they store decimal parts, but they can also use DECIMAL to store integers larger than BIGINT.

The FLOAT and DOUBLE types support approximate calculations using standard floating-point operations.

The DECIMAL type is used to store exact decimals.

Both floating point and DECIMAL types can specify precision. For DECIMAL columns, you can specify the maximum number of digits allowed before and after the decimal point. This affects the space consumption of the column.

There are several ways to specify the precision required for floating-point columns, which causes MySQL to choose different data types or to make trade-offs between values when storing them. These precision definitions are non-standard, so we recommend that you specify only the data type, not precision.

Floating-point types typically use less space than DECIMAL when storing the same range of values. FLOAT uses 4 bytes of storage. DOUBLE occupies 8 bytes, which has higher precision and larger range than FLOAT. As with integer types, only the storage type can be selected; MySQL uses DOUBLE as the type of internal floating-point calculation.

Because of the extra space and computational overhead, you should try to use DECIMAL only when accurately calculating decimals. However, when the data is largest, you can consider using BIGINT instead of DECIMAL, multiplying the monetary units that need to be stored by the corresponding multiples according to the number of decimal places.

3. String type

VARCHAR

Used to store variable duration strings, length up to 65535 requires 1 or 2 extra bytes to record the length of the string is suitable: the maximum degree of the string is much higher than the average degree; few updates are made

CHAR

Fixed, the range of degrees is 1: 255 suitable: store very short strings, or all values are close to the same length; change frequently

Generosity is unwise.

The space overhead of using VARCHAR (5) and VARCHAR (200) to store 'hello' is the same. So what are the advantages 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. It can be especially bad when sorting or manipulating with temporary tables in memory. It's just as bad when sorting using disk temporary tables.

So the best strategy is to allocate only the space you really need.

4.BLOB and TEXT types

Both BLOB and TEXT are string data types designed to store large amounts of data, which are stored in binary and character modes, respectively.

Unlike other types, MySQL treats each BLOB and text value as a separate object. The storage engine usually does special processing when storing. 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 of storage area within the row to store the actual value.

The only difference between BLOB and TEXT is that the BLOB type stores binary data and has no collation or character set, while the TEXT type has a character set and collation

5. Date and time type

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 is what needs to be done to save the date and time. MySQL provides two similar date types: DATE TIME and TIMESTAMP.

At present, however, we recommend the way to store timestamps, so there is no longer much explanation of DATE TIME and TIMESTAMP.

6. Other types

6.1 Select an identifier

Under the premise that you can meet the needs of the range of values and reserve room for future growth, the smallest data type should be selected.

Integer type

Integers are usually the best choice for identifying columns because they are fast and can use AUTO_INCREMENT.

ENUM and SET types

EMUM and SET types are usually a bad choice for identity columns, although it may not be a problem for some static "definition tables" that contain only fixed states or types. The ENUM and SET columns are suitable for storing fixed information, such as ordered status, product type, and person's gender.

String type

If possible, you should avoid using string types as identity columns because they consume a lot of space and are usually slower than numeric types.

You also need to pay attention to completely "random" strings, such as strings generated by MDS (), SHAl (), or UUID (). The new values generated by these functions can be arbitrarily distributed over a large space, which can cause INSERT and some SELECT statements to become very slow. If you store the UUID value, you should remove the "-" symbol.

6.2 Special types of data

Some types of data wells are not directly consistent with built-in types. One example is timestamps with low millisecond precision. Another example is a 1Pv4 address. People often use VARCHAR (15) columns to store IP addresses. However, they are actually 32-bit unsigned integers, not strings. The representation of dividing an address into four segments with a decimal point is just to make it easy for people to read. So unsigned integers should be used to store IP addresses. MySQL provides the INET_ATON () and INET_NTOA () functions to convert between these two representations.

II. Table structure design

1. Paradigm and anti-paradigm

There are usually many ways to represent any given data, from complete normalization to complete anti-normalization, and a compromise between the two. In a standardized database, each factual data appears and occurs only once. In contrast, in an anti-normalized database, the information is redundant and may be stored in multiple places.

Advantages and disadvantages of paradigm

In order to improve performance, it is often recommended to model schema, especially in write-intensive scenarios.

Stylized update operations are usually faster than de-normalization. When the data is well formatted, there is little or no duplicate data, so only less data needs to be modified. Stylized tables are usually smaller and can be better placed in memory, so operations can be performed faster. Having little extra data means fewer DISTINCT or GROUP BY statements are needed to retrieve list data.

Advantages and disadvantages of anti-paradigm

Without the need for associated tables, the worst-case scenario for most queries-even if the table does not use an index-is a full table scan. This may be much faster than the association when the data is larger than memory, because it avoids the random Imax 0.

Individual tables can also use a more efficient indexing strategy.

Mixed use of paradigm and anti-normalization

Mixing is often needed in practical applications, possibly using partially formatted schema, cached tables, and other techniques.

The table adds redundant fields as appropriate, such as performance priority, but increases complexity. Table association queries can be avoided.

Simple familiarity with database paradigm

First normal form (1NF): field values are atomic and cannot be further divided (all relational database systems meet the first paradigm); for example, the name field, in which the last name and first name are a whole, if you distinguish between last name and first name, you must set up two separate fields.

The second normal form (2NF): a table must have a primary key, that is, each row of data can be uniquely distinguished. Note: the first normal form must be satisfied first.

The third normal form (3NF): a table cannot contain information about non-key fields in other related tables, that is, the data table cannot have residual fields. Note: the second normal form must be satisfied first.

two。 Table field is less refined

Single table 1G volume 500W "row evaluation single" row no more than 200Byte no more than 50 INT fields no more than 20 CHAR (10) it is recommended to control the number of fields in a single table within 20 split TEXT/BLOB,TEXT type processing performance is much lower than VARCHAR, forcing the generation of hard disk temporary tables to waste more space. "how to build a MySQL high performance table" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.