MySql数据库索引相关知识

简介：
索引是一种数据结构。用于提高数据查询速度和性能。索引的底层实现主要涉及到数据结构的应用。mysql主要支持两种类型的索引**B+tree（B+tree是一种多路搜索树，每个节点包含关键字以及指向节点的指针）、hash表（hash表索引则是通过哈希函数将键值映射到数组索引上，从而实现查找）**索引。

分类：
【普通索引】：
最基本的索引类型，没有任何限制。create index index_name on table_name(column_name)
唯一索引：
又称为关键词索引，与普通索引类似，但是索引列的值必须唯一，且不能为空。适用于需要确保某一列数据唯一且不为空的场合。create unique index index_name on table_name(column_name)
【主键索引】：
一张表中只能有一个主键索引（与唯一索引的区别），同样是索引值必须唯一，且不能为空。alter table table_name add primary key(column_name)
【组合索引】：
给表的多个字段创建索引，必须遵循“最左前缀匹配”原则(即对应的sql要命中这个索引，对应的查询条件需要与组合索引的字段顺序保持一致,可以不是完全匹配，但是第一个条件需要与第一个索引字段匹配，不然会无法命中索引)。create index index_name on table_name(column1,column2)
【全文索引】：
全文索引的核心在于倒排索引结构，能够极大的提升检索效率。创建全文索引时需要注意只有特定的数据类型才支持：char、varchar、text及其系类列（如tinytext、mediumtext等）的字段可以建立全文索引。全文索引并不是给表中所有字段加上索引。全文索引比传统的like操作符效率更高。create fulltext index_name on table_name(column1,cloumn2),全局索引通常与match against 语法一起使用。基础用法如下：selec * from table_name where match(column1,cloumn2) against ("想要匹配的内容")该语句会将字段1、字段2中与想要匹配内容的值返回出来。全局索引还可以使用修饰符完成其它功能。感兴趣的可以去查一下相关资料。全局索引适用于需要根据文本内容进行搜索的场景，如文章、报告或者长段文本内容。

索引优缺点：
【优点】：
1.提高查询效率：在插入时对数据进行了排序，加快查询效率
2.提高数据操作效率：暂停索引，可以在一定程度提高数据插入、更新、删除等任务的维护效率。
3.降低IO开销：通过索引减少了对磁盘的随机访问次数，索引支持直接定位到数据位置，从而降低IO开销。
4.改善整体系统性能：适当索引可以提高整个数据库性能，包括检索、更新等
5.加速表之间的连接：在处理大量数据时，使用索引能够优化连接算法、减少扫描的数据量提高数据的定位速度，提升查询性能和响应时间。
【缺点】：
1.占用空间：索引需要占用物理存储空间，每个索引会占据一定的磁盘空间。
2.维护成本：创建维护索引需要时间和资源，表中数据更新之后（插入、更新、删除）索引也需要更新
3.可能降低读写操作性能：每次数据更新都需要更新索引、
4.可能导致系统性能下降：经常在全表扫描的情况下使用索引、或者在频繁变更的列上创建索引。
5.不等于操作符限制：在查询语句中使用了不等于（！=）操作符会限制索引使用，从而导致全表扫描，即使该比较字段上有索引。
其它常见问题：

【查看执行的语句是否命中了索引】：
使用 explain select *from table_name where column_1 ="xxx" 可以显示mysql如何执行了查询，包括它是否利用了索引、数据读取操作的类型以及表之间的引用等信息。可以通过分析输出结果type列的值来判断索引是否被使用或者是否要优化查询。type值显示为ref、eq_ref或者range，通常意味着索引被使用了，如果是all或者index，则可能进行全表扫描或者全索引扫描，这可能就需要进行优化。
【b+tree索引、B-tree索引与hash表索引的区别】
1.在mysql中b+tree与b-tree索引通常被认为是相同的，主要是因为MySQL的存储引擎InnoDB和MylSAM主要是使用b+tree结构来构建索引，没有单独的b-tree索引类型。
2.hash表索引查询数据主要是通过hash函数将键值映射到数组上从而实现数据查找，b+tree则是先找到关键词，然后通过关键词找到存储的叶子结点指针，然后从叶子节点找到对应的数据。hash表只能查询满足“=”的数据，不能实现范围数据查找，在一定程度上hash表索引速度比b+tree索引查询更快。

【Innodb为什么要用自增id作为主键】
1.保证整型自增id是递增的，避免主键冲突。
2.提高查询效率，mysql写满一页之后进行分页时，可以接着上一页结束的id继续自增
3.保证索引上的数据比较紧凑，提高空间利用率减少数据分页合并等操作
4.减少事务回滚的影响，自增id锁不是一个事务锁，使用完成之后会立即释放。可以减少因唯一主键冲突和事务回滚导致的自增主键id不连续的情况。
【什么是一级索引、二级索引】
一级索引被称为聚集索引或者主键索引，索引与数据存储在同一个b+tree的叶子节点（非叶子节点只存储包含索引的关键字和指向叶子节点的指针）；二级索引被称为非聚集索引或辅助索引，二级索引的叶子节点存储的是主键而不是数据。一个表只能有一个一级索引，可以有多个二级索引。
【暂停、启动索引】
当有大批量数据变更时，可以通过语句alter table table_name disable keys;暂停对应表中所有的索引；数据操作完之后，通过语句alter table table_name enable keys 重启索引。

【非聚簇索引一定会回表查询吗】
当想要查询的字段未能全部命中索引，不管是非聚簇、聚簇索引都需要回表查询；查询的字段全部命中索引之后，不会回表查询
【覆盖索引是什么】
想要查询的字段全部建立了索引，在查询时，不用回表查询，这样的索引被称为覆盖索引
【联合索引是什么?为什么需要注意联合索引中的顺序】
联合索引是针对多个字段建立的索引，用于提高查询效率。注意顺序是因为联合索引需要遵循“最左前缀匹配”原则。

                                                                                   学海无涯