How to design efficient and reasonable MySQL Query statements _ MySQL

Reasonable MySQL Query statements can make our MySQL database more efficient, so how to design efficient and reasonable query statements becomes a problem facing us. All MySQL Query statements are in use, but how should we design efficient and reasonable MySQL Query statements? The following describes how to design MySQL Query statements.

1. use indexes reasonably
An index is an important data structure in a database. its fundamental goal is to improve query efficiency. Currently, most database products adopt the ISAM index structure first proposed by IBM. The index should be used properly. the usage principles are as follows:

● The Optimizer automatically generates an index for fields that are frequently connected but not specified as foreign keys.

● Index the columns that are frequently sorted or grouped (that is, group by or order by operations.

● Create a search for columns with different values that are frequently used in conditional expressions. do not create an index for columns with fewer values. For example, in the "gender" column of the employee table, there are only two different values: "male" and "female", so there is no need to create an index. If an index is created, the query efficiency is not improved, but the update speed is greatly reduced.

● If there are multiple columns to be sorted, you can create a compound index on these columns ).

● Use system tools. For example, the Informix database has a tbcheck tool that can be checked on suspicious indexes. On some database servers, the index may be invalid or the reading efficiency may be reduced due to frequent operations. if an index-based query slows down, you can use the tbcheck tool to check the index integrity, fix the issue if necessary. In addition, when a database table updates a large amount of data, deleting and re-indexing can increase the query speed.

2. avoid or simplify sorting

Duplicate sorting of large tables should be simplified or avoided. When indexes can be used to automatically generate output in the appropriate order, the optimizer avoids the sorting step. The following are some influencing factors:

● The index does not contain one or more columns to be sorted;

● The order of columns in the group by or order by clause is different from that of the index;

● Sort columns from different tables.

In order to avoid unnecessary sorting, we need to correctly add indexes and reasonably merge database tables (although it may affect table standardization sometimes, it is worthwhile to improve the efficiency ). If sorting is unavoidable, you should try to simplify it, such as narrowing the column range of sorting.

3. eliminates sequential access to data in large table rows

In nested queries, sequential access to a table may have a fatal impact on query efficiency. For example, the sequential access policy is used to create a nested layer-3 Query. if 1000 rows are queried at each layer, 1 billion rows of data are queried. The primary way to avoid this is to index the connected columns. For example, two tables: Student table (student ID, name, age ......) And course selection form (student ID, course number, score ). If you want to connect two tables, you need to create an index on the join Field "student ID.

Union can also be used to avoid sequential access. Although all check columns are indexed, some forms of where clauses force the optimizer to use sequential access. The following query forces sequential operations on the orders table:

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 

Although indexes are created on customer_num and order_num, The Optimizer still uses sequential access paths to scan the entire table in the preceding statement. Because this statement is used to retrieve the set of separated rows, it should be changed to the following statement:

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT * FROM orders WHERE order_num=1008 

In this way, you can use the index path to process queries.

4. avoid related subqueries

The label of a column appears in both the primary query and the where clause query. it is very likely that after the column value in the primary query changes, the subquery must perform a new query. The more nested query layers, the lower the efficiency. therefore, avoid subqueries as much as possible. If the subquery is unavoidable, filter as many rows as possible in the subquery.

5. avoid difficult regular expressions

MATCHES and LIKE keywords support wildcard matching, technically called regular expressions. However, this matching is especially time-consuming. For example:

SELECT * FROM customer WHERE zipcode LIKE “98_ _ _” 

Even if an index is created on the zipcode field, sequential scanning is used in this case. If you change the statement to SELECT * FROM customer WHERE zipcode> "98000", the query will be executed using the index, which will obviously increase the speed.

In addition, avoid non-starting substrings. For example, if SELECT * FROM customer WHERE zipcode [2, 3]> "80" is used in the where clause, non-starting substrings are used. Therefore, this statement does not use indexes.

6. use temporary tables to accelerate queries

Sort a subset of a table and create a temporary table, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. For example:

SELECT cust.name，rcVBles.balance，……other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 AND cust.postcode>“98000” ORDER BY cust.name 

If this query is executed multiple times but more than once, you can find all the unpaid customers in a temporary file and sort them by customer name:

SELECT cust.name，rcvbles.balance，……other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name INTO TEMP cust_with_balance 

Then, query the temporary table in the following way:

SELECT * FROM cust_with_balance WHERE postcode>“98000” 

The temporary table has fewer rows than the primary table, and the physical order is the required order, which reduces disk I/O, so the query workload can be greatly reduced.

Note: After a temporary table is created, the modification to the primary table is not reflected. Do not lose data when the data in the master table is frequently modified.

7. use sorting to replace non-sequential access

Non-sequential disk access is the slowest operation, as shown in the back-and-forth movement of the disk inventory arm. SQL statements hide this situation, making it easy for us to write a query that requires access to a large number of non-sequential pages when writing an application. In some cases, the database sorting capability can be used to replace non-sequential access to improve queries.

The above is to teach you how to design an efficient and reasonable MySQL Query statement method, hope to help you learn.