MySQL的索引

于 2020-08-06 18:10:38 发布
阅读量191 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xioayu96/article/details/107846560
版权

一、什么是索引，为什么要有索引？

索引可以看做是一本书的目录，便于快速定位到想要查找到的内容。

InnoDB管理存储空间的基本单位是页，每个页的默认大小为16KB。

如果不使用索引进行数据查询

在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

  • 以主键为搜索条件

    这个查找过程我们已经很熟悉了，可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

  • 以其他列作为搜索条件

    对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

  1. 定位到记录所在的页。
  2. 从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。

二、索引模型

哈希表

哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。 如果想要范围查询的话，就需要把数据全都遍历一遍了。

哈希表这种结构适用于只有等值查询的场景 ，对于范围查询就无能为力了

有序数组

有序数组的数据存放是一次递增的，想要查找的某个记录，用二分法就可以快速得到，这个时间复杂度是O(log(N))。如果想要查询某个范围内的数据，比如id在[1,5]之间的数据，则可以用二分法找到id=1的记录，然后依次向右遍历，直到id=5的记录。

但是有序数组也有缺点，那就是对于增删某个记录的话，需要移动后边所有的记录，对于性能的开销比较高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是不变的历史数据，这类不会再修改的数据。

搜索树

二叉树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。它的时间复杂度是O(log(N))。为了维持O(log(N))的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。为什么不选择二叉树这种数据结构来存储数据呢，因为索引往往不是存于内存，还需要落磁盘，这时候就会涉及到磁盘的IO。

如果有一个1000万节点的平衡二叉树，树高30。一次查询可能需要访问30个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个1000万行的表，如果使用二叉树来存储，单独访问一个行可能需要300ms，如果对于数据的实时响应的系统来说，这可是致命的。

BTree

在这里插入图片描述

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以下条件：

  • 每个叶子结点的高度一样，等于h；
  • 每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
  • 叶子结点指针都为null；
  • 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

相较于二叉树来说，BTree有N叉，如果使用BTree来存储数据的话，树的高度则会大大降低，一般情况下为3或4，以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了，如果一个表有17亿条数据，这是多么庞大的一个表。

B+Tree

在这里插入图片描述

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

  • B+Tree中的非叶子结点不存储数据，只存储键值；
  • B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
  • B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

而InnoDB则采用的是B+Tree的索引模型。 所以数据都是存储在B+树中的。 每一个索引在InnoDB里面对应一棵B+树。

三、创建索引的语法

InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引，如果没有UNIQUE列，则会自动维护一个row_id。

我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

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

其中的KEY和INDEX是同义词，任意选用一个就可以。我们也可以在修改表结构的时候添加索引：

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引：

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

比方说我们想在创建index_demo表的时候就为c2和c3列添加一个联合索引，可以这么写建表语句：

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

在这个建表语句中我们创建的索引名是idx_c2_c3，这个名称可以随便起，不过我们还是建议以idx_为前缀，后边跟着需要建立索引的列名，多个列名之间用下划线_分隔开。

如果我们想删除这个索引，可以这么写：

ALTER TABLE index_demo DROP INDEX idx_c2_c3;

四、索引分类

先来建一个表

mysql> CREATE TABLE index_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 CHAR(1),
    ->     PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

聚簇索引

1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

  • 页内的记录是按照主键的大小顺序排成一个单向链表。

  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

  • 存放目录项记录（非叶子节点）的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2.B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建（后边会介绍索引相关的语句），InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢？难道只能从头到尾沿着链表依次遍历记录么？

不，我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树

这个B+树与上边介绍的聚簇索引有几处不同：

  • 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照c2列的大小顺序排成一个单向链表。

    • 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。

    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。

  • B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

  • 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下：

  1. 确定目录项记录页

    根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）。

  2. 通过目录项记录页确定用户记录真实所在的页。

    在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。

  3. 在真实存储用户记录的页中定位到具体的记录。

    到页34和页35中定位到具体的记录。

  4. 但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

需要重点看一下步骤4，我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！！！

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照c2列进行排序。
  • 在记录的c2列相同的情况下，采用c3列进行排序

我们需要注意以下几点：

  • 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。

  • B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

千万要注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

  • 建立联合索引只会建立如上图一样的1棵B+树。
  • 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

覆盖索引

使用的是五、索引的使用规则中的表结构

为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，比如这样：

SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

因为我们只查询name, birthday, phone_number这三个索引列的值，所以在通过idx_name_birthday_phone_number索引得到结果后就不必到聚簇索引中再查找记录的剩余列，也就是country列的值了，这样就省去了回表操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为索引覆盖。排序操作也优先使用覆盖索引的方式进行查询，比方说这个查询：

