草庐IT

php - Mysql在哪里查询优化

coder 2023-10-24 原文

以下是自主系统号数据库的格式（下载并从this site！）.

range_start  range_end  number  cc  provider
-----------  ---------  ------  --  -------------------------------------
   16778240   16778495   56203  AU  AS56203 - BIGRED-NET-AU Big Red Group
   16793600   16809983   18144      AS18144

共745465行
正常查询如下：
select * from table where 3232235520 BETWEEN range_start AND range_end

工作正常，但我查询了大量的IP来检查它们的AS信息，这最终会占用太多的电话和时间。
探查器快照：
Blackfire profiler snapshot
我有两个索引：
ID列
range_start列和range_end列上的组合索引，作为make unique行。
问题：
有没有办法在一次查询中查询大量的IP？
multiplewhere (IP between range_start and range_end) OR where (IP between range_start and range_end) OR ...可以工作，但我无法获取ip>行映射，也无法获取为哪个ip检索的行。
有什么建议可以改变数据库结构来优化查询速度和缩短时间吗？
任何帮助都将不胜感激！谢谢！

最佳答案

可以查询多个IP地址。我们可以采取几种方法。假设range_start和range_end被定义为整数类型。
对于合理数量的IP地址，我们可以使用内联视图：

 SELECT i.ip, a.*
   FROM (           SELECT 3232235520 AS ip
          UNION ALL SELECT 3232235521
          UNION ALL SELECT 3232235522
          UNION ALL SELECT 3232235523
          UNION ALL SELECT 3232235524
          UNION ALL SELECT 3232235525
        ) i
   LEFT 
   JOIN ip_to_asn a
     ON a.range_start <= i.ip
    AND a.range_end   >= i.ip
  ORDER BY i.ip

