【真正的干货一篇就够了！】MySQL 索引 详解+使用+优化+面试

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据，提高数据库的性能。。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的分类

从物理存储角度上，索引可以分为聚集索引和非聚集索引。

聚集索引（Clustered Index）
聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。
非聚集索引（Non-clustered Index）
非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。

从逻辑角度分为：主键索引、唯一索引、普通索引、联合索引、全文索引

• 普通索引：最基本的索引，它没有任何限制。
• 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。
• 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
• 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

索引的使用

普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);

删除索引的语法

DROP INDEX [indexName] ON mytable; 

注意
当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁；当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
  以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

mysql> SHOW INDEX FROM table_name; \G
........

索引的优缺点

优点

• 在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系， 增加数据的惟一性特点。
• 能提高数据的搜索及检索速度，符合数据库建立的初衷。
• 能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
• 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
• 建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

缺点

• 在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
• 在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
• 在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

总结

• 索引不是越多越好，索引存在一个和这个表相关的文件里，占用硬盘空间，宁缺勿滥，每个表都有主键（id），操作能使用主键尽量使用主键。
• 同 JVM 自动优化 java 代码一样，MySQL 也具有自动优化 SQL 的功能。低效的索引将被忽略，这也就倒逼开发者使用正确且高效的索引。

使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
一般来说,

• 在经常需要搜索的列上,可以加快索引的速度
• 主键列上可以确保列的唯一性
• 在表与表的而连接条件上加上索引,可以加快连接查询的速度
• 在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
• 在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
• like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引
• 索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串
• 使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
• 不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
• 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

什么情况下不创建索引

• 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
• 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
• 定义为text和image和bit数据类型的列不应该增加索引,
• 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

MySQL索引优化规则

可以通过以下规则对 MySQL 索引进行优化。

1.前导模糊查询不能使用索引。

例如下面 SQL 语句不能使用索引。

select * fromdoc where title like '%XX'

而非前导模糊查询则可以使用索引，如下面的 SQL 语句。

select * fromdoc where title like 'XX%'

页面搜索严禁左模糊或者全模糊，如果需要可以用搜索引擎来解决。

2.union、in、or 都能够命中索引，建议使用 in。

• union：能够命中索引。

示例代码如下：

select * fromdoc where status=1
unionall
select * fromdoc where status=2

直接告诉 MySQL 怎么做，MySQL 耗费的 CPU 最少，但是一般不这么写 SQL。

in：能够命中索引。

示例代码如下：

select * fromdoc where status in (1, 2)

查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in

or：新版的 MySQL 能够命中索引。

示例代码如下：

select * fromdoc where status = 1 or status = 2

查询优化耗费的 CPU 比 in 多，不建议频繁用 or。

3.负向条件查询不能使用索引，可以优化为 in 查询。

负向条件有：!=、<>、not in、not exists、not like 等。
例如下面代码：

select * fromdoc where status != 1 and status != 2

可以优化为 in 查询：

select * fromdoc where status in (0,3,4)

4.联合索引最左前缀原则（又叫最左侧查询）

• 如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

例如登录业务需求，代码如下。

selectuid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。

因为业务上几乎没有 passwd 的单条件查询需求，而有很多 login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

• 建联合索引的时候，区分度最高的字段在最左边。
• 如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。
• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
• 最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

selectuid, login_time from user where passwd=? andlogin_name=?

但还是建议 where 后的顺序和联合索引一致，养成好习惯。

5.范围列可以用到索引（联合索引必须是最左前缀）。

• 范围条件有：<、<=、>、>=、between等。
• 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引。

select * fromemployees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6.把计算放到业务层而不是数据库层。

• 在字段上进行计算不能命中索引。

例如下面的 SQL 语句。

select * fromdoc where YEAR(create_time) <= '2016'

即使 date 上建立了索引，也会全表扫描，可优化为值计算，如下：

select * fromdoc where create_time <= '2016-01-01'

• 把计算放到业务层。

这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。

比如下面的 SQL 语句：

select * fromorder where date < = CURDATE()

可以优化为：

