fix:IP ASN详情sql性能优化

1 files changed, 7 insertions, 6 deletions

diff --git a/cn-admin/src/main/resources/mapper/knowledge/IpAsnBuiltInDao.xml b/cn-admin/src/main/resources/mapper/knowledge/IpAsnBuiltInDao.xml
index cad71b2..17663eb 100644
--- a/cn-admin/src/main/resources/mapper/knowledge/IpAsnBuiltInDao.xml
+++ b/cn-admin/src/main/resources/mapper/knowledge/IpAsnBuiltInDao.xml
@@ -27,9 +27,9 @@
     </select>
 
     <select id="queryEntityDetail" resultType="java.util.Map">
-        select id as id,asn as asn,organization as organization,knowledge_id as knowledgeId,is_valid as isValid from
-        cn_ip_asn_built_in
-        where
+        SELECT t.id as id,t.asn as asn,t.organization as organization,t.knowledge_id as knowledgeId,t.is_valid as isValid FROM
+        (SELECT * FROM cn_ip_asn_built_in
+        WHERE
         <choose>
             <when test="!isIpv6">
                 addr_type = 4
@@ -38,9 +38,10 @@
                 addr_type = 6
             </otherwise>
         </choose>
-        and INET6_ATON(#{resource}) BETWEEN start_ip_binary and end_ip_binary
-        ORDER BY id DESC
-        limit 1
+        AND start_ip_binary &lt;= INET6_ATON(#{resource})
+        ORDER BY start_ip_binary DESC LIMIT 35) AS t
+        WHERE end_ip_binary &gt;= INET6_ATON(#{resource})
+        ORDER BY id DESC LIMIT 1
     </select>
     
     <insert id="insertBatchForBinary">