mysql索引底层原理？

一、mysql索引底层原理？

一、定义

索引定义：索引（Index）是帮助MySQL高效获取数据的数据结构。本质：索引是数据结构。

二、B-Tree

m阶B-Tree满足以下条件：1、每个节点至多可以拥有m棵子树。2、根节点，只有至少有2个节点（要么极端情况，就是一棵树就一个根节点，单细胞生物，即是根，也是叶，也是树)。3、非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整，如5阶B树，每个节点至少有3个子树，也就是至少有3个叉)。4、非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示该节点中保存的关键字个数，K为关键字且Ki<Ki+1，A为指向子树根节点的指针。5、从根到叶子的每一条路径都有相同的长度（叶子节点在相同的层）

B-Tree特性：

1、关键字集合分布在整颗树中；2、任何一个关键字出现且只出现在一个节点中；3、每个节点存储date和key；4、搜索有可能在非叶子节点结束；5、一个节点中的key从左到右非递减排列；6、所有叶节点具有相同的深度，等于树高h。

B-Tree上查找算法的伪代码如下：

三、B+Tree

B+Tree与B-Tree的差异在于：1、B+Tree非叶子节点不存储data，只存储key；2、所有的关键字全部存储在叶子节点上；3、每个叶子节点含有一个指向相邻叶子节点的指针，带顺序访问指针的B+树提高了区间查找能力；4、非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字；

四、B/B+树索引的性能分析

依据：使用磁盘I/O次数评价索引结构的优劣主存和磁盘以页为单位交换数据，将一个节点的大小设为等于一个页，因此每个节点只需一次I/O就可以完全载入。根据B树的定义，可知检索一次最多需要访问h个节点渐进复杂度:O(h)=O(logdN) dmax=floor(pagesize/(keysize+datasize+pointsize))一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，3层可存大约一百万数据）B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存）B+Tree内节点不含data域，因此出度d更大，则h更小，I/O次数少，效率更高，故B+Tree更适合外存索引。

五、MySQL索引实现1、MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址； MyISAM主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复；

2、InnoDB的数据文件本身就是索引文件，叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。 InnoDB的辅助索引data域存储相应记录主键的值而不是地址； 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录；

3、页分裂问题

如果主键是单调递增的，每条新记录会顺序插入到页，当页被插满后，继续插入到新的页；

如果写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

如果频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

六、总结

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助

1、为什么不建议使用过长的字段作为主键？

2、为什么选择自增字段作为主键？

3、为什么常更新是字段不建议建立索引？

4、为什么选择区分度高的列作为索引？区分度的公式是count(distinct col)/count(*)

5、尽可能的使用覆盖索引

七、优化LIMIT分页查询

SELECT * FROM table where condition LIMIT offset , rows ;上述SQL语句的实现机制是： 1、从“table”表中读取offset+rows行记录。 2、 抛弃前面的offset行记录，返回后面的rows行记录作为最终的结果。覆盖索引：select a.id, sid, parent_s_id from cashpool_account_relationship a join (select id from cashpool_account_relationship LIMIT 1000000,10)b on a.id = b.id;select id, sid, parent_s_id from cashpool_account_relationship where id >=(select id from cashpool_account_relationship LIMIT 1000000,1) LIMIT 10;

八、Q&A

1、InnoDB支持hash索引吗？--马欣InnoDB是支持hash索引的，不过其支持的hash索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成hash索引，不能人为干预是否在一张表中生成hash索引。2、InnoDB主键索引的叶节点含完整的数据记录，那主键索引文件要比数据文件大吗？--徐财厚1）.在Innodb 引擎中，主键索引中的叶子结点包含记录数据，主键索引文件即为数据文件。2）.在 tables 表中统计的data_length数据为主键索引大小，index_length 为统计的这个表中所有辅助索引（二级索引）索引的大小。

二、es 底层索引原理？

原理如下所示：

       ES的底层原理主要包括索引、分片、倒排索引、分词器、查询和聚合等。 索引 ES的索引是指将数据存储在一个或多个分片中,每个分片都是一个独立的Lucene索引。

三、sql索引是什么？

1.SQL索引是什么？

定义：索引（Index）是帮助MySQL高效获取数据的数据结构，就好比书的目录，加快数据库的查询速度。

2.SQL索引的作用是什么？优点是什么？

提高查询效率

消除数据分组、排序

避免“回表”查询（索引覆盖）

优化聚合查询

用于多表JOIN关联查询

利用唯一性约束，保证数据唯一性

InnDB行锁实现

四、sql索引有哪些？

SQL索引是在数据库表中创建的数据结构，其目的是加快对表中数据的访问速度。常见的SQL索引主要有以下几种：

1. 主键索引：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据。主键索引通常是基于自增或者GUID等方式创建的，可以提高数据插入和查询的速度。

2. 唯一索引：唯一索引可以确保表中某一列的值是唯一的，这样可以避免重复数据的插入和查询。唯一索引通常用于对表中的某一列进行约束，以确保数据的完整性和正确性。

