MySQL索引原理与实践管理

青旅半醒 2021-12-10 10:07 294阅读 0赞

此文会更新下去，逐步完善敬请期待。索引优化数据查询是最快速有效的方式，可以通过EXPLAIN 解析查询语句是否使用索引查询过滤数据 type=ALL 表示全表查询，type=range表示走索引范围查询。

**目录**

索引

数据库索引原理

数据库索引的分类

数据库索引管理

创建索引

重建索引

查询索引

删除索引

--------------------

# 索引 #

## 数据库索引原理 ##

待续....

## 数据库索引的分类 ##

待续......

## 数据库索引管理 ##

### 创建索引 ###

索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别展示了如何创建主键索引（PRIMARY KEY），联合索引（UNIQUE）和普通索引（INDEX）的方法。

ALTER TABLE `table_name` ADD INDEX|UNIQUE|PRIMARY KEY| `index_name` (column list);
    CREATE [UNIQUE] INDEX `index_name` ON `table_name` (column_list);

### 重建索引 ###

重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建。对数据重建索引可以起到提高检索效率。注意:INNODB不支持重建索引。

![20190713165307938.png][]

REPAIR TABLE `table_name` QUICK;

什么时候需要重建索引 ？

参阅:[https://www.cnblogs.com/Alight/p/4601003.html][https_www.cnblogs.com_Alight_p_4601003.html]

索引在普遍意义上能够给数据库带来带来提升，但索引的额外开销也是不容小视的，而索引的重建也是维护索引的重要工作之一。

经过维护的索引可带来以下好处：  
1、CBO对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引。  
2、使用索引扫描的查询扫描的物理索引块会减少，从而提高效率。  
3、于需要缓存的索引块减少了，从而让出了内存以供其他组件使用。   
重建索引的原因主要包括：  
1、 删除的空间没有重用，导致 索引出现碎片  
2、 删除大量的表数据后，空间没有重用，导致 索引"虚高"  
3、索引的 clustering\_facto 和表不一致  
也有人认为当索引树高度超过4的时候需要进行重建，但是如果表数量级较大，自然就不会有较高的树，而且重建不会改变索引树高度，除非是由于大量引起的索引树“虚高”，重建才会改善性能，当然这又回到了索引碎片的问题上了。  
索引出现碎片

由于索引中只有删除和插入操作，且索引中更新完全不同于表达更新。如果索引中的 记录关键字需要更新，就需要将旧记录的位置标记为删除，并在相应的叶子节点插入新的索引纪录。这种删除标记并非真正的删除索引块中的记录，索引块中被标记 为删除的记录只有在相同索引条目插入到相同块的相同位置时才能重用。由于即使相同的索引记录也不一定插入到被删除的空间中，故如果对索引频繁进行 update和delete操作很容易导致索引出现碎片。较高的PCTFREE也容易出现索引碎片。索引的碎片也就导致了，访问索引数据时需要访问更多的 索引块  
索引虚高   
上面的说的是频繁update和delete导致索引块中有碎片，那如果进行大量的delete操作把整个索引块的数据都删了呢？索引中的索引条目仍然只 被标记为删除而没有被真正清空。设想下，如果这时候的索引关键字是一个不断增大的id，那么被标记为删除的索引条目就永远不会被重用，那树就不会不断增 长，也就出现了，表的数据空间减少了，而索引的数据空间却在不断增大的情况。由于索引的高度不断增加，访问索引数据时需要访问更多的索引块。  
clustering\_factor对 B树索引 的影响   
对于clustering\_factor来说，它是用来比较索引的顺序程度与表的杂乱排序程度的一个度量。Oracle在计算某个 clustering\_factor时，会对每个索引键值查找对应到表的数据，在查找的过程中，会跟踪从一个表的数据块跳转到另外一个数据块的次数（当 然，它不可能真的这么做，源代码里只是简单的扫描索引，从而获得ROWID，然后从这些ROWID获得表的数据块的地址）。每一次跳转时，有个计数器就会 增加，最终该计数器的值就是clustering\_factor。

### 查询索引 ###

SHOW INDEX FROM|IN `table_name`;

### 删除索引 ###

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

DROP index `index_name` ON `table_name` (column list);
    ALTER TABLE `table_name` DROP INDEX|UNIQUE|PRIMARY KEY `index_name` (column list);

索引效果：5万多数据秒出！

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70][]

注意：索引一般建1-3列，并非越多越好，时间建立索引的跨度可以用日期来做索引。

[20190713165307938.png]: /images/20211210/b62f0ce20477449d9862bfb7dd6849d4.png
[https_www.cnblogs.com_Alight_p_4601003.html]: https://www.cnblogs.com/Alight/p/4601003.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvb255YQ_size_16_color_FFFFFF_t_70]: /images/20211210/8bd13c6ee28a4b48be4d898c6948dd5e.png