How to design efficient and reasonable MYSQL query statement _mysql

MySQL query statements are used, but how to design efficient and reasonable MySQL query statements? Below teach you the reasonable design method of MySQL query statement, share to everybody study study.

1. Rational use of indexes
Index is an important data structure in database, and its basic aim is to improve the efficiency of query. Most of the database products now use IBM's first proposed ISAM index structure. The use of indexes is just right, with the following principles:

Indexes are established on columns that are frequently connected but not specified as foreign keys, while fields that are not frequently connected are automatically generated by the optimizer.

Index on a column that is frequently sorted or grouped (that is, a group by or order by operation).

A search is established on columns with more values that are often used in conditional expressions, and no index is established on columns with fewer values. For example, there are only two different values for "male" and "female" on the "Sex" column of the employee table, so there is no need to index. If indexing does not improve query efficiency, it can significantly reduce the speed of updates.

If there are multiple columns to be sorted, you can set up a composite index on those columns (compound index).

Use System Tools. If the Informix database has a Tbcheck tool, it can be checked on suspicious indexes. On some database servers, the index may fail or the read efficiency is reduced because of frequent manipulation, and if a query using an index slows down, try using the Tbcheck tool to check the integrity of the index and fix it if necessary. In addition, when a database table updates a large amount of data, deleting and rebuilding the index can increase the query speed.

2. Avoid or simplify sorting

You should simplify or avoid repeating sorting of large tables. The optimizer avoids sorting steps when it is possible to use indexes to automatically produce output in the appropriate order. Here are some of the factors that affect:

The index does not include one or several columns to be sorted;

The order of the columns in the group BY or ORDER BY clause is not the same as the index;

The sorted columns come from different tables.

In order to avoid unnecessary sorting, it is necessary to build the index correctly and consolidate the database table reasonably (although it may sometimes affect the normalization of the table, but it is worthwhile relative to the increase in efficiency). If sorting is unavoidable, try simplifying it, such as narrowing the range of sorted columns.

3. Eliminate sequential access to large table row data

In nested queries, sequential access to tables can have a fatal effect on query efficiency. For example, the use of sequential access strategy, a nested 3-level query, if each layer query 1000 rows, then the query will query 1 billion rows of data. The primary way to avoid this is to index the connected columns. For example, two tables: Student form (school number, name, age ...) and select the timetable (school number, course number, grade). If two tables are to be connected, the index should be indexed on the connection field "School Number".

You can also use a set of collections to avoid sequential access. Although there are indexes on all of the check columns, some forms of the WHERE clause force the optimizer to use sequential access. The following query forces a sequential operation on the Orders table:

Although indexes are built on Customer_num and Order_num, the optimizer uses sequential access paths to scan the entire table in the above statement. Because this statement retrieves a collection of detached rows, it should be changed to the following statement:

SELECT * FROM Orders WHERE customer_num=104 and order_num>1001 
UNION 

This allows the query to be processed using the index path.

4. Avoid related subqueries

A column's label appears in both the main query and the query in the WHERE clause, it is likely that the subquery must requery once the column value in the main query changes. The more nested the query, the lower the efficiency, so the subquery should be avoided as much as possible. If the subquery is unavoidable, filter out as many rows as possible in the subquery.

5. Regular expressions to avoid difficulties

Matches and like keywords support wildcard matching, technically called regular expressions. But this kind of match is especially time-consuming. For example:

SELECT * FROM customer WHERE zipcode like "98_ _ _" 

Even if an index is established on the ZipCode field, the sequential scan is used in this case. If you change the statement to select * from Customer WHERE zipcode > "98000", the index is used to query when executing the query, which obviously increases the speed significantly.

Also, avoid substrings that do not start. For example, the statement: SELECT * FROM Customer WHERE zipcode[2,3]> "80", which takes a non-start substring in the WHERE clause, and therefore does not use the index.

6. Use temporary tables to speed up queries

Sorting a subset of a table and creating a temporary table can sometimes speed up queries. It helps to avoid multiple sorting operations and, in other ways, simplifies the work of the optimizer. 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" 

If the query is to be executed multiple times and more than once, all unpaid customers can be found in a temporary file and sorted by the customer's 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 

Then query in the Temp table in the following way:

SELECT * from Cust_with_balance 

There are fewer rows in the temporary table than in the primary table, and the physical order is the required order, reducing disk I/O, so the query workload can be drastically reduced.

Note: Temporary table creation does not reflect changes to the primary table. When data is frequently modified in the primary table, be careful not to lose data.

7. Use sorting to replace non sequential access

Non-sequential disk access is the slowest operation, manifested in the movement of the disk access arm back and forth. The SQL statement hides this situation, making it easy for us to write queries that require access to a large number of non sequential pages when writing an application. In some cases, using the ability of database sorting to replace non sequential access can improve the query.

The above is to teach you how to design efficient and reasonable MySQL query statements method, I hope to help you learn.