06.index.md 11 KB
索引是什么
逻辑概念
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
作用价值
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的指针ROWID（相当于页码）快速找到表中对应的记录。
索引的原理
让我们咀嚼一下索引定义中这些加粗的关键词。当我们查询条件没有索引的时候，数据库就只能顺序I/O一条条比对，就像你只能一片片去翻你想翻到的书页，如果有1000条数据，你查询的数据在什么位置，他就要I/O多少次，可能就是1000次。我们知道磁盘的I/O效率是相当之慢的，怎么解决？上索引。索引本质就是一种数据结构，它存在的目的就是为了让我们避免大量的顺序I/O，用特定的数据结构和查找方法尽可能的减少I/O次数。
基本概念
1、数据页，磁盘上划分的一个个物理空间，每个数据页的空间大小是固定的，可以存储固定大小的信息。索引数据、表中数据物理上都是保存在数据页中，保存索引数据的数据页又称为索引页。 通常来说，表中字段较多时，一行记录占用的空间就越多，一个数据页中能保存的表数据就越少。
2、B+树，基于平衡二叉树的优化，让每个节点下可以放的叶子节点多一些，树高降低，I/O也就降下来了。
索引的物理构成方式
索引是由若干个索引页构成的，每个索引页上有指针判断数据和指针数据。
为了可以让查找的速度提升，数据库在索引的设计上采用的就是采用B+树的结构来对索引页进行排序存储，不管是聚集索引、非聚集索引、组合索引，本质上都是在b+树的结构上进行查找。
课后题：普通二叉树、平衡二叉树、B树、B+树有什么区别呢？与其他三种数据结构相比，使用B+树数据结构，真的能够减少I/O次数吗？
索引的分类
聚集索引
1、物理构成方式
（1）主键索引
索引树的叶子节点上直接保存着表中的行记录，即索引树的叶子节点就是表的数据页。
（2）辅助索引
辅助索引的叶子节点上保存着主键ID，然后根据指针再去主键索引中查到对应的行数据(这里的步骤和上面的主键索引查询步骤一样了)。这个重新根据主键id去查询行数据的行为被称为回表查询。
2、表中数据的物理存储方式
堆或B+树。
3、注意事项
（1）聚集索引键最好是唯一值。
（2）聚集索引上的列需要足够短。"列需要足够短"指列的数据类型所占物理空间尽量少。因为索引键的空间是有限的，列值所占空间越少，1个索引页就能保存越多的索引键数据，索引树整体的层级和所占物理空间就有越少。
（3）尽量不要创建成组合索引，因为非聚集索引实际包含了聚集索引值。组合索引会造成非聚集索引的索引键数据增大，非聚集索引树整体的层级和所占物理空间就越多。
（4）在频繁更新的列上不要设计聚集索引，因为：第一，它将导致所有的非聚集所有的更新，阻塞非聚集索引的查询；第二，它将导致聚集索引树结构的频繁变动，表中数据的位置也会频繁变动，数据保存、查询都会受影响。
4、优缺点
（1）优点是，查询速度快。
（2）缺点是，删除或更新索引键字段的值，会造成索引树结构频繁变动，数据维护效率降低。
非聚集索引
1、物理构成方式
（1）未建立聚集索引
索引树的叶子节点上直接保存着表中行记录的地址，然后根据地址再到堆中找到对应的表数据。
（2）建立聚集索引
索引树的叶子节点上直接保存着表中行记录的地址，然后根据地址再到B+树中找到对应的表数据。
2、注意事项
（1）包含聚集索引项，自动追加到末端。
（2）频繁更新的列，不适合做聚集索引，但可以做非聚集索引。因为不会造成表中数据的频繁变动。
（3）宽关键字，例如很宽的一列或者一组列，不适合做聚集索引的列可作非聚集索引列。
（4）检索大量的行不宜做非聚集索引。
3、优缺点
（1）优点是，不会造成表中数据的频繁变动，定义不受限制。
（2）缺点是，与聚集索引相比，查询速度会偏慢。
讨论题：一个表上可以创建几个聚集索引，几个非聚集索引呢？
组合索引
1、物理构成方式
叶子节点上保存着主键ID，然后根据指针再去主键索引中查到对应的行数据，这个重新根据主键id去查询行数据的行为被称为回表查询。
2、注意事项
同聚集索引或非聚集索引。
3、优缺点
（1）优点是，可以应对多种查询情景，体现“最左前缀”原则。
（2）缺点是，与单列索引相比，占用空间多，查询速度可能偏慢。
索引的创建及使用注意事项
创建注意事项
1、索引关键字的选取
（1）表的某个字段值得离散度越高，该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字，原因就是这些字段的值非常离散。
（2）占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。
（3）存储空间固定的字段更适合选作索引的关键字。与text类型的字段相比，char类型的字段较为适合选作索引关键字。
（4）索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时尽量不要让字段的默认值为NULL。
（5） Where子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。
（6）更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。
（7）应用“最左前缀”原则。
（8）尽量避免创建重复索引。
引入索引的目的就是提高数据的检查效率，因此索引关键字的选择与select语句息息相关。这句话有两个含义：一是，select语句的设计可以决定索引的设计；索引的设计也同样影响着select语句的设计。例如原则1与原则2，可以影响select语句的设计；而select语句中的where子句、group by子句以及order by子句，又可以影响索引的设计。两个表的连接字段应该创建索引。
2、创建不同类型的索引
（1）检索的数据行：一般地，检索数据量大的一般使用聚集索引，因为聚集索引的叶子页面与数据页面在相同。相反，检索少量的数据可能非聚集索引更有利，但注意书签查找消耗资源的力度，不过可考虑覆盖索引解决这个问题。
（2） 数据是否排序：如果数据需要预先排序，需要使用聚集索引，若不需要预先排序就那就选择非聚集索引。
（3）索引键的宽度： 索引键如果太宽，不仅会影响数据查询性能，还影响非聚集索引，因此，若索引键比较小，可以作为聚集索引，如果索引键够大，考虑非聚集索引，如果很大的话，可以用INCLUDE创建覆盖索引。
（4）列更新的频度： 列更新频率高的话，应该避免考虑所用非聚集索引，否则可考虑聚集索引。
（5）书签查找开销： 如果书签查找开销较大，应该考虑聚集索引，否则可使用非聚集索引，更佳是使用覆盖索引，不过得根据具体的查询语句而看。
注：如果表有聚集索引(区段结构)，那么书签就是从非聚集索引找到聚集索引后，利用聚集索引定位到数据。此处的书签就是聚集索引。如果表没有聚集索引(堆结构)。那么扫描非聚集索引后，通过RID定位到数据，那么此处书签就是RID。
课后题：1、学习覆盖索引。2、性别、状态字段适合建立索引吗？
使用注意事项
1、避免隐式转换-查询条件的数据类型和字段的数据类型不匹配时，可能造成索引失效。字符串使用单引号。
2、查询条件有is null、is not null 不走索引。
3、查询条件是用函数或计算操作。比如concat(‘jingxi’，1)不走索引。
4、or条件，任意一个or连接的条件没有索引，就会失效。可考虑用UNION替换。
5、如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6、like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%aaa%” 不会使用索引，而like“aaa%”可以使用索引。
7、不使用NOT IN 、<>、!=操作，但<，<=，=，>，>=，BETWEEN，IN是可以用到索引的。NOT IN可考虑使用NOT EXISTS替代。
8、在join操作中(需要从多个数据表提取数据时)，只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
课后题：
1、在一个表上分别创建了主键聚集组合索引、辅助聚集组合索引，请描述一下两个索引查询数据时的原理与差异。
2、在一个创建了聚集索引的表上创建非聚集组合索引，在另一个未创建聚集索引的表上创建非聚集组合索引，请描述一下两个表中的非聚集组合索引在查询数据时的原理与差异。