MySQL数据库——索引及SQL优化(1)

MySql作为天天与我们打交道的朋友，详细探索他的底层原理，对我们日常的开发工作很有帮助。本文从Mysql数据结构开始，逐步分析他的原理和工作中优化使用。

 

目录

一、MySQL数据库索引的数据结构及算法

索引

B-Tree

B+Tree

为什么使用B-Tree（B+Tree）

二、数据库索引

MyISAM索引实现

InnoDB索引

三、索引策略及SQL优化

结构优化

查询优化

结语

一、MySQL数据库索引的数据结构及算法

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

数据库系统的设计者会从查询算法的角度进行优化。复杂度为O(n)的顺序查找（linear search），在数据量很大时显然是糟糕的，二分查找（binary search）要求被检索数据有序、二叉树查找（binary tree search）只能应用于二叉查找树上。每种查找算法都只能应用于特定的数据结构之上，但是数据不可能完全满足各种数据结构，所以数据库系统维护着满足特定查找算法的数据结构，这些以某种方式引用（指向）数据的数据结构，就是索引。

列举一个基础的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log_2n)的复杂度内获取到相应数据。

B-Tree

B-Tree是满足下列条件的数据结构：

• d为大于1的一个正整数，称为B-Tree的度。
• h为一个正整数，称为B-Tree的高度。
• 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
• 所有叶节点具有相同的深度，等于树高h。
• key和指针互相间隔，节点两端是指针。
• 一个节点中的key从左到右非递减排列。
• 所有节点组成树结构。
• 每个指针要么为null，要么指向另外一个节点。
• 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于\(v(key_1)\)，其中\(v(key_1)\)为node的第一个key的值。
• 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于\(v(key_m)\)，其中\(v(key_m)\)为node的最后一个key的值。
• 如果某个指针在节点node的左右相邻key分别是\(key_i\)和\(key_{i+1}\)且不为null，则其指向节点的所有key小于\(v(key_{i+1})\)且大于\(v(key_i)\)。

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
    }
    return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

B+Tree

MySQL就普遍使用B+Tree实现其索引结构。

• 与B-Tree相比，B+Tree有以下不同点：
• 每个节点的指针上限为2d而不是2d+1。
• 内节点不存储data，只存储key；叶子节点不存储指针。

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

为什么使用B-Tree（B+Tree）

