MySQL的B+树索引和hash索引的区别(The difference between MySQL B + tree index and hash index)

简述一下索引:

索引是数据库表中一列或多列的值进行排序的一种数据结构;索引分为聚集索引和非聚集索引,聚集索引查询类似书的目录,快速定位查找的数据,非聚集索引查询一般需要再次回表查询一次,如果不使用索引就会进行全表扫描;还有可以进行多字段组成联合索引,但是要符合最左匹配原则要求。

索引是数据库表中一列或多列的值进行排序的一种数据结构;索引分为聚集索引和非聚集索引,聚集索引查询类似书的目录,快速定位查找的数据,非聚集索引查询一般需要再次回表查询一次,如果不使用索引就会进行全表扫描;还有可以进行多字段组成联合索引,但是要符合最左匹配原则要求。

如果使用覆盖索引就可以不回表扫描。
索引类型:InnoDB引擎,默认B+树(O(logN))、Hash索引 B树索引 O(1)

1、由于底层是使用hash表,以key-value存储,无法直接通过索引查询,只选择一个数据hash索引更快,但是如果选择N条数据,hash索引的时间复杂度是O(N),由于B+树索引有序,且叶子节点有链表连接,查询效率比hash索引快
2、索引在硬盘保存,一般不会一次性保存到内存中,B+树可以设计允许数据分批加载,同时树的高度较低,查询速率较快
3、硬盘的I/O速度相比内存来说非常慢,而索引是用于加快查询速度的,需要减少I/O操作,内存和磁盘以页为单位交换数据,为了减少I/O,索引在新建节点的时候,是直接申请一个页的空间,存储分配是按页对齐,就实现了一个节点一次I/O。
4、B+ 树是平衡树,它查找任意节点所耗费的时间都是完全相同的,比较的次数就是 B+ 树的高度

1、由于底层是使用hash表,以key-value存储,无法直接通过索引查询,只选择一个数据hash索引更快,但是如果选择N条数据,hash索引的时间复杂度是O(N),由于B+树索引有序,且叶子节点有链表连接,查询效率比hash索引快
2、索引在硬盘保存,一般不会一次性保存到内存中,B+树可以设计允许数据分批加载,同时树的高度较低,查询速率较快
3、硬盘的I/O速度相比内存来说非常慢,而索引是用于加快查询速度的,需要减少I/O操作,内存和磁盘以页为单位交换数据,为了减少I/O,索引在新建节点的时候,是直接申请一个页的空间,存储分配是按页对齐,就实现了一个节点一次I/O。
4、B+ 树是平衡树,它查找任意节点所耗费的时间都是完全相同的,比较的次数就是 B+ 树的高度

B+ Tree索引和Hash索引区别?

哈希索引适合等值查询,但是无法进行范围查询 和模糊查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题

哈希索引适合等值查询,但是无法进行范围查询 和模糊查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题

索引的种类有哪些?分别的特点是什么?

普通索引:加速查询
唯一索引:加速查询 + 列值唯一 + 可以为null
主键索引:加速查询 + 列值唯一 + 不可为null + 表中只有一个
组合索引:多列值组成一个索引,专用于组合搜索,效率大于索引合并
全文索引:对文本的内容进行分词,进行搜索

普通索引:加速查询
唯一索引:加速查询 + 列值唯一 + 可以为null
主键索引:加速查询 + 列值唯一 + 不可为null + 表中只有一个
组合索引:多列值组成一个索引,专用于组合搜索,效率大于索引合并
全文索引:对文本的内容进行分词,进行搜索

不适合作为索引

更新频繁的字段不适合创建索引
不会出现在where子句中的字段

更新频繁的字段不适合创建索引
不会出现在where子句中的字段

聚簇索引和非聚簇索引的区别

在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引**

  • 在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引**
  • 聚簇索引查询会更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再进行一次查询。通过覆盖索引也可以只查询一次。**
  • 覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。**
  • 当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。**

联合索引、最左前缀匹配

创建联合索引时,会选择识别度最高的放在最左边,由于mysql遵循最左前缀匹配原则,从联合索引最左边开始匹配。创建一个联合索引(key1,key2,key3),其实创建了(key1)(key1,key2)(key1,key2,key3)三个索引。

