单点突破：MySQL之索引

阅读原文时间：2023年07月09日阅读：3

前言

开发环境：MySQL5.7.31

什么是索引

在MySQL中，索引(Index)是帮助高效获取数据的数据结构。

我们可以将数据库理解为一本书，数据库中的各个数据列（column）就是目录中的章节标题，行（row）就是章节的内容。而索引好比目录，将这些标题管理起来，这样我们找一篇文章的时候，可以根据标题在目录中寻找，这样比起一页一页翻书找标题快的多。

MySQL中索引最常用的数据结构是B+Tree，可以做到减少IO，加速查询，本篇在下文会详细描写B+树。另外还有一种数据结构是hash。

注：所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引

索引的优点

创建索引可以在很大程度上提升系统的性能
可以大大加快数据检索的速度，也可以减少分组、排序的时间
唯一索引还能保证数据库中每一行数据的唯一性

索引的缺点

创建索引需要耗费时间，随着数据量的增大所耗费的时间也会增加
索引也占有空间，所以设置了大量的索引会占用空间
对表数据增删改查的过程中也要动态维护索引，这样会增加增删改查的时间

通过对优缺点的学习不难发现索引并不是越多越好，只有合理使用索引才能发挥索引的作用，提高开发效率，在这里提及几个建立索引常用原则：

对于经常更新的表/字段，应该避免建立太多的索引；对于经常用于查询的表/字段，应该建立索引以加快查询速度点
数据量少的时候可以不用建立索引，数据量少的时候mysql认为走全文检索更快的时候会放弃走索引，所以创建索引就变成徒劳，并不会产生优化效果
如果某个字段（列）的区分度不高，也就是存在大量相同的值，举个例子，比如性别列，经常分为“男”和“女”，这种类型的字段尽量不要建立索引。
- 因为“性别”字段只有2种取值，唯一性太差了，建立索引数据库也不一定会用，就好比用where语句，where sex = "男"，这得查出多少数据呀。
- 另外从B+树的结构分析的话，像性别这样区分度低的字段建立的索引树可能就只有两个节点，跟线性查找的速度没什么区别，而且由于回表和索引维护的存在，也许速度还更慢。
  - 解释一下为什么会回表：性别字段因为可重复，所以只能建立非聚集索引，然而因为非聚集索引叶子节点存储的是索引值和聚集索引值，需要回表。
- 但是也有特例，并不是区分度不高的字段就没必要建立索引，这些分布中存在两极分化，并且分布非常少的数据被频繁查询，那么就可以对他建立索引了
  - 举个例子：某个枚举字段，有数据1,2,3。在大量的数据中，1只占了1%，2占了2%，3占了97%，很明显出现了两极分化的分布，同时，业务需要频繁查询1和2的数据，那么就可以对这个枚举字段建立索引了。

这些使用原则并不全面，了解对索引的使用还可以从哪些字段适合建立索引、索引的优化等方面入手。

适合建立索引

表的主键、外键必须有索引，且主键自动建立索引
数据量超过300最好建立索引
经常用于连接的表，连接字段上最好建立索引
经常出现在where子句中用于查询的字段
统计字段可以建立索引，例如count(),max()
排序字段可以建立索引，分组字段可以建立索引，因为分组的前提是排序

不适合建立索引

经常增删改的表的字段不适合建立索引
数据量太少不适合建立索引
通常在where子句中用不到的字段
区分度低的字段
参与列计算的列不适合建立索引

索引的分类

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引

索引列中的值必须是唯一的，但是允许为空值

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

也叫联合索引，指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则

最左前缀原则

也叫最左匹配原则，即最左优先，在检索数据时从组合索引的最左边开始匹配。

就是说一条sql语句用到了组合索引中最左边的索引，那么这条sql语句就可以利用这个组合索引去匹配。但是，如果遇到范围查询符号，如>, <, between, like的时候就会停止匹配。

下面举一个简单的例子来说明

比如我们建立一个组合索引(a,b)，这个组合索引包含了字段a和字段b，在where条件语句中，如下两个语句都能匹配：

a=1
a=1 and b=2

另外

b=2 and a=1

也会成功匹配，因为MySQL中的优化器会调整a, b的顺序，让它们与组合索引的顺序一致。

