摘要
人类存储信息的发展过程大致如下:
可能不太准确，因为是个人用自己的理解总结出来的，但毫无疑问，目前，各大公司和机构的数据都是维护在数据库里的。数据库作为数据存储介质发展的最新产物，必然具有许多优点，其中之一就是存储在数据库中的数据可以非常快速地被访问。数据库访问速度快的一个重要原因就是索引index的作用。这是本文的主要内容。为什么索引可以让数据库查询更快？
计算机存储原理
在理解索引的概念之前，我们需要了解计算机存储的基础知识。
我们知道数据持久化后存在于数据库中，所以我现在的问题是数据库把数据存储在哪里？答案显然在电脑的存储设备上。就个人电脑而言，数据存储在我们的电脑存储设备中。
电脑存储设备有很多种，其中速度越快越贵，所以容量越小。比如我们的RAM随机存取存储器(RAM)，也就是俗称的内存条，相对便宜，比如我们的硬盘。而我们的数据往往存储在最慢的存储设备硬盘上，因为断电后现有的数据依然存在。
计算机存储介质有很多种，比如硬盘，比如缓存。不同的存储介质具有不同的数据读取速度。比如RAM等易失性存储设备的读写操作非常快，访问其中的数据几乎没有延迟。为此，计算机操作系统的设计是这样的:数据永远不会直接从硬盘等机械设备中取出，而是先从硬盘转移到速度更快的存储设备中，比如RAM，应用程序可以直接从RAM中按需获取数据。
计算机内部的机械硬盘如下:
在典型的硬盘驱动器中，可以有许多磁盘，并且“磁盘”在外观上非常类似于光盘(但是具有高存储容量)。磁盘按磁道分条，磁盘可分为扇区。
为了获得数据，需要通过主轴旋转“磁盘”。大多数硬盘供应商都提到了主轴旋转的速度，例如，7200 rpm和15000 rpm。磁盘中的数据总是表示为扇区的固定大小倍数。因此，如果要从硬盘中访问数据，需要执行以下步骤，这也是性能开销的主要来源。
确定数据所在的正确磁道，并将磁头移动到该磁道。即通常说的寻道。让“主轴”旋转盘片，使正确的扇区位于“磁盘头”下方。从扇区开始到扇区结束获取整个数据。
如果数据正好分布在连续的扇区中，将会提高数据采集的性能。因为主轴和磁头不需要移动/旋转，所以开销并不多，但大部分时间这种开销是存在的。
因为这个开销，我们不能直接从硬盘上获取数据。RAM内存高性能背后的主要原因是它没有硬盘那样的机械运动部件。然而，尽管RAM的性能很高，但其中的数据不会被用作永久存储。断电后就会消失，重启后就什么都没有了，这就是为什么我们需要硬盘来持久。毫无疑问，数据库中的数据是存储在硬盘中的，所以访问数据库中的数据必然会经历磁盘操作的开销。
索引是如何工作的？
了解了以上知识，指数就比较好理解了。
举个例子，想象一下，有一本500页的字典，里面有几十万个单词，里面的单词是无序排列的。现在我需要你从里面找出一些单词，并且不允许你查看目录。毫无疑问，我们只能一页一页地翻，这对于非人类来说是一项无法接受的工作。一定要想到的是先看目录，找到相关的字或者偏旁，再在相应的地方找字，这样效率大大提高。事实上，目录是一种索引，它的思想是一脉相承的。
数据库的索引类似于书中的这个目录。索引将帮助我们快速检索数据库。该查询不需要通过整个表获取数据，而是从索引中找到数据块。以数据库表为例:
上表是一个真实的数据库表，其中每一行都是一条记录，每条记录都有字段。假设上面的数据库是一个有100，000条记录的大型数据库。现在，我们想从10万条记录中搜索一些内容，那么一条一条地搜索无疑需要很长时间。这时，我们在数据结构和算法中学习的二分搜索法方法就派上了用场。
二分搜索法方法
使用二分搜索法方法，需要先对数据进行排序，但其查询效率会大大提高。例子如下:
假设我们在上面的数据库中使用固定长度的记录。固定块记录大小为205字节，默认块大小为1024字节。然后:
固定记录大小=204字节，块大小=1024字节
所以每个数据块的记录数=1024/204=5条记录，100，000条记录就是20，000个数据块。
在没有任何算法的情况下，我们需要查询100，000条记录中的一条，在最坏的情况下，我们需要遍历20，000个块来获得所有100，000条记录。但如果进行二分搜索法，只需要20000的对数底2，即14.287712倍即可。这意味着我们只需要对排序后的值进行14次搜索，就可以用二分法找到你感兴趣的唯一值。
上图显示了由一串数字生成的二叉查找树。其时间复杂度为O(n)=O(log2N)，即n以2为底的对数。其中n是目标组的总数据。
比如假设n是8，那么O(n) = O(2是8的对数)= O(3)。
遍历模式，其时间复杂度为O(n)
在上面的例子中，n是10000。所用索引的时间复杂度为O(2是10000次的对数)，约等于13。O(10000)和O相差800倍左右。
为什么索引使查询更快？
这时候就可以直接回答上面的问题了。索引的数据是预先排序的，因此可以使用二分搜索法来提高查询效率。这也解释了为什么索引要尽可能建立在主键这样的字段上，因为主键必须是唯一的，这样的字段生成二叉查找树的效率无疑是最高的。
为什么不能建立太多的索引？
如果一个表中所有字段的索引都很大，也会导致性能下降。想象一下，如果一个索引和一个表一样长，那么它将成为再次检查的开销。这就好比字典的目录很详细，但是它的长度和所有的单词一样长。这时候目录本身的效率就大大降低了。
索引有什么缺点吗？
是的，索引可以提高查询读取性能，但会降低写入性能。在有索引的情况下，如果一条记录被更改或者一条新记录***入到数据库中，它将执行两次写操作(一次是写记录本身，另一次是更新索引)。因此，在定义索引时，必须记住以下几点:
索引表中的每个字段将降低写入性能。建议使用表中的唯一值为字段编制索引。在关系数据库中充当外键的字段必须建立索引，因为它们有助于跨多个表进行复杂查询。索引还使用磁盘空间，因此在选择要索引的字段时要小心。
什么是聚集索引？
聚集索引也叫聚集索引，它的定义是聚集索引表中数据行的物理顺序与列值(一般是主键的列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。
例如:
结合上表就很好理解了:数据行的物理顺序和列值的物理顺序是一样的。如果我们查询id较低的数据，这一行数据在磁盘中的物理地址会较低。聚集存储的记录是物理连续的，而非聚集索引是逻辑连续的，物理存储是不连续的。
为什么查询更快？通过上面的分析，我们知道索引是用二叉树的数据结构来描述的。我们可以这样理解聚集索引:索引的叶节点就是数据节点。非聚集索引的叶节点仍然是索引节点，但是有一个指向相应数据块的指针。
通常，默认情况下，主键将创建聚集索引。
在创建聚集索引之前，应该首先了解如何访问数据。考虑将聚集索引用于:
包含大量不同值的列。使用以下运算符返回一系列值的查询:BETWEEN、>:、& gt= 、& lt和<>
频繁更改的列这将导致整行移动，因为SQL Server必须按物理顺序保存行中的数据值。请特别注意这一点，因为在大容量事务处理系统中，数据是不稳定的。
指数失败的典型例子
如果在条件中使用or，即使有带索引的条件，也不会使用索引查询。这就是为什么您应该尽量不要在查询中使用or。请改用IN。
常见的sql优化方法有哪些？
1.避免全表扫描。
全表扫描通常发生在下列情况下:
SQL的on子句或者where子句涉及到的列上没有索引；表数据量很小，走索引查询比全表扫描更麻烦；这对于少于10行且行长度较短的表来说很常见
2.避免索引失效。
在索引列上什么都不做(计算、函数、自动或手动类型转换)，会导致索引失效，转向全表扫描。
存储引擎不能使用索引中范围条件右侧的列。这是因为查询的是age中的查询范围，pos列的索引没有影响。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))并减少select *。
对于MySQL
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描is null,is not null也无法使用索引like 通配符开头’%abc..’，mysql索引会失效会变成全表扫描的操作
3.避免排序，这是避免不了的。尽量选择索引排序。
4.避免查询不必要的字段。
5.避免创建和删除临时表。
本文来自微笑向暖投稿，不代表舒华文档立场，如若转载，请注明出处：https://www.chinashuhua.cn/24/586743.html
微信扫一扫 
