Single table optimization

  • Best left prefix: maintains the same order as the composite index definition.
  • Will containinRange query intowhereFinally, it prevents compound indexes from invalidating if they are used.

Multiple tables

  • Small tables drive large tables
  • Column small tablesandOn the left

Avoid the principle of indexing timeliness

  1. Compound indexes. Do not use them across columns or without using them. Using > to the left of a compound index also causes compound index aging.
  2. For composite indexes, use full index matching whenever possible
  3. Do not perform any operations (calculations, functions, casts) on the index, or it will be invalidated
  4. inMysql determines whether an index is fast to run or not to run.
  5. Cannot be used not equal to,is null.is not null.or
  6. likeDon’t want to%Start, otherwise index invalid if must be used%Start your search using overridden indexes whenever possibleusing index), that is, only index fields are queried.

SQL optimization is a probabilistic level of optimization, the SQL Optimizer will interfere with our SQL, as to whether our optimization is actually used, need to use the Explain query.

existandinThe use of

  • Use In if the main query data set is large, otherwise exist

order byTo optimize the

  • using filesortThere are two algorithms: double-way sort (default before Mysql4.1) and single-way sort (default after Mysql4.1)
  • Double sort: scan the disk twice (1: read the sort fields from disk, sort the sort fields in buffer, and read the other fields from disk)
  • Single-way sort: read only once (all fields) inbufferTo sort. If there is a large amount of data, fragmentation sorting will be performed, resulting in multiple I/OS.
  • Single-path sort will use more than double-path sortbuffer, can be setmax_length_for_sort_dataAdjust thebufferCapacity.
  • If the total size of the columns to be sorted exceedsmax_length_for_sort_data, Mysql will automatically change the single-path sort to dual-path sort.
  • It is better to ensure that the sort fields are sorted consistently (either in ascending or descending order)