Common face questions in database

Database transactions:

is a series of database operations, which is the basic logical unit of database application. Nature of transactions: Atomicity,
1) atomicity. That is, the transaction is either fully executed or not executed at all.
2) Consistency or can be strung. Transaction execution causes the database to transition from a correct state to another correct state
3) Isolation. The transaction is not allowed to make any changes to the data to any other transaction until the transaction is committed correctly.
4) Durability. After the transaction is committed correctly, the result is persisted in the database, and the transaction results are saved even if there are other failures after the transaction commits.

--------------------------------------------------------------------------------------------------------------- --------------------

Database inline, Outreach:

Outreach: Left Join/right join

-------------------------------------------------
Table1 | Table2
-------------------------------------------------
ID Name | ID Score
1 Lee | 1 90
2 Zhang | 2 100
4 Wang | 3 70
-------------------------------------------------

eg.

Left Join:select *from table1 left joins table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
------------------------------

Right Join:select *from table1 right join table2 on table1.id = table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
NULL NULL 3 70
------------------------------

Full Join:select *from table1 full join table2 on table2.id = table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
4 Wang Null NULL
NULL NULL 3 70
------------------------------

INNER JOIN:

Select *from table1 join table2 on Table1.id=table2.id

-------------Results-------------
ID Name ID Score
------------------------------
1 Lee 1 90
2 Zhang 2 100
------------------------------

--------------------------------------------------------------------------------------------------------------- --------------------

Common performance optimization scenarios for databases:

~ To optimize the query to avoid full table scanning, first consider establishing an index on the columns involved in the Where and order by.

~ Avoid null-valued fields in the WHERE clause, which will cause the engine to discard full-table scans using the index, such as:

~ Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

~ should try to avoid using or in the WHERE clause to join conditions, if a field has an index, a field is not indexed, will cause the engine to abandon the use of the index for full table scan

~in and not in should also be used with caution, otherwise it will result in a full table scan

~ The following query will also cause a full table scan:

Like '%abc% ' 

To be more efficient, consider full-text indexing.

~ You should try to avoid function operations on fields in the WHERE clause, which will cause the engine to discard full table scans using the index. such as:

Select IDFrom TwhereSUBSTRING (name,  1 ,   3 ) = ' abc '-–name starts with ABC Idselect ID from t Span style= "COLOR: #0000ff" >where datediff (day, CreateDate, '     

should read:

'abc%'2005-11-30 '2005-12-1'      

~ Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

~ When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

~ for a number of large data (here hundreds of even larger) table join, to first paged and then join, otherwise the logical reading will be very high, poor performance.

~ Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

~ When you create a new temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create table and insert to mitigate the resources of the system tables.

~ try to avoid large transaction operations and improve the system concurrency capability.

Common face questions in database