首页
学习
活动
专区
圈层
工具
发布

MySQL优化!记一次关于对十亿行的足球数据表进行分区!

全世界每天玩的数百场游戏中的每一场都有数千行。在短短几个月内,我们应用程序中的 Events 表就达到了 50 亿行! 通过了解足球专家如何查询数据,我们可以对数据库进行智能分区。...当我们开始对 Events 表执行繁重的查询时,真正的挑战出现了。但在深入研究之前,让我们看看事件表是什么样子的: 如您所见,它不涉及很多列,但请记住,出于保密原因,我不得不省略其中一些。...百万行表很慢 如果您曾经处理过包含数亿行的表,您就会知道它们天生就很慢。您甚至无法想到在如此大的表上运行 JOIN。然而,您可以在合理的时间内执行 SELECT 查询。...为什么传统分区可能不是正确的方法 在对我们所有的最大表进行分区之前,我们在MySQL 官方文档和有趣的文章中都研究了这个主题。...优点 在最多包含 50 万行的表上运行查询比在 50 亿行的表上运行性能要高得多,尤其是在聚合查询方面。 较小的表更易于管理和更新。添加列或索引在时间和空间方面甚至无法与以前相比。

1.6K40
  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    HIVE基础命令Sqoop导入导出插入表问题动态分区表创建HIVE表脚本筛选CSV中的非文件行GROUP BYSqoop导出到MySQL字段类型问题WHERE中的子查询CASE中的子查询

    TABLE invoice_lines ADD COLUMNS (new_col2 INT COMMENT '内容'); // 清空表,比delete快很多,在mysql中会连索引记录都清空。...; Hive 创建外部表,仅记录数据所在的路径, 不对数据的位置做任何改变; 在删除表的时候,内部表的元数据和数据会被一起删除, 而外部表只删除元数据,不删除数据。...和数据导入相关 Hive数据导入表情况: 在load data时,如果加载的文件在HDFS上,此文件会被移动到表路径中; 在load data时,如果加载的文件在本地,此文件会被复制到HDFS的表路径中...temp.source_sys_key = t0.source_sys_key AND temp.legal_company = t0.legal_company ) where temp.jobid = '106'; // 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中...WHERE中的子查询 在hive中的子查询会有各种问题,这里的解决方法是将子查询改成JOIN的方式 先看一段在MySQL中的SQL,下不管这段SQL从哪来的,我也不知道从哪里来的 SELECT

    18.5K20

    数据库优化策略(草稿)

    ,可能一次锁住很多数据,占满整个事务日志,耗尽系统资源,阻塞很多小的但重要的查询. 2.分解大连接查询 将一个大连接查询分解成对每一个表进行一次单表查询,然后在应用程序中进行关联....对于连接查询,如果其中一个表发生变化,那么整个查询缓存就无法使用了.而分解后的多个查询,即使其中一个表的查询发生变化,那么对于其他表的查询缓存依然可以使用....当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢. 2.增加中间表 对于经常需要联合查询的表,可以建立中间表以提高查询效率. 3.优化插入记录的速度 插入记录时,影响插入速度的主要是索引,唯一性校验...,一次插入记录条数等.根据这些情况可以分别进行优化....配置多处理器,MySQL是多线程的数据库,多处理器可同时执行多个线程. 2.优化MySQL参数 优化MySQL参数可以提高资源利用率,从而提高服务器性能.

    1.1K10

    MySQL(九)插入、更新和删除

    一、insert insert:用来插入(或添加)行到数据库中,常见方式有以下几种: ①插入完整的行; ②插入行的一部分; ③插入多行; ④插入某些查询的结果; 1、插入完整的行 例如:insert into...,则可以在insert操作中省略某些列,列必须满足这两个条件(1.改列定义为允许null值;2.在表定义中给出默认值【如果不给出值,则使用默认值】); ③数据库被多个用户访问,一般检索是最重要的,可以通过在...insert和into中间添加关键字low priority,指示MySQL降低insert语句的优先级(同样适用于update和delete语句); 2、插入多个行 例如:insert into usertable...语句后面输入一次列名就可以(单个insert语句由多组值,每组值用一堆圆括号包含,用逗号隔开) PS:MYSQL用单条insert语句处理多个插入比使用多个insert语句快。...PS:如果用update更新多个行,且更新中一行或多行出现错误,则update操作将被取消(错误发生前执行的动作将被回滚到最初状态。

    3.3K20

    【MySQL】MySQL中表的增删改查——(基础篇)(超详解)

    2.2非指定插入 SQL执行语句:insert into 表名 values(值,值,值......); 代码实例如下: mysql> insert into student values(1,'孙悟空...2.3指定列插入 SQL执行语句:insert into 表名 (列名,列名....)values (值,值....); 代码实例如下: mysql> insert into student(id,name...一般来说,在插入数据时,就用插入多行数据的操作,因为MySQL是服务器--客户端的程序,每次进行插入时都要进行数据的响应到客户端,所以一次性插入所有数据是提高效率的操作。 ️...,判定列为一项时,如果这一列存在相同的数据,那么既删除其中一个; 若判定为多个列时,就要同时满足每个其中一行与另一行完全相同才可以进行去重,否则不去重; 3.6 查询时进行排序 SQL执行语句:select...注意: • 这里的删除表示的是删除一行数据,而不是一个数据; • 当没有条件时,就会删除整张表的内容,但是表还在,而用drop 时就是数据和表这个框架• 一起删除。 ️

    2.6K10

    MySQL并发控制:锁机制

    当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个线程读表的同时,另一个线程从表尾插入记录。这也是MySQL的默认设置。...3、事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。...UPDATE可能会导致新的普通索引的插入。当新的索引插入之前,会首先执行一次重复索引检查。在重复检查和插入时,更新操作会对受影响的二级索引记录采用共享锁定(S锁)。...MySQL死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。...普通的Gap Lock 不允许 在 (上一条记录,本记录) 范围内插入数据 插入意向锁Gap Lock 允许 在 (上一条记录,本记录) 范围内插入数据 插入意向锁的作用是为了提高并发插入的性能, 多个事务

    3.4K21

    2024年java面试准备--mysql(4)

    半同步复制: 当主库每提交一个事务后,不会立即返回,而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端,通过一份在主库的Binlog,另一份在其中一个从库的Relay-log...其他问题 MySQL有哪些锁 基于粒度: 表级锁:对整张表加锁,粒度大并发小 行级锁:对行加锁,粒度小并发大 间隙锁:间隙锁,锁住表的一个区间,间隙锁之间不会冲突只在可重复读下才生效,解决了幻读 基于属性...MySQL 删除自增 id,随后重启 MySQL 服务,再插入数据,自增 id 会从几开始? innodb 引擎: MySQL8.0前,下次自增会取表中最大 id + 1。...原理是它将最大id记录到数据文件里了 MySQL插入百万级的数据如何优化?...(1)一次sql插入多条数据,可以减少写redolog日志和binlog日志的io次数(sql是有长度限制的,但可以调整) (2)保证数据按照索引进行有序插入 (3)可以分表后多线程插入 Mybatis

    64340

    第23章、存储程序和视图

    触发器定义为在语句插入,更新或删除关联表中的行时激活。这些行操作是触发事件。例如,可以通过 INSERT或LOAD DATA语句插入行,并为每个插入的行激活插入触发器。...触发器可以设置为在触发事件之前或之后激活。例如,可以在插入表的每一行之前或每更新一行之后激活触发器。 创建触发器:CREATE TRIGGER。...触发器充当累加器,将插入到表格的其中一列中的值相加。...以下声明FOR EACH ROW 定义了触发器主体; 即每次触发器激活时要执行的语句,对于受触发事件影响的每一行都会发生一次。...在该示例中,触发器主体很简单 SET ,它将插入到amount列中的值累加到用户变量中。该语句引用该列NEW.amount意味着 “ 要插入到新行中的amount列的值。”

    1.7K30

    别再被死锁坑了!3个最常见的死锁场景及一键避坑清单

    死锁,是指两个或多个事务在执行过程中,因争夺同一资源而互相等待,结果谁也无法继续执行,形成一个循环等待的僵局。...事务A拿着资源1在等资源2,事务B拿着资源2在等资源1,谁也等不到,最后只能被数据库内核强制中止其中一个。死锁通常发生在使用InnoDB行锁且并发较高的事务中。...解法:统一规定业务代码中的访问顺序,按表名或主键ID升序访问。2.批量更新时的死锁SQL执行计划异常导致全表扫描,一次性锁了大量行。解法:在WHERE条件上建立合适的索引,缩小扫描范围。...3.并发插入时的间隙锁冲突RR隔离级别下,InnoDB会锁住间隙防止幻读,多个事务同时插入时可能互相等待。...死锁预防的最佳实践清单​统一加锁顺序​:所有事务严格按相同顺序访问表和行。​拆分长事务​:事务越短越好,避免在事务中调用外部API或做耗时操作。​

    37610

    重新学习Mysql数据库7:详解MyIsam与InnoDB引擎的锁实现

    Memory:通常用于更新不太频繁的小表,用以快速得到访问结果。 Mysql中的锁 如果熟悉多线程,那么对锁肯定是有概念的,锁是计算机协调多个进程或线程对某一资源并发访问的机制。...OK, 0 rows affected (0.00 sec)--加入local选项是说明,在表满足并发插入的前提下,允许在末尾插入数据 当前进程不能进行插入和更新操作mysql> insert into...说明事务在一个数据行加排他锁前必须先取得该表的IX锁。...何时在InnoDB中使用表锁: InnoDB在绝大部分情况会使用行级锁,因为事务和行锁往往是我们选择InnoDB的原因,但是有些情况我们也考虑使用表级锁。...2、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率; 3、对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

    1K30

    MyISAM按照插入的顺序在磁盘上存储数据

    这里做了重复的工作:两次B-TREE查找而不是一次。 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。...由于主键需要唯一性,加了索引可以在插入新数据时快速确定唯一性,不用遍历数据库。...如果查询不能使用索引,MySQL会进行全表扫描,并锁住每一个元组,不管是否真正需要。...Optimize Table   是mysql中一个可以回收更多的空间、减少“碎片”(defragment)的命令。...多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。

    1.3K00

    数据库圣经——CRUD(三)

    [字段1, 字段2]:可选,指定要插入数据的表字段(即表的列名),数量可单个或多个,需与表定义时的字段名一致。...,结论是 “一次插入多条数据效率更高”,原因分三点: 网络开销:执行每条 SQL 都会产生网络通信成本,一次插入多条可减少通信次数,降低总开销。...MySQL所有的查询结果都会通过临时表来返回给用户 4.查询去重(distinct) select distinct 列名,列名,列名 from 表名 在 MySQL 中,distinct关键字用于处理重复数据...在 MySQL 中,可对多个字段进行排序,其规则如下: 排序优先级:与ORDER BY子句中字段的书写顺序相关,先按第一个字段排序,第一个字段值相同的行再按第二个字段排序,以此类推。...JAVA 中一样 NOT > AND > OR 7.4 演示注意 在一行数据中的两个列是可以进行比较的但是不能跨行进行比较,比如一位同学的语文成绩和数学成绩 where 语句不能用别名 当作过滤条件

    52410

    一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

    最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。 ?...image 一、MySql常见的锁 谈到mysql的锁,可以说的就比较多了,比如行锁、表锁、页锁、元数据锁等,当然我们这里没打算把所有的都细列出来,我们这里主要针对行锁、gap锁进行拓展,以方便分析第二节中...锁分类 我们最常说的锁,可以区分为共享锁(S)和排它锁(X),在mysql的innodb引擎中,为了解决幻读问题,引入了gap锁以及next key lock;除此之外,还有一种意向锁的,比如插入意向锁...通常insert语句,加的是行锁,排它锁 在insert之前,先通过插入意向锁,判断是否可以插入(仅会被gap lock阻塞) 当插入唯一冲突时,在重复索引上添加读锁 原因如下: 事务1 插入成功未提交...image.png 关于这个场景详情博文可以参考:记录一次Mysql死锁排查过程 4. 怎么避免死锁呢?

    7.2K11

    写了 5 年代码,就没见过这么全的MySQL 关联查询知识

    在数据库管理中,关联查询(JOIN)是MySQL中一个非常重要的功能,它允许你根据两个或多个表之间的相关性来检索数据。...通过关联查询,你可以将分散在多个表中的相关信息整合到一个结果集中,从而提供更全面、更准确的数据视图。...通过关联查询,你可以从一个或多个表中检索出满足特定条件的数据行,并将它们组合成一个结果集。...分区表将数据分布在多个物理存储单元上,从而减少了单个查询需要扫描的数据量。 关联查询的复杂应用 在实际应用中,你可能需要关联多个表来获取所需的数据。...例如,你可以使用子查询来过滤出满足特定条件的行,然后再使用JOIN将这些行与其他表关联起来。这种方法在处理具有多个条件的查询时非常有用。

    1.8K00

    MySQL 存储引擎 MyISAM 与 InnoDB 区别

    一次写入操作是一次事务,innodb首先把事务数据写入到Buffer Pool和事务日志中,也就是在大脑中记忆下来,并写下草稿。然后就可以提交事务,响应客户端了。...• MyIASM 引擎(原本Mysql 的默认引擎):不提供事务的支持,也不支持行级锁和外键。MyISAM使用的是表级锁,也就意味着在对表中的数据进行修改时,需要对整个表进行加锁。...而在对表中的数据进行读取时,也需要对所有的表加共享锁。读取和写入这两种操作是互斥的,当然在一些情况下我们对表的数据进行读取时,也可以在表的末尾插入数据。...frm-表格定义、 件中(也可能是多个文件,或者 MYD(MYData)-数据文件、 是独立的表空间文件), MYI(MYIndex)-索引文件 InnoDB 表的大小只受限于操作 系统文件的大小...,索引.MYI 记录存储顺序 按记录插入顺序保存 按主键大小有序插入 外键 不支持 支持 事务 不支持 支持 锁支持(锁是避免 表级锁定 行级锁定、表级锁定,锁定力度 资源争用的一个机

    1.4K30

    InnoDB实现了两种类型的行锁

    当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。...从上面两点可知,MySQL的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录(比如上面的>100都会锁定的例子,只要不插入>100的数,就不会幻读),也就是不允许出现幻读,...为了实现MVCC,InnoDB对每一行都加上了两个隐藏的列,其中一列存储行被创建的”时间”,另外一列存储行被删除的”时间”。...Optimize Table   是mysql中一个可以回收更多的空间、减少“碎片”(defragment)的命令。...多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。

    1.8K10

    深入理解 MySQL ——锁、事务与并发控制

    在事务能够获取表中的行上的独占锁之前,它必须首先获取表上的IX锁。 前文说了,意向锁实现的背景是多粒度锁的并存场景。...这个锁以这样一种方式表明插入的意图,如果插入到同一索引间隙中的多个事务没有插入到该间隙中的相同位置,则它们不需要等待对方。 假设存在值为4和7的索引记录。...在最简单的情况下,如果一个事务正在向表中插入值,那么其他任何事务必须等待向该表中插入它们自己的值,以便由第一个事务插入的行接收连续的主键值。...INNODB 的 MVCC 通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列,第三列是用于事务回滚,此处略去),一个保存了行的创建版本号,另一个保存了行的更新版本号(上一次被更新数据的版本号)...幻读:会话T1事务中执行一次查询,然后会话T2新插入一行记录,这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行。

    1.5K80
    领券