Escape Ray

Blog owned by escray

36 Rules for MySQL Developer

22 Jan 2019

MySQL数据库开发的三十六条军规

石展@赶集 http://weibo.com/wushizhan

来自一线的实战经验 每一条军规背后都是血淋林的教训

不要华丽，只要实用 若有一条让你有所受益，慰矣

主要针对数据库开发人员

总是在灾难发生后，才想起容灾的重要性； 总是在吃亏后，才记得曾经有人提醒过。

目录

  1. 核心军规 5 条
  2. 字段类军规 6 条
  3. 索引类军规 5 条
  4. SQL 类军规 15 条
  5. 约定类军规 5 条

核心

1. 不在数据库做运算

别让脚指头想事情，那是脑瓜子的职责

让数据库做擅长的事情：

  • 尽量不在数据库做运算
  • 复杂运算移到程序端 CPU
  • 尽可能简单应用 MySQL

举例：

md5() / order by rand()

2. 控制单表数据量

一年内单表数据量预估：

  • 纯 INT 不超过 1000W
  • 含 CHAR 不超 500W

合理分表不超载：

  • USERID
  • DATE
  • AREA
  • ……

建议单库不超过 300~400 表

3. 保持表身段苗条

表字段数少而精

单表多少字段合适？

单表 1 G 体积 500W 行评估

  • 顺序读 1G 文件需要 N 秒
  • 单行不超过 200 Byte
  • 单表不超过 50 个纯 int 字段
  • 单表不超过 20 个 char(10) 字段

单表字段数上限控制在 20~50 个

4. 平衡范式与冗余

  • 严格遵循三大范式？
  • 效率优先、提升性能
  • 没有绝对的对与错
  • 适当时牺牲范式、加入冗余
  • 但会增加代码复杂度

5. 拒绝 3B

数据库并发像城市交通：非线性增长

拒绝 3B

  • Big SQL，大 SQL
  • Big Transaction，大事务
  • Big Batch，大批量

字段

  1. 用好数值字段类型 三类数值类型：
    • TINYINT, 1 Byte
    • SMALLINT, 2B
    • MEDIUMINT, 3B
    • INT, 4B; BIGINT 8B
    • FLOAT, 4B; DOUBLE, 8B
    • DECIMAL(M, D)
  2. 将字符串转化为数字 数字型 vs. 字符串型索引
    • 更高效
    • 查询更快
    • 占用空间更小 举例：用无符号 INT 存储 IP，而非 CHAR(15)
    • INT UNSIGNED
    • INET_ATON()
    • INET_NTOA()
  3. 优先使用 ENUM 或 SET 优先使用 ENUM 或 SET
    • 字符串
    • 可能值已知且有限 存储
    • ENUM 占用 1 字节，转为数值运算
    • SET 视节点定，最多占用 8 字节
    • 比较时需要加单引号 ‘ （即使是数值） 举例： sex enum(‘F’, ‘M’) COMMENT ‘性别’ c1 enum(‘1’, ‘2’, ‘3’) COMMENT ‘职介审核’
  4. 避免使用 NULL 避免使用 NULL 字段
    • 很难进行查询优化
    • NULL 列加索引，需要额外空间
    • 含 NULL 复合索引无效 举例： ✘ a char(32) DEFAULT NULL ✘ b int(10) NOT NULL √ c int(10) NOT NULL DEFAULT 0
  5. 少用并拆分 TEXT/BLOB TEXT 类型处理性能远低于 VARCHAR
    • 强制生成硬盘临时表
    • 浪费更多空间
    • VARCHAR(65535) ==> 64K （注意 UTF-8） 尽量不用 TEXT/BLOB 数据类型 若必须使用则拆分到单独的表 举例： CREATE TABLE t1( id INT NOT NULL AUTO_INCREMENT, data text NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
  6. 不在数据库存图片

索引

  1. 谨慎合理添加索引 谨慎合理添加索引
    • 改善查询
    • 减慢更新
    • 索引不是越多越好 能不加的索引尽量不加
    • 综合评估数据库密度和数据分布
    • 最好不超过字段数 20% 结合核心 SQL 优先考虑覆盖索引 举例：不要给“性别”列建索引
  2. 字符字段必须建立前缀索引 区分度：
    • 单字母区分度：26
    • 4 字母区分度：26 × 26 × 26 × 26 = 456,976
    • 5 字母区分度：26 × 26 × 26 × 26 × 26 = 11,881,376
    • 6 字母区分度：26 × 26 × 26 × 26 × 26 × 26 = 308,915,776 字符字段必须建前缀索引 pinyin varchar(100) DEFAULT NULL COMMENT ‘小区拼音’, KEY idx_pinyin(pinyin(8)), ) ENGINE=InnoDB
  3. 不在索引列做运算 不在索引列进行数学运算或函数运算
    • 无法使用索引
    • 导致全表扫描 举例：

