Mysql optimization experience

1. starting from the database structure 1. when defining field types, follow the following rules: 1. the minimum length of the selected field is 2. the fixed length type is preferred. define "not null" 4. avoid using "ZEROFILL" 5. if the data to be stored is a string with known and limited values, use enum or set 2 first. optimization of indexes is critical (queries are intensive if not specified) (http://www.002pc.com)

1. the smaller the length of the indexed field, the higher the efficiency of the index. the less repeated the value in the indexed field, the higher the efficiency of the index. if the "group" clause is used in the query statement, create a multi-field index 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. when the "and" condition for multiple different fields in the same table appears in the "where" clause, create a multi-field index based on 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 the fields with the least repeated values. 7. create an index for the "connection field" When querying "internal/external connections. it is meaningless for the "unique" index of "primary key". Do not use 9. use the "not null" attribute 10 as much as possible for the indexed field. for write-intensive tables, minimize indexes, especially "Multi-field indexes" and "unique" indexes 2. 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 3 are selected during query. when an index is used for query, the fewer index entries that are traversed, the smaller the index field length, 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. when using "limit", try to make the part of the "limit" part in the front of the entire result set, so that 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 in which each field appears must be consistent with that 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 in the leftmost end of the condition. in mysql 4.1 or later versions, avoid using subqueries and try to use "internal/external connections" to achieve this function 9. reduce the use of functions. If possible, use a simple expression instead of 10. avoid performing "or" condition queries on different fields in the "where" clause, and splitting the query statement into multiple single fields is more efficient.

Appendix: Mysql Field Length description table

Mysql Field Length description
TINYINT	1 byte	SMALLINT	2 bytes
MEDIUMINT	3 bytes	INTEGER:	4 bytes
BIGINT	8 bytes	DOUBLE	8 bytes
FLOAT (X)	X <= 24: 4 bytes X> 24: 8 bytes	DECIMAL (M, D)	M <D: D + 2 bytes M> = D: M bytes
DATE	3 bytes	DATETIME	8 bytes
TIMESTAMP	4 bytes	TIME	4 bytes
YEAR	1 byte
CHAR (M)	MB bytes	VARCHAR (M)	Value Length + 1 byte
TINYBLOB	Value Length + 1 byte	TINYTEXT	Value Length + 1 byte
BLOB	Value Length + 2 bytes	TEXT	Value Length + 2 bytes
MEDIUMBLOB	Value Length + 3 bytes	MEDIUMTEXT	Value Length + 3 bytes
LONGBLOB	Value Length + 4 bytes	LONGTEXT	Value Length + 4 bytes
ENUM	1 or 2 bytes Depends on the number of enumerated values	SET	1, 2, 3, 4, 8 Depends on the number of members