MySQL-索引原理

MySQL-索引原理

转载声明：

本文系转载自

• BAT面试锦囊——MySQL索引原理为什么索引可以加速查询？
  出处：今日头条
  作者：Java学院

• MySql数据库索引原理
  出处：cnblogs
  作者：A-祥子

• MySQL索引原理及慢查询优化
  出处：美团技术团队博客
  作者：NeverMore

• MySQL索引背后的数据结构及算法原理
  出处：CodingLabs
  作者：张洋

• 同一个SQL语句，为啥性能差异咋就这么大呢？（1分钟系列）
  出处：架构师之路
  作者：58沈剑

• 如何利用工具，迅猛定位低效SQL？ | 1分钟系列
  出处：架构师之路
  作者：58沈剑

• 数据库索引，到底是什么做的？
  出处：架构师之路
  作者：58沈剑

• 如何避免回表查询？什么是索引覆盖？ | 1分钟MySQL优化系列
  出处：架构师之路
  作者：58沈剑

转载仅为方便学习查看，一切权利属于原作者，本人只是做了整理和排版，如果带来不便请联系我删除。

摘要

说到索引，很多人都知道“索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。”

考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

以下一个例子引出数据库为什么要设计索引：

• 图书馆存了1000W本图书，要从中找到《架构师之路》，一本本查，要查到什么时候去？
  于是，图书管理员设计了一套规则以便快速找到一本书：

• 一楼放历史类，二楼放文学类，三楼放IT类…

• IT类，又分软件类，硬件类…

• 软件类，又按照书名音序排序…

  与之类比，数据库存储了1000W条数据，要从中找到name=”shenjian”的记录，一条条查，要查到什么时候去？
  于是，要有索引，用于提升数据库的查找速度。

但是索引是怎么实现的呢？因为索引并不是关系模型的组成部分，因此不同的DBMS有不同的实现，我们针对MySQL数据库的实现进行说明。本文内容涉及MySQL中索引的语法、索引的优缺点、索引的分类、索引的实现原理、索引的使用策略、索引的优化几部分。

注：下图来自网络

1 选择合适的数据类型

在考虑使用索引之前，我们先考虑数据字段类型是否最优，选择合适的数据类型存储数据对性能有很大的影响：

• 越小的数据类型通常更好
  越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
• 简单的数据类型更好
  整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
• 尽量避免NULL
  应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
• 选择合适的标识符
  选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
  • 整型
    通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。
  • 字符串
    尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

2 基本概念

2.1 索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

2.1.1 主键索引

即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY('col')；

2.1.2 唯一索引

用来建立索引的列的值必须是唯一的，不允许重复，但允许空值

ALTER TABLE 'table_name' ADD UNIQUE('col')；

2.1.3 普通索引

用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

2.1.4 全文索引

用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE 'table_name' ADD FULLTEXT('col')；

2.1.5 组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

更详细内容请参阅组合索引示例

2.2 最左前缀

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是单独使用时是不能使用索引的。

但col1 col3这样组合在一起是可以使用col1的索引的。

2.3 前缀索引

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引(前缀索引)。以下表示使用col1的前4个字符和col2的前3个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

2.4 回表

回表的意思就是不能直接从当前索引锁拿到所有所需列的值作为结果直接返回，而还需要回数据主表查询。

• InnoDB聚集索引的叶子节点存储行记录，InnoDB普通索引的叶子节点存储主键值。
• MyISAM的索引叶子节点存储记录指针

比如有一个innodb表

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

此时主键id索引为聚集索引，name为普通索引，则这两个B+树索引如下图：

• id为PK，聚集索引，叶子节点存储行记录；
• name为KEY，普通索引，叶子节点存储PK值，即id列的值；
  普通索引的查询需要扫码两遍索引树

如果有以下语句

select * from t where name='lisi';

则执行过程如下：

查找路径为上图粉红色标注路径，需要扫码两遍索引树：

1. 先通过普通索引定位到主键值id=5；
2. 再通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较仅扫一遍索引树更低。

2.5 覆盖索引(Covering Indexes)

2.5.1 官方定义

SQL-Server官网的说法:

MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖:

不管是SQL-Server官网，还是MySQL官网，都表达了：

• 使用覆盖索引时，只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度很快。

2.5.2 详解

覆盖索引不是一种索引类型，而是指索引包含满足查询的所有数据，不用再回表查询了。

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据，他有以下一些优点：

• 索引项通常比记录要小，所以MySQL访问更少的数据；

• 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

• 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

• 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

  覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到Using index。

