数据库学习笔记(1)

存储引擎

查看支持的引擎

show variables like 'table_type';
# 或者
SHOW VARIABLES LIKE 'have%';

启用

创建新表的时候，可以通过增加 ENGINE 关键字设置新建表的存储引擎，例如，在下面的例子中，表 ai 的存储引擎是 MyISAM，而 country 表的存储引擎是 InnoDB：

CREATE TABLE ai (

i bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (i)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE TABLE country (

country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

country VARCHAR(50) NOT NULL,

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (country_id)

)ENGINE=InnoDB DEFAULT CHARSET=gbk;

也可以使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎。下面的例子介绍了如何将表 ai 从 MyISAM 存储引擎修改到 InnoDB 存储引擎：

mysql> alter table ai engine = innodb;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table ai \G

各引擎存储特性

NeatReader-1583135271871

MyISAM

其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

  • .frm（存储表定义）
  • .MYD（MYData，存储数据）
  • .MYI（MYIndex，存储索引）

支持 3 种不同的存储格式:

  • 静态（固定长度）表
  • 动态表
  • 压缩表

其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表的数据在存储时会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。

动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新和删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk-r 命令来改善性能，并且在出现故障时恢复相对比较困难。

压缩表由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

MyISAM 类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能被访问，会提示需要修复或者访问后返回错误的结果。MyISAM 类型的表提供修复的工具，可以用 CHECK TABLE 语句来检查 MyISAM 表的健康，并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。

InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是，处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

特性

  • 自动增长列(auto_increment)

    InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。

  • 外键约束

    MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

  • 存储方式

    • 使用共享表空间存储
    • 使用多表空间存储

Memory

MEMORY 存储引擎使用存在于内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常地快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是 16MB，可以根据需要加大。此外，在定义 MEMORY 表的时候，可以通过 MAX_ROWS 子句指定表的最大行数。

MEMORY 类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

Merge

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，MERGE 表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行的。

可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响。

类型与选择

类型适用环境
MyISAM默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一
InnoDB用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
MEMORY将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。这对于诸如数据仓储等 VLDB 环境十分适合。

除了这些之外，还有一些常见的第三方存储引擎，在某些特定应用中也有广泛使用，比如列式存储引擎 Infobright、高写性能高压缩的 TokuDB 就是其中非常有代表性的两种

选择数据类型

char 与 varchar

由于 CHAR 是固定长度的，所以它的处理速度比 VARCHAR 快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用 CHAR 类型来存储

在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同，这里简单概括如下。

存储引擎使用原则
MyISAM建议使用固定长度的数据列代替可变长度的数据列。
MEMORY目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
InnoDB建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

text 与 blob

主要差别是BLOB能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。

问题

  1. BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。

    删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

    OPTIMIZE TABLE t;

  2. 可以使用合成的（_Synthetic_）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。

    简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的）。

    select * from t where hash_value=md5(查询内容);

    如果需要对 BLOB 或者 CLOB 字段进行模糊查询，MySQL 提供了前缀索引，也就是只为字段的前 n 列创建索引。

    select * from t where context like 'beijing%' \G;
# 注意，这里的查询条件中，“%”不能放在最前面，否则索引将不会被使用

    注：/G 的作用是将查到的结构旋转 90 度变成纵向

  3. 在不必要的时候避免检索大型的 BLOB 或 TEXT 值。

    例如，SELECT *查询就不是很好的想法，除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。

  4. 把 BLOB 或 TEXT 列分离到单独的表中。

    在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT *查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。

浮点数与定点数

定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更精确地保存数据。

引申：浮点数使用原则

  1. 浮点数存在误差问题；
  2. 对货币等对精度敏感的数据，应该用定点数表示或存储；
  3. 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；如果非要使用浮点数的比较则最好使用范围比较而不要使用“==”比较。
  4. 要注意浮点数中一些特殊值的处理。

日期类型选择

MySQL 中的日期和时间类型:

NeatReader-1583144820301

MySQL 中日期和时间类型的零值表示

NeatReader-1583144823360

字符集

字符集的选择

  1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，目前就是 UTF-8。
  2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字，如果选择 GB 2312-80 为数据库字符集，就很可能出现某些文字无法正确导入的问题。
  3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。因为，相对于 UTF-8 而言，GBK 比较“小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节，这样可以减少磁盘 I/O、数据库 Cache 以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8 更好，因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节，会造成很多不必要的开销。
  4. 如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
  5. 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

MySQL 字符集的设置

MySQL 的字符集和校对规则有 4 个级别的默认设置：服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不相同。

服务器字符集和校对规则，可以在 MySQL 服务启动的时候确定。

可以在 my.cnf 中设置：

[mysqld]
character-set-server=gbk

或者在启动选项中指定：

mysqld --character-set-server=gbk

或者在编译时指定：

cmake . -DDEFAULT_CHARSET=gbk

数据库字符集和校对规则

数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容

设置数据库字符集的规则如下：

如果指定了字符集和校对规则，则使用指定的字符集和校对规则；

如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；

如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；

如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。

推荐在创建数据库时明确指定字符集和校对规则，避免受到默认值的影响。

字符集修改步骤

如果在应用开始阶段没有正确地设置字符集，在运行一段时间以后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。字符集的修改不能直接通过“alter database character set _”或者“alter table tablename character set _”命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。

以下模拟的是将 latin1 字符集的数据库修改成 GBK 字符集的数据库的过程。

（1）导出表结构：

mysqldump -uroot -p –default-character-set=gbk -d databasename> createtab.sql

其中–default-character-set=gbk 表示设置以什么字符集连接，-d 表示只导出表结构，不导出数据。

（2）手工修改 createtab.sql 中表结构定义中的字符集为新的字符集。

（3）确保记录不再更新，导出所有记录。

mysqldump -uroot -p –quick –no-create-info –extended-insert –default- character-set=latin1 databasename> data.sql

–quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中。

–extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入。

–no-create-info：不导出每个转储表的 CREATE TABLE 语句。

–default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。

（4）打开 data.sql，将 SET NAMES latin1 修改成 SET NAMES gbk。

（5）使用新的字符集创建新的数据库。

create database databasename default charset gbk;

（6）创建表，执行 createtab.sql。

mysql -uroot -p databasename < createtab.sql

（7）导入数据，执行 data.sql。

mysql -uroot -p databasename < data.sql

注意： 选择目标字符集的时候，要注意最好是源字符集的超集，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。例如，GBK 字符集的字库大于 GB 2312 字符集，那么 GBK 字符集的数据，如果导入 GB 2312 数据库中，就会丢失 GB 2312 中不支持的那部分汉字的数据。

索引

默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。

索引在创建表的时候可以同时创建，也可以随时增加新的索引。创建新索引的语法为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,. .)

index_col_name:

col_name [(length)] [ASC | DESC]

索引设计原则

  • 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

  • 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。

    例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

  • 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。

    较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。

    例如，有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。

  • 利用最左前缀。在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。

  • 不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。

    每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。