1.MySQL记录存储

  1. 页头

    • 记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针、页面使用情况等。

  2. 虚记录

    1. 最大虚记录：比页内最大主键还大
    2. 最小虚记录：比业内最小主键还小
  3. 记录堆
    1. 行记录存储区，分为有效记录和已删除记录两种
  4. 自由空间链表
    1. 已删除记录组成的链表
  5. 顺序保证
    1. 物理连续：通过移动数据来保证数据连续性
    2. 逻辑连续：通过指针来保证数据的连续性（设计数据库使用逻辑连续）
  6. 页内查找
    1. 遍历
    2. 二分查找法：通过slot实现。

2.索引实现原理

  • 聚簇索引
  • 二级索引
  • 联合索引

主键选择

  • 自增主键：写入查询和磁盘利用率都高，每次查询走两级索引；
  • 随机主键：写入查询和磁盘利用率都低，每次查询走 两级索引；
  • 业务主键：写入查询和磁盘利用率都高，可以使用一级索引；（建议使用）
  • 联合主键：影响索引大小，不易维护，不建议使用；

3.innodb常用参数配置

3.1启动innodb存储引擎

执行sql查看是否使用innodb存储引擎

  1. show variables like 'default_storage_engine';

image.png
在my.cnf加入：

  1. default_storage_engine=innodb

重启MySQL生效

3.2innodb初期重要参数设置

  1. -- 查询所有参数
  2. show variables like '%innodb%';
  1. innodb_buffer_pool_size(类似Oracle的sga） ```sql show variables like ‘innodb_buffer_pool_size’;

— 用于innodb数据和索引的缓存，默认128M，innodb最重要的性能参数。 — 建议值：不要超过物理内存的80%（如果数据量过小，可以是数据量+10%，数据量20G, — 物理内存32G，这时候可以设置buffer pool为22G）

— 设置：在my.cnf加入： innodb_buffer_pool_size=512M(20G) — 重启MySQL生效

  2. 2. innodb_log_buffer_size（日志缓存区）
  3. ```sql
  4. show variables like 'innodb_log_buffer_size';
  5. -- 使用默认16M够使用了
  1. show variables like ‘innodb_flush_log_at_trx_comit’（控制事务的提交方式，控制日志刷新到硬盘的提交方式） ```sql show variables like ‘innodb_flush_log_at_trx_comit’;

— 有3个值：0,1,2 默认是1 — 0：每秒1次写入log file中，同时会进行文件系统到磁盘的同步操作，每个事务的提交commit不会从 — log buffer 到 log file。速度快，不安全，出现故障会丢失1秒的事务，比如游戏数据库建议设置 — 为0 — 1：每个事务的提交会从log buffer到log file，同时触发文件系统的同步操作。 — 2：每个事务的提交会从log buffer到log file，不会触发文件系统到磁盘的同步。但是每秒会有一次文件系统 — 到磁盘的同步。

  2. 4. innodb_log_file_size(指定重做日志文件大小, 日志文件工鞥呢数据挂了之后的恢复操作）
  3. ```sql
  4. show variables like 'innodb_log_file_size';
  5. -- 5.5版本 > 4G  5.6版本>512G
  6. -- 小业务256M够了，中型业务一般保持在2G
  7. -- 设置方法操作参照上方
  1. nnodb_flush_method（控制innodb数据文件及redo log的打开/刷写的模式）
  2. 系统表空间与零时表空间路劲 ```sql show variables like ‘innodb%data%file%’;

— innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M;autoextend:MAX:5G — innodb_temp_data_file_path=ibtmp1:200M:autoextend:MAX20G

  2. 7. 重做日志与undd路径
  3. ```sql
  4. show variables like 'innodb_log_group_home_dir';
  5. show variables like 'innodb_undo_directory';

4.InnoDB buffer pool原理与配置

4.1buffer pool 原理

1、innodb_log_buffer_size
2、show variables like ‘innodb_buffer_poolinstances’;
innodb_buffer_poolinstances: win 1, lunix 8个，如果你的内存小于1G，默认就1个，将热点大散，提高并发的性能。
3、buffer pool以页page单位，大小同innodb_page_size一样。
show variables like ‘innodb_page_size’;
4、 buffer pool组成部分

  • free list: 启动时，有多个16K的空白页，这些页就存在free list中。
  • lur list ： 当读取一个数据页的时候，就从freelist中取出一个页，存放数据，并将这个也放入到lur list。
  • flush list： 当lur list中的页第一次被修改时，就将页的指针（page number） 放到flush list （只要被修改过，无论改了多少次）

flush list 中包含脏页，即数据改过未刷到磁盘的页。
5、lur list是如何管理的？

MID point 算法
这个页第一次读取的时候，改页先放到 MID point的位置；
当被读到第二次，才将这个页放到new page 的部首。
show variables like ‘innodb_old_blocks_pct’;

4.2buffer pool 启动和卸载

mysql 5.6< 每次启动buffer pool中页是空的，每次都需要大量的时间加载新的页到内存中，启动后有一段时间性能差
MySQL 5.6 > 每次停就会dump 出buffer pool的数据（space， page number）， 然后启动时load进buffer pool，预热

  1. show VARIABLES LIKE '%INNODB_buffer_pool%'
  3. innodb_buffer_pool_dump_at_shutdown
  4. innodb_buffer_pool_load_at_startup
  5. innodb_buffer_pool_dump_pct
  6. innodb_buffer_pool_dump_now

4.3buffer pool 的调整

MySQL 5.7 < 不能再先调整，需要重启才生效
MySQL 5.7 > 可以在线调整，需要改my.cnf后重启生效

什么时候调整：

  • 机器增加物理内存
  • 性能原因活历史原因，需要调整

调整innodb_buffer_pool大小的参数
show VARIABLES LIKE ‘innodb_buffer_pool_size’;
show VARIABLES LIKE ‘innodb_buffer_pool_instances’ — 不建议超过CPU的核

比如：设置96G
select 9610241024*1024 from dual;
innodb_buffer_pool_size=4831838208 这个值可以在线调整，也可以在my.cnf里面设置

调整案例：
— 我的环境是4G,我现在在线调整由512M到2M
第一步： 临时全局生效
select 210241024*1024 from dual;
set global innodb_buffer_pool_size=2147483648; 只能使用字节
show VARIABLES LIKE ‘innodb_buffer_pool_size’;

第二步： 永久生产
在my.cnf里面设置
innodb_buffer_pool_size=4831838208 可以使用M
重启

第三步：验证
show varidbles like ‘innodb_buffer_pool_size’;
show variables like ‘innodb_buffer_pool_instances’;

5.innoDB表空间管理