笔记：MySQL技术内幕-04

1 minute read

索引组织表

按主键

逻辑存储结构

=> 所有数据都被逻辑地放置在表空间内，又分别由以下组成 segment, extent, page, row

1.每张表的独立表空间存放：数据、索引、插入缓冲Bitmap页

共享表空间内存放：回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等

共享表空间还是会不断增大

2.数据段：B+树的叶子节点

索引段：B+树的非叶子节点

3.区大小 1MB，内部有64个页，每个页是16k

即使引入压缩页，但区的大小始终是1M，同时页的大小也是可以自定义的

mysql> show variables like 'innodb_page%'\g;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

实际情况是：每个段开始时，现有32个碎片页来存放数据，使用完后才会申请64个连续页

可以使用py_innodb_page_info.py工具查看idb文件的页分配；

4.行记录格式 Row_format:Compat

变长字段长度列表：各列的长度，每列用1-2字节表示

NULL标记位：用位来标记各列是否有NULL

记录头信息：5字节长度，记录了是否删除、下一记录指针等

列数据：

5.行溢出数据

所有VARCHAR列的长度总和不能超过65535字节的数据，否则要求使用text或blob

但超过时，部分数据放在B-tree Node的子节点上，另外的数据作为溢出数据放在blob页中

? 放多少数据在B+Node上，多少在Blob页上呢？

=>原则是，因为按索引组织即B+结构，需确保每个页16k上至少有2条记录，否则索引结构失去意义，即8098字节

对于TEXT和Blob的数据类型，是放在数据页还是Blob页，需看是否一个页能否存放2条记录

=>实际数据页只保存数据的前768字节

6.新的行记录格式

新增2种新的行记录格式：Compressed, Dynamic

5.6默认使用格式 Compact

5.7默认使用格式 Dynamic

=> 新格式对于存放在Blob中的数据采用了完全的行溢出方式，在数据页中只存放20个字节的指针，实际数据都放在Blob页中

Innodb数据页格式

1.数据页格式

1.File Header：头信息，如页类型，表空间中也的偏移，上一个/下一个页的指针

2.Page Header：状态信息，如记录数，插入记录的位置，在索引数中的level层

3.Infimum/Supremum：记录的主键值的上下边界

4.User Record：实际存储数据

5.Free Space：空间空间

6.Page Directory：记录的相对位置，将所有记录的主键按顺序存放，便于二叉查找记录

7.File Trailer：尾信息，用于保证页的完整性

要点：

1.B+树索引用来快速找到记录所在的页，不能找到具体的一条记录；
2.页载入内存后，通过Page Directory的有序主键做二叉查找，找到具体的记录；

2.解决页兼容问题 Named File Formats机制

//5.6中
mysql> show variables like '%file_format'\g;
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+

//5.7中
mysql root@[localhost:mysql3306.sock carddb_20001] > show variables like '%file_format%'\g;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+

约束和索引

1.索引是一个数据结构，既有逻辑上的概念，用于B+树代表物理存储方式

约束是逻辑的概念，用于保证数据的完整性

索引类型有：主键索引，唯一索引，全文索引

约束类型有：主键约束、唯一约束、外键约束、默认值

2.查找约束，表 information_schema.table_constraints

mysql root@[localhost:mysql3306.sock (none)] > select * from information_schema.table_constraints;
+--------------------+-------------------+------------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME  | TABLE_SCHEMA | TABLE_NAME                | CONSTRAINT_TYPE |
+--------------------+-------------------+------------------+--------------+---------------------------+-----------------+
| def                | carddb_10000      | PRIMARY          | carddb_10000 | t_account                 | PRIMARY KEY     |
| def                | carddb_11000      | Index_SRedeem_ID | carddb_11000 | t_sredeem                 | UNIQUE          |

3.对输入值做合法性约束检查 STRICT_TRANS_TABLES

mysql root@[localhost:mysql3306.sock (none)] > show variables like '%sql_mode%'\g;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

4.Enum约束，目前只能实现枚举离散约束，不能实现完整的check约束

sex ENUM('male', 'femaile')

5.创建触发器trigger

create trigger 触发器名 BEFORE/AFTER UPDATE/DELETE/INSERT on 表名 for each row
begin
if new.cache > old.cache then
xxxx
endif;
end;
$$

6.创建外键，即时检查

foreign key (列名) references 表名(列名) 
on delete 选项
on update 选项

可供选择的选项有：

set null 字表数据设置为null

no action 抛出错误，不允许

restrict 抛出错误，不允许（默认）

cascade 更新，也执行update/delete操作

show variables like 'foreign_key_checks'\g;
|foreign_key_checks | ON    |

6.实现完整性约束的手段：

主键/唯一键：
Enum约束：离散数值
创建触发器：保证操作完整性
外键约束：保证参照完整性

视图

1.命名的虚表，没有实际的物理存储

视图也可用于隔离基本表，起到一个安全层的作用

2.也可以对视图执行更新操作，应用到基本表上

3.MYSQL不支持物化视图，只能通过触发器实现类似视图功能；

分区表

1.MYSQL支持水平分区，不支持垂直分区

同时，只能是局部分区索引（一个分区中既有数据也有索引），还不支持全局分区

2.分区主要用于数据库高可用性的管理

3.支持以下几种类型的分区：

RANGE分区：按列值所属连续区间来分区
LIST分区：离散的值来分区
HASH分区：自定义的表达式的返回值来分区  => 均匀分布到各分区中
KEY分区：数据库提供的函数来分区
COLUMNS分区：可使用非整数来分区

3.使用 EXPLAIN PARTITIONS命令查看

mysql root@[localhost:mysql3306.sock carddb_11000] > explain partitions select * from t_account limit 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_account
   partitions: NULL

=>分区修剪可提升查询效率

4.分区和性能

OLAP应用中，分区可以很好地提升查询的性能

OLTP应用中，走主键分区的索引并不会带来性能提升，反而会引起更大开销

非主键查询，数据库会搜索所有的分区，导致更多的IO，影响查询的性能