例如，在sakila的inventory表中，有一个组合索引(store_id,film_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: inventory
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 5007
        Extra: Using index
1 row in set (0.17 sec)

在大多数引擎中，只有当查询语句所访问的列是索引的子集时，才会覆盖索引。

但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。因此，sakila.actor表使用InnoDB，actor_id为主键列，而且对于last_name上有二级索引，所以，索引能覆盖那些访问主键actor_id的查询，如：

mysql> EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER'\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
        Extra: Using where; Using index

2.5.3 覆盖索引实现方法

常见的方法是：将被查询的字段，建立到联合索引里去。

如有下表

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

第一个SQL语句：

能够命中name索引，而且索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

画外音，Extra：Using index。

第二个SQL语句：

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

画外音，Extra：Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了：

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

可以看到：

select id,name ... where name='shenjian';
select id,name,sex ... where name='shenjian';

都能够命中索引覆盖，无需回表。

2.5.4 覆盖索引优化场景

2.5.4.1 场景1：全表count查询优化

原表为：

user(PK id, name, sex)；

直接：

select count(name) from user;

不能利用索引覆盖。

添加索引：

alter table user add key(name);

就能够利用索引覆盖提效。

2.5.4.2 场景2：列查询回表优化

select id,name,sex ... where name='shenjian';

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

2.5.4.3 场景3：分页查询

select id,name,sex ... order by name limit 500,100;

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

2.6 利用索引进行排序

MySQL中，有两种方式生成有序结果集：

2.6.1 使用filesort

2.6.1.1 概述

不使用索引来排序时，就会速度很慢。

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。

2.6.1.2 排序算法

对于filesort，MySQL有两种排序算法：

• 两遍扫描算法(Two passes)
  实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息去磁盘上读取文件数据取出所需的完整Columns。

  注：该算法是MySql4.1之前采用的算法，特点如下：

  • 它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作;
  • 另一方面，内存开销较小。

• 一次扫描算法(single pass)
  该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。

  注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。

  在 MySQL 4.1 之后的版本中，可以通过设置max_length_for_sort_data参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于max_length_for_sort_data的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。

  为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

2.6.1.3 join与filesort

• 当对join操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL先对该表进行filesort操作，然后进行join处理，此时，EXPLAIN输出Using filesort(注：此结论未证实，我打出的explain结果不是这样)；
• 否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出Using temporary;Using filesort。

2.6.2 按索引顺序扫描

利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。

当索引的顺序与ORDER BY中的列顺序相同，且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。

如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

2.6.3 示例

有表如下：

create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name      varchar(16) NOT NULL DEFAULT '',
password        varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
 KEY     (name)
) ENGINE=InnoDB

插入如下记录：

insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');

使用索引排序：

mysql> explain select actor_id from actor order by actor_id \G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: actor
         type: index
possible_keys: NULL
         key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

使用非索引列排序时不能使用索引，而是filesort：

mysql> explain select actor_id from actor order by password \G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set (0.00 sec)

使用二级索引列name排序时，使用索引排序的情况：

mysql> explain select actor_id from actor order by name \G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: actor
         type: index
possible_keys: NULL
          key: name
      key_len: 18
          ref: NULL
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

2.7 索引与加锁

索引对于InnoDB非常重要，因为它可以让查询时锁定更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：

• 首先，即使InnoDB行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。
• 其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。

InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。

来看个例子：

create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name      varchar(16) NOT NULL DEFAULT '',
password        varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
 KEY     (name)
) ENGINE=InnoDB

insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');

SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id < 4
AND actor_id <> 1 FOR UPDATE;

该查询仅仅返回2---3的数据，实际已经对1—3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）：

mysql> EXPLAIN SELECT actor_id FROM test.actor
    -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: actor
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using index
1 row in set (0.00 sec)

以上Explain结果表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。

为了证明row 1已经被锁住，我们另外建一个连接，执行如下操作：

SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。

如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

2.8 组合索引示例

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

比如有如下表，其中last_name, first_name, dob构建了组合索引：

CREATE TABLE People (
   last_name varchar(50)    not null,
   first_name varchar(50)    not null,
   dob        date           not null,
   gender     enum('m', 'f') not null,
   key(last_name, first_name, dob)
);

则索引表结构如下：

可见，组合索引时，每个节点中的数据按所有索引列的从左到右的列进行依次排序，比如上图中先比较last_name，再比较first_name，最后比较dob。

索引使用规则如下：

• 匹配全值(Match the full value)：
  对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
• 匹配最左前缀(Match a leftmost prefix)：
  你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
• 匹配列前缀(Match a column prefix)：
  例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
• 匹配值的范围查询(Match a range of values)：
  可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
