阿里面试宝典（八）：数据库范式、开发规范和索引

问题导读：
1、数据库范式是怎样的？
2、数据库有哪些开发规范？
3、聚集索引和非聚集索引的区别？

上一篇：阿里面试宝典（七）：大数据与高并发（下）



数据库

一、数据库范式

1NF(第一范式)

第一范式是指数据库表中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。第一范式的模式要求属性值不可再分裂成更小部分，即属性项不能是属性组合或是由一组属性构成。

简而言之，第一范式就是无重复的列。例如，由“职工号”“姓名”“电话号码”组成的表(一个人可能有一部办公电话和一部移动电话)，这时将其规范化为1NF可以将电话号码分为“办公电话”和“移动电话”两个属性，即职工(职工号，姓名，办公电话，移动电话)。

2NF(第二范式)

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

如果关系模型R为第一范式，并且R中的每一个非主属性完全函数依赖于R的某个候选键，则称R为第二范式模式(如果A是关系模式R的候选键的一个属性，则称A是R的主属性，否则称A是R的非主属性)。

例如，在选课关系表(学号，课程号，成绩，学分)，关键字为组合关键字(学号，课程号)，但由于非主属性学分仅依赖于课程号，对关键字(学号，课程号)只是部分依赖，而不是完全依赖，因此此种方式会导致数据冗余以及更新异常等问题，解决办法是将其分为两个关系模式：学生表(学号，课程号，分数)和课程表(课程号，学分)，新关系通过学生表中的外关键字课程号联系，在需要时进行连接。

3NF(第三范式)

如果关系模型R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R是第三范式的模式。

以学生表(学号，姓名，课程号，成绩)为例，其中学生姓名无重名，所以该表有两个候选码(学号，课程号)和(姓
名，课程号)，故存在函数依赖：学号——>姓名，(学号，课程号)——>成绩，唯一的非主属性成绩对码不存在部分依赖，也不存在传递依赖，所以属性属于第三范式。

二、数据库开发规范

基础规范

（1）必须使用InnoDB存储引擎解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

（2）必须使用UTF8字符集解读：万国码，无需转码，无乱码风险，节省空间

（3）数据表、数据字段必须加入中文注释解读：N年后谁知道这个r1,r2,r3字段是干嘛的

（4）禁止使用存储过程、视图、触发器、Event解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

（5）禁止存储大文件或者大照片解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

命名规范

（6）只允许使用内网域名，而不是ip连接数据库

（7）线上环境、开发环境、测试环境数据库内网域名遵循命名规范业务名称：xxx线上环境：my10000m.mysql.jddb.com开发环境：yf10000m.mysql.jddb.com测试环境：test10000m.mysql.jddb.com从库在名称后加-s标识，备库在名称后加-ss标识线上从库：my10000sa.mysql.jddb.com

（8）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用

（9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

（10）单实例表数目必须小于500

（11）单表列数目必须小于30

（12）表必须有主键，例如自增主键解读：

a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用

b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率

c） 无主键的表删除，在row模式的主从架构，会导致备库夯住

（13）禁止使用外键，如果有外键完整性约束，需要应用程序控制解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

字段设计规范

（14）必须把字段定义为NOT NULL并且提供默认值解读：

a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识

d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

（15）禁止使用TEXT、BLOB类型解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

（16）禁止使用小数存储货币解读，小数容易导致钱对不上

（17）必须使用varchar(20)存储手机号解读：a）涉及到区号或者国家代号，可能出现+-() b）手机号会去做数学运算么？


c）varchar可以支持模糊查询，例如：like“138%”

（18）禁止使用ENUM，可使用TINYINT代替解读：a）增加新的ENUM值要做DDL操作b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？

（19）单表索引建议控制在5个以内

（20）单索引字段数不允许超过5个解读：字段超过5个时，实际已经起不到有效过滤数据的作用了

（21）禁止在更新十分频繁、区分度不高的属性上建立索引解读：a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

