Shulou(Shulou.com)06/01 Report--

Editor to share with you mysql select and where clause optimization methods, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Database optimization:

1. Can be optimized at the level of a single SQL statement, an entire application, a single database server, or multiple networked database servers

two。 Database performance depends on several factors at the database level, such as tables, queries, and configuration settings

3. Optimize at the database level and optimize at the hardware level to balance portability and performance

4. Appropriate structure, appropriate data type; large scale of applications that perform frequent updates (few columns); few tables of applications that analyze large amounts of data (multiple columns); selection of appropriate storage engine and index

5. Compress various workloads for InnoDB tables, as well as read-only MyISAM tables

6. Choose the appropriate locking strategy; the InnoDB storage engine can handle most locking problems

7. The main memory areas configured are the InnoDB buffer pool and the MyISAM key cache.

8. The technique of optimizing select statements is also applicable to other delete statements with where, such as setting indexes on the columns of the where clause; indexes are especially important for referencing multiple columns such as join and foreign keys

Select where clause optimization:

1. Adjust the structure of the query, such as function calls, only once for each row in the result set and once for each row in the table

two。 Reduce the number of full table scans in a query

3. Regularly use ANALYZE TABLE statements to keep table statistics up to date

4. Learn about tuning techniques, indexing techniques, and configuration parameters specific to each table's storage engine

5. Optimize the single query transaction of InnoDB table

6. Investigate the internal details of a particular query by reading the EXPLAIN plan and adjusting the index, WHERE clause, join clause, etc.

7. Adjust the size and properties of the memory area used by MySQL for caching. Through efficient use of InnoDB buffer pools, MyISAM key caching and MySQL query caching

8.where condition, remove unnecessary parentheses, constant folding, constant condition removal, reduce unnecessary logic

9. Constant expressions used by the indexed are evaluated only once

10.count (*) queries directly from table information; the same is true of not null expressions when there is only one table

11. If you do not use GROUP BY or aggregate functions (COUNT (), MIN (), etc.), HAVING will be merged with WHERE

twelve。 Constant table with only one row or empty table; the where clause acts on the primary key or unique index

13. If all the columns in the ORDER BY and GROUP BY clauses are from the same table, the table is preferred when joining

14. If the order by clause is different from the group by clause, or if it comes from a different table, a temporary table is created

15. If you use the SQL_SMALL_RESULT modifier, MySQL will use temporary tables in memory

16.MySQL can only read rows from the index without even consulting the data file.

17. Lines that do not match the HAVING clause will be skipped before each line is output

The following table is used as a constant scale:

SELECT * FROM t WHERE primary_key=1;SELECT * FROM T1 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 uses only the index tree:

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 an index to retrieve data in sort order, without the need for a separate sort

SELECT... FROM tbl_name ORDER BY key_part1,key_part2,...; SELECT... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC,...; these are all the contents of this article entitled "what are the ways to optimize select and where clauses in mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.