Content Outline:

SQL execution process 2. What is a good SQL 3. Show profile To view execution process 4. Common SQL optimization problems

1. SQL execution process

2. What is good SQL

As simple as possible, modular, easy to read, easy to maintain save resources memory CPU scan data blocks to avoid deadlock sorting

3. Show profile to view the execution process

Show variables like ‘%profiling%’ set profiling=1;

select * from xxx ;

show profiles; Displays the most recent queries

Show profile CPU,block IO for query

4.EXPLAIN performance analysis

When writing SQL statements, we should be clear about the principles under which the optimizer uses indexes, which helps to write high-performance SQL statements. Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL queries to see how MySQL processes your SQL statements. Analyze performance bottlenecks in your query or table structure by using Explain + SQL statements such as Explain SELECT * FROM employees; From best to worst:

system>const>eq_ref>ref>range>index>ALL
Copy the code

In general, make sure the query is at least range and preferably ref

Common SQL optimization problems

MySQL’s parser processes the table names in the FROM clause FROM right to left, so the last table written in the FROM clause will be processed first. In the case of multiple tables in the FROM clause, you must select the table with the fewest entries as the base table. When MySQL processes multiple tables, it joins them by sort and merge. First, scan the first table (the last table in the FROM clause) and sort the records, then scan the second table (the last table in the FROM clause), and finally merge all the records retrieved FROM the second table with the appropriate records FROM the first table. For example, table TAB1 has 16,384 records and table TAB2 has 1 record. Select count() from TAB2 (best method) select count() from tab1 (bad method) select count() from TAB2 (26.09 seconds

Also because MySQL parses WHERE clauses from right to left, joins between tables must be written before other WHERE conditions, and conditions that filter out the maximum number of records must be written at the end of WHERE clauses. For example: (inefficient, execution time 156.3 seconds) SELECT… FROM EMP WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT() FROM EMP WHERE MGR= e.emno); (Efficient, execution time 10.6 seconds) SELECT… FROM EMP WHERE SAL > SAL AND JOB = ‘MANAGER’;

It is best not to use functions or expressions in WHERE clauses; indexes do not take effect if type conversions occur

Select * from employee where last_name like ‘c%’; select * from employee where last_name like ‘c%’; The optimizer cannot take advantage of indexes when wildcards appear elsewhere in the string.

Reference data: www.cnblogs.com/dwlovelife/…