引言 数据库索引，绝对是 MySQL 的核心功能之一，如果没有索引机制的数据库，那数据的检索效率绝对是令人无法接受的，毕竟没有索引的表数据，就如同一个普通的文本文件存储在磁盘中。在《索引上篇》中，我们对于 MySQL 提供的索引机制，从引入，到创建、使用、分类、管理....等进行了全面阐述，相信经过上一篇的讲解后，大家对 MySQL 索引机制建立了系统化的认知，而本篇则会以上篇为基础，对索引机制进一步加深掌握。 不过在上篇中虽对数据库索引机制有了完善认知，但还不够，因为上篇仅是单纯的认知阶段，能否真正的在实际项目中运用好索引机制，还需要具备丰富的经验以及一些原则与方法论，比如下述一些关于索引的问题： • 索引虽然能给 MySQL 检索数据的效率带来质的飞跃，但加入索引没有带来新问题吗？ • 既然索引能够提升查询性能，那是不是为表中每个字段建立索引，性能会更好？ • 一张数据表中，哪些类型的字段不适合建立索引呢？又是因为什么原因呢？ • 表中会存在大量的字段，但其中哪些字段建立索引才能够最大的性能收益呢？ • MySQL 提供的索引种类也不少，...

引言 由于 MySQL 是作为存储层部署在业务系统的最后端，所有的业务数据最终都要入库落盘，但随着一个项目在线上运行的时间越来越久，数据库中的数据量自然会越来越多，而数据体积出现增长后，当需要从表查询一些数据时，效率会越发低下。在正常情况下，表的查询性能和数据量是成反比的，也就是数据越多，查询越慢。 这是什么原因导致的呢？由于 MySQL 默认的查询方式导致的，举个例子 SELECT   FROM  zz_student ; +------------+--------+------+--------+ | student_id | name   | sex  | height | +------------+--------+------+--------+ |          1 | 竹子   | 男   | 185cm  | |          2 | 熊猫   | 女   | 170cm  | |          3 | 子竹   | 男   | 182cm  | |          4 | 棕熊   | 男   | 187cm  | | 

开篇tip 我们知道日志的作用不言而喻，无论是线上排查，亦或是性能优化，几乎都需要从日志中来获得信息作为依据，而MySQL中，很多很多的功能也都需要基于日志实现，比如事务回滚、数据持久化、数据恢复、数据迁移、MVCC机制..... 我们在了解具体内容之前，先对三种日志之间的小总结，涉及到使用场景和文件等，可以把这个当做先前总结，更细的内容在每一章会提及。 简单梳理下日志是在哪个地方写入到不同日志文件中的，undolog、redolog都是InnoDB引擎中的日志，而且都是在 Buffer Pool 中，而binlog在Server层中，位于每条线程中，并且每种日志在磁盘中的的归档方式和文件都是不一样的，之间的区别如下图。 WAL机制是什么？ WAL，全...

