MySQL Optimization--index

Content from: https://yq.aliyun.com/articles/214494?utm_content=m_31338

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 where to query a specific product (such as wherename= ' XXX ') speed 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: selectnamefrom commodity table. The index is not used because there is no where statement.

② Statement two: Select*from commodity table Wherename= ' 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 wherenamelike ' java% ' This is a fuzzy query, you will use the index, please remember that when using like for fuzzy query, if the first is a fuzzy match, such as Wherenamelike '% Java ', then the index is not indexed 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:
Selectnamefrom Student score table where the score >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: Selectnamefrom student score table where scores =60

Cannot use the index notation: Selectnamefrom student score table where scores +40=100

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

For example, SQL statement: Select*from commodity table wheresubstr (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 there is an index on the name field, 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.

MySQL Optimization--index