MySQL联合索引性能比较

最新推荐文章于 2024-06-16 00:29:40 发布

浮生笑尽惹寂寥

最新推荐文章于 2024-06-16 00:29:40 发布

阅读量264

点赞数

分类专栏： mysql 文章标签： mysql 索引

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在分析联合索引性能之前，温故下基础知识。

1 数据结构

1.1 B-树

一个m阶树满足以下条件：

每个节点至多拥有m颗子树；
根节点至少2颗子树（若存在子树的情况下）；
非根节点至少拥有m/2颗子树，其范围为m/2 <= childNum(x) <= m；
所有叶子节点都在同一层，且为null；
有k颗子树的节点，其关键字数为k-1，ceil(m/2)-1 <= keyNum(x) <= m-1;

这里写图片描述

图1

下面分两种边界条件来算算其高度：

(1).度为m/2，关键字总量为n，高度为h。

m/2+(m/2)^2+...+(m/2)^(h-1) = n

\frac{(m/2)(1-(m/2)^(h-1)}{1-m/2} = n

(m/2)^(h-1) = n+1

h = (log_(m/2) (n+1))+1

(2).度为m，关键字总量为n，高度为h。

m+(m)^2+...+(m)^(h-1) = n

\frac{(m)(1-(m)^(h-1)}{1-m} = n

(m)^(h-1) = n+1

h = (log_(m) (n+1))+1

以上算法不一定绝对准确，仅供参考。

1.2 B+树

一个m阶树满足以下条件：

每个节点至多拥有m颗子树；
根节点至少2颗子树（若存在子树的情况下）；
有n颗子树的节点有n个关键字；
所有内节点仅存放索引，数据全部保存在叶子节点上。

这里写图片描述

图2

B+树高度计算与B-树一样，B+树较B-树的优势在于：

内节点没有存放数据，因此可以存放更多的索引数据，一次IO将会获取更多数据，数据总量不变情况下，达到减少IO次数效果；

1.3 B-树、B+树索引性能分析

一般用磁盘IO评价索引结构的优劣。B-树检索一次，最多访问h个节点，即其时间复杂度O(h)=O(log_d N),其实红黑色O(h)=O(log_2 N),接下来以实际数据做对比：数据量640亿。

度为100的B-树，高度为5；
红黑树高度为35；

可以看出，B-树的IO效率较红黑树提升很多。提升B树的度，可以有效降低树的高度。

附加-在线对数计算器

2 MySQL索引实现

2.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，索引文件与数据分离，是一种非聚集索引。下图是MyISAM索引的原理图：

这里写图片描述

图3

这里设表一共有三列，假设我们以Col1为主键，则图3是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

这里写图片描述

图4

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

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

2.2 InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

这里写图片描述

图5

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

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

这里写图片描述

图6

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

3 索引使用策略及优化

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

3.1 联合索引

联合索引顾名思义就是多个列组成的索引，比如

SHOW INDEX FROM pre_sales_rfq

这里写图片描述

通过输出结果可以看出，pre_sales_rfq表有2个索引：主键索引(id)、联合索引(project_id,item_id)。接下来，主体看看什么情况会用到索引，什么时候不会用到索引。

不过在正式分析联合索引前，有必要了解下主键和联合索引都存在时，使用哪个索引。通过下面实例分析：

其中id为主键，(id, name,create_date)联合索引

EXPLAIN select * from index_test where id = 1 and name = 'jackshawn' and create_date = '2017-09-21'

这里写图片描述

通过输出结果可以看出，其key_len为4，ref为const，仅使用了主键；那我们把主键去掉再看看：

这里写图片描述

这次有使用到联合索引。

通过设置其他字段为主键，测试结果依旧如上。也就是说，如果联合索引中包含主键，则优先使用主键。

3.1.1 全列匹配

EXPLAIN select * from pre_sales_rfq where project_id = 1 and item_id = 1

这里写图片描述

通过输出结果可以看出，本次查询用到了联合索引。然后看看颠倒索引列顺序有无影响。

EXPLAIN select * from pre_sales_project_rfq where item_id = 1 and project_id = 1

这里写图片描述

通过输出结果可以看出，MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。很明显，当索引中所有列精准匹配时，是会用索引的。

3.1.2 最左前缀匹配

最左前缀匹配这种情况即，查询条件中匹配最左边开始的连续一个或几个条件。

EXPLAIN select * from pre_sales_project_rfq where project_id = 1

这里写图片描述

通过输出结果可以看出，联合索引依然起作用。

3.1.3 查询条件没有中间列

(id, name,create_date)联合索引

EXPLAIN select * from index_test where id = 1  and create_date = '2017-09-21'

这里写图片描述

通过输出结果可以看出，依然会使用索引，只不过仅使用索引的id列。

3.1.4 查询条件没有指定索引第一列

EXPLAIN select * from pre_sales_project_rfq where item_id = 1

这里写图片描述

通过输出结果可以看出，没有使用任何索引。

3.1.5 匹配某列的前缀字符串

EXPLAIN select * from index_test where id = 1 and name ='%jack'

EXPLAIN select * from index_test where id = 1 and name ='jack%'

输出结果都为：

这里写图片描述

通过输出结果可以看出，模糊匹配时依旧能使用索引。

3.1.6 范围查询

EXPLAIN select * from index_test where id > 1 and name ='jackshawn'

这里写图片描述

通过输出结果可以看出，索引没有被用到，除非这列为主键，between、in也是一个样，索引不会被用到。

EXPLAIN select * from index_test where id in(1,4) and name ='jackshawn'

EXPLAIN select * from index_test where id between 1 and 4 and name ='jackshawn'

3.1.7 查询条件中含有函数或表达式

EXPLAIN select * from index_test where id -1 = 1 and name ='jackshawn'

这里写图片描述

通过输出结果可以看出，索引没有被用到。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

3.2 索引选择性与前缀索引

首先不是任何时候都必须建索引，一般数据量较少（千级别）的数据表没必要建索引，全表查询即可，因为索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

在数据量较大时，选择索引字段有一个原则：

1.字段重复率较低。

## 一个字段不重复的总量与数据总量的比值，越大选择性越好。

SELECT count(DISTINCT(key))/count(*) AS Selectivity FROM table;

对于符合创建索引的情况，是不是只有一种选举，就是用整个字段来做索引？答案是否定的，也可以截取字段的前缀部分来创建索引，关键看截取多长时查询效率高。

1.选择性高。
2.查询效率高。

## 举例如下截取last_name前4个字符

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

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

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

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

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

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