Common optimization terms

  1. Queries should be optimized to avoid full table scans, and indexes should be considered on where and order by columns first

  2. You should avoid null values for fields in the WHERE clause. Null is the default when creating a table, but most of the time you should use NOT NULL or use a special value such as 0 or -1 as an implicit value

  3. Use in where clauses should be avoided! = or <> operators, MySQL only uses indexes for the following operators: <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE

  4. Try to avoid using OR to join conditions in the WHERE clause, which will cause the engine to abandon the index and perform a full table scan instead. Instead, use UNION to merge queries: select id from t where num=10 union all select id from t where num=20

  5. Use not in with caution. Otherwise, a full table scan will occur

  6. Select id from t where name like ‘% ABC %’ select ID from t where name like ‘% ABC %’ Select id from t where name like ‘ABC %

  7. Avoid expression manipulation of fields in the WHERE clause. Avoid functional manipulation of fields in the WHERE clause

  8. An index can improve the efficiency of a select, but it can also reduce the efficiency of an INSERT or update. Because an insert or update can rebuild an index, how to build an index should be considered carefully

  9. It is best not to use “Return all: select from t”, replace “*” with a list of specific fields, and do not return any fields that are not needed.

  10. Use table aliases: When joining multiple tables in an SQL statement, use the table Alias and prefix the Alias to each Column. This reduces parsing time and reduces syntax errors caused by Column ambiguities

  11. Terms with OR can be split into multiple queries, and multiple queries can be joined by UNION. Their speed depends only on whether an index is used or not; if a query requires a federated index, it is more efficient to use UNION all. Multiple OR sentences do not use the index, rewrite into the form of UNION and then try to match with the index. A key question is whether to use indexes

  12. Improve the efficiency of GROUP BY statements BY filtering out unwanted records before GROUP BY

  13. SQL statements are capitalized because Oracle always parses SQL statements to convert lowercase letters to uppercase letters before executing

  14. The use of alias, alias is a large database application skills, is the table name, column name in the query with a letter alias, query speed is 1.5 times faster than the establishment of a link table

  15. Each table in the database should have an ID as its primary key, preferably an INT, and an AUTO_INCREMENT flag on it

SQL design follows common specifications

For example, the corresponding specification of Alibaba

  1. Single table index control 5 or less
  2. Duplicate indexes are not allowed
  3. Prevents index invalidation due to implicit conversions of data types
  4. Use overwrite indexes to reduce back to table operations
  5. Disallow joins of more than three tables
  6. Add index to vachar field, preferably specify length
  7. Index fields must be set to default values, not null
  8. The amount of data in a single table should be controlled at about 10 million
  9. The number of columns should be less than 30 and do not rely on wide tables

Mysql cryptic problem

  1. It is not recommended to use mysql partitioned tables: mysql partitioned tables maintain indexes on their own partitions, which will cause globally unique indexes to fail
  2. Like a%, >=,<, etc., will interrupt the index matching, resulting in the subsequent column of the joint index does not go to the index, in the design and optimization should be considered clearly, which column is the first