mysql数据库索引 MyISAM 索引与InnoDB 索引

mysql基础操作，与一般的sql数据库一样，是增删查改

简单的学习菜鸟:MySQL 教程 | 菜鸟教程

一、什么是索引

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

简单理解就是重新建一个目录，到时候查这目录’然后再返回去找原先那个数据表。可以快速增加查询速度，但会减慢增删改操作。

二、索引的原理

 目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。

早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种

BTREE有以下特点：

-      不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快

-      叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据

-     通过中序遍历，可以访问树上所有节点

B+TREE改进点及优势所在：

-      仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样

-      叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到

-      范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）

-      叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储

-     非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的。

MyISAM 索引实现 

MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址

可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。

辅助索引 

在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复

MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。

InnoDB 索引实现 

InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。

1.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

2.InnoDB 的所有辅助索引都引用主键作为 data 域。

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

 ps:为什么不建议使用过长的字段作为主键?

 因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

MyISAM 索引与 InnoDB 索引的区别

 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。 但InnoDB 表数据文件本身就是主索引   叶节点包含了完整的数据记录。

聚簇索引与非聚簇索引

InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:
第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。
第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点
的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。

主要区别:

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
• 非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

三、索引的使用情况

Cardinality 索引基数

索引基数简单的说就是:你索引列的唯一值的个数，如果是复合索引就是唯一组合的个数。这个数值将会作为MySQL优化器对语句执行计划进行判定时依据。如果唯一性太小，那么优化器会认为这个索引对语句没有太大帮助，而不使用索引。cardinality值越大，就意味着，使用索引能排除越多的数据，执行也更为高效。

有时候记录更新，但索引基数没有更新，需要使用analyzetable更新索引基数。

ps：当MySQL数据库做FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE这些操作时，会导致需要关闭内存中的表，并重新打开表，加载新的表结构到内存中。但是关闭表，需要等待所有的在这个表上的操作执行结束(包括select,insert,update,lock table等)，所以当有一个特别慢的select一直在执行时，analyze table命令就一直无法结束。

联合索引及最左原则

假设A、B、C三个字段索引按A+B+C顺序创建的索引：

A --走索引

B --不走索引

C --不走索引

A + B 或 B + A -- 走索引

B + C 或 C + B -- 不走索引

A + B + C 或 B + C + A 或 C + B + A --走索引

四、索引的类型

UNIQUE唯一索引 该索引其含义是被标定义唯一索引的列，不允许出现重复的数据， 但可以有NULL值。

INDEX普通索引允许出现相同的索引内容，平时创建的索引通常就是普通索引，利用提升查询数据性能

PRIMARY KEY主键索引 不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引，常见于ID字段

fulltext index 全文索引 上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam引擎以及英文支持,并且效率让人不敢恭维,要全文搜索还是建议使用Luence、Solr、ES等方案，更专业且强大一些。
 

五、查看索引是否使用

 Mysql 中 explain可以查询索引使用情况

参考

https://www.jianshu.com/p/d95d6d692e2c

什么是 MySQL 索引?_程序员小灰的博客-CSDN博客

MySQL索引实现原理分析_辛勤的搬运工_-CSDN博客_mysql索引原理

mysql analyze_MySQL数据库执行analyze采集信息_爱笑的浩哥的博客-CSDN博客