MySQL Query optimization

Transferred from: http://blog.csdn.net/kevinlifeng/article/details/43233227

Statement is their own study is not original, hope everyone support the original.

SQL statement optimization and indexing

1.Innerjoin and left connection, right connection, sub query

A. Inner joins are also called equivalent connections, left/rightjoin are outer joins.

SELECT A.id,a.name,b.id,b.name from A left JOIN B on a.id =b.id;

SELECT A.id,a.name,b.id,b.name from A right joins on B a.id= b.id;

SELECT a.id,a.name,b.id,b.name from A INNER joins on a.ID =b.id;

In many ways, it is proven that inner join performance is faster because the inner join is an equivalent join, perhaps with a smaller number of rows returned. But we have to remember that some of the statements are invisible to the equivalent connection, such as:

SELECT A.id,a.name,b.id,b.name from a b WHERE a.id = b.id;

Recommended: You can use the inner join connection as much as possible with inner join connection

B The performance of the subquery is slower than the external connection performance, as far as possible to replace the subquery with an outer join.

Select* from A where exists (SELECT * from B where id>=3000 and A.uuid=b.uuid);

A table of data is a level 100,000 table, B table for the millions table, in the local execution of almost 2 seconds or so, we can see through the explain sub-query is a related subquery (dependence subquery); MySQL is to perform a full-table query on appearance A, and then execute the subquery successive subqueries, if the outer table is a large table, we can imagine that query performance will be worse than this.

A simple optimization is to use the Innerjoin method instead of the subquery, the query statement instead:

select* from A inner join B using (UUID) where b.uuid>=3000;

This statement executes the test in less than a second;

C When using on and where, remember the order of them, such as:

SELECT A.id,a.name,b.id,b.name from A left JOIN B on a.id =b.id WHERE b.name= ' XXX '

Execution will first filter out some of the rows of table B before executing on. The where is, however, then filters the records that are generated by their two connections.

But here's a reminder: on the following conditions only filter out the number of bars in table B, but the number of rows returned by the connection is the same as the number of rows in a table. Such as:

SELECT A.id,a.name,b.id,b.name from A left JOIN B on a.id =b.id;

The number of records returned is the number of tables in a table, and on the following conditions only serve to filter the number of records in Table B, and

SELECT a.id,a.name,b.id,b.name from A, B WHERE a.id = b.id

The number of bars returned is a record of the Cartesian product, which conforms to the a.id = b.id condition.

D When using join, you should use small results to drive the results (left join table results as small as possible, if the conditions should be placed on the left to deal with, right join the same reverse), colleagues try to involve the multi-table union query to split multiple queries (multiple table query efficiency is low, Easy to lock the table and block). Such as:

Select * from A left join B ona.id=b.ref_id where b.ref_id>10;

Can be optimized to: SELECT * FROM (SELECT * from A wehre ID >10) T1 left join B ont1.id=b.ref_id;

2. Build indexes to speed up query performance.

A When creating a composite index, the fields used in the Where condition are in the composite index, it is better to put this field on the leftmost side of the composite index, so that the index can be used to improve the query.

B Ensure that the index of the connection is the same type, meaning that the fields associated with table A and table B must be of the same type. These types are indexed so that two tables can use the index, and at least one table cannot be indexed if the type is different.

C An index, not just a primary key and a unique key, or any other column. When using the like one of the indexed field columns.

such as: Select *from A name like ' xxx% ';

This SQL uses the index of name (if name is indexed), and the following statement does not use the index

Select * from A name like '%xxx ';

Because '% ' represents any character,%xxx does not know how to index it, so the index is not used.

D. Composite Index

For example, there is a statement like this: select* from the users where area = ' Beijing ' and age=22;

If we were to create indexes on area and age, because MySQL queries can use only one index at a time, the full table scan is a lot more efficient when it's relatively non-indexed, but it's more efficient if you create a composite index on the area,age two column. If we create a composite index (area,age,salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left-prefix attribute. Therefore, when we create a composite index, the columns that are most commonly used as constraints are placed on the leftmost, descending order.

E. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index (unless there is a unique value for the domain, there can be one null), and if one column contains a null value in the composite index. So we don't want the default value of the field to be null when the database is designed.

F. Using a short index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), if the majority value is unique within 10 or 20 characters of money, then do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

G Indexed issues for sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. Therefore, the database default ordering can meet the requirements of the situation do not use sorting operations; Try not to include more than one column of sorts, if you need to create a composite index for these columns.

3.limit Tens when paging is optimized.

A In our usual use limit, such as:

Select * from A ORDER by ID limit 1, 10;

This way, when the table data is very small, there is no performance problem, if you arrive at tens, such as:

Select * from A order by ID limit10000000,10;

