INT BETWEEN rowA AND rowB - Index Optimierung

S

stewe

Neuer Benutzer
Beiträge
4
Moin,

ich hab MariaDB 11.5.2 auf Debian 12 laufen. Die Tabelle um die es geht Memory Engine (wobei ich schon Aria/InnoDB usw durch hab).
Die Tabelle hat eine simple Struktur :

CREATE TABLE `asn_ip` (
`asn` int(10) unsigned NOT NULL,
`ip_from` bigint(10) unsigned NOT NULL UNIQUE,
`ip_to` bigint(10) unsigned NOT NULL,
`cou` smallint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`ip_from`),
KEY `asn` (`asn`),
KEY `between` (`ip_from`,`ip_to`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Die Tabelle fast ASN und die dazugehörigen Subnetze die mir im CIDR Format vorliegen 10.0.0.0/8. Ich kalkuliere daraus ip_from und ip_to. Also die erste und letzte IP im subnetz. Die IP rechne ich in ihre INT Notation um und habe somit einen UNSIGNED BIGINT. Was gibt es schöneres als Index. Das habe ich gemacht, weil ich eigentlich der Überzeugung war, damit MariaDB einen guten Index zu liefern um mir es nun zu erlauben, abfragen gegen die Datenbank zu machen alla :

SELECT * FROM asn_ip WHERE 2398796452 BETWEEN ip_from AND ip_to;

Also "Gib mir mir die IP Range und somit hab ich die ASN". Funktioniert, nur leider lässt mich der OPTIOMIZER im stich.

EXPLAIN SELECT * FROM asn_ip WHERE 2398796452 BETWEEN ip_from AND ip_to;

+------+-------------+--------+------+-----------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+-----------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | asn_ip | ALL | PRIMARY,ip_from,betwe | NULL | NULL | NULL | 359527 | Using where |
+------+-------------+--------+------+-----------------------+------+---------+------+--------+-------------+

Selbst wenn ich einen FORCE INDEX auf between setze, selbes ergebniss. Je nach position in der Tabelle macht der mir die überwiegende Zeit fast ganze Table Scans, weil er es zwar schaft indiziert ip_from den index zu nutzen aber danach ALLES heranzieht was eben größer ist als ip_from. Als gäbe es kein COMPOUND Index. Sorry den letzten Absatz bekomm ich nicht besser erklärt. Aber ich denke das PRoblem ist klar ?! Ich bin nicht damit verheiratet die IP_FROM/TO als BIGINT zu speichern. Wenn ich dann Ipset Listen damit generiere, gebe ich sie eh wieder in CIDR Notation aus.

Ich brauch nicht ma jmd der mir die Lösung bringt, ich würde nur gerne verstehen was mit dem INDEX nicht stimmt ?!
Vielen Dank im Vorraus für grübeln & lg
 
Werbung:
Vielleicht kann der Optimizer mit dem BETWEEN nicht umgehen?

Hast Du mal versucht es als >= .. and ... <= umzuschreiben?

Code: 
WHERE 2398796452 >= ip_from 
  and 2398796452 <= ip_to;
 
jup, genau das selbe. Ich hab mir überlegt dass ich jetzt einfach sage "SELECT * FROM asn_ip WHERE 2398796452 >= ip_from" und dann in ner subquery oder im prog selbst zu schauen, dass die IP nicht > ip_to ist.

Bringt nur nichts. Das Problem ist nichtmal der compound key, sondern "2398796452 >= ip_from". Das ergbeniss ist (in dem fall logischerweise) immer full scan. Die Azubis würden jetzt mal "LIMIT 1" hinten dran packen und sich abends selber in schalf loben. Aber LIMIT hat ja nur was mit ausgabe zu tun nicht mit der query selbst. Ich habe jetzt auch nochmal RTFM gemacht und dinge wie "SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause" um dem query optimizer klar zu machen dass es nach dem ersten match feierabend machen kann.

Fällt dir was ein alla "suche records und gib den ersten zurück" aber eben auch so, dass nicht hinten dran trotzdem alles hoch und runter gescanned wird. boha das problem ist derart simple dass es mich umso mehr nervt, dass ich schon x ideen dazu hatte und alle mit dem selben fail enden

​

 
stewe schrieb:
pardon, nicht falsch verstehen, aber was hat das mit meinem Problem zu tun ?
Zum Vergrößern anklicken....
weil man damit einfach schöner arbeiten kann.

Code: 
postgres=# create table stewe(ip inet primary key);
CREATE TABLE
postgres=# set enable_seqscan to false;
SET
postgres=# explain analyse select * from stewe where ip between '127.0.0.1' and '127.255.255.255';
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on stewe  (cost=4.22..14.39 rows=7 width=32) (actual time=0.018..0.019 rows=0 loops=1)
   Recheck Cond: ((ip >= '127.0.0.1'::inet) AND (ip <= '127.255.255.255'::inet))
   ->  Bitmap Index Scan on stewe_pkey  (cost=0.00..4.22 rows=7 width=0) (actual time=0.004..0.005 rows=0 loops=1)
         Index Cond: ((ip >= '127.0.0.1'::inet) AND (ip <= '127.255.255.255'::inet))
 Planning Time: 0.997 ms
 Execution Time: 0.088 ms
(6 Zeilen)

postgres=#
 
stewe schrieb:
pardon, nicht falsch verstehen, aber was hat das mit meinem Problem zu tun ?
Zum Vergrößern anklicken....
Je besser ein 'Datum' in einem Typ abgebildet ist, desto besser kann es in einem Index umgesetzt sein und von einem Optimizer berücksichtigt werden. Der Hinweis ist vollkommen berechtigt. Allein, die Frage ist, wie smart der Optimizer von MariaDB ist. Ich fürchte, nicht allzu sehr.
Eine IP Adresse taugt natürlich sehr gut, als pure Zahl gespeichert zu werden. Vermutlich sind die Effekte aus einem spezifischen IP Typ mehr auf der Komfort- und Lesbarkeitsseite als bei den Möglichkeiten der Indexoptimierung. Aber ich bin kein Indexspezialist. Mein Ansatz ist da immer "Standard" und wenn der Optimizer es nicht hinkriegt sowie die Notwendigkeit besteht, Handarbeit bzw. Hirnschmalz investieren.

Im Prinzip würde ich Deinem Ansatz folgen. Ein Compount Index wäre sicher nicht verkehrt. In dem Fall hier fällt auf,
dass der From Teil in 2 Indizes abgelegt ist und
dass MariaDB Optimizer trotz Einsatz von Hints (Force) stur bleibt

Ich bin auch kein mySQL MariaDB Spezialist, meine Befürchtung wäre, dass man an der Stelle nicht allzuviel erwarten darf. Was bspw. die Explain Ausgabe "possible_keys" bedeutet und was man tun muss, um den wirklich genutzten Key angezeigt zu bekommen, weiß ich nicht. Ich gehe allerdings davon aus, dass Du empirisch vorgehst und anhand Datenmenge und Laufzeit Deine Schlussfolgerungen ziehst (> kein Unterschied).
Ggf. könntest Du dazu mehr harte Fakten liefern.

Versuche aus der Hüfte:
- Compound Index auflösen und statt dessen nur die TO Spalte indizieren
- Wenn möglich, den Compound Index auch unique machen, notfalls unter Zuhilfenahme geeigneter Spalten, die das ermöglichen
- Primary Key auf die From Spalte weglassen (und durch Compound Unique Key aus voriger Zeile ersetzen, dieser müsste bei simplen Suchanfragen auf die From Spalte immer noch herangezogen werden und gut (genug) funktionieren)

Bleibt festzuhalten, dass es alles Bastelei ist, um einen optimalen Ausführungsplan zu erhalten und dass dies in irgendeiner Form Nebeneffekte haben wird / kann und daher/sowieso dokumentiert werden sollte. M.E. ist es das immer gleiche Thema bei mySQL / Maria, man ist sehr viel damit beschäftigt, workarounds für mysteriöse Effekte, wenn nicht sogar Bugs oder einfach fehlende Funktionalität zu schaffen.
 
Also MySQL ist nicht sonderlich mein Ding und leider bin ich zeitlich auch grade knapp, aber ich hatte vor einigen Jahren mal mit IPv4 in MSSQL rum gebastelt. Das ist nie wirklich produktiv gegangen, ich wollte damit mal meine Excel-Tapete ablösen :) Vielleicht kannst du dem ein Denkanstoß abgewinnen, ich kann nicht sagen ob irgendwas davon elegant oder performant umgesetzt ist...
Code: 
--Funktion
CREATE FUNCTION dbo.test_convert_ipv4(@ipv4cidr_decimal VARCHAR(18))
RETURNS @t TABLE (
    ipv4_binary VARCHAR(32),
    ipv4_binary_net VARCHAR(32),
    ipv4_binary_host VARCHAR(32),
    ipv4cidr_decimal_net SMALLINT
)
AS
BEGIN
    DECLARE    @ipv4cidr_decimal_net SMALLINT,
            @ipv4_decimal VARCHAR(15)

    IF        @ipv4cidr_decimal LIKE '%/%'
    BEGIN
        SET        @ipv4_decimal = left(@ipv4cidr_decimal,charindex('/',@ipv4cidr_decimal)-1) + '.'
        SET        @ipv4cidr_decimal_net = convert(SMALLINT,right(@ipv4cidr_decimal,charindex('/',reverse(@ipv4cidr_decimal))-1))
    END
    ELSE
    BEGIN
        SET        @ipv4_decimal = @ipv4cidr_decimal + '.'
    END;

    WITH cte(ipv4,octet,number) AS (
        SELECT    right(@ipv4_decimal,datalength(@ipv4_decimal)-charindex('.',@ipv4_decimal)),
                left(@ipv4_decimal,charindex('.',@ipv4_decimal)-1),
                1
        UNION ALL
        SELECT    right(ipv4,datalength(ipv4)-charindex('.',ipv4)),
                left(ipv4,charindex('.',ipv4)-1),
                number + 1
        FROM    cte
        WHERE    number <= 3
        )
    INSERT INTO @t(ipv4_binary)
    SELECT    stuff((    SELECT    ( CASE convert(INT,octet) & 128 WHEN 128 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 64 WHEN 64 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 32 WHEN 32 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 16 WHEN 16 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 8 WHEN 8 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 4 WHEN 4 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 2 WHEN 2 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 1 WHEN 1 THEN '1' ELSE '0' END )
                    FROM    cte
                    ORDER BY number
                    FOR XML PATH (''))
                    ,1,0,'')

    IF        @ipv4cidr_decimal_net IS NOT NULL
    BEGIN
        UPDATE    @t
        SET        ipv4_binary_net = left(ipv4_binary,@ipv4cidr_decimal_net),
                ipv4_binary_host = right(ipv4_binary,32-@ipv4cidr_decimal_net),
                ipv4cidr_decimal_net = @ipv4cidr_decimal_net
    END

    RETURN;
