MySQL之索引(MySQL index)

一、索引的本质

索引(Index)是排好序的,能够快速查找数据的数据结构,能够帮助 MySQL 高效获取数据。

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。

索引如果没有特别指明,都是指B-Tree(多路搜索树,并不一定是二叉树)结构组织的索引。

其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引。

除了B+树索引,还有哈希索引。

二、索引的优势和劣势

优势:

  • 提高数据检索的效率,降低磁盘IO成本
  • 数据排序,降低CPU消耗

劣势:

  • 索引本质也是一张表,保存着索引字段和指向实际记录的指针,所以也要占用数据库空间,一般而言,索引表占用的空间是数据表的1.5倍
  • 索引虽然能提高查询速度,但是会降低表的更新速度,因为更新数据时,也要更新索引
————————

1、 The essence of index

The index is arranged in order, which can quickly find the data structure of data and help MySQL obtain data efficiently.

In addition to data, the database system also maintains data structures that meet specific search algorithms. These data structures refer to (point to) data in some way, so that advanced search algorithms can be realized on these data structures. This data structure is the index

Generally speaking, the index itself is also large, and it is impossible to store it all in memory. Therefore, the index is often stored on the disk in the form of index file.

If the index is not specified, it refers to the index organized by B-tree (multi-channel search tree, not necessarily binary tree) structure.

Among them, clustered index, secondary index, overlay index, composite index, prefix index and unique index all use B + tree index by default.

In addition to the B + tree index, there are hash indexes.

2、 Advantages and disadvantages of index

Advantages:

  • Improve the efficiency of data retrieval and reduce the cost of disk IO
  • Data sorting to reduce CPU consumption

inferiority:

  • The essence of an index is also a table, which stores index fields and pointers to actual records, so it also occupies database space. Generally speaking, the space occupied by an index table is 1.5 times that of a data table
  • Although the index can improve the query speed, it will reduce the update speed of the table, because the index should also be updated when updating data