SELECT name, birthday, phone_number  FROM person_info ORDER BY name, birthday, phone_number;

虽然这个查询中没有LIMIT子句，但是采用了覆盖索引，所以查询优化器就会直接使用idx_name_birthday_phone_number索引进行排序而不需要回表操作了。

当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们很不鼓励用*号作为查询列表，最好把我们需要查询的列依次标明。

五、索引的使用规则

首先创建一个表

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

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

我们建立的idx_name_birthday_phone_number索引包含的3个列在这个查询语句中都展现出来了。大家可以想象一下这个查询过程：

  • 因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。

  • 在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是'1990-09-27'的记录。

  • 如果很不幸，name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。

有的同学也许有个疑问，WHERE子句中的几个搜索条件的顺序对查询结果有啥影响么？也就是说如果我们调换name、birthday、phone_number这几个搜索列的顺序对查询的执行过程有影响么？比方说写成下边这样：

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

答案是：没影响。MySQL会使用优化器去分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。

最左原则

最左匹配

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

SELECT * FROM person_info WHERE name = 'Ashburn';

或者包含多个左边的列也行：

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

那为什么搜索条件中必须出现左边的列才可以使用到这个B+树索引呢？比如下边的语句就用不到这个B+树索引么？

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

是的，的确用不到，因为B+树的数据页和记录先是按照name列的值排序的，在name列的值相同的情况下才使用birthday列进行排序，也就是说name列的值不同的记录中birthday的值可能是无序的。而现在你跳过name列直接根据birthday的值去查找，臣妾做不到呀～ 那如果我就想在只使用birthday的值去通过B+树索引进行查找咋办呢？这好办，你再对birthday列建一个B+树索引就行了，创建索引的语法不用我唠叨了吧。

但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引idx_name_birthday_phone_number中列的定义顺序是name、birthday、phone_number，如果我们的搜索条件中只有name和phone_number，而没有中间的birthday，比方说这样：

SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

这样只能用到name列的索引，birthday和phone_number的索引就用不上了，因为name值相同的记录先按照birthday的值进行排序，birthday值相同的记录才按照phone_number值进行排序。

最左前缀

我们前边说过为某个列建立索引的意思其实就是在对应的B+树的记录中使用该列的值进行排序，比方说person_info表上建立的联合索引idx_name_birthday_phone_number会先用name列的值进行排序，所以这个联合索引对应的B+树中的记录的name列的排列就是这样的：

Aaron
Aaron
...
Aaron
Asa
Ashburn
...
Ashburn
Baird
Barlow
...
Barlow

字符串排序的本质就是比较哪个字符串大一点儿，哪个字符串小一点，比较字符串大小就用到了该列的字符集和比较规则，这个我们前边儿唠叨过，就不多唠叨了。这里需要注意的是，一般的比较规则都是逐个比较字符的大小，也就是说我们比较两个字符串的大小的过程其实是这样的：

  • 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。

  • 如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小。

  • 如果两个字符串的第二个字符也相同，那就接着比较第三个字符，依此类推。

所以一个排好序的字符串列其实有这样的特点：

  • 先按照字符串的第一个字符进行排序。

  • 如果第一个字符相同再按照第二个字符进行排序。

  • 如果第二个字符相同再按照第三个字符进行排序，依此类推。

也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以'As'开头的记录，那就可以这么写查询语句：

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

但是需要注意的是，如果只给出后缀或者中间的某个字符串，比如这样：

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

MySQL就无法快速定位记录位置了，因为字符串中间有'As'的字符串并没有排好序，所以只能全表扫描了。有时候我们有一些匹配某些字符串后缀的需求，比方说某个表有一个url列，该列中存储了许多url：

+----------------+
| url            |
+----------------+
| www.baidu.com  |
| www.google.com |
| www.gov.cn     |
| ...            |
| www.wto.org    |
+----------------+

假设已经对该url列创建了索引，如果我们想查询以com为后缀的网址的话可以这样写查询条件：WHERE url LIKE '%com'，但是这样的话无法使用该url列的索引。为了在查询时用到这个索引而不至于全表扫描，我们可以把后缀查询改写成前缀查询，不过我们就得把表中的数据全部逆序存储一下，也就是说我们可以这样保存url列中的数据：

+----------------+
| url            |
+----------------+
| moc.udiab.www  |
| moc.elgoog.www |
| nc.vog.www     |
| ...            |
| gro.otw.www    |
+----------------+

