数据库——MySQL

数据库MySQL

1、主键：一个实体集中只有一个主键，用于唯一标识，不能重复，不允许为空

2、外键：一个关系中的一个属性是另外一个关系中的主键，就是外键，用来和其它表建立联系，可以重复，可以为空，一个表中可以多个

3、不建议使用外键与级联，级联是强阻塞，外键影响数据库的插入速度

例如：学生表student中sid是主键，成绩表grade中sid是外键，更新学生表sid,会触发成绩表sid更新，即为级联更新。

4、不建议使用外键（并发量高的情况下）

（1）增加复杂性：delete、update操作要考虑外键约束

（2）增加额外工作：增删改操作后要检查外键约束字段的数据的一致性

（3）可能造成死锁问题

（4）分库分表不友好，分库分表下外键无法生效

5、数据库范式

（1）第一范式：所有关系型数据库的最基本要求

属性（字段）不可再分

属性（表中的字段）不能再分割，即该字段只能是一个值，不能是多值能分割成多个字段。

（2）第二范式：在第一范式基础上，消除非主属性对码的部分函数依赖

消除部分函数依赖

（3）第三范式：在第二范式基础上，消除非主属性对码的传递函数依赖

消除传递函数依赖

（4）依赖

• 函数依赖（functional dependency） ：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。

• 部分函数依赖（partial functional dependency） ：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；

• 完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；

• 传递函数依赖 ： 在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

6、drop、delete、truncate

     (1)drop: drop table 表名

         直接删除表，表结构、数据都删除

         释放表的占用空间

          DDL（数据定义语言）语句，操作即生效，不能回滚，不触发触发器

    (2)truncate: truncate table 表名

        只删除表中的数据，重新插入数据时，自增长id从1开始

        不会产生数据库日志 比delete快

         DDL（数据定义语言）语句，操作即生效，不能回滚，不触发触发器

    (3)delete: delete from 表名 where 列名=值 

        删除某行数据

        会产生数据库binlo日志，会消耗涉及，方便回滚恢复

         DML(数据库操作语言)语句，操作可以执行回滚，事务提交后才生效

7、DML和DDL区别

    （1）DML：数据库操作语言（Data Manipulation Language） 开发人员

             对数据库中表记录的操作，包括insert、update、delete、select

             对表内部数据的操作，不涉及表的定义、结构和对象

   （2）DDL：数据定义语言（Data Definition Language）数据库管理员DBA

            对数据库内部的对象进行创建、删除、修改的操作语言

8、数据库的设计

（1）需求分析 : 分析用户的需求，包括数据、功能和性能需求。

（2）概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。

（3）逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。

（4）物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。

（5）数据库实施 : 包括编程、测试和试运行

（6）数据库的运行和维护 : 系统的运行与数据库的日常维护

（7）基本设计规范

        1、所有表必须使用InnoDB存储引擎

        2、数据库和表的字符集统一使用UTF-8

             兼容性更好，能避免字符集转换产生的乱码，不同字符集进行比较需要进行转换，可能会造成索引失效

        3、添加注释

        4、控制单表数据量的大小，500万以内

        5、冷热数据分离，减少表的宽度

              减少磁盘 IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO）；

              更有效的利用缓存，避免读入无用的冷数据；

              经常一起使用的列放到一个表中（避免更多的关联操作）

