0
240
ViewsMySQL ENTRE consultas que no usan índice
Tengo datos geoip en una tabla, network_start_ip y network_end_ip son columnas varbinary(16) con el resultado de INET6_ATON(ip_start/end) como valores. Otras 2 columnas son latitud y longitud.
CREATE TABLE `ipblocks` ( `network_start_ip` varbinary(16) NOT NULL, `network_last_ip` varbinary(16) NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, KEY `network_start_ip` (`network_start_ip`), KEY `network_last_ip` (`network_last_ip`), KEY `idx_range` (`network_start_ip`,`network_last_ip`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8Como puede ver, he creado 3 índices para probar. ¿Por qué mi consulta (bastante simple)
SELECT latitude, longitude FROM ipblocks b WHERE INET6_ATON('82.207.219.33') BETWEEN b.network_start_ip AND b.network_last_ipno utilizar ninguno de estos índices?
La consulta tarda ~3 segundos, lo que es demasiado tiempo para usarla en producción.
3 answers
Answer question0
No funciona porque hay dos columnas a las que se hace referencia, y eso es muy difícil de optimizar. Suponiendo que no hay rangos de IP superpuestos, puede reestructurar la consulta como:
SELECT b.* FROM (SELECT b.* FROM ipblocks b WHERE b.network_start_ip <= INET6_ATON('82.207.219.33') ORDER BY b.network_start_ip DESC LIMIT 1 ) b WHERE INET6_ATON('82.207.219.33') <= network_last_ip; La consulta interna debe usar un índice en ipblocks(network_start_ip) . La consulta externa solo compara una fila, por lo que no necesita ningún índice.
O como:
SELECT b.* FROM (SELECT b.* FROM ipblocks b WHERE b.network_last_ip >= INET6_ATON('82.207.219.33') ORDER BY b.network_end_ip ASC LIMIT 1 ) b WHERE network_last_ip <= INET6_ATON('82.207.219.33'); Esto usaría un índice en (network_last_ip) . MySQL (y creo que MariaDB) hace un mejor trabajo con ordenaciones ascendentes que con ordenaciones descendentes.
0
Gracias a Gordon Linoff encontré la consulta óptima para mi pregunta.
SELECT b.* FROM (SELECT b.* FROM ipblocks b WHERE b.network_start_ip <= INET6_ATON('82.207.219.33') ORDER BY b.network_start_ip DESC LIMIT 1 ) b WHERE INET6_ATON('82.207.219.33') <= network_last_ip Ahora seleccionamos los bloques más pequeños que INET6_ATON(82.207.219.33) en la consulta interna, pero los ordenamos de forma descendente , lo que nos permite usar el LIMIT 1 nuevamente.
El tiempo de respuesta de la consulta ahora es de 0,002 a 0,004 segundos. ¡Estupendo!
0
¿Esta consulta le da resultados correctos? Sus IP de inicio/fin parecen estar almacenadas como una cadena binaria mientras busca una representación de enteros. Primero me aseguraría de que network_start_ip y network_last_ip sean campos INT sin firmar con la representación entera de las direcciones IP. Esto suponiendo que trabaja solo con IPv4:
CREATE TABLE ipblocks_int AS SELECT INET_ATON(network_start_ip) as network_start_ip, INET_ATON(network_last_ip) as network_last_ip, latitude, longitude FROM ipblocksLuego use (network_start_ip,network_last_ip) como clave principal.