（22）建立组合索引，必须把区分度高的字段放在前面解读：能够更加有效的过滤数据

（23）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性解读：** a）读取不需要的列会增加CPU、IO、NET消耗**


** b）不能有效的利用覆盖索引**

** c）使用SELECT *容易在增加或者删除字段后出现程序BUG**

（24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性解读：容易在增加或者删除字段后出现程序BUG

（25）禁止使用属性隐式转换解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）

（26）禁止在WHERE条件的属性上使用函数或者表达式解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-01-15' 会导致全表扫描正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-01-15 00:00:00')

（27）禁止负向查询，以及%开头的模糊查询解读：

a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描b）%开头的模糊查询，会导致全表扫描
（28）禁止使用JOIN查询，禁止大表使用子查询解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

（29）禁止使用OR条件，必须改为IN查询解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

（30）应用程序必须捕获SQL异常，并有相应处理

（31）同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。

总结

大数据量高并发的互联网业务，极大影响数据库性能的都不让用，不让用哟。

三、数据库索引

Hash索引

B+索引

索引的作用是“排列好次序，使得查询时可以快速找到”。

唯一索引

唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。如学生
表中的’学号‘

非唯一索引

非唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中可以重复，不要求
唯一。如学生表中的‘成绩’

主键索引

主键索引（主索引）是唯一索引的特定类型。表中创建主键时自动创建的索引 。一个表只能建立一个主索引。

聚集索引（聚簇索引）

聚集索引（聚簇索引），表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引。

扩展：聚集索引和非聚集索引的区别？分别在什么情况下使用？

聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。

聚集索引的表中记录的物理顺序与索引的排列顺序一致。

优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧跟其后。

缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。

建议使用聚集索引的场合为：

• 某列包含了小数目的不同值。
• 排序和范围查找。
  

非聚集索引的记录的物理顺序和索引的顺序不一致。

其他方面的区别：

1.聚集索引和非聚集索引都采用了 B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。

2.非聚集索引添加记录时，不会引起数据顺序的重组。看上去聚簇索引的效率明显要低于非聚簇索引， 因为每次使用辅助索引检索都要经过两次 B+树查找， 这不是多此一举吗？ 聚簇索引的优势在哪？

• 由于行数据和叶子节点存储在一起， 这样主键和行数据是一起被载入内存的， 找到叶子节点就可以立刻将行数据返回了， 如果按照主键 Id 来组织数据， 获得数据更快。
• 辅助索引使用主键作为"指针", 而不是使用地址值作为指针的好处是， 减少了当出现行移动或者数据页分裂时,辅助索引的维护工作， InnoDB 在移动行时无须更新辅助索引中的这个"指针"。 也就是说行的位置会随着数据库里数据的修改而发生变化， 使用聚簇索引就可以保证不管这个主键 B+树的节点如何变化， 辅助索引树都不受影响。
  

建议使用非聚集索引的场合为：

• 此列包含了大数目的不同值；
• 频繁更新的列
  

索引实现机制

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：




这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：



同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。



上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：




这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引建立原则

（id，name) where id=1 and name='xxx'

1. .最左前缀匹配原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，范围查询
会导致组合索引半生效。比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，c 可
以用到索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。where范围查询要放在最后 （这不绝对，但可以利用一部分索引）。
2. 特别注意：and 之间的部分可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql
的查询优化器会帮你优化成索引可以识别的形式。where 字句有 or 出现还是会遍历全表。
3. 尽量选择区分度高的字段作为索引,某字段的区分度的公式是 count(distinctcol)/count(*)，表示字段不重复的比例，比例越大，我们扫描的记录数越少，查找匹配的时候可以过滤更多的行， 唯一索引的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。
4. 不在索引列做运算或者使用函数。
5. 尽量扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索
引即可。
6. Where 子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该
创建索引。
7. like 模糊查询中，右模糊查询（321%）会使用索引，而%321 和%321%会放弃索引而使用全局扫描。

最新经典文章，欢迎关注公众号