(3) MySQL optimized SQL statement optimization

Overview

This article mainly introduces some common SQL optimization techniques.

SQL Optimization 1.select * FROM table_name where;

It is recommended to change the * to the required column. This does not have a noticeable effect on speed, mainly considering saving memory.

2.like Statements

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

3. Do not perform calculations on columns and do not use indexes

select * from users where YEAR(adddate)<2007;

The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to

select * from users where adddate<‘2007-01-01’;

4. Do not use NOT and <> operations

None in and <> do not use the index for full table scanning. Not in can be replaced by not exists, id<>3 can be replaced with id>3 or id<3.

5. Optimize your query for query caching

When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.
The main problem here is that this is a very easy thing to ignore for programmers. Because, some of our query statements will let MySQL not use the cache. Take a look at the following example:

6. Never ORDER by RAND ()

This use only degrades the performance of your database exponentially. The problem here is that MySQL will have to execute the rand () function (which consumes CPU time), and this is done for each row of records to be recorded and then sorted. Even if you use limit 1 it doesn't help (because it's sorted).

7.limit Huge_num,offset

At the time of paging, when the huge_num is larger, this paging has a big performance problem. I use ' limit 1000, 20; ' A record of 1000-1020 rows will traverse 1020 rows, and the first 1000 records will be discarded.

SELECT * FROM payment ORDER BY rental_id LIMIT 100,10;

Idea one: Use subqueries to overwrite SQL by index paging back table

SELECT * FROM payment a INNER JOIN (SELECT payment_id FROM payment ORDER BY rental_id LIMIT 100,10)b ON a.payment_id = b.payment_id;

Idea two: Use between ... and ...

#不建议，用处不大SELECT * FROM payment WHERE rental_id BETWEEN 100 AND 110 ORDER BY rental_id;

Idea three: In consultation with the developer, the page turn process by adding a parameter Last_page_record to record the last line sort number on the previous page, and then find the next page of records through the parameter range.

8. Use COUNT (*) instead of count (ID); 9. Bulk Insert

 Insert  into T (id,name) values(1,' test1 '); Insert  into T (id,name) values(2,' test2 '); Insert  into T (id,name) values(3,' test3 ');#改为 Insert  into T (id,name) values(1,' test1 '), (2 ,' test2 '), (3,' test3 ');

10.order by reducing Filesort ordering, returning ordered data directly by index Reference

Http://www.cnblogs.com/daxian2012/articles/2767989.html
http://blog.csdn.net/jetxt/article/details/5992360

(3) MySQL optimized SQL statement optimization