Mysql

前言 本文测试和讨论的前提是事务隔离级别为REPEATABLE READ(默认)且存储引擎为InnoDB的场景 测试表结构 CREATE TABLE `members` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 Consistent Nonlocking Reads(一致性非锁定读,快照读) 官方定义 常见场景 使用SELECT查询 time transaction1 transaction2 T1 BEGIN BEGIN T2 SELECT * FROM members T3 INSERT INTO members (name) VALUES (‘demo’) T4 COMMIT T5 SELECT * FROM members 以上示例中T2和T5读取数据一致,原因是T5读取的是当前事务开启后T2创建的快照(事务执行过程中的第一次读取) time transaction1 transaction2 T1 BEGIN BEGIN T2 INSERT INTO members (name) VALUES (‘demo’) T3 COMMIT T4 SELECT * FROM members 以上示例中T4能够读取到transaction2的已提交数据,因为T4是事务中第一次读取,此时才会创建快照,由于transaction2数据在T3时间已提交,早于T4,因此T4可读取到 ...

基本流程 docker-compose.yml文件配置 #数据库-主库 mysql57: image: ${MYSQL_IMAGE} environment: - MYSQL_ROOT_PASSWORD=${MYSQL_MASTER_ROOT_PASSWORD} #- MYSQL_USER=user #- MYSQL_PASSWORD=password volumes: #配置文件 - ${MYSQL_MASTER_CONF}:/etc/mysql/my.cnf:ro #数据目录 - ${MYSQL_MASTER_DATA}:/var/lib/mysql/:rw #查询日志文件 #- ./logs/mysql/query_mysql.log:/var/logs/mysql/query.mysql.log:rw #慢查询日志文件 #- ./logs/mysql/slow_mysql.log:/var/logs/mysql/slow.mysql.log:rw restart: always ports: - "${MYSQL_MASTER_PORT}:3306" container_name: mysql57 #从库01 mysql57-slave01: depends_on: - mysql57 image: ${MYSQL_IMAGE} environment: - MYSQL_ROOT_PASSWORD=${MYSQL_SLAVE_01_ROOT_PASSWORD} - MASTER_HOST=mysql57 #- MYSQL_USER=user #- MYSQL_PASSWORD=password volumes: #配置文件 - ${MYSQL_SLAVE_01_CONF}:/etc/mysql/my.cnf:ro #数据目录 - ${MYSQL_SLAVE_01_DATA}:/var/lib/mysql/:rw #查询日志文件 #- ./logs/mysql/query_mysql.log:/var/logs/mysql/query.mysql.log:rw #慢查询日志文件 #- ./logs/mysql/slow_mysql.log:/var/logs/mysql/slow.mysql.log:rw restart: always ports: - "${MYSQL_SLAVE_01_PORT}:3306" container_name: mysql57-slave01 #从库02 mysql57-slave02: depends_on: - mysql57 image: ${MYSQL_IMAGE} environment: - MYSQL_ROOT_PASSWORD=${MYSQL_SLAVE_02_ROOT_PASSWORD} - MASTER_HOST=mysql57 #- MYSQL_USER=user #- MYSQL_PASSWORD=password volumes: #配置文件 - ${MYSQL_SLAVE_02_CONF}:/etc/mysql/my.cnf:ro #数据目录 - ${MYSQL_SLAVE_02_DATA}:/var/lib/mysql/:rw #查询日志文件 #- ./logs/mysql/query_mysql.log:/var/logs/mysql/query.mysql.log:rw #慢查询日志文件 #- ./logs/mysql/slow_mysql.log:/var/logs/mysql/slow.mysql.log:rw restart: always ports: - "${MYSQL_SLAVE_02_PORT}:3306" container_name: mysql57-slave02 master配置 my.cnf文件加入: ...