select * fromorder where date < = '2018-01-2412:00:00'

优化后的 SQL 释放了数据库的 CPU 多次调用，传入的 SQL 相同，才可以利用查询缓存。

7.强制类型转换会全表扫描

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

 select * fromuser where phone=13800001234

可以优化为：

select * fromuser where phone='13800001234'

8.更新十分频繁、数据区分度不高的字段上不宜建立索引。

• 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

• “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9.利用覆盖索引来进行查询操作，避免回表。

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

例如登录业务需求，代码如下。

selectuid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

10.如果有 order by、group by 的场景，请注意利用索引的有序性。

• order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
• 例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
• 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

11.使用短索引（又叫前缀索引）来优化索引。

前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的区分度接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销，可以使用 count(distinct left(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引（Covering Index，即当索引本身包含查询所需全部数据时，不再访问数据文件本身），很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

例如对于下面的 SQL 语句：

SELEC *FROM employees.employees WHERE first_name='Eric'AND last_name='Anido';

我们可以建立索引：(firstname, lastname(4))。

12.建立索引的列，不允许为 null。

单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null，可能会得到“不符合预期”的结果集，所以，请使用 not null 约束以及默认值。

13.利用延迟关联或者子查询优化超多分页场景。

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

示例如下，先快速定位需要获取的 id 段，然后再关联:

selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id

14.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

15.超过三个表最好不要 join。

需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

16.如果明确知道只有一条结果返回，limit 1 能够提高效率。

比如如下 SQL 语句：

select * fromuser where login_name=?

可以优化为：

select * fromuser where login_name=? limit 1

自己明确知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

17.SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

• consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• ref：使用普通的索引（Normal Index）。
• range：对索引进行范围检索。
• 当 type=index 时，索引物理文件全扫，速度非常慢。

18.单表索引建议控制在5个以内。

19.单索引字段数不允许超过5个。

字段超过5个时，实际已经起不到有效过滤数据的作用了。

20.创建索引时避免以下错误观念

• 索引越多越好，认为一个查询就需要建一个索引。
• 宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。
• 抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
• 过早优化，在不了解系统的情况下就开始优化。

问题详解

这部分，会列出平时会遇到的一些问题，并给予解答。

1. 请问如下三条 SQL 该如何建立索引？

where a=1and b=1
where b=1
where b=1order by time desc

MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引吗？

回答：

第一问：建议建立两个索引，即 idxab(a,b) 和 idxbtime(b,time)。

第二问：MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，对于上面的第一条 SQL，如果建立索引为 idxba(b,a) 也是可以用到索引的，不过建议 where 后的字段顺序和联合索引保持一致，养成好习惯。

2.假如有联合索引(empno、title、fromdate)，下面的 SQL 是否可以用到索引，如果可以的话，会使用几个列？

select * fromemployees.titles where emp_no between '10001' and'10010' and title='Senior Engineer' and from_date between '1986-01-01'and '1986-12-31'

回答：可以使用索引，可以用到索引全部三个列，这个 SQL 看起来是用了两个范围查询，但作用于 empno 上的“between”实际上相当于“in”，也就是说 empno 实际是多值精确匹配，在 MySQL 中要谨慎地区分多值匹配和范围匹配，否则会对 MySQL 的行为产生困惑。

3.既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？

回答：不是，因为索引虽然加快了查询速度，但索引也是有代价的。索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担。另外，MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，另一种是数据的区分度比较低，可以使用 count(distinct(列名))/count(*) 来计算区分度。

4.主键和聚集索引的关系？

回答：在 MySQL 中，InnoDB 引擎表是（聚集）索引组织表（Clustered IndexOrganize Table)，它会先按照主键进行聚集，如果没有定义主键，InnoDB 会试着使用唯一的非空索引来代替，如果没有这种索引，InnoDB 就会定义隐藏的主键然后在上面进行聚集。由此可见，在 InnoDB 表中，主键必然是聚集索引，而聚集索引则未必是主键。MyISAM 引擎表是堆组织表（Heap Organize Table)，它没有聚集索引的概念。

