数据库学习总结

目录

分页

SQL Server监听SQL

百万级数据秒插数据库

大数据处理思路

SQL的执行顺序

为什么SQL Server第一次查询慢

limit 1000000,10查询比较慢如何优化？

执行计划

是什么

表的扫描方式

索引

为什么需要索引

索引是如何提升效率的

聚集索引/非聚集索引

查询优化

优化建议

in和exists

like为什么会导致索引失效

三大范式

主键

外键

事物

ACID

事物的并发问题

事物的隔离级别

锁

死锁

是什么

死锁原因

如何避免死锁

如何来降低死锁的概率

乐观锁

是什么

乐观锁实现

行锁/表锁

存储过程

触发器

游标

视图

函数

char/varchar/nvarchar

Unicode/非Unicode

读写分离

垂直切分/水平切分

SQL Server表分区

是什么

分区类型

如何进行表分区

分页

1. MySql分页查询：Limit
2. SQL Server分页查询：row_number()
3. SQL Server2012版本及以上分页查询：offset/fetch next
4. Oracle分页查询：                                                                                                             select * from (select t.*,rownum as no from (select * from table order by id) t) where no         between 1    and 10                                                                                                           select * from table offset 0 rows fetch next 10 rows only

SQL Server监听SQL

SQL Server profiler

百万级数据秒插数据库

1. SQL Server:SqlBulkCopy
2. Oracle:OracleBulkCopy
3. Mysql:SqlBulkCopy

大数据处理思路

ConcurrentQueue+Parallel

SQL的执行顺序

从前到后依次是

FROM,ON,JOIN,WHERE,GROUP BY,

WITH HAVING,SELECT,DISTINCT,ORDER BY,LIMIT,TOP。

为什么SQL Server第一次查询慢

1. 这要看把一条SQL语句发送给数据库，数据库是怎么查的，要知道原理才行。
2. SQL语句发送给数据库先到命令解析器。
3. 命令解析器传递查询树信息到查询分析器。
4. 查询分析器查找执行计划。
5. 如果执行计划缓存区有执行计划，就直接返回该执行计划。
6. 如果执行计划缓存区没有执行计划，那么先生成执行计划，然后把新生成的执行计划在执行计划缓存 区进行缓存，最后在返回新生成的执行计划。
7. 查询分析器将返回的执行计划传递给查询执行器。
8. 查询执行器根据执行计划从数据缓存区里面查找数据。
9. 如果数据缓存区里面有数据就直接返回缓存的数据。
10. 如果数据缓存区里面没有数据那么先读取磁盘的数据文件，然后把查询到的数据缓存到数据缓存区， 最后在返回缓存的数据。
11. 所以第一次查询慢是因为SQL Server要在执行计划缓存区生成执行计划，并且还要把查询到的数据缓 存到数据缓存区。

limit 1000000,10查询比较慢如何优化？

1. 第一种，可以先对数据做过滤，然后在limit。Select id,name from user where id > 1000000 limit 10。
2. 第二种，可以通过order by+索引来解决，需要注意id是索引列，通过索引排序以后在limit，同样可以 减少计算次数。Selecte id,name from user order by id limit 1000000,10。
3. 第三种，可以从业务层面来考虑，限制页数，谁会去翻一百万页去查找数据？

执行计划

是什么

1. 存储过程写好以后，会存储在数据库里面，执行计划缓存区也会生成该存储过程的执行计划。
2. SQL的参数化查询也会在执行计划缓存区里面生成该SQL语句的执行计划。
3. 数据库指定的执行计划，并不是按照SQL语句的查询时间来生成，而是按照使用的系统资源 来生成。
4. 一般向数据库提交完SQL语句，数据库会经过查询优化器，生成多种查询方式，然后选择使用系统资 源最少的一种方式。

表的扫描方式

1. Table Scan表示全表扫描，性能是最差的。
2. Index Scan表示该SQL语句按照索引扫描整张表进行查询，性能比较高。
3. Index Seek表示该SQL语句按照索引扫描表中特定范围内的行进行查询，性能是非常高的。
4. Clustered Index Scan表示该SQL语句按照聚集索引扫描整张表进行查询，性能比较高。
5. Clustered Index Seek表示该SQL语句按照聚集索引扫描表中特定范围内的行进行查询，性能是最高的。