创建联合索引时,会选择识别度最高的放在最左边,由于mysql遵循最左前缀匹配原则,从联合索引最左边开始匹配。创建一个联合索引(key1,key2,key3),其实创建了(key1)(key1,key2)(key1,key2,key3)三个索引。

索引下推、查询优化

mysql 5.6版本优化内容:Index Condition Pushdown(索引下推)默认开启,
比如:

people表中(zipcode,lastname,firstname)构成一个索引

people表中(zipcode,lastname,firstname)构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
  • 如果没有使用索引下推技术,则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断数据是否符合条件。
  • 如果使用了索引下推技术,则MYSQL首先会返回符合zipcode=’95054’的索引,然后根据lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。

如果对大家有帮助,请大家多点赞。。。

本文作者:好名字
原文链接:https://www.cuizb.top/article/1637767177
版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。

本文作者:好名字
原文链接:https://www.cuizb.top/article/1637767177
版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。

————————

< strong > briefly describe the index: < / strong >

Index is a data structure that sorts the values of one or more columns in a database table; Indexes are divided into clustered indexes and non clustered indexes. Clustered indexes query directories similar to books and quickly locate the searched data. Non clustered index queries generally need to be queried back to the table again. If the index is not used, the whole table will be scanned; In addition, multiple fields can form a joint index, but it should meet the requirements of the leftmost matching principle.

Index is a data structure that sorts the values of one or more columns in a database table; Indexes are divided into clustered indexes and non clustered indexes. Clustered indexes query directories similar to books and quickly locate the searched data. Non clustered index queries generally need to be queried back to the table again. If the index is not used, the whole table will be scanned; In addition, multiple fields can form a joint index, but it should meet the requirements of the leftmost matching principle.

< strong > if you use an overlay index, you can not scan back to the table
Index type: < strong > InnoDB engine, default B + tree (O (logn)), hash index B tree index o (1) < / strong >

1. Because the bottom layer uses hash table and stores it in key value, it is impossible to query directly through the index. Selecting only one data hash index is faster. However, if n pieces of data are selected, the time complexity of hash index is O (n). Because the B + tree index is orderly and the leaf nodes are connected by linked list, the query efficiency is faster than hash index
2. The index is saved on the hard disk and generally will not be saved to the memory at one time. The B + tree can be designed to allow data to be loaded in batches. At the same time, the height of the tree is low and the query speed is fast
3. The I / O speed of the hard disk is very slow compared with that of the memory, and the index is used to speed up the query speed. It is necessary to reduce I / O operations. The memory and disk exchange data in pages. In order to reduce I / O, when the index creates a new node, it directly applies for the space of one page, and the storage allocation is aligned according to pages, so one-time I / O of a node is realized.
4. B + tree is a balanced tree. It takes exactly the same time to find any node. The number of comparisons is the height of B + tree

1. Because the bottom layer uses hash table and stores it in key value, it is impossible to query directly through the index. Selecting only one data hash index is faster. However, if n pieces of data are selected, the time complexity of hash index is O (n). Because the B + tree index is orderly and the leaf nodes are connected by linked list, the query efficiency is faster than hash index
2. The index is saved on the hard disk and generally will not be saved to the memory at one time. The B + tree can be designed to allow data to be loaded in batches. At the same time, the height of the tree is low and the query speed is fast
3. The I / O speed of the hard disk is very slow compared with that of the memory, and the index is used to speed up the query speed. It is necessary to reduce I / O operations. The memory and disk exchange data in pages. In order to reduce I / O, when the index creates a new node, it directly applies for the space of one page, and the storage allocation is aligned according to pages, so one-time I / O of a node is realized.
4. B + tree is a balanced tree. It takes exactly the same time to find any node. The number of comparisons is the height of B + tree

< strong > what is the difference between B + tree index and hash index

Hash index is suitable for equivalent query, but range query and fuzzy query cannot be performed
Hash index cannot use index to complete sorting
Hash indexes do not support leftmost matching rules for multi column federated indexes
If there are a large number of duplicate key values, the efficiency of hash index will be very low, because there is a hash collision problem

