MySql索引篇

MySql索引篇

前言

上一篇文章MySql体系介绍了MySql数据库的大体组成部分以及运行流程，接下来进入到MySql的索引介绍与深入，毕竟学习MySql，索引都是绕不开的一个问题，若索引不清楚，那许多优化也无从下手。

学习目标

1、知道各种索引机制

2、索引的原理

3、索引如何分析与优化

4、实地环境中如何优化

5、深入了解索引（引擎）

MySql索引类型

索引可以提升查询速度，会影响where查询，以及order by排序，从不同维度索引有不同的叫法：

  • 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引以及R Tree索引

  • 从应用层次划分：普通索引、唯一索引、主键索引、复合索引

  • 从键值划分：主键、辅助

  • 从数据存储以及索引逻辑关系划分：聚集索引、非聚集索引

一、普通索引

最普通的索引类型，给予普通字段建立的索引，没有任何限制

例子：

 
 
 
 
 
 
 
ALTER TABLE `user`
ADD INDEX `index_userNm` (`username`) USING BTREE COMMENT '用户名索引';
 

二、唯一索引

与普通索引类似，不同的点在于：索引的值必须唯一，运行有空值。

例子：

 
 
 
 
 
 
 
ALTER TABLE `user` ADD UNIQUE INDEX `index_un_id`(`id`);
 

三、复合索引

单一索引是指索引列只有一列，而用户可以在多个列上建立索引，这种索引就叫复合索引，也叫组合索引，复合索引可以代替多个单一索引，相遇多个单一索引，复合索引所需的开销更小。

索引同时又俩个概念：窄索引、宽索引

窄索引：指索引列为1-2列的索引

宽索引：指索引列超过2列的索引

索引设计原则：能用窄索引不用宽索引，因为窄索引往往比组合索引更有效

创建组合索引：

 
 
 
 
 
 
 
ALTER TABLE `user` ADD INDEX `index_userNm` (`username`，`password`);
 

注意：

  • 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效 率有很大影响

  • 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查 询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高

四、主键索引

与唯一索引的区别就是不能有空值

 
 
 
 
 
 
 
ALTER TABLE `user` ADD PRIMARY KEY (`id`);
 

五、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果 使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全 文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持

 
 
 
 
 
 
 
ALTER TABLE `user` ADD FULLTEXT `index_text`(`user_text`);
 

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

 
 
 
 
 
 
 
select * from user where match(user_text) against('aaa');
 

注意：

  • 全文索引必须在字符串、文本字段上建立

  • 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4- 84）

  • 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa

  • 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布 尔模式下搜索a*

     
     
     
     
     
     
     
    select * from user
    where match(user_text) against('a*' in boolean mode);
     

索引原理

MySql官方定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护 工作。

  • 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储

  • 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价

索引涉及算法及数据结构：

一、二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范 围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

  1. 首先定位left和right两个指针

  2. 计算(left+right)/2

  3. 判断除2后索引位置值与目标值的大小比对

  4. 索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

    如下图：

     

     

     

二、Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 存储数据的结构。非常适合根据key查找 value值，也就是单个key查询，或者说等值查询

 

 

 

从上面的图可以看出，Hash索引可以方便的提供等值查询，范围查询则需要全表扫描了

Hash索引在MySql周主要用于InnoDB自适应哈希索引：

InnoDB自适应哈希索引为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引在创建一个哈希索引，使得内存中的B+Tree索引具备哈希索引的功能，即快速定值访问频繁访问的索引页

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动的为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉

 
 
 
 
 
 
 
show engine innodb status \G;
show variables like '%innodb_adaptive%';
 

三、B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造

  • B-Tree

    • 索引值和data数据分布在整棵树结构中

    • 每个节点可以存放多个索引值及对应的data数据

    • 树节点中的多个索引值从左到右升序排列

       

  • B+Tree

    • 只有叶子节点包含索引和数据

    • 非叶子节点只存储索引值

    • 叶子节点用指针连接，提高区间访问性能

    • 对比B树来说，B+树范围查找时，只需要定位俩个节点的索引值，然后利用叶子节点指针进行遍历即可，而B树需要遍历范围内所有的节点数据，俩相对比，B+树效率更高

    •  

       

四、 聚簇索引和辅助索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行 记录分开存放就属于非聚簇索引

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值 就属于辅助索引（二级索引）

