mysql索引学习｜豆包MarsCode AI 刷题索引是什么？索引是存储引擎用于快速查找数据记录的一种数据结构，

索引是什么？

索引是存储引擎用于快速查找数据记录的一种数据结构，在数据库中，索引表一般包含的是查询关键字（索引项）及其对应的数据区的地址，并且索引表一般按照关键字排序方便快速查找。

举一个例子：

大家都用新华字典，新华字典查字是怎么查的？可以通过拼音也可以通过偏旁部首，这些偏旁部首和拼音的目录就可以简单理解为现实中的索引。

索引加速查询的本质

数据是存储存储在磁盘上的，每一行数据都有其对应的磁盘地址，如果没有索引的话，每当需要找一条数据的时候就需要对这个磁盘中的所有数据进行遍历（因为每一条数据本身大多数都是无序的），当数据量较小的时候，问题可能没那么大，但是但数据量较大的时候，遍历所花费的时间就比较大了，这个时候就需要建立索引。

索引建立之后查询的过程就变成了先根据索引中的有序结构，只需查找索引中特定的值或者范围，然后直接定位到实际储存数据的位置，再进行查询自己想要的数据。

索引的优缺点以及隐患

优点：

检索较为迅速，减少I/O次数，根据索引的分组和排序可以加快分组和排序

缺点：

占内存：

索引本身也是一个基本表（列），所以也需要存储空间，加大整个数据库的冗余度。

耗时间：

索引本身也是表，所以也需要维护，当对于经常修改的表建立索引的时候，每次插入删除更新时，对应索引也要进行更新和维护，这样就加大了时间的消耗。

隐患：

全表扫描更高效

当表中的数据较小的时候，建立索引和不建立索引的查询效率的差异几乎没有区别，甚至建立索引的查询更加缓慢，并且需要维护，这个时候建立索引就不太明智了。

使用了不合理的索引结构

在建立索引的时候要考虑对应数据的特性来选取索引结构，例如哈希索引适合等值查询，而B-Tree索引适合范围查询。如果使用了错误的索引类型，可能不会看到性能提升。

维护开销

这个就和索引耗时间的情况一致，当插入，删除，修改基本表中数据的同时也要修改索引表，增加时间开销。

索引碎片化

在对索引表进行插入和删除的时候可能会造成索引的碎片化。（由于数据删除后在数据文件中留下的不连续空白空间，以及新数据插入时利用这些空白空间造成的数据存储位置不连续）Mysql在对数据删除的过程中，不是直接将数据删除，而是在这个数据上打上一个tag，代表已经删除，在数据查询的时候仍然会对其进行查询（判断一下tag）,当其过多时，时间消耗就会变大，在插入的时候，当对应的空间位置不够的时候，就会重新找另一个位置，那么这个位置就被空下来了，导致索引页分裂，造成空间和时间的浪费。

锁和并发

在高并发的情况下，索引可能会成为争抢的资源，导致锁争用和性能下降。

建立了索引一定会被用到吗？

不一定，因为Mysql的查询是根据成本来选择索引方式或者是否使用索引，比如这个表格：

 CREATE TABLE `orders` (

  `O_ORDERKEY` int NOT NULL,

  `O_CUSTKEY` int NOT NULL,

  `O_ORDERSTATUS` char(1) NOT NULL,

  `O_TOTALPRICE` decimal(15,2) NOT NULL,

  `O_ORDERDATE` date NOT NULL,

  `O_ORDERPRIORITY` char(15) NOT NULL,

  `O_CLERK` char(15) NOT NULL,

  `O_SHIPPRIORITY` int NOT NULL,

  `O_COMMENT` varchar(79) NOT NULL,

  PRIMARY KEY (`O_ORDERKEY`),

  KEY `idx_custkey_orderdate` (`O_CUSTKEY`,`O_ORDERDATE`),

  KEY `ORDERS_FK1` (`O_CUSTKEY`),

  KEY `idx_custkey_orderdate_totalprice` (`O_CUSTKEY`,`O_ORDERDATE`,`O_TOTALPRICE`),

  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`O_CUSTKEY`) REFERENCES `customer` (`C_CUSTKEY`)) 
  
  ENGINE=InnoDB

