destoon7.0对mysql5.7优化,实现单台几百万数据下秒级速度

  2021-02-03    阅读:32
  destoon7.0对mysql5..7优化,实现单台几百万数据下秒级速度,可以缓解吃内存的情况,希望对大家有帮助

记得要备份数据,以防万一,代码附上

  1.  
    ALTER TABLE `destoon_sell_5` ADD INDEX ( `status` ) ;
  2.  
    ALTER TABLE `destoon_sell_5` ADD INDEX ( `vip` ) ;
  3.  
    ALTER TABLE `destoon_sell_5` ADD INDEX ( `thumb` ) ;
  4.  
    ALTER TABLE `destoon_sell_5` ADD INDEX ( `addtime` ) ;
  5.  
    ALTER TABLE `destoon_sell_5` ADD INDEX ( `ip` ) ;
  6.  
     
  7.  
     
  8.  
    ALTER TABLE `destoon_mall` ADD INDEX ( `status` ) ;
  9.  
    ALTER TABLE `destoon_mall` ADD INDEX ( `vip` ) ;
  10.  
    ALTER TABLE `destoon_mall` ADD INDEX ( `thumb` ) ;
  11.  
    ALTER TABLE `destoon_mall` ADD INDEX ( `addtime` ) ;
  12.  
    ALTER TABLE `destoon_mall` ADD INDEX ( `ip` ) ;
  13.  
     
  14.  
    ALTER TABLE `destoon_buy_6` ADD INDEX ( `status` ) ;
  15.  
    ALTER TABLE `destoon_buy_6` ADD INDEX ( `vip` ) ;
  16.  
    ALTER TABLE `destoon_buy_6` ADD INDEX ( `thumb` ) ;
  17.  
    ALTER TABLE `destoon_buy_6` ADD INDEX ( `addtime` ) ;
  18.  
    ALTER TABLE `destoon_buy_6` ADD INDEX ( `ip` ) ;
  19.  
     
  20.  
    ALTER TABLE `destoon_quote` ADD INDEX ( `status` ) ;
  21.  
    ALTER TABLE `destoon_quote` ADD INDEX ( `thumb` ) ;
  22.  
    ALTER TABLE `destoon_quote` ADD INDEX ( `ip` ) ;
  23.  
     
  24.  
    ALTER TABLE `destoon_group` ADD INDEX ( `status` ) ;
  25.  
    ALTER TABLE `destoon_group` ADD INDEX ( `vip` ) ;
  26.  
    ALTER TABLE `destoon_group` ADD INDEX ( `thumb` ) ;
  27.  
    ALTER TABLE `destoon_group` ADD INDEX ( `addtime` ) ;
  28.  
    ALTER TABLE `destoon_group` ADD INDEX ( `ip` ) ;
  29.  
     
  30.  
    ALTER TABLE `destoon_exhibit` ADD INDEX ( `status` ) ;
  31.  
    ALTER TABLE `destoon_exhibit` ADD INDEX ( `thumb` ) ;
  32.  
    ALTER TABLE `destoon_exhibit` ADD INDEX ( `ip` ) ;
  33.  
     
  34.  
    ALTER TABLE `destoon_news` ADD INDEX ( `status` ) ;
  35.  
    ALTER TABLE `destoon_article_21` ADD INDEX ( `status` ) ;
  36.  
    ALTER TABLE `destoon_article_21` ADD INDEX ( `thumb` ) ;
  37.  
    ALTER TABLE `destoon_article_21` ADD INDEX ( `addtime` ) ;
  38.  
     
  39.  
    ALTER TABLE `destoon_info_22` ADD INDEX ( `status` ) ;
  40.  
    ALTER TABLE `destoon_info_22` ADD INDEX ( `vip` ) ;
  41.  
    ALTER TABLE `destoon_info_22` ADD INDEX ( `thumb` ) ;
  42.  
    ALTER TABLE `destoon_info_22` ADD INDEX ( `addtime` ) ;
  43.  
    ALTER TABLE `destoon_info_22` ADD INDEX ( `ip` ) ;
  44.  
     
  45.  
    ALTER TABLE `destoon_brand_13` ADD INDEX ( `status` ) ;
  46.  
    ALTER TABLE `destoon_brand_13` ADD INDEX ( `vip` ) ;
  47.  
    ALTER TABLE `destoon_brand_13` ADD INDEX ( `thumb` ) ;
  48.  
    ALTER TABLE `destoon_brand_13` ADD INDEX ( `addtime` ) ;
  49.  
    ALTER TABLE `destoon_brand_13` ADD INDEX ( `ip` ) ;
  50.  
     
  51.  
    ALTER TABLE `destoon_job` ADD INDEX ( `status` ) ;
  52.  
    ALTER TABLE `destoon_job` ADD INDEX ( `vip` ) ;
  53.  
    ALTER TABLE `destoon_job` ADD INDEX ( `addtime` ) ;
  54.  
    ALTER TABLE `destoon_job` ADD INDEX ( `ip` ) ;
  55.  
     
  56.  
    ALTER TABLE `destoon_know` ADD INDEX ( `status` ) ;
  57.  
    ALTER TABLE `destoon_know` ADD INDEX ( `thumb` ) ;
  58.  
    ALTER TABLE `destoon_know` ADD INDEX ( `ip` ) ;
  59.  
     
  60.  
    ALTER TABLE `destoon_photo_12` ADD INDEX ( `status` ) ;
  61.  
    ALTER TABLE `destoon_photo_12` ADD INDEX ( `thumb` ) ;
  62.  
    ALTER TABLE `destoon_photo_12` ADD INDEX ( `ip` ) ;
  63.  
     
  64.  
    ALTER TABLE `destoon_video_14` ADD INDEX ( `status` ) ;
  65.  
    ALTER TABLE `destoon_video_14` ADD INDEX ( `thumb` ) ;
  66.  
    ALTER TABLE `destoon_video_14` ADD INDEX ( `ip` ) ;
  67.  
     
  68.  
     
  69.  
    ALTER TABLE `destoon_down_15` ADD INDEX ( `status` ) ;
  70.  
    ALTER TABLE `destoon_down_15` ADD INDEX ( `thumb` ) ;
  71.  
    ALTER TABLE `destoon_down_15` ADD INDEX ( `ip` ) ;
  72.  
     
  73.  
    ALTER TABLE `destoon_404` ADD INDEX ( `url` ) ;
  74.  
    ALTER TABLE `destoon_404` ADD INDEX ( `addtime` ) ;
  75.  
     
  76.  
     
  77.  
    ALTER TABLE `destoon_company` ADD INDEX ( `catid` ) ;
  78.  
    LTER TABLE `destoon_company` ADD INDEX (`catids`);
  79.  
    ALTER TABLE `destoon_company` ADD FULLTEXT (`catids`);
  80.  
     
  81.  
     
  82.  
    ALTER TABLE `destoon_upload_0` ADD INDEX ( `addtime` ) ;
  83.  
    ALTER TABLE `destoon_upload_0` ADD INDEX ( `username` ) ;
  84.  
    ALTER TABLE `destoon_upload_1` ADD INDEX ( `addtime` ) ;
  85.  
    ALTER TABLE `destoon_upload_1` ADD INDEX ( `username` ) ;
  86.  
    ALTER TABLE `destoon_upload_2` ADD INDEX ( `addtime` ) ;
  87.  
    ALTER TABLE `destoon_upload_2` ADD INDEX ( `username` ) ;
  88.  
    ALTER TABLE `destoon_upload_3` ADD INDEX ( `addtime` ) ;
  89.  
    ALTER TABLE `destoon_upload_3` ADD INDEX ( `username` ) ;
  90.  
    ALTER TABLE `destoon_upload_4` ADD INDEX ( `addtime` ) ;
  91.  
    ALTER TABLE `destoon_upload_4` ADD INDEX ( `username` ) ;
  92.  
    ALTER TABLE `destoon_upload_5` ADD INDEX ( `addtime` ) ;
  93.  
    ALTER TABLE `destoon_upload_5` ADD INDEX ( `username` ) ;
  94.  
    ALTER TABLE `destoon_upload_6` ADD INDEX ( `addtime` ) ;
  95.  
    ALTER TABLE `destoon_upload_6` ADD INDEX ( `username` ) ;
  96.  
    ALTER TABLE `destoon_upload_7` ADD INDEX ( `addtime` ) ;
  97.  
    ALTER TABLE `destoon_upload_7` ADD INDEX ( `username` ) ;
  98.  
    ALTER TABLE `destoon_upload_9` ADD INDEX ( `addtime` ) ;
  99.  
    ALTER TABLE `destoon_upload_9` ADD INDEX ( `username` ) ;
  100.  
    ALTER TABLE `destoon_category` ADD INDEX ( `parentid` ) ;
  101.  
    ALTER TABLE `destoon_category` ADD INDEX ( `listorder` )
  102.  
    ALTER TABLE `destoon_category` ADD INDEX ( `item` ) ;
  103.  
     
  104.  
    ALTER TABLE `destoon_area` ADD INDEX ( `parentid` ) ;
  105.  
    ALTER TABLE `destoon_area` ADD INDEX ( `listorder` ) ;
  106.  
     
  107.  
    ALTER TABLE `destoon_comment_stat` ADD INDEX ( `moduleid` ) ;
  108.  
    ALTER TABLE `destoon_comment_stat` ADD INDEX ( `itemid` ) ;
  109.  
     
  110.  
    ALTER TABLE `destoon_message` ADD INDEX ( `ip` ) ;
  111.  
    ALTER TABLE `destoon_message` ADD INDEX ( `typeid` ) ;
  112.  
    ALTER TABLE `destoon_message` ADD INDEX ( `status` ) ;
  113.  
    ALTER TABLE `destoon_message` ADD INDEX ( `addtime` );
  114.  
     
  115.  
    ALTER TABLE `destoon_message` ADD INDEX ( `isread` ) ;
  116.  
    ALTER TABLE `destoon_message` ADD INDEX ( `issend` );
  117.  
     
  118.  
     
  119.  
    ALTER TABLE `destoon_online` ADD INDEX ( `lasttime` ) ;
  120.  
     
  121.  
    ALTER TABLE `destoon_guestbook` ADD INDEX ( `status` ) ;
  122.  
     
  123.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `status` ) ;
  124.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `updatetime` ) ;
  125.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `month_search` ) ;
  126.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `total_search` ) ;
  127.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `week_search` ) ;
  128.  
    ALTER TABLE `destoon_keyword` ADD INDEX ( `today_search` ) ;
  129.  
     
  130.  
    ALTER TABLE `destoon_alert` ADD INDEX ( `status` ) ;
  131.  
    ALTER TABLE `destoon_alert` ADD INDEX ( `addtime` ) ;
  132.  
     
  133.  
    ALTER TABLE `destoon_webpage` ADD INDEX ( `item` ) ;
  134.  
    ALTER TABLE `destoon_webpage` ADD INDEX ( `listorder` ) ;
  135.  
     
  136.  
    ALTER TABLE `destoon_announce` ADD INDEX ( `listorder` ) ;
  137.  
     
  138.  
    ALTER TABLE `destoon_link` ADD INDEX ( `status` ) ;
  139.  
     
  140.  
    ALTER TABLE `destoon_member` ADD INDEX ( `regtime` ) ;
  141.  
     
  142.  
    ALTER TABLE `destoon_ad` ADD INDEX ( `status` ) ;
  143.  
    ALTER TABLE `destoon_ad` ADD INDEX ( `username` ) ;
  144.  
    ALTER TABLE `destoon_spread` ADD INDEX ( `username` ) ;
  145.  
    ALTER TABLE `destoon_spread` ADD INDEX ( `status` ) ;
  146.  
    #下面为全文索引 destoon_club_data 类似的模块内容全文最好都开启 其它的后台有提示
  147.  
     
  148.  
    ALTER TABLE `destoon_club_data` ADD FULLTEXT (`content`);
  149.  
    ALTER TABLE `destoon_cron` ADD INDEX ( `nexttime` );
  150.  
    ALTER TABLE `destoon_online` ADD INDEX ( `username` );
  151.  
     
  152.  
    #公司 供应 模块增加全文索引 建议用ngram版
  153.  
    ALTER TABLE `destoon_company` ADD FULLTEXT (`company`);
  154.  
    ALTER TABLE `destoon_company` ADD FULLTEXT (`business`);
  155.  
    ALTER TABLE `destoon_company_data` ADD FULLTEXT (`content`);
  156.  
    ALTER TABLE `destoon_sell_5` ADD FULLTEXT (`title`);
  157.  
    ALTER TABLE `destoon_sell_data_5` ADD FULLTEXT (`content`);
  158.  
     
  159.  
    #用中文检索分词插件ngram版 MySQL 5.7.6以上版本 中文识别友好 要修改程序
  160.  
    ALTER TABLE `destoon_keyword` ADD FULLTEXT (`keyword`) WITH PARSER ngram;
  161.  
    ALTER TABLE `destoon_company` ADD FULLTEXT (`company`,`business`) WITH PARSER ngram;
  162.  
    ALTER TABLE `destoon_sell_5` ADD FULLTEXT (`title`,`introduce`)WITH PARSER ngram;

免责声明:
本站部份内容系网友自发上传与转载,不代表本网赞同其观点;
如涉及内容、版权等问题,请在30日内联系,我们将在第一时间删除内容!

相关Destoon技术

(c)2012-2021 www.destoon.info All Rights Reserved
慧一科技工作室   
  QQ咨询

购买模板 售前咨询

  咨询QQ

3292628658
(早8:30-晚10:00)

  微信咨询

咨询微信
客服微信

  返回顶部