Six tips for MySQL database performance optimization-MySQL
Source: Internet
Author: User
Six tips for parsing MySQL database performance optimization bitsCN.com database tables have indexing and error prevention mechanisms on the surface. However, a simple query takes a long time. Web applications may run well in the development environment, but are equally bad in the product environment. If you are a database administrator, you may have encountered the above situation at a certain stage. Therefore, this article describes how to optimize MySQL Performance.
1. storage engine selection If the data table requires transaction processing, InnoDB should be considered because it fully complies with the ACID feature. If no transaction is needed, it is wise to use the default storage engine MyISAM. And do not try to use both storage engines. Think about it: in a transaction, some data tables use InnoDB, while others use MyISAM. What are the results? The entire subject will be canceled, and only those in the transaction processing will be taken back to the original state, and the rest of the committed data will be transferred to the memory, which will lead to conflicts throughout the database. However, there is a simple method to take advantage of the advantages of both storage engines. Currently, most MySQL suites include InnoDB, compiler, and linked list. if you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's a simple method, isn't it?
2. counting problems If the data table uses a storage engine that supports transaction processing (such as InnoDB), you should not use COUNT (*) to calculate the number of rows in the data table. This is because the product database uses COUNT (*) and returns an approximate value at most, because at a specific time, some transactions are always running. If you use COUNT (*), it will obviously produce a bug, and this error result will appear.
3. perform repeated test queries. The most difficult issue to query is that no matter how careful you are, errors always occur, and bugs may occur. On the contrary, the problem is that when a bug occurs in most cases, the application or database is online. There is indeed no practical solution to this issue unless you run the test sample on an application or database. Any database query can only be recognized after a large number of sample tests with thousands of records.
4. avoid full table scan Generally, full table scan is used if MySQL (or other relational database models) needs to search for or scan any specific record in the data table. In addition, the simplest method is to use an index table to solve the problem of low efficiency caused by full table scan. However, as we can see in subsequent problems, this has an error section.
5. use "EXPLAIN" to query When debugging is required, EXPLAIN is a good command. The following describes EXPLAIN in depth.
First, create a simple data table:
This data table is clear at a glance and has five columns. The last column "deleted" is a Boolean variable flag to check whether the account is active or deleted. Next, you need to fill 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 login requests, as shown in instance 1 ):
Now let's recall that in instance 1, the code queries and locates and returns the number of rows with "email_id" and "password" equal to the given value. In instance 2, the same query is performed. The difference is that all values of "emp_id" that meet the given criteria are explicitly required. Which query is more time-consuming?
Obviously, both instances are database queries that are equally time-consuming, because, inadvertently, both instances perform full table scans. To better understand the commands, execute the following code:
When outputting data, the data is concentrated in the second to last column: "rows ". Suppose we have filled the table with 100 records, and it will display 100 in the first row. this is the number of rows that MySQL needs to scan to calculate the query results. What does this mean? This requires a full table scan. To overcome this disadvantage, you need to add an index.
6. add an index Start with important: it is unwise to create an index for every secondary problem that may occur. Too many indexes will slow down performance and occupy resources. Before further discussion, create a sample index in the instance:
ALTERTABLE 'awessome _ pcq' ADDINDEX 'loginvalidate' ('email _ id ')
Pay attention to the value after running. Not 100, but 1. Therefore, to give the query results, MySQL only scans one row, thanks to the previously created index. You may notice that the index is only created in the email address field, and other fields are also searched in the query. This indicates that MySQL executes a cros-check first to check whether the defined values in the WHERE clause are specified by an index. if such a value exists, the corresponding operation is performed.
However, it will not be reduced to one at a time. For example, if it is not a unique index field (for example, the employee names column can have two rows with the same value), multiple records will be left even if an index is created. But it is still better than full table scan. In addition, the column sequence in the WHERE clause does not play a role in this process. For example, if the order of fields is changed in the preceding query so that the email address appears at the end, MySQL will still traverse the index column. Therefore, we need to use our brains on indexes to avoid a large number of full table scans and obtain better results. However, this requires a long process.
BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
