MySQL优化-表结构设计

2021-11-14 18:03:20 753

首先明确一个, 减少占用的存储空间, 可以减少操作时占用的内存, 可以提高CPU处理效率
字符串的ip地址可以转换为整数类型存储, mysql提供INET_ATON()和INET_NTOA()进行转换
尽量避免字段允许为NULL, 字段为NULL会占用额外空间
整数类型可以选择置为无符号, 同样的存储空间, 数值绝对值范围会大一倍
int(11) 和 int(1)在存储上没有区别
decimal可以替换为整形, 例: 5.20 可以替换为 520 存储, mysql在处理decimal的时候, 会需要额外的空间和CPU开销
VARCHAR为可变字符串, 会额外占用1~2字节存储字符串长度, 具体多少字节取决于字符串长度, 字节存储范围是有限的. 如下情况可以使用VARCHAR存储数据字符串列的最大长度比平均长度大很多列的更新很少
CHAR是定长的, 所以适用情况大致与VARCHAR是相反的
如果非要存储TEXT/BLOG这种大字段, 建议增加子表存储
对于有查询性能要求的功能, 应该考虑数据冗余, 例汇总字段/表, 也就是反范式, 例如员工和部门, 员工表除了关联部门id外, 还可以冗余存储部门名称等经常关联查询的字段
对于一些需要计数的场景, 比如更新文件下载次数, 可以将其拆分, 以一张独立的表进行存储, 为了提高并发性能, 可以将下载次数分散到100数据行上, 每次hash更新一行, 文件总下载次数就为100行数据的总和
禁止使用复杂数据类型(数组,自定义等)，Json类型的使用视情况而定
需要join的字段(连接键)，数据类型必须保持绝对一致，避免隐式转换
单表字段数不要太多，建议最多不要大于50个
MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内

OLAP / OLTP

数据库系统可以在广义上分为联机事务处理（Online Transaction Process，OLTP）联机分析处理（Online Analyze Process，OLAP）两种面向不同领域的数据库，OLAP数据库也被称为数据仓库。从产品上看，有专门面向OLTP的数据库，例如MySQL、Postgr

2023-10-15

MySQL Binlog/Redolog和CrashSafe机制

redo logredo log是MySQL InnoDB的日志, 是物理日志, 记录的是"在某个数据页上做了什么修改"提一下MySQL里经常说到的WAL技术, WAL的全称是Write Ahead Logging, 它的关键点就是先写日志, 再写磁盘. 日志是顺序写的, 磁盘是随机写. 顺序写速度

2022-12-12

使用 SOAR 优化 SQL

介绍soar是由小米开源的SQL优化器和重写器项目地址 https://github.com/XiaoMi/soar使用基于 ubuntu20.04 lts按文档安装后https://github.com/XiaoMi/soar/blob/master/doc/install.md现有 catego

2022-08-12

MySQL主从复制搭建

masterdocker run -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --network rootnet --ip 172.18.0.10 -v /usr/soft/mysql-master/data:/var/lib/mysql:rw -v /u

2022-05-19

MySQL InnoDB 加锁机制

MySQL 版本: 8.0.25隔离级别: 可重复读InnoDB有两种不同的SELECT，即普通SELECT 和锁定读SELECT. 锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE; 锁定读SELECT 之外的则是普通SE

2022-03-26

MySQL InnoDB MVCC机制

