mysql单节点写入速度

转载

智能探索者 2024-10-30 21:10:03

文章标签 mysql单节点写入速度高性能mysql怎么读数据子节点聚簇索引 文章分类 MySQL 数据库

类型相关

INT(1)和INT(20)对于存储和计算来说，意义是相同的，他不会限制值的合法范围，只是一些交互工具会用来显示字符的个数

默认是有符号的，可以指定为无符号，增加数据存储范围，如0-255，可以声明unsigned

整数比字符操作代价更低，因为字符集和校对规则使字符更复杂，如果是ip，也应该用整型存储

尽量避免NULL：如果查询中包含可能为null的列，对Mysql来说更难优化。它使索引、索引统计和值都比较复杂，可为NULL的列会使用更多的存储空间，当为Null的列表被索引时，每个索引记录需要一个额外的字节，尽量不为NULL列建索引【InnoDB例外，它使用单独的bit存储NULL值，对于很多值为NULL，少数非NULL有很好的空间效率】

char适合存储定长的值，它占用的存储空间固定

varchar适合存储可变长的值，由于值的长度可变，所以存储的空间不确定，当一个内存页无法容纳完varchar数据占用的空间时，innodb会分裂成两页

varchar适合：列的更新少，使用了复杂的字符集，每个字符使用不同的字节数存储时

varchar保存时占用空间： 1-byte or 2 byte + data

BLOB、TEXT:

值太大时，Innodb会分配额外的存储区域，每个值在行内需要1到4个字节存储一个指针

Blob存储的二进制数据，没有排序规则和字符集

TIMESTAMP只能保存1970年到2038年，显示的值依赖当前的时区

Datetime从1001年到9999年，类似字符串，因此和时区无关

字符集相关

字符集：

unicdoe一个字符统一用2个字节来标识，不管是汉字还是英文字母，还是符号，因此空间会有浪费

utf-8是一种变长的编码方式，使用1-4个字节，当字符在ascii码范围，就用一个字节标识，一个中文字符占3个字节

utf-8是广义的unicode字符集的实现方案，他已经尽力节省了空间，但GBK这种字符集还在大行其道，因为GBK是为中文量身定制的，他的空间更少，只是只支持中文，其他文字如韩文，会乱码，因此特定场景下还是有优势的

优化操作

从行缓冲中将编码过的列转换成行数据结构的操作代价是很高的，所以，用什么字段，取什么字段

粗略的经验法则：单个查询关联的表在12个表以内

大表的alter table可能会很慢，Mysql执行大部分修改表结构操作的方法是用新的结构创建一张表，从旧表中插入所有数据，删除旧表，如果服务器内存不足，有很大可能会持续几个小时

聚簇索引

聚簇索引指的是数据行存放在索引的叶子页中，一个表只能有一个聚簇索引

如果一个索引包含了所有需要用到的值，就叫覆盖索引，对于innodb，可以避免对主键索引的二次查询，效率很高

聚簇索引：索引和数据在一起，数据在叶子节点上，非叶子节点不存存储数据，这就决定了一个表只会有一个聚簇索引，B+树结构，适合排序？

优点:访问同一页的不同行数据时，如果数据页已经被加载到内存中，就不用再访问磁盘了

缺点：插入或修改时，代价昂贵，可能导致内存页一分为二；同时建议使用int主键，如果有uuid或其他规则的字符串做主键，可能导致索引稀疏，查询慢

辅助索引：叶子节点存放的不是数据，而是主键id，所以使用辅助索引，会先查到主键id，再查找到数据页

BTree:非叶子节点上也有数据，适合随机检索，越靠近root，磁盘i/o时间越少，速度越快

有j个孩子的节点，恰好有j-1个关键字

红黑树：弱平衡二叉树，每个节点到叶子节点的高度相同，java TreeMap,因此查询效率相当，数据存在节点上(所有节点上都有数据)

为什么不把b+树的真实数据放到非叶子节点：导致每个磁盘存放的数据变多，而磁盘容量大小有限，最终导致磁盘块数据增大，进而导致树的高度增高，树的高度增高后，一次索引查询的磁盘i/o次数变多，磁盘i/o是耗时操作！这实际提高了空间利用率

B+树查询效率稳定,还实现了range-query，扫描所有叶子节点就能扫描全库，这也是数据库使用它作为索引的数据结构的重要原因

mysql分区表

分区表并没有一个全局索引，索引只是在各个底层表上各自加上完全相同的索引，并且操作对程序是暗箱的，有一定风险

redo undo

Redo Log:记录已经commit但尚未写到磁盘的事务的最新数据，保证持久性

Undo Log:记录操作前的数据，方便崩溃回滚，在事务中想看到修改前的数据时，也会用到undo Log，undo log记录了修改前的数据

Innodb

Redo Log包含了Undo Log的内容、事务回滚时的操作

一个被回滚的事务，在恢复时，会先修改redo，再undo，因此不会破坏数据唯一性

innodb 特性

innodb会把>=768bytes的定长field转换可变长field，如char(255)可能存放超过768bytes数据，如utf8md4字符集中，一个字符最多可能占用4个字节，255个字符最多占用255*4= 10220bytes

innodb 默认row-format = Dynamic

dynamic row format

支持了page压缩 format格式下，innodb会把一行的最长的可变长那些列放到单独的off-page中去，并在cluster index上为溢出页保留一个20byte的指针，直到cluster index leaf-node page可以装下它。这通常和page大小以及row占用的总字节长度有关。

当行太长时，将选择最长的列放到页外存储，直到聚集索引记录适合B树页的大小。同时，小于或等于40字节的文本列和BLOB列存储在行中。

disk I/O

Innodb 会使用异步的disk I/O，如果可以的话。方法为：创建一些I/O线程，同时允许在I/O仍在进行时继续执行其他数据库操作。Linux和Windows环境下，会使用可用的OS library方法执行native的异步I/O

How Pages Relate to Table Rows

https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html

最大的row length 为略微低于数据页 size的一半，如默认情况page size =16KB ,则单行数据要略小于8KB

如果row没有超过超过最大row length,它的所有列都会存到这个page中。

反之，则会将可变长的列放到额外的off-page中，直到满足row length限制。

额外的off-page依据列存放依赖row format:

COMPACT and REDUNDANT Row Formats 存储前768 bytes在当前row中，其余的放到溢出页中，768个字节中包含了20-byte的value存储列的真实长度及溢出数据存储的位置

DYNAMIC and COMPRESSED Row Formats 存储20-byte的指针在当前row中

Buffer pool