数据库：带你全面了解msql索引

站长

2024年08月02日 14:33 · 阅读数 25

什么是索引

索引是帮助mysql高效获取数据的排好序的数据结构

根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表（如下图所示）。数据库：带你全面了解msql索引

通过索引查询数据时不用进行全表扫描，只是查询索引列。索引就好比字典的目录一样，我们通常都会先去目录查找关键偏旁或者字母再去查找。

例如，要查“我”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

使用索引可以很大程度上提高数据库的查询速度，可以有效的提高数据库系统的性能。

数据的存储及访问

数据、索引存储在哪里

首先mysql的数据是存储在磁盘上的，我们知道磁盘IO的效率是比较低的这也正是导 慢查询 的其中一个原因。

索引也是存储在磁盘上的，查询数据的时候会优先将索引加载到内存中。索引中主要包含这些信息：索引健值(key)、数据在磁盘中的地址、下级子节点的引用，具体的后面会介绍。

数据访问的两种方式

顺序访问

顺序访问：在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

索引访问

索引访问 通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

局部性原理与磁盘预读

在进行磁盘数据读取时有两个重要的概念：局部性原理 与 磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用——程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高，因为，不需要寻道时间，只需很少的旋转时间，因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

了解了上面的知识之后我们再来看为什么要使用索引就比较好理解了。回到我们的问题，为什么要使用索引？ 可参考下面关于索引的优缺点的介绍

索引的优缺点

优点

可以大大加快数据的检索速度（因为减少了检索的数据量）, 这也是创建索引的最主要的原因。
在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
在实现数据的参考完整性方面可以加速表与表之间的连接。
可以给所有的 MySQL 列类型设置索引。
帮助服务器避免排序和临时表。
将随机IO变为顺序IO。

缺点

创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

索引的使用及相关概念

索引分类

从物理结构上分为：聚集索引、非聚集索引

聚集索引：是指索引的键值的逻辑顺序决定了表中相应行的物理顺序。一个表中只能有一个聚集索引。

使用范围：

范围查询：使用运算符（如between、>、<=等），返回一系列的值
查询连续的值：如一月份的数据、二月份的数据
返回大型结果集：注意，通过索引检索的数据一般控制在表中总量的20%以下，因为当索引数据量过大时，那么系统将不会使用该索引。
在order by或group by子句中指定的列

非聚集索引：通过索引记录地址访问表中的数据。索引的逻辑顺序和表中行的物理存储顺序不同。一个表中可以有多个非聚集索引。

使用范围：

使用join或者group by的子句
不返回大型结果集的查询
经常包含在查询的搜索条件（例如返回完全匹配的where子句）中的列。

从应用上分为：主键索引、唯一索引、全文索引和组合索引

主键索引：当为一个表创建主键的时候，会自动为主键列创建一个索引，并且该索引是聚集索引。
唯一索引：唯一索引可以是聚集索引，也可以是非聚集索引。他要求在唯一索引所限制的列中，不允许有重复的键值。并且具有唯一约束的列，会自动的给他创建一个唯一索引。（其实主键索引，特殊的唯一索引，主键索引所在的列字段必须NOT NULL）
组合索引：表示这个索引的索引列可以有多个。比如学生成绩表中，我们可以把学生ID和考试科目ID合在一起建立索引，他的使用情况是多个列经常在一起作为查询条件。
全文索引：他是一种特性类型的基于标记的功能性索引。如果要在一个大文本里匹配一个字符那么就可以创建一个全文索引。char、varchar、text这些类型才能创建全文索引，匹配语法：select * from 表名 where match(字段名) against(‘要匹配的字符’ in natural language mode);引。

如何创建索

建表的时候就创建
建完表后续想添加索引：alter table 表名 add index 索引名(字段名);

// 新建索引
ALTER TABLE bee_user_location ADD INDEX msid (msid)
// 删除索引
ALTER TABLE bee_user_location DROP INDEX index_name;

怎么判断SQL有没有使用索引

查看SQL是否使用索引，只需要在sql语句前面加上 explain 即可

explain SELECT count(*) as value, province as name, provincecode  as code
    from bee_user_location 
    WHERE dt = '20210218'

查询结果：数据库：带你全面了解msql索引下面对各个字段进行说明：具体参考explain详解

单列索引与联合索引

单列索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。

当一个表中查询大的情况下，where条件中有多个，如果使用多个单列索引，根据mysql优化器策略，造成可能只使用一个索引，其他索引会失效，导致会全盘扫描表。特点：简单，索引个数多

