分布式与中间件
MySQL

MySQL索引原理及应用场景

# 什么是索引

在工作当中，涉及到MySQL的查询，我们经常会遇到给某个表某个字段加索引的诉求，加上索引能够让我们的sql得到查询速度上的提升。但索引的原理是什么呢，他又是怎么工作的，需要开发者对基础知识有一定的了解。

# 索引分类

索引通过特定的一些数据结构，将数据组织有序，将随机I/O变为顺序I/O，有效减少查询一条数据所需要扫描的数据量，加快查询的速度。

从使用者的角度而言，MySQL索引主要为以下几种。

主键索引

根据数据表主键建立的索引，不允许重复和空值。主键索引列全表唯一

alter table 'table_name' add primary key pk_index_name('col_name');

唯一索引

用于确保建立索引列内的值是唯一的，允许空值。比如同样为age字段，不能出现2个age为10的数据

alter table 'table_name' add unique unique_index_name('col_name');

普通索引

用于表中的普通列构建的索引，没有特殊限制

alter table 'table_name' add index index_name('col_name');

联合索引

采用多个列组合构建的索引，用于组合搜索

alter table 'table_name' add index index_name('col1_name','col2_name');

全文索引

用于大文本对象的列构建索引

alter table 'table_name' add fulltext index full_index_name('col_name');

空间索引

用于GIS地图数据的索引，MySQL中的空间数据类型有GEOMETRY、POINT、LINESTRING、POLYGON

alter table 'table_name' add spatial index spa_index_name('col_name');

# 索引数据结构

根据数据结构的不同，MySQL的索引可分为哈希索引、BTree索引、B+Tree索引。

哈希索引

哈希索引通过hash表实现，通过将hash函数计算出的hash值存储在索引中，来完成key-value的快速查询

通常而言当发生hash碰撞之后(即两个key在经过hash函数之后值为同一个)，采用链地址法解决冲突，如上图中小刘到小王的连接线，获取key找到索引地址之后，顺序遍历地址内的链表，获得最终结果。

哈希索引的特点和hashmap类似，适合于精确查找，但不适合范围查找，hash码并不具有规律性，所以存储hash索引时顺序性是无法保障的。

BTree索引

关于平衡二叉搜索树的概念和意义可以详见我的另一篇文章👉二叉搜索树及AVL树详解 (opens new window)。

B树实际上是一棵多叉平衡搜索树。其具有以下特点

笔记

根节点左侧数值小于根节点，根节点右侧数值大于根节点

每个叶子节点高度一致

指针p存储节点的地址信息

非叶子节点为[key,data]二元组，其中key表示作为索引的键，data为key值所在行的数据

B树的结构如下图所示

在B树的结构图中，可以发现每个节点不仅仅包含数据的Key值，还有data值。

而每页的存储空间是有限的，如果data比较大，会导致每个节点的Key存储较少。

当数据量扩大时，B树只能往下进行分裂，导致树深的增高，增加了磁盘I/O的次数，影响查询效率。

B+Tree索引

为了解决B树的效率问题，B+树做了如下改进

笔记

非叶子节点只存储键值信息，不再存储数据

所有叶子节点之间都有一个链指针，指向下一个叶子节点

数据都存放在叶子节点中

B+树的结构如下图所示

为了更详细的描述B+树，图中的指针写明了存储的磁盘块名称，最底下一层含有数据的磁盘块被称为数据页，数据页之间通过双向链表进行连接，非叶子节点的磁盘块被称为索引页

将上图中磁盘块2与磁盘块5、6、7拿出来进行细分，我们展开各磁盘块，查看单个磁盘块内数据的排列方式

MySQL在存储数据时以数据页为最小单位，且数据在数据页中的存储是连续的，数据页中的数据是按照主键排序(如没有主键则由MySQL自身维护的ROW_ID排序)，而数据页与数据页之间则采用双向链表关联，数据与数据之间通过单向链表关联。

在每个数据页中，必然存在最小主键，每个数据页的最小主键+磁盘块地址会形成一个主键目录，多个主键目录会形成一个索引页(如磁盘块2结构所示)。