（8）索引设计规范

        1、单表索引最好不超过5个

        2、禁止给表中每一列都建立单独的索引

        3、每个InnoDB表必须有个主键

              InnoDB是索引组织表，数据存储的逻辑顺序和索引的顺序是相同的，每个表可以有多个索引，但表的存储顺序只能有一种。

              InnoDB是按照主键索引的顺序来组织表的

              不使用更新频繁的类做主键，不适用多列主键

              不使用UUID、MD5、HASH，字符串列做主键，无法保证数据的顺序增长

              主键建议使用自增id值

        4、索引列的建议

              出现在select、update、delete语句的where从句中的列

              包含在order by、group by、distinct中的字段

              多表join的关联列

        5、索引：减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

        6、选择索引列的顺序

              最左匹配原则，区分度高的，经常查询的、字段小的

        7、对于频繁查询的优先考虑使用 覆盖索引

              覆盖索引：包含了所有查询字段（where、select、order by、group by）的索引

              覆盖索引的好处：

              避免二次查询，一般情况下非聚簇索引子叶中保存主键信息，需要二次查询，覆盖索引能够在非聚簇索引的键值中获取所有的数据，避免对主键的二次查询，减少io操作，提            升了查询效率

              随机IO变成顺序IO,加快查询效率

       8、索引失效

              子查询的结果集无法使用索引

              隐式转换    例： 字符转换成int id='100'

              前置%             %xxx

              not in | not exits

              or 可能会很少利用索引

              where 对列进行函数转换或计算

       9、InnoDB（事务性数据库引擎）和MyISAM引擎

          （1）是否支持行级锁

                   InnoDB:支持行级锁和表级锁，默认行级锁

                   MyISAM:仅支持表级锁

         （2）是否支持事务

                  InnoDB:支持事务，有提交事务commit和回滚事务rollback能力

                  MyISAM:不支持事务

         （3）是否支持外键

                 InnoDB:支持外键

                 MyISAM:不支持外键

         （4）是否支持数据库异常后的恢复

                 InnoDB:支持，依赖于redo log 能恢复到异常崩溃前的状态 

                    使用redo log(重做日志)保证事务的持久性（能否恢复）

                    使用undo log(回滚日志)保证事务的原子性（回滚）

                    通过锁机制、MVCC等来保证事务的隔离性（默认隔离级别：可重复读 repeatable-read）

                MyISAM：不支持

       （5）是否支持MVCC（多版本并发控制）

                InnoDB支持MVCC

                   MVCC实现原理：版本链、undo日志、ReadView

                                            不同版本的undo日志，通过版本链形成 undo日志链表

                                            可以有效减少加锁操作，提高性能

                MyISAM不支持

     10、数据库锁机制

         （1）表级锁：锁粒度最大，对整张表加锁，加锁快，不会出现死锁，并发度低，触发锁冲突概率最高

         （2）行级锁：锁粒度最小，对当前操作的行加锁，减少操作的冲突，并发度高，加锁慢，会出现死锁

    11、事务

         （1）是逻辑上的一组操作，要么都执行，要么都不执行,事务是最小的执行单位，不允许分割

         （2）数据库事务，可以保证多个对数据库的操作构成一个逻辑整体，要么都执行，要么都不执行。

         （3）事务的特性（ACID）

                   A：atomicity 原子性

                        原子性保证动作要么全部完成，要么全部不完成

                  C：consistency 一致性

                        一致性保证执行事务前后，数据保持一致

                  I：isolation 隔离性

                       隔离性保证并发访问数据库时，一个用户的事务不被其它事务所干扰，各并发事务之间数据库是独立的

                  D：durability 持久性

                      持久性保证事务被提交后，对数据库数据的改变是持久的，数据看发生故障也没有影响，能够恢复

       （4）事务的实现原理

                使用redo log(重做日志)保证事务的持久性（能否恢复）

                使用undo log(回滚日志)保证事务的原子性（回滚）

                通过锁机制、MVCC等来保证事务的隔离性（默认隔离级别：可重复读 repeatable-read）

       （5）并发事务的问题

             =1、脏读

                1号事务正在访问数据并对数据进行了修改，修改还没提交到数据库中，2号事务也访问了这个数据并使用，这个数据是还没提交的，2号事务读取到的数据便是“脏数据”，即               为脏读。

            =2、丢失修改

               1号事务读取数据时，2号事务也读取该数据，然后1号事务对数据进行修改，2号事务也进行修改，此时，1号事务修改后的结果丢失了，即为丢失修改

            =3、不可重复读

               1号事务内多次读取同一个数据，1号事务还没有结束，2号事务访问数据并进行修改，此时1号事务前后读取的数据可能不一样，即获取的结果不一样，即为不可重复读

            =4、幻读

               1号事务多次读取数据，2号事务插入数据，1号事务会发现多了一些原本不存在的记录，即为幻读

     （6）事务的隔离级别

             =1、read-uncommitted(读未提交)：最低的隔离级别

                     允许读取尚未提交的数据变更

                     可能导致脏读、不可重复读、幻读

            =2、read-commited(读已提交)

                   允许读取并发事务已经提交的数据

                   解决脏读，可能导致不可重复读、幻读

            =3、repeatable-read(可重复读)：MySQL默认隔离级别

                    对同一字段的多次读取结果是一致的

                    解决脏读、不可重复读，可能导致幻读

            =4、serializable(可序列化)：最高的隔离级别

                   完全服从ACID,所有事物依次逐个执行，事务间完全不干扰

                   解决脏读、不可重复读、幻读

12、varchar和char的区别

（1）char: 定长字段，占用10个字符

（2）varchar: 变长字段，占用的空间为实际长度+1 MD5加密