索引

为什么需要索引

1. 如果一本新华字典，没有目录，那得需要花多久才能找到某一个汉字。
2. 在数据库中如果没有索引，查询数据的时候，需要去磁盘里面随机查找，磁盘读取数据的时候需要频 繁的寻找磁道，而且还要从磁盘里面读取数据，这个过程是非常耗时的。

索引是如何提升效率的

1. 有了索引以后，需要把索引列和所属的磁盘块地址，缓存到内存里面。
2. 然后在查询数据的时候，就直接可以找到目标数据列所属的磁盘块地址，然后去读取对应的磁盘块数 据。

聚集索引/非聚集索引

1. 聚集索引的数据是有序排放的，一般是物理排序，常见的有自增主键，创建时间，商品的价格。物理 排序的数据，查询是很快的，相当于新华字典的拼音查询。但是换聚集索引的时候非常耗时，因为会 重新进行物理排序。聚集索引在数据库中只能设置一个，但是可以有多个字段。
2. 非聚集索引相当于新华字典的偏旁部首查询，可以存储重复的值，查询也比较快，但是会有维护索引 的成本。非聚集索引不是设置的越多越好，每个非聚集索引可以设置多个字段，比较适合名称或者账 号这些有重复的值但是需要经常查询的字段。
3. 主键和外键是必须要建立索引的，主键一般考虑使用数值索引，性能是最高的。
4. 一些经常进行查询的字段也可以建立索引，比如在where条件里面的字段。
5. 还有像oder by，group by， distinct，经常在这些条件里面的字段也可以考虑建立索引。
6. 一些基本上不怎么查询，而且重复的值比较多的字段，不需要加索引。比如像性别，状态这些字段。
7. 所以总结一下，就是那些重复的数据特别少而且还需要经常查询的字段都可以考虑加索引。而那些经 常会发生改变，重复的数据也比较多的字段，不需要加索引。
8. 索引也不是越多越好，因为数据库需要撕页，会重新维护索引的目录。

查询优化

优化建议

1. 查询的时候要尽量避免对列的计算。
2. in查询和or查询都会导致索引失效。
3. 可以考虑将in查询换成exists。
4. not in，is null，is not null，like都会导致索引失效。
5. <>在查询的时候表示不等于，也会导致索引失效，可以考虑拆分为>和<。
6. 关联查询的时候，关联的表越少查询的性能肯定越高。

in和exists

1. in适合table2比table1小的情况：select * from tb1 where id in(select id from tb2) 
2. exists适合table2比table1多的情况：select a.* from tb1 a where exists(select b.id from tb2 b where a.id =b.id)

like为什么会导致索引失效

1. 因为索引的本质就是排序，是连续的物理排序。
2. 比如like’%张三’，查询的是以张三为结尾的所有数据，这在索引里面肯定是不连续的，如果数据库需 要返回所有的结果，就只能进行全表扫描。

三大范式

1. 第一范式需要表中的每一列都保持原子性。
2. 第二范式需要一张表只能描述一个对象。如果一张表既存储了部门的信息也存储了用户的信息，就会 导致表的职责不清晰，可以考虑将表进行拆分。
3. 第三范式需要在满足第一范式的同时，表中的每一列都得和主键相关。表中有些冗余的字段，有的时 候也是有必要的，可以便于查询，但是修改的成本会变高，如果查询比较多，修改比较少，可以适当 的违背第二范式。

主键

1. 主键对于用户来说一般是没有意义的，默认都是聚集索引，在数据库中主要是为了提升查询的效率。
2. 常见的主键是自增ID，类型一般为int或者bigint。
3. 还有一些主键是GUID，GUID是全球唯一的，所以在进行数据迁移的时候很方便。但是GUID在表中存 储的空间比较大，而且在物理上也不是连续的，所以它没有聚集索引，查询的效率也比较低。

外键

