u200814342A的博客

时，MySQL缓存也是不起作用的；6. 在表的结构或数据发生改变时，基于该表相关cache立即。

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到。

MyISAM引擎：count（字段) < count(1)

MYSQL中的ALTER TABLE操作的性能对于大表来说是一个很大的问题。”，用要求的表结构创建一张和源表无关的新表，通过重命名交换两张表。上执行ALTER TABLE操作，然后和提供服务的。，由于修改表结构是一个事务，因此失败后会还。结构，在这个过程中表都是锁着不可写入。* 在线修改大表的表结构执行时间。* 如果长时间的修改表结构，中途。，专门提供mysql技术服务的。* 在线修改大表结构容易导致。* 修改大表结构容易导致。，因此在修改表结构时，* 由于修改表结构是。，从而影响业务读取。

后续某个时间点再一次性将多个操作记录写到 redo log file。的日志，在数据库事务开始之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。的，它会记录一个变动的过程或SQL语句的逻辑, 比如: 把一个page页中的一个数据从1改为2，再从2改为3,逻辑日志就会记录1->2,2->3这个数据。的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是。

信息等，每一张表都会有一个frm 文件。MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个 .MYD 文件。MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。ibd文件和 IBDATA 文件：存放 InnoDB 的。

这个图可以看出，在线程 63（也就是获得了 id = 100 排他锁的线程）在尝试获取 id = 1 时 被 block 了，block 他的线程是线程 61，因为线程 61 正在获得 id = 1 的排他锁。如果死锁的发生频率较高，可能需要增加资源，例如增加服务器的处理能力或增加数据库的缓存空间，从而降低死锁的发生率。最后判断图中是否有回路，如果有回路，则认为有死锁，若发现有死锁，回滚 undo 量最小的记录的事务。如果死锁的发生频率较高，可能需要重构事务，重新设计事务的逻辑，从而避免死锁的发生。

会产生更新丢失现象。可以分为和。回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。一个事务读取到了另一个事务修改但的数据。一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。一个事务中按。后续查询的结果和面前查询结果不同，多了或少了几行记录。“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库一致性问题，为了解决这些问题，MySQL数据库是通过。

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。锁定粒度界于表锁和行锁之间，并发度一般。才会进行冲突检测，如果发现冲突了，则提示错误信息。锁定粒度大，发生锁冲突的概率最高，并发度最低。页面锁：开销和加锁时间界于表锁和行锁。乐观锁：一般的实现方式是。，再修改的控制方式。

RR隔离级别下间隙锁才有效，RC隔离级别下没有间隙锁；RR隔离级别下为了解决“幻读”问题：“

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。如果一个索引包含了 所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引。在explain的Extra列, 如果出现 **Using index。索引做覆盖索引 (因为只有B+树能存储索引列值)将被查询的字段建立普通索引或者联合索引。, 所取的数据完全在索引中就能拿到。知识来源：马士兵教育。

会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换。通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效。知识速记：or like null 不等于 最左原则 类型 函数 运算 编码 全表。= 或者 < >，not in）时，会导致索引失效。不同，不能命中索引，会导致笛卡尔积的循环计算。（如，+、-、*、/），索引失效。，查询时的条件列不是联合索引中的。知识来源：马士兵教育。

如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要。连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能。到 buffer 中。

在数据页的User Records中插入的记录是一条一条紧凑的排列的，这种紧凑排列的结构又被称为堆。表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，可以理解为指向下一条记录地址的指针。这个属性表示当前记录的类型，一共有4种类型的记录， 0 表示普通用户记录， 1 表示B+树非叶节点记录， 2 表示最小记录， 3 表示最大记录。，每当向某个包含隐藏的row_id列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1。