3. 聚集索引：聚集索引是基于表中某一列的物理排序方式创建的索引，可以提高查询效率。聚集索引只能创建一个，因为它直接影响到表中数据的物理存储方式。

4. 非聚集索引：非聚集索引是基于表中某一列的逻辑排序方式创建的索引，可以提高查询效率。非聚集索引可以创建多个，因为它不直接影响到表中数据的物理存储方式。

5. 全文索引：全文索引可以用于对文本类型的列进行搜索，可以提高搜索的效率。全文索引可以在包含大量文本的列中使用，如博客、新闻等。

总的来说，SQL索引的作用是提高数据库的查询效率，减少数据扫描的时间和成本。不同类型的索引适合不同的场景，对于表中不同类型的列可以选择不同的索引类型进行优化。

五、聚集索引和非聚集索引的区别底层？

实际上，可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果遇到不认识的字，不知道它的发音，这时候，需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

六、SQL SERVER如何创建索引？

如何创建索引 :

使用T-SQL语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…) [WITH FILLFACTOR=x] UNIQUE表示唯一索引，可选 CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选 FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

在stuMarks表的writtenExam列创建索引：

USE stuDBGO

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_writtenExam') DROP INDEX stuMarks.IX_writtenExam /*--笔试列创建非聚集索引：填充因子为30％--*/CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*-----指定按索引 IX_writtenExam 查询----*/SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定SQL Server按哪个索引进行数据查询，但一般不需要我们人工指定。SQL Server将会根据我们创建的索引，自动优化查询。

七、sql怎么使用已有索引？

sql怎么使用已有的索引的操作步骤如下：

　　1、找到现有索引

　　首先，我们打开数据库，找到一个表，随后，我们打开索引文件夹，可以看到有一个索引，这个项属于是表的主键。

　　2、新建索引

　　我们鼠标右键点击索引文件夹，在诸多选择中选择新建索引的选项。

　　3、新建索引列

　　在弹出的新建索引界面，如果你的索引类型选择聚集的话，会有弹出提示，已经存在聚集索引，这是因为主键默认如此，这时候，我们需要添加索引列，这里，我们给姓名列添加一个索引

　　4、确定名称

　　这时候，我们索引新建界面给索引起一个索引名称，随后，勾选唯一，这样基本完成新建操作。

八、sql有无索引的区别？

有区别 ，具体如下：

有索引的，一般是索引扫描或聚集索引扫描

(seek)

没有索引的，是表扫描(sacn)

现在查询分析器已经很智能了，某些表有索引的情况下，也不是一定使用索引，它会判断io、预读，来自动的使用表扫描和索引扫描。

九、sql between and走索引吗？

这个问题跟IN无关吧。 1. 你查询的字段需要存在相关索引系统才会走索引的。 2. 你查询的字段必须是原表自动不能用套在函数内查询，否则不走索引。 3. 你要确认你没有执行强制走索引语句。否则你走的索引跟你IN的字段不符也是不会走到索引的。 4. 你查询的表要足够多数据。没上1W的数据系统都默认帮你全表查询了，你还走什么索引。 5. 你一定要在系统表内查询，如果你吧查询出来的结果归到临时聚集再IN，因为临时聚集是走临时表空间，不会走索引的。

十、sql索引原理及使用？

SQL索引是一种用于加快数据库查询的数据结构。它类似于书籍的目录，可以让数据库系统快速定位到需要查询的数据，从而提高查询速度和性能。索引通常是在表中的某些列上创建的，可以根据这些列来快速查找数据。

索引的使用原理如下：

1. 创建索引：在数据库表的某些列上创建索引，可以使用CREATE INDEX语句来创建。索引会创建一个B-tree（B树）数据结构，用于快速查找数据。

2. 查询数据：当查询操作发生时，数据库系统会首先检查是否有适用的索引。如果查询条件涉及到了索引列，那么数据库系统将使用索引来查找数据，而不是扫描整个表。这可以大大提高查询速度和性能。

3. 更新数据：当表中的数据发生变化时，索引也需要更新。数据库系统会自动维护索引的正确性和一致性，确保索引与表中数据的一致性。

索引的使用需要注意以下几点：

1. 不要过度使用索引：创建过多的索引会占用大量的存储空间，并可能导致查询性能降低。因此，在创建索引时应该谨慎选择需要创建索引的列，只选择那些经常用于查询的列。

2. 索引列的数据类型应该尽可能小：较小的数据类型会占用更少的存储空间，从而提高查询性能。

3. 索引列的顺序很重要：在创建索引时，应该优先考虑那些选择性好的列，即包含不同值较多的列。这可以使索引更加有效。

4. 定期维护索引：索引也需要定期维护，包括删除不需要的索引、重新组织索引等操作，以确保索引的正确性和一致性。

总之，SQL索引是一种非常重要的数据库性能优化技术，可以通过加快查询速度和性能来提高应用程序的响应能力。但是，索引的使用需要谨慎选择和维护，否则可能会影响数据库的性能和可靠性。