How to optimize MySQL s like keyword

Oracle's parser processes the table names in the From clause in right-to-left order, the table written at the end of the From clause (the driving table) will be processed first, and if the FROM clause contains multiple tables, you must select the table with the fewest records as the base table. If you have more than 3 table join queries, you need to select an intersection table as the underlying table, which is the table that is referenced by other tables.

2) The order of concatenation in the where clause:

Oracle parses the where clause in bottom-up order, according to which connections between tables must be written before other where conditions, and conditions that can filter out the maximum number of records must be written at the end of the where clause.

3) Avoid using ' in the select clause

In the process of parsing, oracle will add the'*'Convert all the column names in turn, which is done by querying the data dictionary, which means more time.

4) Reduce the number of times you access the database:

Oracle does a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, etc.;

5) Re-setting the arraysize parameter in sql*plus, sql*forms and pro*c can increase the amount of retrieved data for each database access, and the recommended value is 200

Use the decode function to avoid repeatedly scanning the same record or joining the same table repeatedly.

7) Integrate simple, non-associated database access:

Don't add it to the front, adding it will cause the whole table to be scanned.

The following is an introduction to the usage of MySqlLike

Like usage in MySQL statements: Common usage: used with % to represent a wildcard of one or more characters, for example, query data that starts with a large in the name field

If you use a wildcard that represents only one character and change the % in the above query statement to %, you will find that only one piece of data can be queried.

Using like fuzzy query will cause index invalidation, and there will be performance problems when the amount of data is large, try to minimize fuzzy query starting with % or .

Data analysis is a series of methods for analyzing and mining data, while big data usually refers to a series of technologies for the storage and management of massive data, and data is the basis of data analysis. Therefore, it can be said that big data is the cornerstone of big data analysis, but the two are not the same. Big data technology provides a data source for big data analysis, and data analysis provides a method to refine the value behind big data.

Big data analysis refers to the data analysis of a certain level of data, which means that the original data management and storage tools are no longer applicable, for example, when the number of data surplus sheds increases from 100,000 to 10 million, the traditional Excel cannot be managed, and it is necessary to use big data flat Sitai technology for storage and management. This kind of data analysis combined with a big data platform is big data analysis.

The syntax format of the like statement is: select*from, table name, where field name, and ike corresponding value (substring), which is mainly for character fields, and its function is to search for the corresponding substring in a character field column. % Any string containing zero or more characters:

All strings that begin with the letter mc (e.g. mcbadden) will be searched. like'%inger'All strings ending with the letter inger (e.g., ringer, stringer) will be searched. like'%en%'Destroy the chain by searching for all strings that contain the letter en anywhere (e.g. bennet, green, mcbadde.

Common MySQL subquery statements:

1. Use in's subquery:

Form: where field in

What it means: Here, the column subquery may be "multiple values", although the output of the query is "one column", but we want to understand that it is a "list of multiple values", which is equivalent to: where field in (value 1, good dry value 2,..

For example: where age in (18, 28, 38); Table Chang orange shows age as any one of them.

2. Use any's subquery:

Form: where field Comparison Operator Stockings Regimentany

Meaning: It means that the value of the field is satisfied if one of the multiple values queried by the column subquery satisfies the comparison operator.

3. Use some's subquery:

Form: where field comparison operator some (column subquery).

Meaning: Same as any. i.e. some is a synonym for any.

4. Use the all subquery:

Form: where field Comparison operator all (column subquery).

Meaning: The value of the field must meet the operator for all the result data of the column subquery to be considered eligible.

5. Use exists subquery:

Form: where exists (any subquery).

What it means: If the subquery has data results, the result of exists() is true. If the subquery has no data results, the result of exists() is false.