数据库
文章平均质量分 83
数据库那些事
爱琴孩
扫盲+科普+解惑,愿天下程序员每天少掉头发
展开
-
MySQL数据实时同步到Elasticsearch
数据传输服务DTS(Data Transmission Service)是阿里云提供的实时数据流服务,支持关系型数据库(RDBMS)、非关系型的数据库(NoSQL)、数据多维分析(OLAP)等数据源间的数据交互,集数据同步、迁移、订阅、集成、加工于一体,助您构建安全、可扩展、高可用的数据架构。在MySQL与ES的同步场景中,其主要目的是将MySQL中的业务数据实时同步到ES中,以便利用ES的高效查询能力来应对复杂的查询需求,同时减轻MySQL的查询压力。Binlog实时同步的原理基于数据库的复制机制。转载 2024-08-18 16:15:55 · 84 阅读 · 0 评论 -
五年经验,还不懂小表驱动大表
而如果sql语句中包含了exists关键字,它优先执行exists左边的语句(即主查询语句)。如果匹配上,则可以查询出数据。这个需求中,order表有10000条数据,而user表有100条数据。小表驱动大表,也就是说用小表的数据集驱动大表的数据集。假如有order和user两张表,其中order表有10000条数据,而user表有100条数据。前面提到的这种业务场景,使用in关键字去实现业务需求,更加合适。,然后再执行in外面的语句。不管是用in,还是exists关键字,其核心思想都是用小表驱动大表。原创 2024-04-08 22:29:37 · 431 阅读 · 0 评论 -
五年经验,还只会用in和not in?
在MySQL中,IN和EXISTS都是用来实现子查询的关键字,但它们在使用方式、查询效率以及索引利用上有所区别。使用方式IN用于判断某个字段的值是否在子查询返回的结果集中。它通常与一个值列表或子查询一起使用,可以看作是多个OR条件的组合。而EXISTS用于检查子查询是否至少返回一行数据,它不关心子查询返回的具体值是什么。查询效率:在某些情况下,EXISTS可能比IN更高效,尤其是当子查询返回大量数据时。因为EXISTS只需要知道是否至少有一行数据满足条件,而IN。转载 2024-03-12 22:27:08 · 42 阅读 · 0 评论 -
Sharding-jdbc那点事(二)
简单点理解就是拿到分片策略中配置的分片键等信息,在从SQL解析结果中找到对应分片键字段的值,计算出 SQL该在哪个库的哪个表中执行,SQL路由又根据有无分片健分为。直接路由是直接将SQL路由到指定⾄库、表的一种分⽚方式,而且直接路由可以⽤于分⽚键不在SQL中的场景,还可以执⾏包括⼦查询、⾃定义函数等复杂情况的任意SQL。SQL经过解析、优化、路由后已经明确分片具体的落地执行的位置,接着就要将基于逻辑表开发的SQL改写成可以在真实数据库中可以正确执行的语句。当 SQL分片健的运算符为。转载 2024-02-05 22:42:33 · 78 阅读 · 0 评论 -
聊聊Mysql中的全文检索
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效,但有时需求就是如此,类似这样的需求还有很多,例如,搜索引擎需要根据用户数据的关键字进行全文查找,电子商务网站需要根据用户的查询条件,在可能需要在商品的详细介绍中进行查找,这些都不是B+树索引能很好完成的工作。通过数值比较,范围过滤等就可以完成绝大多数我们需要的查询了。但是,如果希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较,全文索引就是为这种场景设计的。转载 2023-09-03 17:08:08 · 2404 阅读 · 3 评论 -
MySQL和MongoDB那些事
MySQL 和 MongoDB 是两个可用于存储和管理数据的数据库管理系统。MySQL 是一个关系数据库系统,以结构化表格格式存储数据。相比之下,MongoDB 以更灵活的格式将数据存储为 JSON 文档。两者都提供性能和可扩展性,但它们为不同的应用场景提供了更好的性能。MySQL 是一种关系型数据库管理系统,它使用结构化查询语言(SQL)来操作数据。SQL 是一种通用的、标准化的、声明式的语言,它可以定义数据的结构、约束、操作、查询等。转载 2023-08-14 21:38:32 · 318 阅读 · 0 评论 -
胖虎曾问我:“加密数据如何进行模糊查询?“
我们知道加密后的数据对模糊查询不是很友好,本篇就针对加密数据模糊查询这个问题来展开讲一讲实现的思路,希望对大家有所启发。为了数据安全我们在开发过程中经常会对重要的数据进行加密存储,常见的有:密码、手机号、电话号码、详细地址、银行卡号、信用卡验证码等信息,这些信息对加解密的要求也不一样,比如说密码我们需要加密存储,一般使用的都是不可逆的慢hash算法,慢hash算法可以避免暴力破解(典型的用时间换安全性)。转载 2023-07-06 23:08:02 · 103 阅读 · 0 评论 -
聊聊Innodb中的innodb_autoinc_lock_mode
经过上篇文章的介绍,我们知道在 MySQL 中,主键索引就是聚簇索引,MySQL 表中的数据是根据主键值聚集在一起的,聚簇索引是一棵 B+Tree,这棵树中的数据是有序的。所以,如果我们使用 UUID 字符串作为主键,那么就会导致每次数据插入的时候,都需要在 B+Tree 中寻找到适合它自己的位置,找到之后就要挪动后面的节点(就像在数组中插入一条记录),挪动后面的节点,就有可能涉及到页分裂,插入效率就会降低。转载 2023-06-17 22:39:08 · 1706 阅读 · 0 评论 -
为啥mysql官方建议表主键使用自增id
在MySQL中设计表的时候,MySQL官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究竟有什么坏处?本篇博客我们就来分析这个问题,探讨一下内部的原因。转载 2023-06-17 20:18:04 · 1776 阅读 · 0 评论 -
Mysql必知必会之索引建立原则
从上面分析我们知道,每个索引都对应一颗B+树,并且叶子节点存储了索引列全量的数据,一旦索引数量多,那么就会占有大量磁盘空间同时前面也提到,在查询之前会对索引成本进行计算,一旦索引多,计算的次数就多,也可能会浪费性能。转载 2023-06-04 12:59:59 · 205 阅读 · 0 评论 -
Mysql必知必会之索引失效
在日常开发中,肯定或多或少都遇到过索引失效的问题,这里我总结一下几种常见的索引失效的场景。为了方便解释,这里我再把图拿过来。转载 2023-06-04 12:54:08 · 169 阅读 · 0 评论 -
Mysql必知必会之索引选择
在日常生产中,一个表可能会存在多个索引,那么mysql在执行sql的时候是如何去判断该走哪个索引,或者是全表扫描呢?mysql在选择索引的时候会根据索引的使用成本来判断。一条sql执行的成本大致分为两块全表扫描成本计算。对于全表扫描来说,成本计算大致如下。mysql会对表进行数据统计,这个统计是大概,不是特别准,通过可以查看统计数据。转载 2023-06-04 12:41:44 · 273 阅读 · 0 评论 -
Mysql必知必会之索引下推和索引合并
假设现在对表建立了一个name和age的联合索引,为了方便理解,我把前面的图再拿过来接下来要执行如下的sql所以对于图上所示,整个搜索过程会经历5次回表操作,两个赵六,两个刘七,一个王九,最后符合条件的也就是id=6的赵六那条数据,其余age不符和。虽然这么执行没什么问题,但是不知有没有发现其实没必要进行那么多次回表,因为光从上面的索引图示就可以看出,符合的数据就id=6的赵六那条数据。所以在MySQL5.6之后,对上面的age > 22判断逻辑进行了优化。转载 2023-06-04 12:31:02 · 407 阅读 · 0 评论 -
Mysql必知必会之回表和覆盖索引
讲完,接下来讲一讲如何使用二级索引查找数据。这里假设对name字段创建了一个索引,并且表里就存了上面示例中的几条数据,这里我再把图拿过来那么对于下面这条sql应该如何执行?由于查询条件是,所以会走name索引。赵六王五刘七王五赵六赵六select *赵六赵六赵六从上面的二级索引的查找数据过程分析,就明白了回表的意思,就是先从二级索引根据查询条件字段值查找对应的主键id,之后根据id再到聚簇索引查找其它字段的值。转载 2023-06-04 12:13:03 · 278 阅读 · 0 评论 -
Mysql必知必会之二级索引
二级索引也被称为非聚簇索引,本身也就是一颗B+树,一个二级索引对应一颗B+树,但是二级索引B+树存储的数据跟聚簇索引不一样。聚簇索引前面也说了,叶子节点存的就是我们插入到数据库的数据,非叶子节点存的就是数据的主键id和对应的数据页号。而二级索引叶子节点存的是索引列的数据和对应的主键id,非叶子节点除了索引列的数据和id之外,还会存数据页的页号。转载 2023-06-04 11:06:42 · 2284 阅读 · 0 评论 -
Mysql必知必会之聚簇索引
我们知道,我们插入表的数据其实最终都要持久化到磁盘上,InnoDB为了方便管理这些数据,提出了的概念,它会将数据划分到多个页中,每个页大小默认是16KB,这个页我们可以称为数据页。当我们插入一条数据的时候,数据都会存在数据页中,如下图所示当数据不断地插入数据页中,数据会根据主键(没有的话会自动生成)的大小进行排序,形成一个单向链表。转载 2023-06-04 10:52:56 · 532 阅读 · 1 评论 -
mysql必知必会之Hash索引
mysql必知必会系列主要是针对InnoDB存储引擎进行讲解。转载 2023-06-04 10:37:05 · 420 阅读 · 0 评论 -
Sharding-jdbc那点事(一)
最早是当当网内部使用的一款分库分表框架,到2017年的时候才开始对外开源,这几年在大量社区贡献者的不断迭代下,功能也逐渐完善,现已更名为,2020年4⽉16⽇正式成为Apache软件基⾦会的顶级项⽬。随着版本的不断更迭的核心功能也变得多元化起来。从最开始 Sharding-JDBC 1.0 版本只有数据分片,到 Sharding-JDBC 2.0 版本开始支持数据库治理(注册中心、配置中心等等),再到 Sharding-JDBC 3.0版本又加分布式事务 (支持AtomikosNarayana。转载 2023-05-07 12:03:09 · 290 阅读 · 0 评论 -
select for update到底加了什么锁?
因为加锁是跟息息相关的。而常用的数据库隔离级别也就,所以本文分别根据展开讲述。转载 2023-02-19 19:11:52 · 169 阅读 · 0 评论 -
悲观锁与乐观锁
DBMS中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和数据的统一性。而其中乐观锁和悲观锁是并发控制主要采用的技术手段。无论是悲观锁还是乐观锁,都是人们定义出来的概念,可以认为是一种思想。不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念,像memcache、Hibernate、tair等都有类似的概念针对于不同的业务场景,应该选用不同的并发控制方式。而且不要把乐观锁和悲观锁狭义的理解为DBMS中的概念,更不要把他们和数据中提供的锁机制(行锁、表锁、排他锁、共享锁)混为一谈。转载 2018-05-12 22:32:38 · 170 阅读 · 1 评论 -
五年经验,没实践过分库分表?
分库分表是在海量数据下,由于单库、表数据量过大,导致数据库性能持续下降的问题,演变出的技术方案。分库分表是由分库和分表这两个独立概念组成的,只不过通常分库与分表的操作会同时进行,以至于我们习惯性的将它们合在一起叫做分库分表。转载 2022-12-08 22:05:16 · 163 阅读 · 0 评论 -
mysql幻读那点事
当同一个查询在不同的时间产生不同的结果集时,事务中就会出现所谓的幻象问题。例如,如果 SELECT 执行了两次,但第二次返回了第一次没有返回的行,则该行是“幻像”行。转载 2022-10-27 22:40:55 · 659 阅读 · 0 评论 -
InnoDB引擎行锁那些事
InnoDB 引擎是支持行级锁的,而 MyISAM 引擎并不支持行级锁。普通的 select 语句是不会对记录加锁的,因为它属于快照读。如果要在查询时对记录加行锁,可以使用下面这两个方式,这种查询会加锁的语句称为。上面这两条语句必须在一个事务中,,所以在使用这两条语句的时候,要加上 begin、start transaction 或者 set autocommit = 0。共享锁(S锁)满足读读共享,读写互斥。独占锁(X锁)满足写写互斥、读写互斥。转载 2022-10-26 21:47:43 · 165 阅读 · 0 评论 -
幻读和临键锁那点事
假设我们有表t结构如下,里面的初始数据行为:(0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5)((5, 5, 5)假设select * from where value=1 for update,只在这一行加锁(注意这只是假设),其它行不加锁,那么就会出现如下场景:Session A的三次查询Q1-Q3都是select * from where value=1 for update,查询的value=1的所有row。T1:Q1只返回一行(1,1,1);转载 2022-08-21 19:27:21 · 345 阅读 · 1 评论 -
精通mysql,不了解MVCC(二)?
通过以上描述,我们就可以清楚的知道:InnoDB 中,MVCC 就是通过 Undo Log + Read View 进行数据读取,Undo Log 保存了历史快照,而 Read View 规则帮我们判断当前版本的数据是否可见。从而不需要通过加锁的方式,就可以实现提交读和可重复读这两种隔离级别。总的来说,MVCC本质上就是一种数据结构。已提交读和可重复读都是使用了Read View这种策略通过区间判断获取自己能够读取的内容,然后展示。InnoDB通过MVCC,解决了脏读、不可重复读。转载 2022-08-20 15:31:18 · 127 阅读 · 0 评论 -
精通mysql,不了解MVCC(一)?
MVCC((Mutil-Version Concurrency Control)),全称多版本并发访问,这是一种并发环境下进行数据安全控制的方法,其本质上是一种乐观锁,用于实现提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别。在这里我先为大家理清楚一个概念:我们常说的MVCC是由MySQL数据库InnoDB存储引擎实现的,并非是由MySQL本身实现的,不同的存储引擎,对MVCC都有不同的实现标准。转载 2022-08-20 08:12:25 · 146 阅读 · 0 评论 -
为啥DBA建议使用DATETIME作为日期
前言MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒,所以在表结构设计中,常见使用的日期类型为DATETIME 和 TIMESTAMP。接下来,我就带你深入了解这两种类型,以及它们在设计中的应用实战。DATETIMEDATETIME 最终展现的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节。从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N转载 2022-03-27 21:00:32 · 308 阅读 · 0 评论 -
3年经验,不清楚int(1)和int(10)的区别?
前言最近遇到个问题,有个表的要加个user_id字段,user_id字段可能很大,于是我提mysql工单alter table xxx ADD user_id int(1)。领导看到我的sql工单,于是说:这int(1)怕是不够用吧,接下来是一通解释。其实这不是我第一次遇到这样的问题了,其中不乏有工作5年以上的老司机。包括我经常在也看到同事也一直使用int(10),感觉用了int(1),字段的上限就被限制,真实情况肯定不是这样。数据说话我们知道在mysql中 int占4个字节,那么对于无符号的i转载 2022-01-29 20:59:04 · 269 阅读 · 1 评论 -
mysql索引失效之字符编码不一致
前言我相信你一定听说过,如果两张表字符编码不一致,索引字段在进行join时会导致索引失效,但一定是这样的吗?本文就来一起仔细分析一下这个问题。准备我们先准备两个表,一个采用utf8的字符集,一个采用utf8mb4的字符集,并分别插入一条数据。-- 建表采用utf8字符集CREATE TABLE t_utf8 ( id INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id', age INT ( 11 ) COM...转载 2021-11-27 10:09:24 · 502 阅读 · 1 评论 -
mysql索引失效之隐式类型转换
某些情况下,因为我们自己使用的不当,导致mysql用不到索引,这一般很容易发生在类型转换方面,也许你会说,mysql不是已经支持隐式转换了吗?比如现在有个整型的user_id索引字段,我们因为查询的时候没注意,写成了:selectxxfromuserwhereuser_id="1234"注意这里是字符的1234,当发生这种情况下,MySQL确实足够聪明,会把字符的1234转成数字的1234,然后愉快的使用了user_id索引。但是如果我们有个字符型的user_id索引字段,还是因为我们...原创 2021-11-25 22:55:26 · 982 阅读 · 0 评论 -
3年经验,还不懂大事务问题?
大事务引发的问题在分享解决办法之前,先看看系统中如果出现大事务可能会引发哪些问题从上图可以看出如果系统中出现大事务时,问题还不小,所以我们在实际项目开发中应该尽量避免大事务的情况。如果我们已有系统中存在大事务问题,该如何解决呢?少用@Transactional注解 @Transactional(rollbackFor=Exception.class) public void save(User user) { doSameThing... } 我转载 2021-09-12 13:36:09 · 746 阅读 · 2 评论 -
5年经验,表结构设计字段还设置default null?
公司有个sql脚本审核工具,在创建新表或者老表加字段的时候,建议所有的字段都设置为not null而不是mysql默认的default null。设置not null 究竟有啥好处呢?带着这个疑问我们一起看一下之前遇到的一个问题。之前需要统计订单表中非WebHyjy类型的订单数量,当时写了这样的一个sql然后我们来查询下这个表的总数据量接着我们再来统计下orderType='WebHyjy'的数量。原创 2021-04-10 10:52:41 · 674 阅读 · 0 评论 -
Mysql之真假UTF-8
前言不知道大家在日常开发中可以遇到表情符入库的需求,我们这边对于表情符的处理是在业务层直接拒绝表情符的输入,所以在DB存储层就不需要单独考虑表情符的入库存储了,但是需要产品经理明确要求,用户可以输入表情符,而且也必须要入库存储,那这时候我们在建表的时候就需要考虑表情符的存储了,这里来和大家一起延申下,复习下mysql中的真假UTF-8之迷。插入表情符报错我在往MySQL存入emoji????????表情时,一直出错,无法导入。后来找到办法,通过把 utf8 改成 utf8mb4 就可以了,当时的原创 2020-10-02 22:42:11 · 333 阅读 · 0 评论 -
科普mysql中的binlog、redo log和undo log(下)
前言前面在科普mysql中的binlog、redo log和undo log(上)中,我们介绍了mysql中的binlog,了解了binlog的应用场景和原理。这里再和大家一起看下Mysql的redo和undo。redo log我们都知道,事务的四大特性里面有一个是持久性,具体来说就是只要事务提交成功,那么对数据库做的修改就被永久保存下来了,不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢?最简单的做法是在每次事务提交的时候,将该事务涉及修改的数据页全部刷新到磁盘中。但是这转载 2020-08-20 23:15:24 · 237 阅读 · 1 评论 -
科普mysql中的binlog、redo log和undo log(上)
前言日志是mysql数据库的重要组成部分,记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发,我们重点需要关注的是二进制日志(binlog)和事务日志(包括redo log和undo log),接下来会详细介绍这三种日志。binlogbinlog用于记录数据库执行的写入性操作(不包括查询)信息,以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志,并且由Server层进行记录,使用任何存储引擎的mysql数据库都转载 2020-08-20 23:07:12 · 229 阅读 · 0 评论 -
请别面试问到数据库优化,就往分库分表上扯(二)
前言分库分表能有效缓解单机和单表带来的性能瓶颈和压力,突破网络 IO、硬件资源、连接数的瓶颈,同时也带来一些问题,下面将描述这些问题和解决思路。事务一致性问题分布式事务当更新内容同时存在于不同库,不可避免会带来跨库事务问题。跨分片事务也是分布式事务,没有简单的方案,一般可使用“XA 协议”和“两阶段提交”处理。分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点,推后了提交事务的时间点,延长了事务的执行时间,导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节转载 2020-07-18 10:54:44 · 283 阅读 · 0 评论 -
请别面试问到数据库优化,就往分库分表上扯(一)
前言当数据库的数据量过大,大到一定的程度,我们就可以进行分库分表。那么基于什么原则,如何进行分库分表,在什么场景下进行分库分表,这就是本篇所要讲的。数据库瓶颈不管是 IO 瓶颈还是 CPU 瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载的活跃连接数的阈值。在业务 Service 来看, 就是可用数据库连接少甚至无连接可用,接下来就可以想象了(并发量、吞吐量、崩溃)。IO 瓶颈:第一种:磁盘读 IO 瓶颈,热点数据太多,数据库缓存放不下,每次查询会产生大量的 IO,转载 2020-07-17 22:03:30 · 154 阅读 · 0 评论 -
Mysql中的两列日期求差和取比较值
前言在mysql的使用中我们经常需要对日期进行操作,比如取两列中的日期相差多少天,相差多少小时,多少月。还有些查询场景中,我们需要取两列中较大的日期值或者较小的日期值,这里来和大家一起复习下。使用TIMESTAMPDIFF快速求差相差多少秒SELECT TIMESTAMPDIFF(SECOND,t.CreateTime,now()) FROM AudioInfo t where t.CreateTime>'2020-06-19 00:00:00' limit 5;相差原创 2020-06-19 23:32:05 · 1982 阅读 · 0 评论 -
聊聊聚集索引和辅助索引
前言索引是数据库中非常重要的概念,它是存储引擎能够快速定位记录的秘密武器,对于提升数据库的性能、减轻数据库服务器的负担有着非常重要的作用;索引优化是对查询性能优化的最有效手段,它能够轻松地将查询的性能提高几个数量级。索引的数据结构InnoDB 存储引擎在绝大多数情况下使用 B+ 树建立索引,这是关系型数据库中查找最为常用和有效的索引,但是 B+ 树索引并不能找到一个给定键对应的具体值,它只能找到数据行对应的页,然后把整个页读入到内存中,并在内存中查找具体的数据行。B+ 树是平衡树,它查找转载 2020-06-11 22:48:21 · 769 阅读 · 0 评论 -
如何高效利用索引
前言对于索引,想必无需多言。大家应该都知道什么索引,为什么要建索引。这里来和大家学习下如何高效利用索引,下面主要围绕"覆盖索引"、"最左原则"、"索引下推"三点来和大家一起学习下。覆盖索引覆盖索引是指在普通索引树中可以得到查询的结果,不需要在回到主键索引树中再次搜索。建立如下这张表来演示覆盖索引:create table T (ID int primary key,age int NOT NULL DEFAULT 0,name varchar(16) NOT NULL DEFAULT转载 2020-06-09 21:40:32 · 330 阅读 · 0 评论