Mysql从入门到放弃


前言

从今天开始着手 Mysql 相关博客的编写,计划十月中旬写完。

推荐一本 Mysql 进阶的书 《MySQL技术内幕:InnoDB存储引擎(第2版)》,这本书两天看了 60%,感觉收获还是很多的,会讲一些原理性的东西。相较于《Mysql DBA 修炼之道》和《高性能 Mysql》我更推荐 《MySQL技术内幕:InnoDB存储引擎(第2版)》,这本书基于 Mysql 5.6 来写的。

本文内容

  • Mysql 数据文件的说明
  • Mysql 数据逻辑存储架构
  • Mysql 表空间,主要是系统表空间和独立表空间
  • Mysql 数据类型
    • 时区对 datetime 和 timestamp 影响,java 中 LocalDatetime 保存时,时间和预期不符的原因分析和解决办法
    • varchar(n) 和 char(n) 保存时,n 能取多少,n 的含义。一行数据中 varchar 能存多少个
    • 整型、小数
  • Mysql 索引
    • 索引的类型:UNIQUE,FULLTEXT,SPATIAL,NORMAL(普通索引)
    • 索引为什么会采用 B+ 树结构,为什么不是红黑树、B- 树
    • Mysql 中 B+ 树索引 和 Hash 索引应该选哪个
    • 实际中开发中索引要怎么使用及怎么分析索引会失效

本文内容基于 Mysql 8.0.21 ,系统为 Centos 7。

Mysql 架构说明

https://dev.mysql.com/doc/refman/8.0/en/images/mysql-architecture.png

客户端(比如 java 后台服务)链接 Mysql 的 server 层,server 层会对 sql 进行语法解析和优化生成执行计划,然后调用存储引擎提供的接口,存储引擎层从计算机文件系统上读取对应的文件数据返回给 server ,server 再将数据返回给客户端。

存储引擎不了解的话,使用 InnoDB 就行。

Redis 为什么会比 Mysql 快,很大的原因是 Redis 的数据都在内存中,再加上比较好的数据结构,查询的速度当然不是一个量级的。但同时 Redis 不会存储那么多的数据量。

Mysql 将数据储存在硬盘上,比较好的做法是将索引数据和一部分热数据(经常访问的数据)放到内存中。

当检索数据的时候,Mysql 通过索引查找,就可以知道数据在磁盘哪里了,从硬盘对应位置读取对应的数据到内存中返回给客户端。

如果查询的时候没有走索引就需要扫描整个表数据文件,因为内存比硬盘小,会不停的从硬盘读取表中的一部分数据到内存,然后在内存中筛选出符合要求的数据,再去硬盘读取一部分数据做筛选直到整个表数据读取一遍。如果你有 20 g 数据,你想一下需要读取多长时间。

Mysql 8.0 进行了好多优化,如果有机会推荐使用 Mysql 8.0 。而且现在云上数据库也不贵,为了安全上云吗,别在发生删库跑路的事情了。

Mysql 数据存储

# -h 指定 mysqld 的服务地址
# -P 指定连接端口
# -u 执行用户(生产环境不建议使用 root 用户连接,合理使用权限管理。每个库使用不同的账号密码)
# -p 输入密码
mysql -hlocalhost -P3306 -uroot -p
-- 连接之后输入以下命令,查看数据储存在哪里了
-- /var/lib/mysql/  Centos 7.0 存储位置
show variables like '%datadir%';

系统表空间

系统表空间是所有数据共享的,它保存了数据表结构,事务信息,double write 数据

SHOW VARIABLES LIKE 'innodb_data_file_path%'
-- ibdata1:12M:autoextend

当我们建立数据库的时候,实际上会在 datadir 创建一个同名的目录。

独立表空间

当我们在数据库创建一张表时,除了在系统表空间生成元数据和表结构,也会在对应的数据库文件夹下,新建一个 tablename.ibd。

