Mind Mapping
Click on the picture to view the larger image.
Introduced
Situation: If your table structure is poorly designed or your index is poorly designed, then you should optimize your table structure and give the appropriate index so that your query performance can be increased by several orders of magnitude. The larger the data, the more the value of the index can be reflected.
We want to improve performance, we need to consider the factors:
1. Design structure
2. Design Index
3. Evaluate query performance
Today we are going to talk about the design of the table column, not on the index design. I'll talk about index design in the next chapter.
Choosing the right data type is critical to improving performance.
Here are a few
principles that will help you choose which type.
1, smaller is usually better.
Use the smallest data type. -Less disk space, memory, and CPU caching. And also requires less CPU cycles.
The integer cost is less than the character. --because character sets and collations make characters more complex.
1>mysql built-in types (such as timestamp,date) are better than using string saving.
2> uses integers to save IP addresses.
3, try to avoid null--if you plan to index the column, try to avoid setting the column to null
Define the field as not NULL as much as possible. --You can place a default value, such as ', 0, special string.
(1) MySQL is difficult to optimize the null column. A null column makes indexes, index statistics, and values more complex.
(2) A null column requires more storage space and special handling within MySQL.
(3) NULL columns are indexed, each record requires an extra byte, and the fixed size index in the MyISAM is changed to a variable size index.
To determine the data type of a column, we should follow the following two steps.
The first step, roughly determines the data type. --Judgment is a number, a string or a time. This is usually intuitive.
The second step, determine the specific type.
Many data types can hold data of the same type, but we want to find out the difference between their storage scope, precision and physical space (disk or memory space). such as: datetime and timestamp can save the same type of data: Date and time. --timestamp uses half the space of datetime, saves time zones, and has special automatic update capabilities.
1. Storage Type-data range is -2^ (n-1) to 2^ (n-1)-1, where n is the number of bits of storage space required.
Type name occupies a bit data range TINYINT 8-2^7 ~ 2^7-1 SMALLINT 16-2^15~2^15-1 mediumint 24-2^23 ~2^23-1 INT 32-2^31~2^23-1 BIGINT 64-2^63~2^63-1
2, the unsigned attribute indicates that negative numbers are not allowed, and roughly increases the positive limit by one times. If the tinyint unsigned is saved in a range of 0 to 255 instead of-127 to 128
3. mysql defines the width of the integer type, such as int (1) and Int (22) for storage and computation. Only specify the number of characters that the MySQL interactive tool, such as the command-line client, uses to display.
The real number has fractions (fractional parts).
Storage type: float and double,decimal.
Occupy Size: FLOAT 4 bytes, DOUBLE 8 bytes. Decimal is affected by the MySQL version, with an earlier version of 254 digits and 5.0 or more 65 digits.
Difference: 1, float and double support standard floating-point operations for approximate calculations.
2, decimal for decimal operations, the CPU does not support direct calculation of it. Floating-point operations are faster because the calculations are performed directly on the CPU.
3. Decimal is only a storage format, which is converted to the double type when evaluated.
4. Decimal (18,9) uses 9 bytes, 4 bytes before the decimal point, 1 bytes in decimal, and 4 bytes after the decimal point.
5. Decimal is only used when the decimal is accurately calculated, such as the preservation of financial data.
(1) Save variable length strings.
Understanding: Takes up less storage space than a fixed length because it takes up only the space it needs. Exception: A MyISAM table created using row_format=fixed, which uses fixed-length space for each row, can cause waste.
(2) Storage length information. If the defined column is less than or equal to 255, then a 1-byte storage length value is used, assuming that the latin1 character set, such as varchar (10), will occupy 11 bytes of storage space. Conversely, varchar (1000) consumes 1002 bytes of storage space.
(3) Save space, it is helpful to the performance.
(4) More than 5.0 versions, regardless of whether the value or save, MySQL will retain the end of the string space.
Allocate only the space you really need
It is the same to use varchar (5) and varchar (200) to save ' hello ' occupancy space. --This should refer to the space on the disk.
So what's the advantage of using shorter columns? --A huge advantage
Larger columns use more memory, because MySQL typically allocates a fixed sized chunk of memory (such as varchar (200) with a 200-character memory space) to hold the value ( then trim the value, then put it on the disk) or value. -This is especially bad for sorting or using a memory-based temporary table.
(2) When saving the value, remove the trailing space.
(3) Char is often useful when used in very short strings or strings of similar length. If you store a MD5 hash of a user's password, it is always the same length.
Char superior to varchar place?
1>> for frequently changing values, char is superior to varchar because fixed-length rows do not produce fragmentation easily. -When the longest length is much larger than the average length, and it is rarely updated, it is usually appropriate to use varchar.
2>> for very short columns, char is also more efficient than varchar. For a single-byte character set (such as Latin1), char (1) occupies only 1 bytes, while varchar (2) consumes 2 bytes (one byte is used to store the length of the information).
Used to hold large amounts of data.
(1) InnoDB use an "external" storage area for saving when they are larger. -so you need enough external storage space to hold the actual value.
(2) The sorting method differs from the other character types and is not sorted according to the complete length, but is sorted according to the first few bytes specified by Max_sort_length.
4. Use an enum instead of a string type
(1) The enum column can store 65 535 different strings.
(2) Save in a compact manner. Compress them into 1 to 2 bytes, depending on the number of values in the list.
(3) MySQL internally saves each value as an integer to indicate the position of the value in the list.
(4) A "lookup table" is reserved to represent the mapping of integers and strings in a table's. frm file.
(5) The enum character column is fixed, and the addition and deletion of the string must use alter TABLE.
(6) Use case: Use an enum in the permission table to hold the Y-value and N-values.
When you use an order by with an enum column, you sort by number, not by string.
Date and Time type
DATETIME: Save a large range of values. Package format: YYYYMMDDHHMMSS. --independent of time zone, using 8-byte storage space.
TIMESTAMP: Saves the number of seconds since midnight January 1, 1970 (Greenwich Mean Time). --use 4 bytes of storage space.
Usually use timestamp, which saves more space than DateTime. Sometimes people save Unix timestamps as integer values, but this usually doesn't do any good. This format is not easy to handle and we do not recommend it.
1. When choosing data types for columns, we should consider not only the storage type size, but also how MySQL calculates and compares them. For example: MySQL internally saves the enum and set types as integers, but converts them to strings when compared.
2, we want to use the same type in the related table, the exact match between the types, including properties such as unsigned.
3. Mixing different data types results in performance problems, and implicit type conversions can lead to imperceptible errors even without performance problems.
4, choose the smallest data type to consider the future growth space to be left out. For example, China's provinces, we know that there will not be thousands of them, so do not use int. Tinyint is enough, it is 3 bytes smaller than int.
5. An integer is usually the best data type because it is fast and can use Auto_increment.
6, as much as possible to avoid strings as columns of data types, because they occupy a lot of space and usually required integer type is slow. MyISAM uses a compressed index for strings by default, which makes lookups slower.
Summarize
If there is a mistake, I hope you can teach one or two, appreciate!
References: High performance MySQL