• 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：
  可以利用索引查找last name为Allen，而first name以字母K开始的人，此时可使用索引列的第一和第二列。
• 仅对索引进行查询，即覆盖索引(Index-only queries)：
  如果查询的列都位于索引中，则不需要回表查询数据。
• 可排序(order by)
  由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY

不能使用索引的情况：

• 查询必须从索引的最左边的列开始
  关于这点已经提了很多遍了。例如你不能利用组合索引的第三列dob来查找某一天出生的人。
• 不能跳过某一索引列
  例如，你不能利用索引查找last name为Smith且出生于某一天的人。
• 存储引擎不能使用索引中范围条件右边的列
  例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE操作是范围查询。

3 索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等，

3.1 哈希索引(Hash索引)

3.1.1 概念

只有Memory（内存）存储引擎支持哈希索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置。

Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中(可以联想hashmap)。

3.1.2 示例

假设创建如下一个表：

CREATE TABLE testhash (
   fname VARCHAR(50) NOT NULL,
   lname VARCHAR(50) NOT NULL,
   KEY USING HASH(fname)
) ENGINE=MEMORY;

包含的数据如下：

假设索引使用hash函数f( )，如下：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

此时索引的结构大概如下：

Slot指针是有序的，但是记录不是有序的。

当你执行

SELECT lname FROM testhash WHERE fname='Peter';

MySQL会计算Peter的hash值，然后通过它来查询索引的行指针即8784，得到指向row3的指针。

3.1.3 小结

注意，InnoDB并不支持哈希索引。

• 因为索引自己仅仅存储很短的值，所以，hash索引非常紧凑。

• Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

• 优点
  因为使用散列算法，因此访问速度非常快

• 缺点

  • 一个值只能对应一个hashCode（memory引擎除外），而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。
  • 由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。
  • Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
  • Hash索引只支持等值比较，例如使用 =，IN( )和<=>。对于WHERE price>100并不能加速查询。

3.1.4 哈希(hash)比树(tree)更快，索引结构为什么要设计成树型？

加速查找速度的数据结构，常见的有两类：

• 哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是O(1)；
• 树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(lg(n))；

索引设计成树形，和SQL的需求相关。

• 对于这样一个单行查询的SQL需求：

  select * from t where name=”shenjian”;
  

  确实是哈希索引更快，因为每次都只查询一条记录。

  画外音：所以，如果业务需求都是单行访问，例如passport，确实可以使用哈希索引。

• 但是对于排序查询的SQL需求：

  • 分组：group by
  • 排序：order by
  • 范围查询比较：<、>
  • 等等

  此时，哈希型的索引需要遍历执行每个元素挨个对比，时间复杂度会退化为O(n)效率很低；而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

  此外，Hash表需要大量内存空间。
  

注意，InnoDB并不支持哈希索引。

3.2 全文索引

3.2.1 概念

生产环境一般没有公司直接用数据库做全文索引，而是用ES。

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB。

针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，此时可使用时FULLTEXT索引。

在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

3.2.2 创建

FULLTEXT索引可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

//创建表的时候添加FULLTEXT索引 
CTREATE TABLE my_table( id INT(10) PRIMARY KEY, name VARCHAR(10) NOT NULL, my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, FULLTEXT(my_text));

//创建表以后，在需要的时候添加FULLTEXT索引 
ALTER my_table ADD FULLTEXT ft_index(my_text); CREATE INDEX ft_index ON my_table(my_text);

3.2.3 注意事项

• 导入大量数据时后建全文索引
  对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

• MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。

• 在MySQL中，全文索引支队英文有用，目前对中文还不支持。

• 在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

  此外，如果检索的字符包括停止词，那么停止词会被忽略。

3.3 BTree索引和B+Tree索引

更多关于B树可参考数据结构-常用树总结

动态使用各种数据结构看效果

3.3.1 BTree索引

3.3.1.1 概述

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

• 不再是二叉搜索，而是m叉搜索；
• 叶子节点，非叶子节点，都存储数据；
• 中序遍历，可以获得所有节点；
• 每个叶子结点的高度一样，等于h；
• 每个非叶子结点由n-1个key和n个指针pointer组成，其中d<=n<=2d。
• key和point相互间隔，节点两端一定是pointer；
• 叶子结点指针都为null；
• 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据（聚簇索引才是）；

B树：

3.3.1.2 BTree与聚簇索引

聚簇索引的BTree的结构如下：

在BTree的结构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说BTree是很胖很矮的，高度一般为3左右，因此BTree是一个非常高效的磁盘查找结构（降低磁盘IO次数）。

3.3.1.3 BTree与局部性原理

