MYSQL optimization methods


1, Selection of the most suitable field attribute
MySQL can access support large data very good volume, but generally speaking, the tables in the database is small, it performs the query will be faster. Therefore, when creating the table, in order to obtain a better performance, we can of the fields in the table width to be as small as possible. For example, in the definition of postal code of this field, if you set it to CHAR (255), it is clear to the database add unnecessary space, even the use of VARCHAR type is superfluous, because CHAR (6) can be very good to complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define an integer field.
Another way to improve efficiency in case of possible, should as far as possible the field is set to NOT NULL, so that in the future the query execution time, instead of comparing the NULL database.
For some text fields, such as "province" or "sex", we can define them as a ENUM type. Because in MySQL, the ENUM type is used as the numerical data to process, and the numerical data are processed up faster than text type much faster. In this way, we can improve the performance of the database.
2, Use the connection (JOIN) to replace the sub query(Sub-Queries)
MySQL began to support SQL subqueries from 4.1. This technology can use the SELECT statement to create a single query results, and then put the result as the filter used in another query. For example, we want to customers without any order of basic information in the customer table deleted, can be taken out from the sales table will all send orders to customers using ID query, and then sends the results to the main query, as shown below:
DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
Use a subquery can one-time complete requires multiple steps to complete the SQL operation a lot of logic, but also can avoid the transaction or table lock, and it is very easy to write. However, in some cases, sub queries can be connected more efficient (JOIN). Alternative. For example, suppose that we will all have no order records user take out, can be completed with this query:
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
If you use the connection (JOIN). To complete the query work, speed will be much faster. Especially when the salesinfo table to CustomerID built the index, performance will be better, the query is as follows:
SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL
Connection (JOIN), is more efficient, because the MySQL does not need to create a temporary table in memory to complete this logically requires two steps of inquiry.
3, The use of combined (UNION) instead of manually create temporary table
MySQL began to support UNION query from the 4 version, it can be put to use the temporary table two or more SELECT queries in a query in Merged. When the client query session ends, the temporary table will be automatically deleted, so as to ensure the orderly, efficient database. Use UNION to create the query time, we only need to use the UNION as the key to connect multiple SELECT statements can be, it is necessary to pay attention to the number of all the fields in the SELECT statement to the same. The following example demonstrates using a UNION query.
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author
SELECT Name, Supplier FROM product
4, Transaction
Although we can use the sub query (Sub-Queries), connection (JOIN) and combined (UNION) to create a variety of queries, but not all database operations can be only one or a few SQL statements can be completed. More time is needed to be used in a series of statements to complete some work. But in this case, when the block of statements in a statement run error, the statement block operation will become uncertain. Imagine, to also insert a data of two related tables, may appear such circumstance: the first successful update table in database, unexpected situation suddenly appeared, causing the second tables in the operation is not completed, this will cause data, incomplete, or even destroy the database data. To avoid this situation, you should use a transaction, its role is to: or a block of statements each statement on success, or failure. In other words, is that it can maintain the consistency and integrity of the data in the database. Things start with the BEGIN keyword, the keyword COMMIT end. A SQL operation between the failure, then, the ROLLBACK command to restore the database to the state before the start of the BEGIN.
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
Another important role of the transaction is when multiple users using the same data source at the same time, it can use the method of locking the database to provide a secure access way for the user, so that users are not interference from other users.
5, Lock table
Although the transaction is to maintain a very good method of database integrity, but because of its exclusive character, sometimes will affect the performance of a database, especially in the application of the system greatly in. Because during the execution of a transaction, the database will be locked, so the other user request can only temporarily to wait until the end of the transaction. If a database system with only a few user
To use, the impact will not be the transaction will be much of a problem; but if there are tens of thousands of users to access a database system at the same time, such as access to an electronic commerce website, will have a delayed response to serious.
In fact, performance in some cases we can lock table method to obtain better. The following example with the lock table method to complete the transaction in an example function.
SELECT Quantity FROM inventory
UPDATE inventory SET Quantity=11
Here, we use a SELECT statement to remove the initial data, by some calculations, using the UPDATE statement to new values will be updated to the table. Include the WRITE keyword in the LOCK TABLE statement can guarantee in the UNLOCK TABLES command to be executed before, no other access to insert, update or delete operation on inventory.
6, The use of foreign keys
Lock table method can maintain the integrity of the data, but it cannot ensure the relevance data. This time we can use a foreign key. For example, a foreign key can guarantee the record sales of everything points to an existing customer. Here, a foreign key can map CustomerID in the customerinfo table to the salesinfo table in the CustomerID, any no legitimate CustomerID records will not be updated or inserted into the salesinfo.
CREATE TABLE customerinfo
PRIMARY KEY ( CustomerID )
CREATE TABLE salesinfo
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
Attention parameters in the example"ON DELETE CASCADE". The parameters to ensure when a customer record in the customerinfo table is deleted, salesinfo all the records in the table associated with the customer will be automatically deleted. If you want to use a foreign key in MySQL, we must remember that when creating the table type definition table for transaction safety table type InnoDB. The default type the type is not the MySQL table. The method definition is TYPE=INNODB plus CREATE in the TABLE statement. In the example shown.
7, Using the index
The index is a common method for improving database performance, it can make the database server with no index much faster retrieval of specific lines, especially in the query statement which contains MAX (), MIN (ORDERBY) and when these commands, performance is improved more obviously. The establishment of index on which fields? Generally speaking, the index should be established on the basis of those will be used for JOIN, WHERE and ORDER BY field judgment on sorting. Try not to the database in a containing a large number of repeated values field index. For a field of type ENUM, the emergence of a large number of duplicate values are possible, such as customerinfo in the "province".. field, establish the index in this field will not have what help; on the contrary, may also reduce the performance of the database. We can also create a suitable index when creating the table, can also create index in the use of ALTER TABLE or CREATE INDEX. In addition, MySQL
To support full-text indexing and searching from version 3.23.23. Full text indexing is a type of FULLTEXT index in MySQL, but can only be used for MyISAM types of table. For a large database, loading the data to a no FULLTEXT index in the table, and then use the ALTER TABLE or CREATE INDEX to create index, will be very fast. But if the data is loaded into a FULLTEXT index in the table, the implementation process will be very slow.
8, Query optimization
The vast majority of cases, the use of indexes can improve the query speed, but if the SQL statement using inappropriate words, the index will not be able to play its due role. Here are some points that should be noted. First of all, the best is to compare the operation in the fields of the same type. In the 3.23 edition of MySQL before, this is even a necessary condition. For example, you cannot make a built in INT field and the BIGINT field index were compared; but as a special case, when the field size fields and VARCHAR type of CHAR fields of the same, they can be compared. Secondly, in the construction field indexed. Try not to use the functions operate.
For example, the use of YEAE in the field of a DATE type of function (), will make the index can not play its due role. So, the two query below while the returned results, but the latter is much faster.
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
The same situation also occurs in the calculation of the time for numerical field:
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
The two query above also returns the same result, but the following query will be much faster than a front. Third, in the search character field, we sometimes use the LIKE keyword and wildcards, although this approach is simple, but it is at the expense of the performance of the system for the price. For example, each record comparison table the following query will be in.
WHERE name like "MySQL%"
But if for the following query, return the results, but the speed to be quick on many:
WHERE name>="MySQL"and name<"MySQM"
Finally, attention should be paid to prevent MySQL for automatic type conversion in the query, because the conversion process will make the index ineffective.


Posted by Antoine at December 03, 2013 - 1:59 AM