可能有的同学会有疑问，为啥写两次，刷一次数据文件保存数据不就可以了，为什么还要写共享表空间?：当进行缓冲池中的脏页刷新到磁盘的操作时,并不会直接写磁盘,每次脏页刷新必须要先写double write .: 在完成double write页的写入后，再将double wirite buffer中的页写入各个表的。来还原该页，然后再进行redo log重做，double write实现了InnoDB引擎数据页的。，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做。

用来控制old sublist中page的转移策略，新的page页在进入LRU链表中时，会先插入到old sublist的头部，然后page需要在old sublist中停留innodb_old_blocks_time这么久后，下一次对该page的访问才会使其移动到new sublist的头部，默认值1秒.: 在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加innodb_old_blocks_pct的值，并且减小innodb_old_blocks_time的值。在提交之前不写日志到磁盘。

排序操作用的比较多,order by 后面的字段如果在索引中,索引本来就是排好序的,所以速度很快,没有索引的话,就需要从表中拿数据,在内存中进行排序,如果内存空间不够还会发生落盘操作。3.更新非常频繁的字段不适合创建索引.（因为索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的）.如下图是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时长及具体的SQL等信息.最好的情况消耗的资源,最差情况下消耗的资源,优化的结果只有一个给用户一个好的体验。

一般性分页一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：SELECT * FROM 表名 LIMIT [offset,] rows第一个参数指定第一个返回记录行的偏移量，注意从0开始；第二个参数指定返回记录行的最大数目；如果只给定一个参数，它表示返回最大的记录行数目；结果：在查询记录时，返回记录量，查询时间基本没有变化，差距不大。随着查询，所花费的时间也会。结果：在查询记录时，如果查询记录量相同，偏，查询时间急剧的增加。

使用EXPLAIN关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。Explain使用方式:, 通过执行explain可以获得sql语句执行的相关信息实际使用示例：先解析一条sql语句，看出现什么内容SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句，看下面这条sql它的执行结果为可以看到这时的如果是子查询，id序号会自增，id值，越先被执行id 相同与不同，同时存在select类型，

Buffer Pool是MYSQL数据库中的一个重要的，介于和之间的一个，针数据库的增删改查这些操作都是针对这个内存数据结构中的缓存数据执行的,在操作数据之前，都会将数据从磁盘加载到Buffer Pool中，操作完成之后、写undo log、binlog、redolog等一些列操作，。

mysql中数据是存储在物理磁盘上的，而真正的。由于磁盘的读写速度非常慢，如果每次操作都对磁盘进行频繁读写的话，那么性能一定非常差。为了上述问题，InnoDB将数据划分为若干页，以页作为磁盘与内存交互的，一般页的。这样的话，一次性至少读取到内存中或者将1页数据写入磁盘。。其实，这本质上就是一种典型的，一般缓存的设计基本都是从Page是整个InnoDB存储的，也是InnoDB磁盘管理的，与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型，常见的页类型有页（Undo Log Page）

知识点：【2023年面试】mysql索引的基本原理_哔哩哔哩_bilibili【2023年面试】mysql索引结构有哪些，各自的优劣是什么_哔哩哔哩_bilibili

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。oracle使用explain plan for + sql语句进行模拟优化器来执行分析。table 显示这一行的数据是关于哪张表的。+ sql语句进行模拟优化器来执行分析。rows 显示需要扫描行数。直接分析慢查询日志，

除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要简历聚簇索引，那么需要的。，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。，主键用于唯一标识一条记录，使用关键字primary key来创建。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。，因为在执行这些写操作的时候，还要操作索引文件。主键索引：是一种特殊的唯一索引，在一张表中。普通索引：允许被索引的数据列包含。索引可以极大地提高数据的查询速度。唯一索引：可以保证数据记录的。

B树是一种树状数据结构，它能够存储数据、对其进行排序并允许以的时间复杂度进行查找、顺序读取、插入和删除等操作。B树中允许一个结点中包含多个key，可以是3个、4个、5个甚至更多，并不确定，需要看具体的实现。在实际应用中B树的阶数一般都比较大（通常大于100），所以，即使存储大量的数据，B树的高度仍然比较小，这 样在某些应用场景下，就可以体现出它的优势。若参数M选择为5，那么每个结点最多包含4个键值对，我们以5阶B树为例，看看B树的数据存储。