这个时候，假如我们要查询o_custkey这个字段，Mysql该如何选取呢？这个就与数据库的优化器有关，而优化器的选择是基于成本来进行的，哪一个索引成本低，就使用哪一个索引.

SQL优化器会分析全部可能执行的计划，选择成本最低的执行，这种优化器被称为CBO，在Mysql中一条SQL的计算成本如下所示：

Cost  = Server Cost + Engine Cost

      = CPU Cost + IO Cost

CPU Cost指的是计算的开销，比如排序，索引值的比较等，这些在Server层完成。

IO Cost指的是读取内存IO的开销和读取磁盘IO的开销。

看一下基本的开销（Server,CPU）

从中可以看出，数据在磁盘和在内存之间的创建的成本差异非常大，但是记录的比较和索引值的相比来说就比较小。

disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。
disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5。
key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。
memory_temptable_create_cost：创建内存临时表的成本：默认为1。
memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1。
row_evaluate_cost：记录间的比较成本，默认为0.1。

(Engine IO)

io_block_read_cost：从磁盘读取一个页的成本，默认值为1。
memory_block_read_cost：从内存读取一个页的成本，默认值为0.25。

当然，Mysql提供了显示成本的关键字，就是EXPLAIN关键字，可以帮助我们看到在执行一条SQL语句之后所花的成本。

EXPLAIN FORMAT=json 

SELECT o _custkey, SUM (o_ totalprice) 

FROM orders GROUP BY o _custkey

结果的一部分

           "read_cost": "49024.00", # IO Cost(Engine Cost)

           "eval_cost": "577875.50", # CPU Cost(Server Cost)

           "prefix_cost": "626899.50", # 总成本

选取结果的一部分：

read_cost 表示就是从 InnoDB 存储引擎读取的开销；
eval_cost 表示 Server 层的 CPU 成本；
prefix_cost 表示这条 SQL 的总成本；

SELECT * FROM orders

WHERE o_orderdate > '1994-01-01' and o_orderdate < '1994-12-31';

SELECT * FROM orders 

WHERE o_orderdate > '1994-02-01' and o_orderdate < '1994-12-31';

EXPLAIN FORMAT=tree 

SELECT *  FROM orders  WHERE o_orderdate >  '1994-01-01'   AND o_orderdate <  '1994-12-31' \G   ***************************   1.   row   **************************  *

EXPLAIN: -> Filter: ((orders.O _ORDERDATE >  DATE '1994-01-01' ) and (orders.O_ ORDERDATE < DATE'1994-12-31'))  (cost=592267.11 rows=1876082)

    -> Table scan on orders  (cost=592267.11 rows=5799601)

EXPLAIN FORMAT=tree 

SELECT *  FROM orders FORCE INDEX(idx_orderdate)  WHERE o_orderdate >  '1994-01-01'   AND o_orderdate <  '1994-12-31' \G   ***************************   1.   row   **************************  *

EXPLAIN: -> Index range scan on orders using idx _orderdate, with index condition : ((orders.O_ ORDERDATE > DATE'1994-01-01') and (orders.O _ORDERDATE <  DATE '1994-12-31' ))  (cost = 844351.87  rows = 1876082 )

索引分类

逻辑功能划分

普通索引，最基础的索引，允许空值和重复值，没有限制。

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

ALTER TABLE users ADD INDEX idx_username (username);

这个地方的idx_department就是一个普通索引

唯一索引：相应列的值在表中必须是唯一的

CREATE UNIQUE INDEX idx_email ON users(email);

ALTER TABLE users ADD UNIQUE INDEX idx_username (username);

UNIQUE INDEX就是唯一索引的关键字

主键索引：相应列的值唯一且非空

CREATE TABLE employees (     
id INT PRIMARY KEY,     
name VARCHAR(50),     
department VARCHAR(50),     
salary DECIMAL(10, 2) 
);

或者
ALTER TABLE employees ADD PRIMARY KEY (employee_id);

4. 全文索引

全文索引是Mysql中特有的一种特殊索引，可以通过关键字来进行匹配来进行查询过滤。可以说和LIKE很相似，LIKE仅适用于文本比较少的情况，但是当需要大量文本数据检索，全文索引比LIKE快很多倍，例子：

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
)
或者
create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

使用全文索引

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

