库

库的概念

• Database（库）或者Schema是一系列相关的数据库对象的集合
• 《MySQL Glossary》中的定义
  • For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle Database background will find that the MySQL meaning of a database is closer to what Oracle Database calls a schema.
  • Conceptually, a schema is a set of interrelated database objects, such as table, table columns, data types of the columns, indexes, foreign keys, and so on.
  • In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.
  • Database（库）或者Schema是一系列相关的数据库对象的集合
  • 

表

数据库表定义及构成

数据库表定义

• 在关系数据库中，数据库表，又称表格，是一系列二维数组的集合，用来表达和存储对象之间的关系。表是构成表空间的基本结构，由区间构成。它由纵向的列的横向的行组成，例如一个有关作者信息的名为authors的表格中，每个列包含的是所有作者的某个特定类型的信息，比如姓氏，而每行则包含了某个特定作者的所有信息：姓、名、住址等。

create table authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;

数据库表的基本构成

• 字段：first_name，last_name，gender，age，address，phone_number
• 数据类型：varchar(20)，varchar(20)，enum，tinyint，varchar(512)，varchar(20)
• 类型描述：character set utf8mb64，character set utf8mb64，default 0
• 表属性：engine = innodb，row_format = dynamic，character set = utf8mb64;

字段

字段 - 数据类型

• MySQL支持五大数据类型：
  • 数值型，如BIGINT，FLOAT和DECIMAL等
  • 日期和时间类型，如DATE，TIME和TIMESTAMP等
  • 字符串类型，如VARCHAR，CHAR和BLOB等
  • 空间数据类型，如GEOMETRY，POINT和POLYGON等
  • JSON数据类型
• 数据类型的选择取决于字段存储的具体数据：
  • 确保正确存储数据
  • 空间利用最优
• 同一个数据可能有多种存储方式，选择最合适的方式：
  • 如phone_number，为什么选择VARCHAR(20)？
  • 因为电话号码最长不超过20位，要做到空间利用最优。

字段 - 类型选择（1）

• 以authors表为例，为什么gender是ENUM而不是VARCHAR？
  • 因为，ENUM类型只占用1个存储空间，'Male’用1表示，‘Female’用2表示，而VARCHAR需要最多占用5个存储空间存放’Female’。
• 以authors表为例，为什么first_name/last_name/address是VARCHAR而不是CHAR？假设有个作者叫做John，两种类型存储上有什么不同？
  • 因为每个人名字和地址长短不一，VARCHAR可以存储可变长的字符串；CHAR可以存储定长字符串，意味着长度只能取姓名或地址信息最长的人，浪费了存储空间。
  • VARCHAR(20)存储John占用4个存储空间；CHAR(20)存储John占用20个存储空间。
• 以authors表为例，为什么address是VARCHAR(512)不是VARCHAR(20)？
  • VARCHAR(20)只能存储20个字符的地址。
  • VARCHAR(512)才能保证地址长度比较长的时候，不会报数据太长的错误。
  • 思考：512个字符是多少个字节？
    • ASCII码中，一个英文字母（不分大小写）为一个字节，一个中文汉字为两个字节。
    • UTF-8编码中，一个英文字母（不分大小写）为一个字节，一个中文为三个字节。
    • Unicode编码中，一个英文为一个字节，一个中文为两个字节。
    • 符号：英文标点为一个字节，中文标点为两个字节。
    • UTF-16编码中，一个英文字母或一个汉字都需要2个字节（Unicode 扩展区的一些汉字存储需要 4 个字节）。
    • UTF-32 编码中，世界上任何字符的存储都需要 4 个字节。

字段 - 类型选择（2）

• 以authors表为例，Age字段的DEFAULT 0是不是必须的？
  • DEFAULT可以简化SQL语句。
  • 不显示指定DEFAULT，也会有隐式DEFAULT值。
  • Age显示指定的DEFAULT 0和隐式DEFAULT值相等。
• 以authors表为例，为什么age是TINYINT UNSIGNED？
  • 纯数字用数值类型存储。
  • TINYINT UNSIGNED表示范围是0-255，适合表示年龄。
  • TINYINT，存储字节数1
  • SMALLINT，存储字节数2
  • MEDIUMINT，存储字节数3
  • INT，存储字节数4
  • BIGINT，存储字节数8
• 以authors表为例，为什么phone_number是VARCHAR不是BIGINT？
  • 假设有个电话需要存储+86-1234567890？
    • BIGINT存不了-
  • 假设有个电话需要存储01112345678？
    • 使用BIGINT的话前面的0就不会被记录在数据库中

字段 - 限制与约束

• 字段数限制
  • MySQL允许每张表支持最多4096个字段
  • InnoDB对每张表最多字段限制是1017个字段
  • 不建议一个表包含过多的字段，影响整体的使用体验
• 行长度限制
  • MySQL允许每条记录最长达到65535字节-不考虑大对象字段
  • InnoDB限制每条记录的长度不超过8K（基于华为云RDS配置）
  • 对于超过长度的场景，也可以处理，但是影响性能
  • 按需定义字段类型，按需存储，缩减行数据占用空间

分区表

分区表 - 大表拆小表

• 支持多种分区方式
  • RANGE/LIST/COLUMNS/KEY/HASH
  • 支持子分区
• 支持分区操作
  • 添加/删除/重整/合并/交换等操作
  • 分区表和普通表可以相互转换
  • 支持对指定分区进行查询