✘ select * from table where to_days(current_date) - to_days(date_col) <= 10 √ select * from table where date_col >= DATE_SUB(‘2011-19-22’, INTERVAL 10 DAY); where id+1 = 100 和 id = 100 - 1，效率差很远

  1. 自增列或全局 ID 做 InnoDB 主键
  • 对主键建立聚簇索引
  • 二级索引存储主键值
  • 主键不应更新修改
  • 按自增顺序插入值
  • 忌用字符串做主键
  • 聚簇索引分裂
  • 推荐用独立于业务的 AUTO_INCREMENT 列或全局 ID 生成器做代理主键
  • 若不指定主键，InnoDB 会用唯一且费控值索引替代
    1. 尽量不用外键 线上 OLTP 系统（线下系统另论）
  • 外键可节省开发量
  • 有额外开销
  • 逐行操作
  • 可“到达”其他表，意味着锁
  • 高并发时容易死锁 由程序保证约束 SQL
    1. SQL语句尽可能简单 大 SQL vs. 多个简单 SQL
  • 传统设计思想
  • BUT MySQL NOT
  • 一条 SQL 只能在一个 CPU 运算
  • 5000+ QPS 的高并发中，1 秒大 SQL 意味着？
  • 可能一条大 SQL 就把整个数据库堵死 拒绝大 SQL，拆解成多条简单 SQL
  • 简单 SQL 缓存命中率更高
  • 减少锁表时间，特别是 MyISAM
  • 用上多 CPU
    1. 保持事务（连接）短小 保持事务 / DB 链接短小精悍
  • 事务/连接使用原则：即开即用，用完即关
  • 与事务无关操作放到事务外面，减少锁资源的占用
  • 不破坏一致性前提下，使用多个短事务代替长事务 举例：
  • 发帖时的图片等待上传
  • 大量的 sleep 连接
    1. 尽可能避免使用 SP / TRIG / FUNC 线上 OLTP 系统（线下库另论）
  • 尽可能少用存储过程
  • 尽可能少用触发器
  • 减少使用 MySQL 函数对结果进行处理 由客户端程序负责。
    1. 尽量不用 SELECT * 用 SELECT * 时
  • 更多消耗 CPU、内存、IO、网络带宽
  • 先向数据库请求所有列，然后丢掉不需要的列？ 尽量不用 SELECT *，只取需要的数据列
  • 更安全的设计：减少表变化带来的影响
  • 未使用 covering index 提供可能性
  • Select / Join 减少硬盘临时表生成，特别是有 TEXT/BLOB 时 ✘ SELECT * FROM tag WHERE id = 999184 √ SELECT keyword FROM tag WHERE id = 999184
    1. 改写 OR 为 IN() 同一字段，将 OR 改写为 IN( )
  • OR 效率：O(n)
  • IN 效率：O(log n)
  • 当 n 很大时，OR 会慢很多 注意控制 IN 的个数，建议 n 小于 200 举例： ✘ SELECT * FROM opp WHERE phone = ‘12345678’ or phone = ‘42242233’; √ SELECT * FROM opp WHERE phone IN (‘12345678’, ‘42242233’);
    1. 改写 OR 为 UNION 不同字段，将 OR 改为 UNION
  • 减少对不同字段进行 OR 查询
  • Merge Index 往往很弱智
  • 如果有足够信心：set global optimizer_switch = ‘index_merge=off’; 举例： ✘ SELECT * FROM opp WHERE phone = ‘010-88886666’ OR cellPhone = ‘13800138000’; √ SELECT * FROM opp WHERE phone = ‘010-88886666’ UNION SELECT * FROM opp WHERE cellPhone = ‘13800138000’;
    1. 避免负向查询和 % 前缀模糊查询 避免负向查询：NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE … 避免 % 前缀模糊查询
  • B+ Tree
  • 使用不了索引
  • 导致全表扫描 举例：
  1. 减少使用 COUNT(*) 几个有趣的例子
    • COUNT(COL) vs. COUNT(*)
    • COUNT(*) vs. COUNT(1)
    • COUNT(1) vs. COUNT(0) vs. COUNT(100) 示例： id int(10) NOT NULL AUTO_INCREMENT COMMENT ‘公司的 id’, sale_id int(10) unsigned DEFAULT NULL,