但是，单纯只有

b = 2

这种情况就匹配不到了

如果我们建立了索引为(a, b, c, d)，那么下面这种情况：

 a=1 and b=2 and c>3 and d=4

a,b,c三个字段能用到索引，但是d无法匹配到索引，因为之前的字段出现了范围查询

最左前缀原则的一些例子

例1：

如下语句，该如何建立组合索引？

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

不要只回到a,b,c。其实a,b,c、b,c,a、c,b,a等都可以，因为上面说过了mysql会自动调整顺序，但是顺序并不是随便放都可以，主要要将区分度高的字段放在前面，区分度低的字段放在后面。

例2

如下语句，该如何建立组合索引？

SELECT * FROM table WHERE a > 1 and b = 2;

不要回答a,b，这里应该建立b,a才对。如果是a,b，会因为a是范围查询，导致b匹配不到所以。因为mysql会自动调节顺序，b,a能让两个字段都匹配上。

例3

如下语句，该如何建立组合索引？

SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;

(b,a)或者(b,c)都可以

例4

如下语句，该如何建立组合索引？

SELECT * FROM `table` WHERE a = 1 ORDER BY b;

a,b。因为当a = 1的时候，b相对有序，可以避免再次排序！

那么

SELECT * FROM `table` WHERE a > 1 ORDER BY b;

如何建立索引？

对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。

例5

如下语句，该如何建立组合索引？

SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1;

还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

比如有文本“好好学习，天天向上…”，通过“学习”就能找到这条记录

聚集索引

聚集索引，也叫聚簇索引。数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引就好比字典按字母排序，一个汉语字典，我们希望查找“张”，我们可以直接翻到字典的最后，找到zh开头，然后找到张。因为字典内容本身是按照拼音排版的，所以字典内容本身就是一个聚集索引。

补充：

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

非聚集索引

也称为二级索引或辅助索引

就是我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx，没法用到主键索引的效率，怎么办，就需要我们添加辅助索引了，给name添加一个辅助索引。同样以字典为例，在查找一个不认识的字的时候，我们可以先通过字典的偏旁部首目录，找到字在哪一页，然后通过页码找到“张”。因为字典不是根据偏旁部首排版的，所以需要通过两步才能真正找到。

补充：

对于辅助索引（Secondary Index），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

区别于优缺点

聚集索引和非聚集索引
- 聚集索引的数据和索引一起放（会把表中的每行数据存储到索引的叶子节点中）
- 非聚集索引的数据和索引分开放
- 主键索引属于聚集索引；二级索引属于非聚集索引
- 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
  - 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
  - 非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
- 聚集索引优缺点
  - 优点：查询速度快，定位到了key就可以立刻拿到数据，不用回表
  - 缺点：维护成本高，一旦数据发生修改，对应的索引也要进行修改
- 非聚集索引优缺点
  - 优点：维护成本低
  - 缺点：可能需要回表，查询速度变慢
  - 注：回表：根据行号返回表中查找数据，扫描表是最慢的

覆盖索引将在优化部分讲到

索引的使用

创建表的时候创建索引

