Summary of select and where clause optimization in mysql

This article brings you a summary of the optimization of select and where clauses in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Database optimization:

1. Optimization can be performed at the level of a single SQL statement, the entire application, a single database server, or multiple networked database servers
#2. Database performance depends on several factors at the database level, such as tables, queries and configuration settings
3. Optimize at the database level, optimize at the hardware level, balance portability and performance
4. Appropriate structure, appropriate data type; applications that perform frequent updates with a large number of tables (few columns); applications that analyze large amounts of data with a small number of tables (multiple columns); choose appropriate storage engines and indexes;
5. Compression is applicable Various workloads for InnoDB tables, as well as read-only MyISAM tables
6. Choose an appropriate locking strategy; the InnoDB storage engine can handle most locking issues
7. The main memory areas configured are the InnoDB buffer pool and MyISAM Key cache.
8. Optimize the select statement. This technique is also applicable to other delete statements with where. Set indexes on the columns of the where clause; indexes are especially important for referencing multiple columns such as joins and foreign keys.

select where clause optimization:

1. Adjust the structure of the query, such as function calls, only call them once for each row in the result set, and for each row in the table Called only once
2. Reduce the number of full table scans in queries
3. Regularly use the ANALYZE TABLE statement to keep table statistics up-to-date
4. Understand the tuning techniques of the storage engine specific to each table , indexing technology and configuration parameters
5. Optimize single-query transactions for InnoDB tables
6. Investigate the internal details of a specific query by reading the EXPLAIN plan and adjusting indexes, WHERE clauses, join clauses, etc.
7. Adjust the size and properties of the memory area used by MySQL for caching. By effectively using the InnoDB buffer pool, MyISAM key cache and MySQL query cache
8.where condition, remove unnecessary parentheses, constant folding, constant condition removal, reduce unnecessary logic
9.Used by the index Constant expressions are only calculated once
10.count(*) is queried directly from the table information; when there is only one table, the same is true for not null expressions
11.If you do not use GROUP BY or aggregate function (COUNT (), MIN (), etc.), HAVING will be merged with WHERE
12. Constant table, only one row or empty table; where clause acts on primary key or unique index
13. If ORDER BY and GROUP BY If all the columns in the clause come from the same table, the table will be preferred when connecting
14. If the order by clause and the group by clause are different, or come from different tables, a temporary table will be created
15. If you use the SQL_SMALL_RESULT modifier, MySQL will use an in-memory temporary table
16. MySQL can just read rows from the index without even consulting the data file
17. Before outputting each row, a jump will Pass rows that do not match the HAVING clause

The following table is used as a constant table:

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

The following query runs very fast:

SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;
SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;
SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

Assuming that the index column is numeric, the following query Only the index tree is used:

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;
  SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following query uses the index to retrieve data in sorted order, no separate sorting is required

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
   ORDER BY key_part1 DESC, key_part2 DESC, ... ;

The above is the detailed content of Summary of select and where clause optimization in mysql. For more information, please follow other related articles on the PHP Chinese website!