// 创建单索引
ALTER TABLE bee_user_location ADD INDEX msid (msid)
// 查询
select * from user where ms_id = 'lxm'

联合索引

联合索引：即一个索包含多个列。

当一个表中查询大的情况下，where条件中有多个，那么可以使用组合查询,不会扫描表，直接从索引中获取，查询效率高。特点：稍微复杂，需要考虑索引顺序

// 创建联合索引
ALTER TABLE bee_user_location ADD INDEX dt_msid (dt,ms_id)
// 查询
select * from user where dt = '20210302' and ms_id = 'lxm'

联合索引特点：

联合索引是有顺序的，abc，cba是完全不同的两种联合索引。
建立一个联合索引相当于建立了多个索引，以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。
最左匹配原则，联合索引的最左前缀匹配指的是where条件一定要有联合索引的第一个字段，是否走联合索引与where条件的顺序无关，只与字段有关。
覆盖索引其实是一种特殊的联合索引，怎么理解呢，即是你查询的字段的所有数据都在索引上，不需要再进行一次回表查询，这样的索引即为覆盖索引。要求所查询的字段必须被索引所覆盖。

如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一

如何判定呢？如果Extra信息里有'Using Index'，表示已经使用了覆盖索引。
索引列越多，通过索引筛选出的数据越少。例如有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

创建联合索引时列的选择原则：

经常用的列优先（最左匹配原则）。
离散度高的列优先（离散度高原则）。列的离散性计算：count（distinct col）/ count(col)，例如：id列一共9列都不重复 9/9 = 1。离散度为1
宽度小的列优先（最少空间原则）。

性能对比

多个单列索引的情况下，MySql在执行查询时，如果where条件中是使用and连接各种过滤条件的，那么MySql会选择其中一个限制最严格的索引（区分度最高的）。
多列联合索引中由于索引是考虑索引字段顺序的，根据InnoDB中B-Tree的实现原理，单个索引限制再严格也没有多列限制严格，所以在多列索引的情况下数据库扫描更少的行，可以在较短的时间内返回数据；

索引的使用举例。如何高效使用索引

讲解完了索引的基础知识接下来我们正式进入索引的使用阶段，看下结合上面说的索引的各种特点如何高效使用索引优化sql查询速度。下面会详细的举例索引使用的一些注意点。

如下几点会导致索引失效

