Table of Contents
MySQL内核InnoDB存储引擎(卷1)
目录
概览
基本数据结构和算法
同步机制
重做日志
mini-transaction(mtr)
存储管理
记录
索引页
B+树索引
Insert Buffer
缓冲池
事务处理
数据字典
服务管理
Home Database Mysql Tutorial MySQL内核InnoDB存储引擎(卷1)笔记_MySQL

MySQL内核InnoDB存储引擎(卷1)笔记_MySQL

Jun 01, 2016 pm 01:04 PM
Kernel engine notes

MySQL内核InnoDB存储引擎(卷1)

目录

  • 1 概览
  • 2 基本数据结构和算法
  • 3 同步机制
  • 4 重做日志
  • 5 mini-transaction(mtr)
  • 6 存储管理
  • 7 记录
  • 8 索引页
  • 9 锁
  • 10 B+树索引
  • 11 Insert Buffer
  • 12 缓冲池
  • 13 事务处理
  • 14 数据字典
  • 15 服务管理

    概览

    基本数据结构和算法

    同步机制

    rw-lock/latch s-/x-:x-可递归,s-不可?;以spin获得,一段时间后进入wait array(信号量?) p38 若sync_primary_wait_array中1000个cell都已分配,则ut_error触发crash 当持有latch的线程释放latch后,调用sync_array_signal_object唤醒等待线程

    重做日志

    p42 redo log原来保证事务的持久性(D),undo log用于回滚和MVCCinnodb_flush_log_at_trx_commit=0/1/2redo log VS. bin log 前者记录的是页的物理逻辑操作日志设计思想:物理日志记录页内的修改(old-new value),逻辑日志记录对表的操作(insert/delete) LSN(表示事务写入redo log的字节量?) 对‘检查点’,表示刷新到磁盘的位置?——不管怎么说,LSN有一种‘随时间单调变化’的性质 检查点:将缓冲池中的页刷新到磁盘 sharpfuzzy* redo日志的大小是固定的(3GB)->归档日志ib_logfileredo日志块(512B-12-8) 和磁盘扇区大小一样,保证原子性,不需要double write? 重做日志组*组提交:fsync -> log_flush_up_to 会对最后一个日志块进行复制恢复:recovery_from_checkpoint_start 表空间第一个页头部的FIL_PAGE_FILE_FLUSH_LSN记录了数据库关闭时最后刷新页的LSNrecv_parse_or_apply_log_rec_bodyrecv_add_to_hash_tablerecv_recover_pagerecv_read_in_area 判断页所在相邻的32个页?

    mini-transaction(mtr)

    FIX rules:修改页之前需要持有该页的latchWAL 每个页需要有一个LSN?LSN溢出怎么办? Force-Log-at-Commitmtr_t mtr; mtr_start(&mtr); ... mtr_commit(&mtr); 提交时若mtr->modified==TRUE,先修改缓冲池中的页*1,然后释放log_sys->mutex(这是一个热点) *1 log_reserve_and_write_fast/log_write_slow 快速/慢速2个路径 更新多行记录时,MLOG_MULTI_REC_END

    存储管理

    页:(space_id, offset) 16KB1 extent = 64 连续的page space header 段(segment) 每张用户表至少2个段:聚集索引(B+树)的叶子节点和非叶子节点段一个段最多可以管理32个独立的页,和若干区 表空间数据结构:fil_system/space/node_struct4个异步I/O线程:异步读、异步写、插入缓存、重做日志

    记录

    物理记录 p102 用户记录的heap no总是从2开始 伪记录:Infimum/Supremum(感觉将像是双链表的first/last) p103 VARCHAR类型的NULL不占用磁盘空间,而CHAR NULL用0x00填充大记录:BLOB/TEXT(溢出页,extern属性) 逻辑记录 dtuple_struct,对大记录是big_rec_structB+树索引只定位页,页内记录需要二分扫描 mtype/prtype 行记录版本(MVCC只是列?):通过隐藏的事务ID列 read_view_struct: low/up_limit_idtrx_ids, n_trx_idscreator p114 函数read_view_sees_trx_id用来判断当前事务是否可以读记录的当前版本,不是,则row_sel_build_prev_vers_for_mysql

    索引页

    Page Header 页内记录根据主键是逻辑顺序,不是物理顺序 Page Directory(定位记录在页内的位置) slot?offset的主键逆序记录 Page Cursor*

    p136 理论上,隔离级别越低,事务请求的锁越少或保持锁的时间越短幻读:谓词锁 --> key-range locking --> next/previous-key lockingp138 意向锁:意味着事务希望在更细粒度上加锁 InnoDB是行级锁,不会阻止全表扫描以外的请求 lock_rec_struct = { space, page_no, n_bits } 所有锁对象通过kernel_mutex进行保护(又一个热点!) 优化:细粒度拆分? p144 LOCK_GAP(代表范围锁不包含端点)显式锁和隐式锁**(略)行锁的维护*(重点,略) 插入更新PURGE一致性的锁定读页的分裂页的合并 自增锁(atomic?)死锁*

    B+树索引

    聚集 / 辅助分裂操作:btr_page_split_and_insert合并:btr_compress查找:btr_cur_search_to_nth_level p203 对唯一约束的键值,需要使用模式PAGE_CUR_GE,而不是LElatch_modecursor DML操作 乐观插入:btr_cur_optimistic_insert非主键更新(主要是列的大小会不会发生变化) btr_cur_optimistic_update --> btr_cur_pessimistic_update(例略) 主键更新 删除 持久游标 btr_pcur_struct自适应哈希索引*

    Insert Buffer

    将多次插入合并为一次操作(提高了非唯一约束辅助索引的插入性能)p237 实现最为困难的在于对死锁的处理 页逻辑层次划分:非IB页、IB非bitmap页、bitmap页p241 异步I/O线程可能引起死锁问题 --> rw_lock_x_lock_move_ownership

    缓冲池

    LRU、Free和Flush链表预读 p258 随机预读 要满足32个页中9个已经访问过且都是活跃的才可能触发 线性预读*逻辑预读 页的刷新 部分写问题(?) --> double write(存在于内存的表空间,大小为2MB,这意味着最多128页/次刷新)

    事务处理

    分类:扁平、带保存点的扁平、链、嵌套、分布式事务系统段*doublewrite段*undo日志存储 一致性的非锁定读 p282 读取快照不需要加锁 undo日志实现:回滚段 + undo段 trx_undo_struct undo记录purge*rollback 7B roll_ptr隐藏列 {rseg_id(1), page_no(4), offset(2)}3个回滚类型:TRX_SIG_{TOTAL_ROLLBACK, ROLLBACK_TO_SAVEPT, ERROR_OCCURRED} commit

    数据字典

    服务管理

  • Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    AI Hentai Generator

    AI Hentai Generator

    Generate AI Hentai for free.

    Hot Article

    R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
    2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
    Repo: How To Revive Teammates
    4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
    Hello Kitty Island Adventure: How To Get Giant Seeds
    3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

    Hot Tools

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    How to delete Xiaohongshu notes How to delete Xiaohongshu notes Mar 21, 2024 pm 08:12 PM

    How to delete Xiaohongshu notes? Notes can be edited in the Xiaohongshu APP. Most users don’t know how to delete Xiaohongshu notes. Next, the editor brings users pictures and texts on how to delete Xiaohongshu notes. Tutorial, interested users come and take a look! Xiaohongshu usage tutorial How to delete Xiaohongshu notes 1. First open the Xiaohongshu APP and enter the main page, select [Me] in the lower right corner to enter the special area; 2. Then in the My area, click on the note page shown in the picture below , select the note you want to delete; 3. Enter the note page, click [three dots] in the upper right corner; 4. Finally, the function bar will expand at the bottom, click [Delete] to complete.

    Can deleted notes on Xiaohongshu be recovered? Can deleted notes on Xiaohongshu be recovered? Oct 31, 2023 pm 05:36 PM

    Notes deleted from Xiaohongshu cannot be recovered. As a knowledge sharing and shopping platform, Xiaohongshu provides users with the function of recording notes and collecting useful information. According to Xiaohongshu’s official statement, deleted notes cannot be recovered. The Xiaohongshu platform does not provide a dedicated note recovery function. This means that once a note is deleted in Xiaohongshu, whether it is accidentally deleted or for other reasons, it is generally impossible to retrieve the deleted content from the platform. If you encounter special circumstances, you can try to contact Xiaohongshu’s customer service team to see if they can help solve the problem.

    What should I do if the notes I posted on Xiaohongshu are missing? What's the reason why the notes it just sent can't be found? What should I do if the notes I posted on Xiaohongshu are missing? What's the reason why the notes it just sent can't be found? Mar 21, 2024 pm 09:30 PM

    As a Xiaohongshu user, we have all encountered the situation where published notes suddenly disappeared, which is undoubtedly confusing and worrying. In this case, what should we do? This article will focus on the topic of "What to do if the notes published by Xiaohongshu are missing" and give you a detailed answer. 1. What should I do if the notes published by Xiaohongshu are missing? First, don't panic. If you find that your notes are missing, staying calm is key and don't panic. This may be caused by platform system failure or operational errors. Checking release records is easy. Just open the Xiaohongshu App and click "Me" → "Publish" → "All Publications" to view your own publishing records. Here you can easily find previously published notes. 3.Repost. If found

    How to connect Apple Notes on iPhone in the latest iOS 17 system How to connect Apple Notes on iPhone in the latest iOS 17 system Sep 22, 2023 pm 05:01 PM

    Link AppleNotes on iPhone using the Add Link feature. Notes: You can only create links between Apple Notes on iPhone if you have iOS17 installed. Open the Notes app on your iPhone. Now, open the note where you want to add the link. You can also choose to create a new note. Click anywhere on the screen. This will show you a menu. Click the arrow on the right to see the "Add link" option. click it. Now you can type the name of the note or the web page URL. Then, click Done in the upper right corner and the added link will appear in the note. If you want to add a link to a word, just double-click the word to select it, select "Add Link" and press

    How to add product links in notes in Xiaohongshu Tutorial on adding product links in notes in Xiaohongshu How to add product links in notes in Xiaohongshu Tutorial on adding product links in notes in Xiaohongshu Mar 12, 2024 am 10:40 AM

    How to add product links in notes in Xiaohongshu? In the Xiaohongshu app, users can not only browse various contents but also shop, so there is a lot of content about shopping recommendations and good product sharing in this app. If If you are an expert on this app, you can also share some shopping experiences, find merchants for cooperation, add links in notes, etc. Many people are willing to use this app for shopping, because it is not only convenient, but also has many Experts will make some recommendations. You can browse interesting content and see if there are any clothing products that suit you. Let’s take a look at how to add product links to notes! How to add product links to Xiaohongshu Notes Open the app on the desktop of your mobile phone. Click on the app homepage

    How to install the Linux kernel on Ubuntu 22.04 Detailed tutorial! How to install the Linux kernel on Ubuntu 22.04 Detailed tutorial! Mar 01, 2024 pm 10:34 PM

    To install the Linux kernel on Ubuntu22.04, you can follow the following steps: Update the system: First, make sure your Ubuntu system is the latest, execute the following command to update the system package: sudoaptupdatesudoaptupgrade Download the kernel file: Visit the official Linux kernel website () to download Required kernel version. Select a stable version and download the source code file (with .tar.gz or .tar.xz extension), for example: wget Unzip the file: Use the following command to unzip the downloaded kernel source code file: tar-xflinux-5.14.tar. xz install build dependencies: Install the tools and dependencies required to build the kernel. Execute

    Modify Linux kernel startup sequence Modify Linux kernel startup sequence Feb 23, 2024 pm 10:22 PM

    Modify the kernel startup sequence of Linux 1. Modify the kernel startup sequence of RHEL6/CentOS6. Check the /etc/grub.conf file to determine the system kernel situation. According to the document, there are two kernel versions in the system, namely 2.6.32-573.18.1.el6.x86_64 and 2.6.32-431.23.3.el6.x86_64. Kernel versions are listed from top to bottom. In the grub.conf file, you can decide which kernel version to use when the system starts by adjusting the default parameters. The default value is 0, which means the system will boot the latest kernel version. A value of 0 corresponds to the first content listed in the grub.conf file.

    How to publish notes tutorial on Xiaohongshu? Can it block people by posting notes? How to publish notes tutorial on Xiaohongshu? Can it block people by posting notes? Mar 25, 2024 pm 03:20 PM

    As a lifestyle sharing platform, Xiaohongshu covers notes in various fields such as food, travel, and beauty. Many users want to share their notes on Xiaohongshu but don’t know how to do it. In this article, we will detail the process of posting notes on Xiaohongshu and explore how to block specific users on the platform. 1. How to publish notes tutorial on Xiaohongshu? 1. Register and log in: First, you need to download the Xiaohongshu APP on your mobile phone and complete the registration and login. It is very important to complete your personal information in the personal center. By uploading your avatar, filling in your nickname and personal introduction, you can make it easier for other users to understand your information, and also help them pay better attention to your notes. 3. Select the publishing channel: At the bottom of the homepage, click the "Send Notes" button and select the channel you want to publish.

    See all articles