mysql

字符集和比较规则

1. 字符集指的是某个字符范围的编码规则。

2. 比较规则是针对某个字符集中的字符比较大小的一种规则。

3. 在MySQL中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则，一个比较规则必须对应一个字符集。

4. 查看MySQL中查看支持的字符集和比较规则的语句如下：

   SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
   SHOW COLLATION [LIKE 匹配的模式];

5. MySQL有四个级别的字符集和比较规则

• 服务器级别

  character_set_server表示服务器级别的字符集，collation_server表示服务器级别的比较规则。

• 数据库级别

  创建和修改数据库时可以指定字符集和比较规则：

  CREATE DATABASE 数据库名
      [[DEFAULT] CHARACTER SET 字符集名称]
      [[DEFAULT] COLLATE 比较规则名称];
  ​
  ALTER DATABASE 数据库名
      [[DEFAULT] CHARACTER SET 字符集名称]
      [[DEFAULT] COLLATE 比较规则名称];

  character_set_database表示当前数据库的字符集，collation_database表示当前默认数据库的比较规则，这两个系统变量是只读的，不能修改。如果没有指定当前默认数据库，则变量与相应的服务器级系统变量具有相同的值。

• 表级别

  创建和修改表的时候指定表的字符集和比较规则：

  CREATE TABLE 表名 (列的信息)
      [[DEFAULT] CHARACTER SET 字符集名称]
      [COLLATE 比较规则名称]];
  ​
  ALTER TABLE 表名
      [[DEFAULT] CHARACTER SET 字符集名称]
      [COLLATE 比较规则名称];

• 列级别

  创建和修改列定义的时候可以指定该列的字符集和比较规则：

  CREATE TABLE 表名(
      列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
      其他列...
  );
  ​
  ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

1. 从发送请求到接收结果过程中发生的字符集转换：

   • 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。

   • 服务器将客户端发送来的字节串采用character_set_client代表的字符集进行解码，将解码后的字符串再按照character_set_connection代表的字符集进行编码。

   • 如果character_set_connection代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使用的字符集之后再进行操作。

   • 将从某个列获取到的字节串从该列使用的字符集转换为character_set_results代表的字符集后发送到客户端。

   • 客户端使用操作系统的字符集解析收到的结果集字节串。

   在这个过程中各个系统变量的含义如下：

   系统变量	描述
   character_set_client	服务器解码请求时使用的字符集
   character_set_connection	服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
   character_set_results	服务器向客户端返回数据时使用的字符集

   一般情况下要使用保持这三个变量的值和客户端使用的字符集相同。

2. 比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中。

InnoDB记录存储结构

1. 页是MySQL中磁盘和内存交互的基本单位，也是MySQL是管理存储空间的基本单位。

2. 指定和修改行格式的语法如下：

   CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
   ​
   ALTER TABLE 表名 ROW_FORMAT=行格式名称

3. InnoDB目前定义了4种行格式

   • COMPACT行格式

     具体组成如图：

     变长字段长度列表：所有变长字段的真实数据占用的字节长度逆序排列

     NULL值列表：所有可能存储NULL值的列，通过一个二进制位表示并逆序排列

     记录头信息：

     

     这些二进制位代表的详细信息如下表：

     名称	大小（单位：bit）	描述
     预留位1	1	没有使用
     预留位2	1	没有使用
     delete_mask	1	标记该记录是否被删除
     min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
     n_owned	4	表示当前记录拥有的记录数
     heap_no	13	表示当前记录在记录堆的位置信息
     record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
     next_record	16	表示下一条记录的相对位置

     记录的真实数据：

     除了用户自定义列的数据值之外，为每个数据默认的添加一些隐藏列

     列名	是否必须	占用空间	描述
     row_id	否	6字节	行ID，唯一标识一条记录。在用户没有自定义主键以及Unique键的情况下才会添加该列
     transaction_id	是	6字节	事务ID
     roll_pointer	是	7字节	回滚指针

   • Redundant行格式

     具体组成如图：

   • Dynamic和Compressed行格式

     这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

     另外，Compressed行格式会采用压缩算法对页面进行压缩。

4. 一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出。

InnoDB数据页结构

1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页。

2. 一个数据页可以被大致划分为7个部分，分别是

   • File Header，表示页的一些通用信息，占固定的38字节。

   • Page Header，表示数据页专有的一些信息，占固定的56个字节。

   • Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。

   • User Records：真实存储我们插入的记录的部分，大小不固定。

   • Free Space：页中尚未使用的部分，大小不确定。

   • Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。

   • File Trailer：用于检验页是否完整的部分，占用固定的8个字节。

   

   

3. 每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单链表。

   

4. InnoDB会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

   • 通过二分法确定该记录所在的槽。

   • 通过记录的next_record属性遍历该槽所在的组中的各个记录。

   

5. 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。

   

6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

B+树索引

1. 对页中的记录进行增删改操作的过程中，必须移动记录来保证“下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值”，这个操作过程称为页分裂

   

2. 页在物理存储上可能并不连续，如果要根据主键值快速定位某些记录所在的页，则需要给每个页增加目录项，信息包含：页内最小的主键值（用key标识）、页号（page_no标识）

   

   将目录项作为数据行（目录项记录）再以页的形式组织，可以复用数据页的数据结构，通过record_type来区分。

   

   存储目录项记录的页随着数据增加也会形成多个页，以双向链表的形式连接

   

   如果对多个目录项记录页进行快速定位，则需要在它们的基础之上，再构建形成更高级的目录

   

   这就是B+树！B+树本身就是一个目录，或者说本身就是一个索引。

3. 聚簇索引：

   • 使用记录主键值的大小进行记录和页的排序

   • B+树的叶子节点存储了所有的数据，即所有列的值（包括隐藏列）

4. 二级索引：

   • 使用非主键列值作为索引项进行记录和页的排序

   • B+树的叶子节点存储了该列值和对应的主键值

   • 如果想通过二级索引查找完整数据，则需要先通过二级索引先查到主键，再通过主键查询聚簇索引。这个过程称为回表

5. 联合索引：

   • 同时为多个列建立索引，排序通过从左到右依次排列（即先按照第一列从小到大排序，如果第一列值相同，则按照第二列从小到大排序....）

   • 叶子节点存储了所涉及的列的值，以及主键值

6. MyISAM的索引方案虽然也使用树形结构，但是却将索引文件和数据文件分开存储。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。

B+树索引的使用

1. B+树索引在空间和时间上都有代价，建立索引需要考虑慎重。

2. B+树索引适用于下边这些情况：

   • 全值匹配

   • 匹配左边的列

   • 匹配范围值

   • 精确匹配某一列并范围匹配另外一列

   • 用于排序

   • 用于分组

3. 在使用索引时需要注意下边这些事项：

   • 只为用于搜索、排序或分组的列创建索引

   • 为列的基数大的列创建索引

   • 索引列的类型尽量小

   • 可以只对字符串值的前缀建立索引

   • 只有索引列在比较表达式中单独出现才可以适用索引

   • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。

   • 定位并删除表中的重复和冗余索引

   • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。