精华内容
下载资源
问答
  • 千万级数据处理

    千次阅读 2018-11-12 22:23:06
    千万级数据处理 前言     近日一次版本上线时,涉及到千万级数据处理,因一时疏漏造成Oracle数据库回滚段爆掉,造成版本重复发布。  &...

    千万级数据处理

    前言

        近日一次版本上线时,涉及到千万级数据处理,因一时疏漏造成Oracle数据库回滚段爆掉,造成版本重复发布。

        系统日常运行中,产生部分历史数据,这些数据不能删除。日积月累情况下,出现超级大的数据量。

    • 需要将客户信息历史表中客户性别为女性,出生日期在90年以前的客户存放至另一张表。目前客户信息历史表的数据量高达9000多万。

    如下SQL在执行过程:耗时40多分钟,后续变更处理方式后,耗时4-5分钟。数据库回滚段使用5G左右空间。

    --执行SQL如下,table_cust_history该表数据量高达9000多万。
    insert into table_temp
    select ... from table_cust_history
     where sex=''
       and birthday < ''; 
       
    SQl分析:
    1. 首先分析查询,因数据量大,查询条件必须走索引,才能保证查询速度
    2. 耗时40分钟的原因是,我们在创建table_temp表的时候,惯性思维给该表加的相应字段的索引,导致没插入一条数据,索引都要变动,耗时就上去了
    3. 正确的做法应该为:删除索引(主键索引要保留),插入数据,再加入索引。最后加索引耗时仅需几分钟。
    
    --执行如下删除SQl  删除1150万左右的数据,耗时20分钟 
    delete from table_cust_history
    where exists( select 1 from table_temp where '条件' )
    
    SQL分析:两张表的关联条件必须要有索引,
    
    耗时比上面SQL长,是因为这个SQL要查询多次,子查询的条件查询,删除时条件查询。
    
    

    因业务逻辑更新,需要初始化表中的数据。需要初始化的数据为4000多万。
    执行SQl更新字段包含一个带索引的字段。耗时40分钟,回滚段使用了19-20G。

    总结:索引不仅仅提升了查询的效率,同时也会降低大数据量存储更新的效率

    1. 若遇见千万级的数据查询,一定要使用索引字段限制数据量。
    2. 大数据量更新,插入数据时,建议删除无关紧要的索引,等数据操作完毕后,再加入索引。
    3. 操作千万级数据时,应提前看一下数据库回滚段设置大小。如果可以尽量大些。

    千辛万苦,熬到半夜,吸取经验,记得教训。

    希望能给前赴后继的小伙伴带来帮助。

    展开全文
  • MySQL千万级数据处理

    万次阅读 多人点赞 2018-08-14 17:51:35
    目录 第一篇,优化篇 第二篇,案例篇 第一篇,优化篇 ...除非单表数据未来会...而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量: 字段 1、尽量使用TINYINT、SMALLIN...

    目录

     

    第一篇,优化篇

    第二篇,案例篇


    第一篇,优化篇

    单表优化

    除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量:

    字段

    1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED

    2、VARCHAR的长度只分配真正需要的空间

    3、使用枚举或整数代替字符串类型

    4、尽量使用TIMESTAMP而非DATETIME,

    5、单表不要有太多字段,建议在20以内

    6、避免使用NULL字段,很难查询优化且占用额外索引空间

    7、用整型来存IP

    索引

    索引的种类:

    1、主键索引 (把某列设为主键,则就是主键索引)

    2、唯一索引(unique) (即该列具有唯一性,同时又是索引)

    3、index (普通索引)

    4、全文索引(FULLTEXT)

    5、复合索引(多列和在一起)

    索引建立注意事项:

    1、索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描

    2、应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描如:select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num = 0

    3、值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段

    4、字符字段只建前缀索引

    5、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

    6、应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

    7、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

    8、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num = 10 or num = 20

    可以这样查询: 

    select id from t where num = 10 

    union all 

    select id from t where num = 20

    9、下面的查询也将导致全表扫描:select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

    10、in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3) 

    对于连续的数值,能用 between 就不要用 in 了: 

    select id from t where num between 1 and 3

    11、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num = @num 

    可以改为强制查询使用索引: 

    select id from t with(index(索引名)) where num = @num

    12、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。如:select id from t where num / 2 = 100 

    select id from t where substring(name, 1 ,3) = ’abc’查询name以abc开头的id列表

    分别应改为: 

    select id from t where num = 100 * 2

    select id from t where name like ‘abc%’ 

    13、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    14、很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b) 

    用下面的语句替换: 

    select num from a where exists(select 1 from b where num = a.num)

    引擎

    目前广泛使用的是MyISAM和InnoDB两种引擎:

    MyISAM:

    MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

    • 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
    • 不支持事务
    • 不支持外键
    • 不支持崩溃后的安全恢复
    • 在表有读取查询的同时,支持往表中插入新纪录
    • 支持BLOB和TEXT的前500个字符索引,支持全文索引
    • 支持延迟更新索引,极大提升写入性能
    • 对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
    • 创建一张表,对会应三个文件, *.frm 记录表结构, *.myd 数据, *.myi 索引文件

    InnoDB:

    InnoDB在MySQL 5.5后成为默认索引,它的特点是:

    • 支持行锁,采用MVCC来支持高并发
    • 支持事务
    • 支持外键
    • 支持崩溃后的安全恢复
    • 不支持全文索引
    • 创建一张表,对会应一个文件 *.frm,数据存放到ibdata1

    总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表

    其他注意事项:

    1、AND型查询要点(排除越多的条件放在前面):假设要查询满足条件A,B和C的文档,满足A的文档有4万,满足B的有9K,满足C的是200,那么应该用C and B and A 这样只需要查询200条记录。

    2、OR型查询要点(符合越多的条件放在前面):OR型查询与AND查询恰好相反,匹配最多的查询语句放在最前面。

    3、查询数据不建议使用 select * from table ,应用具体的字段列表代替“*”,不要返回用不到的无关字段,尤其是大数据列。

    4、在分页查询中使用 limit关键字时,应重复考虑使用索引字段来筛选来避免全表扫描,如:

    select c1, c2, c3 from table order by id asc limit 100, 100

    应尽量配合where条件来使用(大数据量情况查询效率提升10倍):

    select c1, c2, c3 from table where id > 100 order by id asc limit 0, 100

    5、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先 create table,然后再insert。

    6、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。但是,避免频繁创建和删除临时表,以减少系统表资源的消耗。

    7、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。因此,使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

    8、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

    9、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    10、为提高系统并发能力,应尽量避免大事务操作,尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

    参数调优:

    wait_timeout:

    数据库连接闲置时间(长连接),闲置连接会占用内存资源。可以从默认的8小时减到半小时。

    max_user_connection:

    最大连接数,默认为0(无上限),最好设一个合理上限。

    thread_concurrency:

    并发线程数,设为CPU核数的两倍。

    key_buffer_size:

    索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大。对于内存4G左右,可设为256M或384M,通过查询show status like 'key_read%',保证key_reads / key_read_requests在0.1%以下最好

    innodb_buffer_pool_size:

    缓存数据块和索引块,对InnoDB表性能影响最大。通过查询 show status like 'Innodb_buffer_pool_read%',

    保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests 越高越好

    read_buffer_size:

    MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能

    sort_buffer_size:

    MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小

    其他参数

    读写分离

    也是目前常用的优化,从库读主库写,一般不要采用双主或多主引入很多复杂性,尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离。

    分库分表

    水平拆分

    垂直拆分

    升级硬件

    根据MySQL是CPU密集型还是I/O密集型,通过提升CPU和内存、使用SSD,都能显著提升MySQL性能。

    缓存应用

    MySQL内部:

    在系统调优参数介绍了相关设置

    数据访问层:

    比如MyBatis针对SQL语句做缓存,而Hibernate可以精确到单个记录,这里缓存的对象主要是持久化对象 Persistence Object

    应用服务层:

    这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略,这里缓存的对象是数据传输对象Data Transfer Object

    Web层:针对web页面做缓存

    用户端的缓存:

    可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现,目前主要有两种方式:1、直写式(Write Through):在数据写入数据库后,同时更新缓存,维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单,同步好,但效率一般。2、回写式(Write Back):当有数据要写入数据库时,只会更新缓存,然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂,需要较多的应用逻辑,同时可能会产生数据库与缓存的不同步,但效率非常高。


    第二篇,案例篇

    实际案例:

    比如针对 1000w 条老数据加密处理

    解决的思路:

    查询优化 + 内存调优 + 分批处理 + 高效数据源 + 多线程(线程池) + 反复调试

    考虑客观因素:

    硬件设施、网络宽带

    假设1000w,分配20个线程,每页1万条

    则分页总数:1000 / 1 = 1000页

    线程平均处理分页数:(int)Math.floor(1000 / 20) = 50页

    线程处理最大分页数:50页 + 1000 % 20 = 50页

    则第一个线程处理 1 - 20页,

    第二个线程处理 21- 40页,

    第三个线程处理 41- 60页,

    ...

    依次内推,第三十个线程,处理981 - 1000页

    代码片段:

        /**
         * maysql 多线程 + 连接池,分页查询 + 批量更新示例
         * 注意:limit 参数不支持使用占位符?
         *
         * 10个线程,60万条数据,每页1w条,进行批量更新总耗时:53558毫秒,约0.90分钟(Where条件 + Limit N )
         * 20个线程,60万条数据,每页1w条,进行批量更新总耗时:53558毫秒,约0.89分钟(Where条件 + Limit N )
         * 20个线程,200万条数据,每页1w条,进行批量更新总耗时:160132毫秒,约2.67分钟(Where条件 + Limit N )
         * 40个线程,200万条数据,每页1w条,进行批量更新总耗时:199220毫秒,约3.32分钟(Where条件 + Limit N )
         *
         * 截止4月27日finance_ant_loan数据库数据:
         * LendingDetail, 总记录数 8729
         * LoanDetail, 总记录数 550417
         *
         * 截止4月27日finance_jd_loan数据库数据:
         * CUS, 总记录数 1006179
         * Loan,总记录数 32395990
         */
        @Test
        public void testMultiThreadBatchUpdate2(){
            Long beginTime = System.currentTimeMillis();
            int pageSize = 10000;
            int threads = 20;
            int resultCount = findCount();
    
            if (resultCount <= 0){
                log.info("未找到符合条件的记录!");
                return;
            }
    
            int pageTotal = (resultCount % pageSize == 0) ? (resultCount / pageSize) :  ((int)Math.floor(resultCount / pageSize) + 1);
            log.info("查询出数据库总计录数:{}", resultCount);
            log.info("每页数量:{}", pageSize);
            log.info("分页总数:{}", pageTotal);
    
            threads = getActualThreads(pageTotal, threads);
            int avgPages = (int)Math.floor(pageTotal / threads);
            int restPages = pageTotal % threads;
            log.info("子线程数:{}", threads);
            log.info("子线程平均处理分页数:{}", avgPages);
            log.info("子线程处理最大分页数:{}", avgPages + restPages);
    
            MultiThreadHandler handler = new MultiParallelThreadHandler();
            for (int i = 0; i < threads; i++){
                int fromPage = i * avgPages + 1;
                int endPage = i * avgPages + avgPages;
    
                if (i == threads - 1) {
                    endPage = endPage + restPages;
                }
    
                String threadName = "thread" + (i + 1);
                log.info("Query child thread:{} process paging interval: [{}, {}]", threadName, fromPage, endPage);
                handler.addTask(new TestThread2(fromPage, endPage, pageSize));
            }
    
            try {
                handler.run();
            } catch (ChildThreadException e) {
                log.error(e.getAllStackTraceMessage());
            }
    
            log.info("【分页查询+批量更新】结束,受影响总记录数:{},总耗时:{}毫秒", resultCount, System.currentTimeMillis() - beginTime);
        }
    
        private int findCount(){
            Connection connection = AntConnPool.getConnection();
            PreparedStatement preparedStatement;
    
            try {
                preparedStatement = connection.prepareStatement("SELECT COUNT(1) FROM test2 WHERE 1 = 1");
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()){
                    return resultSet.getInt(1);
                }
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }finally {
                ConnMgr.closeConn(connection);
            }
            return 0;
        }
    
        /**
         * @description 获得实际线程数
         * @author Zack
         * @date 15:28 2018/5/7
         * @param pageTotal
         * @param threads
         * @return int
         */
        private int getActualThreads(final int pageTotal, final int threads){
            if ((int)Math.floor(pageTotal / threads) < 1 && threads > 1){
                return getActualThreads(pageTotal, threads - 1);
            }
            return threads;
        }
    /**
     * @description 多线程处理分页
     * @author Zack
     * @date 2018/4/27
     * @version 1.0
     */
    @Slf4j
    public class TestThread2 extends MysqlParallelThread{
    
        public TestThread2(int fromPage, int endPage, int pageSize){
            super(fromPage, endPage, pageSize);
        }
    
        @Override
        public void run() {
            log.info("Query child thread:{} process paging interval: [{}, {}] started.", Thread.currentThread().getName(), getFromPage(), getEndPage());
            Long beginTime = System.currentTimeMillis();
            int maxId = 0;
            int fromIndex = (getFromPage() - 1) * getPageSize();
            Connection connection = AntConnPool.getConnection();
    
            try{
                for (int pageNo = getFromPage(); pageNo <= getEndPage(); pageNo++){
                    if (maxId != 0){
                        fromIndex = 0;
                    }
                    maxId = batchUpdate(findList(connection, fromIndex, maxId), pageNo);
                }
            }catch (Exception e){
                throw new RuntimeException(Thread.currentThread().getName() + ": throw exception");
            }finally {
                ConnMgr.closeConn(connection);
                log.info("Query child thread:{} process paging interval: [{}, {}] end. cost:{} ms.", Thread.currentThread().getName(), getFromPage(), getEndPage(), (System.currentTimeMillis() - beginTime));
            }
        }
    
        private ResultSet findList(Connection connection, int fromIndex, int maxId){
            try {
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, name, total FROM test2 WHERE id > ? ORDER BY id ASC limit "+fromIndex+", " + getPageSize(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                preparedStatement.setInt(1, maxId);
                return preparedStatement.executeQuery();
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }
            return null;
        }
    
        private int batchUpdate(ResultSet resultSet, int pageNo) {
            Long beginTime = System.currentTimeMillis();
            Connection connection = AntConnPool.getConnection();
            PreparedStatement preparedStatement;
    
            try {
                connection.setAutoCommit(false);
                preparedStatement = connection.prepareStatement("UPDATE test2 SET name = ?, total = ? WHERE id = ?");
    
                if (Objects.isNull(resultSet) || resultSet.wasNull()){
                    log.info("查询第{}页数据为空!", pageNo);
                    return 0;
                }
    
                while (resultSet.next()){
                    // 加密处理
                    preparedStatement.setString(1, SecUtil.encryption(resultSet.getString(2)));
                    preparedStatement.setInt(2, resultSet.getInt(3) + 1);
                    preparedStatement.setInt(3, resultSet.getInt(1));
                    preparedStatement.addBatch();
                }
    
                int[] countArray = preparedStatement.executeBatch();
                connection.commit();
    
                // 游标移至最后一行
                resultSet.last();
                int maxId = resultSet.getInt(1);
                log.info("子线程{}批量更新MYSQL第{}页结束,maxId:{}, 受影响记录数:{},耗时:{}毫秒", Thread.currentThread().getName(), pageNo, maxId, countArray.length, System.currentTimeMillis() - beginTime);
                return maxId;
            }catch (Exception e){
                e.printStackTrace();
                log.error("数据库操作异常!!!");
            }finally {
                ConnMgr.closeConn(connection);
            }
            return 0;
        }
    }

    总结

    使用多线程合理分配执行任务,避免数据重复执行和漏执行。

     

    扩展阅读

    https://dev.mysql.com/doc/refman/8.0/en/optimization.html

     

     

     

     

     

     

     

     

     

     

    展开全文
  • mysql千万级数据量优化(详)

    千次阅读 2019-07-13 10:10:55
    假设用户表有一百万用户。也就是1000000.num是主键 1:对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by 涉及的列上创建索引。 因为:索引对查询的速度有着至关重要的影响。 2:尽量避免在where...

    在这里插入图片描述
    1:查询语句where 子句使用时候优化或者需要注意的

    2:like语句使用时候需要注意

    3:in语句代替语句

    4:索引使用或是创建需要注意

    假设用户表有一百万用户量。也就是1000000.num是主键

    1:对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by 涉及的列上创建索引。

    因为:索引对查询的速度有着至关重要的影响。

    2:尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。

    例如:select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值,然后在进行查询。

    sql如下:select id from user where num=0;

    (考虑如下情况,假设数据库中一个表有106条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取104个页面,如果这104个页面在磁盘上随机分布,需要进行104次I/O,假设磁盘每次I/O时间为10ms(忽略数据传输时间),则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引,则只需要进行log100(10^6)=3次页面读取,最坏情况下耗时30ms。这就是索引带来的效果,很多时候,当你的应用程序进行SQL查询速度很慢时,应该想想是否可以建索引)

    3:应尽量避免在where子句中使用!=或者是<>操作符号。否则引擎将放弃使用索引,进而进行全表扫描。

    4:应尽量避免在where子句中使用or来连接条件,否则导致放弃使用索引而进行全表扫描。可以使用 union 或者是 union all代替。

    例如: select id from user where num =10 or num =20 这个语句景导致引擎放弃num索引,而要全表扫描来进行处理的。

    可以使用union 或者是 union all来代替。如下:

    select id from user where num = 10;

    union all

    select id from user where num =20;

    (union 和 nuion all 的区别这里就不赘述了)

    5:in 和 not in 也要慎用,否则将会导致全表扫描。

    in 对于连续的数组,可以使用between …and.来代替。

    例如:

    select id from user where num in (1,2,3);

    像这样连续的就可以使用between …and…来代替了。如下:

    select id from user where num between 1 and 3;

    6:like使用需注意

    下面这个查询也将导致全表查询:

    select id from user where name like ‘%三’;

    如果想提高效率,可以考虑到全文检索。比如solr或是luncene

    而下面这个查询却使用到了索引:

    select id from user where name like ‘张%’;

    7:where子句参数使用时候需注意

    如果在where子句中使用参数,也会导致全表扫描。因为sql只会在运行时才会解析局部变量。但优化程序不能将访问计划的选择推迟到运行时;必须在编译时候进行选择。然而,如果在编译时建立访问计划,变量的值还是未知大,因而无法作为索引选择输入项。

    如下面的语句将会进行全表扫描:

    select id from user where num = @num

    进行优化,我们知道num就是主键。是索引。

    所以可以改为强制查询使用索引:

    select id from user where (index(索引名称)) where num = @num;

    8:尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    例如:select id from user where num/2=100

    应修改为:

    select id from user where num = 100*2;

    9:尽量避免爱where子句中对字段进行函数操作,这将导致引擎放弃索引,而进行全表扫描。

    例如:

    select id from user substring(name,1,3) = ‘abc’ ,这句sql的含义其实就是,查询name以abc开头的用户id

    (注:substring(字段,start,end)这个是mysql的截取函数)

    应修改为:

    select id from user where name like ‘abc%’;

    10:不要在where子句中的"="左边进行函数、算术运算或是使用其他表达式运算,否则系统可能无法正确使用索引

    11:复合索引查询注意

    在使用索引字段作为条件时候,如果该索引是复合索引,那么必须使用该索引中的第一个字段作为条件时候才能保证系统使用该所以,否则该索引将不会被使用,并且应尽可能的让字段顺序和索引顺序一致。

    12:不要写一些没意义的查询。

    例如:需要生成一个空表结构和user表结构一样(注:生成的新 new table的表结构和 老表 old table 结构一致)

    select col1,col2,col3…into newTable from user where 1=0

    上面这行sql执行后不会返回任何的结果集,但是会消耗系统资源的。

    应修改为:

    create table newTable (…)这种语句。

    13:很多时候用exists 代替 in是一个很好的选择。

    比如:

    select num from user where num in(select num from newTable);

    可以使用下面语句代替:

    select num from user a where exists(select num from newTable b where b.num = a.num );

    14:并不是所有索引对查询都有效,sql是根据表中数据进行查询优化的,当索引lie(索引字段)有大量重复数据的时候,sql查询可能不会去利用索引。如一表中字段 sex、male、female 几乎各一半。那么即使在sex上创建了索引对查询效率也起不了多大作用。

    15:索引创建需注意

    并非索引创建越多越好。索引固然可以提高相应的查询效率,但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑,视情况而定。一个表中所以数量最好不要超过6个。若太多,则需要考虑一些不常用的列上创建索引是否有必要。

    展开全文
  • mysql千万级数据量更新操作

    千次阅读 2020-01-08 11:08:58
    首先对于千万级数据更新,如果一次性更新,肯定导致卡死,要关注内存变化,注意看一下内存。 #### 可以尝试写一个存储过程,一次更新2000行,可以快很多。 ...

    首先对于千万级数据更新,如果一次性更新,肯定导致卡死,要关注内存变化,注意看一下内存。

    ####    可以尝试写一个存储过程,一次更新2000行,可以快很多。

    展开全文
  • 在Discuz!NT的企业版设计过程中,处理大数据表一直是一个让人头疼的问题
  • MySQL千万级数据量优化方案

    千次阅读 2020-02-19 21:52:30
    前言 ...千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区。除此之外,还有其他的思路和解决方案。根据本人多年的工作经验,做了如下总结。 方案 ...
  • 千万级数据量系统优化实践

    千次阅读 2020-05-04 10:31:44
    关于针对该系统的优化,我主要做了以下几点: 目录 数据库SQL语句调优,mybatis批量插入,建立索引 ...使用异步处理,校验完核心数据即可返回前台操作完成,并异步跑后续操作 多线程处理海量运算
  • 千万级数据查询处理方案

    千次阅读 2019-01-31 11:29:21
    因为单表数据量已经达到了千万级,再多表关联已经达到亿级数据了。之前没有接触过这么大数据量处理,所以刚接到这个项目时有点脑瓜子疼。 最开始我还是想通过优化sql与索引方式来提高查询的性能,但是效果并不...
  • 如图:numberrecord 表 3600W数据量。 需求是去重掉 time 与 number一致的数据。 我用的去重插入复制表的方法。 目前已经执行12个小时了,一夜没睡。 试过用java去处理,多线程分页查询去重,jvm直接崩溃。 !...
  • 千万级数据量的插入操作(MYSQL)

    千次阅读 2019-09-28 11:03:58
    需要从数仓同步一张大表,数据总量大概三千多万,接近四千万的样子,当遇到这种数据量的时候,综合考虑之后,当前比较流行的框架都不能满足于生产需求,使用框架对性能的损耗过于严重,所以有了以下千万级数据量的...
  • 采用POI、JXL框架导出CVS文件,支持千万级数据导出,无内存溢出,自己项目中使用中。
  • spring JdbcTemplate实现千万级数据处理业务代码问题总结 公司最近有需求,需要处理一批千万级别的数据,不过是一次性的,之前没碰到过,开发的过程中遇到不少问题,这里记录一下代码,主要使用的是spring-...
  • Oracle千万级以上数据量查询速度慢问题解决,生产环境查询优化
  • MYSQL千万级数据量的优化方法积累

    千次阅读 2017-03-01 22:21:54
    30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。 ==================================== 1、分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重...
  • 构造测试数据 方案演进 方案一:普通分页 分析: 方案二:普通分页+分页锚点 分析 方案三(最终方案):中间表分页+分页锚点+自连接 分析: 环境参数 硬件 内存 1G ...
  • 文章出处:...标题所说的是千万级数据量也并不是一千万条微博信息而已,而是千万级订阅关系之间发布。在看 我这篇文章之前,大多数人都看过s
  • 数据量有几百万的数据。还有一张表,tm_bus_realtime_his历史表 进行了分区。每10天一分区。每个月有3个分区。整个历史表有几亿的数据量。每个分区有千万条数据。 问题描述:  在从历史表的分区中,查询数据时,...
  • 数据库 SQL千万级数据规模处理概要

    千次阅读 2015-03-11 09:54:20
    我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。...主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,
  • SQL千万级数据规模处理概要

    千次阅读 2010-10-05 10:14:00
    主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,但是都要先取消索引,或者先建立表,导入数据后,再建立索引。 必要时处理完,统计完后,就备份到磁带或者其他介
  • Python处理千万级数据

    千次阅读 2018-08-05 15:17:05
    从别人的论文里找到要用的数据的原始数据自己做过滤 捣鼓了两天觉得代码太慢开始用pandas做处理 不得不说最大的感触就是 pandas 以及numpy在一定程度上在还原MATLAB 比如MATLAB中利用逻辑值取数、元素的用法,...
  • 数据信息的存储处理方式不同.(如果存储引擎是MyISAM的,则创建一张表,对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1)   对于 MyISAM 数据库,需要定时清理 optimize ...
  • 关于插入: 宏观上:建二个表,其中一个表不建主键,不键索引。只记录,到了晚上,在把这个...一、使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快20倍。 二、使用多个值表的 INSERT 语句 ,可以大大
  • 数据库SQL千万级数据规模处理概要

    千次阅读 2009-07-09 13:03:00
    我在前年遇到过过亿条的数据。以至于一个处理过程要几个小时的。后面慢慢优化,查找一些经验文章。才学到了一些基本方法。...主要是考虑实际的数据量而定。当你创建一个新表时,可能这个表需要有索引,但是都要先
  • 数据信息的存储处理方式不同.(如果存储引擎是MyISAM的,则创建一张表,对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1)   对于 MyISAM 数据库,需要定时清理 optimize ...
  • 怎样对千万级甚至亿级数据量排序

    千次阅读 2018-07-24 17:10:00
    有最多1000万条不同的整型数据存在于硬盘的文件中(数据不超过最大值),如何在1M内存的情况下对其进行尽可能快的排序。 数据特征:单个数据<=1000万、不同的(没有重复)、整型(int,4B) 要求:1M内存、...
  • 数据库处理千万级数据的解决方案

    千次阅读 2016-03-29 16:21:07
    解决千万级数据的存储
  • 字节跳动面试官问我海量数据分页如何展示
  • Mysql千万级数据量查询优化

    万次阅读 多人点赞 2018-03-15 17:38:21
    30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。 今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 73,438
精华内容 29,375
关键字:

千万级数据量处理