在InnoDB引擎中，主键索引采用的就是聚集索引结构存储，按照主键顺序来构建B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值存储在一起，所以InnnoDB的主键索引就是是数据表本身，它按主键顺序存放了整个表的数据，占用空间就是整个表数据量大小。所以InnnoDB的表要求必须有聚集索引

  • 如果表定义了主键，主键为聚集索引

  • 若没有定义，第一个非空的唯一索引列为聚集索引

  • 若都没有，则InnoDB会自己建一个隐藏的主键列来作为聚集索引

在说说辅助索引，InnoDB辅助索引也是依据B+Tree结构来创建的，不过它的叶子节点中只存储了索引列和主键信息，所以我们查询一个非主键索引字段，会先检索辅助索引及其主键值，在通过主键值检索主键索引（聚集索引），获取对应行数据

结论：一个InnoDB表只能创建一个聚集索引，但可以创建多个辅助索引

非聚簇索引在MyISAM数据表中使用，由于不常用就没去具体研究了；

索引分析与优化

接下来，深入了解一下平时用的优化方式的具体介绍

1、explain

explain是我们优化sql语句中常用的命令，它可以输出select执行的详细信息

 
 
 
 
 
 
 
EXPLAIN SELECT * FROM user_info WHERE user_no='cj-test-yisheng'
 

  • select_type:查询类型

    • SIMPLE ： 表示查询语句不包含子查询或union

    • PRIMARY：表示此查询是最外层的查询

    • UNION：表示此查询是UNION的第二个或后续的查询

    • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果

    • UNION RESULT：UNION的结果

    • SUBQUERY：SELECT子查询语句

    • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

  • type:表示存储引擎查询数据时采用的方式，通过它可以判断查询是全表扫描还是基于索引的部分扫描

    • ALL：表示全表扫描，性能最差

    • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据

    • range：表示使用索引范围查询。使用>、>=、<、<=、in等等

    • ref：表示使用非唯一索引进行单值查询

    • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果

    • const：表示使用主键或唯一索引做等值查询，常量查询。

    • NULL：表示不用访问表，速度最快

  • possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称

  • key:表示查询时真正使用到的索引，显示的是索引名称

  • rows:MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录

  • key_len:表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引

  • Extra:表示很多额外的信息，各种操作会在Extra提示相关信息

    • Using where:表示查询需要通过索引回表查询数据。

    • Using index:表示查询需要通过索引，索引就可以满足所需数据。

    • Using filesort:表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化

    • Using temprorary:查询使用到了临时表，一般出现于去重、分组等操作

2、复合索引

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效

3、 LIKE查询

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引

4、NULL查询

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他 值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时 不会包括NULL行等，NULL比空字符串需要更多的存储空间等

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以 设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。

5、索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高

查询优化

1、慢查询定位

在mysql使用中，一般我们会开启慢查询日志，如名，就是将执行慢于设定时间的sql语句记录下来

  • 开启命令及查询慢日志地址

     
     
     
     
     
     
     
    SET global slow_query_log = ON;
    SET global slow_query_log_file = 'OAK-slow.log';
    #log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是#slow_query_log的值为ON，否则不会奏效。
    SET global log_queries_not_using_indexes = ON;
    SET long_query_time = 10;
    ​
    SHOW VARIABLES LIKE 'slow_query_log%'
     

  • 查看慢查询日志

    • 文本方式查看

      • time：日志记录的时间

      • User@Host：执行的用户及主机

      • Query_time：执行的时间

      • Lock_time：锁表时间

      • Rows_sent：发送给请求方的记录数，结果数量

      • Rows_examined：语句扫描的记录条数

      • SET timestamp：语句执行的时间点

      • select....：执行的具体的SQL语句

    • 使用mysqldumpslow查看

      • 可在MySQL bin目录下执行下面命令可以查看该使用格式

         
         
         
         
         
         
         
        perl mysqldumpslow.pl --help
         

      • 运行如下命令查看慢查询日志

         
         
         
         
         
         
         
        perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log
         

    • 通过第三方将sql语句打印到其他产品日志中

