使用Hibernate查找有100万条记录的表

Question

我的数据库表(geo查找)有7列，其中2列构成<复合-id>。现在，当我使用前2色查找一个值时，需要花费12-14秒来获取一个记录。

我的DAO代码如下所示：

String queryString = "from Igeo igeo where igeo.ip_from <= " + ip
            + "and igeo.ip_to >= " + ip;

    Query q = session.createQuery(queryString);
    List<Igeo> igeoList = q.list();

    if(igeoList.size() > 0){
        Igeo igeo = igeoList.get(0);

        ISP = igeo.getIsp();
        ...
        ...         
    }

*Igeo = java表示表中的类

**当ip位于组合id列的值之间时，获取记录。

ip_from =1；ip_to =3；ip = 2；

所以上面的行将被退回。

此表仅用于读取记录，请建议我使用比上面更有效的queryString。

Answer 1

首先，删除hibernate，在查询浏览器中运行查询，并查看返回所需的时间。如果花了同样的时间，那就不是Hibernate。这是数据库的性能。确保将索引添加到ip_from和ip_to两列中。您还可以在查询上执行查询计划，以查看数据库在引擎盖下运行的内容，并尝试优化查询计划。

我建议不要像现在这样对您的查询使用连接。这就产生了一个安全漏洞，允许外部各方进行潜在的SQL注入。最好使用以下方法：

Query q = session.createQuery("from Igeo igeo where igeo.from_ip >= ? and igeo.to_ip <= ?");
q.setString( 0, ip );
q.setString( 1, ip );

您还可以使用命名参数，这可能会使其缩短一些。

Answer 2

如果表IGeo不包含ip_from和ip_to的重叠范围，则可以尝试以下操作

    String queryString = "FROM Igeo igeo"
        +  " WHERE igeo.ip_to >= " + ip
        +  " ORDER BY igeo.ip_to";

然后检查列表中的第一项(查看ip_from <= ip)。

即使该表可能包含ip_from、ip_to的重叠范围，我相信上面的HQL运行速度也会更快。

撇开<不说>，您确实不应该将像"ip“这样的原始字符串连接到HQL或>中。这会导致SQL注入攻击漏洞。使用查询参数代替</Aside>

另外，验证您的数据库在列上有对应于Igeo.ip_to的索引。

在我看来，从您的描述中可以看出，数据库具有Igeo.ip_from + IGeo.ip_to的主键。如果ip_from和ip_to的值不是重叠的，则似乎没有规范化。主键只需一个列即可。如果选择将这两列用作主键，则添加单个索引将有利于上述查询。

如果添加包含表中所有列的索引(从ip_to和ip_from开始)，某些数据库的性能会更好。(这使数据库能够通过只访问索引来满足查询)。不确定MySQL是否能优化到这个程度，但我知道DB2和Oracle会提供这种优化。