I. Design:
Table fields avoid null values. Null values are difficult to optimize and take up extra index space. The default value 0 is recommended.
Use INT instead of BIGINT, or UNSIGNED if non-negative (which doubles the size of the value). TINYINT, SMALLINT, MEDIUM_INT are better.
Use enumerations or integers instead of string types
Use TINYINT instead of ENUM: ENUM adds new values to perform DDL operations
Use TIMESTAMP rather than DATETIME: they have different ranges of bytes and representations
Do not have too many fields in a single table, recommend less than 20
Use an integer to store IP addresses
Use varchar(20) to store phone numbers, not integers: varchar can be vague queries like ‘138%’
Use the smallest data type that can store data, integer < date,time < char,varchar < blob
Use text as little as possible
Second, the index
Select * from where (group by, order by, on) where (group by, order by, on); select * from where (group by, order by, on) where (group by, order by, on); select * from where (group by, order by, on); Because the storage unit of the database is pages, the more data can be stored on a page, the better the columns with high dispersion (as many different values) are placed in front of the joint index. Check the dispersion by counting different column values. The larger count is, the higher the dispersion is: Avoid NULL values in the WHERE clause. Otherwise, it will cause the engine to abandon the use of the index and scan the whole table. For example, a field with two or three values such as “gender” is not suitable for index building. SQL > create (a,b,c); SQL > create (a,b,c); SQL > create (a,b,c); (a), (a,b), (a,b,c) **
Third, the preparation of SQL needs to pay attention to optimization
Avoid select *, list the fields you want to find use joins instead of subqueries Splitting large DELETE or INSERT statements can find slow SQL by enabling slow query logs without column operations: SELECT id from table WHERE age + 1 = 10 WHERE age + 1 = 10 WHERE age + 1 = 10 SELECT id from table WHERE age + 1 = 10 Large statement disassembly small statement, reduce lock time; A large SQL block can block the entire library. The efficiency of OR is at the level of N, the efficiency of IN is at the level of log(n), and the number of IN is recommended to be controlled within 200 without functions and triggers. IN application program implementation, avoid % XXX type query and use less JOIN: If JOIN query is to be carried out, the fields to be joined must be of the same type, and indexes must be established to compare with the same type. For example, use ‘123’ and ‘123’ ratios. Avoid using 123 and 123 ratios in WHERE clauses! For consecutive values, use BETWEEN instead of IN: SELECT id FROM t WHERE num BETWEEN 1 AND 5 SELECT id FROM t WHERE num BETWEEN 1 AND 5