数据库

参考：https://www.cnblogs.com/takumicx/p/9998844.html

　　　https://blog.csdn.net/u013628152/article/details/82184809

这几天，要开始面试了，数据库无疑是各家面试的重头之一，在此总结一下数据库的一些知识点。

数据库：

　　数据库表面上就是一系列的表格，包含的属性主要有：

约束：　　

　　主键约束：唯一标志一个数据库

　　外键约束：用来连接标语表之间的关系

　　唯一性约束：

设计：三大范式

　　1NF：每一列属性都是不可拆分的属性值

　　2NF：首先是1NF，其次表必须有主键，且其他列必须完全依赖于主键

　　3NF：首先是2NF，其次数据不存在传递关系，每行数据都与主键直接关联

索引：原理（B Tree  /  B+ Tree)

　　数据库对一个属进行普通的检索的时候，需要从头到尾进行遍历，直到查到相应的数据，如果数据量巨大的话则相应的开销也是相当大，不利于实际使用，于是索引诞生

　　对我们需要频繁进行查找的列进行设置索引，则，下次搜索的时候可以直接搜索索引一列，而不需要全盘搜索，又因为BTree的作用，可以将这其中的搜索时间呈指数式减少

　　比如： 有100000000条某数据，现在要在其中检索出某个数值，最差情况下要检索10000000次，而如果设置索引，根据BTree的深度，可以下降到10（B树的深度即需要检索的次数），甚至更少。

　　索引优点：可以更快的检索数据

　　既然索引这么方便，为什么不给每个列设置一个索引，以方便使用，这是因为，索引需要占据实际的物理空间，且每次对相应的列进行增删改的时候，都需要相应的修改索引，因此，在过多的不需要频繁查询的列设置索引只会增加维护的难度，总结：对索引进行查找省时间，索引占空间，维护耗时间。所以需要综合考虑，达到一个最完美的状态，少了达不到效果，多了增加消耗。

存储过程：

　　将一系列的SQL指令封装起来，进行预编译，以备以后的使用，且可以提高使用的速度

存储引擎：

　　将数据以不同的技术存储到数据库中，其中的每一种技术都使用不同的存储机制、索引技巧、锁定水平、并且最终提供广泛不同的功能和能力。

　　最主要的两种：

　　　　MyISAM:　　它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。　　　　　　

　　　　InnoDB:　　支持事务，外键等，在需要事务支持，并且需要较高的并发读取频率可以使用InnoDB。

　　　　　　1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
　　　　　　2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
　　　　　　3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
　　　　　　4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
　　　　　　5.支持自动增加列AUTO_INCREMENT属性。　　　

　　　　MEMORY： 只支持长度不变的格式的数据，逻辑存储介质为系统内存，因此在守护进程崩溃时所有MEMORY数据都会丢失。在对速度要求很高的情况下适合　

　　　　　　1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，

　　　　　　　设置此参数，就可以限制　　Memory表的最大大小。

　　　　　　2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

　　　　　　3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

　　　　如何选择引擎：

　　　　　　（1）是否需要支持事务；
　　　　　　（2）是否需要使用热备；
　　　　　　（3）崩溃恢复：能否接受崩溃；
　　　　　　（4）是否需要外键支持；

事务：逻辑上的一个整体的一些列操作

　　　当系统发生崩溃等其他情况时，数据库能够以事务为边界进行回滚恢复

　　　当有多个用户同时对操作数据库时，能够通过以事务为单位进行并发控制

　　比如：甲转账给乙100元，反应在数据库层面为（此即一个事物）：

　　　　------------------------------------

　　　　BEGIN TRANSACTION

　　　　A账户减少100元

　　　　B账户增加100元

　　　　COMMIT

　　　　------------------------------------

　　4个特性ACID：

　　　　A原子性：事务整体像一个原子一样不可分割，要么都成功，要么都失败。比如：甲给乙转100元，该事务结果要么成功甲-100，乙+100，要么失败甲不变，乙不变（失败，事务会回滚到最初状态）；不存在，甲不变乙+100这种可能；

