MySQL索引

陈岂几真幸运

已于 2023-09-26 16:22:23 修改

阅读量72

点赞数

分类专栏： MySQL 文章标签：数据库 mysql c++ sql

于 2023-04-25 21:55:42 首次发布

本文链接：https://blog.csdn.net/super8ayan/article/details/130374071

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

使用EXPLAN命令可以查看SQL语句的执行计划

MyISAM的主键索引和二级索引树（非聚集数据和索引不一起）

InnoDB的主键索引和二级索引树（聚集数据和索引在一起）

哈希索引

索引常见问题

当表中的数据量很多，达到几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时需要索引来加速SQL查询。

索引也是需要存储成索引文件的，因此索引的使用也会涉及到磁盘IO。如果索引创建过多，使用不当反而回导致大量无用磁盘IO适得其反，所以需要具体问题具体分析，掌握良好的索引创建原则。

索引优缺点：

索引是创建在表上的，对数据库表中一列或者多列的值进行排序的一种结果。

优点：提高查询效率；

缺点：索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重。

索引分类：

物理上：

聚集索引：

非聚集索引：（二级索引）

聚集索引和非聚集索引区别：

通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。聚集索引和非聚集索引的根本区别是表记录的排序和索引的排序是否一致。

聚集索引（innodb）的叶节点就是数据结点，而非聚集索引（MyisAM）的叶节点任然是索引节点，只不过包含一个指向对应数据块的指针。

逻辑上：

主键索引：主键索引是一种唯一的索引，用于加速对表中主键的查询。使用Primary Key修饰的字段主键索引中的每个值都必须是唯一的。一张表的依次sql查询只能用一个索引。
唯一索引：唯一索引是一种唯一的索引，用于加速对表中某个唯一列的查询。使用UNIQUE修饰的字段，不能重复，唯一索引中的每个值都必须是唯一的。
普通索引：普通索引是一种最基本的索引类型，用于加速对表中某个列的查询。它适用于查找频繁但修改不频繁的列。普通索引中的每个值都可以重复（一张表的一次sql查询只能用一个索引）
全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR、VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度。
单列索引：在一个字段上创建索引
多列索引：联合索引在表的多个字段上创建索引（uid+cid，多列索引必须使用到第一个列）

索引创建和删除

创建表的时候指定索引字段：

CREATE TABLE index1 (id INT,

name VARCHAR(20),

sex ENUM('male', 'female'),

INDEX(id,name));

在已经创建的表上添加索引：

CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

删除索引:

DROP INDEX 索引名 ON 表名;

⭐创建索引时常注意事项（部分）：

经常作为where条件过滤的字段考虑添加索引；
字符串列创建索引时，尽量规定索引长度，不能让索引值的长度key_len过长（字符串类型的列包含的字符串长度是不固定的，如果不指定索引长度，MySQL会使用整个列的长度来创建索引，会使索引的长度变得非常大key_len也会变得很长，这会影响索引的效率和使用，甚至会导致索引失效。）
索引字段涉及类型强制转换、mysql函数调用，表达式计算等 索引失效。

使用EXPLAN命令可以查看SQL语句的执行计划

主要查看：

查询的类型（select_type）：查询分为简单查询、联合查询、子查询等，不同的查询类型会影响执行计划。
table：查询涉及到的表名称
表的访问方式（type）：MySQL查询时会选择不同的访问方式，包括ALL（全表扫描）、index（索引扫描）、range（范围扫描）等，不同的访问方式对于数据量的大小、索引的使用等都有很大的影响。
possible_keys：可以使用的索引名称，可以有多个
实际使用的索引名称（key）：查询时是否使用了索引，以及使用了哪些索引。
匹配的行数：查询可能会扫描几行数据，这个值越小越好。
不匹配的行数 ：查询中可能会过滤掉一些行，这个值越小越好。
额外的信息 (Extra)： 查询过程中额外的信息，包括使用的索引、排序方式、连接类型等，可以帮助我们更好地了解查询的执行过程。

索引的底层实现原理

小林coding写的很好：女朋友问我：为什么 MySQL 喜欢 B+ 树？我笑着画了 20 张图 (qq.com)

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘块（对应索引树的节点），索引树越低，越“矮胖”，磁盘IO次数就少。

在数据库系统中，数据通常存放在磁盘上，而内存的访问速度远快于磁盘因此在进行数据查询时，需要将数据从磁盘读取到内存中，以提高查询效率。

B+树是一种经典的索引数据结构，它可以快速地定位到符合条件的数据记录，避免了全表扫描和顺序扫描的情况，从而大大减少了磁盘IO的访问次数。具体来说，当需要访问某个数据记录时，数据库系统会先通过B+树的索引结构，快速定位到对应的数据页，然后将该页从磁盘读取到内存中，进行数据查询操作。如果数据页已经在内存中，就可以直接访问内存中的数据，避免了磁盘IO的访问。如果数据页不在内存中，就需要从磁盘上读取数据到内存中，然后进行数据查询操作。

索引的底层原理B+树，涉及磁盘到内存上的读取都会用到B+树，主要就是他是一个平衡树，能够对数据排序，加速搜索，B树的一个节点存放的数据比较多，最好的情况下是一次磁盘IO加载的磁盘块的数据刚好放到一个B+树的节点上，B+树这个m值（m阶平衡树）取合适值得话，存储千万级得数据几乎不会超过三层，花费三次磁盘就行了。

B树：

B树缺点：

索引+数据内容分散在不同节点上，离根节点近，搜索就快；离根节点远，搜索就慢！花费的磁盘IO次数不平均，每一行数据搜索花费的时间也不平均；
每一个节点中由key也有data，但是每一个节点的大小是有限的，如果data数据较大时会导致每个节点能存储的key值数据很小
B树不方便做范围搜索、整表遍历操作不方便

B+树

B+树每一个非叶子节点只存放Key，不存放data：好处就是每个节点能够存放的key值更多，B+树在理论上来说层数会更低一些，搜索的效率会更高一些。
B+树叶子节点上存储了所有的索引值和其对应的数据data：搜索每一个索引对应的值data，都要跑到叶子节点上，这样每一行记录搜索的时间是非常平均的！
叶子节点被串在一个链表中，形成一个有序的链表，如果要进行索引树的搜索&整表搜索，直接遍历叶子节点的有序链表即可！或者做范围查询时直接遍历叶子节点的有序链表