![](https://img-blog.csdnimg.cn/20201015091628907.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
MySQL
文章平均质量分 66
MySQL
知知之之
这个作者很懒,什么都没留下…
展开
-
MySQL深分页优化
MySQL中的深分页问题通常是指当我们通过LIMIT语句查询数据,尤其是在翻到较后面的页码时,性能会急剧下降。例如,查询第1000页的数据,每页10条,系统需要跳过前9990条数据,然后才能获取到所需的记录,这在大数据集上非常低效。传统的深分页实现方法通常是使用OFFSET和LIMIT这会导致数据库扫描大量不需要的行然后抛弃它们,才能获取到真正需要的数据。原创 2024-06-06 11:41:17 · 1120 阅读 · 0 评论 -
PostgreSQL和MySQL架构模型的区别
在这种模型下,每个数据库连接被分配给一个新的服务器进程(或者说是数据库进程)。这样做的优点是提高了系统的稳定性和隔离性,因为每个进程的崩溃不太可能影响到其他进程,从而提高了系统的可靠性。但是,这种模型也意味着更高的系统资源消耗(尤其是内存),因为每个连接都需要其自己的进程和相应的系统资源。在多线程模型中,线程较进程轻量,共享内存空间,因此创建和销毁线程的开销小于进程。多线程模型的一个潜在缺点是,不当的线程管理可能会影响系统的稳定性和性能,因为一个线程的崩溃有可能影响到同一进程中的其他线程。原创 2024-06-06 09:41:57 · 473 阅读 · 0 评论 -
数据库三范式
不过,在实际应用中,有时为了提高查询效率,会适当违反这些规则,通过有限的冗余来优化性能。第二范式(2NF):在第一范式的基础上,表必须没有部分函数依赖,即表中的非主键字段应该完全依赖于主键。第一范式(1NF):表的每一列都是不可分割的基本数据项,即表中的所有字段值都是原子的,不可以再分成更小的部分。数据库设计中的三个范式(通常简称为3NF)是规范化数据的基本原则,旨在减少数据的冗余,提高数据结构的清晰度,确保数据依赖的合理性。也就是说,所有的非主键字段都只能依赖于主键字段,而不是依赖于其他非主键字段。原创 2018-08-24 14:49:27 · 180 阅读 · 1 评论 -
MySQL数据库的InnoDB 和 MyISAM存储引擎之间的区别
特性InnoDBMyISAM事务支持支持 ACID 事务不支持外键支持支持不支持锁定机制行级锁(Row-level Locking)表级锁(Table-level Locking)崩溃恢复日志文件支持崩溃恢复仅支持简单的检查和修复工具表空间管理使用表空间文件每个表有单独的表结构和数据索引文件文件扩展名.ibd(数据和索引)全文索引从 MySQL 5.6 开始支持内置全文索引支持数据完整性支持参照约束和外键不支持参照完整性检查数据读取速度。原创 2024-05-16 09:41:00 · 273 阅读 · 0 评论 -
MySQL架构及redo log、undo log、bin log
在一条更新语句进行执行的时候,InnoDB引擎会把更新记录写到redo log日志中,然后更新内存,此时算是语句执行完了,然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中,这里涉及到WAL即Write Ahead logging技术,他的关键点是先写日志,再写磁盘。这一点与redo log的写入时机相似,但是它们记录的内容有所不同,binlog记录的是修改指令本身,而redo log记录的是这些修改如何影响到数据页。命令来关闭当前的binlog并开始一个新的binlog文件。原创 2022-04-12 14:49:49 · 307 阅读 · 0 评论 -
MySQL事务隔离级别
每一个新的事务,系统版本号都会自动递增,事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行对比。:在同一事务内,多次读取同一数据,中间的结果却不一致,这是由于在读取数据的过程中,有另外一个事务也在访问同一数据,且对其做了修改并提交,导致不可重复读。:在同一事务内,进行了两次相同的查询,但返回的结果行数不同。保存这两个系统版本号,使大多数读操作不用加锁,这样使读操作简单化,性能更好,不足之处就是每个记录需要额外的存储空间,需要进行更多的行检查,以及一些额外的维护工作。原创 2022-04-12 15:57:13 · 162 阅读 · 0 评论 -
MySQL索引
索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行。原创 2022-04-12 15:28:11 · 459 阅读 · 0 评论 -
MySQL复合索引示例
虽然不符合最左匹配原则,但是发现还是使用了索引,这是因为建表语句一共四个字段,出了主键其他的字段组成联合索引,就意味着表里面的所有数据都能在联合索引里面找到,如果增加一个字段重新执行就会发现全表扫描了。ref只会读取匹配的行,当查询的列是一个索引的最左前缀或者是一个普通索引(不是主键索引或唯一索引)会使用。符合最左匹配原则,通过explain执行结果可以看到type=ref,key使用了联合索引。可以看到跟第一个执行结果一致,这是因为SQL语句经过优化器优化之后,依然符合最左匹配原则。.........原创 2022-07-17 15:09:07 · 663 阅读 · 0 评论 -
最左匹配原则的原理
最左匹配原则都是针对联合索引来说的,所以我们可以从联合索引的原理来了解最左匹配原则。我们都知道索引的底层是一颗 B+ 树,那么联合索引当然还是一颗 B+ 树,只不过联合索引的键值数量不是一个,而是多个。构建一颗 B+ 树只能根据一个值来构建,因此数据库依据联合索引最左的字段来构建 B+ 树。例子:假如创建一个(a,b,c)的联合索引,那么它的索引树是这样的:该图就是一个形如(a,b,c)联合索引的 b+ 树,其中的非叶子节点存储的是第一个关键字的索引 a,而叶子节点存储的是三个关键字的数据。这里原创 2022-04-22 15:04:57 · 2025 阅读 · 0 评论 -
MySQL锁
行级锁共享锁(S)又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。设置共享锁:SELECT ... LOCK IN SHARE MODE排它锁(X)又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。设置排他锁:S原创 2022-04-12 16:14:19 · 264 阅读 · 0 评论 -
MySQL当前读、快照读、MVCC
当前读,读取的是最新版本,并且对读取的记录加锁,阻塞其他事务同时改动相同记录,避免出现安全问题。原创 2022-08-03 17:03:21 · 383 阅读 · 0 评论 -
MySQL select加锁分析
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!下面讨论是基于InnoDB引擎、事务隔离级别:可重复读和串行化表数据如下,pId为主键索引。...原创 2022-08-03 16:43:55 · 1352 阅读 · 0 评论 -
MySQL执行计划
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)rows:估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。id:select子句或表执行顺序,id相同,从上到下执行,id不同,id值越大,执行优先级越高。ref:关联的字段,常量等值查询,显示为const,如果为连接查询,显示关联的字段。原创 2024-04-17 14:28:42 · 611 阅读 · 0 评论 -
MySQL InnoDB缓存池
磁盘读写,并不是按需读取,而是按页读取,一次至少读一页数据(一般是4K),如果未来要读取的数据就在页中,就能够省去后续的磁盘IO,提高效率。原创 2023-06-01 15:21:36 · 553 阅读 · 0 评论 -
MySQL复制
在主库上把数据更改记录到二进制日志中,这些记录被称之为二进制事件日志(binlog) 备库将主库的日志复制到自己的中继日志中 备库读取中继日志中的事件,将其重放到备库数据之上复制原理基于语句的复制主库把执行过的语句记录到二进制日志中,备库读取并重放这些语句。优点:实现简单,复制出现问题更好定位缺点:有些SQL不能被正确复制,例如CURRENT_USER()基于行的复制主库把数据记录到二进制日志中,复制比较高效优点:几乎没有基于行复制模式无法处理的场景缺点:由于语句没..原创 2022-04-12 16:19:22 · 258 阅读 · 0 评论 -
MySQL连接池优化
MySQL连接池的优化是提高数据库访问性能和系统响应速度的重要手段。连接池允许系统复用已经建立的数据库连接,避免了频繁开关连接的开销,同时也能有效管理系统的数据库资源。原创 2022-04-13 15:29:05 · 54 阅读 · 0 评论 -
MySQL优化
SQL语句优化索引优化缓存优化关系型数据库已经非常成熟,但是并不是完美的,仍然有以下缺点: 关系型数据库存储的是行记录,无法存储数据结构。 关系型数据库的Schema扩展非常不方便。 关系型数据库的Schema是强约束,无法操作不存在的列。当要扩展列时,需要先执行DDL操作。 关系型数据库在大数据场景下I/O较高。 如果对关系型数据库的表进行统计的时候,I/O会非常的高。即使只是统计几列的数据,它也会把行中所有列的数据加载到内存当中。 关系型数据库的全文搜索原创 2022-04-13 10:59:27 · 621 阅读 · 0 评论 -
MySQL分库分表
背景随着业务发展单库单表也越来越不能满足业务了,那么分库分表就势在必行了。方案hash取模方案通过hash取模数据可以均分的分散到4张表中,不会出现热点问题,但是如果后面业务量增加4张表也不能满足业务了,需要额外拓展4张表,那么问题就来了,hash取模数就变成8,之前的历史数据就不能通过这个算法了,那么这个时候就需要进行数据迁移了,数据迁移带来的工作量使我们不想看到的。range范围方案range方案就是根据id返回,把数据放到对应的表中,因为id都是递增的那么新数据都会集中原创 2021-05-16 17:47:02 · 387 阅读 · 0 评论 -
分库分表如何解决跨库查询问题
字段冗余能带来便利,是一种“空间换时间”的体现。最复杂的还是数据一致性问题,这点很难保证,可以借助数据库中的触发器或者在业务代码层面去保证。定时A库中的tab_a表和B库中tbl_b有关联,可以定时将指定的表做同步。当然,同步本来会对数据库带来一定的影响,需要性能影响和数据时效性中取得一个平衡。“订单表”中保存“卖家Id”的同时,将卖家的“Name”字段也冗余,这样查询订单详情的时候就不需要再去查询“卖家用户表”。这是一种典型的反范式设计,在互联网行业中比较常见,通常是为了性能来避免join查询。...原创 2022-08-12 16:58:20 · 676 阅读 · 0 评论 -
分布式事务
原子性确保事务中的操作要么全部完成,要么全部不进行,事务是一个不可分割的工作单位。如果事务中的一部分操作提交成功,其余的操作失败,则事务会回滚到事务开始之前的状态,好像什么操作都没有执行过一样。:一致性确保事务将数据库从一个一致的状态转换到另一个一致的状态。在事务开始之前和事务完成之后,数据库的完整性约束没有被破坏,它保证了数据库的数据完整性和业务规则的一致性。:数据库系统提供一定程度的事务隔离性,以控制事务的操作之间的可见性,确保事务并发执行的结果能够与事务串行执行的结果相一致。原创 2022-04-08 16:49:01 · 2828 阅读 · 0 评论 -
如何保证缓存与数据库双写时的数据一致性
背景在优化系统性能时,总会用到缓存去提高系统性能,但是同时也会单来一个问题缓存跟数据库很难做到原子操作,就会出现缓存与数据库双写时数据不一致性问题。解决方案先更新数据库,后更新缓存这种方案在并发场景下会有问题,例如A线程先更新完数据库,这个时候线程B进行查询操作,那么线程B就会读到缓存的旧数据。先更新数据库,后删除缓存这一种情况也会出现问题,比如更新数据库成功了,但是在删除缓存的阶段出错了没有删除成功,那么此时再读取缓存的时候每次都是错误的数据了。先更新缓存,后更新数据库同样原创 2021-10-14 10:48:13 · 208 阅读 · 0 评论 -
Seata json decode exception, Cannot construct instance of `java.time.LocalDateTime
com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Cannot construct instance of `java.time.LocalDateTime` (no Creators, like default constructor, exist): cannot deserialize from Object val 秃秃爱健身 华为云开发者联盟。转载 2023-07-03 10:58:11 · 99 阅读 · 0 评论 -
Seata XA模式和AT模式的区别
总的来说,XA模式是通过数据库的两阶段提交协议来实现分布式事务,而AT模式则是通过应用程序层面的日志来实现分布式事务。在AT模式中,Seata通过,在每个参与分布式事务的服务中,截取并记录每个本地事务的执行情况,在事务提交阶段,通过回放本地事务日志的方式来判断是否提交或回滚。在XA模式中,Seata通过协调器(TC)来协调各个分支事务的提交或回滚,保证全局事务的一致性。全局事务由事务协调器(TC)统一管理,分支事务由不同的资源管理器(RM)进行管理。原创 2023-07-03 10:30:44 · 1080 阅读 · 0 评论 -
ShardingSphere-JDBC
从架构上看ShardingSphere-JDBC更符合分布式架构的设计,直连数据库,没有中间应用,理论性能是最高的(实际性能需要结合具体的代码实现,理论性能可以理解为上限,通过不断优化代码实现,逐渐接近理论性能)。ShardingSphere-JDBC 定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。:ShardingSphere-JDBC会对原始SQL进行解析,并生成解析后的SQL语法树,以进行后续的SQL路由、改写、执行等操作。原创 2023-02-08 14:44:10 · 242 阅读 · 0 评论 -
MySQL千万级别表如何高性能添加字段和索引
尽管存在短暂的锁表时刻,但由于大部分操作都是在临时表上进行,因此不会明显地阻塞表的访问。会在一个非常短的时间窗口内停止对原表的写操作,将原表重命名为备份表,然后将临时表重命名为原表名,这样就完成了表结构的更改。在操作成功完成后,原表的触发器会被删除,如果一切正常,备份表也会被删除(取决于具体的参数配置),完成整个流程的清理工作。数据复制期间,对原表的插入、更新和删除操作会通过触发器同步到临时表,确保临时表数据的实时更新。首先根据原表的结构创建一个新的临时表,并对这个临时表应用所需的表结构变更。原创 2024-05-15 17:25:45 · 302 阅读 · 0 评论 -
删除千万级数据量导致的慢查询优化
删除千万级的数据,结果导致频繁的慢查询。线上收到大量慢查询告警,于是检查慢查询的SQL,发现不是啥复杂SQL,这些SQL主要针对一个表,基本都是单行查询,看起来应该不会有慢查询。这种SQL基本上都是直接根据索引查找出来的,性能应该极高。是否可能慢查询不是SQL问题,而是MySQL生产服务器的问题?特殊情况下,MySQL出现慢查询还真不是SQL问题,而是他自己生产服务器的负载太高,导致SQL语句执行慢。磁盘I/O磁盘I/O负载高,每秒执行大量高负载的随机I/O,但磁盘本身每秒能执行的随机I/O原创 2022-04-13 15:23:35 · 450 阅读 · 0 评论 -
记一次数据库死锁
有两个事务T1和T2,它们对同一张表employees和进行操作。原创 2022-01-20 11:21:33 · 742 阅读 · 0 评论 -
记一次Mysql并发死锁
可见Transaction1与Transaction2 同时锁住了同一部分,而且是locak_mode X rec bur not gap Record lock。根据show engine innodb status查询。多个终端session模拟并发事务。原创 2022-08-23 14:42:23 · 493 阅读 · 0 评论 -
SQL中为什么不能使用1=1条件
性能问题:当使用1=1条件时,实际上并没有进行任何实际的条件判断,这可能导致数据库引擎无法有效地利用索引或优化查询计划,从而影响查询性能。在SQL查询中,使用1=1条件是一种常见的技巧,它通常被用于动态构建查询条件或者简化查询语句。可读性:在实际的查询中,1=1条件并没有提供任何有意义的过滤条件,可能会让其他开发人员难以理解查询的意图,降低代码的可读性和可维护性。潜在安全风险:在某些情况下,如果1=1条件被滥用或者被恶意利用,可能会导致SQL注入等安全问题,因为它可以绕过正常的条件判断。原创 2024-03-08 17:05:46 · 371 阅读 · 0 评论 -
数据库使用非等于查询的坑
创建如下test表,name可为空字段create table test( id bigint, name varchar(100));现在查询name不等于张三的记录select * from test where name != '张三'查询结果发现最后的查询记录并没有发现id = 3 的记录。查postgresql文档发现问题所在<> is the standard SQL notation for “not equal..原创 2021-10-14 14:53:03 · 234 阅读 · 0 评论 -
数据库逻辑删除如何实现字段唯一性
背景在业务表设计中,删除数据一般建议不使用物理删除,而是使用逻辑删除,在逻辑删除时,就存在一个问题:如何保证表中字段唯一性解决方案删除标志可以不局限为true、false,比如使用0表示未删除,-1、-2、-3.......表示已删除利用为null的字段不会和其他字段有组合索引的效果增加delete_time字段,同一时间内可能性几乎为0如果有更好的解决方案可以留言交流...原创 2021-03-04 14:33:30 · 837 阅读 · 1 评论 -
Mysql存储emoji表情
从MySQL 5.5.3版本开始,数据库可支持4个字节的utf8mb4 字符集,一个字符最多可以有4个字节,所以能支持更多的字符集,故能存储Emoji表情符号。1.修改数据字符集为utf8mb42.修改数据表字符集为utf8mb43.修改字段字符集为utf8mb4...原创 2018-04-10 18:13:36 · 192 阅读 · 0 评论