Six tips for parsing MySQL database performance optimization
- 2020-05-19 06:04:43
- OfStack
There are indexes and error prevention mechanisms on the database table surface, but a simple query can take a long time. An Web application may work well in a development environment, but it also performs poorly in a production environment. If you are a database administrator, chances are that you have encountered one of these situations at some stage. Therefore, this article presents tips and tricks for optimizing the performance of MySQL.
1. Storage engine selection
If your data table needs to be transacted, you should consider using InnoDB because it fully conforms to the ACID feature. If transactions are not required, it is wise to use the default storage engine, MyISAM. And don't try to use both storage engines at the same time. Think 1: in a transaction, some tables use InnoDB and the rest use MyISAM. The result? The entire subject will be cancelled, only those in the transaction will be brought back to the original state, and the rest of the committed data will be saved, which will lead to the conflict of the entire database. However, there is a simple way to take advantage of both storage engines. Most of the current MySQL suites include InnoDB, compilers, and linked lists, but if you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's an easy way, isn't it?
2. Counting problem
You should not use COUNT(*) to calculate the number of rows in a table if the storage engine for the table supports transaction processing, such as InnoDB. This is because using COUNT(*) in a productclass database returns at most one approximation, because at any given time, there is always one transaction running. If you use COUNT(*), you will obviously produce bug, which is the wrong result.
3. Repeatedly test the query
The trickiest problem with queries is not that no matter how careful you are, there will always be errors and bug will appear. On the contrary, the problem is that in most cases when bug appears, the application or database is already online. There really is no practical solution to this problem unless you run the test sample on an application or database. Any database query can be validated only after a large sample of thousands of records has been tested.
4. Avoid full table scans
Typically, a full-table scan is used when MySQL(or some other relational database model) needs to search or scan any particular record in a data table. In addition, it is often easiest to use indexed tables to address the inefficiencies caused by full table scans. However, as we saw in the subsequent problem, there is the wrong part.
5. Query using "EXPLAIN"
EXPLAIN is a good command to use when debugging, and we'll explore EXPLAIN in more detail below.
First, create a simple data table:
This table has five columns, and the last column, "deleted", is a variable of the Boolean class, flag, to check whether the account is active or deleted. Next, you need to populate the table with sample records (for example, 100 employee records). As you can see, the primary key is "emp_id". Therefore, using the E-mail address and password fields, we can easily create a query to verify or deny the login request, as follows (example 1) :
As mentioned earlier, COUNT(*) should be avoided. The code is corrected as follows (example 2) :
Now recall that in instance 1, the code queries locate and returns "email_id" and "password" equal to the number of rows for a given value. In example 2, the same query was made, except that it was explicitly required to list all values of "emp_id" that met the given criteria. Which query takes more time?
Obviously, both instances are equally time consuming database queries, because by accident, both instance queries are full table scanned. To better read the instructions, execute the following code:
In the output, focus on the second to last column: "rows". Let's say we've filled the table with 100 records, and it displays 100 on line 1, which is the number of rows that MySQL needs to scan to calculate the result of the query. What does that mean? This requires a full table scan. To overcome this, you need to add indexes.
6. Add indexes
Let's start with the important one: it's not wise to index every secondary problem you might encounter. Too many indexes can lead to slow performance and resource usage. Before proceeding to step 1, create a sample index in the instance:
Next, run the query again:
Note the value after the run. Not 100, but 1. So, to give the query results, MySQL scans only 1 row, thanks to the index created earlier. You may notice that the index is only created in the E-mail address field, and the query searches for other fields as well. This indicates that MySQL first performs an cros-check, checks to see if any of the values defined in the WHERE clause are indexed, and if so, performs the appropriate action.
However, it is not that each repeat will be reduced to 1. For example, if an index field is not unique to one (for example, the employee names column can have two rows of the same value), multiple records will remain even if the index is created. But it's still better than a full table scan. Also, the order in which columns are specified in the WHERE clause does not play a role in this process. For example, if, in the above query, the order of the fields is changed so that the E-mail address appears at the end, MySQL will still iterate over the indexed columns. Think about indexes and how to avoid a lot of full table scans and get better results. However, it takes a long time.
1. Storage engine selection
If your data table needs to be transacted, you should consider using InnoDB because it fully conforms to the ACID feature. If transactions are not required, it is wise to use the default storage engine, MyISAM. And don't try to use both storage engines at the same time. Think 1: in a transaction, some tables use InnoDB and the rest use MyISAM. The result? The entire subject will be cancelled, only those in the transaction will be brought back to the original state, and the rest of the committed data will be saved, which will lead to the conflict of the entire database. However, there is a simple way to take advantage of both storage engines. Most of the current MySQL suites include InnoDB, compilers, and linked lists, but if you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's an easy way, isn't it?
2. Counting problem
You should not use COUNT(*) to calculate the number of rows in a table if the storage engine for the table supports transaction processing, such as InnoDB. This is because using COUNT(*) in a productclass database returns at most one approximation, because at any given time, there is always one transaction running. If you use COUNT(*), you will obviously produce bug, which is the wrong result.
3. Repeatedly test the query
The trickiest problem with queries is not that no matter how careful you are, there will always be errors and bug will appear. On the contrary, the problem is that in most cases when bug appears, the application or database is already online. There really is no practical solution to this problem unless you run the test sample on an application or database. Any database query can be validated only after a large sample of thousands of records has been tested.
4. Avoid full table scans
Typically, a full-table scan is used when MySQL(or some other relational database model) needs to search or scan any particular record in a data table. In addition, it is often easiest to use indexed tables to address the inefficiencies caused by full table scans. However, as we saw in the subsequent problem, there is the wrong part.
5. Query using "EXPLAIN"
EXPLAIN is a good command to use when debugging, and we'll explore EXPLAIN in more detail below.
First, create a simple data table:
CREATETABLE'awesome_pcq'(
'emp_id'INT(10)NOTNULL
DEFAULT'0',
'full_name'VARCHAR(100)NOTNULL,
'email_id'VARCHAR(100)NOTNULL,
'password'VARCHAR(50)NOTNULL,
'deleted'TINYINT(4)NOTNULL,
PRIMARYKEY('emp_id')
) COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
This table has five columns, and the last column, "deleted", is a variable of the Boolean class, flag, to check whether the account is active or deleted. Next, you need to populate the table with sample records (for example, 100 employee records). As you can see, the primary key is "emp_id". Therefore, using the E-mail address and password fields, we can easily create a query to verify or deny the login request, as follows (example 1) :
SELECTCOUNT(*)FROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
As mentioned earlier, COUNT(*) should be avoided. The code is corrected as follows (example 2) :
SELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
Now recall that in instance 1, the code queries locate and returns "email_id" and "password" equal to the number of rows for a given value. In example 2, the same query was made, except that it was explicitly required to list all values of "emp_id" that met the given criteria. Which query takes more time?
Obviously, both instances are equally time consuming database queries, because by accident, both instance queries are full table scanned. To better read the instructions, execute the following code:
EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
In the output, focus on the second to last column: "rows". Let's say we've filled the table with 100 records, and it displays 100 on line 1, which is the number of rows that MySQL needs to scan to calculate the result of the query. What does that mean? This requires a full table scan. To overcome this, you need to add indexes.
6. Add indexes
Let's start with the important one: it's not wise to index every secondary problem you might encounter. Too many indexes can lead to slow performance and resource usage. Before proceeding to step 1, create a sample index in the instance:
ALTERTABLE'awesome_pcq'ADDINDEX'LoginValidate'('email_id')
Next, run the query again:
EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0
Note the value after the run. Not 100, but 1. So, to give the query results, MySQL scans only 1 row, thanks to the index created earlier. You may notice that the index is only created in the E-mail address field, and the query searches for other fields as well. This indicates that MySQL first performs an cros-check, checks to see if any of the values defined in the WHERE clause are indexed, and if so, performs the appropriate action.
However, it is not that each repeat will be reduced to 1. For example, if an index field is not unique to one (for example, the employee names column can have two rows of the same value), multiple records will remain even if the index is created. But it's still better than a full table scan. Also, the order in which columns are specified in the WHERE clause does not play a role in this process. For example, if, in the above query, the order of the fields is changed so that the E-mail address appears at the end, MySQL will still iterate over the indexed columns. Think about indexes and how to avoid a lot of full table scans and get better results. However, it takes a long time.