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

I previously wrote an analysis of the problem of IP address location in Oracle, and finally gave rise to a series of problems. At that time, the problem was urgent and serious. I grabbed the source of the 10053 incident, came up with a solution, and was complacent. As a result, all the solutions in the industry were generally like this, and my heart was half cold.

I was always hoping to find something different to interpret this kind of problem, and I got some ideas from MySQL one day.

Let me first analyze the previous problems and some gains.

The requirement is to enter an IP, which can query the IP section from a data dictionary table according to IP and return the corresponding area of IP. This is a seemingly simple problem of IP address location.

In terms of system load, the load of CPU is high, and one of the great costs is the translation and mapping between IP addresses and numbers (IP addresses are converted into numbers).

The Buffer Gets index is extremely high, and this part is actually related to the lookup effect of the entire statement. If no matching data is found, more blocks will be scanned. An immediate effect of this part is to use rownum to truncate, on this basis, chat with Oracle friends, in fact, there are some improvement measures, this part can be used for reference for limit optimization, so put aside for a while.

From an index point of view, the Range Scan approach will always have advantages and disadvantages, it is impossible to combine it at the same time to achieve an optimal effect, if that database is the case, it can only be said that some back-to-table data processing Oracle implicit (such as the use of rowid) has been done, while MySQL may need to be handled separately.

This is the end of the question. I would like to discuss this issue again today because I want to start with a few basic questions to talk about the advantages of MySQL in this respect. Yes, it is the advantage over Oracle.

First of all, let's talk about the design of the table structure. If you are in Oracle, the address information designed at that time is as follows:

COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE

1 IP_ID NUMBER (1010) 22 N

2 IP_LEFT_LINE VARCHAR2 (15) 15 N

3 IP_RIGHT_LINE VARCHAR2 (15) 15 N

4 IP2NUM_LEFT_LINE NUMBER (1010) 22 N

5 IP2NUM_RIGHT_LINE NUMBER (1010) 22 N

6 COUNTRY VARCHAR2 (20) 20 Y

7 PROVINCE VARCHAR2 (20) 20 Y

8 CAPITAL VARCHAR2 (20) 20 Y

Both the IP address and the translated IP address are persisted, so the logic of the query is relatively awkward.

For example, B1 is the incoming IP address, that is, a string, which is first converted to a number, and then Range Scan.

SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL

FROM SWD_IP2COUNTY

WHERE STRIPTOINT (: B1) BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE

If it is MySQL, what points need to be considered?

The first consideration is the data type. The IP address is a string. Should we consider using the varchar type or char?

Suppose an IP address is 10.127.133.199, the length of the string is 14 bits, and the maximum setting is 3 "4" 3 "15 bits, which is the first point.

If we store an IP, it means that the work is not finished, and we still need to convert it, so we might as well convert it directly to a numerical value, so to sum up, we actually achieve this requirement, from a simplified point of view, we do not need a character type, but a numerical type.

So the problem comes, numeric data types are actually very rich, which is very different from Oracle, many developers in Oracle, DBA are lazy, or Oracle has done this kind of adaptation, numerical accuracy does not need to be considered more, length does not need to be treated differently, directly a number type, want to adjust the accuracy, directly on this basis to change, such as number (10jue 3), you can define the length and accuracy. MySQL is relatively light in this respect, such as smallint that supports tiny int,32767 within 0-128, and each data type is very fine.

So the arrogance in Oracle is extensive here, and it must be treated differently.

Because we planned to use numeric types, we finally chose int (11), leaving no extra value because we considered allocating as much as possible on demand from a design perspective.

Create table ip_range (ip int (11))

Query OK, 0 rows affected (0.01 sec)

We insert two rows of values:

> insert into ip_range values (inet_aton ('127.0.0.1')), (inet_aton ('192.168.1.1'))

ERROR 1264 (22003): Out of range value for column 'ip' at row 2 turns out to be overflowing and SQL_Mode is in strict mode.

Well, it looks like we're too optimistic. One by one.

> insert into ip_range values (inet_aton ('127.0.0.1'))

Query OK, 1 row affected (0.00 sec)

It turns out that's the problem here:

> insert into ip_range values (inet_aton ('192.168.1.1'))

ERROR 1264 (22003): Out of range value for column 'ip' at row 1

This is because the numerical types of int are actually divided into signed and unsigned, with intervals of 2147483647 and 4294967295 respectively, so we only need to consider the unsigned situation and modify the field type for IP addresses.

> alter table ip_range modify ip int (11) unsigned

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0 and then insert it again.

> insert into ip_range values (inet_aton ('192.168.1.1'))

Query OK, 1 row affected (0.00 sec) need to be mentioned here, that is, for the translation of IP addresses, MySQL has provided this translation method, which can be converted into each other. They are inet_ntoa (numerical value to IP) and inete_aton (IP to numerical value).

> select (inet_ntoa (ip)) from ip_range

+-+

| | (inet_ntoa (ip)) |

+-+

| | 127.0.0.1 | |

| | 192.168.1.1 | |

+-+

2 rows in set (0.00 sec) with these groundwork, combined with index information, it is not a big problem to achieve this requirement.

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.