草庐IT

mysql - IN过多时如何改进sql

coder 2023-10-25 原文

我有一个条件 IN 太长的 SQL。而且我的 SQL 执行时间太长 有什么办法可以提高它的性能吗？

这是SQL

SELECT * FROM bi_bunrui_syouhin_shop_day_jyutyuu AS bi   
WHERE bi.bunrui_code IN     
('0001','1000','1001','1005','1006','1007','1008','1009','1010','1011','1012','1013','1014','1015','1016','1017','1018','1020','1021','1022','1023','1030','1031','1032','1033','1034','1036','1037','1038','1039','1040','1041','1043','1044','1045','1046','1050','1054','1055','1060','1061','1070','1080','1081','1082','1083','1084','1085','1086','1088','1090','1091','1092','1093','1094','1095','1096','1097','1100','1101','1102','1103','1104','1105','1106','1107','1108','1109','1110','1111','1112','1113','1114','1115','1116','1117','1118','1119','1120','1121','1122','1123','1130','1131','1132','1133','1134','1135','1136','1137','1140','1141','1142','1143','1145','1148','1150','1151','1152','1153','1154','1155','1156','1157','1160','1161','1162','1163','1164','1165','1166','1167','1168','1169','1170','1171','1180','1181','1182','1183','1184','1187','1189','1200','1201','1202','1203','1204','1205','1206','1207','1210','1211','1212','1220','2003','2009','2022','2028','2042','2048','2062','2068','2082','2088','2102','2108','2122','2125','2128','2129','2134','2141','2144','2161','2182','2186','2222','2228','2242','2253','2262','2268','2302','2511','2512','2513','3180','3207','3208','4023','4024','4028','4029','4030','4031','4032','4033','4034','4036','4040','4041','4042','4043','4044','4062','4064','4066','4067','4068','4069','4070','4071','4092','5013','5014','5015','5016','5017','5018','6200','6201','6202','6203','6204','6205','6220','6221','6222','6223','6224','6225','6226','6234','6235','6240','6241','6242','6243','6244','6245','6246','6247','6249','6250','6251','6260','6261','6262','6263','6264','6265','6280','6281','6282','6283','6284','6285','6286','6287','6288','6289','6290','6291','6292','6293','6300','6301','6302','6303','6304','6305','6320','6321','6322','6323','6324','6325','6340','6342','6343','6344','6345','6346','6347','6348','6349','6360','6361','6362','6363','6364','6365','6366','6367','6368','6369','6371','6372','6373','6374','6375','6376','6377','6378','6379','6380','6381','6382','6383','6384','6385','6400','6401','6402','6403','6406','6407','6408','6420','6421','6422','6423','6424','6425','6426','6427','6428','6429','6430','6431','6432','6433','6434','6435','6436','6440','6441','6442','6443','6444','6445','6446','6447','6448','6449','6450','6451','6452','6453','6454','6455','6456','6457','6458','6459','6460','6470','6471','6472','6473','6474','6475','6476','6477','6478','6480','6481','6482','6483','6484','6485','6486','6487','6489','6490','6500','6501','6502','6504','6505','6506','6507','6508','6509','6510','6511','6512','6513','6514','6515','6520','6521','6522','6523','6524','6525','6526','6527','6528','6529','6530','6531','6550','6551','6552','6553','6554','6555','6556','6557','6558','6559','6560','6561','6562','6563','6564','6565','6567','6568','6569','6570','6572','6573','6574','7694','7993','9200','9201','9202','9203','9204','9205','9206','9208','9310','9320')

谢谢你的帮助

最佳答案

你应该创建一个 Temporary Table (参见临时表)，在该临时表上创建一个智能索引，将所有数据插入该临时表，然后在临时表和 'bi_bunrui_syouhin_shop_day_jyutyuu' 表之间进行内部连接。

关于mysql - IN过多时如何改进sql，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16910052/

多时改进39sectionbi_bunrui_syouhin_shop_day_jyutyumysql

有关mysql - IN过多时如何改进sql的更多相关文章

  1. ruby - 如何使用 Nokogiri 的 xpath 和 at_xpath 方法 - 2

    我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div

  2. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

    总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

  3. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

    关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

  4. ruby-on-rails - rails : "missing partial" when calling 'render' in RSpec test - 2

    我正在尝试测试是否存在表单。我是Rails新手。我的new.html.erb_spec.rb文件的内容是:require'spec_helper'describe"messages/new.html.erb"doit"shouldrendertheform"dorender'/messages/new.html.erb'reponse.shouldhave_form_putting_to(@message)with_submit_buttonendendView本身，new.html.erb，有代码:当我运行rspec时，它失败了:1)messages/new.html.erbshou

  5. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

    给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

  6. ruby-on-rails - 'compass watch' 是如何工作的/它是如何与 rails 一起使用的 - 2

    我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t

  7. ruby - 如何将脚本文件的末尾读取为数据文件(Perl 或任何其他语言) - 2

    我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚

  8. ruby - 如何指定 Rack 处理程序 - 2

    Rackup通过Rack的默认处理程序成功运行任何Rack应用程序。例如:classRackAppdefcall(environment)['200',{'Content-Type'=>'text/html'},["Helloworld"]]endendrunRackApp.new但是当最后一行更改为使用Rack的内置CGI处理程序时，rackup给出“NoMethodErrorat/undefinedmethod`call'fornil:NilClass”:Rack::Handler::CGI.runRackApp.newRack的其他内置处理程序也提出了同样的反对意见。例如Rack

  9. ruby-on-rails - Rails 源代码 : initialize hash in a weird way? - 2

    在rails源中:https://github.com/rails/rails/blob/master/activesupport/lib/active_support/lazy_load_hooks.rb可以看到以下内容@load_hooks=Hash.new{|h,k|h[k]=[]}在IRB中，它只是初始化一个空哈希。和做有什么区别@load_hooks=Hash.new 最佳答案 查看rubydocumentationforHashnew→new_hashclicktotogglesourcenew(obj)→new_has

  10. ruby-on-rails - Rails 3 I18 : translation missing: da. datetime.distance_in_words.about_x_hours - 2

    我看到这个错误:translationmissing:da.datetime.distance_in_words.about_x_hours我的语言环境文件:http://pastie.org/2944890我的看法:我已将其添加到我的application.rb中:config.i18n.load_path+=Dir[Rails.root.join('my','locales','*.{rb,yml}').to_s]config.i18n.default_locale=:da如果我删除I18配置，帮助程序会处理英语。更新:我在config/enviorments/devolpment

随机推荐