深入理解mysql-第七章 mysql存储引擎InnoDB的索引-索引的使用

小咪咪 2023-01-22 14:48 6阅读 0赞

索引会给我们定位到数据页提供很大的便利，但根据算法的思想，这种快速的定位数据必定要复出一定的代价。所以索引并不是越多越好。

# 一、索引的代价 #

如何更好的使用索引之前先要了解一下使用的代价，它在空间和时间上都会拖后腿：

*  空间上的代价
    
    每建立一个索引都要为它建立一棵`B+`树，每一棵`B+`树的每一个节点都是一个数据页，一个页默认会占用`16KB`的存储空间，一棵很大的`B+`树由许多数据页组成，占用了很大的一片存储空间。
 *  时间上的代价（维护b+查询树结构）
    
    每次对表中的数据进行增、删、改操作时，都需要去修改各个`B+`树索引。而且我们讲过，`B+`树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的`B+`树都要进行相关的维护操作，这可是巨大的时间消耗。
 *  时间上的代价（回表） 为了彻底告别`回表`操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，这也就是select 后尽量建议包含所需要的列，而不是select \*。

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们先得学学这些索引在哪些条件下起作用的，哪些是不起作用的（特别是联合索引），避免建立了无用索引，或者说建立了索引使用不当。

# 二、索引的使用场景 #

正常的来说二级索引比较简单，对于单条件的查询，排序都比较适用，主要是联合索引的使用，会有很多问题。首先我们要明白联合索引的原理，上一章我们讲了联合索引的建立和查询过程，我们以index idx\_name\_birthday\_phone\_number (name, birthday, phone\_number)举例，说明`B+`树中页面和记录的排序方式的主要不同是：

*  先按照`name`列的值进行排序。
 *  如果`name`列的值相同，则按照`birthday`列的值进行排序。
 *  如果`birthday`列的值也相同，则按照`phone_number`的值进行排序。

这个排序方式十分重要，因为只要页面和记录是排好序的，我们就可以通过快速定位查找。索引能否正确工作了，都是依据这个排序原理。

## 2.1 全值匹配和匹配最左边的列 ##

全值匹配指的是搜索条件中的列和索引列一致。这种即便条件是无序的也是可以使用索引的，这是因为Mysql有查询优化器。比如示例：

SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239' AND name = 'Ashburn';

其实在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，比方说下边的查询语句：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

这样是可以使用联合索引的 ，但如果条件中没有name，是无法使用该联合索引的。因为`B+`树的数据页和记录先是按照`name`列的值排序的，在`name`列的值相同的情况下才使用`birthday`列进行排序，也就是说`name`列的值不同的记录中`birthday`的值可能是无序的。而现在跳过`name`列直接根据`birthday`的值去查找，这样是无法查找的。假如条件有name和`phone_number，name查询是可以使用索引的，phone_number是无法使用索引的。`

``匹配左前缀的另外一种情况，就是为字符串列创建的索引。为某个列建立索引的意思其实就是在对应的`B+`树的记录中使用该列的值进行排序，字符串排序一般的比较规则都是逐个比较字符的大小。也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比如：

SELECT * FROM person_info WHERE name LIKE 'As%';

但是如果条件是中间的或者后缀的like语句，就无法用到索引。但后缀的like查询可以考虑插入数据时倒序排列字符串，进而进行索引查询。

### 2.2 精确匹配和范围匹配 ###

范围匹配示例：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

上边这个查询可以分成两个部分：

1.  通过条件`name > 'Asa' AND name < 'Barlow'`来对`name`进行范围，查找的结果可能有多条`name`值不同的记录，
2.  对这些`name`值不同的记录继续通过`birthday > '1980-01-01'`条件继续过滤。

这样子对于联合索引`idx_name_birthday_phone_number`来说，只能用到`name`列的部分，而用不到`birthday`列的部分，因为只有`name`值相同的情况下才能用`birthday`列的值进行排序，而这个查询中通过`name`进行范围查找的记录中可能并不是按照`birthday`列进行排序的，所以在搜索条件中继续以`birthday`列进行查找时是用不到这个`B+`树索引的。

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

这个查询的条件可以分为3个部分：

1.  `name = 'Ashburn'`，对`name`列进行精确查找，当然可以使用`B+`树索引了。
2.  `birthday > '1980-01-01' AND birthday < '2000-12-31'`，由于`name`列是精确查找，所以通过`name = 'Ashburn'`条件查找后得到的结果的`name`值都是相同的，它们会再按照`birthday`的值进行排序。所以此时对`birthday`列进行范围查找是可以用到`B+`树索引的。
3.  `phone_number > '15100000000'`，通过`birthday`的范围查找的记录的`birthday`的值可能不同，所以这个条件无法再利用`B+`树索引了。

## 2.3 排序 ##

SQL语句通过`ORDER BY`按照某种规则进行排序，原理就是`MySQL将查询出的数据，`在内存中或者磁盘上`通过排序算法进行排序，`这种排序的方式统称为文件排序，速度是比较慢的，但是如果`ORDER BY`子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，这是因为索引查询的查询排列大小方式和 ORDER BY的是一样的。

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

当然也存在不可以使用索引进行排序的几种情况：

*  **ASC、DESC混用** 默认都是asc方式，联合索引各列都是倒序无非是从倒着取，无论正序倒序都是可以用索引的。但如果name列正序，birthday倒序，就无法使用索引了。
 *  **排序列使用了复杂的表达式** 要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式。比如，带运算符，带函数等。

## 2.4 分组 ##

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

这个查询语句相当于做了3次分组操作：

1.  先把记录按照`name`值进行分组，所有`name`值相同的记录划分为一组。
2.  将每个`name`值相同的分组里的记录再按照`birthday`的值进行分组，将`birthday`值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
3.  再将上一步中产生的小分组按照`phone_number`的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把`大分组`分成若干个`小分组`，然后把若干个`小分组`再细分成更多的`小小分组`。

然后针对那些`小小分组`进行统计，比如在我们这个查询语句中就是统计每个`小小分组`包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的`B+`树中的索引列的顺序是一致的，而我们的`B+`树索引又是按照索引列排好序的，所以可以直接使用`B+`树索引进行分组。

# 三、建立索引的的注意事项 #

## 3.1 只为用于搜索、排序或分组的列创建索引 ##

也就是说，只为出现在`WHERE`子句中的列、连接子句中的连接列，或者出现在`ORDER BY`或`GROUP BY`子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了：

SELECT birthday, country FROM person_name WHERE name = 'Ashburn';

像查询列表中的`birthday`、`country`这两个列就不需要建立索引，我们只需要为出现在`WHERE`子句中的`name`列创建索引就可以了。

## 3.2 列的基数尽量大 ##

`列的基数`指的是某一列中不重复数据的个数，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。假设某个列的基数为`1`，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

## 3.3 索引列的类型尽量小 ##

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有`TINYINT`、`MEDIUMINT`、`INT`、`BIGINT`这么几种，它们占用的存储空间依次递增。数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘`I/O`带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。所以尽量让索引列使用较小的类型。

## 3.4 让索引列在比较表达式中单独出现 ##

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

## 3.5 主键最好顺序插入 ##

我们知道，对于一个使用`InnoDB`存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在`聚簇索引`的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就会需要不停的有页的分裂和页内容调整-记录移位。所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有`AUTO_INCREMENT`，让存储引擎自己为表生成主键。

## 3.6 去除冗余和重复索引 ##

我们知道，通过`idx_name_birthday_phone_number`索引就可以对`name`列进行快速搜索，再创建一个专门针对`name`列的索引就算是一个`冗余`索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。