** Disclaimer: **** Personal study notes, understanding is limited. ** Welcome correction, not like spray
(Programmer site editor is so rubbish, no color)
SQL optimization is the interview around the past hurdle, recently remedial once, will note down.
Some programmers are not good at expression and their level is very high, but they can’t come out in the interview. To improve the logic of their expression, you can draw more mind maps, abstraction into images, when the expression of the mind map to install, let people sound very hierarchical logical sense.
——————————————————————————–
MySQL optimization can be divided into library table structure optimization, index optimization, query optimization ****, and query optimization can be divided into column reduction, row reduction and application layer association. So far only query optimization has been looked at.
First, understand how slow queries are generated.
The most basic reason for poor query performance is that too much data is accessed. Most low-performing queries can be optimized by reducing the amount of data accessed: 1. Verify that your application is retrieving more data than it needs. This usually means that too many rows are accessed, but sometimes it can also mean that too many columns are accessed. 2. Verify that the MySQL server layer is parsing a large number of data rows that are larger than needed.
— From High Performance MySQL3 (3rd edition all 2013, many optimization tips feel a bit dated, but the thought part can still be learned)
——————————————————————————————-
** 2, ** how to do
1. Reducing columns is simple. Query fields get only the required fields. Especially when querying a joint table, do not use the * sign
SELECT * FROM a JOIN b using(id) SELECT a.id FROM a JOIN b using(id)Copy the code
2. The easiest way to reduce rows is to paginate. In actual scenarios, only the first few rows of data are generally viewed for reports and news.
Here’s a question: Can the WHERE condition reduce the number of rows accessed?
In general, MySQL can apply WHERE conditions in one of the following ways, from good to bad:
① Use the WHERE condition in the index to filter the records that do not match. This is done in the storage engine layer. ② Use index cover scan to return records, filter unwanted records directly from the index and return hit results. This is done at the MySQL server layer without having to go back to the table to query the records. ③ Return data from the table, then filter the records that do not meet the criteria (Using Where appears in the Extra column). This is done at the MySQL server layer, where MySQL first reads the records from the table and then filters them. — From High Performance MySQL
The way the appeal WHERE condition works again illustrates the importance of indexes.
3. Decomposition associated query: query data in a single table and perform joint table operations in the application layer. A table corresponds to a query SQL, which corresponds to an entity class (this is consistent with my understanding of entity classes in the last note). Advantages:
① Let the cache efficiency is higher, because a table of data are from a SQL check, cache hit heel high.
② Performing a single query can reduce lock contention.
③ The disassembly of multiple table queries into single table queries is also a decoupling idea, which realizes the reuse of SQL and improves the scalability.
④ It can reduce the query of redundant records. Associated query at the application layer means that a record application needs to be queried only once, whereas associated query in the database may require repeated access to some data.
Here is another problem: if you want to query table A and table B.
The first scheme: check the data of two tables at one time, and use a double for loop to match at the application layer.
The second scheme: first find out the data of a table, and then repeatedly go to B table query.
Although both solutions have N^2 time complexity, they consume different resources, which is a matter of discretion.
**Hash Join
MySQL 8 has finally introduced Hash Join algorithms, syntable queries, and ** split associative queries. ** Who is better?