SQL is a simple way to improve the efficiency, and SQL improves the efficiency
Use less in operations (very inefficient) and replace tables as much as possible
Select should specify the column, not * (* will read all data, while the specified column will only extract the involved column, reduce io)
Try to have where (to reduce the read volume), and try to have an index for the where Operation column (to speed up the query)
(Mysql indexes use the B-Tree data structure to store additional organizations for specific columns, accelerating the storage engine's record search speed, clustering indexes (the indexes and data are stored together) do not need to be returned to the table for data query ). Generally, you need to go back to the table and check the data again, which consumes extra disk IO .)
The primary key is a special unique index (excluding null), which is easier to use.
The composite index is well designed and is better than multiple-column indexes.Because the column order is very important when multiple column indexes are introduced in the where clause, to satisfy the leftmost prefix column, the leftmost prefix column may not necessarily have a reasonable index.
(Leftmost Prefix: all fields in the query condition must appear in the Multi-column index order from the left. The number of fields in the query condition must be less than or equal to the number of fields in the Multi-column index, the intermediate field cannot have a range query field (<, like, etc.). Such an SQL statement can use this multi-column index .)
Try not to use like. If you use like, do not start with a wildcard. Try to create a prefix index (a string before the wildcard position)
Try not to order
I am too lazy to write. I can organize an article online.
1. Avoid full table Scan
Full table scan:
-No Index
-The returned row has no restrictions (no Where clause)
-There are no restrictions on the primary index column (the first index column ).
-Restrictions on the primary index column are included in the expression.
-The condition for indexing the primary column is (not) null or! =
-The primary index column is subject to the like operation and the value is a bind variable or % hitting value.
2 only use selective Index
Index selectivity refers to the ratio of the number of different values in the index column to the number of records in the table. If the table contains 2000 records and the index column has 1980 different values, the index selectivity is 1980/2000 = 0.99. The closer it is to 1, the higher the efficiency of this index.
Column selectivity = number of different values/total number of rows/* The closer the value is to 1, the better */
Column order in composite indexes:
1. The most frequently used column in the conditions should be the primary column.
2. the most selective column (the clearest column) should be the primary column.
If 1 and 2 are inconsistent, you can consider creating multiple indexes.
Choose between a composite index AND multiple single indexes: consider the number of times the index is read AND the-EQUAL operation.
3. Manage multi-Table connections (Nested Loops, Merge Joins, and Hash Joins)
Optimize Join Operations
Merge Joins are set operations Nested Loops and Hash Joins are record operations that return the first batch of records quickly
Merge Joins operations are suitable for batch operations, large tables, and remote queries.
1 full table scan --> 2 sort --> 3 compare and merge performance overhead mainly in the first two steps
Applicable to full table scan, applicable to the Merge Joins operation (effective than the Nested Loops operation ).
Efficiency 1: I/O optimized, ORACLE multi-block read capability improved, and parallel query options used
Improve efficiency by 1: Increase the Sort_Area_Size value and use Sort Direct Writes to provide dedicated tablespace for temporary segments
4. Manage SQL statements containing views
The optimizer can execute SQL statements containing views in two ways:
-Execute the view first to complete all the result sets, and then use the remaining query conditions as filters to execute the query.
-Integrate view text into queries
Views containing group by clauses cannot be integrated into a large query.
Using union in a view does not prevent view SQL from being integrated into the query syntax.
5. Optimize subqueries
6. query using composite Keys/Star
7 properly index the Connect By Operation
8. Restrict access to remote tables
9. Manage access to extremely large tables
-Storing proximity records in a table stores the most frequently used columns in a range scan of the table. storing data in order helps with range scanning, especially for large tables.
-Avoid index scanning that is not helpful. When the returned data set is large, indexes occupy a large amount of data block cache in SGA and affect other users; full table Scan also benefits from ORACLE's multi-block read mechanism and the "consistent retrieval/each block" feature.
-Create a fully indexed table so that the access index can read more comprehensive data and create multiple indexes with only different primary Columns
-Create a hash Cluster
-Create a split table and view
-Use parallel options
10 use Union All instead of Union
Union all operations do not include Sort Unique operations. The first row of the union all operation responds quickly. In most cases, the operation does not need a temporary segment,
Views created by union all can be integrated into the query syntax in queries to improve efficiency.
11 avoid calling PL/SQL functions in SQL
12 Bind Variable usage management
Bind Variable and Execute using
Replace like: name | '%' with between: name and: name | char (225). Instead of full table scan, indexes are used.
13 return visit optimization process
After the data changes, review the Optimization
