数据库基础知识详解四：存储过程、视图、游标、SQL语句优化以及索引

写在文章前：本系列文章用于博主自己归纳复习一些基础知识，同时也分享给可能需要的人，因为水平有限，肯定存在诸多不足以及技术性错误，请大佬们及时指正。

11、存储过程

​ 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。想要实现相应的功能时，只需要调用这个存储过程就行了（类似于函数，输入具有输出参数）。

优点：

• 预先编译，而不需要每次运行时编译，提高了数据库执行效率。
• 封装了一系列操作，对于一些数据交互比较多的操作，相比于单独执行SQL语句，可以减少网络通信量。
• 具有可复用性，减少了数据库开发的工作量。
• 安全性高，可以让没有权限的用户通过存储过程间接操作数据库。
• 更易于维护。

缺点：

• 可移植性差，存储过程将应用程序绑定到了数据库上。
• 开发调试复杂。
• 修改复杂，需要重新编译，有时还需要更新程序中的代码以更新调用。

Drop/Delete/Truncate的区别？

Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器（包含一个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1。

Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除。不能回滚，不会触发触发器。

触发器是什么？

触发器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）来触发运行的操作（不能被直接调用，不能接收参数）。在数据库里以独立的对象存储，用于保证数据完整性（比如可以检验或转换数据）。

有哪些约束类型？

约束（Constraint）类型：

• 主键（Primary Key）约束
• 唯一约束（Unique）
• 检查约束
• 非空约束
• 外键（Foreign Key）约束

12、视图、游标

视图：

​ 从数据库的基本表中通过查询选取出来的数据组成的虚拟表（数据库中只存放视图的定义，而不存放视图的数据）。可以对其进行增/删/改/查等操作。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）。

可以跟基本表一样，进行增删改查操作(增删改操作有条件限制，一般视图只允许查询操作)，对视图的增删改也会影响原表的数据。它就像一个窗口，透过它可以看到数据库中自己感兴趣的数据并且操作它们。好处：

• 通过只给用户访问视图的权限，保证数据的安全性。
• 可以通过对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户视图上。例如，Student表涉及全校15个院系学生数据，可以在其上定义15个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本原系学生视图。
• 可以通过视图使用户以多种角度看待同一数据，比如不同种类的用户共享一个数据库。（通过权限？）
• 简化复杂的SQL操作（比如原来查的内容是几张几百列的数据表，而我们只关心其中几个数据，那我们可以建立一个视图，直接在视图中查询它们，可以提高很多效率），隐藏数据的复杂性（比如复杂的连接）。

游标（Cursor）：

​ 用于定位在查询返回的结果集的特定行，以对特定行进行操作。使用游标可以方便地对结果集进行移动遍历，根据需要滚动或对浏览/修改任意行中的数据。主要用于交互式应用。它是一段私有的SQL工作区，也就是一段内存区域，用于暂时存放受SQL语句影响的数据，简单来说，就是将受影响的数据暂时放到了一个内存区域的虚表当中，这个虚表就是游标。

​ 游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。即游标用来逐行读取结果集。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

​ 游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。通俗来说，游标就是能在sql的查询结果中，显示某一行（或某多行）数据，其查询的结果不是数据表，而是已经查询出来的结果集。

简单来说：游标就是在查询出的结果集中进行选择性操作的工具。

13、SQL语句的优化

• 应尽量避免在 where 子句中使用!=、<、>操作符或对字段进行null值判断，否则将引擎放弃使用索引而进行全表扫描；
• 只返回必要的列：最好不要使用 SELECT * 语句；
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据；
• 将一个大连接查询分解成对每一个表进行一次单表查询，然后进行关联，这样做的好处有：

​ 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询。减少锁竞争。

14、索引

​ 索引是对数据库表中一列或多列的值进行排序的一种结构（说明是在列上建立的），使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

​ 当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止。有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

• （1）聚簇索引对磁盘上存放数据的物理地址重新组织以使这些数据按照指定规则排序的一种索引（数据的物理排列顺序和索引排列顺序一致）。按照数据存放的物理位置为顺序，每张数据表只能创建一个聚簇索引（因为要改变物理存储顺序），与非聚簇索引相比查询数据速度很快，进行修改的速度较慢。主键索引就是一种聚簇索引。
• InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个NOT NULL 的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。
• （2）非聚簇索引（也叫二级索引/辅助索引）只记录逻辑顺序，并不改变物理顺序。通过索引记录地址访问表中的数据。索引的逻辑顺序和表中行的物理存储顺序不同。Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引。

从应用上分，主键索引（聚集），唯一索引（聚集/非聚集），普通索引，组合索引，单列索引和全文索引

• （3）唯一索引（UNIQUE）：索引列的值必须唯一，允许有空值。
• （4）主键索引 PRIMARY KEY：必须唯一，不允许空值（是一种特殊的唯一索引。表中只有一个，MySQL创建主键时默认为聚集索引，但主键也可以是非聚集索引）。当列添加主键约束时，自动添加主键索引。
• （5）普通索引：用表中的普通列构建的索引，没有任何限制，用于加速查询。
• （6）组合索引（复合索引）：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• （7）全文索引：先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。

索引的优点：

• 大大加快了数据的检索速度。
• 显著减少查询中分组和排序的时间。
• 通过创建唯一性索引，可以保证数据库表中的某一行数据的唯一性。
• 将随机I/O变为了顺序I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的缺点：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

索引失效的情况？

• 以“%(表示任意0个或多个字符)”开头的LIKE语句。
• OR条件中的每个列没有同时使用索引。
• 对于多列索引，必须满足 最左匹配原则/最左前缀原则 (最左优先：多列索引col1、col2和col3，则 索引生效的情形包括 col1或col1，col2或col1，col2，col3)。
• 如果MySQL估计全表扫描比索引快，则不使用索引（比如非常小的表）。

哪些地方适合创建索引？

• 某列经常作为最大最小值。
• 经常被查询的字段。
• 经常用作表连接的字段。
• 经常出现在ORDER BY/GROUP BY/DISDINCT后面的字段。

创建索引需要注意的：

• 只应建立在小字段上，而不要对大文本或图片建立索引（一页存储的数据越多一次IO操作获取的数据越大效率越高）。

• 建立索引的字段应该非空，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL。

• 选择数据密度大（唯一值占总数的百分比很大）的字段作索引。