字段设计规范

1、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。

浮点数的缺点是会引起精度问题,请看下面一个例子:

mysql> CREATE TABLE table1 (f1 float(10,2),f2 decimal(10,2));
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO table1 VALUES (999998.02, 999998.02);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t3;
+-----------+-----------+
| f1        | f2        |
+-----------+-----------+
| 999998.00 | 999998.02 |
+-----------+-----------+
1 row in set (0.00 sec)

2、字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量。

选择字段的一般原则是保小不保大,能用占用字节少的字段就不用大字段。比如主键,强烈建议用int整型,不用uuid,为什么?省空间啊。空间是什么?空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁慢太明显了。涉及几个表做join时,效果就更明显了。更小的字段类型占用的内存就更少,占用的磁盘空间和磁盘I/O也会更少,而且还会占用更少的带宽。

有不少开发人员在设计表字段时,只要是针对数值类型的全部用int,但这不一定合适,就比如用户的年龄,一般来说,年龄大都在1~100岁之间,长度只有3,那么用int就不适合了,可以用tinyint代替。又比如用户在线状态,0表示离线、1表示在线、2表示离开、3表示忙碌、4表示隐身等,其实类似这样的情况,用int都是没有必要的,浪费空间,采用tinyint完全可以满足需要,int占用的是4字节,而tinyint才占用1个字节。

int整型有符号(signed)最大值是2147483647,而无符号(unsigned)最大值是4294967295,如果你的需求没有存储负数,那么建议改成无符号(unsigned),可以增加int存储范围。

int(10)和int(1)没有什么区别,10和1仅是宽度而已,在设置了zerofill扩展属性的时候有用,例:

mysql>CREATE TABLE table1(id int(10) zerofill,id2 int(1));
Query OK, 0 rows affected (0.13 sec)
mysql>INSERT INTO table1 VALUES(1,1);
Query OK, 1 row affected (0.04 sec)
mysql>INSERT INTO table1 VALUES(1000000000,1000000000);
Query OK, 1 row affected (0.05 sec)
mysql>SELECT * FROM table1;
+------------+------------+
| id         | id2        |
+------------+------------+
| 0000000001 |          1 |
| 1000000000 | 1000000000 |
+------------+------------+
2 rows in set (0.01 sec)

索引规范

1、不在索引列进行数学运算和函数运算。

因为无法使用索引,导致全表扫描。请看下面一个例子:

# 低效查询
SELECT * FROM t WHERE YEAR(add_time) >= 2016;
--->
# 高效查询
SELECT * FROM t WHERE add_time >= '2016-01-01';

2、不使用前导%的查询,如like ‘%xxx’。

因为无法使用索引,导致全表扫描。请看下面一个例子:

# 低效查询
SELECT * FROM t WHERE name LIKE '%de%';
--->
# 高效查询
SELECT * FROM t WHERE name LIKE 'de%';

3、不使用反向查询,如 NOT IN / NOT LIKE。

因为无法使用索引,导致全表扫描

4、避免冗余或重复索引。

联合索引IX_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c),那么索引 (a) 、(a,b) 就是多余的。

5、用IN来替换OR。

# 低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
--->
# 高效查询
SELECT * FROM t WHERE LOC_ID IN (10,20,30);

6、避免数据类型不一致。

# 低效查询
SELECT * FROM t WHERE id = '19';
--->
# 高效查询
SELECT * FROM t WHERE id = 19;

7、减少与数据库的交互次数。

# 低效操作
INSERT INTO t (id, name) VALUES(1,'Bea');
INSERT INTO t (id, name) VALUES(2,'Belle');
INSERT INTO t (id, name) VALUES(3,'Bernice');
--->
# 高效操作
INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');
# 其他高效操作
Update … where id in (1,2,3,4);
ALTER TABLE table1 ADD COLUMN col1, ADD COLUMN col2;

8、拒绝大SQL,拆分成小SQL。

# 低效操作
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
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 = 1234
SELECT * FROM post WHERE post_id in (123, 456, 567, 9098, 8904);

9、禁止使用ORDER BY RAND()

# 低效操作
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
--->
# 高效操作
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

参考地址:DBA的40条军规