MySQL is basically a question that every background candidate should ask, and SQL optimization is one of the most important aspects of MySQL. If your SQL optimization answers are well organized and organized, your chances of getting in are greatly improved.

Like this SQL optimization, many online, so the interviewer listen to more, want to answer let the interviewer find new and fresh, it is necessary to learn to pretend to force the answer, ha ha ha…

For example, now I have an interviewer who says, there’s an ONLINE SQL execution that’s slow, how do you optimize it?

This kind of time had better be divided into several steps answer, do not come up to say, how how to write SQL, the interview should learn, jump out, see the whole picture, put in, see the essence.

What are the common causes of slow SQL statements?

At this point, you can say, “Hello, INTERVIEWER, I think the reason why SQL is slow can be divided into the following, remember, do not answer your own ambiguous, unable to justify even if you know, do not answer, or you will dig yourself a hole…

  • If the table is large and the fields following where or order BY are not indexed, it must be difficult to look up. However, sometimes the index is built, but in some specific scenarios, the index may fail, so index failure is one of the main causes of slow query. There are many invalidation scenarios, such as fuzzy query with white semicolon before it, query field using function or calculation operation, which may cause index invalidation.

  • InnoDB storage engine supports both row and table locks. We should pay attention to the possibility that row locks can be upgraded to table locks. During batch update operations, row locks are more likely to be upgraded to table locks. MySQL believes that if you use a large number of row locks on a table, the transaction performance will be reduced, which can lead to long lock waits and more lock conflicts for other transactions, so MySQL will upgrade row locks to table locks. In addition, row locks are index_based locks, and if we invalidate a conditional index during an update operation, the row lock will be upgraded to a table lock. In addition to lock escalation, although row locks are more granular and have more concurrency than table locks, they also introduce a new problem: deadlocks.

  • Improper SQL Statements Using improper SQL statements is also one of the most common causes of slow SQL. For example, it is customary to use SQL statements, to use paging queries in large tables, to sort non-index fields, and so on.

How do I analyze some slow SQL?

Now that I’ve mentioned some reasons why SQL execution is slow, how do you analyze this SQL

  • Slow_query_log: slow query status; slow_query_log_file: slow_query_log_file: slow query status Long_query_time: indicates the number of seconds in which the query is performed before the log is recorded.

  • Analyze the SQL execution plan through EXPLAIN

  • Run the Show Profile command to analyze SQL execution performance

What should you pay attention to when writing SQL? What experience do you have to talk about?

At this point, if the interviewer is still listening to your answer, you can raise your voice a little bit and say that some slow SQL is due in large part to the fact that we developers do not pay attention to SQL optimization when writing SQL, so I will mention some of the things THAT I know about SQL optimization… (Raising the decibel level in case the interviewer falls asleep, and hitting the key points, telling the interviewer I’m ready to pretend to be a jerk, hahaha…)

  • Count (*), count(*)≈count(1), greater than count(primary key).

  • If you are explicitly looking for a statement, use LIMIT 1; “, because the search will not continue after a matching record is found.

  • Optimizing paging queries

  • Avoid writing specific fields as Select * uses them. The reason is not only that Select * queries all fields, but also that Select * cannot use overwrite indexes.

  • Try to use a version later than MySQL 5.6

  • For using indexes

    • The index cannot be used to perform any function or operation on an index field.

      So at this point, don’t add functions except for the fields we index.
      • Note also some implicit conversions, for example, for the tradelog table (tradelog), the tradeid field type is varchar(32), and the field has an index, but when you performselect * from tradelog where tradeid=110717;Statement, you find that you are still going through a full index scan. That’s because it’s actually casting, which is what it doesmysql> select * from tradelog where CAST(tradid AS signed int) = 110717;.
      • Implicit character encoding conversion

        If two tables use different encoding sets, such as UTF8MB4 and UTF8, then the two fields are joined onceCONVERT(traideid USING utf8mb4)“, which also doesn’t use the index. In fact, the character set difference is only one of the conditions. During the join process, the function operation on the index field of the driven table is required, which directly causes the full table scan of the driven table.