MySQL数据库基础(2)--一些高级功能与设计原则

一、MySQL的一些高级功能

视图：
是一条SELECT语句执行后返回的结果集，即一张虚拟的表，不存储具体的数据，引用了若干张基础表。

视图的操作：
①创建create view viewname as (select...)，会创建一个虚拟的表；
②查询语句与普通SQL相同，删除使用drop view viewname；
③一般只用来查询，在特殊情况下可以修改表内容（单表视图，不包含聚合函数和嵌套查询的视图等并需要保证更新后的数据可以被视图查询出来），但一般不使用，直接修改原表（修改原表后视图同步更新）。

视图的优点：
①使用简单（提高了重用性，复杂的SQL语句无需重复写）
②查询安全（可以对不同的用户给与不同的视图）
③修改数据库结构无需修改程序功能（只需修改视图）
④简化数据表，让数据更清晰。

事务：
是一个操作序列，这个序列中的操作要么都执行，要么都不执行，是一个不可分割的工作单位，假如有一项操作没有完成，则回滚至初始状态，事务广泛应用于银行系统/订单系统等，python调用数据库时，默认开启事务。（若两个事务同时操作，则事务依据的其实是当前的真实值，即commit后的值）

事务的特性（ACID）：
①原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单元，不能只完成事务中的一部分操作；
②一致性(consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态，即失败后回滚；
③隔离性(isolation)：一个事务所做的修改在提交前，对其他事务是不可见的，即存在事务锁，若一个事务操作中，则第二个事务会等待；
④持久性(durability)：一旦事务提交，则其所做的修改会永久保存。

事务的操作：
①开启事务，start transaction；
②SQL语句查询；
③commit/rollback，在提交/回滚之前，修改的内容会维护到缓存中，不会直接操作数据表。

索引：
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，相当于一本书的目录。

索引的原理：
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。其目的为提高查询和排序效率，mysql的索引其实也是一张表，有两种类型：b+树和hash。
注：InnoDB引擎中默认的索引类型是b+树，也支持hash索引，但需要手动开启，且其由引擎自动优化创建，普通开发者是无法干预的。

索引的操作：
①创建索引，有两种方式，Ⅰcreate (unique|fulltext) index indexname on tabname(fiedname1, fiedname2); Ⅱalter table tabname add (unique|fut)index indexname(fieldname1, 2);第二种为用修改表结构的方式添加索引，最普通的索引称为单列索引；其中在多个字段上创建的索引称为复合索引，遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用；添加括号内容的称为唯一/全文索引，唯一索引意为创建索引的列不允许重复（null值可重复，但空值不可重复），全文索引用于替代模糊查询（临时方案，多用全文搜索引擎）；主键及外键会自动创建索引，称为主键索引，不允许为空；
②使用索引，没有单独的使用方法，其是优化SQL查询语句效率的工具；
③索引的查看/删除，show indexes in tabname; drop index indexname on tabname; alter table tabname drop index indexname;；
④注意查询时的关键字顺序；

索引的优缺点：
优点：
①有效缩短数据的检索时间；
②加快表与表之间的连接；
③为用来排序或分组的字段添加索引可以加快分组和排序的效率；
缺点：
①创建索引需要时间/空间成本，并随数据量增大而增大；
②降低表的增删改效率，每一次操作索引都要进行动态更新；
因此一般在数据量大但改动操作少，查询响应时间不能满足需求时添加索引。

注：索引与主键的关系如下：
①主键一定是索引，索引不一定是主键，主键可以是单个字段也可以是联合字段(称为联合索引)，主键也称为主键索引，其是唯一索引的一种特殊形式；
②索引按种类分为单一索引，联合索引(其中的字段必须全部相邻)，按创建方式分为唯一索引(不允许任意两条记录拥有相同值)，主键索引，聚集索引(聚集索引表示表中存储的数据按照索引的顺序存储，一个表中只能有一个聚集索引)，非聚集索引(非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，一张表中可以有多个非聚集索引)；
③一个表可以有多个唯一索引，但主键只能有一个，主键列不能为空，但唯一索引列可以为空；
④外键就是其他表的主键，一般会对外键添加索引，否则可能会在查询时产生性能问题。

mysql的账户管理：其存储于mysql数据库的user表，因此修改密码和删除用户相当于修改user表的记录：
①创建账户并授予权限grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';常用的权限有create、alter、drop、insert、update、delete、select/all privilieges；
②修改权限grant 权限名称 on 数据库 to 账户@主机 with grant option;注意修改后需使用flush privileges刷新权限；
③修改密码，update user set authentication_string=password('新密码') where user='用户名'; 即使用password()对密码进行加密，修改密码后需刷新权限；
④远程登陆，修改mysqld.cnf文件下的对本地端口绑定的代码，在进入数据库时添加-h127.888.777.1IP地址，-p3306端口默认；
⑤删除用户，drop user '用户名'@'主机'; /delete from user where user='用户名';后者删除后需刷新权限。

数据库的备份：mysqldump -uroot -p database > xxx.sql; '>'意为重定向，创建的xxx.sql文件记录了表的创建和插入的详细信息（非只有数据，而是包含了创建信息），mysql -uroot -p dbname < xxx.sql即将xxx.sql文件写入dbname数据库。

数据库的主从（master/slave）：
①数据备份；
②作为后备数据库，当主服务器故障时可切换数据库，避免数据丢失；
③读写分离，使数据库能支撑更大的并发，有些报表sql语句耗时很长，分离操作到不同的服务器，避免锁表；
④负载均衡，使master充当调度者，先接收所有的请求，然后根据每台slave的负载情况来分配任务，可用于解决高并发。（负载均衡问题即寻找服务器集群的整体性能最优方案）

主从复制的原理：
主从同步基于二进制日志机制（Binary log），主服务器记录数据库变动情况，从服务器获得主服务器日志后，根据需求执行一部分或全部主服务器日志，并记录自身执行日志，其由三个线程执行：
①master库binlog输出线程，用于发送binlog内容；
②slave库I/O线程，请求master发送binlog更新并在本地读取/保存为relaylog文件；
③slave库SQL线程，用于读取relaylog文件并执行。
其上三个线程，master为每一个slave分配一个①，每一个slave都存在独立的②③。

主从同步的基本实现过程：
①开启master的二进制日志机制并配置一个独立的ID；
②在每一个slave上配置一个独立的ID（不重复）并创建一个用于复制master数据的账号；
③备份master数据并在slave上还原，master/slave建立连接并开启同步；
④同步成功后进行测试，其简单步骤如下：
Ⅰclient修改master数据
Ⅱmaster写binlog
Ⅲmaster通过binlog输出线程发送binlog内容给slave
Ⅳslave的I/O线程接收读取并写入到relay log
Ⅴslave的SQL线程读取relaylog并执行SQL

注：①可以配置slave提升为master以解决master宕机问题，也可以通过双机热备的方式避免master宕机对服务产生影响，其原理为VRRP；
②每个数据库只有一个单独的线程用于SQL，读操作会上读锁(阻塞对同一张表的写操作)，又因为对slave日志中的读写操作是随机的，当某个读操作耗时过长就会导致大量写操作语句阻塞；可通过优化架构，提升硬件性能，设置
专用备份slave，增加slave数量（分散读压力），优化文件系统属性等方式改善；
③半同步复制用于解决数据丢失问题，确保binlog至少提交至一个slave，要求slave返回确认信息。

数据库设计的范式：
数据库的范式(NF)官方定义：符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。
数据库范式也分为1NF，2NF，3NF，BCNF，4NF，5NF，一般数据库设计满足3NF/BCNF即可，高级范式是包括低级范式在内的，即若满足2NF一定满足1NF。

1NF：符合1NF的关系中的每个属性都不可再分，1NF是所有关系型数据库的基础，但是只满足第一范式可能出现的问题有：

①数据冗余过大，会有很多重复；
②插入异常，如想插入一个没有学生的系是做不到的；
③删除异常，删除掉某个系的所有学生，该系也会随之被删除；
④修改异常，假如某人转系，则需要修改多条记录的多个字段。

2NF：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
(1)函数依赖：若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y；部分函数依赖是指Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，称 Y 部分函数依赖于 X，如：姓名完全函数依赖于学号，姓名姓名部分函数依赖于(学号，课名)。
(2)码：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码，一个码可以有多个字段，一个表中可以有多个码。
(3)主属性：包含在任何一个码中的属性称为主属性。
2NF简单的来说，就是一个表中的其他字段不可以部分依赖于主键（主键可以有多个字段，若只依赖主键的一个字段即部分依赖）。

其对上述1NF存在的问题的改进情况如下：
①数据重复量减小；
②插入异常无改进；
③删除异常无改进；
④修改异常有改进。

3NF：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。
即主键以外的字段必须直接依赖主键，不可以有别的依赖的字段。

其对于1NF的所有问题都进行了改进。

BCNF：在 3NF 的基础上消除主属性对于码的部分与传递函数依赖，即不允许一个表中存在两个唯一标识。

注：数据库的设计范式只是设计理念，遵循其可以设计出冗余较小、结构合理的数据库，但实际运用中应以需求为准，特殊情况特殊对待。

E-R模型：是标识数据及数据之间相互关系的一种概念模型。
实体entity（即数据模型中的数据对象，用矩形标识），
属性attribute（对象拥有的属性，用椭圆标识），
关系relationship（对象之间的联系，用菱形标识），
分为一对多/多对一（在多中新建字段）/多对多（新建对象）三种。

数据库事务的隔离：
数据库的事务参上述，但在实际运用中，经常会出现有两个甚至多个线程/用户共同访问同一个数据库的同一条数据，与多线程的全局变量/多进程的文件处理类似的，若不处理此类问题，则可能产生数据错误甚至异常：
①修改时允许修改：某一次的修改数据丢失；
②修改时允许读取：脏读，脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据，而这个事务可能会被回滚；
③读取时允许修改：不可重复读，不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了；
④读取时允许插入：幻读，例如读取总记录数x，此时又插入了一条记录，则还有一条记录没有被计入，则称为幻读；

注：幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

MySQL的四种隔离级别：
(1)Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，也称之为脏读（Dirty Read）；

(2)Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。其中可能出现不可重复读的情况，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果；

(3)Repeatable Read（可重复读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，该级别理论上会导致幻读，但底层引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了此问题；

MVCC：是通过保存数据在某个时间点的快照来实现的，其保证每个事务看到的数据都是一致的，不同存储引擎的MVCC的实现方式不同，典型的有乐观并发控制和悲观并发控制(在前文Django订单模块中有较详细描述)，InnoDB中的MVCC类似于乐观锁的实现方式。
MVCC只在可重复读和读已提交两个隔离级别下工作，其他两个隔离级别都与MVCC不兼容。

(4)Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题，相当于互斥锁，在这个级别，可能导致大量的超时现象和锁竞争。

注：①在实际工作过程中，对于重要的部分(交易、订单等)还是会手动添加并发控制(乐观锁/悲观锁)以防止出错；
②Django2.0版本中已经自动将所有数据库的事务隔离级别修改为read-committed，因此无需专门修改mysql数据库隔离级别。

数据库的分库与分表
单个数据库是有读写瓶颈的，当数据量很大时，①读写性能下降， 即使设计了索引但是在数据量很大的情况下索引也会变的很大，性能依然下降；②数据文件变得很大，数据库的备份和恢复耗费时间很长；③单个数据库越大，在极端情况下数据丢失的风险就越高。

为了缓解单一数据库的性能问题，把一个数据库切分成多个部分放到不同的主机上，这就是分库与分表的基本思想。

数据库分库分表适用场景与注意事项
数据库的分库分表与读写分离类似，都是属于绝招，即使用多台主机存储数据来减轻业务压力，在此之前，应该先考虑的优化顺序为：
①数据库主机硬件优化；
②数据库调优，SQL语句优化，如增加索引，优化慢查询等；
③引入缓存，减少数据库查询压力；
④程序代码优化，数据库表优化甚至重构；
⑤读写分离与分库分表。

考虑数据库切分的场景与注意事项如下：
①尽量不使用分库分表，读写分离的优先度都比分库分表要高，当其他的优化都无法进行或已经无效时，再考虑分库分表；
②当数据量过大，正常业务访问受影响时考虑；
③随着业务发展，可能需要对表进行拆分，如包括有最近登陆时间字段的用户信息表，其中最近登录时间字段会经常变动，当数据量增大时，频繁修改对数据库压力变大，需要将经常变动的字段拆分出去作为一个新表。

分库分表的实现方式
(1)垂直分库与垂直分表
垂直分库即将不同的业务放置在不同的数据库中，建立数据库集群；垂直分表基于关系型数据库表结构中的字段(列)进行，一条记录内容太多即表结构字段太多时会导致跨页影响性能；

优点：
①解决了业务系统方面的耦合，逻辑清晰，也更利于数据库管理；
②在高并发场景下，可以一定程度的提升数据库瓶颈；
缺点：
①关联查询不再适用；
②数据库事务变复杂，考虑分布式事务；
③服务器成本和代码开发成本；
④并没有解决单表数据量过大的问题；
前两者可以通过业务代码重构来解决，但确实增加了业务的复杂度。

(2)水平库内分表与分库分表
水平切分即不改变表结构，将过量的记录分到不同的表/库中，库内分表可以解决单一表数据量过大的问题，对于减轻MySQL数据库的压力帮助不大，因此常用的水平切分为分库分表。

优点：
①解决了单表数据量过大的问题；
②应用端代码改动较小；
缺点：
①分布性事务处理困难；
②关联查询(join)、聚合查询(count)、排序查询(order by)等操作变得复杂，需要使用中间件或修改业务代码；
③数据库扩展与维护困难。

分库分表的一些可能出现的问题
(1)事务一致性，分布式事务没有简单的方案，一般可使用"XA协议"和"两阶段提交"处理；
(2)跨节点关联查询问题，可以通过字段冗余(即空间换时间的方式添加额外字段，是一种典型了反范式设计)，数据组装(进行多次查询后拼装结果)，ER分片(根据数据的ER关系来进行分片，使得有关联的内容尽可能在同一个数据库中)；
(3)跨节点分页、排序问题，一般采用数据组装的方法进行多次查询后组合结果；
(4)主键重复问题，有使用UUID、结合数据库本身生成不重复ID、Snowflake分布式自增ID等方法，此处可参考比较成熟的解决方案：Leaf——美团点评分布式ID生成系统；
(5)数据迁移、扩容问题。

注：分库分表还使得安全性和可用性变高，垂直切分将不同的业务放在不同的数据库中，水平切分将数据分别放置于不同的数据库中，出问题时不会牵涉到其他业务或不会影响全部用户。