Read High performance MySQL summary

Type-related

Int (1) and int (20) have the same meaning for storage and computation, and he does not limit the legal scope of a value, but some interactive tools are used to display the number of characters
The default is signed, can be specified as unsigned, increase the data storage range, such as 0-255, can be declared unsigned
Integers are less expensive than word Fu Cao because character sets and collation rules make characters more complex, and if they are IP, they should be stored with an integer type.
Try to avoid null: If the query contains potentially null columns, it is more difficult to optimize for MySQL. It makes indexes, index statistics, and values more complex, nullable columns use more storage space, and when a null list is indexed, each index record requires an extra byte, and as far as possible not to index the null column "InnoDB exception, which uses a separate bit to store null values, For many values of NULL, a few non-null have good space efficiency "

varchar (1000) and char 1000 mean bytes, not characters
Char is suitable for storing a fixed-length value, which occupies storage space
varchar is suitable for storing variable long values, because the length of the value is variable, so the storage space is uncertain, when a memory page cannot hold the space occupied by the varchar data, InnoDB will split into two pages
varchar fit: Fewer updates to columns, use of complex character sets, when each character is stored with a different number of bytes

BLOB, TEXT:
When the value is too large, InnoDB allocates additional storage areas, each of which requires 1 to 4 bytes in the row to store a pointer
Blob-stored binary data with no collation and character set

Timestamp can only be saved from 1970 to 2038, the value displayed depends on the current time zone
DateTime from 1001 to 9999, similar to string, and therefore time zone Independent

Character Set correlation

Character:
Unicdoe A character is uniformly identified by 2 bytes, whether it is a Chinese character or an English letter or a symbol, so space can be wasted.
Utf-8 is a variable-length encoding that uses 1-4 bytes, when the character is in the ASCII range, is identified by one byte, and a Chinese character occupies 3 bytes
Utf-8 is a broad-sense implementation of the Unicode character set, he has tried to save space, but GBK this character set is still in the big line, because GBK is tailored for the Chinese, he has less space, just support Chinese, other text such as Korean, will be garbled, so specific scenarios or there are advantages

Optimize operations

The cost of converting a coded column from a row buffer to a line data structure is expensive, so what field to use and what field to take

Rough rule of thumb: the table associated with a single query is within 12 tables

The ALTER TABLE of a large table can be slow, and MySQL performs most of the table-altering operations by creating a table with the new structure, inserting all the data from the old table, deleting the old table, and, if the server is running out of memory, a large number of hours

Clustered index

Clustered index refers to the data row is stored in the index leaf page, a table can have only one clustered index
If an index contains all the required values, it is called an overlay index, and for InnoDB, you can avoid two queries on the primary key index, which is highly efficient

MySQL partition table

The partition table does not have a global index, and the index simply adds the exact same index on each underlying table, and the operation is black-out for the program, with some risk

Read High performance MySQL summary