High Performance MySQL Notes Chapter 4th schema and data type optimization

4.1 Selecting the Optimized data type   general principles smaller is usually betterThe premise is to ensure that you do not underestimate the range of values that you need to store: because it consumes less disk, memory, CPU cache, and requires less CPU cycles to process. it's simple.Operations with simple data types require less CPU cycles. try to avoid nullNullable columns make indexing, index statistics, and value comparisons more complicated. A nullable column uses more storage space. integer TypeTINYINT 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. The UNSIGNED property identifies that negative values are not allowed, and the stored range becomes 0 to 2 (N)-1. MySQL can specify a width for an integer type, such as int (11), and he does not limit the amount of legal scope, but specifies that some of the interactive tools of MySQL are used to display the number of characters. For storage and Computation, int (1) and int (20) are the same. Real TypeFLOAT DOUBLE DECIMAL. String Type VARCHAR CHAR

• The VARCHAR type is used to store variable-length strings.

• The CHAR type is used to store fixed-length strings. Ideal for storing MD5 values.

• VARCHAR requires 1 or 2 extra bytes to record the length of a string: if the maximum length of a column is less than or equal to 255 bytes, then only 1 bytes are used, otherwise 2 bytes are used. VarChar saves storage space, so it is also useful for performance. However, because the rows are longer, you might make the rows longer than they were when you update, which leads to additional work that is handled differently by different storage engines. For example, MyISAM will split the rows into different fragments for storage, and InnoDB will need to split the page so that the rows can be placed in the page.

BLOB TEXT  

• BLOBs are stored in binary mode, with no collation or character set.

• Text is stored in character mode, with character set and collation.

• Tinytext smalltext TEXT Mediumtext longtext, of which text=smailltext.

• Tinyblob Smallblob BLOB Meduimblob Longblob, where Blob=smallblob.

using enumerations (enums) instead of string types  The enumeration column stores some non-repeating strings as a predefined collection. MySQL saves the position of each value in the collection as an integer internally. date and Time typeDATETIME TIMESTAMP time DATE year special types of dataIPV4 address, MySQL provides Inet_aton () and Inet_ntoa () functions to convert each other. 4.2 A trap in MySQL schema design

• Too many columns

• Too many associations: preferably less than 12

• Error using Enumeration

• Not NULL for this invention

4.3 paradigm and inverse paradigm   three main paradigms  

• The first paradigm (1NF) refers to the fact that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties.

• The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. For implementation, it is common to add a column to the table to store unique identities (primary keys) for each instance.

• The third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables.

the advantages and disadvantages of paradigm   Advantages  

• The normalized update operation is usually faster than the inverse normalization;

• When the data is well normalized, as long as there is little or no duplication of data, so only need to modify less data;

• The normalized table is usually smaller and better placed in memory, so it is faster to perform the operation;

• Little extra data means less need for distinct or group by statements when retrieving list data.

DisadvantagesThe disadvantages of the schema of a normalized design usually need to be correlated. This is expensive and may invalidate some index policies. advantages and disadvantages of inverse paradigm   Advantages

• Avoid association;

Disadvantages

• Redundant fields;

4.6 Summary

• Avoid over-design as much as possible, such as a schema design that is extremely responsible for querying, or the design of tables with many columns;

• Use a small and simple fit data type, unless the real data model has the exact needs of the town, you should avoid using null values as much as possible;

• Try to store similar or related values using the same data type, especially for columns that you want to use in the associated condition;

• Note variable-length strings, which may cause pessimistic allocation of memory by maximum length when sorted in temporary tables;

• Use shaping to define the identity column as much as possible;

• Avoid the use of features that MySQL has abandoned, such as specifying the precision of floating-point numbers, and the display width of the latter integers;

• Use the enum and set carefully. Although they are convenient to use, do not misuse, or sometimes become a trap. It is best to avoid using bit;

High Performance MySQL Notes Chapter 4th schema and data type optimization