提示

索引页的出现就是为了解决数据页记录过多，导致主键目录特别大，影响二分查找效率的问题。

与数据页类似，如果一张数据页/索引页存储空间不够，则继续分裂到下一张，即结构图中展示的磁盘块1、2、3、4均为索引页

那么索引页和数据页到底是多大的大小呢？

引用一下网络中的解释

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。

基于该理论，我们可以大致算出B+树能够存储的数据。

假设数据库中一条记录是1KB，那么一个页就可以存16条数据（叶子结点）；对于非叶子结点存储的则是键值+指针，在InnoDB中，一个指针的大小是6个字节，假设我们的主键是bigint ，那么主键占8个字节，忽略一些其他的头信息大小，大致计算可以得到：

16*1024/(8+6)=1170

即一个非叶子节点可以指向1170个页，那么一个三层的B+树可以存储的数据量为：

1170*1170*16=21902400

大约2100万条数据。

在InnoDB存储引擎中，B+树的高度一般为2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次I/O，那我们通过主键索引查询的时候，其实最多只需要2-4次I/O操作就可以了。

# B+Tree的查询过程

还是基于B+树的结构图

假设我们要查询key=16的数据，通过最顶层索引页的条件，我们知道需要从磁盘块2的索引页进行查询。

依次比较磁盘块2中的key值，能够知道key>=12的数据均在p6指针指向的磁盘块内。

于是我们可以定位到磁盘块7对应的数据页，通过顺序遍历数据页内的单向链表，最终找到key=16对应的data数据行。

# 聚簇索引与非聚簇索引

作为非常高频的知识点，这两种索引通常会有如下解释

笔记

聚簇索引的叶子节点存储的是键值对应的数据本身

非聚簇索引的叶子节点存储的是键值对应的数据的主键键值

在Innodb中聚簇索引一般默认以主键建立，也称为主键索引

非聚簇索引则以其他非主键字段建立，也称为辅助索引

以下列数据表为例

如果采用聚簇索引的存储方式(主键为ID)，则对应的索引结构为

可以看出聚簇索引的数据和索引存储在一起，根据Id索引能够找到对应的数据行，比如where Id = 7这样的条件，聚簇索引能够查到对应主键，获得对应的叶子节点上的行数据。

如果以Name字段建立索引，则对应的辅助索引结构为

可以看出非聚簇索引的数据和索引，并非存储在一起，根据Name索引只能找到Name对应的主键Id，比如where Name = 张三，非聚簇索引需要通过两个步骤才能获取行数据。

第一步，在非聚簇索引的B+树中查询对应Name，获得叶子节点上匹配的Name的主键。
第二步，根据匹配的主键Id在聚簇索引B+树上再执行一次检索，获取叶子节点上的行数据。

整个过程可如下图所示

两者的主要区别在于非聚簇索引会多一次查询聚簇索引的过程

# 索引优化

# 回表

回表是什么？

假设有如下Sql执行

select * from student where name = '张三';

由于查询键为name，所以查询时走辅助索引树，很快就定位到了Name=张三的叶子节点，但由于查询条件为select *，辅助索引树中并没有包含全表字段，所以会再次根据Name=张三的主键Id查询主键索引树。

这个过程和刚才非聚簇索引的查询过程相同，通过辅助索引查询到字段，但仍需要走主键索引补全select信息的过程，叫做回表。

# 索引覆盖

索引覆盖就是执行的sql查询的字段刚好可以命中索引，不需要额外回表

例如

select id from student where id = 123

这样的查询能够命中索引，且索引字段和查询字段匹配

而例如

select id, name from student where id = 123

这样的查询则会回表

# 联合索引

作为下文的基础，这里先说一下联合索引。

假设现在有如下场景，按照市民的身份证号去查询他的家庭地址，这个查询需求在业务中出现的概率不高，但我们也不能让他直接走全表扫描。

如果单独的为一个不频繁的查询请求创建一个索引，感觉又有点浪费资源，这时候就可以通过建立联合索引，减少开销。

假设以数据库的a字段和b字段建立联合索引，a和b均为int类型字段