最近在开发一个公司内部使用的财务报表系统,在一次查询时数据库报了一个错误,在网上查看原因并解决问题之后顺便记录一下,该sql的目标是从日报主表,日报月数据表,日报模块表中查询到指定经营公司,指定日期的当日汇总数据(包含部分当月数据用于后续计算) 问题sql如下: SELECT A1.depcode AS depcode, SUM(d_dd_xcddsntq) AS sntq, ( SELECT ( SUM(d_jk_scddldl) + SUM(d_jk_zcddldl) + SUM(d_jk_hdjkl) ) AS bysj FROM mall_daily A LEFT JOIN mall_daily_jk B ON A.id = B.d_id LEFT JOIN mall_daily_month C ON A.depcode = C.depcode AND A.d_datetime >= C. START AND A.d_datetime <= C. END WHERE A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d') AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d') AND A.depcode = A1.depcode ) AS aj13, ( SELECT SUM(B.d_dd_xcztdd) AS bysj FROM mall_daily A LEFT JOIN mall_daily_dd B ON A.id = B.d_id LEFT JOIN mall_daily_month C ON A.depcode = C.depcode AND A.d_datetime >= C. START AND A.d_datetime <= C. END WHERE A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d') AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d') AND C.table_name = 'DailyDd' AND A.depcode = A1.depcode ) AS aj25, TRUNCATE ( (SELECT aj25) / (SELECT aj13), 2 ) AS bysj, D.d_month_byjh AS byjh, TRUNCATE ( (SELECT bysj) / (SELECT byjh), 2 ) AS jhdcl, TRUNCATE (30 / 31, 2) AS sjjd, TRUNCATE ( (SELECT bysj) / (SELECT sntq), 2 ) AS tb FROM mall_daily A1 LEFT JOIN mall_daily_dd B ON A1.id = B.d_id LEFT JOIN mall_daily_jk C ON A1.id = C.d_id LEFT JOIN mall_daily_month D ON A1.depcode = D.depcode AND A1.d_datetime >= D. START AND A1.d_datetime <= D. END WHERE A1.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d') AND A1.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d') GROUP BY A1.depcode; 上述sql在执行时提示 ...

命名规范 库名、表名、字段名必须使用小写字母，并采用下划线分割 库名、表名、字段名禁用超过32个字符。须见名知意 库名、表名、字段名禁用使 MySQL保留字 临时库、表名必须以tmp为前缀，并以日期为后缀 备份库、表必须以bak为前缀，并以日期为后缀 基础规范 使用INNODB存储引擎 表字符集使用utf8mb4 所有表都需要添加注释 单表数据量建议控制在5000W以内 不在数据库中存储图片、文件等大数据 禁止在线上做数据库压力测试 禁止从测试、开发环境直连数据库 库表设计 禁止使用分区表 拆分大字段和访问频率低的字段，分离冷热数据 HASH进行散表，表名后缀使用十进制数，下标从0开始 按日期时间分表需符合YYYY[MM][DD][HH]格式 采用合适的分库分表策略。例如千库十表、十库百表等 字段设计 尽可能不使用TEXT、BLOB类型 DECIMAL代替FLOAT和DOUBLE存储精确浮点数 Simple is good 将字符转化为数字 使用TINYINT来代替ENUM类型 Generosity can be unwise 存储 “hello”时VARCHAR(5) VS VARCHAR(200) Avoid null if possible 所有字段均定义为NOT NULL ! Smaller is usually better 使用UNSIGNED存储非负整数 INT类型固定占用4字节存储 使用timestamp存储时间 使用INT UNSIGNED存储IPV4 使用VARBINARY存储大写敏感的变 字符串 禁用在数据库中存储明文密码 索引规范 索引的用途 去重 加速定位 避免排序 覆盖索引 索引数量控制 单张表中索引数量不超过5个 单个索引中的字段数不超过5个 对字符串使用前缀索引，前缀索引长度不超过8个字符 建议优先考虑前缀索引，必要时可添加伪列并建立索引 主键准则 表必须有主键 不使用更新频繁的列 尽量不选择字符串列 不使用UUID MD5 HASH 默认使用非空的唯一键 建议选择自增或发号器 重要的SQL必须被索引 UPDATE、DELETE语句的WHERE条件列 ORDER BY、GROUP BY、DISTINCT的字段 多表JOIN的字段 区分度最大的字段放在前 核心SQL优先考虑覆盖索引 避免冗余和重复索引 索引不是越多越好 综合评估数据密度和分布 考虑查询和更新比例