结论：

  • COUNT(*) = COUNT(1)
  • COUNT(0) = COUNT(1)
  • COUNT(1) = COUNT(100)
  • COUNT(*) != COUNT(col) WHY? MyISAM vs. InnoDB
  • 不带 WHERE COUNT()
  • 带 WHERE COUNT() COUNT(*) 的资源开销大，尽量不用/少用 计数统计
  • 实时统计：用 memchache，双向更新，凌晨跑基准
  • 非实时统计：计量用单独统计表，定期重算
    1. LIMIT 高效分页 传统分页： SELECT * FROM table LIMIT 10000,10; LIMIT 原理：
  • LIMIT 10000, 10
  • 偏移量越大则越慢 推荐分页： SELECT * FROM table WHERE id >= 23423 LIMIT 11;

    10+1 （每页 10 条）

    SELECT * FROM table WHERE id >= 23434 LIMIT 11; 分页方式二： SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 10000,1) LIMIT 10; 分页方式三： SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000, 10) USING (id); 分页方式四：

    程序取 ID

    SELECT id FROM table LIMIT 10000, 10; SELECT * FROM table WHERE id IN (123, 456 … ); 可能需按场景分析并重组索引 示例：

  1. 用 UNION ALL 而非 UNION 若无需对结果进行去重，则用 UNION ALL；UNION 有去重开销 举例： SELECT * FROM detail20091128 UNION ALL SELECT * FROM detail20110427 UNION ALL SELECT * FROM detail20110426 UNION ALL SELECT * FROM detail20110425 UNION ALL SELECT * FROM detail20110424 UNION ALL SELECT * FROM detail20110423;
  2. 分解连接保证高并发 高并发 DB 不建议进行两个表以上的 JOIN 适当分解连接保证高并发
    • 可缓存大量早期数据
    • 使用了多个 MyISAM 表
    • 对大表的小 ID 使用 IN()
    • 连接引用同一个表多次 举例： SELECT * FROM tag JOIN tag_post ON tag_post.tag.id = tag.id JOIN post ON tag_post.post_id = WHERE tag.tag = ‘二手玩具’; → SELECT * FROM tag WHERE tag = ‘二手玩具’; SELECT * FROM tag_post WHERE tag_id = 1321; SELECT * FROM post WHERE post.id IN (123, 456, 314, 141);
  3. GROUP BY 去除排序 GROUP BY 实现
    • 分组
    • 自动排序 无需排序：ORDER BY NULL 特定排序：GROUP BY DESC/ASC 举例
  4. 同数据类型的列值比较 原则：数字对数字，字符对字符 数值列与字符类型比较
    • 同时转换为双精度
    • 进行比对 字符列与数值类型比较
    • 字符列整列转数值
    • 不会使用索引查询 举例： remark varchar(50) NOT NULL COMMENT ‘备注，默认为空’,

SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark = 115127; → SELECT id, gift_code FROM pool_gift WHERE deal_id = 640 AND remark = ‘115127’;

  1. Load data 导数据 批量数据快速导入
    • 成批装载比单行装载更快，不需要每次刷新缓存
    • 无索引时装载比索引装载更快
    • Insert values, values, values 减少索引刷新
    • Load data 比 Insert 快约 20 倍 尽量不用 INSERT … SELECT
    • 延迟
    • 同步出错
  2. 打散大批量更新 大批量更新凌晨操作，避开高峰 凌晨不限制 白天上限默认为 100 条/秒（特殊再议） 举例： UPDATE post SET tag = 1 WHERE id in (1, 2, 3); SLEEP 0.01; UPDATE post SET tag = 1 WHERE id in (4, 5, 6); SLEEP 0.01; …
  3. Know Every SQL!
  • EXPLAIN
  • SHOW PROFILE
  • SHOW SLOW LOG
  • SHOW PROCESSLIST
  • MySQL dumpslow
  • SHOW QUERY_RESPONSE_TIME(Percona) 约定
    1. 隔离线上线下 构建数据库的生态环境
  • 开发无线上库操作权限 原则：线上连线上，线下连线下
  • 实时数据用 real 库
  • 模拟环境用 sim 库
  • 测试用 qa 库
  • 开发用 dev 库 案例：
  1. 禁止未经 DBA 确认的子查询 MySQL 子查询
    • 大部分情况优化较差
    • 特别 WHERE 中使用 IN id 的子查询
    • 一般可用 JOIN 改写 举例： ✘ select * from table1 where id in (select id from table2); ✘ insert into table1 (select * from table2); // 可能导致复制异常
  2. 永远不在程序端显式加锁

永远不在程序端对数据库显式加锁

  • 外部锁对数据库不可控
  • 高并发时是灾难
  • 极难调试和排查 并发扣款等一致性问题
  • 采用事物
  • 相对值修改
  • Commit 前二次校验冲突
    1. 统一字符集为 UTF8

字符集：

  • MySQL 4.1 以前只有 latin1
  • 为多语言支持增加了多字符集
  • 也带来了 N 多问题
  • 保持简单 统一字符集：UTF8 校对规则：utf8_general_ci 乱码：SET NAMES UTF8
    1. 统一命名规范 库表等名称同意用小写
  • Linux VS Windows
  • MySQL 库表大小写敏感
  • 字段名大小写不敏感 索引命名默认为 “idx_字段名” 库名用缩写，尽量在 2~7 个字母
  • DataSharing ==> ds 注意避免使用保留字命名 举例： ✘ select * from return; √ select * from return;

MySQL数据库开发的三十六条军规_石展 https://github.com/calvinlau/calvinwiki/wiki/MySQL https://my.oschina.net/BearCatYN/blog/476748 http://www.educity.cn/wenda/402373.htmlMySQL数据库开发的三十六条军规 石展@赶集 http://weibo.com/wushizhan 来自一线的实战经验 每一条军规背后都是血淋林的教训 不要华丽，只要实用 若有一条让你有所受益，慰矣 主要针对数据库开发人员 总是在灾难发生后，才想起容灾的重要性； 总是在吃亏后，才记得曾经有人提醒过。 目录

  1. 核心军规 5 条
  2. 字段类军规 6 条
  3. 索引类军规 5 条
  4. SQL 类军规 15 条
  5. 约定类军规 5 条 核心
  6. 不在数据库做运算

别让脚指头想事情，那是脑瓜子的职责 让数据库做擅长的事情：

  • 尽量不在数据库做运算
  • 复杂运算移到程序端 CPU
  • 尽可能简单应用 MySQL 举例： ✘ md5() / order by rand()
    1. 控制单表数据量

一年内单表数据量预估：

  • 纯 INT 不超过 1000W
  • 含 CHAR 不超 500W 合理分表不超载：
  • USERID
  • DATE
  • AREA
  • …… 建议单库不超过 300~400 表
    1. 保持表身段苗条

表字段数少而精 单表多少字段合适？ 单表 1 G 体积 500W 行评估

  • 顺序读 1G 文件需要 N 秒
  • 单行不超过 200 Byte
  • 单表不超过 50 个纯 int 字段
  • 单表不超过 20 个 char(10) 字段 单表字段数上限控制在 20~50 个
    1. 平衡范式与冗余
  • 严格遵循三大范式？
  • 效率优先、提升性能
  • 没有绝对的对与错
  • 适当时牺牲范式、加入冗余
  • 但会增加代码复杂度
    1. 拒绝 3B