alter table 'table_name' add index index_name('a','b');

则生成的联合索引B+树会以联合索引字段共同建立，即联合索引的键值对大于1个

将索引的叶子节点按照字段拿出来进行分析，从左至右

a的顺序为: 1, 1, 2, 2, 3, 3

b的顺序为: 1, 2, 1, 4, 1, 2

大家可以发现a字段是有序排列，b字段是无序排列(因为B+树只能选择一个字段来构建有序的树)

同时通过观察还可以发现，在a相等的情况下，b字段是有序的，即此时a=1时，b=1，2，b保持相对有序。

在平时的sql中，我们经常会有个共识，当要对两个字段进行排序时，首先会按照第一个字段排序，如果第一个字段相等，则用第二个字段排序。

这种共识的原理就是因为，联合多字段建树时B+树的独特构建方式。

# 最左前缀原则

有了联合索引的概念，最左前缀原则就好理解了，由于B+树的建树方式，只有叶子节点存储数据，且叶子节点从左往右排序保持顺序，是最左前缀匹配的根本原因。

如果要查询a字段所有以1开头的数据，则对应的SQL条件为where a like '1%'。这个时候索引能够用上，查找到第一个符合条件的记录为ID-1,然后一直向后遍历，直到不满足条件为止。

再举一个能走联合索引的例子，如select * from student where a=1 and b=2。

首先a字段在B+树上是有序的，所以我们通过二分查找来定位到a=1的位置，其次在a确定的情况下，b是相对有序的。因为有序，所以我们同样可以通过二分查找找到b=2的位置。此时索引是有效的。

再举一个不能走联合索引的例子，如select * from student where b=2。

b字段有序的前提是：在a确定的情况下，现在查询条件中并没有规定a的范围，所以也无法确定b的顺序，在一棵无序的B+树上是无法用二分查找来定位到b字段的，这个时候索引失效。

# 索引下推

最左前缀可以用在索引中定位记录，那么那些不符合最左前缀的部分会怎么样呢？

还是以联合索引(a, b)为例，假设现在表内有a,b,c,d...多个字段，如果现在有一个需求：检索出表中a字段第一个数字为1，且b字段为2，c字段为3的所有数据。那么这条Sql语句则是这么写的：

select * from student where a like '1%' and b=2 and c=3;

根据前缀规则，这个语句在搜索索引树时，只能用1，找到第一个满足条件的记录ID-1。

之后则需要判断其他条件是否满足。在MySQL5.6之前，只能从ID1开始一个个回表。到主键索引上找出数据行，再对比字段值。

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

这两个过程可如下图所示。

无索引下推过程，均需要回表

索引下推过程

在这两个图中，每个箭头表示回表一次

在无索引下推过程中，InnoDB只需要观察a字段的值，按顺序把a字段第一个数值为1的记录一条条取出来进行回表，所以总计需要回表2次。

索引下推过程的区别在于，InnoDB在(a, b)索引内部就判断了b字段是否等于2，对于不等于2的记录，直接判断并跳过。在这个例子中，只需要对ID-2这一条记录回表取数据判断，只需要回表1次。

# 索引分析—执行计划

说了这么多，对于一条SQL，我们分析的思路应该是怎么样的呢？

MySQL提供了explain命令，可以用于对SQL的分析，根据分析出的结果，开发者可以针对性的进行优化

提示

后续操作基于如下数据表

create table if not exists testIndex.t_users
(
	id bigint auto_increment comment '自增ID'
		primary key,
	name varchar(50) not null comment '姓名',
	sex int not null comment '性别，0女，1男',
	age int not null comment '年龄',
	info json null comment '信息'
);

INSERT INTO testIndex.t_users (id, name, sex, age, info) VALUES (1, '张三', 1, 15, '{"text": "我是张三"}');
INSERT INTO testIndex.t_users (id, name, sex, age, info) VALUES (2, '李四', 1, 16, '{"text": "我是李四"}');
INSERT INTO testIndex.t_users (id, name, sex, age, info) VALUES (3, '王五', 0, 12, '{"text": "我是王五"}');

