The first step (which can be omitted generally) is to enable the mysql service (which runs automatically at startup by default): control panel-management tools-service, in which the item named mysql is found, double-click it to check whether the service status is started or stopped. Set it as the first step (generally omitted ):
Enable the mysql Service (it runs automatically when it is started by default ):
Control Panel --> Administrative Tools --> service, find the item named mysql in it, and double-click it to see whether its service status is started or stopped, set it to start
Connect to mysql:
Enter mysql-h localhost-u root-p in the command line and press enter, or directly run the mysql Connection Tool and enter the password.
1. Write an SQL script. Assume the content is as follows:
Create database dearabao;
Use dearabao;
Create table niuzi (name varchar (20 ));
Save the script file. If I save it in the hello world Directory of drive F, the path of the file is F:/hello world/niuzi. SQL.
2. Run the SQL script in either of the following ways:
Method 1:
In the command line (Database not connected), enter mysql-h localhost-u root-p123456 <F:/hello world/niuzi. SQL (note that no quotation marks are required for the path !!) Press enter.
Method 2:
In the command line (the database is connected, and the prompt is mysql>), enter source F:/hello world/niuzi. SQL (note that no quotation marks are required for the path) or /. f:/hello world/niuzi. SQL (note that no quotation marks are required for the path). Press enter.
SQL writing specifications
1. Writing format
Sample Code:
Stored Procedure SQL document writing format example
Select
C. dealerCode,
Round (sum (c. submitSubletAmountDLR + c. submitPartsAmountDLR + c. submitLaborAmountDLR)/count (*), 2) as avg,
Decode (null, 'x', 'XX', 'cny ')
From (
Select
A. dealerCode,
A. submitSubletAmountDLR,
A. submitPartsAmountDLR,
A. submitLaborAmountDLR
From SRV_TWC_F
Where (to_char (a. ORIGSUBMITTIME, 'yyyy/mm/dd')> = 'date Range (start )'
And to_char (a. ORIGSUBMITTIME, 'yyyy/mm/dd') <= 'date Range (end )'
And nvl (a. deleteflag, '0') <> '1 ')
Union all
Select
B. dealerCode,
B. submitSubletAmountDLR,
B. submitPartsAmountDLR,
B. submitLaborAmountDLR
From SRV_TWCHistory_F B
Where (to_char (B. ORIGSUBMITTIME, 'yyyy/mm/dd')> = 'date Range (start )'
And to_char (B. ORIGSUBMITTIME, 'yyyy/mm/dd') <= 'date Range (end )'
And nvl (B. deleteflag, '0') <> '1 ')
) C
Group by c. dealerCode
Order by avg desc;
SQL string writing format example in Java source
StrSQL = "insert into Snd_FinanceHistory_Tb"
+ "(DEALERCODE ,"
+ "REQUESTSEQUECE ,"
+ "HANDLETIME ,"
+ "JOBFLAG ,"
+ "FRAMENO ,"
+ "INMONEY ,"
+ "REMAINMONEY ,"
+ "DELETEFLAG ,"
+ "UPDATECOUNT ,"
+ "CREUSER ,"
+ "CREDATE ,"
+ "HONORCHECKNO ,"
+ "SEQ )"
+ "Values ('" + draftInputDetail. dealerCode + "',"
+ "'" + DraftInputDetail. requestsequece + "',"
+ "Sysdate ,"
+ "'07 ',"
+ "'" + FrameNO + "',"
+ RequestMoney + ","
+ RemainMoney + ","
+ "'0 ',"
+ "0 ,"
+ "'" + DraftStruct. employeeCode + "',"
+ "Sysdate ,"
+ "'" + DraftInputDetail. honorCheckNo + "',"
+ Index + ")";
1). indent
For Stored Procedure files, indentation is 8 spaces
SQL strings in Java source cannot be indented, that is, each line of strings cannot start with a space.
2). line feed
1>. The Select/From/Where/Order by/Group by clause must be written in another row.
2> If the Select clause contains only one item, it must be written in the same way as the Select clause.
3> If the Select clause contains more than one item, each item occupies a single row, and 8 spaces are indented to the right based on the corresponding Select clause (Java source has no indentation)
4> If the content of the From clause has only one item, it must be written in the same way as the From clause.
5> If the content of the From clause contains more than one item, each item occupies a single row, and 8 spaces are indented to the right based on the corresponding From (Java source does not indent)
6>. If there are multiple conditions in the Where clause, each condition occupies one row AND starts with AND without indentation.
7>. (Update) each item in the Set clause occupies a single row without indentation.
8>. Each table field in the Insert clause occupies one row without indentation. Each value occupies one row without indentation.
9>. Empty rows are not allowed in the SQL text.
10>. in Java source, single quotes must be in the same line as the SQL clause, and the connector ("+") must be at the beginning of the line.
3). Space
1>. SQL Arithmetic Operators and logical operators must be separated by spaces.
2>. A space must be followed by a comma.
3>. There must be a space between keywords, reserved words, and left parentheses.
2. Not equal to uniform use "<>"
Oracle considers "! = "And" <> "are equivalent and both represent non-equal meanings. For unification, "<>" is not always used
3. Use the table alias
Database query. The table alias must be used.
4. Compatibility of SQL statements with table Field Extensions
When using Select * in Java source, it is strictly prohibited to get the query result in the form of getString (1). The format of getString ("field name") must be used.
When using Insert, you must specify the inserted field name.
5. Reduce the use of subqueries
In addition to poor readability, subqueries also affect SQL operation efficiency to some extent.
Please try to reduce the use of subqueries and use other methods with higher efficiency and better readability
6. Add indexes to improve query efficiency
Adding indexes appropriately can greatly increase the search speed.
See oracle SQL Performance Optimization Series
7. Special requirements for database table operations
This project also has the following special requirements for database table operations:
1). replace physical deletion with logical Deletion
Note: Currently, data in the database table is not physically deleted, and only logical deletion is required.
The deleteflag field is used as the deletion flag. deleteflag = '1' indicates that the record is deleted logically. Therefore, you must consider the deleteflag factor when querying data.
Standard query conditions for deleteflag: NVL (deleteflag, '0') <> '1'
2). Add the record Status field
Each table in the database has the following fields: DELETEFLAG, UPDATECOUNT, CREDATE, CREUSER, UPDATETIME, UPDATEUSER
Note that the following fields must be taken into account during benchmarking
When inserting a record, set DELETEFLAG = '0', UPDATECOUNT = 0, CREDATE = sysdate, CREUSER = logon User.
When querying a record, you must consider DELETEFLAG. If it is possible to update this record, you must obtain UPDATECOUNT for synchronization check.
When modifying a record, set UPDATETIME = sysdate, UPDATEUSER = logon User, UPDATECOUNT = (UPDATECOUNT + 1) mod 1000,
When deleting a record, you must set DELETEFLAG = '1'
3). History Table
Some tables in the database still have corresponding historical tables, such as srv_twc_f and srv_twchistory_f.
When querying data, in addition to the table where the data is located, you must also retrieve the corresponding historical table and perform Union (or Union All) on the results of the two tables)
8. Analyze SQL Performance Using execution plans
Explain plan is a good tool for analyzing SQL statements. It can analyze statements without executing SQL statements.
Through analysis, we can know how ORACLE connects to the table, how to scan the table (index scan or full table scan), and the index name used
Interpret the analysis results in the order from the inside out to the bottom.
The analysis results of the explain plan are arranged in indent format, and the most internal operations will be interpreted first. If the two operations are on the same layer, the operations with the minimum operation number will be executed first.
Currently, many third-party tools, such as PLSQL Developer and TOAD, provide extremely convenient explain plan tools.
PG needs to record the SQL query text added by itself into the log, and then analyze it in the EXPLAIN PLAN to minimize full table Scanning
Oracle SQL Performance Optimization Series
1. Select the most efficient table name sequence (only valid in the rule-based Optimizer)
The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) will be processed first.
When the FROM clause contains multiple tables, the table with the least number of records must be selected as the base table.
When ORACLE processes multiple tables, it uses sorting and merging to connect them.
First, scan the first table (the last table in the FROM clause) and sort the records;
Then scan the second table (the last second table in the FROM clause );
Finally, merge all records retrieved from the second table with the appropriate records in the first table.
For example:
Table TAB1 16,384 records
Table TAB2 5 Records
Select TAB2 as the base table (the best method)
Select count (*) from tab1, tab2 execution time 0.96 seconds
Select TAB2 as the base table (poor method)
Select count (*) from tab2, tab1 execution time 26.09 seconds
If more than three tables are connected for query, You need to select an intersection table as the base table. A cross table is the table referenced by other tables.
For example:
The EMP table describes the intersection between the LOCATION table and the CATEGORY table.
SELECT *
From location l,
Category c,
EMP E
Where e. EMP_NO BETWEEN 1000 AND 2000
And e. CAT_NO = C. CAT_NO
And e. LOCN = L. LOCN
It will be more efficient than the following SQL statements
SELECT *
From emp e,
Location l,
CATEGORY C
Where e. CAT_NO = C. CAT_NO
And e. LOCN = L. LOCN
And e. EMP_NO BETWEEN 1000 AND 2000
2. Connection sequence in the WHERE clause
ORACLE uses bottom-up sequence parsing WHERE clause
According to this principle, the join between tables must be written before other WHERE conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:
(Inefficient, execution time: 156.3 seconds)
SELECT *
FROM EMP E
Where sal> 50000
And job = 'manager'
AND 25 <(select count (*) from emp where mgr = E. EMPNO );
(Efficient, execution time: 10.6 seconds)
SELECT *
FROM EMP E
WHERE 25 <(select count (*) from emp where mgr = E. EMPNO)
And sal> 50000
And job = 'manager ';
3. Avoid '*' in the SELECT clause '*'
When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. Unfortunately, this is a very inefficient method.
In fact, ORACLE converts '*' into all column names in sequence during parsing.
This task is completed by querying the data dictionary, which means it will take more time
4. Reduce the number of database accesses
When each SQL statement is executed, ORACLE performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, and so on.
It can be seen that reducing the number of visits to the database can actually reduce the workload of ORACLE.
For example:
There are three ways to retrieve employees with employee numbers equal to 0342 or 0291
Method 1 (most inefficient)
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE emp_no. = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE emp_no. = 291;
Method 2 (low efficiency)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1 (342 );
FETCH C1 ...,...,...;
...
OPEN C1 (291 );
FETCH C1 ...,...,...;
...
CLOSE C1;
END;
Method 2 (efficient)
Select a. EMP_NAME, A. SALARY, A. GRADE,
B. EMP_NAME, B. SALARY, B. GRADE
From emp a, EMP B
Where a. EMP_NO = 342
And B. EMP_NO = 291;
5. Use the DECODE function to reduce processing time
Use the DECODE function to avoid repeated scan of the same record or join the same table.
For example:
Select count (*), SUM (SAL)
FROM EMP
WHERE DEPT_NO = '20140901'
And ename like 'Smith % ';
Select count (*), SUM (SAL)
FROM EMP
WHERE DEPT_NO = '20140901'
And ename like 'Smith % ';
You can use the DECODE function to efficiently get the same result.
Select count (DECODE (DEPT_NO, '20140901', 'x', NULL) D0020_COUNT,
COUNT (DECODE (DEPT_NO, '20140901', 'x', NULL) D0030_COUNT,
SUM (DECODE (DEPT_NO, '20140901', SAL, NULL) D0020_SAL,
SUM (DECODE (DEPT_NO, 0030, SAL, NULL) D0030_SAL
FROM EMP
Where ename like 'Smith % ';
'X' indicates any field
Similarly, the DECODE function can be used in the group by and order by clauses.
6. Replace HAVING clause with Where clause
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved. This processing requires sorting, statistics, and other operations.
If the WHERE clause can be used to limit the number of records, this overhead can be reduced.
For example:
Inefficient
Select region, AVG (LOG_SIZE)
FROM LOCATION
GROUP BY REGION
Having region! = 'Sydney'
And region! = 'Perth'
Efficient
Select region, AVG (LOG_SIZE)
FROM LOCATION
Where region! = 'Sydney'
And region! = 'Perth'
GROUP BY REGION
7. Reduce table queries
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.
For example:
Inefficient
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
Where version = 604)
AND DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
Where version = 604)
Efficient
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS
Where version = 604)
Example of updating multiple columns:
Inefficient
UPDATE EMP
SET EMP_CAT = (select max (CATEGORY)
FROM EMP_CATEGORIES ),
SAL_RANGE = (select max (SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
Efficient
UPDATE EMP
SET (EMP_CAT, SAL_RANGE) = (select max (CATEGORY), MAX (SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
8. Use the table Alias (Alias)
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column.
This reduces the parsing time and syntax errors caused by Column ambiguity.
9. Replace IN with EXISTS
In many basic table-based queries, to meet one condition, you often need to join another table.
In this case, using EXISTS (or not exists) usually improves the query efficiency.
Inefficient
SELECT *
From emp (basic table)
Where empno> 0
And deptno in (SELECT DEPTNO
FROM DEPT
Where loc = 'melb ')
Efficient
SELECT *
From emp (basic table)
Where empno> 0
And exists (SELECT 'x'
FROM DEPT
Where dept. DEPTNO = EMP. DEPTNO
And loc = 'melb ')
10. replace not in with NOT EXISTS
IN a subquery, the not in Clause executes an internal sorting and merging.
IN either case, not in is the most inefficient because it executes a full table traversal for the table IN the subquery.
To avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or NOT EXISTS
For example:
SELECT...
FROM EMP
WHERE DEPT_NO not in (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT = 'A ');
To improve efficiency, rewrite
Efficient
SELECT...
From emp a, DEPT B
Where a. DEPT_NO = B. DEPT (+)
And B. DEPT_NO IS NULL
And B. DEPT_CAT (+) = 'A'
Most efficient
SELECT...
FROM EMP E
Where not exists (SELECT 'x'
FROM DEPT D
Where d. DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A ');
11. Replace EXISTS with table join
Generally, table join is more efficient than EXISTS.
For example:
SELECT ENAME
FROM EMP E
Where exists (SELECT 'x'
FROM DEPT
WHERE DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A ');
More efficient
SELECT ENAME
From dept d, EMP E
Where e. DEPT_NO = D. DEPT_NO
AND DEPT_CAT = 'a ';
12. Replace DISTINCT with EXISTS
When you submit a query that contains multi-table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. Generally, you can consider replacing it with EXIST.
For example:
Inefficient
Select distinct DEPT_NO, DEPT_NAME
From dept d, EMP E
Where d. DEPT_NO = E. DEPT_NO
Efficient
SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
Where exists (SELECT 'x'
FROM EMP E
Where e. DEPT_NO = D. DEPT_NO );
EXISTS makes the query more rapid, because the core module of RDBMS will return results immediately after the subquery conditions are met.
13. Use indexes to improve efficiency
An index is a conceptual part of a table to improve the efficiency of data retrieval. In fact, ORACLE uses a complex self-balancing B-tree structure.
Data Query by index is usually faster than full table scan. When ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index
Similarly, using indexes to join multiple tables can also improve efficiency. Another advantage of using an index is that it provides uniqueness verification of the primary key.
Except for those LONG or long raw data types, You Can index almost all columns.
Using indexes in large tables is particularly effective. Of course, using indexes to scan small tables also improves efficiency.
Although index can improve the query efficiency, we must pay attention to its cost.
The index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified.
This means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O operations.
Because the index requires additional storage space and processing, the query response time will be slowed down by unnecessary indexes.
ORACLE has two access modes for indexes:
1) UNIQUE INDEX SCAN)
In most cases, the optimizer accesses the INDEX through the WHERE clause
For example:
The table LODGING has two indexes: the unique index LODGING_PK on the LODGING column and the non-unique index LODGING on the MANAGER column $ MANAGER
SELECT *
FROM LODGING
Where lodging = 'Rose HILL ';
Internally, the preceding SQL statement is executed in two steps:
First, the LODGING_PK index will be accessed by means of unique index scanning to obtain the corresponding ROWID. Then, the ROWID will be used to access the table and perform the next retrieval.
If the returned columns are included in the INDEX column, ORACLE will not perform the second step (access the table through ROWID)
Because the retrieval data is stored in the index, accessing the index alone can fully meet the query results.
2). index range query (index range scan)
Applicable to two situations:
1>. Search for a range based on the unique index
2>. Non-unique index-based retrieval
Example 1
SELECT LODGING
FROM LODGING
Where lodging like'm % ';
The WHERE clause condition includes a series of values. ORACLE queries LODGING_PK through index range query.
The index range query returns a set of values, which is less efficient than the unique index scan.
Example 2
SELECT LODGING
FROM LODGING
Where manager = 'bill Gates ';
This SQL statement is executed in two steps: LODGING MANAGER index range query (get all rowids that meet the condition records), access the table through ROWID to obtain the value of the LODGING column because of LODGING MANAGER index range query (get all rowids that meet the condition records), access the table through ROWID to obtain the value of the LODGING column. Because LODGINGMANAGER is a non-unique index, the database cannot perform a unique index scan on it.
In the WHERE clause, if the first character of the value corresponding to the index column starts with the WILDCARD (WILDCARD), the index will not be used
SELECT LODGING
FROM LODGING
Where manager like '% HANMAN ';
In this case, ORACLE uses full table Scan
14. Avoid using computation on index Columns
In the WHERE clause, if the index column is part of the function, the optimizer uses full table scan without using the index.
For example:
Inefficient
SELECT...
FROM DEPT
Where sal * 12> 25000;
Efficient
SELECT...
FROM DEPT
Where sal> 25000/12;
Do not process index columns in the search, such as TRIM, TO_DATE, and type conversion. If the index is damaged, full table scan will affect the SQL Execution efficiency.
15. Avoid using is null and is not null in the index column.
Avoid using any columns that can be empty in the index. ORACLE will not be able to use this index.
This record does not exist in the index if the column contains a null value;
For a composite index, if each column is empty, this record does not exist in the index. If at least one column is not empty, the record is stored in the index.
If the unique index is created in column A and column B of the table, and there is A record in the table, the value of A and B is (123, null ),
ORACLE will not accept insert of the next record with the same A and B values (123, null)
If all index columns are empty, ORACLE considers the entire key value to be null, but null cannot be equal to null. Therefore, you can insert 1000 records with the same key value, of course they are all empty!
Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable ORACLE.
Inefficient (index failure)
SELECT...
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL
16. Use UNION-ALL and UNION
When the SQL statement needs to UNION two query result sets, these two result sets will be merged in the UNION-ALL method, and then sorted before the final result is output.
If union all is used to replace UNION, this sorting is not necessary and the efficiency will be improved.
It should be noted that union all will repeatedly output the same records in the two result sets, so it is still necessary to analyze the feasibility of using union all from the business needs.
For more information about indexes, see:
1) if you retrieve more than 30% of the records in a table, using indexes will not significantly improve the efficiency.
2 ). in certain circumstances, using indexes may be slower than full table scanning, but this is a gap of the same order of magnitude. In general, using indexes is several or even several thousand times faster than full table scanning!
For more information, see oracle SQL Performance Optimization series.
17. Use PrepareStatement
To improve performance,
Use PreparedStatement. Note,
Only applicable to a few modules.
The method is as follows:
? PreparedStatement stmt
= Conn. prepareStatement ("select a from TABLE_A where B =? C =? ");
For (?? ){
???? Stmt. setInt (1, varB );
???? Stmt. setString (2, varC );
? ResultSet rst = stmt.exe cuteQuery ();
}