三、MySql相关设计

建表强制规约

表、字段命名

1. 必须使用小写字母或数字
2. 禁止出现数字开头
3. 禁止两个下划线中间只出现数字
4. 不使用复数名词
5. 禁用保留字
6. 表示是否，必须使用is_xxx（is_accept）

数据类型

1. 小数类型为decimal
2. 货币数据使用最小货币单位，数据类型为bigint
3. 字符串几乎相等使用char
4. varchar长度不要超过5000

表必备三字段

1. id
2. create_time
3. update_time

建表推荐规约

1. 表明最好遵循“业务名称_表的作用”
2. 苦命与应用名称一致
3. 修改字段含义时，需及时更新字段注释
4. 字段允许适当冗余（品类）
5. 单表行数超过500w或容量超过2G才考虑分库分表

索引

索引的特性

1. 持久性：存储空间对比数据大概1:1
2. 有序性：可提高查询速度

索引的分类

1. 存储形式：
   聚簇索引：主键
   非聚簇索引：其他
2. 数据约束
   主键索引
   唯一索引（组合唯一也需要创建）
   非唯一索引
3. 索引列的数量
   单列索引
   组合索引
4. innoDB可以创建的索引
   主键索引
   唯一索引
   普通索引
   不可以创建的：覆盖索引，全文索引

索引名称规约

1. 主键：pk_字段名
2. 唯一：uk_字段名
3. 普通：idx_字段名

创建索引规约

1. 有唯一索引的字段必须建成唯一索引
2. 区分度最高的放在组合索引左边

SQL规约

1. 隐式类型转换可能导致索引失效
2. 利用覆盖索引
3. 利用有序性（order by）
4. 禁止做模糊查询
5. 使用count(*)查询数量
6. 计算不重复行数count(distinct colum)
7. 当全是null时count返回0，sum返回null
8. 当limit从较大页数开始（深翻页）速度会比较慢，利用延迟查询或子查询优化

避坑指南

1. 在应用中处理外键
2. 禁止使用存储过程
3. in中最好少于1000个

ORM映射规约

1. 不要使用*作为查询字段
2. pojo类的布尔属性不能加is，而数据库字段必须加“is_”
3. 查询结果使用resultMap映射
4. 谨慎使用${}
5. 不要使用MyBatis自带的queryForList方法，它会把所有数据都查出来
6. 更新表时别忘更新更新update_time
7. 不要写大而全的更新接口

Exlain妙用

1. id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
2. select_type: SELECT 查询的类型.
3. table: 查询的是哪个表
4. partitions: 匹配的分区
5. type: 显示的是单位查询的连接类型或者理解为访问类型
6. possible_keys: 此次查询中可能选用的索引
7. key: 此次查询中确切使用到的索引.
8. ref: 哪个字段或常数与 key 一起被使用
9. rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
10. filtered: 表示此查询条件所过滤的数据的百分比

重点参数：

type

性能从好到差：

1. system：表中只有一行数据或者是空表，等于系统表
2. const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
5. fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
6. ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
7. unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
8. index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重
9. range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中
10. index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range
11. index：select结果列中使用到了索引，type会显示为index。全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
12. ALL：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

extra

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种

1. Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。需要优化sql。
2. Using temporary：使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. using index：查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。
4. using where：表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引
5. impossible where：where子句的值 总是false ，不能用来获取任何元组