MySQL索引知识整理

流浪的荒

已于 2024-08-21 22:24:46 修改

阅读量127

点赞数

文章标签：数据库 mysql 索引

于 2020-08-06 12:06:41 首次发布

本文链接：https://blog.csdn.net/qq_40640713/article/details/107836470

版权

看到一位同事说，数据库是程序的灵魂，要不都说自己是写CURD的呢，项目中用到最多的就是mysql和Redis了，今天整理一下mysql相关的知识点。

索引

什么是索引

索引类似于图书的目录，能帮助快速的查找数据库中的内容，如果没有索引的情况下找一行数据可能需要对全表进行扫描，如果有一列加了索引，可以很快的找到对应的行。
学术型描述：关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
总结：索引是一种存储结构，作用相当于图书的目录，用于快速的查找数据库中的内容。

但索引也不是越多越好，查找快要付出空间的代价，而且插入删除和修改时也会有额外的负担，同时也会增大运行时维护所消耗的资源

索引的数据结构

有很多可以实现快速查找的数据结构，如二叉查找树，红黑树，堆等，但mysql存储引擎却没用这些，什么原因？mysql用的什么？mysql用的是B+树，什么是B树，什么又是B+树？简单来说，B树就是一个n叉树，就是一个节点可以有n个子节点。
然后再说一下mysql中数据的存储，数据都是存在磁盘中，磁盘的检索速度相比内存很慢，如果存储结构用树，树的高度越高，那么进行判断和IO的次数就越多，如果把一个节点中存放多个节点，就可以显著降低树的高度，而且一个节点内数据的比较是在内存中进行的，速度很快，这就是不用二叉搜索树和红黑树的原因。再结合磁盘预读和局部性原理，增大一个节点的大小，再存储较多的数据，能大大减少IO的次数，岂不美哉。

有一个概念叫做度：树的节点的度指的是这个节点的子节点的个数，一个树的度指的是这个树中节点度最大的那个

局部性原理和磁盘预读
很好理解：因为磁盘读取速度较慢，那么一次只读一个字节是不是很亏，所以一般一次读一页的数据(因OS而异，一般是4k大小)，且程序运行期间所需要的数据通常比较集中，所以预读后程序会先从内存中找数据，找不到再去磁盘里读，这样很多数据库设计时就把node大小设计成一页的大小，这样一个node只需一次IO就能完全载入了，且B+树每次新建节点时直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

那么为啥用B+树而不用B树呢？
二者都是n叉树，最大的区别在于B树的内节点既存指针又存数据，而B+数的数据只存在于叶节点，内节点只存指针，而且由于B+树的数据都存在叶子节点上，还在不同的叶子节点之间加了指针，这样就可以实现跨叶节点的范围访问，大大提高了范围查询的效率。而且数据一般都比较大，这样B树的内节点存的指针数量就有限，所以B+树内节点只存指针，能存储更多的指针，能减少更多的IO次数

总结起来：B树只适合随机检索，而B+树同时支持随机检索和顺序检索，且B+树空间利用率更高，减少更多的I/O次数，磁盘读写代价更低

注：由上可知，非单调的主键在插入数据时会导致数据文件为了维护B+树的特性而频繁的分裂调整，十分低效，如果主键是递增的，因为主键聚集索引时存放在叶子节点中，每插入一个数据(增加一个节点)就放到最后一个节点后面就可以了，达到最大填充因子后写入下一页即可，如果是随机主键，那就会插入到中间数据中，就会导致数据移动，甚至是页分裂，页分离会产生碎片，这就带来了一系列的问题，无谓的消耗资源

总结：采用递增的主键，可以避免随机插入带来的频繁页分裂，因为B+树申请新节点是有序的（叶子节点存有主键！），如果主键是递增的话，新增节点就可以按顺序放在后面的页就好了，可以减少由于插入中间带来的频繁页分裂。
B+树的叶子节点是有序的！

MyISAM和InnoDB

MyISAM和InnoDB是两种常用的mysql引擎，两者的存储结构用的都是B+树(内节点只存指针)，他们最大的区别在于叶节点中存储的数据是否是一行完整的数据。MyISAM中叶节点存储的是完整数据的地址，也就是索引没和数据存在一起，这种也叫非聚集索引。
InnoDB中叶节点存储的是一行完整的数据，即索引和数据是存放在一起的，这种就叫聚集索引。
MyISAM：非聚集索引
innodb：聚集索引

聚集索引和非聚集索引

聚集索引：
InnoDB索引实现中，数据文件本身就是索引文件，即B+树的叶子节点存的就是数据文件，而不是数据的地址，即data域保存了完整的数据记录所以叫聚集索引。
就像字段一样，内容和索引是在一起的

非聚集索引：
MyISAM引擎使用B+Tree作为索引结构，叶节点中只记录数据记录的地址，MyISAM中主索引和辅助索引在实现上没区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，因为数据和数据地址是分开的，所以叫做非聚集索引。
目录是目录，正文是正文，二者是分开的

联合索引

联合索引是指对多个字段同时建立的索引，注意字段间是有顺序的，如建立a，b，c三个字段的联合索引和b，a，c顺序建立的索引是不一样的。
联合索引的优点：
1、建一个等于建多个，索引是很耗资源的，所以这点还是能省一定空间
2、索引字段越多，筛选出来的数据就越少
3、联合索引可以做覆盖索引(这个在查询优化时很有用)

使用时注意事项：
1、单个索引需要注意的，联合索引也需要，如索引列不要参与计算，or的两边要么都索引要么都不索引，模糊匹配不要加在头部即第一个筛选条件上
2、还有就是最做匹配原则，要避免索引失效
3、把最常用的，筛选数据最多的字段放在左侧