　　　　C一致性：事务的执行使数据库从一个一致性状态转到另一个一致性状态一致性。一致性状态反应为1：数据库仍然满足各种约束；2：数据库描述的的现实世界的状态不会变，如上面的例子，假设甲只有100元，乙有0元，则在经过一次转账事务后，无论成功失败，甲乙两人加起来共都只会有100元，不会多，不会少

　　　　I隔离性：并发执行的事务不会互相影响，其对数据库的影响和他们串行执行一样；比如甲乙同时给丙转账应该和甲乙相继给丙转账结果相同；

　　　　D持久性：一个事物执行成功后，结果会持久的写进数据库保存起来；

　　　　事务：并发控制：隔离性；一致性

　　　　　　    日志恢复：原子性；持久性；一致性

　　并发异常：

　　　　脏写：一个事务回滚了导致另一个事务的提交被吞掉了，事务回滚了其他事务已经提交的修改

　　　　丢失更新：一个事务的操作抵消了另一个事务（+100；-100），导致另一个事务的更新好像消失了

　　　　脏读：一个事务读取了另一个事务未提交的修改

　　　　不可重复读：一个事务的两次读取结果不同，事务读取了另一个事务的已提交的修改

　　　　幻读：一个事务在读取一个范围的数据时，两次读取结果不同（insert）

　　事务的隔离级别（从低到高）及可能发生的并发问题：

　　　　　　　　　　脏写　　脏读　　不可重复读　　幻读　　丢失更新

　　　　读未提交　　　　　　可能　　可能　　　　　可能　　可能

　　　　读已提交　　　　　　　　　　可能　　　　　可能　　可能

　　　　可重复读　　　　　　　　　　　　　　　　　可能

　　　　串行化

 数据库优化：

　　优化说明：

　　　　优化的方法策略有很多，但设计初期的数据结构是系统的基石，至关重要

　　　　优化性价比走势：

                      

　　　　性能优化无止境，满足需求时即可，不要过度优化.

　　优化方向：根据上图优化主要可以分为以下：

　　　　SQL及索引优化

　　　　合理的数据库设计

　　　　系统配置

　　　　硬件优化

　　优化具体方案：

　　　　代码优化：首先检查代码设计是否规范，是否可以优化（比如for循环嵌套过多，无畏的判断过多，逻辑设计重复等）

　　　　优化慢SQL：通过慢查询日志或慢查询系统定位问题SQL，然后使用explain、profile等工具来逐步调优

　　　　SqlServer执行计划：通过执行计划了解：哪些步骤花费的成本比较高、哪些步骤产生的数据量多、每一步执行了什么动作

　　具体手段：

　　　　尽量少用或不用SQL Server自带函数　　　　　　

　　　　　　select id from t where substring(name,1,3) = ’abc’
　　　　　　select id from t where datediff(day,createdate,’2005-11-30′) = 0
　　　　　　可以这样查询：
　　　　　　select id from t where name like ‘abc%’
　　　　　　select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

　　　　连续数值条件用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5 

　　　　Update语句，如果只更改1、2个字段，不要Update全部字段

　　　　尽量使用数字型字段

　　　　尽量少用 * ，用确定的字段列表代替*，尽量不返回用不到的信息。

　　　　表与表之间拥有一个冗余字段关联比直接用Join有更好的性能

　　　　连接池优化，随着业务访问量或者数据量增长，原有连接池参数可能不能很好满足需求，此时应当对连接池参数进行调优

　　　　合理使用索引

 　　　　合理分表

 　　　　读写分离

　　　　合理设计缓存

　　

小题目：数据库里存放IP地址
　　可以将数据转为byte型或者int型，节约内存（INET_ATON()、INET_NTOA()）

　　　　inet_aton()算法，其实借用了国际上对各国IP地址的区分中使用的ip number。

　　　　a.b.c.d 的ip number是：

　　　　a * 256的3次方 + b * 256的2次方 + c * 256的1次方 + d * 256的0次方。

　　　　查询某段IP时可使用如下语句：

　　　　SELECT IP FROM IPDB WHERE INET_ATON(IP) BETWEEN INET_ATON('192.168.11.1') AND INET_ATON('192.168.11.150')