这种方法对合理数量的IP地址有效。内联视图可以用更多的UNION ALL SELECT扩展以添加额外的ip地址。但对于一个“庞大”的数字来说，这并不一定奏效。
当我们变得“巨大”时，我们将在mysql中遇到限制……受max_allowed_packet限制的sql语句的最大大小，可能对可以显示的SELECT数目有限制。
内联视图可以替换为临时表，首先生成。
 DROP TEMPORARY TABLE IF EXISTS _ip_list_;
 CREATE TEMPORARY TABLE _ip_list_ (ip BIGINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
 INSERT INTO _ip_list_ (ip) VALUES (3232235520),(3232235521),(3232235522),...;
 ...
 INSERT INTO _ip_list_ (ip) VALUES (3232237989),(3232237990);

然后引用临时表代替内联视图：
 SELECT i.ip, a.*
   FROM _ip_list_ i
   LEFT
   JOIN ip_to_asn a
     ON a.range_start <= i.ip
    AND a.range_end   >= i.ip
  ORDER BY i.ip ;

然后删除临时表：
 DROP TEMPORARY TABLE IF EXISTS _ip_list_ ;

其他注意事项：
搅动数据库连接会降低性能。在建立和断开连接时会有大量的开销。如果应用程序重复连接和断开连接，如果它对发出的每一条sql语句都这样做，那么这种开销就会很明显。
运行单独的sql语句也有开销…必须将语句发送到服务器，对语句进行语法分析，根据语义进行求值，选择执行计划，执行计划，准备结果集，将结果集返回到客户端。这就是为什么按集合而不是按行处理更有效。与将语句发送到数据库并让它一次处理一个集合相比，处理rbar（一行一行地折磨一行）可能非常慢。
但有一个折衷方案。有了Ginormous集，事情可能会再次变得缓慢。
即使您可以在每个语句中处理两个IP地址，也将需要执行的语句数量减半。如果在每个语句中使用20个IP地址，那么语句的数量将减少到每次需要一行的数量的5%。
已经在(range_start,range_end)上定义的复合索引适合于此查询。
后续行动
正如里克·詹姆斯在评论中指出的那样，我之前说的“合适”的指数并不理想。
我们可以用不同的方式编写查询，这样可以更有效地使用该索引。
如果(range_start,range_end)是唯一（或主）键，则即使存在“重叠”范围，也将为每个IP地址返回一行。（上一个查询将返回range_start和range_end与IP地址重叠的所有行。）
 SELECT t.ip, a.*
   FROM ( SELECT s.ip
               , s.range_start
               , MIN(e.range_end) AS range_end
            FROM ( SELECT i.ip
                        , MAX(r.range_start) AS range_start
                     FROM _ip_list_ i
                     LEFT
                     JOIN ip_to_asn r
                       ON r.range_start <= i.ip
                    GROUP BY i.ip
                 ) s
            LEFT
            JOIN ip_to_asn e
              ON e.range_start = s.range_start
             AND e.range_end  >= s.ip
           GROUP BY s.ip, s.range_start
        ) t
   LEFT
   JOIN ip_to_asn a
     ON a.range_start = t.range_start
    AND a.range_end   = t.range_end
  ORDER BY t.ip ;

使用此查询，对于最内部的内联视图查询s，优化器可能能够有效地使用前导列为range_start的索引，以快速标识range_start的“最高”值（即小于或等于IP地址）。但是对于外部连接和group by oni.ip，我确实需要查看explain输出；这只是猜测优化器可能会做什么；重要的是优化器实际会做什么。）
然后，对于内联视图查询，mysql可能能够更有效地使用e上的复合索引，因为第一列上有等式谓词，第二列上有min aggregate上的不等式条件。
对于最外层的查询，mysql肯定能够有效地使用复合索引，因为这两列上都有相等的谓词。
对此表单的查询可能会显示性能的提高，或者性能可能会在手提篮中崩溃。(range_start,range_end)的输出应该能很好地指示正在发生的事情。我们希望在额外的列中看到“using index for group by”，并且只希望在最外层的查询中看到orderby的“using filesort”。（如果删除ORDERBY子句，则不希望在额外的列中看到“using filesort”。）
另一种方法是使用select列表中的相关子查询。当结果集包含大量行时，相关子查询的执行可能会变得昂贵。但是这种方法可以在一些用例中提供令人满意的性能。
此查询依赖于EXPLAIN表中没有重叠范围，并且当存在重叠范围时，此查询不会产生预期结果。
 SELECT t.ip, a.*
   FROM ( SELECT i.ip
               , ( SELECT MAX(s.range_start)
                     FROM ip_to_asn s
                    WHERE s.range_start <= i.ip
                 ) AS range_start
               , ( SELECT MIN(e.range_end)
                     FROM ip_to_asn e
                    WHERE e.range_end >= i.ip
                 ) AS range_end
            FROM _ip_list_ i
        ) r
   LEFT 
   JOIN ip_to_asn a
     ON a.range_start = r.range_start
    AND a.range_end   = r.range_end

为了演示为什么重叠的范围对于这个查询是个问题，给出了一个完全愚蠢的、虚构的例子
range_start  range_end 
-----------  ---------
       .101       .160
       .128       .244

给定IP地址ip_to_asn，.140子查询将找到MAX(range_start)，.128子查询将找到MIN(range_end)，然后外部查询将尝试找到匹配的行.160。那一排根本不存在。

关于php - Mysql在哪里查询优化，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42513868/

Mysqlphprangecoderange_startdatabasedatabase-design

有关php - Mysql在哪里查询优化的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  3. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  4. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  5. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  6. ruby-on-rails - ActiveRecord::Associations::CollectionProxy 从哪里获取.each 实例方法？ - 2

    假设我有模型Topics和Posts，其中Topichas_many:posts和Postbelongs_to:topic。此时我的数据库中已经有了一些东西。如果我进入Rails控制台并输入Topic.find(1).posts我相信我得到了一个CollectionProxy对象。=>#]>我可以对此调用.each以获得枚举器对象。=>#]:each>我对CollectionProxy如何处理.each感到困惑。我意识到它在某些时候是继承的，但我一直在阅读API文档，他们并没有说得很清楚CollectionProxy是从什么继承的，除非我遗漏了一些明显的东西。Thispage似乎并没有

  7. ruby-on-rails - solr 清理查询 - 2

    我在Rails上使用带有ruby​​的solr。一切正常，我只需要知道是否有任何现有代码来清理用户输入，比如以?开头的查询。或* 最佳答案 我不知道执行此操作的任何代码，但理论上可以通过查看parsingcodeinLucene来完成并搜索thrownewParseException(只有16个匹配!)。在实践中，我认为您最好只捕获代码中的任何solr异常并显示“无效查询”消息或类似信息。编辑:这里有几个“sanitizer”:http://pivotallabs.com/users/zach/blog/articles/937-s

  8. ruby-on-rails - Rails 3 在一个查询中包含多个表 - 2

    我正在为锦标赛开发一个Rails应用程序。我在这个查询中使用了三个模型:classPlayertruehas_and_belongs_to_many:tournamentsclassTournament:destroyclassPlayerMatch"Player",:foreign_key=>"player_one"belongs_to:player_two,:class_name=>"Player",:foreign_key=>"player_two"在tournaments_controller的显示操作中，我调用以下查询:Tournament.where(:id=>params

  9. ruby-on-rails - 闪存消息存储在哪里？ - 2

    我以为它们存储在cookie中-但不，检查cookie没有任何结果。session也不存储它们。那么，我在哪里可以找到它们？我需要这个来直接设置它们(而不是通过flashhash)。 最佳答案 它们存储在inyoursessionstore.自rails2.0以来的默认设置是cookie存储，但请检查config/initializers/session_store.rb以检查您是否使用默认设置以外的东西。 关于ruby-on-rails-闪存消息存储在哪里？，我们在StackOverf

  10. ruby-on-rails - Sunspot:如何对具有不同值的多个字段进行全文查询？ - 2

    我想用sunspot重现以下原始solr查询q=exact_term_text:fooORterm_textv:foo*ORalternate_text:bar*但我无法通过标准的太阳黑子界面理解这是否可能以及如何实现，因为看起来:fulltext方法似乎不接受多个文本/搜索字段参数我不知道将什么参数作为第一个参数传递给fulltext，就好像我通过了"foo"或"bar"结果不匹配如果我传递一个空参数，我得到一个q=*:*范围过滤器(例如with(:term).starting_with('foo*')(顾名思义)作为过滤器查询应用，因此不参与评分。似乎可以手动编写字符串(或者可能使

随机推荐