SQL性能治理经验谈

news/2025/1/15 14:02:45/

e78d1d1929aa37609f8e6781607b4edb.jpeg

背景

SQL数据类型

数值

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

类型大小范围(有符号)范围(无符号)用途
TINYINT1 Bytes(-128,127)(0,255)小整数值
SMALLINT2 Bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 Bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 Bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 Bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度
浮点数值
DOUBLE8 Bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度
浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

字符串

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

时间

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型大小
( bytes)
范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME8'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'YYYY-MM-DD hh:mm:ss混合日期和时间值
TIMESTAMP4'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYY-MM-DD hh:mm:ss混合日期和时间值,时间戳


SQL治理的几条特殊建议

【建议】表中的自增列(auto_increment属性),推荐使用bigint类型。

因为无符号int存储范围为-2147483648~2147483647(大约21亿左右),溢出后会导致报错。

真实项目运维会复杂一点:

  • 主键溢出问题暴露,往往意味着生产环境已经有脏数据产生,需要考虑数据回滚恢复

  • 对大表而言,修改表结构意味着大量binlog产生,要考虑主从同步延迟,带来的影响

  • 生产环境和灰度环境的代码兼容问题

【建议】业务中IP地址字段推荐使用int类型,不推荐用char(15)。

因为int只占4字节,可以用如下函数相互转换,而char(15)占用至少15字节。

一旦表数据行数到了1亿,那么要多用1.1G存储空间。

举例子,通过SQL的内置函数:inet_aton 和 inet_ntoa,完成数值到ip地址,以及ip地址到数值的转换。

bc04ab3d58e642e2f797955f2a1cc317.png

4634519bf6926ae6332bcf3dd6fea0b0.png

原理:ip地址的数字总是按照网络字节顺序存储,那么我们可以按照二进制规律去进行数值转换。

如ip地址(209.207.224.40),是按照 209×2^24 + 207×2^16 + 224×2^8 + 40 进行计算得出结果。

【建议】不推荐使用blob,text等类型,且文本数据尽量用varchar存储。

它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段。

另外,因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字符(实际上InnoDB并不支持65535长度的VARCHAR,因为还有别的开销,实际测试是能存放最大长度是65532字符),因此在utf8字符集下最多存21844个字符,超过会自动转换为mediumtext字段。

而text在utf8字符集下最多存21844个字符,一般建议用varchar类型,字符数不要超过2700。

注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。

行记录格式概念

Compact行记录格式

是Mysql5.0+引入的,设计目的是高效存储数据,一个页存放行数据越多,性能越好。

变长字段长度列表 + NULL标志位 + 记录头信息 + 列1数据 + 列2数据 + ...

060aaef108c46c67c91f15baca686fc7.png

Innodb中当一行记录超过8098字节时,会将该记录中选取最长的一个字段将其768字节放在原始page里,该字段余下内容放在overflow-page里。在compact行格式下,原始page和overflow-page都会加载。

上面讲的blob或变长大字段类型包括blob、text、varchar,其中varchar列值长度大于某数N时也会存溢出页;如果有TEXT、BLOB、VARCHAR列,Compact格式会存放768个前缀字节的列数据,行溢出数据则存放到overflow-page;

Dynamic行记录格式

对blob采用完全行溢出,即聚集索引记录(数据页)只保留20字节的指针,指向真实存放它的溢出段地址:

f81eeeac3f474b06f3eee7c4b87088bf.png

dynamic行格式,列存储是否放到off-page页,主要取决于行大小,它会把行中最长的那一列放到off-page,直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。可以避免compact那样把太多的大列值放到 B-tree Node,因为dynamic格式认为,只要大列值有部分数据放在off-page,那把整个值放入都放入off-page更有效。

【建议】时间类型尽量选取timestamp。

因为datetime占用8字节,timestamp仅占用4字节,但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。

更为高阶的方法,选用int来存储时间,使用SQL函数unix_timestamp()和from_unixtime()来进行转换。

sql">CREATE TABLE `test` (`id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名字',`desc` text COLLATE utf8mb4_unicode_ci,`birthday` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`birthday_time` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