Hash index is suitable for equivalent query, but range query and fuzzy query cannot be performed
Hash index cannot use index to complete sorting
Hash indexes do not support leftmost matching rules for multi column federated indexes
If there are a large number of duplicate key values, the efficiency of hash index will be very low, because there is a hash collision problem

< strong > What are the types of indexes? What are the characteristics

General indexes: accelerating queries
Unique index: accelerated query + unique column value + nullable
Primary key index: accelerated query + unique column value + non nullable + there is only one in the table
Combined index: multiple column values form an index, which is dedicated to combined search and is more efficient than index merging
Full text index: word segmentation and search for the content of the text

General indexes: accelerating queries
Unique index: accelerated query + unique column value + nullable
Primary key index: accelerated query + unique column value + non nullable + there is only one in the table
Combined index: multiple column values form an index, which is dedicated to combined search and is more efficient than index merging
Full text index: word segmentation and search for the content of the text

< strong > not suitable for index < / strong >

Frequently updated fields are not suitable for index creation
Fields that do not appear in the where clause

Frequently updated fields are not suitable for index creation
Fields that do not appear in the where clause

< strong > difference between clustered index and non clustered index < / strong >

In InnoDB, the leaf node of index B + tree stores the whole row of data, which is the primary key index, also known as the cluster index. The leaf node of index B + tree stores the value of the primary key, which is a non primary key index, also known as a non clustered index**

  • In InnoDB, the leaf node of index B + tree stores the whole row of data, which is the primary key index, also known as the cluster index. The leaf node of index B + tree stores the value of the primary key, which is a non primary key index, also known as a non clustered index**
  • Cluster index query will be faster, because the leaf node of the primary key index tree is directly the whole row of data we want to query. The leaf node of the non primary key index is the value of the primary key. After finding the value of the primary key, you need to query again through the value of the primary key. You can also query only once by overwriting the index**
  • Covering index means that the execution of a query statement can be obtained only from the index without reading from the data table. It can also be called index coverage**
  • When a query statement meets the conditions of overwriting the index, MySQL can return the data required by the query only through the index, which avoids the operation of returning to the table after finding the index, reduces I / O and improves efficiency**

< strong > union index, leftmost prefix matching < / strong >

When creating a federated index, the one with the highest recognition will be placed on the leftmost. Since MySQL follows the leftmost prefix matching principle, matching starts from the leftmost side of the federated index. Create a joint index (key1, key2, Key3). In fact, three indexes (key1) (key1, key2) (key1, key2, Key3) are created.

When creating a federated index, the one with the highest recognition will be placed on the leftmost. Since MySQL follows the leftmost prefix matching principle, matching starts from the leftmost side of the federated index. Create a joint index (key1, key2, Key3). In fact, three indexes (key1) (key1, key2) (key1, key2, Key3) are created.

< strong > index push down and query optimization < / strong >

MySQL version 5.6 optimization content: the index condition pushdown is enabled by default,
For example:

The people table (zipcode, LastName, firstname) forms an index

The people table (zipcode, LastName, firstname) forms an index

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
  • If the index push down technology is not used, MySQL will query the corresponding data from the storage engine through zipcode =’95054 ‘and return it to the MySQL server. Then the MySQL server will judge whether the data meets the conditions based on LastName like’% etrunia% ‘and address like’% Main Street% ‘.
  • If the index push down technology is used, MySQL will first return the index that meets zipcode =’95054 ‘, and then judge whether the index meets the conditions according to LastName like’% etrunia% ‘and address like’% Main Street% ‘. If the conditions are met, locate the corresponding data according to the index. If not, reject it directly. With index push down optimization, you can reduce the number of table returns when there are like conditional queries.

If it is helpful to you, please praise more…

Author: good name
Original link: https://www.cuizb.top/article/1637767177
Copyright notice: unless otherwise stated, all articles on this blog are licensed under CC by 3.0 CN agreement. For reprint, please sign the author and indicate the source of the article.

Author: good name
Original link: https://www.cuizb.top/article/1637767177
Copyright notice: unless otherwise stated, all articles on this blog are licensed under CC by 3.0 CN agreement. For reprint, please sign the author and indicate the source of the article.