1
2
3
4
5
6
7
8
9
10
11
12
13

如何使用？

在需要执行的SQL前加上explain命令执行即可，如：

mysql> explain select * from t_users where id < 2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在explain输出的内容中，重点关注select_type，type，key，rows，Extra

select_type: 表示查询类型, 常见值表格如下

查询类型	解释
SIMPLE	表示查询语句不包含子查询或者union
PRIMARY	表示此查询最外层的查询
UNION	表示此查询是union的第二个或者后续的查询
DEPENDENT UNION	union中的第二个或者后续的查询语句使用了外面的查询结果
UNION RESULR	union的结果
SUNQUERY	select子查询语句
DEPENDENT SUBQUERY	select子查询语句依赖外层的查询结果

type: 表示存储引擎查询数据时采用的方式。是一个比较重要的属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。

它的常见属性如下(从上到下，查询效率依次增强)

ALL: 全表扫描

mysql> explain select * from t_users \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

index: 基于索引的全表扫描，先扫描索引再扫描全表(在排序上性能优于ALL)

mysql> explain select id from t_users \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

range: 表示使用索引的范围查询。用>,>=,<,<=等

mysql> explain select * from t_users where id > 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ref: 表示使用非唯一索引进行单值查询

先为age字段创建索引

mysql> alter table t_users add index `idx_t_user_age` (`age`);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t_users where age = 15 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: ref
possible_keys: idx_t_user_age
          key: idx_t_user_age
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

eq_ref: 一般情况下出现在多表join查询，表示前面表的每一行记录，都只能匹配后面表的一行结果

创建成绩表t_exam

create table if not exists testIndex.t_exam
(
	id bigint auto_increment comment '自增ID'
		primary key,
	user_id bigint not null comment '用户ID',
	score int null comment '成绩'
);

INSERT INTO testIndex.t_exam (id, user_id, score) VALUES (1, 1, 96);
INSERT INTO testIndex.t_exam (id, user_id, score) VALUES (2, 2, 86);
INSERT INTO testIndex.t_exam (id, user_id, score) VALUES (3, 3, 76);

1
2
3
4
5
6
7
8
9
10
11

采用多表join查询

mysql> explain select * from t_users tu left join t_exam te on tu.id = te.user_id where te.score = 96;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | te    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    3 |    33.33 | Using where |
|  1 | SIMPLE      | tu    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | testIndex.te.user_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

const: 表示使用主键或者卫衣索引做等值查询

mysql> explain select * from t_users where id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

null: 表示不用访问表，速度最快

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

possible_keys: 表示查询时能够使用到的索引，并不一定会真的使用，显示的是索引名称
key: 表示查询时真正用到的索引，显示的是索引名称
rows: MySQL查询优化器根据统计信息，估算要查询到结果需要扫描多少行记录。原则上rows越少效率越高。
key_len: 表示查询使用了索引的字节数量。可以判断是否全部使用了联合索引。
extra: 表示额外信息，通常有以下表现

using where: 表示查询需要通过索引回表查询数据。即通过普通索引查询出主键，再通过主键查询出数据。

mysql> explain select age from t_users where age = 15 and name = '张三';
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_users | NULL       | ref  | idx_t_user_age | idx_t_user_age | 4       | const |    1 |    33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7

using index: 表示通过索引就能查询出所需要的数据。效率最高

mysql> explain select age from t_users where age=1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_users | NULL       | ref  | idx_t_user_age | idx_t_user_age | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7

using filesort: 表示查询结果需要额外排序，数据量小在内存，数据量大在磁盘。如果出现这个字段，建议优化SQL

mysql> explain select * from t_users order by id;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_users | NULL       | index | NULL          | PRIMARY | 8       | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_users order by id,age;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

using temproary: 表示查询用到了临时表，一般出现在去重、分组等操作