独立表空间主要用于储存表的数据和索引。

在 mysql 8.0 是默认开启独立表空间的,默认每张表使用一个文件进行保存数据和索引

-- 查看是否开启独立表空间配置
show variables like '%innodb_file_per_table%';

InnoDB 逻辑储存结构

image-20200921113000653

InnoDB 存储引擎下,所有的数据储存在表空间中。每个表有自己单独的表空间 (启用了 innodb_file_per_table,Mysql 8.0 默认开启)。

表空间又包含多个段(segment),常见的数据段有:

  • Leaf node segment 数据段,存储当前表中的数据
  • Non-Leaf node segment 索引段,存储当前表中的索引

段包含很多个区,每个区始终为 1MB 。区由多个连续连续的页组成,页的大小通常是 16KB,所以一个区可以有 64 个连续页。

页是 InnoDB 与磁盘交互的最小单位。从磁盘上读取数据,一次性是读取一页数据。将内存中的数据落盘到硬盘上,也是操作一页数据。

页也有类型,数据页,索引页,系统使用的页等等。

-- 查看页的大小,默认是 16KB 
show variables like '%innodb_page_size%'; ![image-20200921114611123](http://oss.mflyyou.cn/blog/20200921184611.png?author=zhangpanqin)

每页存放一行一行的数据。

mysql> SHOW TABLE STATUS LIKE "test_data_type"\G;
*************************** 1. row ***************************
           Name: test_data_type
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 22
 Avg_row_length: 14894
    Data_length: 327680
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 243
    Create_time: 2020-09-21 01:55:51
    Update_time: 2020-09-21 02:06:59
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

Row_format 定义了一行数据在数据页中怎么保存。

image-20200921120326750

VARCHAR(M)TEXT 类型的字段为变长字段,变长字段占用多少字节,放在额外信息保存,变长字段的内容保存在列下。

记录头信息中,记录着当前行的类型,下一条记录位置等信息。

真实的记录数据,除了我们自己定义的,还有 Mysql 为我们添加一些列,行 id(定义主键的时候,和主键一样),事务 id,回滚指针。

一页可以存 16KB 数据,但是 VARCAHR(m),可以存 65535 字节,m 是字符数量。这些变长数据大于一页需要怎么存呢。这个现象也叫做行溢出。

行溢出的数据会单独存在一页中,在真实数据中对应的列中记录一个指针指向溢出的数据。

这些内容了解即可,只是为了理解原理及辅助表设计。

数据类型

整型

类型 描述 占用字节 范围
tinyint 对应 java 中 byte 1 字节 有符号-128 至 127。
无符号 0 至 255
smallint 对应 java 中 short 2 字节 有符号 -32768 至 32767。
无符号 0 至 65535
int 对应 java 中 int 4 字节 有符号 -2147483648 至 2147483647。
无符号 0 至 4294967295
bigint 对应 java 中 long 8 字节 有符号 -9223372036854775808 至 9223372036854775807
无符号 0 至 18446744073709551615

小数

类型 描述 占用字节
FLOAT(M, D) 对应 java 中 float 4 字节
DOUBLE(M, D) 对应 java 中 double 8 字节
DECIMAL(M, D) 对应 java 中 BigDecimal。定点数,可以精确保存小数 M 和 D 决定

M 表示小数的有效数字,D 表示小数点后的有效数字。

FLOAT(4, 1) 不能存 4000.1 会报错误。

日期和时间

类型 描述 占用字节 取值范围
YEAR 年份 1 字节 1901~2155
DATE 日期,年月日 3 字节 1000-01-01~ 9999-12-31
TIME(fsp) 时间,时分秒 3 字节 -838:59:59.000000 ~ 838:59:59.000000
DATETIME(fsp) 日期+时间 5 字节 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
TIMESTAMP(fsp) 底层存储的是 UTC 时间戳,
显示值会随mysql 数据库所在时区变化
4 字节 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999

TIMEDATETIMETIMESTAMP这几种类型支持小数秒。x.xxx xxx,fsp 就是指定秒的精度,有多少个小数,取值 0,1,2,3,4,5,6。

DATETIME(0) 精确到秒,没有小数位。

DATETIME(3) 精确到豪秒,有三位小数。

日期和时间牵扯到时区的设置。

TIMESTAMP 的显示和数据库系统设置的时区有关。

还有 Java 1.8 新增的 LocalDateTime 需要怎么转换 Mysql 中的时间呢。

-- 查看 mysql 的时区设置
SHOW VARIABLES LIKE "%time_zone%";
mysql> SHOW VARIABLES LIKE "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CET    |
| time_zone        | +08:00 |
+------------------+--------+

system_time_zoneMysql 启动的时候获取计算机系统所在的时区。只要计算机的时间准确就没有问题。

time_zone 设置的是连接 mysql 的会话中,时间 (java.util.Date)转换为字符串时的 TimeZone。这个值可以被 jdbc 连接中的 serverTimezone=Asia/Shanghai 覆盖。

因为我们是在东八区,希望时间都转换为东八区时间。

[mysqld]
# 将时间转换为东八区的时间
default-time-zone = '+08:00'
CREATE TABLE `test_data_type` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_data_time` datetime DEFAULT NULL,
  `test_timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=243 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 实际保存数据的时候需要将日期转换为字符串,拼接成这样的 sql
INSERT INTO test_data_type (test_data_time,test_timestamp) VALUES ('2020-12-12 12:12:12','2020-12-12 12:12:12');

比如我们将 java 中的 LocalDateTime 存为 datetime 类型。

@Data
@TableName(value = "test_data_type")
public class TestDataType {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @TableField(value = "test_data_time")
    private LocalDateTime testDataTime;

    @TableField(value = "test_timestamp")
    private LocalDateTime testTimestamp;
}

当我们保存数据的时候,需要根据配置的 time_zone,将 LocalDateTime 转为 String,在替换到 sql 中的 ?

INSERT INTO test_data_type (test_data_time,test_timestamp) VALUES (?,?);
// NativeProtocol.configureTimezone 可以看到这个逻辑
@Test
public void run33() {
    // 首先获取到在服务器设置的 time_zone,如果没有设置的话,默认取 mysql 服务器所在时区
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
    // jdbc 链接中设置的参数
    String canonicalTimezone = getStringProperty("serverTimezone");
    if(canonicalTimezone==null||canonicalTimezone.length()<0){
        canonicalTimezone=configuredTimeZoneOnServer;
    }
    // jdbc url 参数中配置的 serverTimezone 和 time_zone 都是为了获取 TimeZone,serverTimezone 的优先级更高
     final TimeZone timeZone = TimeZone.getTimeZone(canonicalTimezone);
     // Timestamp 继承了 java.util.Date
     // 这里会将获得 LocalDateTime 获取其年月日时分秒上的值
    final Timestamp timestamp = Timestamp.valueOf(LocalDateTime.now());
    final SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
    simpleDateFormat.applyPattern("yyyy.MM.dd HH:mm:ss");
    simpleDateFormat.setTimeZone(timeZone);

    // 然后将这个 time 替换 ?
       String time= simpleDateFormat.format(timestamp);
}

当我们保存数据的创建时间的时候,只需获取当前时间就行。当前时区与东八区的时差,mysql 配置的 time_zoneserverTimezone 转换成字符串时会自动加上。

final Date createTime = new Date();
// LocalDateTime 一定不要自己补时差,不然时间会对不上
final LocalDateTime createTime2 = LocalDateTime.now();

字符串

varchar (M) 中 M 指的是字符数,但是 mysql 限制一行数据中,所有 varchar 列的总字节数不能超过 65535 字节。

-- utf8mb4 实际会占用 1-3 字节
CREATE TABLE `test_varchar`  (
  `test_name` varchar(65535) CHARACTER SET utf8mb4  NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

执行上述 sql 报错为:

1074 - Column length too big for column 'test_name' (max = 16383); use BLOB or TEXT instead, Time: 0.000000s

验证所有 varchar 列总数据不能超过 65535

CREATE TABLE `test_varchar`  (
  `test_name1` varchar(7000) CHARACTER SET utf8mb4  NOT NULL,
    `test_name2` varchar(7000) CHARACTER SET utf8mb4  NOT NULL,
    `test_name3` varchar(7000) CHARACTER SET utf8mb4  NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

执行上述 sql 报错信息为

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs, Time: 0.002000s

varchar (M) 实际占用字节数,除了数据的占用,还有数据字节数大小的记录(1-2 字节)。

char(M) M 也是指的字符数,列采用的字符集不同,char 类型数据占用大小也不一样。char 类型的数据没有达到指定字符数,数据库会自动补充空格,返回数据的时候在去掉空格。

当一个字符串太长的时候一定要采取 text 类型的数据,text 类型的数据存储的时候会作为行溢出数据存储,就没有 65535 大小的限制。

// 可以看到占用
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
数据类型 总字节数 内容字节
VARCHAR(M)、VARBINARY(M) L+ 1 bytes if column values require 0 − 255 bytes,
L+ 2 bytes if values may require more than 255 bytes
TINYBLOB 、TINYTEXT L+1 L<2^8
BLOB、TEXT L + 2 L<2^16
MEDIUMBLOB、MEDIUMTEXT L+ 3 L<2^24
LONGBLOB、LONGTEXT L+ 4 L<2^32

在表设计的时候经常有人说要选择合适的数据类型,为什么要选择合适的数据类型呢。

一方面是为了减少硬盘的占用量和内存的占用。另一个原因就是 Mysql 是以页为单位与计算机的文件系统进行读写数据的,当你的数据类型越小,一页中的数据就会越多,可以减少读取硬盘的次数(减少 io 次数)。

索引

Mysql 中常见的索引类型有:

  • 普通索引
  • 唯一索引
  • 全文索引
  • 空间索引

Mysql 中索引的结构有 B+ 树 和 Hash 。Hash 索引通常存储 key - value ,类似于 java 中的 Map,时间复杂度 O(1)。 基本我们使用较多的是 B+ 树数据结构的索引。

可以在这个网站,看数据结构是怎样运行的。比如说 B+ 树插入和删除
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

image-20200923203559362

相较于二叉树,B+ 树子节点会更多,树的高度会更低,在查找数据的时候,减少了遍历的次数以达到可以减少 Io 次数 (从磁盘加载数据到内存中)。

B+ 树相较于 B- 树,叶子节点是有序的,并且只有叶子节点会存数据。

比如查询大于 3 的数据的时候,找到了 3 直接遍历 3 上的链表就可以查询大于3 的数据。

当 B+ 树的每个子节点为 500 。500*500*500=125000000 树的高度为 4 时就能存下 1000 多万条数据。

但是一个表中的索引也不是越多越好,一般不超过 5 个索引。

索引相关名词

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(200) DEFAULT NULL,
  `phone` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_a_b` (`name`(20),`age`(5)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `my_test` VALUES (1, 'a', '1');
INSERT INTO `my_test` VALUES (2, 'a', '2');
INSERT INTO `my_test` VALUES (3, 'a', '3');
INSERT INTO `my_test` VALUES (4, 'b', '1');
INSERT INTO `my_test` VALUES (5, 'b', '2');

关于索引的图只是示意性展示,重在理解。

聚簇索引

聚簇索引(也可以简单理解为主键)的叶子节点存的是整行数据,而非聚簇索引的叶子节点存的是索引数据和主键。

未命名文件

非叶子节点都是主键,叶子节点中既有主键对应的行数据。

当使用主键查询数据的时候,实际就是查询聚簇索引,然后从其中把数据读出来返回。

联合索引

联合索引:就是多个列组成一个索引。比如 name 和 age 组成一个索引

联合索引

我们看到覆盖索引是按照 name,age 进行排序的,当 name 一样的时候,按 age 排序,并且叶子节点会有 id 的数据。

通过查询这个索引就可以得到对应数据行的数据主键,然后根据主键 id 查询聚簇索引得到整行数据。

覆盖索引

覆盖索引:也是联合索引。只是我们查询的数据就在索引中,不用再去 回表 查找数据了。

SELECT `name`,age FROM my_test WHERE `name` = b;

上述 sql 执行就利用了覆盖索引,查询的结果就在索引中。

SELECT * FROM my_test WHERE `name` = b;

索引中的数据只有 id,name,age,差 phone。这个数据只能通过回表去查询数据。

先查询 (name,age) 这个索引拿到主键 id,在通过主键 id 去聚簇索引中查询数据。

也有人经常推荐说,查询的时候要查询需要的字段,不要使用 select * ,这样做的好处一是减少 io,另一个就是避免回表。

Mysql 与磁盘交互的最小单位是 Page, B+ 树 中存储的实际是一页一页的数据,下面这张图可以近似理解。

图来自《MySQL 是怎样运行的:从根儿上理解 MySQL

img

索引的使用

了解了 B+ 树索引数据结构,我们也就差不多知道怎样使用索引了,也可以理解使用索引的一些规则。

通常说的索引失效,一部分是可以从数据结构来推算出来的,一部分就是 mysql 查询统计的数据显示不走索引性能会更高。我们可以通过查看索引的执行计划来判断 sql 怎么优化。

最左匹配规则

我们在使用索引的时候,只需要包含索引的最左边就可以匹配索引(name,age)

SELECT * FROM my_test WHERE `name` = 'b';

SELECT * FROM my_test WHERE `name` = 'b' AND age = 1;

当我们执行下面的 sql 的时候,就用不到索引 (name,age)

-- 通过查看执行计划,看查询的性能:全表扫描
EXPLAIN SELECT * FROM my_test WHERE age=1;

从索引的数据结构可以知道,B+ 树是按照 name 排序再按照 age 排序。age 实际是乱序的,没有办法进行范围查找。如果你还想在 age 进行索引查找,就需要在 age 上建立一个新索引。

-- 全表扫描
EXPLAIN SELECT * FROM my_test WHERE NAME LIKE '%a';

-- 索引范围查找
EXPLAIN SELECT * FROM my_test WHERE NAME LIKE 'a%';

索引的建立,不会整个字段值都参与索引的建立,一般会指定多长的字段(从值开头部分的长度)参与索引的建立。当我们不适用前缀匹配可能会有的数据查不到,这就是大问题了,这就是最左匹配规则。

当你需要关键字查找的时候,可以使用全文索引,或者是增加一个 ES 用于检索。通常不推荐 ES 代替关系型数据库进行数据存储,一方面 ES 不支持事务保证不了你业务数据的可靠性,另一方面 ES 大量的索引在数据库中才会性能很高。增加了 ES 同时也增加了架构的复杂性,不要为了一个小功能就要加 ES ,根据业务需求来判断。

image-20200926231208148


文章作者: 张攀钦
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 张攀钦 !
评论
 上一篇
java 异步编程 java 异步编程
前言在 java 中你不了解异步编程,crud 完全没有问题,但是有的需求你无法优雅的实现。 js 也存在异步编程,当你理解了用同步的思维编写异步的代码时,相信你在编程上的造诣又更进一步。 大多人都在追捧微服务,可能他们只会用 Ribbon
2020-11-08
下一篇 
你需要知道的TCP/IP 你需要知道的TCP/IP
计算机之间怎么通信及 TCP/IP 的重要点
2020-08-01
  目录