最左匹配规则

通俗理解：即最左优先，在检索数据时从联合索引的最左侧字段开始匹配。
如果查询时没有使用联合索引的第一个字段，那索引就会失效，当不判断索引首个字段或者对其使用like这样的模糊匹配时不走索引！后面字段的模糊匹配也会走索引，说明第一个字段一定不能少且要精确匹配 比如建立(a,b,c)的联合索引
select ** WHERE c = “c” AND a = “a” – 走索引
select ** WHERE b = “b” AND c = “c” – 不走索引
select ** WHERE c = “c” AND b = “b” – 不走索引
select ** WHERE a LIKE “a%” – 不走索引
select ** WHERE b = “b” – 不走索引
select ** WHERE c = “c” – 不走索引
总结：mysql中一般使用的都是B+树来作为索引的数据存储结构，所以一定要有一个索引的引导列，应该也就是B+树的root节点，只有根节点存在才可以使用到联合索引

覆盖索引

真正实际应用中覆盖索引是提升性能的优化手段之一，比如把经常查询的条件加在一起做一个联合索引，项目中有很大的提升
省去回表查询的操作（注意是非主键查询时可以减少回表操作，如果是主键查询，就不用回表了）

explain

执行计划，可以模拟executor执行sql查询语句，从而知道mysql是如何处理sql的
用法：explain sql语句注意，这条sql语句不会执行，只会展示执行过程

查询出的结果一般有下面这些：
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

可以重点关注：

type：这列很重要，显示了连接使用哪种类别，有无使用索引，表示访问类型
从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL
①ALL表示将遍历全表以找到匹配行，最差的
②const表示一次索引就找到了结果，主键嘛
③eq_ref:表示唯一索引扫描，每个索引键表中只有一条记录与之匹配，主键或唯一索引
④ref：非唯一索引扫描
⑤range：检索一个范围，一般是大于小于between获取
⑥index：表示全索引扫描，遍历索引树会比all快，all则是从磁盘中读取

possible_keys：是否使用索引，为null表示没用索引，有值则是显示可能用了哪个索引

rows：有多少行被优化器查询，扫描出的行数(估算的行数)

其余字段：
id：select识别符，如果有查询有子查询的话，id会递增，id越大优先级越高，id相同则从上至下执行

table 表名

select_type：查询类型，常见的有以下几种
simple：简单select，没有union或子查询
primary：子查询中的最外层查询(若包含复杂的子查询，最外侧的被标记为primary)
union：(UNION中的第二个或后面的SELECT语句)
subquery：子查询中的第一个SELECT，结果不依赖于外部查询
derived：派生表的select，from子句的子查询

Key：key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

key_len ：列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好 越短可以节省空间

ref ：列与索引的比较，显示使用哪个列或常数与索引进行了比较。

filtered：按表条件过滤的百分比

extra：执行情况的描述和说明，比如发起一个覆盖索引的查询时，可以在extra中看到using index

Using where：表示优化器需要通过索引回表查询数据；
Using index：使用覆盖索引时就会出现；
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

key和primary key等的区别

key表示普通索引，有两个作用：一是约束，二是索引，普通索引没有唯一性之类的限制

primary key：主键，主键值唯一，且不能为空，每个表只能有一个主键
unique key：唯一索引列，值唯一，但可以为空，每个表可以有多个唯一索引
foreign key：外键，外键一般是另一个表的主键，外键表示了两个表之间的关系(子表中的行外键字段的取值必须包含于另一个表，例如歌曲表中的专辑id字段为外键，专辑id的取值必须存在于专辑表中)

注意：数据量大时不用外键，因为插入一条记录时有外键就需要去扫描此记录是否合格，十分十分耗时！！

index和key的区别

索引（index）和约束（key）的区别主要在于二者的出发点不同：
索引(index)负责维护表的查找和操作速度，约束(key)负责维护表的完整性，因为key有很多延伸，如unique key和primary key
通俗的理解index就是索引，是处于实现层面的，比如给这个列那个列加索引，索引只是索引，不会对字段产生约束行为；
而key是处于理论层面的，有着两层含义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）

MySQL的约束类型有5种：

主键约束 primary key
外键约束 foreign key
非空约束 not null
唯一约束 unique
默认约束 default

MySQL数据完整性的概念：
6. 实体完整性
7. 域完整性
8. 参照完整性
9. 用户定义完整性

常见问题：mysql索引有哪些？

这个问题从不同角度来说有不同的答案：
从物理存储上来看：分为聚集索引和非聚集索引

从逻辑角度上可分为：
1、主键索引
2、唯一索引
3、联合索引(多列索引、复合索引等)
4、覆盖索引：覆盖索引和联合索引的区别在于前者查询的列可以直接通过索引获取，而不用再回表查询

从数据结构上来看：
1、B+树索引(通常我们说的索引不出意外指的就是B树索引(实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引))
2、hash索引：仅满足对等比较，不能使用范围查询
3、fulltext索引、全文索引：
适用于数据量较大的文本查询的场景(like %这种适用于较小数据量)，具体如何用等用到时再查…现在还用不到

创建索引

常用的创建索引的方式有三种：
1、执行create table时创建索引create table test_table(…)

2、使用alter table命令去增加索引：
alter table test_table add index_name (column_list);
alter table用来创建普通索引、unique索引或primary key索引。其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可自己命名，另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

3、使用create index 命令创建：
create index index_name ON table_name (column_list);
create index可对表增加普通索引或unique索引(但不能创建主键索引）