Java interview Knowledge Point of the database (i)

The relevant knowledge of database, in the interview also often appeared, I think it is necessary to the relevant summary of this kind of knowledge.

1. Index

An index is a structure that sorts the values of one or more columns in a database table, and is a data structure that helps the database to efficiently get it.

Popular understanding: Indexing is the way to speed up the retrieval of data in a table. The index of the database is similar to the index of the book. In books, the index allows users to quickly find the information they need without having to read through the entire book, and in the database, the index allows the database program to quickly find the data in the table without having to scan the entire database to improve query speed.

Advantages of the index:

1) by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
2) can greatly speed up the retrieval of data, which is the main reason for creating indexes.
3) The connection between tables and tables can be accelerated, particularly in terms of the referential integrity of the data.
4) When using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
5) by using the index, you can improve the performance of the system by using the optimized hidden device during the query process.

Disadvantages of the index:

1) It takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
2) The index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.
3) When the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Specific reference:

60968248

Http://www.cnblogs.com/KissKnife/archive/2009/03/30/1425534.html

Https://www.cnblogs.com/aspwebchh/p/6652855.html

Https://www.cnblogs.com/tgycoder/p/5410057.html

2. Business

A transaction (Transaction) is the basic unit of concurrency control. The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work. A transaction is a unit of data consistency maintained by a database that maintains data consistency at the end of each transaction.

Its four basic characteristics are: atomicity , consistency , isolation and persistence .

The understanding of consistency :

Consistency means that the integrity constraints of a database are not compromised until the start of the transaction and after the transaction has ended. This means that database transactions do not break the integrity of relational data and the consistency of business logic.

Take the "transfer" example to illustrate:

Assuming that both user A and User B have a total of 5000, then no matter how much money is transferred between A and B, a few times, the sum of two users should be 5000 when the transaction ends, which is the consistency of the transaction.

Specific reference:

60968283

Https://www.cnblogs.com/hebao0514/p/5490698.html

Https://www.cnblogs.com/fjdingsd/p/5273008.html

3. Optimistic lock and pessimistic lock of database

Pessimistic lock: It is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked.

Optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation.

Specific reference:

Http://www.open-open.com/lib/view/open1452046967245.html

4.three main paradigms of database

Specific reference:

60968300

5. Database Connection Pool

A database connection pool (Connection pooling) is a program that establishes enough database connections when it starts and makes these connections a pool of connections that are dynamically applied, used, and released by the program to connect to the pool.

Specific reference:

Https://www.cnblogs.com/aspirant/p/6747238.html

53311165

by Shawn Chen 2018.4.8, late.

Java interview Knowledge Point of the database (i)