Interview skills, how to use index to say database optimization ability, content from Java Web Lightweight Development Interview tutorial

Last week wrote an article, database aspects of interview skills, how to display their ability from the table, courtesy of the administrator to lift, put in the home, but also thanks to you love, two days in the harvest of nearly 770 clicks, also once entered the 48-hour hot list.

In order to thank the Administrator and everyone for their support, and then based on my interview experience, an original article on how the index to promote their own articles. It's also from the book I wrote.

Java Web Lightweight Development Interview tutorial.

If we need to recruit a senior Java programmer, on the one hand look at the age (undergraduate 3 years), specific to the database skills requirements, including the following three aspects:

First, whether the basic additions and deletions, stored procedures and other skills, will use some group by, having,distinct, exist, in, and other advanced points of the statement. This is generally not a problem, even a newly graduated college student or work experience within 2 years of junior programmer also no problem, that is, this is a senior programmer must, you will be should, not even less than the graduates.

Second, there is no design table experience, this aspect of the interview skills have been in the database of interview skills, how to display their ability from the table to say, the key point, you have to actual needs.

The third is also the key point, in the database optimization, whether you have relevant experience.

This is an open question, as the great gods know that they can be considered in many ways, from partitioning, to optimization of execution plans, and to senior programmers, my expectation is that you can at least say something about indexing. But according to my interview experience, few candidates (especially senior programmer candidates) can describe them in more depth.

The prologue said a lot, the following into the text.

Indexing is a necessary tool for database optimization and generally does not ask conceptual questions during an interview because everyone can find the answer from the textbook, so the following two questions are generally asked:

What is the price of the ① index? What scenarios do you need to build an index on? Or sometimes in turn ask, which scenarios are not recommended to build the index.

How can I use the index most efficiently after ② has built the index? Or, in turn, say a case in which you cannot effectively use the index you have built.

Structurally, the index is like a B-tree (also known as b* or plus), assuming that the student table has only the student ID and the name two columns, the student table has 1000 students, the study number from 1 to 1000, if indexed for the ID, the approximate structure as shown.

Of course, in the actual database system, the index is much more complex than this, but from this diagram, we can see the principle of how the index works.

After the index is built, if we want to find the student ID 111, then the database system will go index, from Figure 2.1 we can see, according to the root node guidance, will find the second layer from left to right second data block, and so on, will be in the fourth layer of the ID 111 physical address, and then directly from the hard disk to find data.

Conversely, if there is no index, the database system may have to be located from a large scope to locate each one, the efficiency is not so high.

The benefits of the index have already been seen, so in order to get this "high query efficiency" benefits, we have to pay what price?

1 The index takes up hard disk space, which is the cost of space.

2 Once the new data is inserted, the index needs to be re-built, which is the cost of the time.

With regard to index performance issues, I will wise, what is the size of the table you are indexing? Many people directly told me the table on thousands of, I or ask, index what price? Many of the answers are that the index is only the benefit of no harm, that is, can be built casually.

Let's take a detailed analysis (i.e. what you need to say during the interview):

Scene one, the size of the data table is small, thousands of rows, even if the index is not built, the return time of the query statement is not long, then the significance of the index is not big. Of course, if the thousands of rows, the index occupies less space, so in this case, at most, belong to the "cost-effective" is not high.

Scene two, a product table has millions of items of information, at the same time every day at a point, to update about 100,000 of the product information, and now use the WHERE statement to query a particular product (such as WHERE name = ' XXX ') is very slow. Indexing can be built to improve query efficiency, but it is time-consuming to rebuild the index every day when data is updated. This requires a comprehensive consideration, and even the ability to delete the index before updating, and then rebuilding it.

Scene three, as you can see, because in the data table ID values are not the same, so the index can play a relatively large role. Conversely, if a field has a high repetition rate, such as a gender field, or if most of the value of a field is empty (null), it is not recommended to Jianjian the word index.

Please remember that there must be a business need to build the index. For example, in a commodity table, we often have to query according to name, if there is no index, query speed is very slow, then we need to build an index. However, in project development, it is not necessary to index a number if it is not often queried according to the product number.

Finally, the index is to pay the price, nothing to do with the construction of play, but also on a table can not build too many indexes.

The following is how to use the index is built? After all, we have spent a lot of time and space to build the index, at least back to the book it?

If there are some bad SQL statements, then the index is built in white. Here are some concrete examples of the correct use of indexes.

① statement One: Select name from commodity table. The index is not used because there is no where statement.

② Statement Two: SELECT * from the commodity table where name = ' java book ', will use the index, if the project is often used to query the name, and the data volume of the commodity table is very large, and the name value of the repetition rate is not high, then the proposed index.

③ Statement Three: SELECT * from commodity table where name like ' java% ' This is a fuzzy query, will use the index, please remember that when using like for fuzzy query, if the first is a fuzzy match, such as where the name "%java", that The index is not run at query time. In other cases, the index can be used regardless of the number of% used, or the position of the%, as long as it does not appear in the first position.

The Student score table has two fields: Name and score. Now the index of the character Jianjian the integer type of the score.

① The first case, the index cannot be used when a numeric field encounters a non-equivalent operator. Like what:

Select name from student score table where results >95, once the greater than the symbol, you can not use the index, in order to use the index, we should change the SQL statement in the WHERE clause: where score in (96,97,98,99,100)

② The second case, if an lvalue operation is performed on an indexed field, the index cannot be used.

can use the notation of the index: select name from Student score table where score = 60

Cannot use index notation: select name from Student score table where score +40 = 100

③ The third case, if the index field is functional, then the index cannot be used.

such as the SQL statement: SELECT * from the commodity table where SUBSTR (name) = ' J ', we want to query the first letter of the product name is a record of J, but once the function for name is used, even if the name field has an index, it is not available.

With respect to indexes and, of course, bitmap indexes and composite indexes, if you want to apply for a more advanced position (such as 5 years of experience), then you can not stop here, but according to my interview experience, the above-mentioned about the index of the work experience 3 years of candidates are helpful.

In fact, I know that many programmers usually use the index, but do not know how to say that this is a disadvantage. For high-level programmers, if you're not good at it, how much higher is your ability than the beginner? For a novice programmer, if you have mastered it and can say it well in an interview, then you are in a dominant position compared to someone with the same ability.

Interview skills, how to use index to say database optimization ability, content from Java Web Lightweight Development Interview tutorial