外键可以做数据的校验，还可以做级联删除。如果系统对数据的精度要求比较高，就可以考虑使用外键。

事物

ACID

1. ACID是数据库事物正确执行的四要素。
2. 原子性是要么都执行，要么都不执行。
3. 一致性是要么执行都成功，要么执行都失败。
4. 隔离性是两个事务在同时操作一张表时，B事务要么是在A事务之前完成，要么是在A事务之后完成。
5. 持久性是事务成功提交之后，会持久化的保存在硬盘上。

事物的并发问题

1. 脏读是事物A读取了事物B的更新数据。如果事物B进行了回滚操作，这个时候事物A读取到的数据 就是脏数据。
2. 不可重复读是事物A多次读取同一条数据时，事物B在事物A读取的过程中，对数据进行了更新，导 致事物A读取到的结果出现了不一致。
3. 幻读是事物A按照搜索条件进行数据读取时，事物B也插入了相同搜索条件的新数据，然后事物A再 次按照原来的搜索条件进行数据读取时，查询到了事物B新插入的数据。
4. 丢失更新是事物A修改了一条记录，然后事物B在事物A提交的同时也进行了一次修改，当事物 A进 行数据查询的时候，查询到了刚才修改的记录没有被修改。
5. 不可重复读主要侧重于修改，幻读主要侧重于新增或者删除。
6. 解决不可重复读的问题需要锁住满足条件的行。
7. 解决幻读的问题需要锁住整张表。

事物的隔离级别

1. Read uncommitted(读未提交)是事物A和事物B都开始进行写操作，但是事物B不允许其它事物同时进 行写操作，而是允许其它事物进行读操作。能够有效避免丢失更新，但是可能会出现脏读。
2. Read committed(读已提交)如果是读操作的事物，其它事物可读可写。如果是写操作的事物，其它事物 不能进行读写。能够有效避免脏读和丢失更新，但是可能会出现不可重复读。
3. Repeatableread(可重复读取)是指在一个事物内，多次读取同一条数据，如果这个事物还没有结束，将 禁止其它事物对该条数据进行读写。能够有效避免不可重复读和脏读，但是可能会出现幻读。
4. Serializable(可串行化)是最严格的事物隔离级别，要求所有的事物都能够序列化的执行，也就是说所有 的事物只能是执行完一个再执行下一个。可串行化的事物隔离级别解决了丢失更新，脏读，不可重复 读，幻读，但是性能是最差的。

锁

死锁

是什么

死锁可以理解为是一组相互竞争资源的线程因为相互等待而导致的阻塞现象。

死锁原因

1. 第一个是指互斥的条件，共享的资源只能被一个线程所占有。
2. 第二个是指占有并且等待，线程A已经取得了共享资源X的占有权，然后在等待共享资源Y的时候不 释放共享资源X。
3. 第三个是指不可抢占，其它线程不能强行抢占某个线程占有的共享资源。
4. 第四个是指循环等待，线程A等待线程B释放共享资源的占有权，线程B也等待线程A释放共享资源 的占有权。

如何避免死锁

1. 在高并发的项目中，死锁是没有办法避免的，只能够通过降低死锁的概率来解决。
2. 互斥的条件是没有办法被破坏的，因为锁本身就是通过互斥来解决线程的安全问题。
3. 对于占有并且等待的这个条件，可以一次性申请所有的资源，这样子也就不存在等待的问题。
4. 对于不可抢占的这个条件，占有共享资源的线程，再进一步申请其它资源的时候，如果申请不到可以 主动释放自己所占有的资源，这样子不可抢占的这个条件也就被破坏掉了。
5. 对于循环等待的这个条件，可以按照一定的顺序来申请资源，先申请资源序号小的然后再申请资源序 号大的，通过这种线性化的资源申请方式，就不会存在循环等待的问题。

如何来降低死锁的概率

