Evolution of High-Performance MySql (2): Optimization of Data Types_Part 2

· blob/text
often requires two large data data in actual applications. Text e.g. an article of tens of thousands of words. In Oracle, there are BOLB and CLOB to deal with these two types of data, while in MySQL they correspond to BLOB and TEXT.
In view of the particularity of these two data types, the storage and operation of BLOB and TEXT are done in MySQL. Special processing:
    1) BLOB/TEXT values ​​are often treated as objects. These objects have their own IDs and independent storage spaces
    2) When BLOB/TEXT values ​​are used for sorting , only the first N bytes will be used, N corresponds to a constant value in the database (max_sort_length), if you want to specify more bytes to be used for sorting, then you can increase the value of max_sort_length or use ORDER BY SUBSTRING(column, length) function to process
3) When BLOB/TEXT is used for indexing or sorting, the value of the entire field cannot be used.
Avoid using BOLB/TEXT as a last resort Indexing or sorting

Because MySQL's Memory engine does not support BLOB and TEXT types, if BLOB/TEXT is involved in the query process, you need to use a MyISAM disk temporary table, even if there are only a few rows of data. This is true (the Memory engine in the latest Percona Server supports BLOB and TEXT types).

The Memory engine's frequent access to disk temporary tables will cause serious performance overhead. The best solution is to avoid using the BLOB and TEXT types as much as possible. If you can't avoid it, a trick is to use SUBSTRING(column, length) everywhere a BLOB field is used to convert the column value to a string (also works in the ORDER BY clause), so that you can use an in-memory temporary table . However, make sure that the intercepted substring is short enough so that the size of the temporary table does not exceed max_heap_table_size or tmp_table_size. After exceeding the limit, MySQL will convert the memory temporary table to a MyISAM disk temporary table.

The worst-case length allocation is the same for sorting, so this trick is useful for creating large temporary tables and file sorting in memory, and creating large temporary tables on disk. and file sorting are helpful in both cases. For example, assume you have a 10 million-row table that takes up several gigabytes of disk space. There is a VARCHAR(1000) column with utf8 character set. Each character uses up to 3 bytes, requiring 3,000 bytes of space in the worst case. If this column is used in ORDER BY and the query scans the entire table, more than 30GB of temporary tables will be needed for sorting

##· DATETIME/TIMESTAMP

MySQL contains two time formats DATETIME and TIMESTAMP, Usually the difference between the two types is not very big during use, but there are still differences in details

Because TMESSTAMP takes up less storage space, it can be used It serves as the default time format. The conversion between the enumeration position and the actual value may have a certain impact on the overall performance, and the enumeration value is stored in .frm (data table structure definition file), so after the ENUM column is created, if If you want to update the content of EMUM, it is equivalent to updating the table structure.

The following is a simple example of creating an ENUM column:

mysql> CREATE TABLEenum_test(
->  e ENUM('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTOenum_test(e) VALUES('fish'), ('dog'), ('apple');

##·    BIT
If you need to design a field that represents a Boolean value How would you design it to take up the least amount of space? Use INT or CHAR(1)? Compared to INT and CHAR(1), BIT(1) may be a better choice because it only takes up one BIT. It can express the values ​​of multiple BITs in the form of BIT(N), which supports up to BIT(64).

In versions prior to MySQL 5.0, BIT was considered equivalent to TINYINT, but in the new version it is treated as two completely different types.

When you retrieve a BIT field from the database and display it on the console, the value will be displayed as ASCII encoding. When the value of the field appears in the context of a number operation, it will be treated as The decimal value of BIT. The following example can clearly illustrate these two situations.

mysql>CREATE TABLE bittest(a bit(8));
mysql> INSERT INTObittest VALUES(b'00111001');
mysql> SELECT a, a+ 0 FROM bittest;
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+

The above example may confuse you, and it is very likely that you will no longer want to use this mechanism. To store a single bit, as an alternative, you can set the relevant field to CHAR (0), NULL is used to represent False, "" (Empty String) represents True

The above is the evolution theory of high-performance MySql (2) Contents under :Data type optimization_, for more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!