weixin_47184173的博客

搞清楚了MySQL底层的数据结构B+树后，我们应该知道整棵树的非叶子节点存放的都是仅仅是索引，而真正的值都存储在叶子节点中。而我们的MySQL中的索引种类其实又细分为了很多种，本篇带大家一起熟悉MySQL中InnoDB引擎下的那些索引。聚集索引/聚簇索引/主键索引InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。 聚集...

MyISAM 和 InnoDB 的区别是什么?InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。 I..

InnoDB级别的锁化分按照 MySQL 官方的说法，InnoDB 中锁可以分为：可见，InnoDB 中锁非常多，总的来说，可以如下分类：锁的意义——解决并发事务问题我们已经知道事务并发执行时可能带来的各种问题，最大的一个难点是：一 方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一 致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下。而一般新手朋友们都有一个疑惑。MySQL花了那么大的功夫，搞出来了MVCC机制提高并发。R

MySQL中的各种锁根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。本节重点讲解一下全局锁以及表锁，InnoDB级别的锁比较特殊，我们单独开一个章节讲解。全局锁顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和

事务隔离级别（面试必问）隔离级别我们上边介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题也 有轻重缓急之分，我们给这些问题按照严重性来排一下序：脏读 > 不可重复读 > 幻读隔离级别就是为了解决这些问题的。隔离的越严实，效率就越低，并发越低，安全性越高。隔离级别分为以下 4 种：四种隔离级别的概念读未提交（read uncommitted，RU）一个事务还未提交时，它做的变更就可以被别的事务看到。读提交（read committed，RC）事务提交

