Building rockets in the interview, screwing screws in the job, even though we all use basic SQL in the job, sorry, 90% of the interview is about principles, such as indexes, locks, logs, engines, etc.

In relational databases, an index is a single, physical storage structure that sorts the values of one or more columns in a database table. It is a set of values of one or more columns in a table and the corresponding logical pointer list to the data page in the table that physically identifies these values. Index function is equivalent to a book catalog, you can quickly find the required content according to the page number in the catalog.

If we want to find something specific in a book, we have to go page by page without a table of contents. Similarly, when an SQL statement such as the following is executed, how does the database find the corresponding record if there is no index?

SELECT * FROM student WHERE name='white'
Copy the code

The search engine can only scan each row of the entire table and compare the value of name to see if it is equal to “little white”. As we know, simple memory operations are fast, but fetching data from disk to memory is relatively slow. When there is a large amount of data in the table, the number of interactions between memory and disk increases greatly, which leads to low query efficiency.

The advantages of indexes are as follows:

Creating a unique index ensures the uniqueness of each row in a database table.

The main reason for using indexes is that they can greatly speed up the query of data.

Table to table joins can be accelerated in terms of achieving referential integrity of data.

Grouping and sorting clauses can also significantly reduce the grouping and sorting time in queries when using data queries.

Since indexes are so good, should we use them to our heart’s content? No, indexes have obvious advantages, but they also have corresponding disadvantages:

Creating and maintaining index groups takes time and increases as the volume of data increases.

Indexes occupy disk space. In addition to the data space occupied by data tables, each index also occupies a certain amount of physical space.

Indexes are maintained dynamically as data in a table is added, deleted, or modified, which slows down data maintenance.

Therefore, when using an index, it is necessary to consider the advantages and disadvantages of the index and find the most favorable balance.

Pick up Java interview questions