1, optimize the SQL statement overview
The core logic of a database application is executed through SQL statements, whether published directly through the interpreter or submitted behind the scenes through an API. Tuning guidelines help speed up various MySQL applications. These guidelines cover SQL operations that read and write data, the overhead behind SQL operations, and operations used in specific scenarios, such as database monitoring.
2, select statement optimization
2.1 Perform all lookup operations in the database in the form of a SELECT statement query. Whether you’re implementing sub-second response times for dynamic web pages or shortening the time it takes to generate large numbers of overnight reports, tuning these statements is a top priority.
2.2 In addition to the SELECT statement, query tuning techniques are also applicable to CREATE TABLE… AS SELECT, INSERT INTO… Structures such as SELECT and WHERE conditions in DELETE statements. These statements have additional performance considerations because they combine write operations with read-oriented query operations.
2.3 The number of optimized queries is mainly:
★ To make SELECT… To speed up queries, the first thing to check is whether you can add indexes. Set indexes on columns used in the WHERE clause to speed evaluation, filtering, and final retrieval results. To avoid wasting disk space, build a set of indexes to speed up the many related queries used in your application. Indexes are especially important when using queries that can reference different tables using features such as joins (JION) and foreign keys. Here you can use the EXPLAIN statement to determine whether a SELECT is being used. Indexing and exlain usage will be expanded later.
★ Isolate and tune any part of the query, for example, which takes too much time on function calls. Depending on the query structure, a function may be called once per row in the result set, or even once per row in the table, greatly magnifying any inefficiencies.
★ Minimize the number of full table scans in the query, especially for large tables.
★ The ANALYZE TABLE periodically uses statements to keep TABLE statistics up to date so that the optimizer has the information it needs to build an efficient execution plan.
Learn tuning techniques, indexing techniques, and configuration parameters specific to each table storage engine. InnoDB and MyISAM have a set of criteria for achieving and maintaining query performance. It will unfold later!
InnoDB tables with single query transactions can be optimized using InnoDB read-only transaction optimization technology
★ Avoid transforming queries in ways that are difficult to understand, especially if the optimizer automatically performs some of the same transformations.
★ If the performance problem cannot be easily solved by one of the basic criteria, investigate the internal details of a particular query by reading the EXPLAIN plan and adjusting indexes, WHERE clauses, join clauses, and so on. (Reading the EXPLAIN plan may be the first step in every query you make.)
★ Adjust the size and properties of the memory region used by MySQL for caching. Through efficient use of InnoDB buffer pools, MyISAM key cache and MySQL query cache, repeated queries run faster because the second and subsequent retrieval of results from memory.
★ Even for queries that run quickly using a cache, you can still optimize further so that they require less cache memory, making your application more scalable. Scalability means that your application can handle more users, larger requests, and so on at the same time without experiencing a significant performance degradation.
★ Handle the locking problem, where the query speed may be affected by other sessions accessing the table at the same time.