对于普通select来说, InnoDB使用MVCC保证了事务隔离. 同一事务的两次相同查询语句都是同样结果, 其他事务修改记录不影响当前事务, 特殊情况是会看到同一事务中先前语句所做的更新, 所以对于普通select(快照读)来说, MVCC是解决了脏读/不可重复读/幻行的; 而对于当前读(锁定读

2022-02-23

MySQL WITH AS 语法

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用特别对于UNION ALL比较有用. 因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，

2022-02-20

MySQL优化-表结构设计

首先明确一个, 减少占用的存储空间, 可以减少操作时占用的内存, 可以提高CPU处理效率字符串的ip地址可以转换为整数类型存储, mysql提供INET_ATON()和INET_NTOA()进行转换尽量避免字段允许为NULL, 字段为NULL会占用额外空间整数类型可以选择置为无符号, 同样的存储空间

2021-11-14

大字段如何对查询产生影响

一些应用, 在表结构的设计上使用了text或者blob的字段；其中一个应用，对blob/text字段的依赖非常的严重，查询和更新的频率也是非常的高，单表的存储空间已经达到了近100G，这个时候，应用其实已经被数据库绑死了，任何应用或者查询逻辑的变更几乎成为不可能；为了清楚大字段对性能的影响，我们必须

2021-10-10

常见的大表查询优化

测试表user, user_detail各100w数据下面是一个常见的连表查询分页sqlSELECT * FROM user u LEFT JOIN user_detail ud ON u.id = ud.user_id LIMIT 800000, 10 执行时间3.323s优化下可以写成这样SEL

2021-05-10

各平台时间格式

javayyyy-MM-dd HH:mm:ss2021-01-18 13:05:25mysqlDATE_FORMAT(time,'%Y-%m-%d %H:%i:%s')2021-01-18 13:05:25sql serverSELECT CONVERT(varchar(100), GETDATE(

2021-01-18

Mysql 通过binlog日志恢复数据

https://www.cnblogs.com/YCcc/p/10825870.html

2021-01-13

EXPLAIN 命令

EXPLAIN SELECT * FROM Test WHERE id = 200 其中：id — 选择标识符，id 越大优先级越高，越先被执行；select_type — 表示查询的类型；table — 输出结果集的表；partitions — 匹配的分区；type — 表示表的连接类型；poss

2020-08-06

如何将一个长URL转换为一个短URL？

https://blog.csdn.net/xlgen157387/article/details/80026452

2020-07-20

freemarker 时间显示不正常设置时区

项目在本地开发的时候显示正常，部署上服务器就一直差8个小时，最后发现freemarker官方文档有这样的说明time_zone：时区的名称来显示并格式化时间。默认情况下，使用JVM的时区。也可以是 Java 时区 API 接受的值，或者 "JVM default" (从 FreeMarker 2

2020-03-28

IDEA 2019.1 xml 不高亮

前几天更新了idea后，发现xml里的代码都没有了高亮，变得跟记事本一个德性了打开setting ，搜索 File Types，找到xml项，查看下方的匹配格式，果然没有xml，(idea真是厉害)点击右方的+，输入*.xml，点击ok，解决问题

2020-03-28

npm install 淘宝镜像

npm install --registry=https://registry.npm.taobao.org

2020-03-28

Java中方法的参数传递机制

来看一段代码 public class Man { private String name; private Integer age; public String getName() { return name; } publi

2020-03-28

基于自定义注解手写权限控制

方法一: AOP 方法二: 拦截器项目结构项目依赖<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-w

2020-03-28

Docker 部署详细全过程附代码

Docker 部署本站全过程环境：CentOS7.61. 安装Docker其他版本CentOS可以参考这个https://help.aliyun.com/document_detail/187598.html查看本机内核版本，内核版本需高于 3.10uname -r 确保 yum 包最新yum u

2020-03-28

SpringBoot 启动普通java工程

引入依赖<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> <version>2.0.9</version> </dependency>

2020-03-28

Vue.js DOM操作

<template> <input type="button" @click="reply($event)" value="回复"> </template> export default { methods: { replyFun(e) {

2020-03-29

CentOS7编译调试OpenJDK12

1. 下载源码https://hg.openjdk.java.net/jdk/jdk12点击左侧的browse，再点击zip，就可以下载zip格式的源码压缩包。unzip xxx.zip 解压文件2. 安装jdkyum install java-11-openjdk-devel -y3. 运行con

2020-04-23

编写自己的Spring Boot Starter

1.新建一个maven项目命名规则统一是xxx-spring-boot-starter完整pom.xml<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0"

2020-06-29