B树被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”。

局部性原理的逻辑是这样的：

• 内存读写块，磁盘读写慢，而且慢很多；

• 磁盘预读
  磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

  画外音：通常，一页数据是4K。

• 局部性原理
  软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

3.3.1.4 BTree为何适合做索引？

• 由于是m分叉的，高度能够大大降低；
• 每个节点可以存储j个记录，如果将节点大小设置为页大小，例如4K，能够充分的利用预读的特性，极大减少磁盘IO；

3.3.2 B+Tree索引

3.3.2.1 概述

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

• B+Tree中的非叶子结点不存储数据，只存储键值；

• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应的数据的物理地址（非聚簇索引）；

• B+Tree的数据只存储在同一层的叶子节点上

  画外音：B+树中根到每一个节点的路径长度一样，而B树不是这样。

• 叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

B+Tree的结构如下：

3.3.2.2 b+树的查找过程

1. 如上图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，
2. 通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，
3. 通过P2指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

3.3.2.3 B+树作为索引时一些重要的点

1. 每个磁盘快数据项尽量小的原因
   通过上面的分析，我们知道IO次数取决于b+树的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N（m+1为log底数），所以当数据量N一定的情况下，m越大，h越小；

   而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小（4K），是固定的，如果数据项占的空间越小，则一个磁盘块（B数一个节点）的数据项的数量 m 更多，结果就是树的高度更低，可有效减少磁盘IO次数。

   这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点：因为一旦放到内层节点，磁盘块的数据项数量会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

   B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：
   dmax=floor(pagesize/(keysize+datasize+pointsize))

   其中floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

2. 最左匹配
   当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当张三,20,F这样的数据检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

   但当20,F这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

   比如当张三,F这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了。

   这个是非常重要的性质，即索引的最左匹配特性。

3.3.2.4 B+ Tree对比B Tree的优势

• 范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；

  画外音：范围查询在SQL中用得很多，这是B+树比B树最大的优势。

• 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；

• B+树能够存储更多索引
  非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构：

• 即磁盘的一个page（页）是整数倍的扇区（512字节），页是存储中的一个单位，通常默认为4K。因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找。

已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O。那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

检索一次最多需要访问h个节点，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现时还需要使用如下技巧：

• 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

3.3.3 带顺序索引的B+TREE

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

带顺序索引的B+Tree的结构如下：

3.3.4 其他数据结构

数据库索引为什么使用B+树？

单介绍下BST 二叉搜索树

二叉搜索树，如上图，是最为大家所熟知的一种数据结构，就不展开介绍了，它为什么不适合用作数据库索引？

• 当数据量大的时候，树的高度会比较高，查询会比较慢，IO次数过多；
• 每个节点只存储一个记录，可能导致一次查询有很多次磁盘IO；

m叉的B+树比二叉搜索树的高度大大降低的原因？
大概计算一下：

• 局部性原理，将一个节点的大小设为一页，一页4K，假设一个KEY有8字节，一个节点可以存储500个KEY，即j=500

• m叉树，大概m/2<= j <=m，即可以差不多是1000叉树

• 那么：

  • 一层树：1个节点，1*500个KEY，大小4K
  • 二层树：1000个节点，1000500=50W个KEY，大小10004K=4M
  • 三层树：10001000个节点，10001000500=5亿个KEY，大小10001000*4K=4G

• 可以看到，存储大量的数据（5亿），并不需要太高树的深度（高度3），索引也不是太占内存（4G）。

3.4 存储引擎对索引的实现

3.4.1 概述

分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”。使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

3.4.2 MyISAM - 非聚簇索引

3.4.2.1 概述

• MyISAM存储引擎采用的是非聚簇索引
  非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
• 非聚簇索引的数据表和索引表是分开存储。
• 非聚簇索引中的数据是根据数据的插入顺序保存。
  因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
• 只有在MyISAM中才能使用FULLTEXT索引。

最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

3.4.2.2 主键索引

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

下面我们举一个例子来说明MyISAM中索引的原理。

设表一共有三列，假设我们以Col1为主键，则下图表示一个MyISAM表的主索引（Primary key）：

3.4.2.3 二级索引

从上图可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，此索引的结构如下图所示：

MyISAM的二级索引同样也是一颗B+Tree，但是MyISAM data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

所以，MyISAM的索引方式也叫做非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分。

3.4.3 InnoDB - 聚簇索引

3.4.3.1 概述

概念

