MySQL Database performance optimization tips and tricks

Indexes and error-proof mechanisms exist on the surface of a database table, but a simple query can take a long time. Web applications may run well in the development environment, but they behave equally poorly in a production environment. If you are a database administrator, you are likely already experiencing this situation at some stage. Therefore, this article will describe the techniques and tips for optimizing MySQL performance.

1. Selection of the storage engine

If the data table requires transaction processing, you should consider using INNODB because it is fully acid-compliant. If transaction processing is not required, it is advisable to use the default storage engine MyISAM. And do not attempt to use both storage engines at the same time. Think about it: in one transaction, some data tables use InnoDB, while the rest use MyISAM. The result? The entire subject will be canceled, only those in the transaction are brought back to the original state, and the rest of the committed data is dumped, which will result in a collision of the entire database. However, there is an easy way to take advantage of two storage engines at the same time. Most MySQL packages currently include InnoDB, compilers, and linked lists, but if you choose MyISAM, you can still download InnoDB separately and use it as a plugin. It's a simple way, isn't it?

2. Counting problems

If the data table uses a storage engine that supports transactions (such as InnoDB), you should not use COUNT (*) to calculate the number of rows in the data table. This is because the Product class database uses the Count (*) to return at most an approximation, because at a certain time, there are always some transactions running. This error results if you use COUNT (*) to clearly produce a bug.

3. Test queries repeatedly

The most difficult problem with queries is not that mistakes are always taken care of and cause bugs to occur. On the contrary, the problem is that in most cases the application or database is online when a bug occurs. There is no practical workaround for the problem, unless the test sample is run on the application or database. Any database query can be recognized only by a large number of sample tests with thousands of records.

4. Avoid full table scan

Generally, a full table scan is used when MySQL (or other relational database models) needs to search or scan any particular record in a datasheet. In addition, it is usually easiest to use an index table to solve the low-performance problems caused by full-table scanning. However, as we have seen in the subsequent questions, there is an erroneous part.

5. Use "EXPLAIN" to query

Explain is a good command when debugging is required, and the explain will be explored in depth below.

First, create a simple data table:

CREATE TABLE AWESOME_PCQ (

emp_id INT (Ten) Notnull DEFAULT ' 0 ',

Full_name VARCHAR (Notnull),

email_id VARCHAR (+) not NULL,

Password VARCHAR () not NULL,

Deleted TINYINT (4) Notnull,

PRIMARYKEY (emp_id)

) collate=utf8_general_ci

Engine=innodb

Row_format=default

This data table at a glance, a total of five columns, the last column "deleted" is a Boolean variable flag to check whether the account is active or has been deleted. Next, you need to populate the table with a sample record (for example, 100 employee records). As you can see, the primary key is "emp_id". Therefore, using the email address and password fields, we can easily create a query to validate or deny the login request as follows (instance one):

SELECT COUNT (*) from AWESOME_PCQ WHERE email_id= ' blahblah ' and password= ' Blahblah ' and deleted=0

As we mentioned before, you should avoid using count (*). The code is corrected as follows (example two):

SELECT emp_id from Awesome_pcq WHERE email_id= ' blahblah ' and password= ' Blahblah ' and deleted=0

Now recall that in instance one, the code query locates and returns the number of rows that "email_id" and "password" are equal to the given value. In instance two, the same query was made, and the difference was explicitly asked to list all values that meet the given criteria for "emp_id". Which query is more time consuming?

Obviously, these two instances are the same time-consuming database queries, because inadvertently, two instance queries have all been scanned in a full table. To read the instructions better, execute the following code:

EXPLAIN SELECT emp_id from Awesome_pcq where email_id= ' Blahblah ' and password= ' Blahblah ' and deleted=0 in the output, focus on the penultimate column: "Row S ". Let's say we've populated the table with 100 records, and it shows 100 in the first row, which is the number of rows that MySQL needs to scan to calculate the results of the query. What does this mean? This requires a full table scan. To overcome this disadvantage, you need to add an index.

6. Add an Index

The important thing to start with is that it's not wise to create indexes on every minor issue you might encounter. Too many indexes can cause slow performance and resource consumption. Before further discussion, create a sample index in the instance:

ALTER TABLE awesome_pcq addindex loginvalidate (email_id) Next, run the query again:

EXPLAIN SELECT emp_id from Awesome_pcq WHERE email_id= ' blahblah ' and password= ' Blahblah ' and deleted=0

Note the value after the run. Not 100, but 1. Therefore, to give the query results, MySQL scanned only 1 rows, thanks to the previously created index. You may notice that the index is created only in the E-mail address field, and the query searches for the other fields as well. This indicates that MySQL performs a cros-check first, checks whether there is an index designation for the value defined in the WHERE clause, and performs the corresponding operation if there is such a value.

However, it is not every repetition that will be reduced to one. For example, if the index field is not unique (such as the employee Names column can have two rows of the same value), even if the index is created, there will be multiple records left. But it's still better than a full table scan. Also, the order in which the columns are specified in the WHERE clause does not work in this process. For example, if you change the order of the fields in the query above so that the e-mail address appears at the end, MySQL will still traverse the index column. Then, take your mind on the index, and notice how to avoid a lot of full-table scans and get better results. However, this is going to take a long process. AC Buckle 2881064157

MySQL Database performance optimization tips and tricks