MySQL-00基础概念

分类于
字数统计: 2,425

基础概念

参考:《深入浅出MySQL》

SCHEMA

schema 为数据库对象的集合， schema里面包含了各种对象如tables, views, sequences, 可以视作”数据库”, show database可以看到schema.
不同数据库产品的schema概念是不同的:

  • MySQL: Schema 等同于 Datebase
  • SQL Server: schema中包含了数据库的表，字段，数据类型以及主键和外键的名称。

数据类型

  • 数字:
    • 整数:
      • TINYINT 1, SMALLINT 2, INT 4, BIGINT 8
    • 小数:
      • 浮点数: float, double
      • 定点数: decimal, 常用来表示高精度数据, 比如货币
  • 字符串:
    • CHAR 255, 定长
    • VARCHAR 65535, 变长
    • TEXT: 还分为TEXT(65535), MEDIUMTEXT, LONGTEXT
    • BOLB: 还分为BOLB(65535), MEDIUMBOLB, LONGBOLB, 与TEXT的区别是, BOLB可以存储二进制数据, 比如图片
  • 日期:
    • DATE: 2017-07-25
    • DATETIME: 2017-07-25 21:57
    • TIMESTAMP: 字符串的”2017-07-25 21:57”

DATETIME 和 TIMESTAMP类型的区别:

  • DATETIM 和 TIMESTAMP类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。
  • 前者范围为 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.
  • TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。@ref: MySQL timestamp自动更新时间分享
  • TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

运算符

符号 描述 备注
= 等于
> 大于
\< 小于
!= 不等于 不可比较NULL, 例 1 != NULL也返回NULL
<> 不等于 同上
<=> 等于 NULL-Safe的比较, NULL<=>NULL为1, 1<=>NULL为0
IS NULL 等于NULL
IS NOT NULL 不等于NULL
BETWEEN 在两值之间
IN 在集合中
LIKE 模糊匹配

函数

  • 字符串拼接: CONCAT(s1, s2, …)
  • LOWER(s1), UPPER(s1)
  • FLOOR(a): 返回小于a的最大整数
  • MOD(a,b): 返回x/y的模

字符集(编码)

查看数据库使用的字符集:

# 方式1
mysql> status;

# 方式2
show variables like 'character%';

GBK vs UTF-8

  • GBK: 扩展了GB2312标准, 无论英文还是汉字都是2字节
  • UTF-8: 英文1字节, 汉字3字节, 同时也兼容ASCII码
  • 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。 相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而 UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库Cache以及网络传输的时间，从而提高性能。
  • 如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8更好，因为UTF-8的西文占1字节, 而GBK西文字符编码都是2个字节(汉字3字节)，会造成很多不必要的开销。

utf8 vs utf8mb4

  • utf8 表示西文需2字节, 汉字需3字节, 如果在utf8编码上使用vchar(100)这种类型, Mysql会为该列保留 “一个utf8最大占用空间x100” 也即 300字节.
  • utf8 存在的问题: uft8最大能编码的Unicode范围是3字节, 对于超过3字节的无能为力(包括一些汉字, 以及emoji表情)
  • utf8mb4 (后缀mb4意思是”most bytes 4”), “4字节 UTF-8 Unicode 编码”, utf8mb4可以最多表示4字节Unicode编码, utf8是utf8mb4的一个子集, utf8mb4使用与utf8相同的编码值和长度, 此外utf8mb4还包括utf8没有的4字节编码, 因此从旧版本的MySQL UTF8 升级数据时 不用担心字符转换或丢失数据

@ref 全面了解mysql中utf8和utf8mb4的区别 - 谢思华blog - OSCHINA

