Database数据库学习

骑猪看日落 2021-06-26 16:06 420阅读 0赞

## ORM的设计思想 ##

![4a3f315fc03e0cb4054b14b25ee06a57.png][]

对于ORM一定是以对象为起点，使用对象构造出`LINQ`表达式，这样我们在对象的世界中可以描述我们希望对数据库所进行的操作，`LINQ`的最终实现其实也是`Lambda`表达式（毕竟`LINQ`在代码上会直观很多），功能较强的`ORM`中都会记录对象类型到数据库对象的元数据，使用这些元数据可以将复杂的`Lambda`表达式翻译成一个通用的中间表达式，这个表达式其实是抽象于各个不同数据库的具体实现，最后中间表达式再按指定数据库的具体实现生成最终的SQL语句，交由`ADO.NET`对象执行到数据库，如果数据存在返回则会回写到`CLR`对象中。

## 聚集索引和非聚集索引 ##

聚集索引： 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引的好处，索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询。因此在查询方面，聚集索引的速度往往会更占优势。

注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引。

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引的二次查询问题：非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据，也会存放聚集索引所覆盖的列数据。

如何解决非聚集索引的二次查询问题？

建立两列以上的索引（复合索引），即可查询复合索引里的列的数据而不需要进行回表二次查询。建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询。

1.  使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
2.  非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
3.  不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

## 主键，唯一索引和聚集索引 ##

为列创建索引实际上就是为列进行排序，以方便查询。建立一个列的索引，就相当于建立一个列的排序。

主键是唯一的，所以创建了一个主键的同时，这个字段也就创建了一个唯一的索引，唯一索引实际上就是要求指定的列中所有的数据必须不同。

**主键与唯一索引的区别：**

1.  一个表的主键只能有一个，而唯一索引可以建多个。
2.  主键可以作为其它表的外键。
3.  主键不可为`null`，唯一索引可以为`null`。

聚集索引：将表内的数据按照一定的规则进行排列的目录。正因为如此，一个表中的聚集索引只有一个。对此我们要注意“主键就是聚集索引”这是极端错误的，是对聚集索引的一种浪费。（虽然SQL Server默认主键就是聚集索引）使用聚集索引的最大好处就是按照查询要求，迅速缩小查询范围，避免进行全表扫描。

**索引的作用**

1.  帮助检索数据；
2.  提高联接效率；
3.  节省`ORDER BY、GROUP BY`的时间；
4.  保证数据唯一性（仅限于唯一索引）。

**索引的设计**

在确定要建立一个索引时，首先我们要确定它是聚集还是非聚集、单列还是多列、唯一还是非唯一、列是升序还是降序、它的存储是如何的，比如：分区、填充因子等。

*聚集索引*

1.  **首先指出一个误区，主键并不一定是聚集索引**，**只是在SQL SERVER中，未明确指出的情况下，默认将主键定义为聚集，而ORACLE中则默认是非聚集，因为SQL SERVER中的ROWID未开放使用**。
2.  聚集索引适合用于需要进行范围查找的列，因为聚集索引的叶子节点存放的是有序的数据行，查询引擎可根据`WHERE`中给出的范围，直接定位到两端的叶子节点，将这部分节点页的数据根据链表顺序取出即可；
3.  聚集索引尽量建立在值不会发生变更的列上，否则会带来非聚集索引的维护；
4.  尽量在建立非聚集索引之前建立聚集索引，否则会导致表上所有非聚集索引的重建；
5.  **聚集索引应该避免建立在数值单调的列上，否则可能会造成`IO`的竞争，以及B树的不平衡，从而导致数据库系统频繁的维护`B`树的平衡性。聚集索引的列值最好能够在表中均匀分布。**

*唯一索引*

1.  **再指出一个误区，聚集索引并不一定是唯一索引，由于SQL SERVER将主键默认定义为聚集索引，事实上，索引是否唯一与是否聚集是不相关的，聚集索引可以是唯一索引，也可以是非唯一索引；**
2.  将索引设置为唯一，对于等值查找是很有利的，当查到第一条符合条件的纪录时即可停止查找，返回数据，而非唯一索引则要继续查找，同样，由于需要保证唯一性，每一行数据的插入都会去检查重复性。

