Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the MySQL index interview test questions". In the daily operation, I believe many people have doubts about the MySQL index interview test questions. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubts about "what are the MySQL index interview test questions?" Next, please follow the editor to study!

1. The real question of the interview

Can you introduce the principle and data structure of MySQ index?

What's the difference between a b + tree and a b-tree?

What is the difference between a MySQL clustered index and a non-clustered index?

How are they stored respectively?

What are the principles for using MySQL indexes?

How to use MySQL composite index?

2. Psychological analysis of the interviewer

The database is a necessary question for engineers within 30k for an interview, and if you ask the database, it must be that mysql,N may have gone out for an interview with java engineers a year ago. The skill of oracle is a killer mace. Now no one says that meeting oracle is an additional item. Now they are all familiar with big data hadoop, hbase and other technologies.

3. Analysis of the interview questions

3.1 what is the data structure of the index

In fact, it allows you to talk about what the underlying data structure of the mysql index is, and maybe the site will let you draw the data structure of the index, and then ask you about the common principles of using the mysql index, or take a SQL to ask you, how do you build an index like this SQL?

What is the index? This problem is too basic, as we all know, mysql index is to use a data structure to organize a column of data, and then if you want to query based on that column of data, you don't have to scan the whole table, just find the value of that column according to that specific data structure, and then find the physical address of the corresponding row.

So to answer a question from the interviewer, how is the index of mysql implemented?

The answer is not a binary tree, nor a messy tree, but a b + tree. Many people will answer this, and then the interviewer will ask, so can you talk about the b + tree?

But before we talk about a b + tree, let's talk about what a b-tree is. From a data structure point of view, a b-tree has to meet the following conditions:

(1) d is a positive integer greater than 1, called the degree of B-Tree.

(2) h is a positive integer called the height of B-Tree.

(3) each non-leaf node consists of a key and n pointers, of which d 15, Zhang San, 22

The biggest feature of myisam is that the data file and the index file are separate, you see, first search in the index file, and then locate a line in the data file.

3.3Index of the innodb storage engine

All right, let's take a look at the index implementation of the innodb storage engine. The biggest difference from myisam is that the data file of innodb itself is an index file, which is the primary key key, and then the data of the leaf node is the row of that data. Let's use the index above to draw this index by hand on the spot, and give it to everyone to feel.

The innodb storage engine, which requires a primary key, builds a default index based on the primary key, called a clustered index. The data file of innodb is also an index file. The index storage structure is roughly as follows:

15recoverydataVOX07, complete row of data, (15, Zhang San, 22) 22 ~ # data: complete row of data, (22, Li Si, 30)

For this reason, the innodb table requires a primary key, but the myisam table does not require a primary key. Another is that under the innodb storage engine, if you create an index on a field that is not a primary key, then the value of the last leaf node is the value of the primary key, because you can use the value of the primary key to find the data again in the cluster index according to the primary key value, that is, the so-called return table, for example:

Select * from table where name = 'Zhang San'

First look in the name index to find the leaf node corresponding to Zhang San. The data of the leaf node is the primary key of that line, id=15. Then, according to id=15, to the clustered index in the data file (the index organized according to the primary key), locate the complete data of the id=15 line according to id=15.

So here is a reason, why not use the super-long string generated by UUID as the primary key under innodb? Because playing like this will cause all indexes to have that primary key value, and eventually the index will become too large, wasting a lot of disk space.

There is another reason, in the general innodb table, it is recommended to uniformly use auto_increment self-increment as the primary key value, because this can keep the clustered index to add records directly, if you use the primary key value that is not monotonously increasing, it may cause the b + tree to split and reorganize, which will be a waste of time.

3.4 rules for the use of indexes

Generally speaking, when job-hopping, the index must ask, how is the structure of the b + tree index generally stored? give a question: for this SQL, how should the index be established?

Select * from table where axiom 1 and bread2 and censor 3, do you know how you need to build an index to make sure that this SQL uses an index to query

All right, students, at this point, you should know the difference between myisam and innodb index, and also know what clustering index is. Hand-drawn on the spot, it should all be ok. Then let's talk about some of the most basic rules for using indexes.

In fact, the most basic, as a java programmer, you need to know the leftmost prefix matching principle, this thing is associated with a federated index (composite index), that is, most of the time, you do not index one field separately, but build a joint index for several indexes.

To give you an example, if you want to query a table of goods according to three dimensions: store, product, and creation time, you can create a federated index: shop_id, product_id, gmt_create.