match()返回的可以是Boolean值（在不在）以及匹配度。

物理分类

聚簇索引

表数据按照索引的顺序来存储。即索引的叶子节点包含了完整的数据行。（并且数据在物理层面是相邻的）

非聚簇索引

索引和数据是分开存储的，索引的叶子节点存储的是指向数据行的指针。

聚簇索引的优点：

查找数据更快：由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

聚簇索引适合用在排序的场合，非聚簇索引不适合：

聚簇索引中，数据是按照索引键的顺序物理存储的。这意味着，如果按照聚簇索引的键进行排序查询，数据库不需要额外的排序操作，因为数据本身已经是有序的。

作用字段个数划分

单列索引就是索引只包含一个列
组合索引

组合索引是一种索引类型，它包含多个列的值。与单列索引不同，组合索引将多个列的值组合在一起作为索引键，以提高多列查询的效率。

CREATE INDEX idx_abc ON my_table (A, B, C);

注意你建立索引的顺序，搜索时按照A-B-C进行，因为组合索引支持“最左前缀”匹配，即在查询中必须包含索引的最左列，才能有效利用该索引。

常见的索引结构

B树

B树是一种平衡的多路查找树

特点：

所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）；
在关键字全集内做一次查找,性能逼近二分查找；

B+树

B+树是B树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
为所有叶子结点增加了一个链指针

B树和B+树的区别

B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

R树

R树在我看来就像是B+树的二维显示，就是用矩形面积来表示

哈希表

直接通过哈希函数来找到存储地址

地址index=H（key）

说白了，hash函数就是根据key计算出应该存储地址的位置，而哈希表是基于哈希函数建立的一种查找表

哈希函数

直接定法

哈希函数为关键字的线性函数如 H（key）=a*key+b，这种构造方法比较简便，均匀

除留余数法

取关键字被某个不大于散列表长度 m 的数 p 求余，得到的作为散列地址。

即 H(key) = key % p, p < m。

平方取中法

先计算出关键字值的平方，然后取平方值中间几位作为散列地址。（解决hash冲突）

数字分析法

分析一组数据，比如一组员工的出生年月日，这时我们发现出生年月日的前几位数字大体相同，这样的话，出现冲突的几率就会很大，但是我们发现年月日的后几位表示月份和具体日期的数字差别很大，如果用后面的数字来构成散列地址，则冲突的几率会明显降低。因此数字分析法就是找出数字的规律，尽可能利用这些数据来构造冲突几率较低的散列地址。

解决hash冲突的方法

当两个不同的数经过哈希函数计算后得到了同一个结果，即他们会被映射到哈希表的同一个位置时，即称为发生了哈希冲突。

开放定址法

线性探测法

位置被占，就+1，并对m取模，判断是否为空，空就写入，否则就+2。（每次多加1）

h(x)=(Hash(x)+i)mod (Hashtable.length);

平方探测法

h(x)=(Hash(x) +i)mod (Hashtable.length)，i依次为+(i^2)和-(i^2)

再哈希法

同时构造多个不同的哈希函数，等发生哈希冲突时就使用第二个、第三个……等其他的哈希函数计算地址，直到不发生冲突为止。

链地址法

h(x)=xmod(Hashtable.length);

跳表

跳表全称为跳跃列表，它允许快速查询，插入和删除一个有序连续元素的数据链表。简单理解就是跳表是基于链表实现的有序列表，跳表通过维护一个多层级的链表实现了快速查询效果

Mysql InnoDB和MyISAM之间的区别

InnoDB与MyISAM是mysql中两种不同的存储引擎

MyISAM存储引擎中，索引和数据是分开存储的，数据是无序排放的，这种无序的排放的结构就是堆表结构。在MyISAM中的索引都是非聚簇索引。

索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新。

InnoDB存储引擎中，索引和数据是一起存储的，所以数据按照索引的顺序排放，这个就是索引组织表的结构，在索引组织表中，数据即索引，索引即数据。

二级索引

在InnoDB中除了主键索引之外，都称为二级索引或者非聚簇索引，在查询的时候，就是先通过二级索引查到对应的主键，再通过主键查询来找到对应的数据，这种操作成为“回表”

索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。

mysql索引学习 ｜ 豆包MarsCode AI 刷题