数据库并发像城市交通：非线性增长 拒绝 3B

  • Big SQL，大 SQL
  • Big Transaction，大事务
  • Big Batch，大批量 字段
    1. 用好数值字段类型 三类数值类型：
  • TINYINT, 1 Byte
  • SMALLINT, 2B
  • MEDIUMINT, 3B
  • INT, 4B; BIGINT 8B
  • FLOAT, 4B; DOUBLE, 8B
  • DECIMAL(M, D)
  1. 将字符串转化为数字 数字型 vs. 字符串型索引
    • 更高效
    • 查询更快
    • 占用空间更小 举例：用无符号 INT 存储 IP，而非 CHAR(15)
    • INT UNSIGNED
    • INET_ATON()
    • INET_NTOA()
  2. 优先使用 ENUM 或 SET 优先使用 ENUM 或 SET
    • 字符串
    • 可能值已知且有限 存储
    • ENUM 占用 1 字节，转为数值运算
    • SET 视节点定，最多占用 8 字节
    • 比较时需要加单引号 ‘ （即使是数值） 举例： sex enum(‘F’, ‘M’) COMMENT ‘性别’ c1 enum(‘1’, ‘2’, ‘3’) COMMENT ‘职介审核’
  3. 避免使用 NULL 避免使用 NULL 字段
    • 很难进行查询优化
    • NULL 列加索引，需要额外空间
    • 含 NULL 复合索引无效 举例： ✘ a char(32) DEFAULT NULL ✘ b int(10) NOT NULL √ c int(10) NOT NULL DEFAULT 0
  4. 少用并拆分 TEXT/BLOB TEXT 类型处理性能远低于 VARCHAR
    • 强制生成硬盘临时表
    • 浪费更多空间
    • VARCHAR(65535) ==> 64K （注意 UTF-8） 尽量不用 TEXT/BLOB 数据类型 若必须使用则拆分到单独的表 举例： CREATE TABLE t1( id INT NOT NULL AUTO_INCREMENT, data text NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
  5. 不在数据库存图片

索引

  1. 谨慎合理添加索引 谨慎合理添加索引
    • 改善查询
    • 减慢更新
    • 索引不是越多越好 能不加的索引尽量不加
    • 综合评估数据库密度和数据分布
    • 最好不超过字段数 20% 结合核心 SQL 优先考虑覆盖索引 举例：不要给“性别”列建索引
  2. 字符字段必须建立前缀索引 区分度：
    • 单字母区分度：26
    • 4 字母区分度：26 × 26 × 26 × 26 = 456,976
    • 5 字母区分度：26 × 26 × 26 × 26 × 26 = 11,881,376
    • 6 字母区分度：26 × 26 × 26 × 26 × 26 × 26 = 308,915,776 字符字段必须建前缀索引 pinyin varchar(100) DEFAULT NULL COMMENT ‘小区拼音’, KEY idx_pinyin(pinyin(8)), ) ENGINE=InnoDB
  3. 不在索引列做运算 不在索引列进行数学运算或函数运算
    • 无法使用索引
    • 导致全表扫描 举例：

