(3) mysql Optimization-SQL statement Optimization

(3) mysql Optimization-SQL statement Optimization
Overview

This article mainly introduces some common SQL Optimization Techniques

SQL Optimization 1. select * from table_name where;

We recommend that you change * to the required column. This will not have a significant impact on the speed, mainly considering saving memory.

2. like statement

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

3. Do not perform operations on columns. indexes cannot be used.

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

The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it

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

4. Do NOT use the not in and <> operations

Not in and <> operations do NOT use indexes to scan the entire table. Not in can be replaced by not exists, and id <> 3 can be replaced by id> 3 or id <3.

5. Optimize Your query for the query Cache

When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table.
The main problem here is that this is easy for programmers to ignore. Because some of our query statements will make MySQL not use cache. See the following example:


6. Never order by rand ()

This only causes an exponential decline in the performance of your database. The problem here is that MySQL will not execute the RAND () function (which consumes CPU time), and this is to record rows for each row of records, and then sort them. Even if you use Limit 1, it will not help (because you want to sort ).

7.limit huge_num,offset

During paging, when the value of huge_num is large, there is a big performance problem in paging. I use 'limit 1020; 'to traverse 1000 rows, and then the first records will be discarded.

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

Idea 1: Use subqueries to rewrite the SQL statement based on the index paging and back-to-table method

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 2: Use... And...

# Not recommended, useful SELECT * FROM payment WHERE your al_id BETWEEN 100 AND 110 order by your al_id;

Idea 3: negotiate with the developer to record the order number of the last row of the previous page by adding the parameter last_page_record during the page turning process, and then query the records of the next page through the parameter range.

8. Use count (*) instead of count (id ); 9. Batch 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'); # change to insert into t (id, name) values (1, 'test1'), (2, 'test2 '), (3, 'test3 ');

10. order by reduces filesort sorting and returns ordered data directly through indexes.