这也就意味着B-Tree索引能够加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。回想BTree的查找操作：进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查 找。尤其是当表中的数据量越来越大时，索引对性能的影响跃愈发重要。那么在MySQL中，存储引擎的用类似的方法使用索引，它先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。，这是一种特殊类型的索引，它查找的是文本中的关键词。

非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。非聚集索引层次多，不会造成数据重排。

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

时候（InnoDB 默认的最大填充因子是页大小的 15/16，会留出 1/16 的空间留作以后的修改）。的聚簇键的值来插入新行。自增的主键的值是顺序的，所以 InnoDB 把每一条记录都存储在一条记录的。，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了。的，新行的值不一定要比之前的主键的值要大，所以 innodb。，速度快，而且是增量增长，按顺序存放，对于检索非常有利。，以便为新的行分配空间，页分裂导致移动大量的数据，的，也就是说新增的数据永远在后面，这点对于。

解题： 2NF非主键列要完全依赖于主键， 主键只能有一个，只能是AC，但是 B 和D 只依赖主键的一部分，所以不满足。id、name(姓名)、sex_code(性别代号)、sex_desc(性别描述)、contact(联系方式)stu_id(学生id)、kc_id(课程id)、score(分数)、kc_name(课程名)，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。(A -> B, B -> C, A->C)(主键)，从而推出sex_desc依赖于id(主键)；

最佳左前缀法则: 如果创建的是,就要遵循该法则. 使用索引时，where后面的条件需要从索引的,并且索引中的列使用。最左匹配原则是什么？简单来讲：在联合索引中，只有左边的字段被用到，右边的才能够被使用到。假如我们创建联合索引 create index idx_a_b on shopTable(a,b);有如下B+树我们看到 最左边的a 都是有序的，分别是 ： 1，1，2，2，3，3 但是右边的b 不一定有序： 1，2，1，4，3，2。

InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，我们重点来看下两者区别。InnoDB支持事务和外键，支持，具有安全性和完整性，适合大量insert或update操作MyISAM不支持事务和外键，它提供，适合大量的操作InnoDB支持，锁定指定记录。基于索引来加锁实现。MyISAM支持，锁定整张表。InnoDB使用（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。可以使用sphinx插件支持全文索引，主键索引保存的是数据，其他索引保存的是主键索引地址。MyISAM使用。

索引下推（index）简称ICP，在Mysql5.6的版本上推出，用于优化查询。需求: 查询users表中 "名字第一个字是张，年龄为10岁的所有记录"。SELECT * FROM users WHERE user_name LIKE '张%' AND user_age = 10;根据最左前缀法则，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录，接下来是怎么处理的呢？当然就是从该记录开始，逐个回表，到主键索引上找出相应的记录，再比对 `age` 这个字段的值是否符合。

自适应Hash索引（Hash Index，内部简称）是InnoDB的，还有两个是 Buffer Pool简称BP、1、自适应即我们，当InnoDB引擎根据发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引；2、hash索引底层的数据结构是，其数据特点就是比较适合在，自适应Hash索引存在于InnoDB架构中的（不存在于磁盘架构中），见下面的InnoDB架构图。3、自适应hash索引只适合。

那么一个页(就是B+Tree中的一个节点) ,大概可以存储: 16384B / (4B+6B) = 1638 ,一个节点最多可以存储。,在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。,所以每个节点的大小也是16KB, 并且B+Tree的根节点是保存在内存中的,子节点才是存储在磁盘上.(这样做的目的是每个节点只需要一次I/O就可以完全载入), InnoDB的一个页的大小是。MySQL设计者将一个B+Tree的节点的大小设置为。