对索引列运算，运算包括(+、-、*、/、!、<>、%、like’%_'(% 放在前面)。
类型错误，如字段类型为 varchar，where 条件用 number。例如。列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引。
对索引应用内部函数，这种情况下应该要建立基于函数的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此时应该建 ROUND (t.logicdb_id) 为索引。
如果条件有 or，即使其中有条件带索引也不会使用(这也是为什么建议少使用 or 的原因)，如果想使用 or，又想索引有效，只能将 or 条件中的每个列加上索引，形成索引合并。
B-tree 索引 is null 不会走，is not null 会走，位图索引 is null，is not null 都会走

索引的使用注意点

1.只有where的情况，遵从最左原则，条件必须有左边的字段，才会用到索引，中间如果断开了，则都不会用到后面的索引

例如，现在我们的表 user_location 有复合索引dt_provincecode_citycode(dt, provincecode, citycode)。

2.group by和order by 其实一样，也是遵从最左原则，可以看做继承where的条件顺序，但需要where作为基础铺垫，即没有where语句，单纯的group by | order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

例子：

group by provincecode | order by provincecode，由于没有where的铺垫，不使用任何索引
where dt = '20210309' group | order by provincecode，使用dt,provincecode
where dt = '20210309' group | order by citycode，只使用dt索引
where dt > '20210309' group | order by provincecode，前面也说了，范围搜索会断掉连接，所以也只会使用dt索引
where dt = '20210309' group | order by provincecode,citycode，使用dt, provincecode, citycode索引
where dt = '20210309' group | order by provincecode,citycode，只使用dt索引，顺序不一致

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描；

例如，现在我们的表 user_location 有单索引mis_id。

我们的查询条件为：where mis_id = 'w_yaoguixiong02' 会用到 mis_id 索引。
我们的查询条件为：where mis_id <> 'w_yaoguixiong02' 会用到 mis_id 索引。

如果MySQL估计使用全表扫描要比使用索引快，则不使用索引

其他的还有后面会逐步补充

索引所使用的数据结构

索引所使用数据结构包括：二叉树、红黑树、Hash表、B-Tree。相关数据结构可视化可以使用这个网站，很方便，传送门cs.usfca.edu

二叉树

对于单边增长的情况不适合用二叉树进行存储，因为在极端情况下二叉树会退化为链表，查询速度O(n)

数据库：带你全面了解msql索引

红黑树

黑平衡的二叉树数据库：带你全面了解msql索引红黑树也是特殊的平衡二叉树，每层最多只有左右两个孩子节点。树的高度就是查询的时间复杂度一般平衡二叉树的查找复杂度平均为O(logh),当数据多时高度也会比较大，查找效率也是比较低的

B-Tree

平衡多叉树

叶子节点具有相同的深度，叶子节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列数据存储方式：

B+Tree(B-Tree的变种)

非叶子节点不存储数据，只存储索引(冗余)，可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间的访问性能 数据库真正存储数据的数据结构，叶子节点包含整个数据表所有数据，进行数据查找时，会把查找到的索引节点对应的树加载到内存，且整个树是有序的，加载到内容后会提升查找效率。B+树的节点存储的是一页数据大小大概16kb16kb16kb 例如，树的高度为三层，存放bigint数据大小大概8bit8bit8bit，下个节点的指针6bit6bit6bit，则每层可以存储的索引元素个数大概为16kb/(8+b)bit约等于1170个索引元素16kb/(8+b)bit约等于 1170个索引元素16kb/(8+b)bit约等于1170个索引元素同样第二层同理为 1170x1170。叶子节点略有不同，叶子节点还存储了data数据（可能是索引所对应数据的磁盘文件地址，也有可能是索引所在行的其他列数据），加入大小为1kb，则叶子节点可以存放16个元素，所以整颗树大概可以存放 1170x1170x16约等于2千多万个索引1170x1170x16 约等于 2千多万个索引1170x1170x16约等于2千多万个索引。索引存储数据量是比较大的，而且一般情况下根结点是常驻内存的，很多非叶子节点也是常驻内存的，索引查找效率是很高的。数据查找会用二分查找，效率更高效。

数据库的存储引擎

聚集索引：叶节点包含了完整的数据记录，例如主键索引就是一种聚集索引
非聚集索引：数据和索引分开存储，例如主键索引就是一种聚集索引

存储引擎是形容数据库表的而不是数据库的

MyISAM 索引文件和数据文件是分离的(非聚集的)

MyISAM一个表分为三个文件(表结构、数据、索引)，索引存储在MIY文件里面结构如下图所示数据库：带你全面了解msql索引

查找过程如下，先判断查找的字段是否是索引字段，是就去MYI索引文件根据索引快速定位，找到后再去MYD数据文件去找对应的数据

InnoDB索引实现(聚集、更常用的类型)

表数据文件本身是按照B+Tree组织的一个索引结构文件

InnoDB的表对应了两个文件（frm和ibd文件）ibd文件存储索引和数据，即表数据文件本身是按照B+Tree组织的一个索引结构文件，它和MyISAM的区别在于，叶子节点存储的元素不是数据所在磁盘文件的地址而是存储索引所在行的列数据 ibd文件结构如下非主键索引叶子节点存储的是主键

Hash结构

对索引的key记性呢一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+ 树索引更高效
仅能满足“=”， “IN” 不支持范围查询
会有hash冲突问题

联合索引结构

数据库：带你全面了解msql索引

数据库：带你全面了解msql索引

什么是索引

数据的存储及访问

数据、索引存储在哪里

数据访问的两种方式

顺序访问

索引访问

局部性原理与磁盘预读

索引的优缺点

优点

缺点

索引的使用及相关概念

索引分类

从物理结构上分为：聚集索引、非聚集索引

从应用上分为：主键索引、唯一索引、全文索引和组合索引

如何创建索

怎么判断SQL有没有使用索引

单列索引与联合索引

单列索引

联合索引

性能对比

索引的使用举例。如何高效使用索引

如下几点会导致索引失效

索引的使用注意点

索引所使用的数据结构

二叉树

红黑树

B-Tree

B+Tree(B-Tree的变种)

数据库的存储引擎

MyISAM 索引文件和数据文件是分离的(非聚集的)

InnoDB索引实现(聚集、更常用的类型)

Hash结构

联合索引结构

相关问题

为什么建议InnoDB表必须建主键，且推荐使用整型自增主键？

为什么非主键索引结构叶子节点存储的是主键？