数据库笔记

视图(view)
也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表。

存储过程
create or replace procedure 存储过程名。
紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。
OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.
对于IN参数，其宽度是由外部决定。 对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。

ACID
原子性：事务是一个不可分割的整体。要么全做，要么全不做的规则称之为原子性。
一致性：一个事务执行之前和执行之后，数据库数据必须保持一致性状态。（转账前后两个账户总金额应该保持不变）
隔离性：当两个或多个事务并发操作时，将一个事务内部的操作与事务的操作隔离开来。，不被其他正在进行的事务看到。
持久性：事务完成后，DBMS保证它对数据中的数据的修改是永久性的。

事务的几个状态：
活动的（active）：事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。
失败的（failed）：当事务处在活动的或者部：分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
中止的（aborted）：如果事务执行了半截而变为失败的状态，就是要撤销失败事务对当前数据库造成的影响。书面一点的话，我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。
提交的（committed）：当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

SQL三范式：

1. 同一列中某个属性不能有多个值或者不能有重复的属性。
2. 要求数据库中的每个实例或行可以被唯一的区分。
3. 如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R是第三范式的模式。

数据库存储过程和函数存在以下几个区别：
1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。
2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。
4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。
存储和函数的区别（来自书上的描述）：
它们允许 封装功能块，函数与存储过程之间的主要区别在于它们返回的内容：函数总是返回单个值（非常像我们在书中使用的SQL函数）；存储过程不返回值，但是它们可以接受参数，而参数可用于传入和传出数据。在函数体和存储过程体内，可以编写的代码以及执行的操作非常相似；区别在于代码的使用和执行方式。

索引：
加快数据的检索速度。
为什么不给每一个列创建索引：
1、因为增删改的时候，索引库也要动态维护，降低数据库速度。
2、索引需要占物理空间。
3、创建和维护索引需要耗费时间。

Mysql如何为表字段添加索引？？？
1.添加PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )
2.添加UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE ( column )
3.添加INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( column )
4.添加FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column)
5.添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

隔离级别：
1、READ_UNCOMMITTED
读未提交，即能够读取到没有被提交的数据，所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种，因此很少使用
2、READ_COMMITED
读已提交，即能够读到那些已经提交的数据，自然能够防止脏读，但是无法限制不可重复读和幻读
3、REPEATABLE_READ
重复读取，即在数据读出来之后加锁，类似"select * from XXX for update"，明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。REPEATABLE_READ的意思也类似，读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，但是幻读的问题还是无法解决
4、SERLALIZABLE
串行化，最高的事务隔离级别，不管多少事务，挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务，这样就解决了脏读、不可重复读和幻读的问题了

二、脏读、幻读、不可重复读
1.脏读：
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2.不可重复读：
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
3.幻读:
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象
发生了幻觉一样。
例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

数据库查询优化：
1.建立高效且合适的索引。
索引有三种（可以在navicat设计表里面查看）：分别是普通索引（Normal），唯一索引（Unique），全文索引（Full text）
2.排查连接资源未显式关闭的情形。
3.合并短的请求。
根据CPU的空间局部性原理，对于相近的数据，CPU会一起提到内存中。另外，合并请求也可以有效减少连接次数。
4.合理拆分多个表join的SQL，若是超过三个表则禁止join
三个表以上的join，笛卡尔积操作会程几何级数增加。
5.使用临时表。
把中间结果保存到临时表，然后重建索引，再通过临时表进行后续的数据操作。
6.应用层优化。
进行数据结构优化，并发多线程改造。
7.改用其他数据库。
8.当查询大量的数据的时候，又可以分开获得，可以使用limit

**MySQL调优：**原文：http://baijiahao.baidu.com/s?id=1600428542320134160&wfr=spider&for=pc
一、innodb_buffer_pool_size
这个是Innodb最重要的参数，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值为128M。 如果是一个专用DB服务器，那么它可以占到内存的70%-80%。并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M就够了。 设置方法：在my.cnf文件里：innodb_buffer_pool_size=4G
二、innodb_log_file_size这个参数指定在一个日志组中，每个log的大小。innodb的logfile就是事务日志，用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB。一般取256M可以兼顾性能和recovery的速度。 设置方法：在my.cnf文件里：innodb_log_file_size=256M
事务在内存中的缓冲，也就是日志缓冲区的大小， 默认设置即可，具有大量事务的可以考虑设置为16M。
三、innodb_flush_log_at_trx_commit控制事务的提交方式,也就是控制log的刷新到磁盘的方式。这个参数只有3个值（0，1，2）.默认为1，性能更高的可以设置为0或是2，这样可以适当的减少磁盘IO（但会丢失一秒钟的事务。），游戏库的MySQL建议设置为0。主库请不要更改了。 其中： 0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作； 1：（默认为1）在每次事务提交的时候将logbuffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步； 2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。
说明： 这个参数的设置对Ｉｎｎｏｄｂ的性能有很大的影响，所以在这里给多说明一下。
当这个值为1时：innodb 的事务LOG在每次提交后写入日志文件，并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。
当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。
当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。
四、innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT 。
默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer。
为O_DSYNC时，innodb会使用O_DSYNC方式打开和刷写redo log,使用fsync()刷写数据文件。
为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。在unix操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的。

