MySQL索引

今天你做核酸了吗
56 阅读12分钟

MySQL索引

索引是什么？

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:快速查找排好序的一种数据结构。

建立索引会对索引列进行排序，将数据页从大到小按顺序排好序，并且每个数据页都是双向链表互相指向上一页、下一页的，数据页里面的数据都会有一个next_record指向下一条数据，是一个双向链表。

索引的创建与删除

创建表的时候指定索引列或者联合索引的多个列，其中key 和 index 是同义词，任意选一个就可以了。

CREATE TALBE 表名 (
	各种列的信息 ··· ,
	[KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

修改表结构的时候添加索引。

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
-- 例如 alter table test_student add index index_key1 ( key1 );

修改表结构的时候删除索引。

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
-- 例如 alter table test_student drop index index_key1_key2;

索引的代价

  • 空间上的代价：显而易见，每建立一个索引都会去创建一颗B+树，B+树的每个节点都是一个数据页，一页有16kb，一颗树由很多数据页组成，也是一片很大的存储空间。
  • 时间上的代价：每次我们都数据进行增、删、改操作的时候，都会去修改B+数索引，同时可能还会重新进行排序，去维护链表的指向关系。

一个表建立的索引越多，就会占用很多的存储空间，同时在增删改的时候由于要维护每一颗索引树，所以性能会越来越差，但是又由于索引能极大的提高我们的查询效率，所以索引对我们来说是一把双刃剑，是利还是弊得取决于我们怎么用它。

索引读取为顺序I/O，回表时可能主键分布在不同的页中，需要将不相连的页面加载出来，这个读取方式成为随机I/O。

索引的类型

回表

select * from test where name = 'xxx'，当前索引列为name，此时我们查询是先从二级索引中找到符合name='xxx'条件的列，但是因为二级索引只存储了我们的name+id列，而我们又需要获取*全部数据，所以此时我们需要根据该记录的id列去聚簇索引中查出完整数据，这个根据id列去聚簇索引中查询完整数据的操作也叫做回表。

聚簇索引

在InnoDB存储引擎中，我们每创建一个表，都会默认为我们自动的创建一个以主键值从小到大排序（如果没有主键会去使用唯一非null的列作为主键,如果表中连唯一非null的列都没有定义的话。那么表会默认添加一列row_id的隐藏列作为主键）的B+树（一个B+树索引的根节点自动被创建以后，就不会在移动）的聚簇索引，当我们最初向表中插入数据的时候，会把这一行的完整数据（包含隐藏列和表中自定义的列）存储B+树叶子节点中。

由于所有的数据都是存放在叶子节点中，我们也称索引即数据、数据即索引。

特点 ：1、使用主键值大小进行记录和页的排序。2、聚簇索引中的叶子节点存储的数据是一行的完整数据。

唯一二级索引

当我们在一个Unique列上面创建索引的话，这个索引就叫做唯一二级索引。

他在索引树的数据存储上面不会跟聚簇索引一样存放完整的用户数据，只会存放主键列，以及当前索引列的数据，通过索引列的大小进行记录和页的排序。

当使用到该索引进行查询时，因为该列的唯一特性，在效率上面只会比聚簇索引增加一个回表的成本消耗。

二级索引

二级索引和唯一二级索引的区别只是该列是否为Unique。

如果使用该索引进行查询时，由于不具备唯一特性，所以可能会查询到多条符合条件的数据，在回表操作的时候，由于我们的主键在二级索引中并没有排序，可能这些主键值在聚簇索引中分别处于页1、页5、页10，这种情况属于随机I/O，效率相对唯一二级索引会低。

联合索引

我们在建立索引的时候也可以选择使用多列，比如：alter table tset add index index_name_age(name,age)，这条SQL语句就以name和age两列来创建一个索引。

我们在使用联合索引的时候，注意查询条件后的列一定要跟创建索引的列一致，如果不一致的会导致使用不了该联合索引。（索引最左匹配原则）

覆盖索引（避免回表的性能消耗）

为了避免回表操作带来的性能损耗，最好需要查询列包含在索引中，如果包含在索引中的话，我们就不需要得到主键Id去聚簇索引中进行回表获取需要查询列的值了，省去了回表操作带来的性能损耗，这种只用到索引中的查询方式成为覆盖索引。

小贴士：对于二级索引的排序来说，如果索引列的值相等的话，那么会根据主键值从大到小排序。所以使用二级索引对数据值做等值匹配时（ref），回表对页IO操作是一个顺序IO。如果等值匹配也能满足需求尽量避免使用范围查询（range），范围查询出来的一个主键列并不是从大到小排好序的，在回表时的操作是随机IO，可能在成本上还没有全表扫描的成本大。

使用索引的情况

全值匹配

如果我们查询的条件列和索引列是一致的话，这种情况称之为全值匹配。

比如现在test表中的有个二级索引 index index_name(name) ，select * from test where name = 'xxx';这种语句匹配该索引称之为全值匹配。

查询过程大概为：

  1. 该索引列是二级索引，按照name进行排序的，通过二分法能很快定位到xxx的记录位置。
  1. 因为该二级索引只存放了name+主键的值，查询的列又是*，所以需要用符合该条件的主键值去做回表操作，去聚簇索引中查该条数据的完整信息。
  1. 将查询的结果返回。

索引列最左匹配原则

当有一个联合索引时，我们查询的条件中可以不用包含该联合索引中全部的列，只要包含左边的列就能使用该联合索引。

比如现在test表中有个index  index_name_age_phone (name , age , phone)。

select * from test where name = 'xxx' and age = xxx；这个语句可以用到联合索引的 name 和 age 两个索引列。

select * from test where age = xxx；这个语句并不能用到该联合索引。

总得来说，如果我们想要使用联合索引中尽可能多的列，查询条件的各个列必须要是联合索引中从最左边连续的列。

匹配列前缀

为某个列建立索引会对该列进行排序，比如现在对test表中的name列建立索引，会对数据进行排列，对字符串的本质就是一个个字符进行对比，排序后的值如 Aaaaa、Aaaab、Aaaac。我们可以看到一个特点，就是字符串的前N个字符都是排好的，索引我们只需要匹配它的前缀也是可以快速定位记录的。

select * from test where name = 'Aaaa%'，该语句可以通过前几个字符Aaaa去对列进行一个快速的匹配，这个原则也叫做匹配列前缀。如果SQL 改成这样select * from test where name = '%Aaaa'，由于我们并不知道首个字符是什么，所以并不能根据索引进行查询，需要进行全表查询每一条数据做筛选。

匹配范围值

由于所有记录都是按照索引列的从小到大的规则排好序的，所以我们可以很方便的查询某个索引的范围值。

select * from test where name > 'AAA' and name < 'BBB' ; 该语句的步骤为

  1. 查找出name 大于 AAA 的记录，name小于BBB的记录。
  1. 由于索引页是双向链表锁连起来的，所以我们很容易找到这个范围。
  1. 找到这个范围记录的主键值，去做回表操作查数据的完整信息。

如果通过联合索引列进行范围查找的时候，如果有多个列同时进行范围查找，只有条件最左边的那个范围查找才能用到该联合索引。

select * from where name > 'AAA' and age > 18;此时，只有name列能用到索引进行范围查询，age并不能用到索引。

select * from where name = 'AAA' and age >18;此时两个索引列都能用上，name是全值匹配查询，age是范围查询。（精准匹配某一列+匹配范围）

排序

因为我们的索引列已经排好序了，所以直接从索引中提取数据，然后进行回表操作取出完整数据就好了。（当然，如果需要查询的列 在索引列中，此时造成了覆盖索引，不需要进行回表操作。）

使用联合索引进行排序的时候Order by 语句后面也必须要按照索引列的顺序给出。

索引不能进行排序的几种情况

  1. ASC、DESC 进行混用。（默认是按照ASC 升序排序的）

有name,age列的联合索引，select * from test order by name , age limit 10,这种情况直接直接从左往右读10条记录就好了。

select * from test order by name asc , age desc，先从索引的最左边确定 name 列最小的值，然后找到 name 列等于该值的所有记录，然后从 name 列等于该值 的最右边的那条记录开始往左找10条记录。
如果 name 列等于最小的值的记录不足10条，再继续往右找 name 值第二小的记录，重复上边那个过程，直 到找到10条记录为止。这种情况并不能高效的使用索引，还不如文件排序来的快。所以规定联合索引的排序顺序必须要是一直的。

  1. Where 条件中出现了非排序使用到的索引列条件
    有name列的二级索引，select * from test where phone = 'xxx' order by name；此时是按照phone条件去进行全表查询了，并不会使用到name列索引。
  1. Order by 后面跟着一些非联合索引的列。
  1. Order by 排序列使用了一些函数进行操作。（要想使用索引，列必须要是单独形式出现，而不是修饰过后的形式。）

分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。

select name,age,count(*) from test group by name , age ；该语句的步骤为：

  1. 先把记录按照name分成一组，所有name相同的一组
  1. 在将每个name值相同的分组里面的记录按照age 值进行分组，将age相同的放入一个组里面，看起来像分化了一个小小组。
  1. 然后针对这个小小组进行统计。（如果是联合索引的话也是要符合最左匹配原则。）

如何更好的使用到索引

搜索、排序、分组列创建索引

尽量在where条件后面的列、连接后面的列、排序后的列、分组后的列上创建索引，最大程度的使用索引。

考虑列的基数

考虑索引的基数，假设一列有1、2、3、1、2、3、3、2、1,9行数据，此时基数为3。在记录行数一定的情况下，列的技术越大，该列中的值越分散，该列的基数越小，该列的值就越集中。这个指标非常重要，直接影响能否有效利用索引，如果为1的话，那么该列的值都一样，建立索引并没用，他的效率可能还比不上全表查询，同时还占用了存储空间。所以最好在为那些基数大的列上面建立索引，基数小的情况下可能效果并不好。

索引列的类型尽量小

在对列指定类型的时候尽量选择 符合业务的最小类型，以整数类型为例，有 int、bigInt等，如果可以使用int 就不要使用bigInt。数据类型越小，索引的存储空间就越小，在一个数据页内就可以存放更多的东西，减少数据页的IO消耗。

索引字符串的前缀

在对字符串进行查询时，由于字符串前缀是排好序的，建立二级索引时可以用字符串前几个字符，这样虽然在查找时不能定位准确位置，但是也能定位到前缀大概的位置。

这种只索引字符串值的前缀的策略是 我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

查询条件让索引列单独出现

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的，所以不要对索引列进行一些骚操作~。

主键插入顺序

建议主键使用自增，避免添加数据在维护B+树的时候产生页分裂，减少一些不必要的资源消耗，这个在集群情况下可能不合适，了解一下

冗余和重复索引

如果某一列已经在索引里面了，那么就不要在创建该列的索引了。如果索引冗余了，每次增、删、改还会对这个索引列进行维护，简直是浪费维护的成本，同时还浪费存储空间。

avatar
文章
阅读
粉丝