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

一、核心军规(5)

1.1 尽量不在数据库做运算

1.2 控制单表数据量

1.3 保持表身段苗条

1.4 平衡范式不冗余

1.5 拒绝 3B

1.6 核心军规小结

二、字段类军规(6)

2.1 用好数值字段类型

2.2 将字符转化为数字

2.3 优先使用 ENUM 或 SET

2.4 避免使用 NULL 字段

2.5 少用并拆分 TEXT/BLOB

CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT, data text NOT NULL,
‏PRIMARY KEY id
) ENGINE=InnoDB;

2.6 不在数据库里存图片

2.7 字段类军规小结

三、索引类军规(5)

3.1 谨慎合理添加索引

3.2 字符字段必须建前缀索引

(
`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音', KEY `idx_pinyin` (`pinyin`(8)),
) ENGINE=InnoDB

3.3 不在索引列做运算

BAD: SELECT * from table WHERE to_days(current_date) – to_days(date_col) <= 10
GOOD: SELECT * from table WHERE date_col >= DATE_SUB('2011-10- 22',INTERVAL 10 DAY);

3.4 自增列或全局 ID 做 INNODB 主键

3.5 尽量不用外键

3.6 索引类军规小结

四、SQL 类军规(15)

4.1 SQL 语句尽可能简单

4.2 保持事务(连接)短小

4.3 尽可能避免使用 SP/TRIG/FUNC

4.4 尽量不用 SELECT

SELECT * FROM tag WHERE id = 999184;
SELECT keyword FROM tag WHERE id = 999184;

4.5 改写 OR 为 IN()

SELECT * from opp WHERE phone='12347856' or phone='42242233' \G;
SELECT * from opp WHERE phone in ('12347856' , '42242233');

4.6 改写 OR 为 UNION

SELECT * from opp WHERE phone='010-88886666' or cellPhone='13800138000';
SELECT * from opp WHERE phone='010-88886666' union SELECT * from opp WHERE cellPhone='13800138000';

4.7 避免负向查询和% 前缀模糊查询

SELECT * from post WHERE title like '北京%'; -- 298 rows in set (0.01 sec)
SELECT * from post WHERE title like '%北京%'; -- 572 rows in set (3.27 sec)

4.8 COUNT(*)的几个例子

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '公司的id', `sale_id` int(10) unsigned DEFAULT NULL,

4.9 减少 COUNT(*)

4.10 LIMIT 高效分页

MySQL> SELECT sql_no_cache * from post limit 10,10; 10 row in set (0.01 sec)
MySQL> SELECT sql_no_cache * from post limit 20000,10; 10 row in set (0.13 sec)
MySQL> SELECT sql_no_cache * from post limit 80000,10; 10 rows in set (0.58 sec)
MySQL> SELECT sql_no_cache id from post limit 80000,10; 10 rows in set (0.02 sec)
MySQL> SELECT sql_no_cache * from post WHERE id>=323423 limit 10; 10 rows in set (0.01 sec)
MySQL> SELECT * from post WHERE id >= ( SELECT sql_no_cache id from post limit 80000,1 ) limit 10; 10 rows in set (0.02 sec)

4.11 用 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;

4.12 分解联接保证高并发

MySQL> SELECT * from tag JOIN post on tag_post.post_id=post.id WHERE tag.tag='二手玩具';

MySQL> SELECT * from tag WHERE tag='二手玩具';
MySQL> SELECT * from tag_post WHERE tag_id=1321;
MySQL> SELECT * from post WHERE post.id in (123,456,314,141);

4.13 GROUP BY 去除排序

MySQL> SELECT phone,count(*) from post group by phone limit 1 ; 1 row in set (2.19 sec)
MySQL> SELECT phone,count(*) from post group by phone order by null limit 1; 1 row in set (2.02 sec)

4.14 同数据类型的列值比较

字段:`remark` varchar(50) NOT NULL COMMENT '备注, 默认为空',

MySQL>SELECT `id`, `gift_code` FROM gift WHERE `deal_id` = 640 AND remark=115127; 1 row in set (0.14 sec)
MySQL>SELECT `id`, `gift_code` FROM pool_gift WHERE `deal_id` = 640 AND remark='115127'; 1 row in set (0.005 sec)

4.15 Load data 导数据

4.16 打散大批量更新

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;
......

4.17 Know Every SQL

4.18 SQL 类军规小结

五、约定类军规(5)

5.1 隔离线上线下

5.2 禁止未经 DBA 确认的子查询

SELECT * from table1 where id id from table2) in (SELECT insert into table1 (SELECT * from table2); -- 可能导致复制异常

5.3 永远不在程序端显式加锁

5.4 统一字符集为 UTF8

5.5 统一命名规范

5.6 注意避免用保留字命名

SELECT * from return;
SELECT * from `return`;
MySQL系统关键字 * ADD * ALL * ALTER GOTO * GRANT * GROUP * PURGE * RAID0 * RANGE * ANALYZE * AND * AS HAVING * HIGH_PRIORIT Y * HOUR_MICROSEC OND * READ * READS * REAL * ASC * ASENSITIVE * BEFORE HOUR_MINUTE * HOUR_SECON D * IF * REFERENCES * REGEXP * RELEASE * BETWEEN * BIGINT * BINARY IGNORE * IN * INDEX * RENAME * REPEAT * REPLACE * BLOB * BOTH * BY INFILE * INNER * INOUT * REQUIRE * RESTRICT * RETURN * CALL * CASCADE * CASE INSENSITIVE * INSERT * INT * REVOKE * RIGHT * RLIKE * CHANGE * CHAR * CHARACTER INT1 * INT2 * INT3 * SCHEMA * SCHEMAS * SECOND_MICROSEC OND * CHECK * COLLATE * COLUMN INT4 * INT8 * INTEGER * SELECT * SENSITIVE * SEPARATOR * CONDITION * CONNECTION * CONSTRAINT INTERVAL * INTO * IS * SET * SHOW * SMALLINT * CONTINUE * CONVERT * CREATE ITERATE * JOIN * KEY * SPATIAL * SPECIFIC * SQL * CROSS * CURRENT_DA TE * CURRENT_TIM KEYS E * KILL * LABEL * SQLEXCEPTION * SQLSTATE * SQLWARNING * CURRENT_TIMESTA MP * CURRENT_US ER * CURSOR LEADING * LEAVE * LEFT * SQL_BIG_RESUL T * SQL_CALC_FOUND_R OWS * SQL_SMALL_RESULT * DATABASE * DATABASES * DAY_HOUR LIKE * LIMIT * LINEAR * SSL * STARTING * STRAIGHT_JOIN * DAY_MICROSECON D * DAY_MINUTE * DAY_SECOND LINES * LOAD * LOCALTIME * TABLE * TERMINATED * THEN * DEC * DECIMAL * DECLARE LOCALTIMESTAMP * LOCK * LONG * TINYBLOB * TINYINT * TINYTEXT * DEFAULT * DELAYED * DELETE LONGBLOB * LONGTEXT * LOOP * TO * TRAILING * TRIGGER * DESC * DESCRIBE * DETERMINISTI LOW_PRIORITY C * MATCH * MEDIUMBLOB * TRUE * UNDO * UNION * DISTINCT * DISTINCTROW * DIV MEDIUMINT * MEDIUMTEXT * MIDDLEINT * UNIQUE * UNLOCK * UNSIGNED * DOUBLE * DROP * DUAL * MINUTE_MICROSECO ND * MINUTE_SECO ND * MOD * UPDATE * USAGE * USE * EACH * ELSE * ELSEIF MODIFIES * NATURAL * NOT * USING * UTC_DATE * UTC_TIME * ENCLOSED * ESCAPED * EXISTS * NO_WRITE_TO_BINL OG * NULL * NUMERIC * UTC_TIMESTAM P * VALUES * VARBINARY * EXIT * EXPLAIN * FALSE ON * OPTIMIZE * OPTION * VARCHAR * VARCHARACTER * VARYING * FETCH * FLOAT * FLOAT4 OPTIONALLY * OR * ORDER * WHEN * WHERE * WHILE * FLOAT8 * FOR * FORCE OUT * OUTER * OUTFILE * WITH * WRITE * X509 * FOREIGN * FROM * FULLTEXT PRECISION * PRIMARY * PROCEDURE * XOR * YEAR_MONTH * ZEROFILL

5.7 约定类军规小结

六、原文链接