网站建设服务好公司,网站seo在线检测,做视频网站公司要怎么做,做网站文字居中代码这里整理一些mysql相关的知识点#xff0c;是自己不太熟悉的内容
varchar(n) 中 n 最大取值为多少
MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外#xff0c;其他所有的列#xff08;不包括隐藏列和记录头信息#xff09;占用的字节长度加起来不能超过 65535 个字节。 …这里整理一些mysql相关的知识点是自己不太熟悉的内容
varchar(n) 中 n 最大取值为多少
MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外其他所有的列不包括隐藏列和记录头信息占用的字节长度加起来不能超过 65535 个字节。
也就是说一行记录除了 TEXT、BLOBs 类型的列限制最大为 65535 字节注意是一行的总长度不是一列。
知道了这个前提之后我们再来看看这个问题「varchar(n) 中 n 最大取值为多少」
varchar(n) 字段类型的 n 代表的是最多存储的字符数量并不是字节大小哦。
要算 varchar(n) 最大能允许存储的字节数还要看数据库表的字符集因为字符集代表着1个字符要占用多少字节比如 ascii 字符集 1 个字符占用 1 字节那么 varchar(100) 意味着最大能允许存储 100 字节的数据。
单字段的情况
前面我们知道了一行记录最大只能存储 65535 字节的数据。
那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii在这种情况下 varchar(n) 中 n 最大取值是 65535 吗
不着急说结论我们先来做个实验验证一下。
我们定义一个 varchar(65535) 类型的字段字符集为 ascii 的数据库表。
CREATE TABLE test ( name VARCHAR(65535) NULL ) ENGINE InnoDB DEFAULT CHARACTER SET ascii ROW_FORMAT COMPACT; 看能不能成功创建一张表
可以看到创建失败了。
从报错信息就可以知道一行数据的最大字节数是 65535不包含 TEXT、BLOBs 这种大对象类型其中包含了 storage overhead。
问题来了这个 storage overhead 是什么呢其实就是「变长字段长度列表」和 「NULL 值列表」也就是说一行数据的最大字节数 65535其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以 我们在算 varchar(n) 中 n 最大值时需要减去 storage overhead 占用的字节数。
这是因为我们存储字段类型为 varchar(n) 的数据时其实分成了三个部分来存储
真实数据 真实数据占用的字节数 NULL 标识如果不允许为NULL这部分不需要 本次案例中「NULL 值列表」所占用的字节数是多少 前面我创建表的时候字段是允许为 NULL 的所以会用 1 字节来表示「NULL 值列表」。
本次案例中「变长字段长度列表」所占用的字节数是多少 「变长字段长度列表」所占用的字节数 所有「变长字段长度」占用的字节数之和。
所以我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示具体情况分为
条件一如果变长字段允许存储的最大字节数小于等于 255 字节就会用 1 字节表示「变长字段长度」 条件二如果变长字段允许存储的最大字节数大于 255 字节就会用 2 字节表示「变长字段长度」 我们这里字段类型是 varchar(65535) 字符集是 ascii所以代表着变长字段允许存储的最大字节数是 65535符合条件二所以会用 2 字节来表示「变长字段长度」。
因为我们这个案例是只有 1 个变长字段所以「变长字段长度列表」 1 个「变长字段长度」占用的字节数也就是 2 字节。
因为我们在算 varchar(n) 中 n 最大值时需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下varchar(n) 中 n 最大值 65535 - 2 - 1 65532。
多字段的情况
如果有多个字段的话要保证所有字段的长度 变长字段字节数列表所占用的字节数 NULL值列表所占用的字节数 65535。
行溢出后MySQL 是怎么处理的
MySQL 中磁盘和内存交互的基本单位是页一个页的大小一般是 16KB也就是 16384字节而一个 varchar(n) 类型的列最多可以存储 65532字节一些大对象如 TEXT、BLOB 可能存储更多的数据这时一个页可能就存不了一条记录。这个时候就会发生行溢出多的数据就会存到另外的「溢出页」中。
如果一个数据页存不了一条记录InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时溢出的数据会存放到「溢出页」中。
当发生行溢出时在记录的真实数据处只会保存该列的一部分数据而把剩余的数据放在「溢出页」中然后真实数据处用 20 字节存储指向溢出页的地址从而可以找到剩余数据所在的页。大致如下图所示。
MySQL 的 NULL 值是怎么存放的 MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列NULL 值并不会存储在行格式中的真实数据部分。
NULL值列表会占用 1 字节空间当表中所有字段都定义成 NOT NULL行格式中就不会有 NULL值列表这样可节省 1 字节的空间。
MySQL 怎么知道 varchar(n) 实际占用数据的大小 MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。
MySQL 有哪些锁
在 MySQL 里根据加锁的范围可以分为全局锁、表级锁和行锁三类。 全局锁是怎么用的 要使用全局锁则要执行这条命令
flush tables with read lock 执行后整个数据库就处于只读状态了这时其他线程执行以下操作都会被阻塞
对数据的增删改操作比如 insert、delete、update等语句 对表结构的更改操作比如 alter table、drop table 等语句。 如果要释放全局锁则要执行这条命令
unlock tables 当然当会话断开了全局锁会被自动释放。
意向锁
在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前需要先在表级别加上一个「意向共享锁」 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前需要先在表级别加上一个「意向独占锁」 也就是当执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占锁。
而普通的 select 是不会加行级锁的普通的 select 语句是利用 MVCC 实现一致性读是无锁的。
不过select 也是可以对记录加共享锁和独占锁的具体方式如下
//先在表上加上意向共享锁然后对读取的记录加共享锁 select … lock in share mode;
//先表上加上意向独占锁然后对读取的记录加独占锁 select … for update; 意向共享锁和意向独占锁是表级锁不会和行级的共享锁和独占锁发生冲突而且意向锁之间也不会发生冲突只会和共享表锁lock tables … read和独占表锁lock tables … write发生冲突。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。
如果没有「意向锁」那么加「独占表锁」时就需要遍历表里所有记录查看是否有记录存在独占锁这样效率会很慢。
那么有了「意向锁」由于在对记录加独占锁前先会加上表级别的意向独占锁那么在加「独占表锁」时直接查该表是否有意向独占锁如果有就意味着表里已经有记录被加了独占锁这样就不用去遍历表里的记录。
所以意向锁的目的是为了快速判断表里是否有记录被加锁。
行锁
InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。
前面也提到普通的 select 语句是不会对记录加锁的因为它属于快照读。如果要在查询时对记录加行锁可以使用下面这两个方式这种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁 select … lock in share mode;
//对读取的记录加独占锁 select … for update; 上面这两条语句必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin、start transaction 或者 set autocommit 0。
共享锁S锁满足读读共享读写互斥。独占锁X锁满足写写互斥、读写互斥。 行级锁的类型主要有三类
Record Lock记录锁也就是仅仅把一条记录锁上 Gap Lock间隙锁锁定一个范围但是不包含记录本身 Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。 Record Lock 称为记录锁锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的
当一个事务对一条记录加了 S 型记录锁后其他事务也可以继续对该记录加 S 型记录锁S 型与 S 锁兼容但是不可以对该记录加 X 型记录锁S 型与 X 锁不兼容; 当一个事务对一条记录加了 X 型记录锁后其他事务既不可以对该记录加 S 型记录锁S 型与 X 锁不兼容也不可以对该记录加 X 型记录锁X 型与 X 锁不兼容。 举个例子当一个事务执行了下面这条语句
mysql begin; mysql select * from t_test where id 1 for update; 就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁这样其他事务就无法对这条记录进行修改了。
Gap Lock 称为间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。
假设表中有一个范围 id 为35间隙锁那么其他事务就无法插入 id 4 这条记录了这样就有效的防止幻读现象的发生。 Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。
假设表中有一个范围 id 为35] 的 next-key lock那么其他事务即不能插入 id 4 记录也不能修改 id 5 这条记录。