主键 & 外键

  • 主键: 一个表只能有一个列作为主键, 主键的值不可重复, 不可为空(NULL)

    • 主键一定是唯一性索引，唯一性索引并不一定就是主键

  • 外键: 一个表中的FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

    • FOREIGN KEY 约束用于预防破坏表之间连接的动作。
    • FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  • 外键约束：
    • (1)插入非空值时，如果主键值中没有这个值，则不能插入。
    • (2)更新时，不能改为主键表中没有的值。
    • (3)删除主键表记录时，可以在建外键时选定外键记录一起联删除还是拒绝删除。
    • (4)更新主键记录时，同样有级联更新和拒绝执行的选择。

@ref: 大家设计数据库时使用外键吗？ - 知乎

互联网行业应用不推荐使用外键： 用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受IO能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩；
所以Hibernate多对一（many-to-one）/一对多（one-to-many）关联也就很少提及了。

Hibernate，JPA 对象关系映射之关联关系映射策略

索引

① 按照索引的物理存储来分:

  • 聚集索引 (clustered index)：聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key来约束。主键索引对应的B+树, 叶子节点是一行的完整数据
  • 非聚集索引 (non-clustered index)：它并不决定数据在磁盘上的物理排序，其叶子节点的数据是主键的值。所以使用普通索引查询的时候，需要先找到对应的主键值，再回主键索引的B+树上找到行数据（回表）

② 从逻辑角度, MySQL一共有五类索引:

  • 唯一索引(UNIQUE INDEX), 唯一索引是不允许其中任何两行具有相同索引值的索引。主键是一种唯一性索引，它必须指定为“PRIMARY KEY”
  • 主键索引：主键索引是一种特殊的唯一索引，不允许有空值, 主键索引也是聚簇索引
  • 普通索引：非主键索引, 最基本的索引类型，没有唯一性之类的限制。
  • 联合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用联合索引时遵循最左前缀集合
    注意：建了一个(a,b,c)的联合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，每多一个索引都会增加写操作的开销和磁盘空间的开销。
  • 候选索引：与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。
  • 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

③ 从数据结构角度:

  1. BTREE索引: MyISAM和InnoDB存储引擎默认都是BTREE索引
  2. HASH索引：
    • 仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询
    • 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
    • 只有Memory存储引擎显示支持hash索引
  3. FULLTEXT索引: 全文索引，在 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。FULLTEXT索引也是按照分词原理建立索引的。
  4. RTree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

MySQL引擎

  • InnoDB
    • 支持事务
    • 支持行级锁
  • MyISAM
    • 不支持事务
    • 支持表锁, 不支持行级锁 // 并发性能差
    • 设计简单，某些场景下性能很好，例如获取整个表有多少条数据count(*)，性能很高。
  • Memory
    • 不支持事务
    • 支持哈希索引

MySQL Commands

连接

mysql -h 127.0.0.1 -u xxx -pXXX -P 3306

# --auto-rehash 启用自动补全(但我试了没用)
mysql -h主机地址 -P端口 -u用户名 -p密码 -D数据库名 --auto-rehash
#　Mysql的`schemas`和`数据库名`是等同的

# 连接成功后可以输入以下命令, 分号是必须的:
show databases;
use db_name;
show tables;
desc table_name; # 查看表结构
use table_name;

# 如果输入了一半sql命令想要放弃, 加上'\c'即可
select * from table_name \c

# 如果忘记了table在那个database 或schema, 表占用大小以及行数:
select * from  information_schema.tables where table_name = 'xxx'

导出

  • 导出数据库结构,不带数据: mysqldump -h ip_addr -uxxx -pxxx -d DBName > dump.sql
  • 如果要一并导出数据, 去掉-d参数.
  • 导出表, 不带数据: mysqldump -h ip_addr -uxxx -pxxx -d DBName TableName > dump.sql

导入

  • mysql -u username -p -h localhost DATABASE-NAME < data.sql

mycli

官网: https://www.mycli.net/

安装:

pip install mycli

或:

brew update && brew install mycli

Usage:

# 获取帮助
mycli --help

# 连接数据库
mycli -h 主机地址 -p 端口 -u 用户