# Sql Tuning Principles (A)

+ SQL tuning is a system project that may involve:
  + databases
  + cache
  + apps
  + products, etc
------------------------------------------------------------
1. SQL tuning is a system project that may design databases, cache, applications, products, etc.
   
2. Use appropriate indexes for retrieval and sort, avoiding full table scan, and sort of large amounts of data
  - Indexes need to have a high degree of selectivity
  - Consider the frequency of index usage and index cost
  - column that are updated frequently are not suitable for indexing

3. Avoiding Unusable Indexes
  - Avoid implicit conversions (type mismatch)
  - Avoid using function on indexed column
  - Avoid starting fuzzy query on search column: '%abc'
  - Avoid using! in the where clause = or <> operator
  - Too much data after index filtering may cause the optimizer to choose to full table scan

4. The leftmost index principle is required to use a composite index
  - Composite indexes must use fields in the order of the fields in the index. If the previous index column is not used, the subsequent index column cannot be used
  - In a composite index, the previous column uses a range query (between, >, <, etc.), and the following column cannot be used for index filtering and sort

5. Try to avoid complex query (multi-table joins, subquery, etc.)
  - Always drive big result sets with small result sets
  - Multi-table join association column need to have indexes
  - Try to use inner join for multi-table joins 
  - You can use STRAIGT_JOIN to specify the join order

6. Use truncate instead of delete if you can

7. TIDB personalized optimization
  - Optimization of primary key and unique key lookup
  - Be aware of the impact of historical versions (mvcc) on query