InnoDB存储引擎的三个关键特性：插入缓冲（insertbuffer）、二次写（doublewrite）、自适应哈希索引（adaptivehashindex）。insert/change buffer什么是change buffer？在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。它是一种应用在非唯一普通索引页(non-unique secondary i...

InnoDB的脏页刷盘时机场景一：redo log快满了我们知道，redo log的空间是固定大小的，那么就就有使用完毕的风险。假设某个时刻系统疯狂的进行修改操作，那么大量的日志就会进入redo log中。此时刷盘速度（对应check point前进速度）的远远小于写盘速度（write pos前进速度）时，就会触发刷盘。但这种刷盘的后果是非常严重的，和上图一样，这时系统就会停止所有更新操作，把checkoutpoint 往前推，redo log留出空间可以继续写。此时整个系统都不能接受更新。

事务的四大特性能被称为事务，就一定具备以下四个特性：分别是原子性、一致性、隔离性、持久性。俗称ACID。原子性（Atomicity）原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。一致性（Consistency）一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那

MySQL级别的缓存说起缓存，我们回忆起MySQL的Server层有一个缓存，这个缓存在MySQL8.0之后被直接禁用了。随着技术的进步，经过时间的考验，MySQL 的工程团队发现启用缓存的好处并不多。首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。而且为了维护缓存结果的正确性，我们还需要频繁的去更新缓存。其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。 （所以用了InnoDB级

表空间的基本概念表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd 的实际文件（frm存的是表结构文件，也就是你alert表的结构）。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。InnoDB 是以页为单位管理存储空间的，我们的聚簇索引（也 就是完整的表数据）和其他的二级索引都是以 B+树的形式保存到表空间的，而B+树的节点就是数

MySQL 对于我们来说还是一个黑盒，我们只负责使用客户端发送请求并等待服务器返回结果，表中的数据到底存到了哪里？以什么格式存放的？MySQL 是以 什么方式来访问的这些数据？这些问题我们统统不知道。要搞明白查询优化背后 的原理，就必须深入 MySQL 的底层去一探究竟，而且事务、锁等的原理也要求 我们必须深入底层。InnoDB存储数据的实际流程InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。那么，很多新手理所当然的会理解成——每次我们存储，查询数

我们常说的SQL优化是指什么在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。所谓的优化查询，实际上意味着要优化其子任务。要么消除其中一些子任务，要 么减少子任务的执行次数，要么让子任务运行得更快。MySQL 查询的生命周期大致可以按照顺序来看:客户端->服务器->在服务器上进行解析->生成执行计划->执行->返回结果给客户端。其中“执行” 可以认为是整个生命周期中最

优化器的功能不改变语义的情况下，重写sql。重写后的sql更简单，更方便制定执行计划。 根据成本分析，制定执行计划。对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写。MySQL开发人员对优化器进行了大量的优化措施，主要干的也就是这个事儿。可见我们就算开发功底不咋样，写的烂sql经过优化器也能优化成还不错的样子进入InnoDB引擎。那么这些优化规则就让我们一起探索一下。条件化简我们.

通过 SHOW TABLE STATUS 可以看到关于表的统计数据，通过 SHOW INDEX 可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？我们知道，Explain的计划选择，就是根据这些统计信息来判断的。因此，理解这些统计方式，我们才知道MySQL到底为什么可以在优化阶段时，选择一个它认为的最佳执行方案。统计数据存储方式永久性的统计数据这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。非永久性的统计数据这种统计数据存储在内存中，

我们在开启慢查询日志时，发现了有sql慢查询，之该怎么解决呢？Explain（优化器分析sql后的查询信息）Explain使用场景这个指令一定一定是我们最常用的指令，没有之一，因为他显示的数据言简意赅，对于MySQL基础掌握牢固的程序员，可以一眼看出sql是否有问题。比如索引信息，预估行数，查询类型等等，都可以帮我们快速分析到sql查询慢的问题。一般，假设表的索引设计不到位，或者sql写的不正确，百分之90以上的问题都可以通过Explain语句快速定位到问题。所以说EXPLAIN 语法可以说

连接查询的本质新建两张表CREATE TABLE e1 (m1 int, n1 char(1)); CREATE TABLE e2 (m2 int, n2 char(1)); INSERT INTO e1 VALUES(1, 'a'), (2, 'b'), (3, 'c'); INSERT INTO e2 VALUES(2, 'b'), (3, 'c'), (4, 'd');连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。连接内部实现这个过

MySQL有哪些查询成本MySQL 执行一个查询可以有不同的执行方案。在我们开发过程中，所有写过的sql语句都会丢给MySQL端的优化器。由优化器判断并选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在 MySQL优化器去考察一条sql执行成本是由下边这两个方面。I/O 成本我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从

过 MySQL 在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二 级索引，MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为：索引 合并/index merge，具体的索引合并算法有下边三种。Intersection 交集合并某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：SELECT * FROM order_exp WHERE order_no = 'a' AND exp

简介分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引,但是实际上这个表可能由数 10 个物理分区对象组成，每个分区都是一个独立的对象，从局部来说，每个分区别可以独自处理任务，从整体来说，每个分区表可以作为总表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。分区表的底层存储结构从上面的说明我们可以看到，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的的封装。

不在索引列上做任何操作我们的一些不正当的使用方式，往往会导致我们使用了索引却没有效果。MySQL的索引列必须是独立的列，才能正常使用索引。什么是独立的列呢？我们举下面两个反例：在索引列做运算mysql>SELECT * FROM order_exp WHERE order_status + 1 = 1;凭肉眼很容易看出 WHERE 中的表达式其实等价于 order_status = 0，但是MySQL 无法自动解析这个方程式。这完全是用户行为。我们应该养成简化 WHERE条件的习

什么是执行计划我们往往通过慢查询日志，可以找到慢查询的sql具体是怎么写的。我们往往可以通过表的索引，执行引擎等等去自己判断sql为什么执行缓慢的原因。但是这明显不是高效的。我们的MySQL中为我们提供了EXPLAN关键字来协助我们分析SQL。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个查询语

优化金字塔性能优化是一个很大的话题。而真正的内存优化也存在一个金字塔之说。具体的硬件调优，一般属于DBA专门来操作的。对于 MySQL 调优，需要确认业务表结构设计是否合理，SQL 语句优化是否足 够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL 和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 I/O 存

索引的弊端这么说，索引这么好用，我们疯狂创建索引岂不是速度飞起？其实并不是的，在学习如何更好的使用索引之前先要了解一下使用它的代价，它在空间和时间上都会拖后腿。空间上的代价这个是显而易见的，每建立一个索引都要为它建立一棵 B+树，每一棵 B+树 的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大 的 B+树由许多数据页组成会占据很多的存储空间。时间上的代价每次对表做出任何修改，我们都需要去维护该表下面所有的索引信息。这必然会对性能造成影响。那么如何建立必

索引在查询过程中的作用一个索引就是一个 B+树，索引让我们的查询可以快速定位和扫描到我们 需要的数据记录上，加快查询的速度。 一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即使在 where 条件中用了多个二级索引。（比较苛刻条件下会出现索引合并）扫描区间全表扫描对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断 每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该录。这就是全表扫描。对于使用 InnoDB 存储引擎的表来说，全表扫

数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用 缩写 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个 下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布， 所以字段名称需要慎重考虑。 说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任 何大写字母，避免节外生枝。 表名不使用复数名词 数据库、表、字段的命名禁用保留字，如 desc、range、match 之类 对象的

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重 要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。基本原则更小的通常更好一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据 类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要 的 CPU 周期也更少。 但是要确保没有低估需要存储的值的范围，因为在的多个地方增加数据类型 的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就 选择你认为不会超过范围的

良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设 计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严 重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花 费大量不必要的优化时间，效果往往还不怎么样。在数据库表设计上有个很重要的设计准则，称为范式设计。范式（前三式）概述与设计范式来自英文 Normal Form，简称 NF。要想设计—个好的关系，必须使关系 满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求 得严格。满足

什么是MySQL事务事务就是为了保证一组数据库操作，要么全部成功，要么全部失败。事务是在引擎层实现的，也就是说并不是所有引擎都可以使用事务，MyISAM 就不支持事务，这也是为什么会被 InnoDB 取代的原因。没有事务造成的问题事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位（不可再进行 分割），由一个有限的数据库操作序列构成（多个 DML 语句，select 语句不包含 事务），要不全部成功，要不全部不成功。 A 给 B 要划钱，A 的账户-1000 元， B 的账户就要+100

什么是文件系统我们知道像 InnoDB、MyIASM 这样的存储引擎都是把表存储在磁盘上的（持久化）。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们， 当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。当然，MySQL除了存储实际的数据，还存储了一系列其他的日志，在这些也属于文件系统。存储引擎的落盘文件地址使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：show variables like 'datadir';当然这个目录可以

在博主最开始接触mysql的时候，甚至不知道索引这个东西。直到有一天写的代码查询速度越来越慢无计可施时，才腼腆的问了一下隔壁大佬该如何解决。原本以为大佬会过来帮我检查一下sql是否写的有问题，哪知道大佬直接问了一句："你的数据库加索引了吧？"索......索引是啥子东西？大佬无语道："索引不加，肯定查询效率上不去啊。"受到打击的博主立刻去补充了索引的相关知识。在此篇中和大家好好聊聊索引。MySQL为什么要建立索引？首先，还是那个问题，为什么要出现索引这么概念。我们都知道，我们在写CR