这里说一下innodb的锁的实现：只有通过索引条件检索数据，innodb才会使用行级锁，否则会使用表锁。
innodb存储引擎有以下锁类型：
1.共享锁 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（Shared and Exclusive Locks） 允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集的共享读锁和排他锁。
2.意向锁（Intention Locks） 意向锁是表级锁，它先指明了该事物是那种类型的锁（共享锁或者独占锁），然后去锁定表中某行记录。
3.记录锁（Record Locks） 行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。
4.间隙锁（Gap Locks） 间隙锁避免了别的事务插入数据，从而避免了不可重复读现象。
5.Next-Key Locks 结合了Gap Lock和Record Lock的合并，其设计目的主要是为解决RR级别下的幻读问题。该锁定方式相对于Gap Lock和Record Lock是带闭合区间的范围锁定。
6.插入意向锁（Insert Intention Locks）
7.自增锁（AUTO-INC Locks）
8.空间索引谓词锁（Predicate Locks for Spatial Indexes）

索引失效的几种情况 原为：https://www.cnblogs.com/shynshyn/p/7887742.html
　　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
　　2.对于多列索引，不是使用的第一部分，则不会使用索引
　　3.like查询以%开头
　　4.如果列类型是字符串，那一定要在条件中将数据使用单引号引用起来,否则不使用索引
　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
　　6.is null ,is not null 也无法使用索引
　　7.mysql在使用不等于( != , <>)的时候会导致全表扫描
　　8.尽量使用覆盖索引
　　9.存储引擎不能使用索引中范围条件右边的列
　　10.不在索引列上做任何操作

索引需求定义：
1.功能性需求:
数据是格式化数据还是非格式化数据
数据是静态数据还是动态数据
索引存储在内存还是硬盘
单值查找还是区间查找
单关键词查找还是多关键词组合查找
2.非功能性需求
不管是存储在内存中还是磁盘中，索引对存储空间的消耗不能过大
在考虑索引查询效率的同时，我们还要考虑索引的维护成本：增删改的性能
构建索引常用的数据结构有哪些：散列，红黑树，跳表，B+，位图，布隆过滤器（辅助）
散列表：增删改查性能都是O(1)，这类索引一般存储在内存中。
红黑树：平衡二叉查找树，增删查为O(logn)，一般作为内存索引，EXT文件系统中，对磁盘块的索引用的就是红黑树。
B+树：适合构建存储在磁盘中的索引。读取B+树的索引，需要的磁盘IO次数比较少。
跳表：调整索引结点个数和数据个数之间的比例，可以很好地平衡索引对内存的消耗及其查询效率。

数据库中关于时间的一些问题：https://blog.csdn.net/aha_liu/article/details/82763752
数据库优化查询效率：https://blog.csdn.net/xiaochendefendoushi/article/details/81051444

MySQL的两种引擎原文：https://www.cnblogs.com/xiaohaillong/p/6079551.html
在MySQL数据库中，常用的引擎主要就是2个：Innodb和MyIASM。
1.简单介绍这两种引擎，以及该如何去选择。
Innodb引擎(默认存储引擎)，并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢。由于锁的粒度小。update和insert多，并且高并发下，支持使用Innodb。
MyIASM引擎：也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

MyISAM和InnoDB两者之间有着明显区别，简单梳理如下:
2) 存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
3) 存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
4) 可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
5) 事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
6) AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
7) 表锁差异
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是, InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
8) 全文索引
MyISAM：支持(FULLTEXT类型的)全文索引，InnoDB：不支持(FULLTEXT类型的)全文索引
全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
另外，MyIsam索引和数据分离，InnoDB在一起，MyIsam天生非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”
9) 表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。
10) 表的具体行数
MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
11) CURD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
12) 外键
MyISAM：不支持
InnoDB：支持
13) 查询效率
没有where的count()使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count()时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count()的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM和InnoDB两者的应用场景：

1. MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

MVCC：多版本并发控制
MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。
MVCC 提供了时点（point in time）一致性视图。MVCC 并发控制下的读事务一般使用时间戳或者事务 ID去标记当前读的数据库的状态（版本），读取这个版本的数据。读、写事务相互隔离，不需要加锁。读写并存的时候，写操作会根据目前数据库的状态，创建一个新版本，并发的读则依旧访问旧版本的数据。
实现：MVCC 使用时间戳（TS）、递增的事务 ID（T）实现事务一致性。

去重函数
distinct
select distinct name from table_name;

聚合函数
count()
avg()
sum()
max()
min()

备注：
1.检查字段是否有反斜杠\，这种插入到数据库或者更新，都会被转移掉。要小心