• 第一个重大区别是InnoDB的数据文件本身就是索引文件。

  • 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身（完整的数据），而内节点页面仅包含索引的列(索引的列为整型)；
  • 而辅助索引的叶子结点存储的是键值对应的数据的主键键值。
    因此主键的值长度越小越好，类型越简单越好。
  • 应用上来说，在InnoDB中如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率，不需要回表查询，即覆盖索引

• 聚簇索引的数据和主键索引存储在一起。

• 聚簇索引的数据是根据主键的顺序保存，这和非聚簇索引不同（这种索引下数据是就是按插入顺序保存）。
  因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。

  但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。

注意事项：

• 使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

• 因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低维护成本，因为这时不用维护辅助索引。但是辅助索引会占用更多的空间。

• 聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检验主键是否重复，这需要遍历主索引的所有叶节点；

  而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，

  但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

• 如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。

  最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用primary key进行连接的查询的性能。

  应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机，容易导致节点频繁分裂，影响性能。

• InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引

  如果表定义了PK，则PK就是聚集索引；

  如果你不为您的表定义主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引;

  如果没有这样的列，InnoDB就自己产生一个这样的ID值（RowId），它有六个字节，而且是隐藏的，使其作为聚簇索引。

• 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

3.4.3.2 主键索引

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

3.4.3.3 二级索引

第二个与MyISAM索引的不同是，InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域，这一点相当重要！。例如，下图为定义在Col3上的一个辅助索引：

上例中以英文字符的ASCII码作为比较准则。

3.4.3.4 小结

• 聚集索引这种实现方式使得按主键的搜索十分高效，
• 但是辅助聚集索引搜索需要检索两遍索引：首先检索辅助索引获得主键值，然后用主键值到主索引中检索获得记录。

3.4.4 聚簇索引对比非聚簇索引

下图可以形象的说明聚簇索引和非聚簇索引的区别：

举个具体例子，比如如下表：

CREATE TABLE layout_test (
   col1 int NOT NULL,
   col2 int NOT NULL,
   PRIMARY KEY(col1),
   KEY(col2)
);

假设主键的值位于1---10,000之间，且按随机顺序插入，然后用OPTIMIZE TABLE进行优化。col2随机赋予1---100之间的值，所以会存在许多重复的值。

3.4.4.1 MyISAM

MyISAM按照插入的顺序在磁盘上存储数据，因为元组的大小固定，所以MyISAM可以很容易的从表的开始位置找到某一字节的位置，结构如下：

据此建立的primary key的MyIsam索引结构大致如下：

可以看出MyIsam中索引叶子节点按主键col1顺序存储，还有指向行号对应数据的指针。

而辅助索引col2的索引结构如下：

可以看出辅助索引的叶子节点也和主键索引差不多都有索引值及指向对应的原表行号的指针，只不过辅助索引叶子节点的辅助索引值可能重复而已。

3.4.4.2 InnoDB

• 主键索引结构
  
  可以看出叶子节点按主键顺序存放了主键值及对应的其他列的值，即索引和数据存放在一起。除此之外还有指向下一个叶子节点的指针。还包括事务ID和回滚指针(rollback pointer)，被用于事务和MVCC。

• 二级索引
  相对于MyISAM，InnoDB的二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：
  

3.5 空间(R-Tree)索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

3.6 Innodb 行大小限制

可参考

• Row Size Limits
  
• 单行不能超过64KB
• 如果使用InnoDB，单行不能超过页的大小的一定比例，可参考innodb-limits.html
  默认页大小16KB，则单行限制小于8KB

4 索引的使用策略

4.1 使用索引时机

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 经常作为排序的列要建立索引；
• 查询中与其他表关联的字段，即外键关系建立索引
• 高并发条件下倾向组合索引；

4.2 不应使用索引场景

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引（基数cardinality低）；
• 表记录太少不要建立索引；

4.3 其他注意事项

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的；
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；
• LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。原因是B+树最左匹配原则；
• 在索引的列上使用表达式或者函数会使索引失效
  例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
• 在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在查询条件中使用<>会导致索引失效。
• 在查询条件中使用IS NULL会导致索引失效。
• 在查询条件中使用OR连接多个条件会导致索引失效，这时应该改为两次查询，然后用UNION ALL连接起来。
• 尽量不要包括多列排序，如果一定要那么最好为这些列构建组合索引；
• 只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

4.4 建索引原则

1. 最左前缀匹配原则，
   mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4，此时如果建立(a,b,c,d)顺序的索引，c可以用索引，而d是用不到索引的；此时如果建立(a,b,d,c)的索引则都可以用到，这样查询中a,b,d的顺序可以任意调整。

