数据库索引

数据库

索引是定义在table基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一种索引项由索引字段和行指针构成。

**索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。**索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（BTree）中，使SQL可以快速有效地查找与键值关联的行

2. 为什么要建立索引，即索引的优点：

1. 建立索引的列可以保证行的唯一性，生成唯一的rowId；

2. 建立索引可以有效缩短数据的检索时间；

3. 建立索引可以加快表与表之间的连接；

4. 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序。

3. 索引的缺点：

• 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大；

• 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间），从而会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，这样会导致时间变长。

• 在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

常用索引？

索引的优点

• 大大加快数据的检索速度，这也是创建索引的最主要的原因；
• 加速表和表之间的连接；
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

什么情况下设置了索引但无法使用？

• 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
• OR 语句前后没有同时使用索引；
• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
• 对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则 索引生效的情形包括 col1或col1，col2或col1，col2，col3)。

什么样的字段适合创建索引？

• 经常作查询选择的字段
• 经常作表连接的字段
• 经常出现在order by, group by, distinct 后面的字段

创建索引时需要注意什么？

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；
• 空间方面：索引需要占物理空间。

索引的分类

• 普通索引和唯一性索引：索引列的值的唯一性

• 单个索引和复合索引：索引列所包含的列数

• 聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；

  而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

  主键就是默认的主键索引。

  唯一索引

  普通索引

  索引是帮助mysql高效获取数据的数据结构。索引就是数据结构

  分类：

• 主键索引 primary key

  • 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引 unique key

  • 避免重复的列出现，可以重唯一索引可以重复，多个列都可以标志位 唯一索引

• 常规索引

  • 默认的，index key关键字来设置

• 全文索引

  • 在特定的数据库引擎下菜有，myisam支持
  • 快速定位数据

  索引规则：

  索引不是越多越好，不要对经常变动的数据加索引，小数据的表不需要加索引，索引一般加在常用来查询的字段上

主键、自增主键、主键索引与唯一索引概念区别

主键：指字段 唯一、不为空值 的列；

主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

自增主键：字段类型为数字、自增、并且是主键；

唯一索引：索引列的值必须唯一，但允许有空值。

​ 主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，

​ 主键不允许有空值，所以不能说唯一索引也是主键。

索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：

• 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
• OR语句前后没有同时使用索引；
• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
• 对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则 索引生效的情形包括col1或col1，col2或col1，col2，col3)。

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程具有以下特点：

• 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
• 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
• 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；
• 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

• Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；

• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；

• Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

  因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

什么叫视图？游标是什么？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作。特别地，对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

索引的底层原理？

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

B-Tree结构 m叉

1. 每个节点最多有m个子节点。
2. 除了 根节点和叶子节点，每个节点至少有 cell(m/2) 个子节点
3. 若根节点不是叶子节点，至少有2个子节点
4. 所有叶子节点都在同一层
5. 每个非叶子节点由n个key与n+1个指针组成 cell(m/2) - 1 《 n《 n - 1

B+Tree InnoDB存储引擎的索引实现

与B树不同点：

1.叶子节点冗余了所有的非叶子节点

2.每个叶子节点增加一个指向相邻叶子节点的指针

• B+最多包含n个key，B-最多有n-1个

• B+叶子节点包含所有的key信息，依key大小顺序排列

• B+所有的非叶子节点都可看作是key的索引部分，B+所有的叶子节点包含了所存信息，非叶子节点不存储数据信息

  优点：叶子节点有指向相邻节点的指针，提高了范围查询的效率

  缺点：一个节点中存放多少元素合适

  另外B+树相比AVL树层数低，降低了磁盘IO的操作次数

1. B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
2. B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，使