Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge of "how to avoid full table scanning for fuzzy queries in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Fuzzy query efficiency is very low:

Reason: like itself is relatively inefficient, we should try to avoid using like; for query conditions such as like'%.%'(fully fuzzy), the index cannot be used, and the natural efficiency of full table scanning is very low; in addition, due to the matching algorithm, the larger the field length of the fuzzy query, the lower the efficiency of the fuzzy query.

Solution: first of all, try to avoid fuzzy query, if you must use fuzzy query because of business needs, then at least make sure not to use full fuzzy query, for right fuzzy query, that is, like '… %', index will be used; left fuzzy like

'%.' You can't use an index directly, but you can change it to like'by using the form of reverse + function index. %'; full fuzziness cannot be optimized, so consider using a search engine if necessary. In order to reduce the load of the database server, reduce the database fuzzy query as much as possible.

2. The select statement with is null in the query condition executes slowly

Reason: in Oracle 9i, a single index fails when querying field is null, resulting in a full table scan.

Solution: using NULL in SQL syntax will have a lot of trouble, preferably index columns are NOT NULL; for is null, you can set up a composite index, nvl (field, 0), after analyse on tables and indexes, is null query can re-enable index lookup, but the efficiency is not worth affirming; indexes are never used in is not null. Generally, tables with a large amount of data should not be queried by is null.

3. Select statements that are not equal to the operator (,! =) are used in the query condition to execute slowly.

Reason: in SQL, the unequal operator restricts the index, causing a full table scan, even if there is an index on the compared field

Solution: by changing the unequal operator to or, indexes can be used to avoid full table scans. For example, you can use an index by changing column'aaa', to column'aaa',.

4. Using a combined index, if there is no leading column in the query condition, the index will not work and will cause a full table scan; but starting from Oracle9i, the index skip scan feature has been introduced, which allows the optimizer to use a combined index, even if the leading column of the index does not appear in the WHERE clause. For example: create index skip1 on emp5 (job,empno); full index scan select count (*) from emp5 where empno=7900; index skip scan select / * + index (emp5 skip1) * / count (*) from emp5 where empno=7900; the former is a full table scan, and the latter uses a combined index.

5. Improper use of or statement will cause full table scan.

Reason: two conditions are compared in the where clause, one with an index and one without an index. Using or will cause a full table scan. For example, if there is an index on where indexer An and there is no index on B, then a full table scan will be restarted when comparing Broad Vista 2.

6, combined index, sorting should be sorted according to the order of the columns in the combined index, even if only one column in the index is to be sorted, otherwise the sorting performance will be poor. For example: create index skip1 on emp5 (job,empno,date); select job,empno from emp5 where job='manager'and empno='10' order by job,empno,date desc; is actually just querying the records that meet the job='manager'and empno='10' criteria and sorting them in descending order of date, but the performance of writing as order by date desc is poor.

7. Update statement, if you change only 1 or 2 fields, do not Update all fields, otherwise frequent calls will cause obvious performance consumption and a large number of logs.

8. For multiple tables with a large amount of data (a few hundred are considered large here), JOIN should be paged first and then JOIN, otherwise the logical reading will be very high and the performance will be very poor.

9. Select count (*) from table;, a count without any conditions, will cause full table scanning and does not have any business significance, so it must be eliminated.

10. The where condition of sql should be bound with variables, such as where column=:1, and should not be written as where column='aaa',. This will cause re-analysis every time it is executed, wasting CPU and memory resources.

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 databases, 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 value when creating tables, but most of the time you should use NOT NULL, or use a special value, such as 0Quest 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.