2. =和in查询索引时可以乱序
   比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3. 尽量选择区分度高的列作为索引
   区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大代表不同的记录比例越我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4. 索引列不能参与函数计算，保持列“干净”
   比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5. 尽量的扩展索引，不要新建索引
   比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。新建索引会很耗时。

6. 不建议使用过长的字段作为主键（innoDB）
   因为所有辅助索引的叶子节点data域都引用主索引，过长的主索引会令辅助索引变得过大。

7. 不要使用非单调的字段作为主键（innoDB）
   因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

5 索引优化

5.1 最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：

• col1，
• <col1,col2>，
• <col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>（这种应该是能使用col1的索引），col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推（这点有待证实）。

5.2 带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

5.3 前缀索引

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引(前缀索引)。当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

以下表示使用col1的前4个字符和col2的前3个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

5.4 InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主

6 MySQL中索引的语法

6.1 创建索引

• 在创建表的时候添加索引

CREATE TABLE tablename( id INT(10) PRIMARY KEY, content TEXT, name CHAR(10) NOT NULL, [UNIQUE] INDEX name_index (name(4)))ENGION MyISAM DEFAULT CHARSE utf8;

• 在创建表以后添加索引

ALTER my_table ADD [UNIQUE] INDEX my_index(my_text); CREATE INDEX my_index ON my_table(my_text);

6.2 根据索引查询

SELECT * FROM tablename WHERE MATCH(content) AGAINST('word1','word2',...);

6.3 删除索引

DROP my_index ON tablename；

6.4 查看表中的索引

SHOW INDEX FROM tablename

7 慢查询优化基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2. where条件单表查，锁定最小返回记录表。
   这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3. explain查看执行计划，是否与上一步预期一致（从锁定记录较少的表开始查询）
4. order by limit 形式的sql语句让排序的表优先查
5. 了解业务方使用场景
6. 加索引时参照建索引的几大原则
7. 观察结果，不符合预期继续从1分析

更多慢查询案例可参考MySQL索引原理及慢查询优化

8 索引的优缺点

8.1 优点

• 可以快速检索，减少I/O次数，加快检索速度；
• 根据索引分组和排序，可以加快分组和排序；

8.2 缺点

• 索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；
• 索引表的维护和创建需要时间成本（脑袋里要想到B+树的各种规则，创建、移动、合并、分裂），这个成本随着数据量增大而增大；
• 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

9 Explain执行计划与索引

可参考explain-output.html

9.1 概述

当我们对一条sql执行explain操作时，结果类似如下：

这里对主要字段含义进行解析。

9.2 type

可参考

• explain-type字段解释
• 官网explain-output.html

9.2.1 概述

explain结果中的type字段代表连接类型(the join type)，它描述了找到所需数据使用的扫描方式，有如下常见类型：

• system：系统表，少量数据，往往不需要进行磁盘IO；
• const：常量连接；
• eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
• ref：非主键非唯一索引等值扫描；
• range：范围扫描；
• index：索引树扫描；
• ALL：全表扫描(full table scan)；

上面各类扫描方式由快到慢：
system > const > eq_ref > ref > range > index > ALL

9.2.2 system

系统表，少量数据，往往不需要进行磁盘IO；

9.2.2.1 例1

上例中，从系统库mysql的系统表time_zone里查询数据，扫码类型为system，这些数据已经加载到内存里，不需要进行磁盘IO。

所以这类扫描是速度最快的。

9.2.2.2 例2

再举一个例子，内层嵌套(const)返回了一个临时表，外层嵌套从临时表查询，其扫描类型也是system，也不需要走磁盘IO，速度超快。

9.2.2.3 小结

速度最快

9.2.3 const

常量连接；

9.2.3.1 概述

数据准备：