62461fa9e6e1b203241f4a09c787f8e6.png

【建议】存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。因为int占用4字节,而double占用8字节,空间浪费。

举例子,101.11元,数据库用double存储,要消耗8字节;

如果改为int存储,101.11 * 100 = 10111,恰好可以用int存储到位,这样写入数据库没有问题;

同样,读出数据,10111 / 100 = 101.11,即可还原数据。

其他文章

亿级大表垂直拆分的工程实践

亿级大表冷热分级的工程实践


http://www.ppmy.cn/news/1521731.html

相关文章

vue 批量自动引入并注册组件或路由

有时候有大量的组件.vue后缀的,或.js,或.ts文件, 需要一个个的手动引入很麻烦,那么你可以尝试这样创建一个index.js 本项目使用vue3.x, vue2.x也可以照样用; 这里在components里面创建了一个idnex.js 文件 require.context 可以读取文件, 第一个参数是指当前文件夹, 第二个参…

828华为云征文|华为云Flexus X实例docker部署harbor镜像仓库

828华为云征文&#xff5c;华为云Flexus X实例docker部署harbor镜像仓库 华为云最近正在举办828 B2B企业节&#xff0c;Flexus X实例的促销力度非常大&#xff0c;特别适合那些对算力性能有高要求的小伙伴。如果你有自建MySQL、Redis、Nginx等服务的需求&#xff0c;一定不要错…

搜维尔科技:数据手套+机械手遥操作,五指触感灵巧手解决方案!

本灵巧手模仿人手的自由度分配及相关运动学参数进行设计的灵巧手&#xff0c;整手的手指设计采用了模块化的思想。整机重量轻&#xff0c;单指指尖抓取力强&#xff0c;五指灵巧手作为人体机器人或者机械臂末端操作工具&#xff0c;在工业生产的特殊环境中&#xff0c;对复杂形…

太极八卦图时钟屏保 这个电脑屏保很有个性哦 你喜欢吗?

太极八卦图时钟屏保 这个电脑屏保很有个性哦 你喜欢吗&#xff1f;今天小编给大家带来一个非常好看的电脑屏保软件&#xff1a;芝麻时钟&#xff1b;https://clock.zhimasoft.cn/ 太极八卦图时钟屏保 小编在网上看到很多人找八卦时钟屏保&#xff0c;发现这个非常有特色&#…

基于百度AIStudio飞桨paddleRS-develop版道路模型开发训练

基于百度AIStudio飞桨paddleRS-develop版道路模型开发训练 参考地址&#xff1a;https://aistudio.baidu.com/projectdetail/8271882 基于python35paddle120env环境 预测可视化结果&#xff1a; &#xff08;一&#xff09;安装环境&#xff1a; 先上传本地下载的源代码Pad…

火狐浏览器现在可以让你在不切换标签的情况下访问你最喜欢的AI聊天机器人 - 具体方法如下

希望快速使用生成式AI的Firefox用户&#xff0c;现在无需离开当前页面即可实现。周二发布的Firefox 130版带有一个设置选项&#xff0c;可以将您选择的聊天机器人添加到侧边栏中。 这一AI功能是设置界面中新的Firefox实验室页面的一部分&#xff0c;您可以通过该页面试验由Moz…

pytorch 模型部署

AI模型部署基本步骤 在训练好模型后&#xff0c;需要将模型进行部署&#xff0c;一般情况下&#xff0c;AI模型部署基本步骤有&#xff1a; 获取模型文件对模型进行转换&#xff0c;也就是所谓的parse或者convert针对转换后的模型进行优化&#xff0c;可能涉及很多优化步骤在…

记录k8s重启之后kubelet无法启动的问题

重启机器后&#xff0c;kubelet没有自启动&#xff0c;手动启动失败&#xff0c;检查日志反馈找不到bootstrap-kubelet.conf这个文件。 systemctl start kubelet journalctl -u kubelet 57481 run.go:74] "command failed" err"failed to run Kubelet: unable …