END;

--Testdaten und Abfrage
WITH Netzwerkbereichsdefintionen(IP_Bereich,Name) AS (
    SELECT    '192.168.0.0/24','Standardnetz' UNION ALL
    SELECT    '10.27.0.0/16','Servernetz' UNION ALL
    SELECT    '172.16.4.0/24','DMZ'
    ), Geräteliste(Gerätename,IP_Adresse) AS (
    SELECT    'Server1','192.168.0.16' UNION ALL
    SELECT    'Server2','10.27.0.12' UNION ALL
    SELECT    'Server3','172.16.4.4' UNION ALL
    SELECT    'Server4','10.27.0.13'
    ), Netzwerkbereichsdefintionen_erweitert(IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net) AS (
    SELECT    IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net
    FROM    Netzwerkbereichsdefintionen
    CROSS APPLY dbo.test_convert_ipv4(IP_Bereich)
    ), Geräteliste_erweitert(IP_Adresse,Gerätename,ipv4_binary) AS (
    SELECT    IP_Adresse,Gerätename,ipv4_binary
    FROM    Geräteliste
    CROSS APPLY dbo.test_convert_ipv4(IP_Adresse)
    )
SELECT    *
FROM     Netzwerkbereichsdefintionen_erweitert nets
LEFT JOIN Geräteliste_erweitert hosts
ON        nets.ipv4_binary_net = left(hosts.ipv4_binary,nets.ipv4cidr_decimal_net)
Später hatte ich mir dann noch einen interessanten Link notiert, da ging es um die Speicherung von IPv4 (habe ich aber nie eingebaut / berücksichtigt):
stackoverflow.com

