High performance MySQL (quad)-schema and Data type optimization (1)

Schema and data type optimization Select an optimized data type

Here are some simple principles:

1. Smaller is usually better

   In general, you should try to use the smallest data types that can be stored correctly. Such as: only need to store 0-200, tinyint unsigned is better. Small data types account for less disk, memory, and CPU cache, and require fewer CPU cycles to process.

2. It's simple.

   Simple data type amount operations typically require less CPU cycles. For example, you should use MySQL's built-in type to store time and date instead of strings. For example, an integer storage IP address should be used.

3. Try to avoid null

   1. It is generally preferable to specify the column as NOT null unless you really need null. When you query a column that contains nullable columns, it is difficult for MySQL to optimize. Because a null column exists, it is more complex to make indexes, index statistics, and worth comparisons. and null columns require more storage space. When a null column is indexed, an extra byte is required for each index record.
   2. It is generally not necessary to change NULL to NOT NULL to improve performance, so there is no need to find and modify existing schemas. However, if you plan to add indexed columns, try to avoid columns that are designed to be null.
   3. Exception: InnoDB uses a separate bit to store null, so there is good space efficiency for sparse data (many values are null).

When selecting a data type, the first step is to determine the appropriate large type: number, string, time, and so on. The next step is to select the specific type. Many MySQL data types can store the same type of data, except that the length and range of the storage are different, the accuracy allowed is not the same, or the physical space required. For example, datetime and timestamp can store the same type of data: Date and time, accurate to seconds. However, the timestamp uses only a datetime half of the storage space, and will change according to the time zone, with special Automatic update capability. But the timestamp allows for a much smaller time frame. , sometimes its special ability becomes an obstacle.

Integer type

1. The type of the stored integer: Tinyint,smallint,mediumint,int,gigint. The storage space used separately is 8,16,24,32,64 bit storage space. They're just. The scope of storage is: (?2) N? 1 To 2N ?1 , n is the number of bits of storage space.
3. An integer type has an optional unsigned property, which means that negative values are not allowed, which can roughly increase the upper limit of an integer by a factor.
5. MySQL can specify widths for integer types, such as int (11), which makes no sense for most applications. It does not limit the worth of legal scope. It just specifies the number of characters the MySQL interactive tool (client) uses to display. Int (1) and int (20) are the same for storage and computation.

Real type

A real number is one with a fractional part. MySQL supports exact types and also supports imprecise types.

1. The float and double types support approximate calculations using standard floating-point operations.
2. The decimal type is used to store exact decimals. The decimal type supports accurate calculations in the mysql5.0+ version.
3. Both floating-point and decimal types can specify precision. For the decimal column, you can specify the maximum number of digits allowed before and after the decimal point. This affects the space consumption of the column.

String type

2. VARCHAR

   1. Used for variable-length strings.
   2. Requires 1 or 2 extra bytes to record the length of the string. If the maximum length of a column is less than or equal to 255, the value requires 1 bytes, otherwise 2 bytes are used.
   3. MySQL will delete the trailing space in the mysql4.1-version, and in the 5.0+ version, MySQL retains the trailing space when it is stored and retrieved.
   4. The InnoDB stores the long varchar as blobs.

4. CHAR

   1. Fixed length. All trailing spaces are deleted.
   2. Suitable for storing very short strings, or all values are close to the same length.
   3. The MD5 value for storing passwords, as this is a fixed length.
   4. Suitable for frequently changing data because the length is not prone to fragmentation.
   5. Suitable for very short columns. A single-byte character set requires only one byte, but varchar (1) requires two bytes.

5. The behavior of padding and intercepting spaces

   2. It is the same on unused storage engines, because this is handled at the MySQL server layer.

   3. Use the following command to verify:

       CREATE TABLE ' char_test ' ( ' Char_col ' char(ten) not  NULL DEFAULT ', ' varchar_col ' varchar  not NULL default ') engine=innodb default Charset=utf8;Insert  intoChar_test (Char_col, Varchar_col)Values(' string1 ',' string1 '),(' string2 ',' string2 '),(' String3 ',' String3 ')SelectConcat"'", Char_col,"'"), Concat ("'", Varchar_col,"'") fromChar_testSelect* fromChar_testwhereChar_col ="String3"  andVarchar_col ="String3"

      Note the padding and interception of spaces.

Binary and varbinary