5.一个6亿的表 a，一个3亿的表 b，通过外键 tid 关联，如何最快的查询出满足条件的第50000到第50200中的这200条数据记录？

回答：方法一：如果 a 表 tid 是自增长，并且是连续的，b表的id为索引。SQL语句如下。

select * froma,b where a.tid = b.id and a.tid>500000 limit200;

方法二：如果 a 表的 tid 不是连续的，那么就需要使用覆盖索引，tid 要么是主键，要么是辅助索引，b 表 id 也需要有索引。SQL语句如下。

select * fromb, (select tid from a limit 50000,200) awhere b.id = a.tid;

6.假如建立联合索引(a,b,c)，下列语句是否可以使用索引，如果可以，使用了那几列？（考察联合索引最左前缀原则）

where a= 3

答：是，使用了 a 列。

where a= 3 and b = 5

答：是，使用了 a，b 列。

where a = 3 and c = 4 and b = 5

答：是，使用了 a，b，c 列。

where b= 3

答：否。

where a= 3 and c = 4

答：是，使用了 a 列。

where a = 3 and b > 10 andc = 7

答：是，使用了 a，b 列。

where a = 3 and b like 'xx%' andc = 7

答：是，使用了 a，b 列。

7.文章表的表结构如下：

CREATE TABLEIF NOT EXISTS `article` (`id`int(10) unsigned NOT NULLAUTO_INCREMENT,
`author_id`int(10) unsignedNOT NULL,
`category_id`int(10) unsigned NOT NULL,
`views`int(10) unsignedNOT NULL,
`zomments`int(10) unsignedNOT NULL,
`title`varbinary(255) NOT NULL,
`content`text NOTNULL,
PRIMARY KEY (`id`)
);

下面语句应该如何建立索引？

selectauthor_id, title, content from `article`
wherecategory_id = 1 and comments > 1
order byviews desc limit 1;

回答：

没有联合索引时，explain显示，如下图所示：

创建 idxcategoryidcommentsviews(category_id,comments, views) 联合索引时，explain显示，如下图所示：

创建 idxcategoryidviews(categoryid,views) 联合索引，explain 显示，如下图所示：
由此可见，可以创建 idxcategoryidviews(categoryid,views) 联合索引。

8、为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何MySQL独独喜欢B+树？

哈希表有什么特点？
假如有这么一张表(表名：sanguo)：

现在对name字段建立哈希索引：

注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。那么对于这样一个索引结构，现在来执行下面的sql语句：

select*fromsanguowherename='周瑜'

可以直接对‘周瑜’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到锁对应那一行数据的地址，进而查询那一行数据。 那么如果现在执行下面的sql语句：

select*fromsanguowherename>'周瑜'

则无能为力，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

如果用完全平衡二叉树呢？
还是上面的表数据用完全平衡二叉树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

图中的每一个节点实际上应该有四部分：

• 左指针，指向左子树
• 键值
• 键值所对应的数据的存储地址
• 右指针，指向右子树

另外需要提醒的是，二叉树是有顺序的，简单的说就是“左边的小于右边的”假如我们现在来查找‘周瑜’，需要找2次（第一次曹操，第二次周瑜），比哈希表要多一次。而且由于完全平衡二叉树是有序的，所以也是支持范围查找的。

如果用B树呢？
还是上面的表数据用B树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

如果用B+树呢？
还是上面的表数据用B+树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

那么B+树到底有什么优势呢？
这里我们用“反证法”，假如我们现在就用完全平衡二叉树作为索引的数据结构，我们来看一下有什么不妥的地方。实际上，索引也是很“大”的，因为索引也是存储元素的，我们的一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。回头看一下完全平衡二叉树，当我们需要查询“张飞”时，需要以下步骤

• 从磁盘中取出“曹操”到内存，CPU从内存取出数据进行笔记，“张飞”<“曹操”，取左子树（产生了一次磁盘IO）
• 从磁盘中取出“周瑜”到内存，CPU从内存取出数据进行笔记，“张飞”>“周瑜”，取右子树（产生了一次磁盘IO）
• 从磁盘中取出“孙权”到内存，CPU从内存取出数据进行笔记，“张飞”>“孙权”，取右子树（产生了一次磁盘IO）
• 从磁盘中取出“黄忠”到内存，CPU从内存取出数据进行笔记，“张飞”=“张飞”，找到结果（产生了一次磁盘IO）