Datatype for storing ip address in SQL Server

What datatype should I choose for storing an IP Address in a SQL Server? By selecting the right datatype would it be easy enough to filter by IP address then?
stackoverflow.com stackoverflow.com
 
Werbung:
danke für das ganze feedback. Wie ich ja schrieb, hab ich lediglich den Zusammenhing nicht verstanden als es um den Datatype ging. Türlich, wenn ich schon schreib ich will es 'gschait und nicht iregendwie ist der einwand bereichtigt. Numerische darstellung von IP benötige ich eben BigINT und für inet4 nur 4byte. Auch der Einwand das der optimzier vll besser mit um kann, wenn ich den datentyp nochmal eingrenze, auch plausibel. In diesem Fall, macht es aber keinen unterschied wie ob ich INT/BIGINT oder inet4.

akretschmer schrieb:
weil man damit einfach schöner arbeiten kann.

Code: 
postgres=# create table stewe(ip inet primary key);
CREATE TABLE
postgres=# set enable_seqscan to false;
SET
postgres=# explain analyse select * from stewe where ip between '127.0.0.1' and '127.255.255.255';
                                                    QUERY PLAN                                                    
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on stewe  (cost=4.22..14.39 rows=7 width=32) (actual time=0.018..0.019 rows=0 loops=1)
   Recheck Cond: ((ip >= '127.0.0.1'::inet) AND (ip <= '127.255.255.255'::inet))
   ->  Bitmap Index Scan on stewe_pkey  (cost=0.00..4.22 rows=7 width=0) (actual time=0.004..0.005 rows=0 loops=1)
         Index Cond: ((ip >= '127.0.0.1'::inet) AND (ip <= '127.255.255.255'::inet))
 Planning Time: 0.997 ms
 Execution Time: 0.088 ms