2、慢查询优化

  • 索引和慢查询

    • 查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定 的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系

    • 我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果 扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过 滤性好，执行速度才会快

  • 提高索引过滤性

    表：user

    字段：id,name,sex,age

    SQL案例：select * from user where age=18 and name like '张%';（全表扫 描）

  • 优化方案

     
     
     
     
     
     
     
    alter table `user` add index(name); //追加name索引
     
     
     
     
     
     
     
     
    alter table `user` add index(age,name); //追加age,name索引
     

    以上俩个是咋们都常用的方式，还有一种复杂点的

    可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名 字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。

     
     
     
     
     
     
     
    //为user表添加first_name虚拟列，以及联合索引(first_name,age)
    alter table `user` add first_name varchar(2) generated always as
    (left(name, 1)), add index(first_name, age);
    explain select * from student where first_name='张' and age=18;
    ​
     

  • 慢查询原因总结

    • 全表扫描：explain分析type属性al

    • 全索引扫描：explain分析type属性index

    • 索引过滤性不好：靠索引字段选型、数据量和状态、表设计

    • 频繁的回表查询开销：尽量少用select *，使用覆盖索引

3、分页查询优化

  • 问题：偏移量固定时，返回记录增多

    • 在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录 量越大，所花费的时间也会越来越多

  • 问题：返回记录增多，偏移量变大

    • 在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间 急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而 且查询的数据越多，也会拖慢总查询速度。）

  • 优化

    • 1、先利用覆盖索引

       
       
       
       
       
       
       
      select id from `user` limit 10000,100;
       

    • 2、再利用子查询优化

       
       
       
       
       
       
       
      select * from `user` where id>= (select id from user limit 10000,1) limit 100;
       

      原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化

存储引擎

如图，目前主流市场都采用的是5.5以后的版本，默认使用的都是Innodb存储引擎

  • InnoDB：支持事务，具有提交，回滚和崩溃恢复能力，事务安全

  • MyISAM：不支持事务和外键，访问速度快

1、 InnoDB和MyISAM对比

InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，也是面试常客

  • 事务和外键

    InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作

    MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

  • 锁机制

    InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。

    MyISAM支持表级锁，锁定整张表。

  • 索引结构

    InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。 MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。

  • 并发处理能力

    MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。

    InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发

  • 存储文件

    InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB； MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从 MySQL5.0开始默认限制是256TB。

2、InnoDB存储结构

从MySQL 5.5版本开始默认使用InnoDB作为引擎，它擅长处理事务，具有自动崩溃恢复的特性，在日 常开发中使用非常广泛。下面是官方的InnoDB引擎架构图，主要分为内存结构和磁盘结构两大部分

 

 

1、InnoDB内存结构

内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件

  • Buffer Pool：缓冲池。缓冲池以页为单位，默认16K，底层采用链表数据结构管理页，在InnoDB访问表记录和索引时会在页中缓存，以后使用减少磁盘IO操作，提升效率

    • 页码管理机制

      • free page：空闲页，未被使用

      • clean page：被使用页，数据没被修改过

      • dirty page：脏页，被使用过，数据也被修改过，页中数据与磁盘数据不一致

    • 三种也的链表结构

      • free list：管理空闲页

      • flush list，表示需要刷新磁盘到缓冲区，管理脏页，内部页按修改时间拍下，脏页即存在与flush链表中，也在LRU链表，但俩种胡不影响，LRU链表负责管理页的可用性和释放，而flush链表管理脏页的刷盘操作

      • LRU list：表示正在使用的缓冲区，管理被使用页和脏页，缓存区以midpoint为基点，前面链表为new列表区，存储经常访问数据，占63%，后面为old列表，占37%

    • 改进型LRU算法维护

      • 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间从末尾淘汰

      • 改进性LRU：链表分new和old俩部分，加入时从中间的midpoint位置插入，若数据被访问，则页向头部移动，如果没被访问，则向old尾部移动，等待淘汰

    • Buffer Pool配置参数

       
       
       
       
       
       
       
      show variables like '%innodb_page_size%'; //查看page页大小
      show variables like '%innodb_old%'; //查看lru list中old列表参数
      show variables like '%innodb_buffer%'; //查看buffer pool参数
      #建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，
      #innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。
       

  • Change Buffer:写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据， 并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数 据合并恢复到BP中

    • ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来 进行调整。参数innodb_change_buffer_max_size

    • 当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如 果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不 用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从 ChangeBuffer中读取信息合并，最终载入BufferPool中

    • 如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘， 做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在 ChangeBuffer操作。

  • Adaptive Hash Index:：自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监 控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以 称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

  • Log Buffer:日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲 区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O

    • LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志

    • LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少 磁盘IO频率

    • innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1

      • 0 ： 每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache，刷盘OS cache-->磁盘文件），最多丢失1秒数据

      • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作

      • 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

