赶集网Mysql36条军规 · PHP开发笔记&解决方案

# 赶集网Mysql36条军规 # 1.核心军规 ### 1.1尽量不在数据库做运算 * 尽量不在数据库做运算 * 复杂运算到程序端CPU * 举例: md5() / Order by Rand()不要出现 ### 1.2控制单表数据量 * 纯INT不超1000W * 含CHAR不超500W * 建议单库不超过300-400个表 ### 1.3保持表身段苗条 * 表字段数少而精 * 单表字段数上限控制在20~50个 ### 1.4平衡范式与冗余 * 没有绝对的对与错 * 适当时牺牲范式、加入冗余 * 但会增加代码复杂度 ### 1.5拒绝3B * 大SQL (BIG SQL) * 大事务 (BIG Transaction) * 大批量 (BIG Batch) # 2.字段类军规 ### 2.1用好数值字段类型 * TINYINT(1Byte) * SMALLINT(2B) * MEDIUMINT(3B) * INT(4B)、BIGINT(8B) * DECIMAL(M,D) ### 2.2将字符转化为数字 * 数字型VS字符串型索引，更高效，查询更快 * 举例：用无符号INT存储IP，而非CHAR(15) ### 2.3优先使用ENUM或SET * ENUM占用1字节，转为数值运算 * SET视节点定，最多占用8字节 * 比较时需要加‘ 单引号(即使是数值 ### 2.4避免使用NULL字段 * 很难进行查询优化 ### 2.5少用并拆分TEXT/BLOB * TEXT类型处理性能远低亍VARCHAR * 若必须使用则拆分到单独的表 ### 2.6不在数据库里存图片 # 3.索引类军规 ### 3.1谨慎合理添加索引 * 索引不是越多越好 ### 3.2字符字段必须建前缀索引 ### 3.3不在索引列做运算 * 无法使用索引 * 导致全表扫描 ### 3.4自增列或全局ID做INNODB主键 ### 3.5尽量不用外键 * 高并发时容易死锁 * 由程序保证约束 # 4.SQL类军规 ### 4.1SQL语句尽可能简单 * 拒绝大SQL，拆解成多条简单SQL * 减少锁表时间，特别是MyISAM ### 4.2保持事务(连接)短小 * 事务/连接使用原则：即开即用，用完即关 * 不破坏一致性前提下，使用多个短事务代替长事务 ### 4.3尽可能避免使用SP/TRIG/FUNC * 尽可能少用触发器 * 尽可能少用存储过程 * 减用使用MySQL凼数对结果进行处理 * 由客户端程序负责 ### 4.4尽量不用 SELECT \* * 更安全的设计：减少表变化带来的影响 * 特别是有TEXT/BLOB时 ### 4.5改写OR为IN() * 注意控制IN的个数，建议n小亍200 ### 4.6改写OR为UNION * 减少对不同字段进行 "or" 查询 ### 4.7避免负向查询和% 前缀模糊查询 * 避免负向查询【NOT、!=、、NOT EXISTS、NOT IN、 NOT LIKE】等 * 避免 % 前缀模糊查询，使用不了索引，导致全表扫描 ### 4.8减少COUNT(\*) * COUNT(\*)的资源开销大，尽量少用 * 尽量用单独统计表，定期重算 ### 4.9LIMIT高效分页 * 推荐分页一： Select \* from table WHERE id>=23423 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…) ; ### 4.10用UNION ALL 而非 UNION * UNION有去重开销 * 若无需对结果进行去重，则用UNION ALL ### 4.11分解联接保证高并发 * 高并发DB不建议进行两个表以上的JOIN ### 4.12GROUP BY 去除排序 * GROUP BY 实现分组自动排序 * 无需排序：Order by NULL * 特定排序：Group by DESC/ASC ### 4.13同数据类型的列值比较 * 原则：数字对数字，字符对字符 ### 4.14Load data 导数据 * 批量数据快导入：Load data比insert快约20倍 * 尽量丌用 INSERT ... SELECT ### 4.15打散大批量更新 * 大批量更新凌晨操作，避开高峰 * 白天上限默认为100条/秒（特殊再议） # 5.约定类军规 ### 5.1隔离线上线下 * 开发无线上库操作权限 * 原则：线上连线上，线下连线下 ### 5.2禁止未经DBA确认的子查询 ### 5.3永远不在程序端显式加锁永远不在程序端对数据库显式加锁高幵发时是灾难，极难调试和排查并发扣款等一致性问题，采用事务，相对值修改，Commit前二次较验冲突 ### 5.4统一字符集为UTF8 * 校对规则：utf8\_general\_ci ### 5.5统一命名规范 * 库表等名称统一用小写 * 索引命名默认为“idx\_字段名” * 注意避免用保留字命名