T31Day3

一、表的约束

1. 表、字段命名

 1. 必须使用小写字母或数字
 2. 禁止数字开头
 3. 禁止两个下划线中间只出现数字（禁用day_3_day)
 4. 不使用复数名词
 5. 禁用保留字
 6. 是与否概念的字段，必须使用is_xxx的方式命名，字段类型使用tinyint(1),字段值只能为1（是）或0（否）

2. 数据类型

 1. 小数类型为decimal。
 2. 货币数据使用最小货币单位，数据类型为bigint。
 3. 字符串长度几乎相等使用char，注意不足长度时，将在字符末尾自动补空格（好处：节约空间。）。
 4. varchar长度不要超过5000，超过时使用其他数据类型，如text等。

3. 表必备三字段： id（主键）、create_time（创建时间）、update_time（更新时间）

4. 建表推荐规约

 1. 表的命名：业务名称_表的作用；
 2. 库名与应用名称尽量一致；
 3. 如果修改字段含义或对字段表示的状态追加时，需要及时更新自动注释；
 4. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致；
 5. 单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表；

二、索引

索引的作用：提高查询效率

索引特性：持久性、有序性

索引的分类：

 

P.S. 覆盖索引（不可创建的索引,在查询中产生的一种索引）：当查询语句中的返回的字段只有主键和查询条件中的使用索引字段，则该查询会使用一种覆盖索引。

索引的数据结果：二叉查找树，其中平衡二叉查找树：左右两个子树的层级最多相差1（理想状态）

索引的数据结构：B-Tree、B+Tree

  B-Tree

B+Tree

 

 MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。
 InnoDB中非主键索引使用的B-Tree数据结果，主键索引使用的是B+Tree。

B+Tree的优点：

1) B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如 果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越 多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2) B+树的查询效率更加稳定 　　 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以 任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当。

扩展：

1. mysql读写数据页大小默认为16K

2. 查看查询语句中对索引等的使用情况： explain select xxxx

3. 数据结构更多内容的：Arts and Sciences - Computer Science | myUSF

索引命名规则：

主键：pk字段名

唯一：uk字段名

普通：idx_字段名

创建索引规约：

1. 有唯一特性的字段必须建立唯一索引

2. 在varchar字段上建索引时，必须指定索引长度

3. 组合索引：字段按区分度的高低程度，从左到右组合，左边为区分度最高的

使用误区：

1. 索引宁滥勿缺：认为一个查询就需要建一个索引（应该在基础作为的查询条件的字段建立索引）；

2. 吝啬索引创建：认为建索引占用存储空间（索引虽然会占用存储空间，但可以提高查询的速度）

3. 抵制唯一索引：担忧唯一索引造成数据操作的异常

三、SQL规约

有关索引：

 1.注意字段类型：防止因字段类型不同造成的隐式转换，导致索引失效；
 2.利用覆盖索引：避免回表；
 3.利用有序性：如果有order by 的场景；
 4.禁模糊：禁左模糊或全模糊，如果需要请走搜索引擎解决；

有关count：

 1.拒绝替代：不要用count（col)或count(常量)代替count(*)；
 2.计算不重复行数：count（distinct col)计数该列除NULL之外的不重复行数；
 3.当值全是NULL时，count(col) = 0,但sum(col) = NULL；

isnull判断是否为null

 1. null <> null -> null
 2. null == null -> null
 3. null <> 1 -> NULL

关于分页：

 1. 如果count=0，则直接返回；
 2. 优化超多分页场景：利用延迟关联或者子查询优化超多分页场景

避坑指南：

 1.不得使用外键与级联，一切外键概念必须在应用层解决；
 2.禁止使用存储过程，不推荐使用函数，存储过程和函数难以调试和扩展，更没有移植性；
 3.数据订正时，要先select，避免出现误删，确认无误才能执行更新；
 4.只要涉及多个表，都需要在列名前加表的别名(或表名)进行限定；
 5.SQL语句中表的别名前加as，并且以t1、t2。。。的顺序依次命名；
 6.in后边的集合元素数量，控制在1000个之内；
 7.不推荐使用视图；
 8.使用join最好不超过3个表，同时结果集小的表在前，结果集大的在后面。