Although only 10 records are queried, but this performance is unbearable. So why is it that when the table data is very large, we continue to use the persistence layer framework such as hibernate,ibatis to have some performance problems, unless the persistence layer framework optimizes these big data tables.

B In meeting the above situation, we can use another kind of statement optimization, such as:

SELECT * from A where id>= (select Idfrom a limit 10000000,1) limit 10;

This is really a lot quicker, but only if the ID field is indexed. Perhaps this is not the optimal, in fact, can also write:

Select * from A where ID between 10000000and 10000010;

This is more efficient.

4. Try to avoid the SELECT * command

A. The more data is read from a table, the more slowly the query becomes. It increases the operating time of the disk, or if the database server is separate from the Web server, you will experience a very long network delay. Simply because the data is not necessary to transfer between servers.

5. Try not to use the by RAND () command

A If you really need to show your results randomly, there are many better ways to achieve them. This function may execute the by RAND () command for each individual row in the table-this consumes the processor's processing power, and then gives you just one line back.

6. Use limit 1 to obtain a unique row

A. Sometimes when you want to query a table, you know you need to look at a line, you may be able to query a unique record. You can use limit 1 to terminate the database engine to continue scanning the entire table or index, such as:

Select * from A where namelike '%xxx ' limit 1;

So as long as the query conforms to a record like '%xxx ', the engine will not continue to scan the table or index.

7. Sort as few as possible

A. Sorting operations consume more CPU resources, so reducing the ordering can be high in cache hit ratio

8. As little or as

A. When there are multiple conditions in the WHERE clause to "or" coexist, the MySQL optimizer does not have a good solution to its execution plan optimization problem, coupled with the MySQL-specific SQL and storage layered architecture, resulting in its performance compared to the underground, Many times the use of the Union All or union (when necessary) instead of "or" will result in better results.

9. Try to use UNION all instead of union

The difference between a.union and union All is that the former needs to merge two (or more) result sets and then perform a unique filtering operation, which involves sorting, adding a lot of CPU operations, and increasing resource consumption and latency. So when we can confirm that it is not possible to duplicate a result set or do not care about repeating the result set, try to use union all instead of union.

10. Avoid type conversions

A. The "type conversion" here refers to the type conversion that occurs when the type of the column field in the WHERE clause is inconsistent with the passed parameter type. The conversion of an artificially converted function directly results in the inability of the index to be used by MySQL. If you do not want to transform, you should convert on the incoming parameter.

11. Do not perform calculations on columns

A. as follows: SELECT * fromusers where year (adddate) <2007; will operate on each row, which causes the index to fail the full table scan, so we can change to:

Select * from users where adddate< ' 2007-01-01 ';

12. Try not to use not and <> operations

A. None in and <> operations do not use indexes, but full table scans will be performed. Not in can be replaced by not exists, id<>3 can use id>3 or ID <3; if not exists is a subquery, but also as far as possible to convert to outer joins or equivalent connections, to see the specific SQL business logic.

B Convert not in to a left join such as:

SELECT * FROM CustomerInfo WHERE customeridnot in (SELECT CustomerID from Salesinfo);

Optimization:

SELECT * from CustomerInfo left Joinsalesinfoon CustomerInfo. Customerid=salesinfo. CustomerID Wheresalesinfo.customerid is NULL;

13. Use BULK Insert to save interactivity (preferably with stored procedures)

A. Try to use Insert Intousers (Username,password) VALUES (' test1 ', ' Pass1 '), (' Test2 ', ' Pass2 '), (' Test3 ', ' pass3 ');

14. Locking the table

A. Although a transaction is a good way to maintain database integrity, it can sometimes affect the performance of the database, especially in many applications, because of its exclusivity. The database will be locked as a result of the transaction execution process. Therefore, other user requests can only be temporarily waited until the transaction is settled. If a database system has only a few users to use, the impact of the transaction will not be a big problem; but suppose that thousands of users are accessing a database system at the same time, such as visiting an e-commerce site, will result in a more severe response delay. In some cases we can get better performance by locking the table. For example:

LOCK TABLE Inventory Write

Select quanity from Inventory whereitem= ' book ';

...

Update Inventory Set quantity=11 whereitem= ' book ';

UNLOCK TABLES;

Here, we use a SELECT statement to remove the initial data and, with some calculations, update the new value to the list with the UPDATE statement. The lock table statement, which contains the Write keyword, guarantees that there will be no additional access to the inventory to insert, update, or delete operations until the unlock tables command is executed.

15. Create a view of queries associated with multiple tables

A The Association of multiple tables may have a performance problem, we can set up a view of multiple tables, so that the operation of simple words, increase data security, through the view, the user can only query and modify the specified data. and improve the logical independence of the table, the view can shield the effect of the original table structure changes.

MySQL Query optimization