这样再查找以com为后缀的网址时搜索条件便可以这么写：WHERE url LIKE 'moc%'，这样就可以用到索引了。

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以person_info表的联合索引idx_name_birthday_phone_number (name, birthday, phone_number)为例。如果现在有一个需求：检索出表中“name是As开头，而且birthday为2020-08-06的记录”。那么，SQL语句是这么写的：

SELECT * FROM person_info WHERE name LIKE 'As%' and birthday = '2020-08-06';

已经知道了最左前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “As”，找到第一个满足条件的记录。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在MySQL 5.6之前，只能从第一个满足条件的记录开始一个个回表。到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

匹配范围值

回头看我们idx_name_birthday_phone_number索引，所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中的数据页和记录是先按name列排序的，所以我们上边的查询过程其实是这样的：

  • 找到name值为Asa的记录。
  • 找到name值为Barlow的记录。
  • 哦啦，由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来喽～
  • 找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

不过在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，比方说这样：

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+树索引了，只能遍历上一步查询得到的记录。

同理，下边的查询也是可能用到这个idx_name_birthday_phone_number联合索引的：

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

用于排序

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

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序。大家可以回过头去看我们建立的idx_name_birthday_phone_number索引的示意图，因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。简单吧？是的，索引就是这么牛逼。

使用联合索引进行排序注意事项

对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name的顺序，那也是用不了B+树索引，这种颠倒顺序就不能使用索引的原因我们上边详细说过了，这就不赘述了。

同理，ORDER BY name、ORDER BY name, birthday这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样：

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

这个查询能使用联合索引进行排序是因为name列的值相同的记录是按照birthday, phone_number排序的

不可以使用索引进行排序的几种情况
ASC、DESC混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

为啥会有这种奇葩规定呢？这个还得回头想想这个idx_name_birthday_phone_number联合索引中记录的结构：

  • 先按照记录的name列的值进行升序排列。

  • 如果记录的name列的值相同，再按照birthday列的值进行升序排列。

  • 如果记录的birthday列的值相同，再按照phone_number列的值进行升序排列。

如果查询中的各个排序列的排序顺序是一致的，比方说下边这两种情况：

  • ORDER BY name, birthday LIMIT 10

    这种情况直接从索引的最左边开始往右读10行记录就可以了。

  • ORDER BY name DESC, birthday DESC LIMIT 10，

    这种情况直接从索引的最右边开始往左读10行记录就可以了。

但是如果我们查询的需求是先按照name列进行升序排列，再按照birthday列进行降序排列的话，比如说这样的查询语句：

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

这样如果使用索引排序的话过程就是这样的：

  • 先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。

  • 如果name列等于最小的值的记录不足10条，再继续往右找name值第二小的记录，重复上边那个过程，直到找到10条记录为止。

累不累？累！重点是这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，设计MySQL的大叔觉得这样还不如直接文件排序来的快，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。

WHERE子句中出现非排序使用到的索引列

如果WHERE子句中出现了非排序使用到的索引列，那么排序依然是使用不到索引的，比方说这样：

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

这个查询只能先把符合搜索条件country = 'China'的记录提取出来后再进行排序，是使用不到索引。注意和下边这个查询作区别：

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

虽然这个查询也有搜索条件，但是name = 'A'可以使用到索引idx_name_birthday_phone_number，而且过滤剩下的记录还是按照birthday、phone_number列排序的，所以还是可以使用索引进行排序的。

排序列包含非同一个索引的列

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说：

SELECT * FROM person_info ORDER BY name, country LIMIT 10;

name和country并不属于一个联合索引中的列，所以无法使用索引进行排序

排序列使用了复杂的表达式

要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比方说这样：

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用了UPPER函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。

用于分组

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

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+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

来源：掘金小册子《MySQL 是怎样运行的：从根儿上理解 MySQL》

