SQL to improve query efficiency

 

◆ Try not to include subqueries in the where clause;

Do not write the time query as follows: where to_char (dif_date, 'yyyy-mm-DD') = to_char ('2017-07-01 & prime ;, 'yyyy-mm-DD ');

◆ In the filter condition, the condition for filtering the maximum number of records must be placed at the end of the where clause;

The base table (driving table) written in the FROM clause will be first processed. When the FROM clause contains multiple tables, you must select a table with the least number of records as the base table. If more than three join queries exist, you need to select an intersection table as the base table, which is the table referenced by other tables;

◆ Bind variables

◆ Do not use OR in WHERE

◆ Replace IN with EXISTS and not exists instead of not in;

◆ Avoid calculation on the index column: where sal * 12> 25000;

◆ Replace OR with IN: WHERE LOC_ID = 10 OR LOC_ID = 15 OR LOC_ID = 20

◆ Avoid using is null and is not null in the index column;

◆ Always use the first column of the index;

◆ Replace UNION with UNION-ALL;

◆ Avoid changing the index column type: SELECT... From emp where empno = '2017 & prime;, due to implicit data type conversion, to_char (EMPNO) = '2017 & prime;, no index is used, generally, dynamic SQL statements are concatenated by strings;

◆ '! = 'No index is used;

◆ Optimize group;

◆ Avoid wildcards with the LIKE parameter. LIKE '4ye % 'uses indexes, but LIKE' % YE 'does not use indexes.

◆ Avoid using difficult regular expressions, such as select * from customer where zipcode like "98 ___". Even if an index is created on zipcode, in this case, sequential scanning is also used. If you change the statement to select * from customer where zipcode> "98000 & Prime;, the query will be executed using the index, which will obviously increase the speed;

◆ Complete SQL statements as clearly as possible and minimize database work. For example, when writing a SELECT statement, you must explicitly specify the table name of the queried field. Try not to use the SELECT * statement. Organize SQL statements according to database habits as much as possible.

16. update the clustered index data column should be avoided as much as possible, because the order of the clustered index data column is the physical storage order of the table records. Once the column value changes, the order of the entire table record will be adjusted, it will consume a considerable amount of resources. If the application system needs to frequently update the clustered index data column, consider whether to create the index as a clustered index.

17. Use numeric fields whenever possible. If fields containing only numerical information are not designed as numeric fields, this will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in the string one by one during query and connection processing, and only one comparison is required for the number type.

18. try to use varchar/nvarchar instead of char/nchar, because the first step is to reduce the storage space of the variable-length field, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

19. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not used.

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index ).

21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

22. Temporary tables are not unavailable. Using them appropriately can make some routines more effective. For example, when you need to reference large tables or a data set in common tables repeatedly. However, it is best to use the export table for one-time events.

23. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid creating a large number of logs to increase the speed. If the data volume is small, to ease system table resources, create table first and then insert.

24. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

25. Avoid using a cursor whenever possible, because the efficiency of the cursor is poor. If the cursor operation has more than 10 thousand rows of data, you should consider rewriting.

26. Before using the cursor-based or temporary table method, you should first find a set-based solution to solve the problem. The set-based method is generally more effective.

27. Like a temporary table, the cursor is not unavailable. Using a FAST_FORWARD cursor for a small dataset is usually better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. A routine that includes "sum" in the result set is usually faster than a cursor. If the development time permits, you can try both the cursor-based method and the set-based method to see which method works better.

28. set nocount on at the beginning of all stored procedures and triggers, and set nocount off at the end. You do not need to send the DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger.

29. Avoid large transaction operations as much as possible to improve the system concurrency capability.

I. Index introduction
1. Common index
The only task of a common INDEX (INDEX defined by the KEY or INDEX keyword) is to speed up data access. Therefore, you should create an index only for the data columns that most frequently appear in the query condition (WHEREcolumn =) or sort condition (ORDERBYcolumn. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.
2. Unique index
Normal indexes allow indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear twice or more times in the same "employee profile" data table.
If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: first, it simplifies MySQL's management of this index, and this index becomes more efficient. Second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.
3. Primary index
I have already repeatedly stressed that an index must be created for the primary key field. This index is called a "primary Index ". The only difference between a PRIMARY index and a UNIQUE index is that the keywords used by the former During definition are PRIMARY rather than UNIQUE.
4. Foreign key index
If a foreign key constraint is defined for a foreign key field, MySQL defines an internal index to help you manage and use foreign key constraints in the most efficient way.
5. Composite Index
Indexes can cover multiple data columns, such as INDEX (columnA, columnB) indexes. This index features that MySQL can selectively use such an index. If you only need to use an INDEX on the columnA data column for the query operation, you can use a composite INDEX (columnA, columnB ). However, this method is only applicable to the combination of data columns in the composite index. For example, INDEX (A, B, C) can be used as an INDEX of A or (A, B), but cannot be used as B, C or (B, C).
6. Index length
When defining indexes for CHAR and VARCHAR data columns, you can limit the index length to a given number of characters (this number must be less than the maximum number of characters allowed by this field ). The advantage of this is that you can generate an index file with a relatively small size and fast retrieval speed. In most applications, most of the string data in the database is based on a variety of names, and the index length is set to 10 ~ 15 characters is enough to narrow the search range to a few data records. When creating indexes for BLOB and TEXT data columns, you must limit the index length; mySQL allows a normal index on the text field of the maximum index of the full text index to accelerate the retrieval of the character string (that is, the character starting with the field content) at the beginning of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of, which is very complicated for MySQL. If the data volume to be processed is large, the response time will be very long.
This type of scenario is exactly where full-text indexing (full-textindex) can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data table, or you can use the following command to add a full-text index if necessary in the future:
ALTERTABLEtablenameADDFULLTEXT (column1, column2) with full-text indexes, you can use the SELECT query command to retrieve data records containing one or more given words. The basic syntax for such query commands is as follows:
SELECT * FROMtablename
WHEREMATCH (column1, column2) AGAINST ('word1 ', 'word2', 'word3 ')
The preceding command will query all the data records of word1, word2, and word3 in column1 AND column2 fields.
Note: InnoDB data tables do not support full-text indexing.

II. Index creation statement:

SQL code
1. Add the primary key (primary key index)
2. mysql> alter table 'Table _ name' add primary key ('column ')
. Add UNIQUE (UNIQUE index)
4. mysql> alter table 'Table _ name' add unique ('column ')
. Add index (common INDEX) mysql> alter table 'Table _ name' add index index_name ('column ')
6. 4. Add FULLTEXT (full-text index)
7. mysql> alter table 'Table _ name' add fulltext ('column ')
. Add multi-column indexes
9. mysql> alter table 'Table _ name' add index index_name ('column1 ', 'column2', 'column3 ')
3. Where to add indexes:
1. where condition column
2. Sort or group columns
3. The primary key itself is an index and does not need to be added again