The vast sea of millions, thank you for this second you see here. I hope my series of interview questions can help you! ‘!

May you keep your love and go to the mountains and seas in the coming days!

Three interview questions a day, achievement better self

Let’s talk more about MySQL!

1. Do you know index? So why do you use it

An index is a special file that contains a reference pointer to all the records in the table, and it occupies physical space.

An index is also a data structure. The index is usually implemented using B Tree or B+ Tree.

Why do we need to use indexes? Here’s an example:

We all know that Xinhua Dictionary is composed of a lot of Chinese characters, and these Chinese characters are just like the data in the table of our database, so how do we need to quickly locate a specific Chinese character in such a thick dictionary?

Of course, we will first look at the dictionary directory, through the directory in accordance with pinyin or radical way to quickly locate a small range, and then to a small range to find the data you want, so the dictionary directory here is the index.

So what are the benefits of using indexes?

  1. Speed up data retrieval and reduce database IO costs: The point of using indexes is to speed up the search by reducing the number of records that need to be queried in the table.
  2. Reduce the cost of sorting data, reduce THE CPU consumption: the index is faster because the data is sorted first. If the field needs to be sorted, the cost of sorting is reduced.

Of course, it also has some disadvantages, things always have two sides, we have to see which aspect brings us higher value!

  1. Storage footprint: An index is actually a table of primary keys and index fields, usually stored on disk as an index file.
  2. Slow down the update speed of the table: when the table data changes, the corresponding index also needs to change, thus slowing down the update speed. Otherwise, the index is invalid.

So when can we use the index?

  1. For the fields that frequently appear in WHERE, Order by and other fields that need to be queried frequently, we can add indexes to them to increase efficiency.
  2. Indexes are useful when we read too much and write too little, because maintaining an index and storing it consume resources!

Good ah! I knew you could do it. I didn’t know you could do it!

2. Do you know the principle and design of index?

Principle:

Indexes are used to quickly find records that have a specific value. If there is no index, queries are generally executed to traverse the entire table

The idea behind indexing is to turn unordered data into ordered queries:

  1. Sort the contents of indexed columns
  2. Sort the results to generate an inversion list
  3. Add a data address link to the inversion list contents
  4. In the query, the content of the table will be inverted, take out the data address link, so as to get the specific data

Design principles:

Indexes are designed to be faster and take up less space.

  1. These fields can be indexed if they occur frequently in a query condition after a WHERE clause or if they are specified by a join clause
  2. We can use a short index. If we need to build an index for some long strings, we can specify a prefix length, which can save the index space, and exclude the lines that do not match when querying, and then check whether the rest of the lines can match. That’s one of them
  3. Some columns with foreign keys must be indexed.

However, we do not recommend index creation in the following cases, because index creation also requires space consumption, and index maintenance.

  1. For some tables with small cardinals, indexes are not necessary. Because indexes are large for data, the efficiency of queries is faster.
  2. Do not over-index. For some fields that do not need to be queried, fields with many duplicate values, and fields that cannot effectively distinguish data are not suitable for indexing.
  3. Fields that update frequently cannot be created.
  4. Try to do extended index, for example, in the query a, B field, A has an index, can be changed to a, B joint index.
  5. Do not index data type fields defined as text, image, or bit.

Quite right, since know index knowledge, follow to ask a bit bai!

3. Can you talk about the data structure of the index

The data structure of the index depends on the specific storage engine implementation. The default index used by mysql storage engine Innodb is B+ index. For Hash indexes, if there are many single queries, you can use Hash indexes to achieve the highest speed. For most other scenarios, the B+Tree index is suitable.

  1. B + Tree index

    B + Tree is more than a balanced Tree, each leaf node from the root node to the height difference of no more than 1, and a pointer to a node has same rank and mutual connection, so for some range queries, suit to use B + Tree index, can use two-way pointer is going around fast, efficiency is higher, for regular queries, The search efficiency from the root node to the leaf node is basically the same, not much different.

  2. A Hash index

    Hash index is calculated by hash algorithm to the hash value, and then stored in a hash table, if is a single equivalent query, highest efficiency, because only to calculate a hash algorithm to get the corresponding hash value should be able to find it, but if more duplicates, may need to be in the subsequent to each query in the list, until the query to.

    For range queries, the hash algorithm is very unsupported because it evaluates the hash once and then cannot move left or right quickly.

So how do we choose?

If we are only doing an equivalent operation, we can use the Hash index, otherwise we are better off using the B+Tree index.

Nice guy! Today is over here, looking forward to your arrival tomorrow, I hope to continue to keep a surprise!

Note: If there are any mistakes and suggestions, please leave a message! If this article is helpful to you, I hope you will pay attention to it for three times. Thank you very much! You can also search the wechat prince Nezha public number private chat me, thank you guys!