There are indexes and error-proofing mechanisms on the surface of database tables, but a simple query can take a long time. Web applications may work well in the development environment, but they are equally bad in the product environment. If you're a database administrator, you're probably already experiencing this at some point. Therefore, this article will introduce the techniques and tips for optimizing MySQL performance.
1. Storage Engine Selection
If the datasheet requires transactions, consider using INNODB because it fully conforms to the acid characteristics. It is advisable to use the default storage engine MyISAM if transaction processing is not required. And do not try to use both storage engines at the same time. Consider: In a transaction, some data tables use InnoDB, while the rest use MyISAM. What happened? The entire subject will be canceled, only those in the transaction are brought back to the original state, and the rest of the submitted data is transferred, which will cause the entire database to conflict. However, there is a simple way to take advantage of the two storage engines at the same time. Most MySQL suites currently include InnoDB, compilers, and lists, but if you choose MyISAM, you can still download InnoDB separately and use it as a plugin. It's an easy 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 datasheet. This is because the product class database uses count (*) to return up to an approximate value, because there are always some transactions running at a particular time. This error result occurs if you use COUNT (*) to clearly produce a bug.
3. Repeatedly test the query
The most difficult problem with queries is not that there will always be errors, and that bugs can be created. On the contrary, the problem is that in most cases the application or database is online when a bug occurs. Indeed, there is no practical workaround for the problem, unless you run the test sample on the application or database. Any database query can only be recognized if it passes through a large number of sample tests of thousands of records.
4. Avoid full table scan
In general, 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, the easiest way to do this is to use an index table to address the inefficiencies caused by full table scans. However, as we have seen in subsequent questions, there is a part of the error.
5. Use "EXPLAIN" for query
Explain is a good command when debugging is needed, and the explain is discussed in depth below.
First, create a simple data table:
Copy Code code as follows:
CreateTable ' Awesome_pcq ' (
' emp_id ' INT (notnull)
DEFAULT ' 0 ',
' Full_name ' VARCHAR notnull,
' email_id ' VARCHAR notnull,
' Password ' VARCHAR notnull,
' Deleted ' TINYINT (4) Notnull,
PRIMARYKEY (' emp_id ')
) collate= ' Utf8_general_ci '
Engine=innodb
Row_format=default
This datasheet at a glance, a total of five columns, the last column "deleted" is a Boolean class 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 login requests, as follows (example one):
Copy Code code as follows:
SelectCount (*) fromawesome_pcqwhere
Email_id= ' Blahblah ' andpassword= ' Blahblah ' anddeleted=0
We mentioned earlier that you want to avoid using count (*). The code is corrected as follows (example two):
Copy Code code as follows:
Selectemp_idfromawesome_pcqwhere
Email_id= ' Blahblah ' andpassword= ' Blahblah ' anddeleted=0
Now recall that in instance one, the code query locates and returns the number of rows with "email_id" and "password" equal to the given value. In instance two, the same query is made, with the distinct requirement to list "emp_id" for all values that meet the given criteria. Which query is more time-consuming?
Obviously, these two examples are the same time-consuming database queries, because unintentionally, two instance queries all have a full table scan. To better read the instructions, execute the following code:
Copy Code code as follows:
Explainselectemp_idfromawesome_pcqwhere
Email_id= ' Blahblah ' andpassword= ' Blahblah ' anddeleted=0
In the output, focus on the penultimate column: "Rows." Let's say we've populated the table with 100 records, which shows 100 in the first row, which is the number of rows that MySQL needs to scan to compute the results of the query. What does that mean? This requires a full table scan. To overcome this disadvantage, you need to add an index.
6. Add Index
It's important to start with: It's unwise to create an index on every minor problem that you might encounter. Excessive indexing can result in slower performance and resource consumption. Before further discussion, create a sample index in the instance:
Copy Code code as follows:
Altertable ' Awesome_pcq ' Addindex ' loginvalidate ' (' email_id ')
Next, run the query again:
Copy Code code as follows:
Explainselectemp_idfromawesome_pcqwhere
Email_id= ' Blahblah ' andpassword= ' Blahblah ' anddeleted=0
Note the value after the operation. Not 100, but 1. Therefore, to give the results of the query, MySQL scanned only 1 rows, thanks to the previously created index. 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 executes a cros-check first, checks whether there is an index designation for the value defined in the WHERE clause, and performs the appropriate action if there is such a value.
However, it is not every repetition that will be reduced to one. For example, if you are not a unique indexed field (such as the employee Names column can have two rows of the same value), even if you create an index, there will be multiple records left. But it's still better than full table scans. Also, the order in which columns are specified in the WHERE clause does not work in this process. For example, if you change the order of fields in the previous query so that the e-mail address appears at the end, MySQL will still traverse the indexed column. Then, you should use your head on the index, and notice how to avoid a lot of full table scans and get better results. However, it will take a long time to go through the process.