同理，回头看一下B树，我们发现只发送三次磁盘IO就可以找到“张飞”了，这就是B树的优点：一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。

而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

==到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。 ==

那么，一个B+树的节点中到底存多少个元素合适呢？
其实也可以换个角度来思考B+树中一个节点到底多大合适？

答案是：==B+树中一个节点为一页或页的倍数最为合适。==因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费；所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

那么，Mysql中B+树的一个节点大小为多大呢？
这个问题的答案是“1页”，这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBAL STATUS LIKE 'Innodb_page_size';查看。

并且还可以告诉你的是，一个节点为1页就够了。

为什么一个节点为1页（16k）就够了？

解决这个问题，我们先来看一下Mysql中利用B+树的具体实现。

Mysql中MyISAM和innodb使用B+树

通常我们认为B+树的非叶子节点不存储数据，只有叶子节点才存储数据；而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找。上图的data区域两个存储引擎会有不同。

MyISAM中的B+树

MYISAM中叶子节点的数据区域存储的是数据记录的地址

主键索引

辅助索引

MyISAM存储引擎在使用索引查询数据时，会先根据索引查找到数据地址，再根据地址查询到具体的数据。并且主键索引和辅助索引没有太多区别。

InnoDB中的B+树

InnoDB中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值

主键索引


辅助索引

Innodb中的主键索引和实际数据时绑定在一起的，也就是说Innodb的一个表一定要有主键索引，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见）。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：

• 手动建立主键索引
• 尽量利用主键索引查询

回到我们的问题：为什么一个节点为1页（16k）就够了？

对着上面Mysql中Innodb中对B+树的实际应用（主要看主键索引），可以发现B+树中的一个节点存储的内容是：

• 非叶子节点：主键+指针
• 叶子节点：数据

那么，假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：117016=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。所以也就回答了我们的问题，1页=16k这么设置是比较合适的，是适用大多数的企业的，当然这个值是可以修改的，所以也能根据业务的时间情况进行调整。

9、 最左前缀原则

我们模拟数据建立一个联合索引 select * , concat ( righ t(emp_no,1), "-" ,right (title,1),"-",right(from_date,2)) from employees.titles limit 10;

那么对应的B+树为

我们判断一个查询条件能不能用到索引，我们要分析这个查询条件能不能利用某个索引缩小查询范围

对于select * from employees.titles wheree mp_no=1是能用到索引的，因为它能利用上面的索引所有查询范围，首先和第一个节点“4-r-01”比较，1<4，所以可以直接确定结果在左子树，同理，依次按顺序进行比较，逐步可以缩小查询范围。对于 select * from employees.titles where title='1'是不能用到索引的，因为它不能用到上面的所以，和第一节点进行比较时，没有empno这个字段的值，不能确定到底该去左子树还是右子树继续进行查询。对于select * from employees.titles where title='1' and emp_no=1是能用到索引，按照我们的上面的分析，先用title='1’这个条件和第一个节点进行比较，是没有结果的，但是mysql会对这个sql进行优化，优化之后会将empno=1这个条件放到第一位，从而可以利用索引。

结语

对于大数据量的业务，应该时刻考虑到性能，本文只是从使用层面提供了一些优化的思路，由于业务和数据的复杂性，需要具体问题具体分析，对于数据量比较大的业务，最好自己使用 explain 具体分析一下，同时，知其然知其所以然，有时间大家可以看看索引的底层实现原理。欢迎各路大神拍砖，共同学习。

感谢【真·干货】MySQL 索引及优化实战
感谢干货！MySQL常见的面试题＋索引原理分析！

参考资料

• 《阿里巴巴 Java 开发手册》

• 58 沈剑老师的 SQL 优化相关文章

• MySQL 索引背后的数据结构及算法原理

• 互联网各路资料及实践