建表规范

1. 必须包含整数型自增主键
2. 对于 where 查询的字段增加索引
3. 联合索引是最左性原理，例如联合索引是（a,b,c），在查询时 a,ab,abc的精确匹配，都会使用索引;查询 b,ac,c 都不会使用该索引
4. 建议对创建时间字段建立索引，一般命名创建时间为ctime，修改时间为mtime

创建表的例子，实现了ctime为初次创建时间，mtime为每次更新时间

````

CREATE TABLE `test` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`score` int(1) NOT NULL,

`money` decimal(10,2) NOT NULL DEFAULT '0.00',

`balance` decimal(10,3) NOT NULL DEFAULT '0.000',

`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

`````

字段类型

1. int(10) 和 int(1) 只是MySQL中显示显示宽度值，不影响真正存储的数值范围，也就是说int(10)和int(1)的范围都是-2147483648 ~ 2147483647
2. decimal 类型用于精确位数的精度计算，因为是字符串存储，decimal(m,d) 其中m表示显示宽带就有实际作用了,可以存储m位长度的数值，d表示多少位小数。

当 decimal(10,0) 则表示可以存储10位整数同时小数位数为0，可以理解为 精确到元。deciaml(3,2) 表示可以存储3位数值，小数精确到2位，也就是可以精确到分；

同时3位数值是包含小数位数，所以 decimal(3,2) 的数值范围是-9.99 ~ 9.99

3. float、double 是不精确位数的浮点类型，不能用于 金额/比例计算。
4. 合理利用 unsigned 类型，int 数值可以简单记忆为正负21亿，unsigned int 则为 0-42亿。
5. timestamp 占用4个字节，范围是1970-01-01 00:00:00′到2037年，datetime占用8个字，节范围是 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

存储金额使用什么类型？

1. 推荐使用 decimal 类型，精确指定小数位数 decimal(10,2) 应该适用于绝大多数场景，即-9999千万~9999千万的范围数值，同时精确到分。

如果需要比分更小的精度可以使用 decimal(11,3)

2. 有些情况下，也可以考虑采用整数存储分，这样的问题就是当程序在显示/处理时都需要进行一次元单位和分单位的转化，很容易出现bug。
3. 注意事项：

当MySQL中采用decimal类型时，对应的编程语言也应该尽量使用decimal类型进行运算。

以上表中执行 `update test set balance='1.005'` ，则 balance 值最终为 `1.01` ，也就是MySQL会进行正确的四舍五入。

考虑一些场景下的精度问题，比如计算比例 1/3=0.3333... 也就是 33.33% ，但是三个元素 33.33%+33.33%+33.33%=99.99% 。

这种情况，可以考虑 子元素中采用三位小数即 33.333%，在求和中采用两位小数精度来实现最终100.00%数值显示目标，合理利用精确位数的四舍五入。

4. 不正确的使用：

   decimal(10,0) 即不要求精确小数位，则应该应该使用int(10)，而不是字符串存储的decimal.

常用字段类型规范

| 字段名 | 类型 | 说明|

| -------- | -------- |-------- |

| ad_id | char(32) | 广告id，定长，建议索引|

| device_id | varchar(50) | device_id，一般在30个字符左右|

| ctime | timestamp/datetime | 记录创建时间，建议索引|

| mtime | timestamp/datetime | 记录修改时间|

| app_id | varchar(32) | 应用id|

|package_name|varchar(200) | 应用包名|

| 你的字段 | 写到这里 | 谢谢|

使用索引

1. 不使用索引会怎么样？当记录超过10万行，使用where查询特定字段内容时速度会慢到可感知。如果是前台应用，会出现超时,慢查询堆积等。
2. 合理利用自增主键id，作为关联id，查询尽量指定主键id查询，效率最高。
3. 优先考虑unique唯一索引，效率最高。
4. 通过explain select .... 来查看索引使用效果。
5. 表上索引多，会导致 insert 写入变慢，update 操作会引发索引更新。
6. 提高查询效率的办法还有 大表拆小表，分表，分库等，别忘记了恰当的使用redis等缓存。
7. 当对varchar(50)这样的字段建立索引时，可以考虑只对前20个字符进行索引，这样可以提高索引查询效率，同时节约内存。

其他建议

1. 建立数据库索引，一定不要和字段名一致 。推荐使用 `i_字段1_字段2` 索引命名方式。

2. 删除索引/删除字段前，运维需要对表进行备份。

3. 需要对针对的SQL进行explain测试，新增索引后，确认索引生效。

   • 需要看 key 值是否命中设置的索引值
   • 如果SQL中where 包含了4个字段，则可以建立4个字段的联合索引
   • 需要看扫描表行数 rows 值是否大幅减小
   • 索引字段上使用函数，会导致索引无效

4. 索引是最左性原理，例如联合索引是（a,b,c），在查询时 a,ab,abc的精确匹配，都会使用索引;查询 b,ac,c 都不会使用该索引

5. 对外服务接口SQL执行效率优先

   • 避免使用视图
   • 避免使用外键
   • 避免使用ORM事务

6. 设置连接超时时间，防止满连接过多占满连接

7. 频繁更新的表，不要加太多索引

8. 如果一张表超过2000万行，加索引很可能会失败，解决方案是导出老数据到bak表，原表删除数据，再加索引。

9. 关注自己系统的慢查询

10. log型的数据库表，自增id列应该采用bigint，防止达到int32的上限，仅为了节省空间而采用int32带来的收益微乎期微，不值得为止冒险