2、InnoDB磁盘结构

InnoDB磁盘主要包含表空间，数据字典，双写缓冲区、重做日志 和撤销日志

  • 表空间：用于存储表结构和数据。表空间又分为系统表空间、独立表空间、 通用表空间、临时表空间、Undo表空间等多种类型

    • 系统表空间（The System Tablespace）:系统表空间是存放change buffer的区域,Change Buffer是缓存那些不在buffer pool里的辅助索引的变化的特殊数据结构,在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分，当数据库宕机时，索引的变更会被缓冲到磁盘的Change Buffer区域

      • 如果表是在系统表空间中创建的，而不是单表单文件表空间或常规表空间，则系统表空间中还会保存表和索引的数据。

      • 在早期的MySQL版本中，系统表空间包含InnoDB数据字典。 在MySQL 8.0中，InnoDB将元数据存储在MySQL数据字典中

      • 系统表空间可以有一个或多个数据文件，默认情况下，有一个单独的数据文件被创建在数据目录下，名称为：iddata1

      • MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), SDI是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中

    • 独立表空间:默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于 系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表空间中。否则， innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件 默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩 （commpressed）行格式

    • 通用表空间:是使用CREATE TABLESPACE语法创建的共享InnoDB表空间，和系统表空间类似，也是共享的表空间，一个文件能够存储多个表数据

    • Undo表空间：回滚表空间，用来保存回滚日志，即undo logs

      • 回滚表空间（undo tablespaces）的默认路径是mysql的数据存储路径，会在undo tablespaces下生成undo_001和undo002共2个文件

      • 可通过配置可以通过 innodb_undo_directory属性主动设定回滚表空间的位置

      • 在MySQL8.0.14后，可以通过 CREATE UNDO TABLESPACE主动创建回滚表空间

      • 回滚表空间的文件必须以".ibu"作为扩展后缀名

    • 临时表空间（Temporary Tablespaces）：InnoDB使用会话临时表空间和全局临时表空间

  • 数据字典

    • InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数 据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上 与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

  • 双写缓冲区（Doublewrite Buffer）

    • doublewrite缓冲区是一个存储区域，InnoDB在将页面写入InnoDB数据文件中的适当位置之前，会在其中写入从缓冲池中刷新的页面。如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的良好副本

    • 默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将 innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flush_method设 置为O_DIRECT

      MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、 刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示 数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件。 默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文 件与redo log的缓存信息。

  • 重做日志（Redo Log）

    • 重做日志是基于磁盘的数据结构，主要作用是在崩溃恢复期间用于纠正不完整事务写入的数据。 在正常操作期间，重做日志对更改表数据的请求进行编码记录，这些请求是由SQL语句或低级API调用引起的。 在初始化期间以及接受连接之前，会自动重播未完成意外关闭之前未完成更新数据文件的修改（读写事务在执行过程中，都会不断产生redo log。默认名：ib_logfile0）

    • Redo Log 的生成和释放:随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生 Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入 到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）

    • Redo Log工作原理:Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表 的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘 数据进行持久化这一特性。

    • Redo Log写入机制:Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写

    • Redo Log相关配置参数:

       
       
       
       
       
       
       
      show variables like '%innodb_log%';
       

    • Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置(参考上面的参数设置)

  • Binlog日志

    • Binlog记录模式：Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制 日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的 消耗时间。开启Binlog日志有以下两个最重要的使用场景

      • 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到 Binlog后实现数据恢复达到主从数据一致性。

      • 数据恢复：通过mysqlbinlog工具来恢复数据

    • Binlog文件名默认为“主机名_binlog-序列号”格式，也可以在配置文件中指定名称，文件记录模式有STATEMENT、ROW和MIXED三种

      • ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave端对相同的数据进行修改

        • 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

        • 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨

      • STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到 master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的 SQL再次执行。简称SQL语句复制

        • 优点：日志量小，减少磁盘IO，提升存储和恢复速度

        • 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数

      • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存 binlog，MySQL会根据执行的SQL语句选择写入模式

    • Binlog文件结构

      MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合

      log event:

      timestampEvent Typeserver_idEvent sizeNext_log posFlagFixed partVarable part
      4字节 1 1 4 4 2    
      事件开始执行时间 事件类型 服务器ID 事件长度 固定4字节下一个event开始位置 固定2字节event flags 每种Event Type对应结构体固定的结构部分 每种Event Type对应结构体可变的结构部分

    • Binlog写入机制

      • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）

      • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区

      • 事务在提交阶段会将产生的log event写入到外部binlog文件中

    • Binlog相关命令

       
       
       
       
       
       
       
      show variables like 'log_bin';//状态查看
      set global log_bin=mysqllogbin;//开启Binlog功能
      show binary logs; //等价于show master logs;
      ​
      show master status;
      show binlog events;
      show binlog events in 'mysqlbinlog.000001';
      ​
      mysqlbinlog "文件名"
      ​
      //按指定时间恢复
      mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
      //按事件位置号恢复
      mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002
      | mysql -uroot -p1234
      ​
      ##可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉
      purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
      purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
      reset master; //清除所有文件
       

      需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启 MySQL服务

       
       
       
       
       
       
       
      #log-bin=ON
      #log-bin-basename=mysqlbinlog
      binlog-format=ROW
      log-bin=mysqlbinlog
       

    • Redo Log和Binlog区别

      • Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制 文件记录

      • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程

      • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不 会覆盖使用

      • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢 复使用。Binlog没有自动crash-safe能力

  • 回滚日志（Undo Logs）

    • 回滚日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。回滚日志 属于逻辑日志，根据每行记录进行记录。回滚日志存在于系统表空间、撤消表空间和临时表 空间中。回滚日志名：undo_001

    • 一个事务最多可以分配四个撤消日志，以下每种操作类型都可以分配一个

      • 对用户自定义表执行插入操作

      • 对用户自定义表执行删除和更新操作

      • 对用户自定义的临时表执行插入操作

      • 对用户自定义的临时表执行删除和更新操作

    • undo log的产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进 行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记 录一个insert；执行一个update，undolog会记录一个相反的update

    • Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存 储。

    • 命令：

       
       
       
       
       
       
       
      show variables like '%innodb_undo%';
       

    • Undo Log作用

      • 实现事务的原子性

        Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态

      • 实现多版本并发控制（MVCC）

        Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快 照读

         

         

        事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。

        事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

         

  • 以下为大概表文件

     

     