CREATE TABLE 表名(

字段名 数据类型 [完整性约束条件],

       ……，

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

说明：

UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
注：索引方法默认使用B+TREE。

单列索引(示例)：

CREATE TABLE projectfile (
    id INT AUTO_INCREMENT COMMENT '附件id',
    fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    projectid INT COMMENT '项目id;此列受project表中的id列约束',
    filename VARCHAR (512) COMMENT '附件名',
    fileurl VARCHAR (512) COMMENT '附件下载地址',
    filesize BIGINT COMMENT '附件大小，单位Byte',
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
    -- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
    FOREIGN KEY (projectid) REFERENCES project (id),
    -- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    UNIQUE INDEX (projectid),
    -- 给fileuploadercode字段创建普通索引
    INDEX (fileuploadercode)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表'

组合索引(示例)：

CREATE TABLE projectfile (
    id INT AUTO_INCREMENT COMMENT '附件id',
    fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    projectid INT COMMENT '项目id;此列受project表中的id列约束',
    filename VARCHAR (512) COMMENT '附件名',
    fileurl VARCHAR (512) COMMENT '附件下载地址',
    filesize BIGINT COMMENT '附件大小，单位Byte',
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
        -- 创建组合索引
    INDEX (fileuploadercode,projectid)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码

建表后创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

或

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；

添加主键索引：alter table user add primary key(id)
添加唯一索引：alter table user add unqiue(id)
添加普通索引：alter table user add index index_name(id)
添加前缀索引：alter table user add index(username(4))
添加全文索引：alter table user add fulltext(username)
添加多列索引：alter table user add index index_name(id, age, gender)

（了解语法是show index即可）

查看索引的语法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

语法说明如下：

<表名>：指定需要查看索引的数据表名。
<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

示例：

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 1. row ***************************
        Table: tb_stu_info2
   Non_unique: 0
     Key_name: height
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.03 sec)

其中各主要参数说明如下：

Table：表示创建索引的数据表名，这里是 tb_stu_info2 数据表。
Non_unique：表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name：表示索引的名称。
Seq_in_index：表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name：表示定义索引的列字段。
Collation：表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality：索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part：表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed：指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null：用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment：显示评注。

基本语法：

当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。

使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

语法说明如下：

<索引名>：要删除的索引名。
<表名>：指定该索引所在的表名。

使用 ALTER TABLE 语句

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

删除索引实例

【实例 1】删除表 tb_stu_info 中的索引，输入的 SQL 语句和执行结果如下所示。

mysql> DROP INDEX height
    -> ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

【实例 2】删除表 tb_stu_info2 中名称为 id 的索引，输入的 SQL 语句和执行结果如下所示。

mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

B+树

二叉树：

二叉树是一种二分查找树，有很好的查找性能，相当于二分查找。

但是当N比较大的时候，树的深度比较高。数据查询的时间主要依赖于磁盘IO的次数，二叉树深度越大，查找的次数越多，性能越差。

最坏的情况是退化成了链表，如下图

为了让二叉树不至于退化成链表，人们发明了AVL树（平衡二叉搜索树）：

平衡二叉树（Balanced Binary Tree）又被称为AVL树（有别于AVL算法），且具有以下性质：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多。

而后还有多叉树

多叉树就是节点可以是M个，能有效地减少高度，高度变小后，节点变少I/O自然少，性能比二叉树好了

B树

B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。

例如要查找9，步骤如下

我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。

B+树

B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了

例如要查找关键字16，步骤如下

与根节点的关键字 (1，18，35) 进行比较，16 在 1 和 18 之间，得到指针 P1（指向磁盘块 2）
找到磁盘块 2，关键字为（1，8，14），因为 16 大于 14，所以得到指针 P3（指向磁盘块 7）
找到磁盘块 7，关键字为（14，16，17），然后我们找到了关键字 16，所以可以找到关键字 16 所对应的数据。

B+树与B树的不同：

B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据
B+树查询效率更高。B+树使用双向链表串连所有叶子节点，区间查询效率更高（因为所有数据都在B+树的叶子节点，扫描数据库只需扫一遍叶子结点就行了），但是B树则需要通过中序遍历才能完成查询范围的查找。
B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定
B+树的磁盘读写代价更小。B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，通常B+树矮更胖，高度小查询产生的I/O更少。

可以从这几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数等等。

为什么不使用哈希结构？我们知道哈希结构，类似k-v结构，也就是，key和value是一对一关系。它用于等值查询还可以，但是范围查询它是无能为力的哦。

为什么不使用二叉树？如果二叉树特殊化为一个链表，相当于全表扫描。那么还要索引干嘛。

为什么不使用平衡二叉树？它也是一颗二叉查找树，任何节点的两个子树高度最大差为1。所以就不会出现特殊化一个链表的情况啦。但是，平衡二叉树插入或者更新是，需要左旋右旋维持平衡，维护代价大如果数量多的话，树的高度会很高。因为数据是存在磁盘的，以它作为索引结构，每次从磁盘读取一个节点，操作IO的次数就多啦。

为什么不使用B树？B+树是B树的升级版，详见两者的区别。

这里我放上一篇很好的索引面试场景题博客：https://www.cnblogs.com/jay-huaxiao/p/14352349.html

分别从以下几点开展：

面试官考点之索引是什么？
面试官考点之索引类型
面试官考点之为什么选择B+树作索引结构
面试官考点之一次索引搜索过程
面试官考点之覆盖索引
面试官考点之索引失效场景
面试官考点之最左前缀
面试官考点之索引下推
面试官考点之大表添加索引

explain关键字详解

详见我另一篇博客：https://www.cnblogs.com/kylinxxx/p/14403135.html

索引优化的建议

在使用InnoDB存储引擎时，如果没有特别的需要，尽量使用一个与业务无关的递增字段作为主键，主键字段不宜过长。如常用的雪花算法生成主键

尽量在索引列上完成分组、排序，遵循索引最左前缀法则，如果order by的条件不在索引列上，就会产生Using filesort，降低查询性能。

当查询语句的where条件或group by、order by含多列时，可根据实际情况优先考虑联合索引(multiple-column index)，这样可以减少单列索引(single-column index)的个数，有助于高效查询。

建立联合索引时要特别注意column的次序，应结合上面提到的最左前缀法则以及实际的过滤、分组、排序需求。区分度最高的建议放最左边。

再强调一次最左前缀原则中提及的例子，在order by中是经常使用到的

order by的字段可以作为联合索引的一部分，并且放在最后，避免出现file_sort的情况，影响查询性能。正例：where a=? and b=? order by c会走索引idx_a_b_c，但是WHERE a>10 order by b却无法完全使用上索引idx_a_b，只会使用上联合索引的第一列a
存在非等号和等号混合时，在建联合索引时，应该把等号条件的列前置。如：where c>? and d=?那么即使c的区分度更高，也应该把d放在索引的最前列，即索引idx_d_c
如果where a=? and b=?，如果a列的几乎接近于唯一值，那么只需要建立单列索引idx_a即可

索引列上做任何操作(表达式、函数计算、类型转换等)时无法使用索引会导致全表扫描

MySQL分页查询大多数写法可能如下：

select * from table limit offset,N;

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下。

可以对超过特定阈值的页数进行SQL改写如下：

先快速定位需要获取的id段，然后再关联

select a.* from table a, (select id from table where 条件 limit 100000,20 ) b where a.id = b.id;

或者

select a.* from table a inner join (select id from table where 条件 limit 100000,20) b on a.id = b.id;

记住前文说的索引使用原则，总结成较短的话

索引文件具有B+Tree最左前缀匹配特性，如果左边的值未确定，那么无法使用索引，所以应尽量避免左模糊(即%xxx)或者全模糊(即%xxx%)。简单的说就是百分号后的索引会失效。

原则：小表驱动大表，即小的数据集驱动大的数据集

（1）当A表的数据集大于B表的数据集时，in优于exists

select * from A where id in (select id from B)

（2）当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)

如果 where 条件的列和 select 的列都在一个索引中，通过这个索引就可以完成查询，这就叫就叫覆盖索引；当然，覆盖索引基本针对的是组合索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

利用覆盖索引(covering index)来进行查询操作，避免回表，从而增加磁盘I/O。换句话说就是，尽可能避免select *语句，只选择必要的列，去除无用的列。

详情可以看这篇专利：https://zhuanlan.zhihu.com/p/107125866

阿里巴巴Java开发手册中有这样的规约：

不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关【说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行】。

count(distinct col)计算该列除NULL之外的不重复行数，注意count(distinct col1, col2)如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0

另外，InnoDB对count(*)、count(1)的处理完全一致。

(1) 需要join的字段，数据类型必须绝对一致；

(2) 多表join时，保证被关联的字段有索引

索引下推

索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。索引下推需要明白什么是覆盖索引和回表，详见上一节的覆盖索引。

当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。
使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

博主青石路的索引下推写的很好，同样他的博文也很优秀，我就不在这里做更多的记录了，直接放上他的索引下推博客地址：https://www.cnblogs.com/youzhibing/p/12318565.html

实际开发中可能遇见的问题

放一篇敖丙的文章：https://www.cnblogs.com/aobing/p/13623703.html

如果被问到这个问题，可以从b+树展开，并说到减少磁盘IO的次数，毕竟索引快就是通过索引的结构最大化的减少数据库的IO次数

很可能是没用到索引，摘抄一个网上看到的例子：

某单表写入了近2亿条数据，过程中发现配的报表有几个数据查询时间太长，所以重点看了几个慢查询SQL。避免敏感信息，这里对其提取简化做个记录。

mysql> select count(*) from tb_alert;
+-----------+
| count(*)  |
+-----------+
| 198101877 |
+-----------+

表join后，取前10条数据就花了15秒，看了下SQL执行计划，如下：

mysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 10;
10 rows in set (15.46 sec)

mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 10;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows      | filtered | Extra                                              |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tb_alert           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 190097118 |   100.00 | NULL                                               |
|  1 | SIMPLE      | tb_situation_alert | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   8026988 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看出join的时候没有用上索引，tb_situation_alert表上联合主键是这样的PRIMARY KEY (situation_id, alert_id)，参与表join字段是alert_id，原来是不符合联合索引的最左前缀法则，仅从这条sql看，解决方案有两种，一种是对tb_situation_alert表上的alert_id单独建立索引，另外一种是调换联合主键的列的次序，改为PRIMARY KEY (alert_id, situation_id)。当然不能因为多配一张报表，就改其他产线的表的主键索引，这并不合理。在这里，应该对alert_id列单独建立索引。

mysql> create index idx_alert_id on tb_situation_alert (alert_id);

mysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 100;
100 rows in set (0.01 sec)

mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 100;
+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key          | key_len | ref                             | rows      | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+
|  1 | SIMPLE      | tb_alert           | NULL       | ALL  | NULL          | NULL         | NULL    | NULL                            | 190097118 |   100.00 | NULL  |
|  1 | SIMPLE      | tb_situation_alert | NULL       | ref  | idx_alert_id  | idx_alert_id | 8       | tb_alert.alert_id |         2 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

优化后，执行计划可以看出join的时候走了索引，查询前100条0.01秒，和之前的取前10条数据就花了15秒天壤之别。

从第10000000条数据往后翻页时，25秒才能出结果，这里就能使用上面的分页查询优化技巧了。上面讲优化建议时，没看执行计划，这里正好看一下。

mysql> select * from tb_alert limit 10000000, 10;
10 rows in set (25.23 sec)

mysql> explain select * from tb_alert limit 10000000, 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+-----------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows      | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-----------+----------+-------+
|  1 | SIMPLE      | tb_alert | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 190097118 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+-----------+----------+-------+
1 row in set, 1 warning (0.00 sec)

再看下使用上分页查询优化技巧的sql的执行计划

mysql> select * from tb_alert a inner join (select alert_id from tb_alert limit 10000000, 10) b on a.alert_id = b.alert_id;
10 rows in set (2.29 sec)

mysql> explain select * from tb_alert a inner join (select alert_id from tb_alert a2 limit 10000000, 10) b on a.alert_id = b.alert_id;
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key           | key_len | ref       | rows      | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL          | NULL    | NULL      |  10000010 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY       | 8       | b.alert_id |         1 |   100.00 | NULL        |
|  2 | DERIVED     | a2         | NULL       | index  | NULL          | idx_processed | 5       | NULL      | 190097118 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

分组聚合慢

分析SQL后，发现根本上并非分组聚合慢，而是扫描联合索引后，回表导致性能低下，去除不必要的字段，使用覆盖索引。

这里避免敏感信息，只演示分组聚合前的简化SQL，主要问题也是在这。

表上有联合索引KEY idx_alert_start_host_template_id ( alert_start, alert_host, template_id)，优化前的sql为

mysql> select alert_start, alert_host, template_id, alert_service from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
10000 rows in set (1 min 5.22 sec)

使用覆盖索引，去掉template_id列，就能避免回表，查询时间从1min多变为0.03秒，如下：

mysql> select alert_start, alert_host, template_id from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
10000 rows in set (0.03 sec)

mysql> explain select alert_start, alert_host, template_id from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys                      | key                                | key_len | ref  | rows     | filtered | Extra                    |
+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
|  1 | SIMPLE      | tb_alert | NULL       | range | idx_alert_start_host_template_id   | idx_alert_start_host_template_id   | 9       | NULL | 95048559 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

手机扫一扫

移动阅读更方便

你可能感兴趣的文章

JavaScript高级学习

Web通用漏洞--sql注入

Cookies 完全指南