(6 Zeilen)

postgres=#
Zum Vergrößern anklicken....
ich hab es jetzt 5 mal gelesen und auch wenn ich postgres nur für PUBSUB dinge nutze, denke ich trotzdem dass ich die query richtig lese. Ich versuche eine einzelne IP also 1.1.1.1/32 in einer liste von subnetzen zu finden. diese abfrage sieht für mich danach aus, als würde ich subnetze mit start und end adresse suchen.

ukulele schrieb:
Also MySQL ist nicht sonderlich mein Ding und leider bin ich zeitlich auch grade knapp, aber ich hatte vor einigen Jahren mal mit IPv4 in MSSQL rum gebastelt. Das ist nie wirklich produktiv gegangen, ich wollte damit mal meine Excel-Tapete ablösen :) Vielleicht kannst du dem ein Denkanstoß abgewinnen, ich kann nicht sagen ob irgendwas davon elegant oder performant umgesetzt ist...
Code: 
--Funktion
CREATE FUNCTION dbo.test_convert_ipv4(@ipv4cidr_decimal VARCHAR(18))
RETURNS @t TABLE (
    ipv4_binary VARCHAR(32),
    ipv4_binary_net VARCHAR(32),
    ipv4_binary_host VARCHAR(32),
    ipv4cidr_decimal_net SMALLINT
)
AS
BEGIN
    DECLARE    @ipv4cidr_decimal_net SMALLINT,
            @ipv4_decimal VARCHAR(15)

    IF        @ipv4cidr_decimal LIKE '%/%'
    BEGIN
        SET        @ipv4_decimal = left(@ipv4cidr_decimal,charindex('/',@ipv4cidr_decimal)-1) + '.'
        SET        @ipv4cidr_decimal_net = convert(SMALLINT,right(@ipv4cidr_decimal,charindex('/',reverse(@ipv4cidr_decimal))-1))
    END
    ELSE
    BEGIN
        SET        @ipv4_decimal = @ipv4cidr_decimal + '.'
    END;

    WITH cte(ipv4,octet,number) AS (
        SELECT    right(@ipv4_decimal,datalength(@ipv4_decimal)-charindex('.',@ipv4_decimal)),
                left(@ipv4_decimal,charindex('.',@ipv4_decimal)-1),
                1
        UNION ALL
        SELECT    right(ipv4,datalength(ipv4)-charindex('.',ipv4)),
                left(ipv4,charindex('.',ipv4)-1),
                number + 1
        FROM    cte
        WHERE    number <= 3
        )
    INSERT INTO @t(ipv4_binary)
    SELECT    stuff((    SELECT    ( CASE convert(INT,octet) & 128 WHEN 128 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 64 WHEN 64 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 32 WHEN 32 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 16 WHEN 16 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 8 WHEN 8 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 4 WHEN 4 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 2 WHEN 2 THEN '1' ELSE '0' END ) +
                            ( CASE convert(INT,octet) & 1 WHEN 1 THEN '1' ELSE '0' END )
                    FROM    cte
                    ORDER BY number
                    FOR XML PATH (''))
                    ,1,0,'')

    IF        @ipv4cidr_decimal_net IS NOT NULL
    BEGIN
        UPDATE    @t
        SET        ipv4_binary_net = left(ipv4_binary,@ipv4cidr_decimal_net),
                ipv4_binary_host = right(ipv4_binary,32-@ipv4cidr_decimal_net),
                ipv4cidr_decimal_net = @ipv4cidr_decimal_net
    END

    RETURN;
