• Old Liu is about to find a job of graduate students, self-taught big data development, along the way, feeling quite deep, online big data data of good and bad mixed, so want to write a detailed guide to big data development. This guide to the big data [basic knowledge] [framework analysis] [source understanding] are described in their own words, so that partners self-study from now on do not ask people.
  • Your praise is my motivation to continue to update, no white whoring, see will have harvest, refueling together.

Today I’m going to share with you MySQL performance optimization, which is the last part of MySQL’s big Data development guide. Performance optimization for Liu, is a means to master, how to make themselves more excellent, this content or take a good look!

This article is relatively concise, the core content is summarized in SQL optimization experience, through this article mysql performance optimization, you can master the following content:

  1. Can use and analyze slow query logs
  2. Ability to use and analyze profiles
  3. Summary of SQL optimization experience

How to perform performance analysis?

Generally, performance analysis is carried out in the following three steps:

  1. You need to use the slow query log function to obtain all SQL statements that take a long time to query
  2. Second view the execution plan View the execution plan explain of the SQL with the problem
  3. Finally, you can use show Profile to see the performance usage of the SQL in question

Slow query log analysis

First, we need to use slow query log because it collects SQL statements that take a long time to query. Before using slow query log, we must enable slow query log. Add the following parameters to [mysqld] in the configuration file my.cnf (usually /etc/my.cnf) :

slow_query_log=ON

long_query_time=3

slow_query_log_file=/var/lib/mysql/slow-log.log

Copy the code

After these parameters are added, restart MySQL to query whether logs are enabled.

Tool for analyzing slow query logs

There are many tools to analyze the slow query log, Lao Liu shares several tools, detailed usage we query by ourselves.

  1. Mysqldumpslow is a slow query log tool provided by MySQL. You can use mysqlDumpslow to search SQL statements in slow query logs.
  2. Percona-toolkit is a collection of advanced command line tools that can view summary information about current services, detect disk, analyze slow query logs, find duplicate indexes, implement table synchronization, and much more (write a separate blog about Percona-Toolkit).

Explain to view the SQL statements with problems

When SQL query speed is slow, we can use explain to view the related situation of this SQL statement, this part of the content has been in the master MySQL index talked about, we can go to see.

Show profile To view the SQL statement in question

Query Profiler is a Query diagnostic analysis tool that comes with MySQL. It can analyze the hardware performance bottlenecks of an SQL statement. Such as CPU, IO, etc., and the time it takes to execute the SQL. However, this tool is only available in MySQL 5.0.37 and above. This function is disabled by default. You need to manually enable it.

Summary of SQL optimization experience

Since Liu is still a graduate student and has not yet worked, he can only summarize other people’s experience in SQL performance optimization and share it with you. What Liu wants to do in this paper is to share some SQL optimization knowledge points summarized by excellent engineers. The previous content is relatively simple, I hope you don’t complain!

  1. Don’t use select * from t anywhere, replace “*” with a list of specific fields, and don’t return any fields you don’t need.

  2. More indexes are not always better. While indexes can improve the efficiency of select operations, they can also reduce the efficiency of insert and update operations. The number of indexes in a table should not exceed 6. If there are too many, you should consider whether it is necessary to build indexes on infrequently used columns.

  3. Not all indexes are effective for queries. SQL queries are optimized according to the data in the table. When there is a large number of repeated data in the index column, SQL queries may not use indexes.

  4. Use numeric fields. If fields contain only numeric information, do not use character fields. This reduces query and connection performance and increases storage overhead. This is because the engine compares each character in the string one by one while processing queries and joins, whereas for numeric types it only needs to compare once.

  5. Use vARCHar instead of char whenever possible because, first of all, the storage space of a longer field is smaller, which saves storage space, and second, it is obviously more efficient for queries to search within a relatively small field.

  6. If temporary tables are used, you must explicitly delete all temporary tables at the end of the stored procedure, truncate TABLE first, and then DROP table. In this way, system tables cannot be locked for a long time.

  7. Queries should be optimized to avoid full table scans, and indexes should be considered on columns related to WHERE and Order BY first.

  8. Try to avoid null values for fields in the WHERE clause, as this will cause the engine to abandon the index for a full table scan.

    For example, select * from t where num is null

    Select * from t where num=0; select * from t where num=0;

  9. Do not use inequality on index fields, use (! If = or < >), the index is invalid and full table scan is performed.

  10. Try to avoid using OR to join conditions in the WHERE clause, as this will cause the engine to abandon the index for a full table scan.

    For example, select * from t where num=10 or num=20

    Select * from t where num=10 union all select * from t where num=20

  11. Expression operations on fields in the WHERE clause should be avoided as much as possible, which can cause the engine to abandon indexes for a full table scan.

    For example, select * from t where num/2=100

    Select * from t where num=100*2

  12. Try to avoid functional manipulation of fields in the WHERE clause, which will cause the engine to abandon indexes for full table scans.

    For example, select * from t where substring(name,1,3)=’ ABC ‘– name id starting with ABC

    Select * from t where name like ‘ABC %’

  13. Do not perform functions, arithmetic operations, or other expression operations to the left of the “=” in the WHERE clause, or the system may not use the index properly.

  14. In many cases it is a good choice to use exists instead of in.

    For example: select num from a where num in(select num from b)

    Select num from a where exists(select 1 from b where num=a.num)

conclusion

This article, as the last big data development guide for MySQL, introduces some skills of SQL performance optimization succinctly. I hope you will follow Liu’s article and try to explain these points in your own words.

Although the current level may not be as good as you, but Old Liu will strive to become more excellent, let you friends self-study from now on!

Big Data Development Guide address is as follows:

  • github：https://github.com/BigDataLaoLiu/BigDataGuide
  • Yards cloud: https://gitee.com/BigDataLiu/BigDataGuide

If you have any questions, please contact our official account: hardworking Old Liu. The article has seen this, like attention to support a wave!