多一天，多一点
关注
专栏目录
MYSQL索引
01-27
MySQL索引是数据库管理系统中用于加速数据检索的关键结构。索引优化是提升查询性能的关键手段。MySQL中的索引主要在存储引擎层实现，而非服务器层。B-Tree索引是最常见的索引类型，其特点是所有值按顺序存储，每个...
Mysql索引设计和使用
安得情怀似旧时
11-13 864
文章目录1 概述2 设计原则3 设计误区4 索引方法5 索引类型6 参考资料 1 概述 索引就是对某些字段提前排序，生成特定的数据结构（例如B+树或者哈希），然后加快查询。 2 设计原则 要在过滤字段上创建索引。即where后出现的字段上创建索引。 要在取值范围广的字段创建索引。比如用性别字段创建索引，那意义就不大。 尽量使用短字段做索引。首先是节省空间，其次是IO较少，比较更快。 主键尽量使用自增、整型字段。因为普通索引会保存主键，太长的主键会占用空间。 创建联合索引时，利用最左字段查询。创建联合索引后
sql查询调优之where条件排序字段以及limit使用索引的奥秘
weixin_30466953的博客
02-08 1465
奇怪的慢sql 我们先来看2条sql 第一条： select * from acct_trans_log WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10 第二条： select * from acct_trans_log WHERE acct_id = 100000...
MySql基础查询，where查询，排序
有时间就写写代码
07-28 1293
简单查询 查询一张数据表的所有数据行的内容。 简单查询 语法1: SELECT [DISTINCT] * from 表名 语法2: SELECT [DISTINCT] 列名 [AS] “别名”,列名 别名… FROM 表名; 表和列都可以去别名 查询emp表的所有数据,即所有行和所有列的数据。 select * from emp; 查询 emp表的员...
Mysql索引详细介绍
09-10
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它们通过创建数据结构，使得数据库能够快速定位到所需的数据行，从而显著提高了查询性能。以下是关于MySQL索引的详细介绍： **一、索引概述** MySQL允许为...
php mysql索引问题
10-30
最后，关于phpmysql索引问题，通过上述的方法，可以有效地解决PHP与MySQL结合使用时遇到的索引生成和管理问题。在实际开发过程中，理解数据库中索引的作用及其类型对于提高应用程序性能和数据管理能力是非常有帮助的...
MySQL索引原理
11-15
### MySQL索引原理详解 #### 一、索引的基本概念 **索引**是帮助MySQL高效获取数据的数据结构。在数据库中，索引扮演着极其重要的角色，它能够显著提高数据检索的速度，尤其是在处理大规模数据集时尤为重要。索引...
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
MySql索引优化
12-14
MySQL索引优化是数据库管理中的关键环节，它直接影响着数据查询效率和系统整体性能。创建索引的主要目的是为了加速数据的检索，特别是对于大型数据库，这一点尤为重要。索引能够确保数据的唯一性，使得数据库表中的...
浅析mysql索引
12-16
MySQL索引是数据库管理系统中一个重要的概念，它显著提升了数据查询和访问的速度。索引是一种特殊的数据结构，设计用于加快数据库表中数据的查找和排序。通过对表中一个或多个列创建索引，数据库可以更有效地执行SQL...
MySQL索引
12-14
MySQL索引是数据库管理系统中的一种重要工具，它创建了一个排序的数据结构，目的是为了加快数据库表中数据的查询速度。在没有索引的情况下，查询数据时，数据库需要从第一条记录开始逐条检查，直到找到匹配的行，...
MySQL 索引扩展
02-21
自己做的笔记，存在于印象笔记，主要讲了一些MySQL中索引的优化扩展，比如IPC，MRR等，还有具体的作用
MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项
09-10
MySQL索引是数据库管理系统中用于加速查询的关键组件，它们通过预排序数据来帮助快速定位表中的特定行。然而，尽管索引带来了显著的查询性能提升，但也存在一些缺点和使用时需要注意的事项。 首先，MySQL索引的缺点...
MYSQL索引知识
12-07
MySQL索引知识是数据库管理中至关重要的一部分，它能显著提高数据查询的速度，特别是在处理大量数据时。索引就像书的目录，使得数据检索更为高效。MySQL中的索引主要有两种存储类型：BTREE（B树）和HASH。 1. **...
MySQL索引检查.docx
12-10
MySQL索引检查是数据库优化的重要环节，它直接影响到查询性能。索引的作用在于加快数据检索速度，通过预先建立的数据结构，使数据库系统不必全表扫描就能快速定位到所需数据。以下将详细介绍标题和描述中涉及的几个...
mysql 索引与执行计划
10-21
### MySQL 索引与执行计划 #### 一、索引与执行计划 ##### 1.1 索引入门 在深入探讨之前，我们首先需要理解什么是索引以及其重要性。 ###### 1.1.1 索引是什么 索引（Index）在MySQL中是一种帮助数据库高效获取...
MySQL索引1
08-03
MySQL索引是数据库管理系统中用于加速数据检索的关键组成部分。它们通过预排序和存储数据的引用来减少查询数据所需的时间。本篇文章将深入探讨聚簇索引、非聚簇索引、覆盖索引以及MySQL中不同类型的索引。 首先，...
mysql 索引1
08-08
MySQL索引是数据库管理系统中用于加速查询的关键技术。在MySQL中，索引能够极大地提高查询效率，通过预先创建的数据结构，使数据库系统不必遍历整个数据表就能找到所需的数据。然而，索引也有其局限性，例如在某些...
mysql索引
最新发布
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值