Shulou(Shulou.com)06/02 Report--

The main content of this article is to explain "mysql range query index", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "mysql range query go index" bar!

Several principles of indexing

1. Leftmost prefix matching principle, a very important principle

If you build an index in the order (a _ d, d does not need an index.

If you build the index (aformab _ dpenc), you can use it, and you can adjust the order of them at will (the order of the three when indexing)

Mysql matches to the right until it encounters a range query (>, 3 and d = 4

2. = and in can be out of order

For example, a = 1 and b = 2 and c = 3 index can be set up in any order

Mysql's query optimizer will help you optimize it into a form that the index can recognize.

3. Try to select a highly differentiated column as the index

The larger the proportion, the fewer records we scan, and the distinguishing degree of the unique key is 1.

And some status and gender fields may be 0 in front of big data.

Then some people may ask, is there any empirical value for this ratio?

It is difficult to determine this value for different scenarios. Generally, we need more than 0.1 for the fields that require join, that is, an average of 10 records are scanned.

The formula for distinguishing degree is count (distinct col) / count (*), which indicates the proportion of fields that are not duplicated.

4. Index columns cannot participate in the calculation, keeping the columns "clean"

So the statement should be written as create_time = unix_timestamp ('2014-05-29')

For example, an index cannot be used if from_unixtime (create_time) = '2014-05-29'.

The reason is simple: the values of the fields in the data table are stored in the b + tree, but when you retrieve them, you need to apply functions to all the elements to compare, which is obviously too expensive.

5. Expand the index as much as possible, do not create a new index.

For example, if you already have an index of an in the table, and now you want to add the index of (a), you only need to modify the original index.

At this point, I believe that you have a deeper understanding of "mysql range query index", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.