# SQL Tuning

+ SQL Business logic optimization
  + TiDB Dashboard slow query
  + TiDB Dashboard SQL Statements
  + expensive query in TiDB server log
+ Execution plan optimization
  + The optimizer did not choose the optimal execution plan
  + Necessary indexes were not created
  + The execution plan is accurate, but the memory footprint is high

-------------------------------------------------------------

You need to confirm with business development stakeholders whether it is reasonable to return large amounts of data and whether the scope of data can be narrowed down to meet business requirements.
- SQL execution plan optimization
We have already learned about TiDB SQL optimization through the previous SQL optimization course, so we will not go into it here, but only briefly explain it. If you have any questions, you can study the SQL optimization course again.
  - Step 1: Through the TiDB Dashboard slow requery and SQL statement analysis function module, you can view the execution plan of SQL that occupies a large amount of memory in the cluster and the number of execution plans
  - Step 2: Through step 1, it is basically possible to judge the accuracy of the execution plan
  
    - The optimizer chose an inefficient execution plan
        - The optimizer can be intervened in the following ways to select a low-cost, but highly accessible execution plan:
          - Hint, SQL needs to be rewritten, which is intrusive to front-end services
          - SPM, which interferes with the selection of execution plans without update SQL statement, automatic capture of bindings, automatic evolution bindings, etc.
        - Checking the health of the statistics
          - If the pesudo keyword appears in the SQL execution plan, it means that no statistics are available and the table or index needs to be analyzed
          - If the statistics health is 100%, it is possible that the CBO optimizer cost estimate is inaccurate. You can use SQL Binding to fix the SQL execution plan without overwriting the SQL
         
      - Necessary indexes were not created
        - Create necessary indexes based on the behavioral characteristics of business access target tables to improve SQL access efficiency while reducing unnecessary data return and memory usage
  
     - Accurate execution plan, but high memory usage
        - If the statistics and optimizer selection are accurate, high memory usage may also occur. For example, the inner table of a hash join uses a lot of memory, so consider Merge Join.
        - Merge Join is a special join algorithm. The Merge Join algorithm applies when the field to be joined by two related tables need to be read in a sorted order. Merge joins take up much less memory than hash joins, but merge joins cannot be executed concurrently and take longer to execute. It is recommended to try it when the amount of data is too large or the system is low on memory.