END;

--Testdaten und Abfrage
WITH Netzwerkbereichsdefintionen(IP_Bereich,Name) AS (
    SELECT    '192.168.0.0/24','Standardnetz' UNION ALL
    SELECT    '10.27.0.0/16','Servernetz' UNION ALL
    SELECT    '172.16.4.0/24','DMZ'
    ), Geräteliste(Gerätename,IP_Adresse) AS (
    SELECT    'Server1','192.168.0.16' UNION ALL
    SELECT    'Server2','10.27.0.12' UNION ALL
    SELECT    'Server3','172.16.4.4' UNION ALL
    SELECT    'Server4','10.27.0.13'
    ), Netzwerkbereichsdefintionen_erweitert(IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net) AS (
    SELECT    IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net
    FROM    Netzwerkbereichsdefintionen
    CROSS APPLY dbo.test_convert_ipv4(IP_Bereich)
    ), Geräteliste_erweitert(IP_Adresse,Gerätename,ipv4_binary) AS (
    SELECT    IP_Adresse,Gerätename,ipv4_binary
    FROM    Geräteliste
    CROSS APPLY dbo.test_convert_ipv4(IP_Adresse)
    )
SELECT    *
FROM     Netzwerkbereichsdefintionen_erweitert nets
LEFT JOIN Geräteliste_erweitert hosts
ON        nets.ipv4_binary_net = left(hosts.ipv4_binary,nets.ipv4cidr_decimal_net)
Später hatte ich mir dann noch einen interessanten Link notiert, da ging es um die Speicherung von IPv4 (habe ich aber nie eingebaut / berücksichtigt):
stackoverflow.com

Datatype for storing ip address in SQL Server

What datatype should I choose for storing an IP Address in a SQL Server? By selecting the right datatype would it be easy enough to filter by IP address then?
stackoverflow.com stackoverflow.com
Zum Vergrößern anklicken....
auch danke für deinen ansatz.
 

Ähnliche Themen

H
MySQL Syntax Fehler
Antworten
5
Aufrufe
2K
dabadepdu
dabadepdu
A
Query verbessern da Index nicht genutzt
2
Antworten
15
Aufrufe
2K
akretschmer
akretschmer
G
Newbie - Einfacher Shop als Übung (7 Tabellen mit jeweils maximal 4 Spalten)
Antworten
11
Aufrufe
3K
Garzec
G
B
Problem mit MySQL Anweisung in PHP
Antworten
1
Aufrufe
1K
akretschmer
akretschmer
P
Probleme mit Stored Procedure nach Umzug von MySQL auf MariaDB
Antworten
2
Aufrufe
3K
pooleleven
P
Zurück
Oben