mysql> explain select distinct name from t_users;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name, count(*) name from t_users group by name;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 2 warnings (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 索引失效场景

# 使用原则

使用联合索引应满足最左匹配原则
匹配数据从左至右，遇到范围查询(>、<、between、like)会停止匹配
尽量选择高区分度的字段作为索引

区分度公式：count(distinct col) / count(*)，表示字段不重复的比例，比例越大扫描的记录数越少。

索引尽量不用在高频更新的字段上，最好用在高频查询字段

# 失效场景

like '%xx'

select * from t_users where name like '%张';

首字母有序时，后续相对有序，但无法确定首字符时，查询是乱序的。

使用函数

比如将索引列放入函数计算中，就无法走索引，比如max(age)。其原因在于，B+树需要把所有元素都应用在函数中才能比较。

比如a,b,c三个字段，a和b建了索引，c没有建索引

当执行如下SQL时，能使用索引

select * from test where a = 1 or b = 2;

当执行如下SQL时，不能使用索引，a和b的索引失效

select * from test where a = 1 or b = 2 or c = 3;

使用select *

select *会采用全表扫描，即使建立了索引，索引也会进行回表，效率低下

字段类型不同

比如有个字段age，原本是varchar，但SQL查询时用的int类型传参，虽然查询能够成功，但实际上走的全表扫描，MySQL通过字段的隐式转换保证了SQL执行成功

select * from test where age = 123;

不等于

普通索引的不等于不会走索引

select * from test where name !='张三';

主键索引的不等于会走索引

select * from test where id ! = 123;

order by

order by排序条件为索引，则select字段必须也是索引字段，否则无法命中索引

如果对主键排序，则还是能够走索引

select * from test order by id desc;

范围查询

比如a,b,c三个字段，a、b、c都建了索引

select * from test where a = 1 and b > 2 and c = 3

只有a和b能够走索引，c不能走索引，范围查询就是索引截断处，原因和联合索引一致，在无法确定b有多少数量时，无法保证b的顺序性，在这个前提下导致c无法确定

# 小表驱动大表

这里补充一个额外的知识，小表驱动大表。

首先引入一张网络中流传的经典图，了解left join, inner join, right join等常见结果

所谓的小表驱动大表，可以理解为用小的数据集去匹配大的数据集。

比如表A中有10条数据，表B中有10w条数据，外部执行一次连接，内部要执行多次

按照小表驱动大表，A驱动B，则伪代码可写为

for (10条) {
    for (10w条) {
        ...
    }
}

1
2
3
4
5

大表驱动小表，即B驱动A，伪代码可写为

for (10w条) {
    for (10条) {
        ...
    }
}

1
2
3
4
5

两者的主要区别在于：小的循环在外层，表连接需要10次；大的循环在外层，表连接需要10w次。

小表驱动大表的目的是为了减少连接创建的次数，加快查询速度。

如何判断驱动表和非驱动表？

1 LEFT JOIN左连接, 左边为驱动表, 右边为被驱动表

2 RIGHT JOIN右连接, 右边为驱动表, 左边为被驱动表

3 INNER JOIN内连接, MySQL会选择数据量比较小的表作为驱动表，大表作为被驱动表

4 可通过EXPLANIN查看SQL语句的执行计划, EXPLANIN分析的第一行的表即是驱动表

# 总结

相信读完这篇文章，读者能够对索引和B+树的原理有一定的了解，在开发过程中解决慢SQL最为基本的分析方式可以采用explain，加上索引能够最简单有效的加快查询效率

除此之外还可以从网络、MySQL架构(读写分离/冷热备)等方向进行考虑，由于篇幅原因这里暂不介绍了~

# 参考文章

《MySQL45讲》

https://www.cnblogs.com/aaabbbcccddd/p/14864982.html (opens new window)

https://www.jb51.net/article/229580.htm (opens new window)

https://mp.weixin.qq.com/s/SSHWKcuGK5x4Uf7ZBC7stQ (opens new window)

https://blog.csdn.net/q736317048/article/details/114415571 (opens new window)

https://www.cnblogs.com/heishuichenzhou/p/10813463.html (opens new window)

https://mp.weixin.qq.com/s/tD_yDXBYfmLqN3BHmhOnfQ (opens new window)

https://blog.csdn.net/ABestRookie/article/details/117059948 (opens new window)