MySQL Optimization 2

6. Use Exists,not exists clauses reasonably. As shown below:

1.SELECT SUM (T1. C1) from the T1 where (SELECT COUNT (*) from T2 where t2.c2=t1.c2>0) 2.SELECT SUM (T1. C1) from T1where EXISTS (SELECT * from T2 WHERE T2. C2=t1. C2) both produce the same result, but the latter is obviously more efficient than the former. Because the latter does not produce a large number of locked table scans or index scans. If you want to verify that there is a record in the table, do not use COUNT (*) as inefficient and waste server resources. Can be replaced with exists. such as: if (select COUNT (*) from table_name WHERE column_name = ' xxx ') can be written as: if EXISTS (SELECT * FROM table_name WHERE Column_nam e = ' xxx ')

7. Do not use in if you can use between

8. You can use distinct without GROUP by

9. Try not to use the SELECT INTO statement. The SELECT into statement causes the table to lock and prevent other users from accessing the table.

10. Force the query optimizer to use an index if necessary

SELECT * from T1 where nextprocess = 1 and ProcessID in (8,32,45) change to: SELECT * from T1 (INDEX = ix_processid) where NEXTPR ocess = 1 and ProcessID in (8,32,45) the query optimizer will forcibly use the index Ix_processid to execute the query.

14. Use UNION ALL to not use Union

UNION ALL does not execute the SELECT DISTINCT function, which reduces the amount of unnecessary resources that are used when crossing multiple different databases. Union is an interesting optimization method, and the union returns data from two unrelated tables, which means that no duplicate rows appear. It is also necessary to sort the data, and we know that sorting is very resource-intensive, especially for large tables. Union ALL can be much faster if you already know that your data does not include duplicate rows, or if you don't care if duplicate rows appear, in both cases it is more appropriate to use UNION all. In addition, you can use some methods in your application logic to avoid duplicate rows, so that the Union all and union return the same results, but the union all is not sorted.

15. field Data type optimization:

A. Avoid the use of NULL types: null for most databases require special handling, MySQL is no exception, it requires more code, more checks and special indexing logic, some developers do not realize that when creating a table null is the default value, but most of the time should use not NULL , or use a special value, such as 0,-1, as the default value. B. Use smaller fields whenever possible, MySQL reads data from disk into memory, then reads it with CPU cycles and disk I/O, which means smaller data types take up less space, read from disk or package to memory more efficiently, but don't be too persistent in reducing data types. There is no room for any future changes to the application. Modifying the table will require refactoring, which can indirectly lead to changes in the code, which is a headache, so you need to find a balance point. C. Priority use of fixed length type

do not place the index on the selected field, which is meaningless. The index should be placed reasonably on the statement of the conditional selection, such as Where,order by.

SELECT id,title,content,cat_id from article WHERE cat_id = 1;

the MySQL optimization of the ORDER BY statement: A. Index optimization for the order by + limit combination. If an SQL statement is like:

SELECT [Column1],[column2],.... From [TABLE] ORDER by [sort] LIMIT [offset],[limit];

This SQL statement optimization is relatively simple and can be indexed on the [Sort] field.

B. Index optimization for the WHERE + ORDER by + limit combination, shaped like:

SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [VALUE] ORDER by [sort] LIMIT [offset],[limit];

This statement, if you still use the first example of the method of indexing, although you can use the index, but not high efficiency. A more efficient approach is to create a federated index (Columnx,sort)

D.where+order by multiple fields +limit, such as:

SELECT * from [table] WHERE uid=1 ORDER x, y LIMIT 0, 10;

For this statement, you may be adding an index such as this: (X,y,uid). But actually the better effect is (uid,x,y). This is caused by the mechanism of MySQL processing sorting.

MySQL Optimization 2