日常学习

Mysql

April 11, 2021
mysql

Mysql Relearn

内存模型：

memory-cache

自适应Hash 索引：

Log Buffer:

InnoDB 磁盘数据结构：

Table

创建表： create table,

   CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

Primary key

在其他目录中存储 数据：（即 外部创建表）

导入 InnoDB 表：

AUTO_INCREMENT 在InnoDB 中的处理方式

  1. consecutive mode: 在该种模式下，
    • “bulk inserts” 使用特殊的 AUTO-INC table level lock 并一直把持到 语句的最后。该类规则将 应用到 所有 的 insert ..select, replace .. select, load data 的语句中，同一时间 只有 一个把持 AUTO-INC 的语句 能够执行，
    • “simple inserts”: 因为可以预先知道插入的数量， 可以使用 特殊的 轻量级 X锁（只在auto-increment 分配阶段 保持），来避免 table-level AUTO-INC 锁。如果存在 其他的 transaction 持有 table-level 的 AUTO-INC lock 则 需要等待 该 transaction 完成

    • ”mixed-mode inserts“: 分配的数值增量 要大于 插入的行数， 自动分配的数值 也是连续的。

    • 简单的说， consecutive lock mode 下， 在 保证 基于语句的 replic 正确下，提高了 并发与可伸缩性， 完美兼容 tranditional mode

  2. interleaved mode: 不使用 table-level auto-inc lock， 而且可以同时执行多个insert 语句， 是最快的，可伸缩 的 lock 模式， 但是对于 基于语句 复制的 server来说 并不安全。 在这种锁定模式下，保证auto-increment column 是唯一的，并且在所有同时执行的“类似INSERT”语句中单调递增。 但是，由于多个语句可以同时生成数字（也就是说，在语句之间交错分配数字），因此为任何给定语句插入的行生成的值可能不是连续的。即： 在 simple insert 语句中， auto-increment 中的column是连续的， mixed-mode insert 与 bulk insert 中 可能存在 gap

  3. auto-increment gap: 自动增量间隙
    • 在所有的lock mode下： 如果事务发生回滚，则分配给该 transaction 的数值 就丢失了，该值 将不会被重用，因为表中的 auto-increment column 数值 可能存在 gap
    • 批量插入： 在 traditional 或者 consecutive 模式下 任何插入 都不会产生 gap，因为批量插入 都需要获得 table-level 的 auto-inr lock 并保持到 活动结束。 在 interleaved 下，”bulk-insert” 可能产生gap。在 consecutive 或者 interleaved 模式下， 在连续的语句之间可能会出现gap，因为对于批量插入，可能不知道每个语句所需的自动递增值的确切数量，并且可能会高估。( 这里面 是不是描述有问题？ 因为已经在前面说了， tranditional or consecutive 模式下 批量插入并不会产生gap， 这里又说会有， 为什么？ 文档在此)
    • mixed mode insert 在各种不同mode下产生的结果： 最近产生的sequence number is 100：
        mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;  

    mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

    # lock mode 为 traditional 时候， 执行插入操作
    mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
     +-----+------+
     | c1  | c2   |
     +-----+------+
     |   1 | a    |
     | 101 | b    |
     |   5 | c    |
     | 102 | d    |
     +-----+------+

    # lock mode 为 consecutive 时候， 执行插入操作   

        mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
    +-----+------+
    | c1  | c2   |
    +-----+------+
    |   1 | a    |
    | 101 | b    |
    |   5 | c    |
    | 102 | d    |
    +-----+------+


    # lock mode 为 interleaved 时候， 执行插入操作 

     mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
     +-----+------+
     | c1  | c2   |
     +-----+------+
     |   1 | a    |
     |   x | b    |
     |   5 | c    |
     |   y | d    |
     +-----+------+    

     x y 代表 唯一，但是并不能够确定的数值

index: 没个 InnoDB 表， 都有一个 特殊的index 称为 聚簇索引， 用来保存 row data， 通常 聚簇索引 与 primary key(主键) 同意， 为了 请求获得 更好的性能， 理解 InnoDB 如何使用 聚簇索引 来 进行优化 查询，和 DML 操作 是非常重要 的

The System Tablespace:

File-Per-Table Tablespace: 包含 table中的 data 和 index，存储在系统中 单个文件中

General Tablespace： 通用 Tablespace

  1. 创建
CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]  

# 可以 使用 变量 innodb_directories  控制 general tablespace 的存储目录， 
# ts1.idb 为相对 innodb_directories 目录
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

# 也可以执行全路径
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;
  1. 使用： 添加table 到 general tablespace:
    ```sql

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
mysql> ALTER TABLE t2 TABLESPACE ts1;

  
   * 使用alter table 可以将table 的tablespace 在各个 tablespace 中进行转换： general tablespace为具体的create tablespace 的名字， file-per-table名称为 innodb_file_per_table， system tablespace 为innodb_system； 如下： 
   
   ```sql
   
   
   ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;
   
   ALTER TABLE tbl_name TABLESPACE [=] innodb_system;
   
   ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

  1. 重命名tablespace： ALTER TABLESPACE s1 RENAME TO s2;， 重命名操作不能发生在 lock tables， flush tables 作用期间

  2. 删掉 tablespace： 在tablespace中的所有table 都必须删除掉，才能够成功的 drop tablespace

   
    #Use a query similar to the following to identify tables in a general tablespace. 
     mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,
          INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
   +------------+------------+
   | space_name | table_name |
   +------------+------------+
   | ts1        | test/t1    |
   | ts1        | test/t2    |
   | ts1        | test/t3    |
   +------------+------------+

   DROP TABLESPACE ts1;

文档在此