3、 InnoDB数据文件

 

 

  • InnoDB数据文件存储结构：从图中可得出最大的是一个Ibd文件，里面包含segment(段)，段里面包含Extent（区），区里面包含page（页），页里面包含row（行）

    • Tablesapce：表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段

    • Segment:段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数 据，一个管理索引。每多创建一个索引，会多两个segment。

    • Extent：区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会 一页一页分，直接分配一个区

    • Page：页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系 统页，事务数据页，大的BLOB对象页

    • Row：行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息

  • 文件存储格式：

     

     

  • Row行格式（Row_format）

    • Row是存储的基本单位，表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在 单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更 新时所需的I/O更少

    • InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED

      • REDUNDANT 行格式：使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余 的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便 能够在页外存储

      • COMPACT 行格式：与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了 某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式 可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些

      • DYNAMIC 行格式：使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只 包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。 DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数 控制。

      • COMPRESSED 行格式：COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引 数据压缩的支持。

    • 在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件 格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令

       
       
       
       
       
       
       
      ALTER TABLE 表名 ROW_FORMAT=格式类型;
       

       

4、MySql不同版本对比

  • MySQL 5.7 版本

    • 将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用 户自行指定文件大小和数量

    • 增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。

    • Buffer Pool 大小可以动态修改，无需重启数据库实例

  • MySQL 8.0 版本

    • 将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要 ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了

    • temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建 索引，这样加快了处理的速度

    • 用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个 表空间可以给多个表使用，但一个表只能存储在一个表空间中

    • 将Doublewrite Buffer从共享表空间ibdata中也分离出来了

posted @   小学程序员  阅读(125)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
相关博文：
· MySql体系
· MySql事务篇
· Mysql都有那些最需要掌握的原理？
· Mysql面试汇总
· MySQL 系统文件你了解吗
阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
点击右上角即可分享
微信分享提示