Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you sql optimization on how to avoid full table scanning. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

To optimize queries, you should avoid full table scans as much as possible, and first consider indexing on the columns involved in where and order by:

. Try the following techniques to prevent the optimizer from mistaking the table scan:

Update the keyword distribution for the scanned table using ANALYZE TABLE tbl_name.

Use FORCE INDEX on the scanned table to tell MySQL that it will be very time-consuming compared to scanning with the given index table.

SELECT * FROM T1, T2 FORCE INDEX (index_for_column)

WHERE t1.col_name=t2.col_name

Start mysqld with the-- max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that keyword scans will not exceed 1000 keyword searches.

1. Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan

Such as:

Select id from t where num is null

NULL requires special handling for most, and MySQL is no exception. It requires more code, more checking, and special indexing logic. Some developers are completely unaware that NULL is the default when creating tables, but most of the time you should use NOT NULL, or use a special value, such as 0fui 1 as the default.

You cannot use null as an index, and any column that contains a null value will not be included in the index. Even if the index has multiple columns, as long as one of these columns contains null, the column is excluded from the index. That is, if a column has a null value, even indexing that column will not improve performance. Any statement optimizer that uses is null or is not null in the where clause does not allow the use of indexes.

In this example, you can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

two。 The use of the! = or operator in the where clause should be avoided as much as possible, otherwise the engine will abandon the use of indexes and perform a full table scan.

MySQL uses indexes only for the following operators: =, BETWEEN,IN, and sometimes LIKE. An index can be used in a LIKE operation when another Operand does not start with a wildcard character (% or _). For example, the query "SELECT id FROM t WHERE col LIKE 'Mich%';" will use indexes, but the query "SELECT id FROM t WHERE col LIKE'% ike';" will not use indexes.

3. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan

Such as:

Select id from t where num=10 or num=20

You can query it like this: select id from t where num=10 union all select id from t where num=20

4. In and not in should also be used with caution, otherwise it will lead to full table scan.

Such as:

Select id from t where num in (1, 2, 3)

For consecutive values, use between instead of in:

Select id from t where num between 1 and 3

5. The following query will also cause a full table scan:

Select id from t where name like'% abc%' or

Select id from t where name like'% abc' or

To improve efficiency, consider full-text retrieval.

The index is used by select id from t where name like 'abc%'.

7. Using parameters in the where clause also results in a full table scan. Because SQL parses local variables only at run time, the optimizer cannot delay the choice of the access plan to the runtime; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and cannot be used as an input to the index selection. A full table scan will be performed as follows:

Select id from t where num=@num

You can force the query to use the index: select id from t with (index (index name)) where num=@num instead

8. Expression manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans.

Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

9. Functional manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans.

Such as:

Select id from t where substring (name,1,3) = 'abc'--name

The id generated by select id from t where datediff (day,createdate,'2005-11-30') = 0Murray 2005-11-30' should be changed to:

Select id from t where name like 'abc%'

Select id from t where createdate > = '2005-11-30' and createdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.