## 联合索引 ##

对多个字段同时建立的索引(**有顺序**，ABC，ACB是完全不同的两种联合索引)。

以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销，联合索引的顺序非常重要，这个要切记。

**使用一个具有两列的索引不同于使用两个单独的索引。创建复合索引时，应该仔细考虑列的顺序**。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

**重点：**  
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

**最左前缀原则：**  
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，  
注：如果第一个字段是范围查询需要单独建一个索引  
注：在创建联合索引时，要根据业务需求，`where`子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 `userid` 经常需要作为查询条件，而 `mobile` 不常常用，则需要把 `userid` 放在联合索引的第一位置，即最左边

同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；

有人说`where`查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

**联合索引本质**  
当创建(a,b,c)联合索引时，可以理解为创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引，但是实际上没有创建，只是为了好理解。  
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，经过测试，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

**其他知识点**

1.  需要加索引的字段，要在`where`条件中
2.  数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）
3.  避免在`where`子句中使用`or`来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
4.  联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的粒度会更大，效率更高。

## 被问到的高频SQL语句 ##

**在`Customer`表（Id，UserName）中，删除同名的数据，仅保留Id最小的一条数据。**

--思路分析如下：
          --1.找出重复的Name
          select Username  from Customer GROUP BY Username HAVING COUNT(*)>1;
          --2.找出重复Name所在行的Id
          select Id from Customer where Username in (
          select Username  from Customer GROUP BY Username HAVING COUNT(*)>1
          );
          --3.找到重复Name行最小的Id
          select Min(Id)  from Customer GROUP BY Username HAVING COUNT(*)>1;
          --4.删除其他数据，保留最小的Id所在行数据
          --所以最终结果如下：
          delete from Customer where
          Id in (
                      select Id from Customer where Username in (
                               select Username  from Customer GROUP BY Username HAVING COUNT(*)>1
                               )
                   )
          and Id not in(
             select Min(Id)  from Customer GROUP BY Username HAVING COUNT(*)>1
          );

## SQL的执行顺序 ##

下面我们来具体分析一下查询处理的每一个阶段

`FORM`: 对`FROM`的左边的表和右边的表计算笛卡尔积。产生虚表`VT1`

`ON`: 对虚表`VT1`进行`ON`筛选，只有那些符合`join-condition`的行才会被记录在虚表`VT2`中。

`JOIN`： 如果指定了`OUTER JOIN`（比如`left join、 right join`），那么保留表中未匹配的行就会作为外部行添加到虚拟表`VT2`中，产生虚拟表`VT3`, `from`子句中包含两个以上的表的话，那么就会对上一个`join`连接产生的结果`VT3`和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止

`WHERE`： 对虚拟表`VT3`进行`WHERE`条件过滤。只有符合`where-condition`的记录才会被插入到虚拟表`VT4`中。

`GROUP BY`: 根据`group by`子句中的列，对`VT4`中的记录进行分组操作，产生`VT5`.

`CUBE | ROLLUP`: 对表`VT5`进行cube或者rollup操作，产生表`VT6`.

`HAVING`： 对虚拟表`VT6`应用`having`过滤，只有符合`having-condition`的记录才会被 插入到虚拟表`VT7`中。

`SELECT`： 执行`select`操作，选择指定的列，插入到虚拟表`VT8`中。

`DISTINCT`： 对`VT8`中的记录进行去重。产生虚拟表`VT9`.

`ORDER BY`: 将虚拟表`VT9`中的记录按照`order_by_list`进行排序操作，产生虚拟表`VT10`.

`LIMIT/Top`：取出指定行的记录，产生虚拟表`VT11`, 并将结果返回。

书写顺序：`select ... from... where.... group by... having... order by.. limit [Top,]`

(rows)

执行顺序：`from... where...group by... having.... select ... order by... limit[Top]`

[4a3f315fc03e0cb4054b14b25ee06a57.png]: /images/20210626/2a8b8b135a7044f693e3b595be8cf1af.png