1.常见表表达式（CTEs） 如果您想要查询子查询，那就是CTEs施展身手的时候 CTEs基本上创建了一个临时表。 使用常用表表达式（CTEs）是模块化和分解代码的好方法，与您将文章分解为几个段落的方式相同。 请在Where子句中使用子查询进行以下查询。 SELECT name, salary FROM People WHERE NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = 'Canada' AND city = 'Toronto' ) AND salary = ( SELECT AVG( salary ) FROM salaries WHERE gender = 'Female') 这似乎似乎难以理解，但如果在查询中有许多子查询，那么怎么样？这就是CTEs发挥作用的地方。 with toronto_ppl as ( SELECT DISTINCT name FROM population WHERE cou

引言 MySQL 的库表设计，在很多时候我们都是率性而为，往往在前期的设计中考虑并不全面，同时对于库表结构的划分也并不明确，所以很多时候在开发过程中，代码敲着敲着会去重构某张表结构，甚至大面积重构多张表结构，这种随心所欲的设计方式，无疑给开发造成了很大困扰。 但实际上设计 DB 库表结构时，也有一些共同需要遵守的规范，这些规范在数据库设计中被称为“范式”，理解并掌握这些设计时的规范，能让咱们在项目之初，设计的库表结构更为合理且优雅。数据库范式中，声名远扬的有三大范式，但除此之外也有一些其他设计规范，如： • ①数据库三大范式（ 1NF、2NF、3NF ） • ③第四范式( 4NF ）和第五范式：完美范式（ 5NF ） • ②巴斯-科德范式（ BCNF ） • ④反范式设计 不过对于上述的几种设计范式，大部分小伙伴应该仅了解过三范式，对于其他的应该未曾接触，那在本篇中会重点阐述库表设计时，会用到的这些范式。 一、数据库三大范式 范式( Normal Form )在前面也提到过，它就是指设计数据库时要遵守的一些原则，而数据库的三大范式，相信诸位...

一、什么是前缀索引？ 所谓前缀索引，说白了就是对文本的前几个字符建立索引（ 具体是几个字符在建立索引时去指定 ），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！ 有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。 二、为什么要用前缀索引？ 可能有的同学会发出疑问，为什么不对整个字段建立索引呢？ 一般来说，当某个字段的数据量太大，而且查询又非常的频繁时，使用前缀索引能有效的减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。 比如，客户店铺名称，有的名称很长，有的很短，如果完全按照全覆盖来建索引，索引的存储空间可能会非常的大， 有的表如果索引创建的很多，甚至会出现索引存储的空间都比数据表的存储空间大很多 ，因此对于这种文本很长的字段，我们可以截取前几个字符来建索引，在一定程度上，既能满足数据的查询效率要求，又能节省索引存储空间。 但是另一方面，前缀索引也有它的缺点，MySQL...

引言 在上篇文章中，我们以《MySQL架构篇》拉开了 MySQL 数据库的的序幕，上篇文章中将 MySQL 分层架构中的每一层都进行了详细阐述。而在本篇中，则会进一步站在一条 SQL 的角度，从 SQL 的诞生开始，到 SQL 执行、数据返回等全链路进行分析。 在此之前呢，其实也写过两篇类似的姊妹篇，第一篇讲的是《一个网络请求的神奇之旅！》，在其中化身一个网络请求，切身感受了从浏览器发出后，到响应数据的返回。而在更早的时候，《JVM系列》中也有一篇文章，讲的是《一个Java对象从诞生到死亡的历程》，其中聊到了 Java 对象是如何诞生的，运行时会经历什么过程？使用结束后又是如何回收的。 在本篇中，则会在站在数据库的视角，再次感受“一条 SQL 多姿多彩的历程”！你如果认真的看完了“一个请求、一个对象、一条 SQL ”这三部曲后，相信你对于程序开发又会有一个全新的深刻认知。 一、一条SQL是如何诞生的？ SQL 语句都诞生于客户端，主要有两种方式产生一条 SQL ，一种是由开发者自己手动编写，另一种则是相关的 ORM 框架自动生成，一般情...

引言 无论你是前端还是后端，只要是一个合格的开发者，对于 MySQL 这个名词相信都不陌生， MySQL 逐渐成为了最受欢迎的关系型数据库，无论你是大前端，亦或是 Java、Go、Python、C/C 、PHP.... 等这些语言的程序员，对于 MySQL 是必然要掌握的核心技术之一，程序员不能没有 MySQL ，就像西方不能失去耶路撒冷一般。 当然， MySQL 也不仅仅是唯一的数据库，与它类似的关系型数据库竞品还有很多，例如 Oracle、SQLServer、PostgreSQL、DB2.... ，这其中使用最为广泛的是 Oracle ，但 Oracle 实际上并不怎么受程序员欢迎，或者说 Oracle 并不怎么受中小企业的 Boss 欢迎，原因嘛大家都清楚，无非因为它收费罢了。 也正是由于 Oracle 收费的原因，才导致 MySQL 像如今这么流行，正所谓时势造英雄， MySQL 作为免费的开源数据库，也正是抓住了这个风口，所以才越发流行。对于 MySQL ，用一句话形容很贴切：“天不生我 MySQL ，编程万古如长夜”。 一

讲述了 MySQL semijoin 从识别到优化器根据代价决定最优执行策略，以及执行方式的全过程，掌握 MySQL semijoin 这一篇就够了！ MySQL semijoin也被称为半连接，主要是解决外查询一个或多个表的记录在包含一个或多个表的子查询结果中的存在性问题，比如IN/EXIST子查询。如果按照IN/EXIST谓词的原语义去执行，对外查询的每行记录都去计算IN/EXIST谓词的结果，子查询的内容就需要单独执行，在关联子查询的情况下，子查询需要多次重复执行，整体的执行效率很低。实际上，部分存在性问题(SPJ 子查询)类似于外查询的多个表与子查询的多个表(以下简述为外表和内表)的连接(JOIN)问题，能连接上说明存在。但是与普通JOIN不同的点在于，当外表和内表JOIN的时候，外表的每行记录都有可能和内表的多行连接上，这就导致了外表行的重复记录，但是按照原语义IN/EXIST子查询只是对外表行上进行的过滤，数据只能减少，而不能膨胀。 如何在外表和内表JOIN的同时保证这一点呢？MySQL中实现了四种执行策略来避免重复行：Materialize、DuplicateWee...

0、前言 原项目框架 SpringBoot + MybatisPlus + Mysql 1、切换流程 1.1、项目引入postgresql驱动包 由于我们要连接新的数据库，理所当然的要引入该数据库的驱动包，这与mysql驱动包类似 <dependency     <groupId org.postgresql</groupId     <artifactId postgresql</artifactId </dependency 1.2、修改jdbc连接信息 之前用的是mysql协议，现在改成postgresql连接协议 spring:   datasource:       修改驱动类     driver-class-name: org.postgresql.Driver       修改连接地址     url: jdbc:postgresql://数据库地址/数据库名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTim

先看下这一连串的MySQL锁问题你能接住几个先！ • 面试官👨‍⚖️：你好呀，小伙子 • 我🧑‍💻：嗯嗯，你好你好 • 面试官👨‍⚖️：简历上说熟悉MySQL锁，那你讲讲MySQL有哪些锁 • 我🧑‍💻：表锁、行锁...，心想我这是背过多少遍了 • 面试官👨‍⚖️：什么是意向锁、auto-inc锁，有啥作用呢？ • 我🧑‍💻：我知道，但为什么要加呢、、、 • 面试官👨‍⚖️：那换一个好吧，举个说说行锁的加锁原理？ • 我🧑‍💻：完了、我还不知道呀，对呀，既然加了锁，那 • 面试官👨‍⚖️：那死锁知道吧，举个栗子，如何避免死锁展开讲讲？ • 我🧑‍💻：好，那我举一个吧，心里已经萌生退意了 为什么需要引入锁 锁是计算机协调多个进程或线程并发访问某一资源的一种机制，在并发事务下保证数据的正确和唯一性。 锁在 MySQL 中是非常重要的一部分，对 MyS...

本文主要通过图示介绍了用主键进行分片查询的过程，介绍了主键分页查询存在SQL性能问题，如何去创建高效的索引去优化主键分页查询的SQL性能问题 对于数据分布不均如何发现，提供了一些SQL查询案例来进行参考，对MySQL Index Condition Pushdown优化算法做了一些简单介绍。 一、背景介绍 最近在线上环境发现了一条执行较慢的分页查询，高并发执行，产生了大量的慢查询日志，CPU使用率逐步升高。 通过观察它的执行时间，发现该SQL查询时快时慢，执行时间并不稳定，以至于在高并发执行场景时，数据库来不及响应，数据库服务变慢。 二、分析定位 2.1 定位 SQL 执行变慢的原因 通过数据库管理平台查看SQL执行信息发现，SQL解析行数（扫描行数）和SQL执行时间都很...

对于 MySQL 索引，相信每位后端同学日常工作中经常会用到，但是对其索引原理，却可能未曾真正深入了解。B 树和 B+ 树是 MySQL 索引使用的数据结构，对于索引优化和原理理解都非常重要，下面就揭开 B 树和 B+ 树的神秘面纱，让大家在面试的时候碰到这个知识点一往无前，不再成为你前进的羁绊！ 本文主要内容： 1. MySQL 查询耗时分析，抓性能优化核心问题点 2. 常见用于查询的数据结构，性能优劣对比 3. B-Tree 与 B+Tree 如何选择，结合场景来做分析更靠谱 4. InnoDB 中的索引介绍，知己知彼，应用得心应手 一、查询耗时分析 我们首先进行下查询耗时分析，抓性能优化核心问题点。 1.1 记录读取顺序 MYSQL维护着自己的缓存空间，如果要读取一条记录，根据优先顺序，路径如下： 缓存区 = 磁盘缓存区 = 磁盘 1.1.1 缓存区读取 这是成本最低的方式，可以直接被CPU使用，不涉及磁盘IO，可以考虑IO时间为0。 1.1.2 从磁盘缓存区读取 如果

前言 我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效，但有时需求就是如此，类似这样的需求还有很多。 例如，搜索引擎需要根基用户数据的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是 B+ 树索引能很好完成的工作。 通过数值比较，范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较，全文索引就是为这种场景设计的。 全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。 在早期的 MySQL 中，InnoDB 并不支持全文检索技术，从 MySQL 5.6 开始，InnoDB 开始支持全文检索 倒排索引 全文检索通常使用倒排索引（inverted index）来实现，倒排索引同 B+Tree 一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一...

实验环境 | 序号 | 主机名 | IP地址 | 备注 | | --- | ----------- | -------------- | --------- | | 1 | mysql-master | 192.168.204.201 | MySQL主库 | | 2 | mysql-slave | 192.168.204.202 | MySQL从库 | | 3 | appserver | 192.168.204.111 | 应用服务器 | 安装配置 MySQL数据库 1.使用yum安装mysql和mysql-server yum install -y mariadb mariadb-server 2.启动mysql服务 systemctl start mariadb systemctl enab