索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。B-Tree是一个非常有效率的索引数据结构。例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为(log_d((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(log_dN)。

根据B-Tree的定义，可知检索一次最多需要访问h个节点。设计者利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为(O(h)=O(log_dN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

d_{max}=floor(pagesize / (keysize + datasize + pointsize))

由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

二、数据库索引

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

MyISAM索引实现

MyISAM的索引文件仅仅保存数据记录的地址。MyISAM的索引方式也叫做“非聚集”的，在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。

如果我们在Col2(age)上建立一个辅助索引，则此索引的结构如上图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。叶节点包含了完整的数据记录。这种索引叫做聚集索引。

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

与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。为什么不建议使用过长的字段作为主键，是因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

HASH索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。

hash索引的限制：

• 哈希索引不存储字段值，只包含哈希值和行指针，不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

Mysql中InnoDB引擎有一个特殊的功能叫做自适应哈希索引，它会在内存中基于B-Tree索引的基础上面创建一个哈希索引，这让B-Tree索引具备了一些哈希索引的优点。在B-Tree基础上创建一个伪哈希索引。因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。性能会非常高，缺陷是需要维护哈希值，不过可以用触发器来实现，避免“碰撞”问题，必须在WHERE条件中带入哈希值和对应列值。如果只统计记录数，直接用CRC32()的哈希值查询，也可用FNV64()函数作为哈希函数，哈希值为64位，冲突比CRC32()少

三、索引策略及SQL优化

在讨论优化之前，我们可以先看看阿里规范中的数据库规范部分

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
2. 【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
3. 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
5. 【推荐】如果有order by的场景，请注意利用索引的有序性。order by ***的字段是组合索引的一部分，并且放在索引组合顺序的***，避免出现file_sort的情况，影响查询性能。
6. 【推荐】利用覆盖索引来进行查询操作，避免回表。
7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
8. 【推荐】 SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts***。 
9. 【推荐】建组合索引的时候，区分度***的在最左边。
10. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
11. 【参考】创建索引时避免有如下极端误解：
1）宁滥勿缺。认为一个查询就需要建一个索引。
2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

大致浏览后，我们可以看到MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。我们就从两方面来探考工作中对于数据库的优化。

结构优化

我们首先从索引的建立来优化查询，《Relational Database index design and the optimizers》书中提到过一个名为“三星索引”的概念。三星索引如何定义呢：

• STAR1：与查询相关的索引行是相邻的，也就是where后面的等值谓词，可以匹配索引列顺序

把 WHERE 后的等值条件列作为索引最开头的列，如此，必须扫描的索引片宽度就会缩至最短。它最小化了必须扫描的索引片的宽度，避免全表扫描

• STAR2：索引行的顺序与查询语句需求一致，也就是order by 中的排序和索引顺序是否一致

将 ORDER BY 列加入到索引中，保持列的顺序排除了排序操作，避免在索引上使用计算，

• STAR3：索引行包含查询语句中所有的列

将查询语句中剩余的列都加入到索引中，避免了访问表的操作（避免了回表操作），只访问索引就可以满足了。索引并不是越多越好，不要超过6个。同时要避免在索引上使用计算，避免使用过长索引，尽量控制在长度为20。同时满足三颗星在工作中是很难达到的，我们只能根据实际情况，尽量将常用的查询字段加入索引中。

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关。实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引。联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，我们将where中的条件顺序颠倒，效果是一样的。对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。如果遇到索引中间某个条件未提供例如索引（a,b,c）中，where a= 1 and c= 1，可以考虑加入辅助索引(a,c),或者加入“隔离列”的优化方法，例如 SELECT * FROM user WHERE uid IN(1,2,'3','c') 。

总结来说，建立索引我们需要有以下考虑。

1、越靠左的索引，索引越需要保证唯一、自增，最好是表主键，主键需要选择与业务无关的自增字段。
2、需要从左到右，使查询条件依次匹配索引，有辅助索引或者“隔离列”来“填坑”。
3、索引不是越多越好，千级别记录条数的表，可以不建索引。
4、索引过长，我们可以用前缀索引，兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作
5、InnoDB聚集索引的叶子节点存储行记录，InnoDB必须要有，且只有一个聚集索引

查询优化

在建立好索引之后，其实最影响查询性能的，往往是我们业务中的慢SQL，那么如何避免慢SQL影响我们的性能呢？

1、我们一定要避免在索引上使用计算。在where字句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表查询，函数属于计算的一种。避免在 where 子句中对字段进行 null 值判断

效率低：
 select * from user where salary*22>11000(salary是索引列)

效率高：
 select * from user where salary>11000/22(salary是索引列)

我们需要注意的是，一直以来认为exists比in效率高的说法是不准确的。

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in，in 是把外表和内表作hash 连接。
例如select * from A where id in(select id from B) == (select * from A)*(select id from B)，适合B表比A表数据小的情况；

如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
例如select a.* from A a where exists(select 1 from B b where a.id=b.id) == select * from A，那有人说，exists中A只遍历A中数据条数，in是A条数*B条数，为什么还用IN呢，因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快。

综上，通常情况下采用exists要比in效率高，因为IN不走索引，exists()适合B表比A表数据大的情况，in反之，当A表与B表数据一样大时,in与exists效率差不多,可任选一个使用。无论那个表大，用not exists都比not in要快。not in 内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。

2、使用预编译查询
程序中通常是根据用户的输入来动态执行SQL，应该尽量使用参数化SQL，不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL进行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

3、尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

4、用where字句替换HAVING字句，少用DISTINCT

大量的排序操作影响系统性能，所以尽量减少排序操作。HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

具体可以看我的另外一篇文章：

阿里云ARMS解决慢SQL导致任务无法重启问题

5、使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

6、使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是也得避免频繁创建和删除临时表，以减少系统表资源的消耗。

7、避免使用事务、游标

游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。只在必要的情况下才使用事务，begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。 好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。

8、用varchar/nvarchar 代替 char/nchar

变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。不要以为 NULL 不需要空间，是varchar这样的变长字段， null 不占用空间。

9、更新优化

如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

10、 删除优化

能逻辑删除，就不要物理删除掉。最高效的删除重复记录方法：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

11、新增优化

在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

结语

SQL优化设计到非常多的方面，还有分库分表的垂直、水平分库分表等。由于篇幅有限，只能先从开发涉及到的优化部分开篇。抛砖引玉，如果有朋友感兴趣，我会在分章节好好探索一下数据库的优化问题。