Shulou(Shulou.com)05/31 Report--

This article focuses on "sharing six tips for MySQL database performance optimization". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "six tips for MySQL database performance optimization".

1. Selection of storage engine

If the data table requires transaction processing, you should consider using InnoDB because it fully conforms to the ACID feature. If transaction processing is not required, it is advisable to use the default storage engine MyISAM. And don't try to use both storage engines. Think about this: in a transaction, some data tables use InnoDB and others use MyISAM. How did it turn out? The entire subject will be deleted, only those in the transaction will be brought back to the original state, and the rest of the committed data will be transferred, which will lead to conflicts throughout the database. However, there is a simple way to take advantage of the advantages of both storage engines. Most MySQL suites currently include InnoDB, compiler, and linked lists, but if you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's a simple way, isn't it?

two。 Counting problem

If the data table uses a storage engine that supports transaction processing (such as InnoDB), you should not use COUNT (*) to count the rows in the data table. This is because using COUNT (*) in the product database returns at most an approximate value, because there are always some transactions running at a particular time. If you use COUNT (*), you will obviously produce bug, and this error results.

3. Repeated test query

The thorniest problem with queries is not that no matter how careful you are, there will always be errors that lead to bug. On the contrary, the problem is that in most cases when bug appears, the application or database is already online. It is true that there is no practical solution to this problem unless the test sample is run on an application or database. Any database query can be recognized only if it is tested by a large number of samples of thousands of records.

4. Avoid full table scanning

Typically, full table scans are used when MySQL (or other relational database models) need to search for or scan any particular record in a data table. In addition, the easiest way is to use index tables to solve the inefficiency problems caused by full table scans. However, as we can see in subsequent questions, there is an error.

5. Use "EXPLAIN" to query

EXPLAIN is a good command when you need to debug, and EXPLAIN will be discussed in depth 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

The data table is clear at a glance, with five columns, and 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 sample records (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 verify or reject the login request, as shown in example 1:

SELECTCOUNT (*) FROMawesome_pcqWHEREemail_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

We mentioned earlier that you should avoid using COUNT (*). The code is corrected as follows (example 2):

SELECTemp_idFROMawesome_pcqWHEREemail_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

Recall that in example 1, the code queries to locate and returns the number of rows with "email_id" and "password" equal to a given value. In example 2, the same query is made, except that it is explicitly required to list all values of "emp_id" that meet the given criteria. Which query is more time-consuming?

Obviously, both instances are equally time-consuming database queries, because inadvertently, both instance queries perform full table scans. To better understand the instructions, execute the following code:

EXPLAINSELECTemp_idFROMawesome_pcqWHEREemail_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

On output, focus on the penultimate column: "rows". Assuming that we have populated the table with 100 records, it will display 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 that mean? This requires a full table scan. To overcome this drawback, you need to add an index.

6. Add Index

Let's start with the important one: it's not wise to index every minor problem you might encounter. Too many indexes can lead to slower performance and resource consumption. Before going any further, create a sample index in the instance:

ALTERTABLE'awesome_pcq'ADDINDEX'LoginValidate' ('email_id')

Next, run the query again:

EXPLAINSELECTemp_idFROMawesome_pcqWHEREemail_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

Please note the value after running. Not 100, but 1. Therefore, to give the query results, MySQL scanned only one row, thanks to the previously created index. You may notice that the index is created only in the e-mail address field, while the query searches other fields as well. This indicates that MySQL first executes a cros-check to check whether the value defined in the WHERE clause is indexed, and if there is such a value, the corresponding operation is performed.

However, it is not reduced to one at a time. For example, if it is not a unique index field (for example, an employee names column can have two rows of the same value), even if the index is created, multiple records will be left. But it is still better than a full table scan. Also, the order of the columns specified in the WHERE clause does not fluctuate in this process. For example, if you change the order of the fields in the above query so that the e-mail address appears last, MySQL will still traverse the index column. In that case, you should use your brains on the index to pay attention to how to avoid a large number of full table scans and get better results. However, it will take a long time.

At this point, I believe you have a deeper understanding of the "sharing of six tips for MySQL database performance optimization". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.