MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布

蓝绿色~菠菜

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量1k

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/bocai_xiaodaidai/article/details/101675116

版权

Mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

索引是一种高效获取数据的存储结构，例：hash、二叉、红黑。

SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。
MySQL 索引类型有：唯一索引，主键（聚集）索引，辅助（非聚集）索引，全文索引。

Myisam引擎(非聚集索引)

索引数据结构为B + 树

若以这个引擎创建数据库表Create table user （…..），它实际是生成三个文件：

user.myi 索引文件 user.myd数据文件 user.frm数据结构类型。

当我们执行 select * from user where id = 1的时候，它的执行流程。

　　(1)查看该表的myi文件有没有以id为索引的索引树。

　　(2)根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。

　　(3)根据数据地址去myd文件里面找到对应的数据返回出来。

Innodb引擎(聚集索引)

索引数据结构为B + 树

若以这个引擎创建数据库表Create table user （…..），它实际是生成两个文件：

user.ibd 索引文件 user.frm数据结构类型

因为innodb引擎创建表默认就是以主键为索引，所以不需要myi文件。

它与myisam最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息)

若此时，你在其他列创建索引例如name，它就会另外创建一个以name为索引的索引树（辅助索引），(叶子节点存的是索引和主键索引)。

你在执行select * from user where name = ‘吴磊’，他的执行过程如下：

　　(1)找到name索引树

　　(2)根据name的值找到该树下叶子的name索引和主键值

　　(3)用主键值去主键索引树去叶子节点到该条数据信息

聚集索引特点：

(1)聚集索引就是叶子节点的顺序和物理存储的顺序是一样的，所以范围查找的时候效率很高，但是DML操作的时候，为了维护物理存储的顺序和叶子节点一样，涉及到大量的数据位移调整。

(2)聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键

MyISAM引擎和InnoDB引擎的区别

MyISAM：支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
InnoDB：5.6以后才有全文索引;支持事务；它是行级锁;不会保存表的具体行数.
一般：不用事务的时候,count计算多的时候适合myisam引擎。对可靠性要求高就是用innodby引擎。推荐用InnoDB引擎.

为啥 MyISAM 就可以直接保存表具体行数呢？因为它不支持事务啊，不用担心数据不一致的问题。

加了索引之后能够大幅度的提高查询速度，但是索引也不是越多越好，一方面它会占用存储空间，另一方面它会使得写操作变得很慢。通常我们对查询次数比较频繁，值比较多的列才建索引。
例如：

select * from user where sex = "女"，这个就不需要建立索引，因为性别一共就两个值，查询本身就是比较快的。
　　　select * from user where user_id = 1995 ,这个就需要建立索引，因为user_id的值是非常多的。

为什么Mysql不选择Hash索引？

Hash索引的优势是精确查找的话，速度会更快，为什么不选择Hash索引

Hash索引不适合范围查找，而B+树特别适合范围查找（特别是聚簇索引的时候）
Hash索引每次查询要加载所有的索引数据到内存当中，而B+树只需要根据匹配规则选择对应的叶子数据加载即可
另外B+树引入了缓存机制和数据页技术来提升性能（不过理论上来说，这两个特性Hash索引也可以实现）

为什么Mysql不选择B树索引？

B+树的层级更少：b树每个节点都存储索引值和数据，而b+树只有叶子结点存储数据或者主键索引值，mysql索引以数据页形式存储，默认每个页（节点）大小16kb，因而b+树一个节点可以存储更多的索引值使得树变得矮胖，减少节点读取次数，进而提高速度。
B+树天然具备排序功能：b+树叶子节点是有序的，即双向链表，叶子结点是自增索引，可以进行范围查找。也正因如此，b+树的插入和修改操作不如b树快，因为要不断调整顺序。
B+树查询速度更稳定：B+所有数据都存在叶子节点上，所以每次查找的次数都相同，查询速度要比B树更稳定;
B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。
B树相对于B+树的优点是：如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比B+树快。