精华内容
下载资源
问答
  • 一天百万数据
    千次阅读
    2021-03-24 14:55:03

    其实前面使用多线程进行写过一个动态加载,原先我自己以为没问题,但是后面看了下任务管理器,100万条数据后台加载到5个g还在激增。这样下去当内存超出一定范围,就会宕机,后来查看代码就发现这个机制是一直向你的表格中一直插入,并不会释放掉。后来,经过几天的沉淀,网上大部分说使用tableview以及自定model,效率比qtablewidget效率高。其实自定义的model无非就是后台处理数据,我给qtablewidget单独开出一条线程,也不会有任何卡顿。
    下面来说下简易思路,然后配合代码细讲。
    简单的思路:比如你有一个文件,里面有100万行数据,我们想将他加载到qtablewidget,qtablewidget加载100条,1000条数据都没啥问题。你不会有感觉卡顿什么的,但是当你加载到一万条数据以上,卡顿就会越来越明显,(重点来了),那该如何加载这100万条数据呢。首先一个窗体是有限的,也就是说,不论你将窗口放的再大,你也不可能同时看到100万条数据,其实有限的视野也就是同时看到100条左右。首先我们需要单独开出一个线程来进行加载100万条数据,目的是为了不卡住主界面,使用一个自定义结构体来存储你的数据,当我鼠标滚轮滚动的时候,(比如向下滚动滚轮,我就向下加载5条数据),但是我的界面始终只有100条数据,所以你的界面始终不会卡顿,内存也不会存在溢出的情况。
    好,下面结合代码细说
    我们先看自定义tablewidget:
    构造:

    connect(this->verticalScrollBar(),&QScrollBar::valueChanged,this,&MyTableWgt::OnVertivalValueChanged);
        m_pDeal= new MyDeal();
    
        m_pDeal->moveToThread(&m_thread);
        connect(this,&MyTableWgt::startRunning,m_pDeal,&MyDeal::CreateData);
        connect(&m_thread,&QThread::finished,m_pDeal,&QObject::deleteLater);
        connect(m_pDeal,&MyDeal::sSendFinished,this,&MyTableWgt::ReceiverData);
        connect(m_pDeal,&MyDeal::sSendValueChanaged,this,&MyTableWgt::ReceiverChanged);
    
        m_thread.start();
    

    构造中mydeal就是我单独开出来的线程,用作数据处理。线程我就不细说了,自己可以网上搜索得知。
    connect我们知道是信号和槽,能传输数据,但是你自定义的结构体是不可以进行传输的,所以我们要在数据加载完成时候发出一个信号。让tablewidget进行显示数据

    鼠标滚轮事件:

    void MyTableWgt::wheelEvent(QWheelEvent *event)
    {
        int delta= event->delta();
    
        int row=m_pDeal->GetCurrentRow();
        if(delta>0)
        {
            if(row<5)
            {
                return;
            }
            else
            {
                row=row-5;  //每次滚动滚轮,向上加载5个
            }
        }
        else
        {
            if(row>m_pDeal->GetDataSize()-100)
            {
                row=m_pDeal->GetDataSize()-100;
            }
            else
            {
                row=row+5;
            }
    
        }
    
       m_pDeal->LoadData(row);
    }
    

    我们获取到当前滚轮是向上或向下滚动,然后从处理数据类中获取当前是第几个,如果鼠标向下滚动就是在原有基础上向下加载5个。反之亦然。

    拖动滚动条:

    void MyTableWgt::OnVertivalValueChanged(int i_value)
    {
        if(i_value==this->verticalScrollBar()->maximum())
        {
            int row=m_pDeal->GetCurrentRow();
    
            if(row>m_pDeal->GetDataSize()-200)
            {
                row=m_pDeal->GetDataSize()-100;
            }
            else
            {
                row=row+100;
                this->verticalScrollBar()->setValue(this->verticalScrollBar()->maximum()*0.9);
            }
            m_pDeal->LoadData(row);
        }
        if(i_value==this->verticalScrollBar()->minimum())
        {
            int row=m_pDeal->GetCurrentRow();
            if(row>=100)
            {
                row=row-100;
                if(row<100)
                {
                    row=0;
                }
                this->verticalScrollBar()->setValue(this->verticalScrollBar()->maximum()*0.1);
            }
            m_pDeal->LoadData(row);
        }
    }
    

    原理就是和wheelevent事件原理差不多,但是需要注意的是,不能让滚动为最大值或最小值,不然就不能继续加载数据了。

    第一次加载数据:

    void MyTableWgt::ReceiverData()
    {
        this->ClearAllRow();
        QList<myData> dataList=m_pDeal->GetFirstData();
        this->setRowCount(dataList.size());
        for(int i=0;i<dataList.size();i++)  //首次加载100个
        {
            myData data=dataList[i];
            this->setItem(i,0,new QTableWidgetItem(data.str0));
            this->setItem(i,1,new QTableWidgetItem(data.str1));
            this->setItem(i,2,new QTableWidgetItem(data.str2));
            this->setItem(i,3,new QTableWidgetItem(data.str3));
            this->setItem(i,4,new QTableWidgetItem(data.str4));
            this->setItem(i,5,new QTableWidgetItem(data.str5));
            this->setItem(i,6,new QTableWidgetItem(data.str6));
            this->setItem(i,7,new QTableWidgetItem(data.str7));
            this->setItem(i,8,new QTableWidgetItem(data.str8));
            this->setItem(i,9,new QTableWidgetItem(data.str9));
            this->setItem(i,10,new QTableWidgetItem(data.str10));
        }
    }
    

    默认界面就是显示100个,因为加载100万数据是需要时间的,所以当数据又100个的时候,我就会进行加载一次。

    当滚动滚轮或滑动滚动条

    void MyTableWgt::ReceiverChanged()
    {
        this->ClearAllRow();
        this->setRowCount(100);
        //int currentRowcount=this->rowCount();
    
        QList<myData> dataList=m_pDeal->GetSendData();
        this->setRowCount(100);
        int k=0;
        for(int i=0;i<dataList.size();i++)
        {
            myData data=dataList[i];
            this->setItem(i,0,new QTableWidgetItem(data.str0));
            this->setItem(i,1,new QTableWidgetItem(data.str1));
            this->setItem(i,2,new QTableWidgetItem(data.str2));
            this->setItem(i,3,new QTableWidgetItem(data.str3));
            this->setItem(i,4,new QTableWidgetItem(data.str4));
            this->setItem(i,5,new QTableWidgetItem(data.str5));
            this->setItem(i,6,new QTableWidgetItem(data.str6));
            this->setItem(i,7,new QTableWidgetItem(data.str7));
            this->setItem(i,8,new QTableWidgetItem(data.str8));
            this->setItem(i,9,new QTableWidgetItem(data.str9));
            this->setItem(i,10,new QTableWidgetItem(data.str10));
            k++;
        }
    }
    

    记住一句画,界面始终显示100条数据

    加载数据类:
    初始加载数据:

    void MyDeal::CreateData(QString i_path)
    {
        m_CurrentRow=0;
        QFile file(i_path);
        if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
            return;
        int i=0;
        while (!file.atEnd())
        {
            QString strLine=file.readLine();
    
            QStringList strList=strLine.split("&");
    
            myData data;
            data.str0=strList[0];
            data.str1=strList[1];
            data.str2=strList[2];
            data.str3=strList[3];
            data.str4=strList[4];
            data.str5=strList[5];
            data.str6=strList[6];
            data.str7=strList[7];
            data.str8=strList[8];
            data.str9=strList[9];
            data.str10=strList[10];
            m_DataList.append(data);
    
            if(i<=100)
            {
                m_FirstData.append(data);
            }
    
            if(i==100)
            {
               emit sSendFinished();
            }
    
            i++;
    
        }
        m_DataSize=m_DataList.size();
    }
    

    mydata是自定义数据类,就是简单的存储数据。

    当数据改变时候:

    m_SendData.clear();
        for(int i=row;i<row+100;i++)
        {
            myData data=m_DataList[i];
            m_SendData.append(data);
        }
        m_CurrentRow=row;
        emit sSendValueChanaged();
    

    先拿到需要改变的数据,然后将数据发送给界面,界面进行刷新。

    看下运行效果:
    在这里插入图片描述
    这是我需要加载的文件,3万行,不算多。后面可能会有百万行。这套方法绝对承受的住百万条数据!!

    在这里插入图片描述
    可以看到数据一直在往下加载,但没有消耗任何的内存。

    全部代码到这里下载。
    ヾ( ̄▽ ̄)ByeBye

    更多相关内容
  • 百万数据导入MySQL的方法汇总(

    千次阅读 多人点赞 2020-04-23 20:37:23
    昨晚遇到个把百万数据导入MySQL的问题,翻遍整个网络,最后找到了如下几种方法,这里先做个汇总! 直接导入 ------此方法极不推荐!费时费力还损电脑! 用Load data infile和临时表导入;------ 推荐 使用存储...

    昨晚遇到一个把百万条数据导入MySQL的问题,翻遍整个网络,最后找到了如下几种方法,这里先做个汇总!

    1. 直接导入 ;
    2. 用Load data infile导入
    3. 使用存储过程批量导入
    4. 更换引擎,让ENGINE=InnoDB为MyISAM,再导入;
    5. 合并单条SQL语句为多条,再导入;
    6. 利用事务进行有序的多次插入。

    现在掌柜再依次对上面的各种方法进行一个介绍以及各种方法会遇到的坑😂。

    • 首先是直接导入,如果需要示例数据可以私掌柜。

    ----------------------------------------2021.07.12更新--------------------------------------------
    因为网盘过期了,所以示例数据如果大家有需要,还是直接私我你的邮箱,我再发给你,谢谢🤝!!!
    ----------------------------------------2021.07.12分割--------------------------------------------

    ----------------------------------------2021.05.18更新--------------------------------------------
    因为最近好些朋友都想要示例数据,所以掌柜干脆直接放网盘,大家可以自取👇:
    链接:https://pan.baidu.com/s/1VTBSZBFGZ5VY9YnCHtrODw
    提取码:1isa
    ----------------------------------------2021.05.18分割--------------------------------------------

    PS:直接导入之前需要更改MySQL的数据导入权限,具体方法可以查看之前掌柜写的比较详细的一篇 ------>MySQL8.0版本以上的文件导入权限问题;此外已经新建表名为user_gender。
    PPS:掌柜这里的MySQL是8.0.15版本!

    不过掌柜一开始使用source直接导入的时候就遇到第一个坑👇报错如下:
    在这里插入图片描述
    有点纳闷?再三检查文件名没有写错啊,咋会打不开外部文件导入呢?
    于是翻阅谷歌,在一个外国小哥的博客里面发现遇到同样的问题,于是往下一看,终于找到原因了:
    在这里插入图片描述
    没有写对文件路径和最后多加了分号导致的。。。

    use your_database;
    SOURCE your_disk:/yourpath/user_gender1.sql
    

    后来按👆上面的步骤和正确写法再次操作就成功导入了:
    在这里插入图片描述
    注意看右下角的开始时间是20:28,结果半个小时过去了,数据还在导入。。。
    在这里插入图片描述
    于是掌柜后来自动掐断,再随手查看这半小时导入了多少数据:
    在这里插入图片描述
    惨不忍睹!半小时才导入3万多条。。。这上百万的数据岂不是要一天???这怎么能忍!于是就有了开头的一番搜索。

    • 下面来到第二种方法👉:使用Load data infile 导入。
      依然一开始要更改导入权限,然后开始执行导入文件操作:
      在这里插入图片描述
      但是突然报错如上!👆,说不正确的整数值(ERROR1366: Incorrect integer value) ???成功get到了
      第二个坑
      👈。奇怪,明明要导入的文件打开就是整数:
      在这里插入图片描述
      后来掌柜再次搜索官方文档发现是由sql_mode这个所导致的,因为MySQL安装的时候是默认使用的严格SQL模式,所以对数据类型的验证要求很严格。
      在这里插入图片描述
      于是解决办法就是:更改sql_mode的值即可。

    如果你不确定MySQL目前有哪些模式可以先查询一下:
    在这里插入图片描述
    上面两条命令都可以查询到目前你的sql包含哪些模式,第一个查询是全局查询;第二个是目前进程的查询。可以发现两个里面都有strict 这个模式存在,所以需要去掉它
    在这里插入图片描述
    或者设置成空字符串也可以:
    在这里插入图片描述
    可以发现设置后再次查询sql_mode已经没有strict 模式了,现在再次用Load导入看看:
    在这里插入图片描述
    终于不再报错Incorrect,但是第三个坑又出现了🕳👆 ------> ERROR 1062(23000):Duplicate entry ‘0’ for key ‘PRIMARY’.
    然后掌柜又去官方文档查看,发现官方说出现这样的情况是因为主键的问题,然后接着官方说如果在插入数据后面加入IGNORE,会产生警示但不报错,并且重复的键值不会插入。
    在这里插入图片描述
    显然掌柜这里并不能使用IGNORE方法,然后掌柜又去翻阅谷歌有同样情况的人是如何解决的,发现有些说用自增主键的方式可以解决但是掌柜再看 这里 要导入的数据集并不符合这个方法。。. (打脸自己😂,今天发现就是在一开始创建表格的时候设置自增主键即可解决这个问题!!!
    在这里插入图片描述
    接着再次导入数据到表格:
    在这里插入图片描述
    终于成功导入了百万条数据,然后今天耗时42秒,比第一种方法确实快了不止100倍!!!!

    (未完待续。。。)

    展开全文
  • MySQL 数百万数据条件查询优化

    千次阅读 2019-05-08 10:36:16
    最近在公司实习做到一个项目,要在一个包含数百万数据表(如果以日期来分类,大概是同一天里又十多万行数据)之中查询出日期在某个日期查询出与之相邻日期的那些行的结果,其中只有日期包含索引,然后还有多个条件...

    MySQL 数百万行数据条件查询优化

      最近在公司实习做到一个项目,要在一个包含数百万行数据表(如果以日期来分类,大概是同一天里又十多万行数据)之中查询出日期在某个日期查询出与之相邻日期的那些行的结果,其中只有日期包含索引,然后还有多个条件查询。在刚开始的时候,需要花费2秒多才能查询出想要的结果,这个速度肯定对于用户查询很不友好,于是我被要求将查询出结果的时间降到300ms之内。然后经过多种方法优化,最终将查询时间减少到200多ms。速度快了仅10倍。

      这篇博客就是基于此背景之下产生的,这里顺便将自己的优化方法记录下来以防以后遗忘。

      MySQL 数百万行以上的数量多条件查询一般都会很浪费时间,这里提供了几种方法及思路进行优化,尽量优化到能够应用的场景。

    首先创建一个百万数据量的数据表

      这里使用了mysql的存储过程来插入数据,我通过改变存储过程CONCAT('2018-06-0',i)中的2018-06-0的月份的值从而得到不同日期的多行数据。

      例子表格建立sql语句

    -- PS: 这个表和存储过程是我从网上随便找的,可能建表方面不太规范,大家忽略,只要看字段值即可
    
    -- 创建MyISAM模式表方便批量跑数据
    CREATE TABLE `logs_info` (
      `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键id',
      `dt` VARCHAR(20) NOT NULL comment '记录日期',
      `logtype` VARCHAR(255) DEFAULT NULL comment '日志类型',
      `logurl` VARCHAR(255) DEFAULT NULL comment '日志url',
      `logip` VARCHAR(255) DEFAULT NULL comment 'ip地址',
      `logdz` VARCHAR(255) DEFAULT NULL comment '其他字段',
      `ladduser` VARCHAR(255) DEFAULT NULL comment '其他字段',
      `lfadduser` VARCHAR(255) DEFAULT NULL comment '其他字段',
      `laddtime` DATETIME DEFAULT NULL comment '其他字段',
      `htmlname` VARCHAR(255) DEFAULT NULL comment '页面名称'
    ) ENGINE=MYISAM  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='日志表';
    -- 创建索引
    CREATE INDEX dtIndex ON cfile.logs_info(dt); 
    -- 添加表字段
    ALTER TABLE cfile.logs_info ADD (ctime  DATETIME DEFAULT '1991-01-01 10:10:10' NOT NULL);
    ALTER TABLE cfile.logs_info ADD ( mtime DATETIME DEFAULT NOW() NOT NULL);
     
    -- 创建存储过程
    
    DROP PROCEDURE IF EXISTS my_insert;
    
    DELIMITER $$
    
    CREATE
        /*[DEFINER = { user | CURRENT_USER }]*/
        PROCEDURE `cfile`.`my_insert`()
        /*LANGUAGE SQL
        | [NOT] DETERMINISTIC
        | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
        | SQL SECURITY { DEFINER | INVOKER }
        | COMMENT 'string'*/
    	BEGIN
    	
       DECLARE n INT DEFAULT 1;
       DECLARE i INT DEFAULT 1;
            loopname:LOOP
                INSERT INTO `logs_info`(`dt`,`logtype`,`logurl`,`logip`,`logdz`,`ladduser` ,`lfadduser`,`laddtime`,`htmlname`) VALUES ( CONCAT('2018-06-0',i),2+n, '/index', '0:0:0:0:0:0:0:1', n, n+1, 'null', '2018-05-03 14:02:42', '首页');
                SET n=n+1;
    	    IF n%300000=0 THEN 
    	        SET i=i+1; 
                END IF;
            IF n=3000000 THEN
                LEAVE loopname;
            END IF;
            END LOOP loopname;
    
    	END$$
    
    DELIMITER ;
     
    -- 执行存储过程
    CALL my_insert();
     
    -- 数据插入成功后修改表模式InnoDB 时间稍微久点
     ALTER TABLE `logs_info` ENGINE=INNODB;
     
    

    建完表后和自定义一些插入数据后,数据量有两百多万行数据。这里需要说一下,多次统计数量的速度count(*)比较快,之前了解过count(*)会统计每行中特定某个标志行的id来统计的,而且会保留缓存,所以比较比count(id)快一点
    在这里插入图片描述

    方法一,使用in, 查询

    EXPLAIN  
    
    SELECT d.id FROM logs_info d WHERE htmlname='第一页' AND logurl='/index' AND dt IN('2018-05-01','2018-05-03','2018-05-05');
    
    

    执行多次,发现使用时间大约为2.432秒。如果使用explain做分析,可以看出是使用了全表扫描,共扫描两百多万行。
    in 查询结果
    in 查询结果
    in 的执行情况
    in 执行情况

    方法二,使用or, 查询

    EXPLAIN  
    
    SELECT d.id FROM logs_info d  WHERE htmlname='第一页' AND logurl='/index' AND (dt ='2018-05-01' OR dt='2018-05-03' OR dt='2018-05-05');
    

    执行多次,发现使用时间大约为2.44秒。如果使用explain做分析,可以看出也是使用了全表扫描,共扫描两百多万行。
    or 执行结果
    or 执行结果
    or执行情况
    or 执行情况

    方法三,使用union all, 查询

    EXPLAIN  
    
    SELECT d.id FROM logs_info d WHERE d.htmlname='第一页' AND d.logurl='/index' AND d.dt='2018-05-01'
    UNION ALL
    SELECT d.id FROM logs_info d WHERE d.htmlname='第一页' AND d.logurl='/index' AND d.dt='2018-05-03'
    UNION ALL
    SELECT d.id FROM logs_info d WHERE d.htmlname='第一页' AND d.logurl='/index' AND d.dt='2018-05-05'
    

    用union all进行查询,发现所用时间为2.05秒左右,比上两种情况都要快一点,然后通过explain查看其执行情况,可以看出其使用了索引进行查询,然后查询的行数一共是60万*3 =180万行左右,比上两种的全表扫描行数少,所以速度会快一点。

    union all执行结果,
    union 执行结果
    union all 执行情况
    union all 执行情况

    方法四,代码解决,多线程执行再合并

    EXPLAIN 
    
    SELECT d.id FROM logs_info d WHERE d.htmlname='第一页' AND d.logurl='/index' AND d.dt='2018-05-01'
    
    大约1秒左右
    

    这里看github项目中的代码,后面也会贴出代码。

    代码:

        /**
         * 使用方法四,多线程得到数据,
         *
         * @param request 条件实体类
         * @return 日志数据集合
         **/
        public List<LogPO> getData4(LogRequest request) throws InterruptedException, ExecutionException {
            this.commonSetDt(request);
            CountDownLatch latch = new CountDownLatch(3);
            //设置新的条件
            LogRequest[] requests = new LogRequest[2];
            for (int i = 0; i < requests.length; i++) {
                requests[i] = new LogRequest();
                requests[i].setHtmlname(request.getHtmlname());
                requests[i].setLogurl(request.getLogurl());
            }
            requests[0].setDt(getDateStr(request.getDt(), 2));
            requests[1].setDt(getDateStr(request.getDt(), 4));
            //异步执行
            List<LogPO> list = new ArrayList<>(16);
            Future<List<LogPO>> list1 = asyncService.execMapper(request, latch);
            Future<List<LogPO>> list2 = asyncService.execMapper(requests[0], latch);
            Future<List<LogPO>> list3 = asyncService.execMapper(requests[1], latch);
            //等待5秒还没执行完,直接返回
            latch.await(5, TimeUnit.SECONDS);
            list.addAll(list1.get());
            list.addAll(list2.get());
            list.addAll(list3.get());
            return list;
        }
    

    异步方法类

    /**
     * 异步方法操作类
     *
     * @author zhangcanlong
     * @since 2019/5/8 9:22
     **/
    @Component
    public class AsyncService {
        @Autowired
        private LogMapper logMapper;
    
        /**
         * 异步执行mapper
         *
         * @param request 条件
         * @return 执行返回的实体类
         **/
        @Async("taskExecutor")
        public Future<List<LogPO>> execMapper(LogRequest request, CountDownLatch latch) {
            Long time1 = System.currentTimeMillis();
            AsyncResult<List<LogPO>> asyncResult = new AsyncResult<>(this.logMapper.getLogsByCondition4(request));
            System.out.println("线程名为:" + Thread.currentThread().getName());
            System.out.println("花费时间为:" + (System.currentTimeMillis() - time1));
            latch.countDown();
            return asyncResult;
        }
    }
    
    

    方法五,建立复合索引

    暂时还没测试,速度会快很多,但是要修改数据库表,这里就不测试了

    总结

    建议如果能修改数据库的话,使用方法五,如果不能使用方法四,然后可以经常用一下explain做一下分析,再确定使用哪一种方法。

    遇到的问题:

    1. 刚刚开始写的时候,忘记在spring boot的启动类上加上@EnableAsync注解了,导致多线程刚开始的时候并没有启作用,后来还测试了很久,一直奇怪为什么多线程还能比其他晚,果然写代码需要仔细。

    2. 调用的异步方法,不能为同一个类的方法,简单来说,因为Spring在启动扫描时会为其创建一个代理类,而同类调用时,还是调用本身的代理类的,所以和平常调用是一样的。其他的注解如@Cache等也是一样的道理,说白了,就是Spring的代理机制造成的。参考:https://blog.lqdev.cn/2018/08/17/springboot/chapter-twenty-one/

    3. 在默认情况下,未设置TaskExecutor时,默认是使用SimpleAsyncTaskExecutor这个线程池,但此线程不是真正意义上的线程池,因为线程不重用,每次调用都会创建一个新的线程。可通过控制台日志输出可以看出,每次输出线程名都是递增的。参考:https://blog.lqdev.cn/2018/08/17/springboot/chapter-twenty-one/

    项目GitHub地址:
    https://github.com/KANLON/practice/tree/master/data-optimize

    展开全文
  • 百万数据连表查询优化

    千次阅读 2019-03-25 14:18:34
    交代一下背景,这算是次项目经验吧,属于公司个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 这个SQL查询关联两个...

    本文转自:https://blog.csdn.net/Tim_phper/article/details/78344444

    概述:

    交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 
    这个SQL查询关联两个数据表,一个是攻击IP用户表主要是记录IP的信息,如第一次攻击时间,地址,IP等等,一个是IP攻击次数表主要是记录每天IP攻击次数。而需求是获取某天攻击IP信息和次数。(以下SQL语句测试均在测试服务器上上,正式服务器的性能好,查询时间快不少。)

    准备:

    查看表的行数: 
    这里写图片描述 
    这里写图片描述 
    未优化前SQL语句为:

     
    1. SELECT

    2. attack_ip,

    3. country,

    4. province,

    5. city,

    6. line,

    7. info_update_time AS attack_time,

    8. sum( attack_count ) AS attack_times

    9. FROM

    10. `blacklist_attack_ip`

    11. INNER JOIN `blacklist_ip_count_date` ON `blacklist_attack_ip`.`attack_ip` = `blacklist_ip_count_date`.`ip`

    12. WHERE

    13. `attack_count` > 0

    14. AND `date` BETWEEN '2017-10-13 00:00:00'

    15. AND '2017-10-13 23:59:59'

    16. GROUP BY

    17. `ip`

    18. LIMIT 10 OFFSET 1000

    先EXPLAIN分析一下: 
    这里写图片描述
    这里看到索引是有的,但是IP攻击次数表blacklist_ip_count_data也用上了临时表。那么这SQL不优化直接第一次执行需要多久(这里强调第一次是因为MYSQL带有缓存功能,执行过一次的同样SQL,第二次会快很多。) 
    这里写图片描述
    实际查询时间为300+秒,这完全不能接受呀,这还是没有其他搜索条件下的。 
    那么我们怎么优化呢,索引既然走了,我尝试一下避免临时表,这时我们先了解一下临时表跟group by的使联系:

    查找了网上一些博客分析GROUP BY 与临时表的关系 : 
      1. 如果GROUP BY 的列没有索引,产生临时表. 
      2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表. 
      3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表. 
      4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表. 
      5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表. 
      6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

    其实,9W的临时表并不算多,那么为什么导致会这么久的查询呢?我们想想这没优化的SQL的执行过程是怎么样的呢?

     
    1. 网上搜索得知内联表查询一般的执行过程是:

    2. 1、执行FROM语句

    3. 2、执行ON过滤

    4. 3、添加外部行

    5. 4、执行where条件过滤

    6. 5、执行group by分组语句

    7. 6、执行having

    8. 7、select列表

    9. 8、执行distinct去重复数据

    10. 9、执行order by字句

    11. 10、执行limit字句

    第一种优化:Mysql 是先执行内联表然后再进行条件查询的最后再分组,那么想想这SQL的条件查询和分组都只是一个表的,内联后数据就变得臃肿了,这时候再进行条件查询和分组是否太吃亏了,我们可以尝试一下提前进行分组和条件查询,实现方法就是子查询联合内联查询。 

    这里写图片描述
    这里EXPLAIN看来,只是多了子查询,ROWS和临时表都没有变化。那么我们看看实际的效果呢? 
    这里写图片描述

    可见,取出来的数据完全一模一样,可是优化后效率从原来的330秒变成了0.28秒,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。

     

     第二种优化:这里用的是内联表查询,大家都是知道子查询完全是可以代替内联表查询的,只不过SQL语句复杂了不少,那么我们分析一下这SQL,两个表分表提供了什么? 

    1、IP攻击次数表blacklist_ip_count_data主要提供的指定时间条件查询,攻击次数条件查询后的IP和每个IP符合条件下的具体攻击次数。 
    2、攻击IP用户表blacklist_attack_ip主要是具体IP的信息,如第一次攻击时间,地址,IP等等。 
    那么我们一步步来: 
    1、IP攻击次数表blacklist_ip_count_data获取符合时间条件和攻击次数的IP并且以IP分组: 
    这里写图片描述 
    2、攻击IP用户表blacklist_attack_ip指定具体的IP获取信息: 
    这里写图片描述 
    然后结合在一起: 
    这里写图片描述
    可见,取出来的数据完全一模一样,可是优化后效率从原来的330秒变成了0.28秒,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。 
    我们EXPLAIN了解一下情况: 
    这里写图片描述

     

    展开全文
  • ![图片说明](https://img-ask.csdn.net/upload/201803/02/1519966936_581903.png)![![图片说明](https://img-ask.csdn.net/upload/201803/02/1519966424_539301.png)图片说明]...
  • sql优化问题(百万数据优化方案)   .sql数据库优化方案 1、索引 2、分库分表分区 3、数据库引擎  4、预处理  5、读写分离 1、索引,建立索引是数据库优化各种方案之中成本最低,见效最快的解决方案,...
  • 研究了一天的导入数据终于有了结果。 我这个sql文件在1.5G 左右,700万条数据。 起初用的mysql命令 : mysql -uroot -p123456 test < aaa.sql , 3个小时了才几千条数据 这篇文章希望能帮助大家 1、我们一张...
  • 百万数据的对账优化

    千次阅读 2018-06-30 08:15:40
    原来的流程系统订单量不大的情况下可以这样子处理没有什么异常问题,随着系统订单的增长,对账时间不断增长,远远超过我们能忍的极限,比如说:到早上还没有对帐完,影响第二出结算单的时间,不能做到财务T+1结算...
  • 这两处理后端返回得数据数据量比较大 1就2.3万,并且我们得需求比较特殊相当于一下要画最少10万得点),并且在个画布内画多个折线图(数量不定); 问题:可视化工具得寻找 常年使用echarts,用得比较顺手...
  • 如果是百万数据,我还可以甩锅为服务器性能问题,十万数据查不动,要是汇报让领导升级服务器,估计领导直接让我去跟人事结算工资了。 我是这么设想的: 我:“查不动是服务器性能问题。”领导:“现在服务器...
  • 单核 2G 轻量服务器的配置,近百万数据的分析查询时间仅在 3 秒以内。
  • 给mysql百万条数据的表添加索引

    千次阅读 2021-01-18 21:21:49
    打开mysql 命令行客户端这里我们那可以看到导出的数据文件所存放的默认位置 C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\二. 创建新表创建一张新表与要添加索引的表结构保持一致;CREATE TABLE 表名 LIKE...
  • PHP百万数据导出方案(多csv文件压缩)

    万次阅读 多人点赞 2017-08-25 16:49:36
    概述:最近公司项目要求把数据除了页面输出也希望有导出功能,虽然之前也做过几个导出功能,但这次数据量相对比较大,差不多一天数据就20W条,要求导7天或者30天,那么数据量就轻松破百万了甚至破千万,因此开发的...
  • 微信公众号文章数据是互联网最有价值的数据,各大小厂商都在与腾讯微信开发组斗智斗勇,爬取和反爬技术在这几年不断登上新台阶。本大神于试过了GITHUB上面前5的微信公众号API,结果不如人意,开源的毕竟还是弱势...
  • 但是要懂得数据库也并非是件易事,首先各种数据库的名字已经足够让人迷糊了,什么oracle、mysql、obase这些,加上还要懂得数据存储,还要会写sql语句,这些头疼的事情不禁会让没有数据库基础的人抓狂:哪,放过...
  • 一文读懂数据挖掘建模预测

    万次阅读 多人点赞 2022-06-12 21:47:36
    数据挖掘就是从大量的、不完全的、有噪声的、模糊的、随机的实际应用数据中,提取隐含在其中的、人们事先不知道的、但又是潜在有用的信息和知识的过程。听起来比较抽象,我们举个例子。傍晚小街路面上沁出微雨后的...
  • 近一年,数据隐私泄露事件频发,涉及面广,影响力大,企业因此陷入数据保护合规与社会舆情压力的双重危机。近日,我们根据网上已公开的数据泄露受影响人数,梳理了近一年来十大数据泄密事件,可供读者参考。据数据...
  • 如果服务器端批次的数据大于该值 (50m)仍然可以拉取回来这批数据,因此,这不是个绝 对最大值。批次的大小受 message.max.bytes (broker config)or max.message.bytes (topic config)影响 max.poll....
  • 百万数据查询优化之in

    千次阅读 2017-05-10 14:21:15
    1)使用IN查询连续的数,没毛病,数度非常快 2)当使用IN查询不连续的数 3)使用UNION优化
  • 编译 | 核子可乐、钰莹 最新论文指出,AI 行业正在残酷压榨刚刚兴起的全球零工经济体系。 现代人工智能依赖各种...在数据标注行业流行着句话,“有多少智能,就有多少人工”。数据标注是人工智能发展中至关重要的
  • 利用布隆处理百万数据

    万次阅读 2018-07-31 20:31:16
    这两遇到了数据处理的问题 是使用Java将三个不定数量(至少百万)的map集合中取得数据的交集 这里可以使用布隆进行数据处理   效果是非常好的.,这里用的是整型 所以速度也比较快,如果是字符串的话,可以...
  • 在MySQL上构造个100W条记录的表,要求有日期类型 存放5年的数据。 表结构 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------...
  • 数据中台怎么选型?终于有人讲明白了

    万次阅读 多人点赞 2022-01-07 14:07:21
    数据中台怎么选型?终于有人讲明白了
  • 刚开始的情况是条sql语句将数据库中的所有数据一次全部查询出来,每条放到个map中,最后放在list中,刚开始写的代码由于数据不多,没出现问题,后来数据达到百万后,客服在导出数据的时候直接内存溢出,于是...
  • MYSQL百万数据分页查询优化实战

    万次阅读 2020-04-07 15:47:58
    最近项目中,需要将公司老的订单日志数据迁移到新的ElasticSearch统一日志存储,我们老日志数据是分库分表存储在mysql数据库中(按分表),单表数据量在500w左右,本人就写了个小程序负责mysql到es的数据迁移,...
  • 近期,中国科学院空信息创新研究院研究团队和国际摄影测量与遥感协会合作,构建了一套目前全球规模最大的遥感图像细粒度目标识别数据集,并面向全球公开发布。 02项目目标 ISPRS在高分辨率卫星图像中进行目标...
  •  输入若干个float数字(百万级以上) ,编写个算法从中取出指定数量(100个以内)的最大的数字。 我们先分析一下这道题,从堆数字里取出几个最大的数,以我们通常的思想去考虑,首先想到的是对这堆数字进行倒序...
  • 、数据库访问优化的五个法则  在实际开发,我们主要是需要对SQL语句进行优化,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?根据木桶原理可以知道,最慢的设备往往是性能瓶颈。...
  • 我最近发现很多人都走进了这样个误区:觉得业务数据分析是专业的数据分析岗位的人才需要做的事情,业务人员只需要给他们提需求就可以了。 但实际上业务人员一点数据分析都不会就是只会打仗,不会算账,缺乏了统筹...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 62,044
精华内容 24,817
关键字:

一天百万数据