数据库-索引

B+ Tree 原理

数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。
在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1。

操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：
（一）更少的查找次数
平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。
（二）利用磁盘预读特性
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

mysql索引有哪些

从数据结构角度

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

1.BTree索引(O(log(n)))

是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
复合索引是N个字段组合成一个索引的。

2.hash索引O(1)

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。
  InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

3.全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。

从物理存储角度

1、聚集索引（clustered index）
2、非聚集索引（non-clustered index）

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值
2、唯一索引：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3、单列索引 （普通索引）
4、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
5、全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
6、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

主键索引ALTER TABLE table_name ADD PRIMARY KEY ( column )
唯一索引ALTER TABLE table_name ADD UNIQUE ( column )
普通索引ALTER TABLE table_name ADD INDEX index_name ( column )
多列索引ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
FULLTEXT(全文索引)ALTER TABLE table_name ADD FULLTEXT ( column)

索引什么时候出现失效

• 对于多列索引，不是使用的第一部分，则不会使用索引。（最左匹配）
• like的模糊查询以%开头
• 在 where 子句中使用 != 或 <> 操作符
• where 子句中使用 or 来连接条件，但不是or条件中的每个列都加上索引
• where 子句中的 “=” 左边进行函数运算、算术运算或其他表达式运算
  表达式操作，如：select id from t where num/2 = 100 应改为:select id from t where num = 100*2；函数操作，例如：select id from t where substring(name,1,3) = ‘abc’ – name;以abc开头的，应改成：select id from t where name like ‘abc%’
• is null,is not null无法使用索引
• 如果MySQL估计使用全表扫描要比使用索引快，则不使用索引；
• 列类型是字符串，数据没有用引号引用起来
• 不适合键值较少的列（重复数据较多的列）

索引优化

1. 独立的列
   在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
   例如下面的查询不能使用 actor_id 列的索引：
   SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
2. 多列索引
   在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。
   SELECT film_id, actor_ id FROM sakila.film_actor
   WHERE actor_id = 1 AND film_id = 1;
3. 索引列的顺序
   让选择性最强的索引列放在前面。
   索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。
   例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。
   SELECT COUNT(DISTINCT staff_id)/COUNT() AS staff_id_selectivity,
   COUNT(DISTINCT customer_id)/COUNT() AS customer_id_selectivity,
   COUNT() FROM payment;
   staff_id_selectivity: 0.0001
   customer_id_selectivity: 0.0373
   COUNT(): 16049
4. 前缀索引
   对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。
5. 覆盖索引
   索引包含所有需要查询的字段的值。具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

主键和唯一键有什么区别

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。（主键列在创建时，已经默认为非空值 + 唯一索引了。）
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

索引的优点/为什么建立索引

将无序的数据变成相对有序的数据（就像查目录一样）

• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。大大加快检索速度，这也是创建索引的主要原因
• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 创建唯一性的索引可以保证表中每一行数据的唯一性
• 可以加快表与表之间的连接，是在实现表与表之间的参考完整性实现有特别的意义
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能。

建立索引的缺点？

1.创建索引和维护索引耗时随着数据的增加而增加，成正比；
2.索引需要占物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果建立聚簇索引，占得物理空间会更大；
3.对表中的数据进行维护时，对索引也要进行维护，降低了数据的维护速度。

什么情况下适合建立索引

• 经常需要搜索的列上，可以加快搜索的速度；
• 作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 经常需要根据范围进行搜索的列上，因为索引已经排序，其指定的范围是连续的；
• 经常需要排序（排序字段order by，分组字段group by，因为分组的前提是排序）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 经常使用在WHERE子句中的列，加快条件的判断速度；
• 统计字段，如count(),max()

什么情况下不适合建立索引

• 查询过程中用的少或很少参考的列（比如where条件中出现的字段）
• 频繁更新的，要求的修改性能远大于检索性能（索引会提高检索性能，降低修改性能）
• 唯一性太差的字段不适合建立索引，例如性别，真假值
• 表数据可以确定，比较少的不需要建索引
• 定义为image，text和bit数据类型的列（要么数据量大，要么取值少）

索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

为什么mysql用B+树做索引而不用B-树或红黑树

B+树做索引而不用B-树

• 优点一： B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。（B-树的每个节点都有data域，增大了节点大小，增加了磁盘IO次数。因为磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多。而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。）
• 优点二： B+树所有的Data域在叶子节点，并且所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据，能进行区间访问。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B+树做索引而不用红黑树

大规模存储数据，红黑树往往出现由于树的深度 过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。根据磁盘查找存取的次数往往由树的高度所决定，B树可以有多个子女，从几十到上千，可以降低树的高度。

MyISAM和InnoDB实现BTree索引方式的区别

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 通过 B+Tree 结构对 主键ID 建索引，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。若建索引的字段不是主键 ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

为什么非主键索引结构叶子节点存储的是主键值？

主键索引和非主键索引维护各自的B+树结构，当插入的数据的时候，由于数据只有一份，通过非主键索引获取到主键值，然后再去主键索引的B+树数据结构中找到对应的行数据，节省了内存空间；
如果非主键索引的叶子节点也存储一份数据，如果通过非主键索引插入数据，那么要向主键索引对应的行数据进行同步，那么会带来数据一致性问题。可以通过事务的方式解决，我们都知道使用事务后，就会对性能有所消耗。

Myisam 引擎也是采用的 B+Tree 结构来作为索引结构。
由于 Myisam 中的索引和数据分别存放在不同的文件，所以在索引树中的叶子节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以 Myisam 是非聚簇索引。

InnoDB 是以 ID 为索引的数据存储。
采用 InnoDB 引擎的数据存储文件有两个，一个定义文件，一个是数据文件。
InnoDB 通过 B+Tree 结构对 ID 建索引，然后在叶子节点中存储记录。

若建索引的字段不是主键 ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录。

聚集索引 非聚集索引 以及各自适用情况

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。 （InnoDB）
非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（MyISAM）

覆盖索引

• 所谓的索引覆盖就是索引包含查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据，有以下一些优点：
• 索引项通常比记录要小，所以MySQL访问更少的数据；
• 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
• 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
• 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

组合索引和两个单个索引的区别

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

先用第一索引定位到数据，在用第二个索引查找，因为索引都是有序的，所以比单独的两个好

原文链接：https://blog.csdn.net/Abysscarry/article/details/80792876

最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，联合索引最多只能包含16列
最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则索引就可以被用到，如果查询的列不是索引的最左前缀, 那MySQL不会将索引用于执行查询.
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。