Generally speaking, you have a product: shop_id, product_id, and gmt_create. Your SQL statement needs to be queried according to these three fields, so generally speaking, you don't just build three indexes. Generally speaking, you will set up a joint index for several fields to be queried.

The SQL written later in the java system must conform to the leftmost prefix matching principle. Make sure that all your sql can use this federated index and query it through the index.

Create index (shop_id,product_id,gmt_create)

(1) full column matching

That is to say, these three fields are used in one of your sql, which happens to be in the where condition, so you can definitely use this federated index:

Select * from product where shop_id=1 and product_id=1 and gmt_create='2018-01-01 10purl 0000'

(2) leftmost prefix matching

That is to say, if you happen to use the leftmost list or lists of the federated index in your sql, you can also use this index and just use the leftmost columns when looking in the index:

Select * from product where shop_id=1 and product_id=1, this is no problem, you can use this index

(3) the leftmost prefix matches, but one of the middle values does not match.

This means that if you use the first column and the third column of the federated index in your sql, it will look in the index according to the value of the first column. After searching, the result set will be scanned and filtered according to the third column. The third column is not searched by the index, but there is an extra filtering work, but the index can also be used, so it's okay, for example:

Select * from product where shop_id=1 and gmt_create='2018-01-01 10purl 0000'

That is, first look in the index according to shop_id=1 and find, for example, 100 rows of records, and then scan these 100 rows again to filter out the rows of gmt_create='2018-01-01 10 0000'.

We often encounter this situation in our online system, that is, according to the first one or two columns of the joint index, it is searched by the index, followed by a bunch of complex conditions, and functions, but as long as the index search results are filtered, according to online practice, when a single table has millions of data, the performance is also good, simple SQL is only a few ms, complex SQL is only a few hundred ms. It's acceptable.

(4) No leftmost prefix match

That's no good, that's funny. You can't use an index, so don't make this mistake.

Select * from product where product_id=1, this definitely won't work.

(5) prefix matching

That is to say, if you are not equivalent, such as =, > =, =, = 1 and product_id=1

Here is the query based on shop_id in the federated index.

(7) include function

If you use a function on a column, such as substring or something, then that column does not need an index

Select * from product where shop_id=1 and function (product_id) = 2

The above query is made in the federated index according to shop_id.

3.5 shortcomings of the index and precautions in use

Indexes have disadvantages, such as increasing disk consumption, because disk files are occupied, and frequently inserting and modifying indexes in high concurrency can lead to performance loss.

Our advice is to create as few indexes as possible, such as one or two indexes per table, two or three indexes, a dozen or so, and 20 indexes, which is fine in high concurrency scenarios.

Field, status,100 line, status for 2 values, 0 and 1

Do you think it still makes sense for you to build an index? It's almost as good as a full table scan.

Select * from table where status=1, which is equivalent to scanning 50 lines out of 100.

You have an id field, each id is different, set up an index, at this time its practical index effect is very good, for example, in order to locate the row of a certain id, in fact, through the index binary search, you can greatly reduce the amount of data to be scanned, the performance is very good

When creating an index, pay attention to a selective problem, select count (discount (col)) / count (*), you can look at the selectivity, that is, the proportion of the only value of this column in the total number of rows, if it is too low, it means that the values of this field are actually about the same, or many rows of this value are similar, then creating an index is almost meaningless, you search a value to locate a large row, and you have to re-scan.

That is, if the value of a field is almost different, the effect of using index is the best.

There is also a special index called prefix index, that is, a field is a string, which is very long. If you want to build an index, it is best to create a prefix for this string, such as the first 10 characters. If you want to create a prefix index with the first number of bits, just look at the prefix of different length. In general, the longer the prefix length, the higher the selective value.

All right, students, indexing can be talked about to this extent, or mastered to this extent, in fact, 80% of the work in ordinary Internet systems can be done, because in Internet systems, it is generally to minimize the complexity of SQL, make SQL very simple, and then match it with a very simple primary key index (clustered index) + a few federated indexes. You can override all SQL query requirements for a table. More complex business logic, let java code to implement on the ok.

You have to understand that SQL up to 95% is a single table addition, deletion, change and query, if you have some join and other logic, put it in the java code to do it. The simpler the SQL, the lower the cost for subsequent migration of sub-database, sub-table and read-write separation, and there is almost no need to modify SQL.

Let me tell you here, as far as Internet companies are concerned, use MySQL as the best online real-time storage, save data, and simply retrieve it; do not use MySQL to calculate, do not write join, sub-query, and function in MySQL, in high concurrency scenarios; calculate in java memory and do it by writing java code; you can make reasonable use of mysql transaction support.

At this point, the study of "what are the MySQL index interview questions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.