Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Some database optimization methods and indicators are discussed

We all know that to become an architect, database optimization is a must to understand some, today we will talk about Mysql database optimization problems. Limited to the author’s limited technology, dare not talk eloquently, then sorted out the following information for your reference.

Optimization of database statements

(1) Run the show status command to check the overall execution efficiency of SQL

(2) locate the execution efficiency of low SQL — SELECT

Because SELECT is the most performed, like the general add, delete and change does not take up too much efficiency, generally is the query.

(3) Analyze the execution efficiency of SQL statements through explain

Let’s say we write a statement, but we’re not sure how efficient that statement is. Mysql provides a built-in analysis tool that can help you analyze this statement, because you don’t know how efficient this statement is, but Mysql certainly does.

(4) Determine the problem to take optimization

Once we find the problematic statements, we need to optimize them.

Show status commands

  1. Check the current status of the mysql database.

The key for optimization is the fields starting with com, so we only need to run this command to see the fields starting with COM.

  1. Viewing Connection Count

We can use the following statement to check the number of connections:

 show status like 'connections';
Copy the code

You can use the number of database connections tool to view the number of database accesses during web page accesses.

  1. Check the running time of the Mysql server

In our actual production environment, we sometimes have some problems due to the long running time. In this case, this running time can help us know how long Mysql has been running continuously on this server. Normally, Mysql needs to be restarted every six months.

  1. View the number of slow queries

The number of slow queries is related to database optimization!

  1. View the duration definition of a slow query

Mysql defaults to 10 seconds for this item. Generally this slow query is not the same as each company, according to the needs of your company to set.

  1. Set the time definition for slow query

There are other methods of database optimization: adding indexes, killing processes, etc., which are not covered in this article.