1. 不加锁就不会死锁(nolock)。
2. 可以严格控制代码的执行顺序。
3. 给数据库的每张表要设置好索引，单表的数据量如果非常大的时候可以考虑进行分表。
4. 可以考虑使用读写分离，来降低数据库的读写压力，其实只要数据库的读写很快就能减少死锁的概率。
5. 对于一些静态内容的数据可以考虑使用缓存或者CDN进行加速。
6. 也可以设置死锁的时间，一旦出现了死锁等待，就要果断的放弃。
7. 也可以降低数据库的事务隔离级别。

乐观锁

是什么

1. 乐观锁的事物隔离级别是Read committed，也就是读已提交。
2. 能够有效避免脏读和丢失更新，但是可能会出现不可重复读。

乐观锁实现

1. 取出记录时，获取当前oldVersion/旧的时间戳。
2. 执行更新时，set version/时间戳=newVesion/新的时间戳where version/时间戳=oldVersion/旧的时间戳。
3. 如果version/时间戳不对，就更新失败。

行锁/表锁

1. where里面的条件就是在进行行锁。
2. 但是where 1=1的时候是表锁，会导致全表扫描。

存储过程

1. 存储过程是SQL语句经过编译后存储在数据库中，性能一般比较高。
2. 它的性能相比普通的SQL语句要高一些，在一些公司的老项目里面用的比较多。
3. 但是它的SQL语句都在数据库里面，后面如果换数据库的话会很麻烦。
4. 对于数据库的磁盘IO也有比较大的压力，因为SQL语句的业务逻辑基本上都写在了数据库里面。

触发器

1. 触发器是一种特殊类型的存储过程，通过事件进行触发，能够被自动的调用并且执行。
2. 执行前触发是指执行A表之前去触发B表里的内容。
3. 执行后触发是指执行A表之后去触发B表里的内容。

游标

游标跟索引比较类似，能够从多条记录中精确的定位到某一条记录，现在基本上没有人用它。

视图

1. 视图就是把多个表关联起来进行查询，生成了一个虚拟的表在数据库里面。
2. 对于一些非常复杂的SQL语句就可以考虑使用视图，用来简化查询。

函数

常见的函数有sum()，avg()，max()，min()。

char/varchar/nvarchar

1. char的最大长度是8000，长度是数据的实际字节长度，如果数据的长度不够会被空格填充。英文和数 字占一个字节，汉字占两个字节。
2. varchar的最大长度是8000，存储的是非Unicode字符集，长度是数据的实际字节长度。英文和数字占 一个字节，汉字占两个字节。
3. nvarchar的最大长度是4000，存储的是Unicode字符集，长度是字符个数的两倍。英文，数字和汉字 都占两个字节。
4. 如果数据的长度基本上是一致的，就可以使用char。
5. 如果数据的长度差异性比较大，就可以使用varchar或者nvarchar。
6. 如果项目里面的数据都是英文和简体中文，就用varchar。
7. 如果还包含了其它字符集的文字，就用nvarchar。

Unicode/非Unicode

1. 非Unicode字符集只能保存简体中文和英文，如果保存了其它文字的字符集可能会出现乱码。
2. Unicode字符集除了能保存简体中文和英文，还能保存繁体中文，韩文，日文，阿拉伯文。

读写分离

1. 读写分离存在一个主库和多个从库，主库主要负责新增，修改和删除。从库主要负责数据查询。
2. 新增，修改和删除的占比是20%，数据查询的占比是80%。

垂直切分/水平切分

1. 垂直切分是把每个单独的大模块或者子系统划分为不同的数据库。
2. 水平切分是把一张表存储的数据按照时间或者区域划分为不同的数据库。

SQL Server表分区

是什么

1. 在SQL Server中所有的数据都是存储在一个文件上，默认是mdf文件，在数据量非常多的时候查询效 率肯定比较低。
2. 如果采用表分区，数据就会按照指定的分区规则，被存储到不同的文件中，这样一个非常大的数据文 件就会被拆分成多个小文件，查询效率也会有所提升。

分区类型

1. 水平分区可以按照时间或者区域来进行划分。
2. 垂直分区可以按照单独的大模块或者子系统来进行划分。

如何进行表分区

SQL Server的表分区涉及到数据文件，文件组，分区函数和分区方案，不是随便敲一个简单的SQL命令就能搞定的。