• 不是银弹
  • 性能考虑
  • 功能考虑

create table authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64
    partition by range (age)(
        partition p0 values less than (40),
        partition p1 values less than (50),
        partition p2 values less than maxvalue
        );

索引

索引是什么

• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data
  without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access
  of ordered
• 简而言之，匹配的查询可以通过索引快速定位到指定的数据。
• MySQL索引种类
  • Btree索引 -> 主键索引 -> PRIMARY KEY(first_name,last_name)
  • 聚簇索引 -> PRIMARY KEY(first_name,last_name)
  • 全文检索索引 -> 二级索引 -> UNIQUE KEY(phone_number)，KEY(address)
  • Rtree空间索引 -> 二级索引 -> UNIQUE KEY(phone_number)，KEY(address)
  • 唯一索引 -> UNIQUE KEY(phone_number)

Btree索引

Btree索引 - 定义

• 可以看作是一棵二叉树

create table authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20),
    primary key (first_name, last_name),
    unique key (phone_number),
    key (address)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;

Btree索引 - 快速定位

聚簇索引

聚簇索引定义

• 摘自MySQL文档的<Clustered and Secondary Indexes>
  • Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.
• 聚簇索引就是主键索引（Primary Key）
• 聚簇索引/主键索引只能一张表一个
• 即使表没有定义主键索引，聚簇索引也存在
• 聚簇索引一定是Btree索引
• 聚簇索引直接影响了数据的排序
• 索引的键值必须是唯一的

聚簇索引生成

• 原始表

• 

• 根据原始表的(first_name,last_name)排序

• 

• 建立索引

• 

二级索引

• 同一张表上非主键（聚簇）索引即为二级索引
• 二级索引可以有多个
• 按照索引类型区分：
  • Btree二级索引
  • 全文检索索引
  • 空间索引
  • 。。。
• 按照索引特性区分
  • 唯一索引
  • 非唯一索引

二级索引 - 结构

• 二级索引的键值一般都比较小
• 二级索引占用的总空间一般也比较少
• 二级索引的数据格式为：

二级索引 - 与聚簇索引的关联

唯一索引

• 聚簇索引必须是唯一索引
• 二级索引可以定义为唯一索引
• 唯一性约束保证每个索引键值都不会重复
• 定义前要确认数据是否满足唯一性
• 唯一性约束在特殊场景下可能被打破（华为云RDS for MySQL能最大程度避免这个问题）

最佳实践

表/聚簇索引

• 单表大小需要有效控制

  • 太大的表不利于性能
  • 考虑使用分区表（水平切分）或者拆表（垂直拆分）
  • 分区表的一个好处是，老的数据不用了，可以整个分区删除

• 聚簇索引必须按照PK排序

  • PK键值尽可能小，减少索引整体大小，必须为NOT NULL字段
  • PK覆盖的字段，最好保证数据能有序插入
  • 如果表中每个字段都无法保证唯一、无法保证NOT NULL，或者不适合做索引，推荐自定义一个ID自增列作为主键，自动满足有序插入

• 定义PK的重要性-不定义会怎样？

  • InnoDB会使用隐藏的ROW_ID作为主键
  • 表的一些访问会很低效
  • 影响Binlog复制的应用的效率
  • 强烈建议一定要自定义主键

表/聚簇索引 - 自增字段（AUTO_INCREMENT ID）

create table authors
(
    ID           BIGINT AUTO_INCREMENT NOT NULL,
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20),
    primary key (first_name, last_name),
    unique key (phone_number),
    key (address)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;

• 按照插入顺序存储，性能更好

索引的两面性

• 索引能加快查询，是不是越多越全越好？

  • 并不是，取决于业务侧的重点和需求
  • 正确的索引才能加速查询

• 索引是有代价的

  • 额外的二级索引占用额外的存储空间
  • 多个二级索引混合存储导致IO不连续
  • 二级索引有维护的代价
    • 增删改都需要同步每个受影响的二级索引
    • 最差的是对PK的更新，需要同步每个二级索引
    • 更新first_name，所有索引都要更新
    • 更新age，所有二级索引都不涉及
    • 更新phone_number，address的索引不涉及

二级索引

二级索引 - 建立哪些索引

• 有了主键（first_name,last_name），可以指定last_name为条件进行主键索引查询吗？

  • 不行，只能进行以first_name为首的查询

• gender字段适合建立Btree索引吗？

  • 不适合，该字段区分度很小，适合位图索引

• 有个查询需要返回first_name，last_name，address，phone_number，需要对这四个字段建立索引吗？该怎么建立索引，字段应该如何排序？

  • 根据实际需要权衡，但是一般不建议建立太多字段的组合索引。组合索引应该首选最频繁查询的字段排前面，同等情况下选择区分度高的字段排前面

二级索引 - 和聚簇索引的查询差异

• 二级索引覆盖扫描效率最高，如根据phone_number为条件查询某个号码存在
• 二级索引覆盖扫描支持包含主键，如上一个查询要返回first_name也是覆盖扫描
• 二级索引扫描有时候需要回表，获取索引上不存在的字段信息，导致两次索引扫描
• 聚簇索引扫描代价比较高，有更多的数据要读取，但是一次索引扫描即可完成

建库、表和索引语句

show databases;
create database library;
show databases;
use library;
show tables;
create table authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;
show tables;
show create table authors;