✘ select * from table where to_days(current_date) - to_days(date_col) <= 10 √ select * from table where date_col >= DATE_SUB(‘2011-19-22’, INTERVAL 10 DAY); where id+1 = 100 和 id = 100 - 1，效率差很远

  1. 自增列或全局 ID 做 InnoDB 主键
  • 对主键建立聚簇索引
  • 二级索引存储主键值
  • 主键不应更新修改
  • 按自增顺序插入值
  • 忌用字符串做主键
  • 聚簇索引分裂
  • 推荐用独立于业务的 AUTO_INCREMENT 列或全局 ID 生成器做代理主键
  • 若不指定主键，InnoDB 会用唯一且费控值索引替代
    1. 尽量不用外键 线上 OLTP 系统（线下系统另论）
  • 外键可节省开发量
  • 有额外开销
  • 逐行操作
  • 可“到达”其他表，意味着锁
  • 高并发时容易死锁 由程序保证约束 SQL
    1. SQL语句尽可能简单 大 SQL vs. 多个简单 SQL
  • 传统设计思想
  • BUT MySQL NOT
  • 一条 SQL 只能在一个 CPU 运算
  • 5000+ QPS 的高并发中，1 秒大 SQL 意味着？
  • 可能一条大 SQL 就把整个数据库堵死 拒绝大 SQL，拆解成多条简单 SQL
  • 简单 SQL 缓存命中率更高
  • 减少锁表时间，特别是 MyISAM
  • 用上多 CPU
    1. 保持事务（连接）短小 保持事务 / DB 链接短小精悍
  • 事务/连接使用原则：即开即用，用完即关
  • 与事务无关操作放到事务外面，减少锁资源的占用
  • 不破坏一致性前提下，使用多个短事务代替长事务 举例：
  • 发帖时的图片等待上传
  • 大量的 sleep 连接
    1. 尽可能避免使用 SP / TRIG / FUNC 线上 OLTP 系统（线下库另论）
  • 尽可能少用存储过程
  • 尽可能少用触发器
  • 减少使用 MySQL 函数对结果进行处理 由客户端程序负责。
    1. 尽量不用 SELECT * 用 SELECT * 时
  • 更多消耗 CPU、内存、IO、网络带宽
  • 先向数据库请求所有列，然后丢掉不需要的列？ 尽量不用 SELECT *，只取需要的数据列
  • 更安全的设计：减少表变化带来的影响
  • 未使用 covering index 提供可能性
  • Select / Join 减少硬盘临时表生成，特别是有 TEXT/BLOB 时 ✘ SELECT * FROM tag WHERE id = 999184 √ SELECT keyword FROM tag WHERE id = 999184
    1. 改写 OR 为 IN() 同一字段，将 OR 改写为 IN( )
  • OR 效率：O(n)
  • IN 效率：O(log n)
  • 当 n 很大时，OR 会慢很多 注意控制 IN 的个数，建议 n 小于 200 举例： ✘ SELECT * FROM opp WHERE phone = ‘12345678’ or phone = ‘42242233’; √ SELECT * FROM opp WHERE phone IN (‘12345678’, ‘42242233’);
    1. 改写 OR 为 UNION 不同字段，将 OR 改为 UNION
  • 减少对不同字段进行 OR 查询
  • Merge Index 往往很弱智
  • 如果有足够信心：set global optimizer_switch = ‘index_merge=off’; 举例： ✘ SELECT * FROM opp WHERE phone = ‘010-88886666’ OR cellPhone = ‘13800138000’; √ SELECT * FROM opp WHERE phone = ‘010-88886666’ UNION SELECT * FROM opp WHERE cellPhone = ‘13800138000’;
    1. 避免负向查询和 % 前缀模糊查询 避免负向查询：NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE … 避免 % 前缀模糊查询
  • B+ Tree
  • 使用不了索引
  • 导致全表扫描 举例：
  1. 减少使用 COUNT(*) 几个有趣的例子
    • COUNT(COL) vs. COUNT(*)
    • COUNT(*) vs. COUNT(1)
    • COUNT(1) vs. COUNT(0) vs. COUNT(100) 示例： id int(10) NOT NULL AUTO_INCREMENT COMMENT ‘公司的 id’, sale_id int(10) unsigned DEFAULT NULL,

结论：

  • COUNT(*) = COUNT(1)
  • COUNT(0) = COUNT(1)
  • COUNT(1) = COUNT(100)
  • COUNT(*) != COUNT(col) WHY? MyISAM vs. InnoDB
  • 不带 WHERE COUNT()
  • 带 WHERE COUNT() COUNT(*) 的资源开销大，尽量不用/少用 计数统计
  • 实时统计：用 memchache，双向更新，凌晨跑基准
  • 非实时统计：计量用单独统计表，定期重算
    1. LIMIT 高效分页 传统分页： SELECT * FROM table LIMIT 10000,10; LIMIT 原理：
  • LIMIT 10000, 10
  • 偏移量越大则越慢 推荐分页： SELECT * FROM table WHERE id >= 23423 LIMIT 11;

    10+1 （每页 10 条）

    SELECT * FROM table WHERE id >= 23434 LIMIT 11; 分页方式二： SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 10000,1) LIMIT 10; 分页方式三： SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000, 10) USING (id); 分页方式四：

    程序取 ID

    SELECT id FROM table LIMIT 10000, 10; SELECT * FROM table WHERE id IN (123, 456 … ); 可能需按场景分析并重组索引 示例：

  1. 用 UNION ALL 而非 UNION 若无需对结果进行去重，则用 UNION ALL；UNION 有去重开销 举例： SELECT * FROM detail20091128 UNION ALL SELECT * FROM detail20110427 UNION ALL SELECT * FROM detail20110426 UNION ALL SELECT * FROM detail20110425 UNION ALL SELECT * FROM detail20110424 UNION ALL SELECT * FROM detail20110423;
  2. 分解连接保证高并发 高并发 DB 不建议进行两个表以上的 JOIN 适当分解连接保证高并发
    • 可缓存大量早期数据
    • 使用了多个 MyISAM 表
    • 对大表的小 ID 使用 IN()
    • 连接引用同一个表多次 举例： SELECT * FROM tag JOIN tag_post ON tag_post.tag.id = tag.id JOIN post ON tag_post.post_id = WHERE tag.tag = ‘二手玩具’; → SELECT * FROM tag WHERE tag = ‘二手玩具’; SELECT * FROM tag_post WHERE tag_id = 1321; SELECT * FROM post WHERE post.id IN (123, 456, 314, 141);
  3. GROUP BY 去除排序 GROUP BY 实现
    • 分组
    • 自动排序 无需排序：ORDER BY NULL 特定排序：GROUP BY DESC/ASC 举例
  4. 同数据类型的列值比较 原则：数字对数字，字符对字符 数值列与字符类型比较
    • 同时转换为双精度
    • 进行比对 字符列与数值类型比较
    • 字符列整列转数值
    • 不会使用索引查询 举例： remark varchar(50) NOT NULL COMMENT ‘备注，默认为空’,

SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark = 115127; → SELECT id, gift_code FROM pool_gift WHERE deal_id = 640 AND remark = ‘115127’;

  1. Load data 导数据 批量数据快速导入
    • 成批装载比单行装载更快，不需要每次刷新缓存
    • 无索引时装载比索引装载更快
    • Insert values, values, values 减少索引刷新
    • Load data 比 Insert 快约 20 倍 尽量不用 INSERT … SELECT
    • 延迟
    • 同步出错
  2. 打散大批量更新 大批量更新凌晨操作，避开高峰 凌晨不限制 白天上限默认为 100 条/秒（特殊再议） 举例： UPDATE post SET tag = 1 WHERE id in (1, 2, 3); SLEEP 0.01; UPDATE post SET tag = 1 WHERE id in (4, 5, 6); SLEEP 0.01; …
  3. Know Every SQL!
  • EXPLAIN
  • SHOW PROFILE
  • SHOW SLOW LOG
  • SHOW PROCESSLIST
  • MySQL dumpslow
  • SHOW QUERY_RESPONSE_TIME(Percona) 约定
    1. 隔离线上线下 构建数据库的生态环境
  • 开发无线上库操作权限 原则：线上连线上，线下连线下
  • 实时数据用 real 库
  • 模拟环境用 sim 库
  • 测试用 qa 库
  • 开发用 dev 库 案例：
  1. 禁止未经 DBA 确认的子查询 MySQL 子查询
    • 大部分情况优化较差
    • 特别 WHERE 中使用 IN id 的子查询
    • 一般可用 JOIN 改写 举例： ✘ select * from table1 where id in (select id from table2); ✘ insert into table1 (select * from table2); // 可能导致复制异常
  2. 永远不在程序端显式加锁

永远不在程序端对数据库显式加锁

  • 外部锁对数据库不可控
  • 高并发时是灾难
  • 极难调试和排查 并发扣款等一致性问题
  • 采用事物
  • 相对值修改
  • Commit 前二次校验冲突
    1. 统一字符集为 UTF8

字符集：

  • MySQL 4.1 以前只有 latin1
  • 为多语言支持增加了多字符集
  • 也带来了 N 多问题
  • 保持简单 统一字符集：UTF8 校对规则：utf8_general_ci 乱码：SET NAMES UTF8
    1. 统一命名规范 库表等名称同意用小写
  • Linux VS Windows
  • MySQL 库表大小写敏感
  • 字段名大小写不敏感 索引命名默认为 “idx_字段名” 库名用缩写，尽量在 2~7 个字母
  • DataSharing ==> ds 注意避免使用保留字命名 举例： ✘ select * from return; √ select * from return;

MySQL数据库开发的三十六条军规_石展 https://github.com/calvinlau/calvinwiki/wiki/MySQL https://my.oschina.net/BearCatYN/blog/476748 http://www.educity.cn/wenda/402373.html