create table user (
id int primary key,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');

const扫描的条件为：

• 命中主键(primary key)或者唯一(unique)索引；
• 被连接（where后跟的等值连接条件）的部分是一个常量(const)值；

如上例，id是PK，连接部分是常量1。

画外音：别搞什么类型转换的幺蛾子。

9.2.3.2 小结

这类扫描效率极高，返回数据量少，速度非常快。

9.2.4 eq_ref

主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；

9.2.4.1 概述

数据准备：

create table user (
id int primary key,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int primary key,
age int
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

eq_ref扫描的条件为：

• 对于前表的每一行(row)，后表只有一行被扫描。

再细化一点：

• join查询；
• 命中主键(primary key)或者非空唯一(unique not null)索引；
• 等值连接；

如上例，id是主键，该join查询为eq_ref扫描。

9.2.4.2 小结

这类扫描的速度也异常之快。

9.2.5 ref

非主键非唯一索引等值扫描；

9.2.5.1 概述

数据准备：

create table user (
id int,
name varchar(20) ,
index(id)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int,
age int,
index(id)
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

如果把前面eq_ref案例中的主键索引，改为普通非唯一(non unique)索引（id由pk变为普通index），就由eq_ref降级为了ref，此时对于前表的每一行(row)，后表可能有多于一行的数据被扫描，因为普通非唯一索引是允许重复的。

当id改为普通非唯一索引后，常量的连接查询，也由const降级为了ref，因为也可能有多于一行的数据被扫描。

9.2.5.2 小结

ref扫描，可能出现在join里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。

9.2.6 range

范围扫描

9.2.6.1 概述

数据准备：

create table user (
id int primary key,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

range扫描就比较好理解了，它是索引上的范围查询，它会在索引上扫描特定范围内的值。

像上例中的between，in，>都是典型的范围(range)查询。

画外音：必须是索引，否则不能批量"跳过"。

9.2.6.2 小结

索引上的范围查询，会在索引扫描特定范围内的值。

9.2.7 index

索引树扫描；

9.2.7.1 概述

index类型，需要扫描索引上的全部数据。

如上例，id是主键，该count查询需要通过扫描索引上的全部数据来计数。

9.2.7.2 小结

它仅比全表扫描快一点。

9.2.8 index_merge

9.2.8.1 概述

https://www.bilibili.com/video/BV1oG41177es/?spm_id_from=333.880.my_history.page.click&vd_source=8b887a61a1af8d964ecaea547d51b762

https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

index_merge类型，索引合并，当使用多个range scan且将结果合并为一时会优化。该优化只针对单表，对跨多表扫描时无效、对全文索引无效。merge的时候，可以产生并集、交集、并集+交集

以下是可能会使用索引合并的例子：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

如果负责where条件不生效时，可以考虑等量替换

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

当sql用到索引合并时，执行explain会在type=index_merge，key列表包含使用的索引，key_len包含这些索引的最长key部分的列表，而Extra列此时会根据代价CBO评估，可能为：

• Using intersect(…)
  Index Merge Intersection Access Algorithm
  本算法会对所有用到的索引同时scan，且对接收到的行取交集。如果所有列都被用到的索引列覆盖（如SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;），则不用查所有行，Extra= Using index 。如果使用的索引没有覆盖查询中使用的所有列，则只有在满足所有使用的key的范围条件时才检索完整行。如果其中一个合并条件是对 InnoDB 表的主键的查询条件，则它不用于行检索，而是用于过滤掉使用其他条件检索的行。
  当使用AND链接多个range条件在不同索引，且每个条件都是以下情况之一：

  • 索引如果是多列索引，则必须每部分都在sql中
    如

    key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
    # 例子
    SELECT * FROM tbl_name
    

  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
  ```

  • InnoDB 表的主键的任何range条件。

    SELECT * FROM innodb_table
      WHERE primary_key < 10 AND key_col1 = 20;
    

• Using union(…)
  Index Merge Union Access Algorithm
  该算法的准则类似于索引合并交集算法的准则。 该算法适用于表的WHERE子句在不同key上通过OR链接转换为多个range条件，且每个条件为以下之一：

  • An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered)索引如果是多列索引，则必须每部分都在sql中:

    key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
    

  • InnoDB 表的主键的任何range条件。
  • A condition for which the Index Merge intersection algorithm is applicable.

  例子：

  # 对两个索引同时使用查询，对结果中的已经有序的主键id去重取并集得到id，再回表查询
  SELECT * FROM t1
   WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
  
  SELECT * FROM innodb_table
   WHERE (key1 = 1 AND key2 = 2)
      OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
  

• Using sort_union(…)
  Index Merge Sort-Union Access Algorithm
  该访问算法适用于WHERE子句转换为OR组合的多个range条件，但Index Merge union算法不适用时。sort-union 算法和 union 算法之间的区别在于，sort-union 算法必须首先获取所有行的行 ID 并在返回任何行之前对它们进行排序（因为都是range查询，拿到的数据的id不像等值查询时有序，而是无序的，需要先排序再去重）。例子如下：

  # 对两个索引同时使用查询，对结果中的无序的主键id去重取并集得到id，再回表查询
  SELECT * FROM tbl_name
    WHERE key_col1 < 10 OR key_col2 < 20;
  
  SELECT * FROM tbl_name
    WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
  

  

默认状况下以上算法都开启，如果只想有一个就设置index_merge off，打开指定的算法。参考Section 8.9.2, “Switchable Optimizations”

9.2.8.2 例子

9.2.8 All

全表扫描(full table scan)；

9.2.8.1 概述

数据准备：

create table user (
id int,
name varchar(20)
)engine=innodb;
 
insert into user values(1,'shenjian');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
 
create table user_ex (
id int,
age int
)engine=innodb;
 
insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);
insert into user_ex values(5,50);

explain select * from user,user_ex where user.id=user_ex.id;

如果id上不建索引，对于前表的每一行(row)，后表都要被全表扫描。

9.2.8.2 小结

最慢

9.2.9 type与性能

今天这篇文章中，这个相同的join语句explain select * from user,user_ex where user.id=user_ex.id; 出现了三次：

• 扫描类型为eq_ref，此时id为主键；
• 扫描类型为ref，此时id为非唯一普通索引；
• 扫描类型为ALL，全表扫描，此时id上无索引；

有此可见，建立正确的索引，对数据库性能的提升是多么重要。全表扫描代价极大，性能很低，是应当极力避免的，通过explain分析SQL语句，非常有必要。

9.2.10 type总结

• explain结果中的type字段，表示（广义）连接类型，它描述了找到所需数据使用的扫描方式；
• 常见的扫描类型由快到慢有：
  system>const>eq_ref>ref>range>index>ALL
  • 各类扫描类型的要点是：
  • system最快：不进行磁盘IO
  • const：PK或者unique上的等值查询
  • eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
  • ref：非唯一索引，等值匹配，可能有多行命中
  • range：索引上的范围扫描，例如：between/in/>
  • index：索引上的全集扫描，例如：InnoDB的count
  • ALL最慢：全表扫描(full table scan)
• 建立正确的索引(index)，非常重要；
• 使用explain了解并优化执行计划，非常重要；

9.3 Extra

可参考explain-output.html

9.3.1 数据准备

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
 
insert into user values(1, 'shenjian','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');

数据说明：

• 用户表：id主键索引，name普通索引（非唯一），sex无索引；
• 四行记录：其中name普通索引存在重复记录lisi；

实验目的：

• 通过构造各类SQL语句，对explain的Extra字段进行说明，启发式定位待优化低性能SQL语句。

9.3.2 Using where

9.3.2.1 实验语句

explain select * from user where sex='no';

9.3.2.2 结果说明

Extra为Using where说明，SQL使用了where条件过滤数据。

9.3.2.3 注意点

• 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
• 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；

本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。

9.3.2.4 常见的优化方法

常见的优化方法为，在where过滤属性上添加索引。

画外音：本例中，sex字段区分度不高，添加索引对性能提升有限。

9.3.3 Using index

9.3.3.1 实验语句

explain select id,name from user where name='shenjian';

9.3.3.2 结果说明

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

画外音：只需从索引树的信息即可查找到所需列信息，无需回表

9.3.3.3 注意点

这类SQL语句往往性能较好。

需要注意的是，如果是使用聚集索引列作为条件查询，所需字段也是该表字段，则extra不会显示using index，但其实就是覆盖索引了。

用到二级索引的覆盖索引才会显示 using index。

Mysql官网原话：

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY. 

9.3.3.4 常见的优化方法

问题来了，什么样的列数据，会包含在索引树上呢？

覆盖索引

9.3.4 Using index condition

9.3.4.1 实验语句

explain select id,name,sex from user 
where name='shenjian';

画外音：该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。

9.3.4.2 结果说明

Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要回表访问实际的行记录，如本例中的sex列。

9.3.4.3 注意点

这类SQL语句性能也较高，但不如Using index。

9.3.4.4 常见的优化方法

问题来了，如何优化为Using index呢？

name和sex建立联合索引即可。

9.3.5 Using filesort

可参考MySQL中出现using filesort现象的一般情况与分析

9.3.5.1 实验语句

explain select * from user order by sex;

9.3.5.2 结果说明

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。

9.3.5.3 注意点

这类SQL语句性能极差，需要进行优化。

9.3.5.4 常见的优化方法

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort。

常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

9.3.6 Using temporary

9.3.6.1 实验语句

explain select * from user group by name order by sex;

9.3.6.2 结果说明

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。

9.3.6.3 注意点

这类SQL语句性能较低，往往也需要进行优化。

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

9.3.7 Using join buffer (Block Nested Loop)

9.3.7.1 实验语句

explain select * from user where id in(select id from user where sex='no');

9.3.7.2 结果说明

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。

画外音：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

9.3.7.3 注意点

这类SQL语句性能往往也较低，需要进行优化。

9.3.7.4 常见的优化方法

典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。

常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

10 索引下推

11 案例

一个案例彻底弄懂如何正确使用 mysql inndb 联合索引

参考文档

• MySQL索引原理及慢查询优化