2. Stores a binary string. A binary code stores a byte code instead of a character.
4. The padding is not the same: the MySQL fill binary uses the \ s (other bytes) instead of spaces, and does not remove the padding value when retrieving it.
5. Advantages of the

6. Binary comparison:

   1. Case sensitive.

      select binary ' abcd ' = ' ABCD ' COM1, ' abcd ' = ' ABCD ' com2;+--------+-----------+  | COM1 |      COM2 |+--------+-----------+  |      0 | 1 |+---------+-----------+  1 row in Set (0.00 sec) SELECT * from usertest where username = binary  ' ab '   

   2. Is compared by the number of bytes, so a binary comparison is much simpler than a character, so it is faster.

8. Blob and text types

   1. Are string data types that are designed to store large data and are stored in binary and character mode, respectively.
   2. Character type: Tinytext,smalltext,text,mediumtext,longtext.
   3. Binary type: Tinyblob,smallblob,blob,mediumblob,longblob.
   4. 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 is stored using a dedicated "external" storage area, where each value needs 1~4 bytes in the row to store a pointer, and then stores the actual value in the external storage area.
   5. The difference between blob and text is that the BLOB type stores binary data, there is no collation or character set, and the text type has character sets and collations.
   6. MySQL sorts blobs and text differently than other types: it sorts only the first max_sort_length bytes of each column, not the entire string.

10. Using enumerations instead of string types

    1. MySQL is very compact when storing enumerations, and is compressed to one to two bytes depending on the number of lists worth.
    2. Using numbers as enumeration types is not recommended for storage.
    3. MySQL internally saves each value in the list as an integer and holds the lookup table for the number-string mapping relationship in the table's. frm file.
    4. Enumeration fields are sorted by an internally stored integer rather than a defined string.
       
       1. So if you want to bypass this restriction, then define the enumeration columns in the order that you want them.
       2. You can specify the sort order in which the query is displayed using the field () function, but this causes MySQL to not be able to take advantage of the index elimination sort.

Date and Time type

1. Datetime
   The value that this type can hold: From 1001 to 9999, the accuracy is wonderful. It encapsulates the time and date in a formatted YYYYMMDDHHMMSS integer, regardless of the time zone, and is stored in 8 bytes.

2. TIMESTAMP

   1. It holds the number of seconds from midnight January 1, 1970, which is the same as the Unix timestamp.
   2. But it only uses 4 bytes, so the range represented is a little smaller. can only represent 1970-2038.
   3. The From_unixtime () function in MySQL converts a Unix timestamp to a date.
   4. The Unix_timestamp () function converts a date to a Unix timestamp.
   5. Depends on the time zone.

Bit data type

All bit data, regardless of the underlying storage format and processing method, is technically a string type.

1. BIT

   MySQL handles the bit as a string instead of a number. When the bit (1) is retrieved, the result is a string containing binary 0 and 1, instead of the ASCII code "0" and "1".

2. SET

   You can save a lot of true and false values. Set is represented within MySQL as a collection of packaged bits.

Select an identifier

It is important to select the type for the identity column.

1. Integer type

   Integers are usually the best choice for identifying columns. Because they are fast and can use Auto_increment.

2. Enumeration types and set types

   It is not recommended to use these two as identity columns.

3. String type

   If possible, you should avoid using strings as identity columns because they are very space-consuming and generally slower than integer types.

   1. It is also important to be aware of a completely "random" string. For example: MD5 (), SHA1 () or UUID () the resulting string. The new values generated by these functions are arbitrarily distributed in a large amount of space, which causes the insert and select to become very slow.

   2. Reason:

      1. Because the insertion value is randomly written to a different location in the index, the INSERT statement is slow.
      2. The SELECT statement will become slower. Because logically adjacent rows are distributed in different places on disk and memory.
      3. Random values cause the cache to become poorly for all types of query statements. Because the caching work relies on the access locality principle to fail. There will be a lot of misses.

Special types of data

Some data types are not directly consistent with the built-in data types.

1. A timestamp below the second level of accuracy is an example.
2. IP storage is a 32-bit unsigned integer, not a string. MySQL provides the Inet_aton () and Inet_ntoa () functions between integers and .表示 conversions.

Resources:
High-performance MySQL (3rd edition)
Note:
Reprint Please specify source: http://blog.csdn.net/wsyw126/article/details/53454823
WSYW126

High performance MySQL (quad)-schema and Data type optimization (1)