mysql 索引 时间复杂度_MySQL调优之索引优化

TABLE OF CONTENTS

1 索引是什么

2 索引的底层结构

3 什么时候用索引

4 如何用好索引

索引是什么

正经的定义：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

简单来说，索引是能够帮助我们快速查找需要的数据行的东西。就像我们平时查字典，先翻目录，通过拼音查到字对应的页码，就可以快速地找到我们需要的东西，而不用翻阅整本书。

字典目录

索引的底层结构

如果要我们自己实现一个索引，你会怎么做？

哈希表

一个最直接的想法就是使用一个哈希表去存储(C++中可以使用unordered_map，Java中可以使用HashMap)，通过key去查找对应的value，这样的效率最高，在哈希函数选择比较合适的情况下，时间复杂度为O(1)。

但是哈希索引有很多问题：

只支持等值查找，不支持范围查找。(比如，相亲网站需要查找年龄18~25的人群)

只支持全键值查找，不支持最左前缀查找。(比如，只能查找张三，无法查找姓名是张开头的)

索引无法用于排序。

...

只有memory存储引擎显式支持了哈希索引，但是InnoDB有自适应哈希索引去处理热点数据。

树

那用二叉查找树(Binary Search Tree)如何？二叉树数据有序，查找/插入/删除的效率高。

查找的平均时间复杂度为O(logn(底数为2))，最差为O(n)。

二叉查找树(BST)

二叉查找树的一个缺点就是没有处理平衡，在极端情况下，会退化成类似于链表的结构。就像这样：

image.png

那用自平衡二叉查找树(Adelson-Velsky and Landis Tree)如何？左子树的高度与右子树的高度的差至多是 1，避免了二叉树退化为链表的问题。查找的时间复杂度为O(logn(底数为2))，最差为O(logn(底数为2))

AVL树

AVL树解决了BST的平衡问题，但是AVL每次插入/删除时，都需要维护树的平衡问题(旋转)，开销比较大。

那用红黑树(Red-black Tree)如何？

红黑树

红黑树相对于AVL树来说，牺牲了部分平衡性以换取插入/删除操作时少量的旋转操作，整体来说性能要优于AVL树。(从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。)Java里的TreeMap就是用红黑树去实现的。

那为啥MySQL选择了B-Tree，而不是红黑树？因为MySQL的索引是需要落盘的，磁盘I/O远比内存慢，而B-Tree需要的磁盘I/O比红黑树少得多。

B-Tree

RBT

通过上图，我们可以看到，要存储20个key，B-Tree的树高只有2，而红黑树(RBT)的树高是6。访问结点20的时候，使用B-Tree只需要2次磁盘操作，而红黑树需要6次磁盘操作。在实际应用中，B-Tree的出度一般会很大，通常是100以上，所以往往树高为3时，就可以存储百万级数据(100^3)，而红黑树需要40的树高(2lg(n+1))，显然B-Tree需要的I/O次数少得多。

MySQL的InnoDB存储引擎使用的是B+Tree，这是B-Tree的变种。

B-Tree

B+Tree

与B-Tree不同的是，B+Tree的中间结点(非叶子结点)不存储data，而只存储key，所以能存储更多的key，即B+Tree的出度比B-Tree更大。data都存在叶子结点上，叶子结点之间使用指针连接，加速区间访问的性能。(不用做中序遍历)

什么时候用索引？

对于非常小的表，比如1000行以下的，通常来说，简单的全表扫描更高效，使用索引反而会带来额外的维护成本。对于中到大型的表，索引就非常有效了。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

如何用好索引(主要针对InnoDB引擎)

1 独立的列(不要在索引上做运算)

索引列不能是表达式的一部分，或者用作函数的参数，否则，MySQL无法使用索引快速地找到需要的数据行，因为对索引字段做了一些操作，而操作的结果，显然无法用上B-Tree里面存储的原始字段值去查找。

比如下面的两个例子(actor表有两个索引：1. actor_id的主键索引 2. last_name的普通索引)：

例一：

SELECT * FROM sakila.actor WHERE actor_id + 1 = 5;

通过查看执行计划(EXPLAIN SELECT * FROM sakila.actor WHERE actor_id + 1 = 5;)可以发现，MySQL走了全表扫描(type为All)，优化器估计需要扫描200行(rows为200)，而没有使用索引。

EXPLAIN结果

改写为：

SELECT * FROM sakila.actor WHERE actor_id = 4;

再看执行计划：

改写后

用了主键索引(Key为PRIMARY )，只扫描了一行(rows为1)。

例二(官方的payment表在payment_date上没有索引，需要自行加上)：

SELECT * FROM sakila.payment WHERE YEAR(payment_date) = 2006;

通过查看执行计划，可以发现，走了全表扫描，需要扫描16086行，没有使用索引。

索引字段作为函数参数

改写为：

SELECT * FROM payment WHERE payment_date BETWEEN DATE('2006-01-01') AND DATE('2006-12-31');

再看一眼执行计划：

改写后

用了idx_payment_date这个索引，只需要扫描182行。

2 最左前缀原则

like '%xxx' 不会使用索引。就像之前提到的字典目录的这个例子，MySQL会使用B-Tree去存储key，也就是这里的拼音，我们可以通过索引字段的左前缀去查找，比如查找zhu开头的，都有哪些(zhu, zhua, zhuai, zhuan, zhuang)。但是如果想要查找'ang'结尾的，MySQL就不会使用索引了。

字典目录

MySQL支持联合索引(一个索引包含多个字段)，但是如果查询的时候没有满足最左前缀原则，也无法用上索引。

有以下的user表，我们在name, age上创建联合索引：

CREATE TABLE `tuser` (

`id` int(11) NOT NULL,

`id_card` varchar(32) DEFAULT NULL,

`name` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`ismale` tinyint(1) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `id_card` (`id_card`),

KEY `name_age` (`name`,`age`)

) ENGINE=InnoDB

索引结构示意图

和like的规则是类似的：

SELECT * FROM tuser WHERE name='张三'; -- 可以使用索引

SELECT * FROM tuser WHERE name='张三' AND age=10; -- 可以使用索引

SELECT * FROM tuser WHERE age='10'; -- 无法使用索引

3 覆盖索引(不要动不动就SELECT *)

InndoDB存储索引的key时，是按照原样保存的，这意味着如果索引已经包含了需要查询的信息时，就不用从行数据(data)里面拿了。特别是针对二级索引，就可以不用回表了。

比如上面的例子，如果想查'张三'的年龄可以这么写：

SELECT age FROM tuser WHERE name='张三'; -- 使用索引覆盖

SELECT * FROM tuser WHERE name='张三'; -- 索引无法覆盖，需要查询原始的行数据

4 索引失效的常见例子

表关联的时候，JOIN字段的字符集不一样。

or条件会导致索引失效。如果查询条件中使用了or，只要其中一个条件没有索引，其他条件(字段)有索引也不会使用。解决方案：1.给or的所有字段添加索引(不是联合索引)2. 使用union或者union all

隐式转换，当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能导致查询无法使用索引。

参考