Considerations for optimizing MYSQL table Creation

1. char and varcharchar: Fixed Length, suitable for storing very short (such as house number 101,201), fixed length (such as using uuid as the primary key), column fields with very frequent changes; char (M) each of the Data columns occupies M bytes. If the length of a column is smaller than M, MySQL uses null characters to complement it. (In retrieval

1. char and varchar char: They have fixed length and are suitable for storing fields with very short length (such as house number 101,201), fixed length (such as using uuid as the primary key), and columns with very frequent changes; each data column of the char (M) type occupies M bytes. If the length of a data column is smaller than M, MySQL uses null characters on the right side of the data column. (In retrieval

1. char and varchar

Char: Fixed Length, suitable for storing very short (for example, house number 101,201), fixed length (for example, using uuid as the primary key), column fields that frequently change; char (M) in a data column of the type, each value occupies M bytes. If the length of a column is smaller than M, MySQL uses spaces on the right side of the column. (The space characters filled in the search operation will be removed)

Varchar: variable length, with a length of more than 1 characters (used for storage)
Conclusion: due to the fixed length of char, the processing speed is much faster than that of varchar, but it is relatively less expensive, however, if the speed requirement is met, the char type can be used. Otherwise, the varchar type can be used for instances.

2. Storage Engine

	MyISAM	InnoDB
Approximate comparison	It is not transaction-safe and does not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice.	The transaction is secure and supports foreign keys. If you execute a large number of INSERT or UPDATE operations on your data, you should use the InnoDB table for performance considerations.
Data occupation	The indexes and data of MyISAM are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded. Tables can be compressed, and they support full-text search.	Innodb is closely bound to indexes and data. Without compression, Innodb is much larger than MyISAM.
Read/write Performance	Strong read performance	Strong Write Performance
Lock	The whole table lock. For the select count (*) from table operation, MyISAM has the number of rows in the table separately, which can be directly read without reading the table.	Row-Level Lock. If the number of rows in a table is not saved, select count (*) from table is a full table scan. However, when the count (*) statement contains the where condition, it is no different from MyISAM.
Char & varchar	We recommend that you use a fixed-length data column instead of a variable-length data column. The disadvantage is that the disk space is occupied.	We recommend that you use the varchar type. For innodb data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use a header pointer to the value of the data column). Therefore, in essence, using a fixed-length char column is not necessarily better than using a variable-length varchar. Therefore, the main performance factor is the total amount of storage used by data rows. Because char occupies more space on average than varchar, therefore, it is better to use varchar to minimize the total amount of data rows to be processed and disk I/O.

3. column type conversion rules

Rules Used in MySQL to determine whether data column type conversion is required

1. In a data table, if the length of each data column is fixed, the length of each data row will also be fixed.

2. As long as there is a variable length of a data column in the data table, the length of each data row is variable.

3. If the length of data rows in a data table is variable, in order to save storage space, mySQL converts a fixed-length data column in the data table to a variable-length data column. char data columns with a length less than 4 characters are not converted to varchar

4. In practice, the length of a varchar is limited by the length defined by a row. MySQL requires that the definition length of a row cannot exceed 65535.

4. Primary Key and foreign key

Primary Key: Try to use a primary key with a short length. It is better to use a foreign key as the primary key. You do not need to create an independent index on the primary key because the system creates a clustered index for the primary key.

Foreign key: The foreign key affects the insertion and update performance. For batch reliable data insertion, we recommend that you disable the foreign key check first.

For tables with a large amount of data, we recommend that you remove the foreign key and use the application to perform data integrity check.

Use the primary key of the corresponding primary table as the foreign key whenever possible to avoid using the unique key of the primary table with a large length as the foreign key.

The foreign key is indexed by default.

5. Fields

1. Minimum Field Length

2. The fixed length type is preferred.

3. Define "not null" as much as possible"

4. Avoid using "ZEROFILL" in Numeric Fields"

5. If the data to be stored is a string with known and limited values, use enum or set first.

6. Index

Indexes should not be created for columns that are rarely used or referenced in queries. Free Space

Indexes should not be added to columns with only few data values. Too few Mappings

Indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small.

When the modification performance is much higher than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent.

1. The smaller the length of the indexed field, the higher the efficiency of the index.

2. In the indexed field, the less repeated the value, the higher the efficiency of the index.

3. If the "group" clause is used in the query statement, multi-field indexes are created based on the order in which the fields appear.

4. If "distinct" is used in the query statement, create a multi-field index based on the order in which the fields appear.

5. When the "and" condition for multiple different fields in the same table appears in the "where" clause, create a multi-field index according to the order in which the fields appear.

6. When the "or" condition for multiple different fields in the same table appears in the "where" clause, a single field index is created for fields with the least repeated values.

7. Create an index for the "connection field" When querying "internal/external connections"

8. The "unique" index of the "primary key" is meaningless and should not be used. For a Primary Key column, MySQL has automatically created a Unique Index for it, and there is no need to repeat it to create an Index.

9. Use the "not null" attribute as much as possible for the indexed Field

10. Minimize indexes for write-intensive tables, especially "Multi-field indexes" and "unique" Indexes

11. MySQL only uses the prefix, such as key (a, B )... Where B = 5 will not use the index.

12. Control the length of a single index. Use key (name (8) to index the first few characters of the data

13. Similar key values are better than random values. Auto_increment is better than uuid.

7. Optimization of query statements

1. Use "explain" to query index usage, so as to find out the best query statement Writing Method and index setting solution.

2. Use "select *" with caution. Only required fields are selected during query.

3. When an index is used for a query, the smaller the number of indexes to be traversed, the smaller the index field length, and the higher the query efficiency (you can use "explain" to query the index usage)

4. Avoid using the mysql function to process the query results and hand over the processing to the client

5. When "limit" is used, make sure that the part produced by "limit" is located at the front of the entire result set. The query speed is faster and the system resource overhead is lower.

6. When the "and" condition of multiple fields is used in the "where" clause, the order of each field appears must be consistent with the order in the Multi-field index.

7. When "like" is used in the "where" clause, the index is used only when the wildcard does not appear on the leftmost end of the condition.

8. In mysql 4.1 or later versions, avoid using subqueries and try to use "internal/external connections" to implement this function.

9. Reduce the use of functions. If possible, use a simple expression instead.

10. Avoid performing the "or" condition query on different fields in the "where" clause, and split it into multiple single fields. The query statement is more efficient.

11. Use the matching type during query. For example, select * from awhere id = 5. If the id here is of the character type and has an index, the index cannot be used for this query, and the full table scan will be performed, resulting in a slow speed. Which of the following statements is true... Where id = "5", with quotation marks indicating the type is a character.