索引

文章来自作者张洋等，总结晋学领

一、索引的本质

　　索引是帮助MySQL高校获取数据的数据结构。提取数据主干就可以得到索引的本质，索引是数据结构；

　　我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

二叉查找树结构的索引（很少真正在数据库中实现）查找复杂度为 $O (l o g_{2} n)$

B—Tree树的示意图（查询复杂度为树的高度）

　　如果某个指针在节点node的左右相邻key分别是 $k e y_{i}$

$k e y_{i}$

B+Tree（mysql普遍使用B+Tree实现索引结构）

　　1.与B-Tree树相比，B+Tree的每个节点的指针上限为2d而不是2d+1;

　　2.因为，B+树的叶节点是集合了所有的节点，而B树中的节点是分布到整个树中的；所以内节点不存储data，只存储key，叶子结点不存储指针；

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关;

带有顺序访问指针的B+Tree

$O (l o g_{2} n)$

为什么使用B-Tree或者B+Tree

上文说过，红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

　　B-Tree

　　B-Tree树的查找的渐进复杂度是索引树的高度。树的节点大小被设计为一个页的大小，根据磁盘预读原理，这样查找一个节点只需要一次I/O就能实现；同时在创建节点的时候，一个节点直接申请一个页的内存；B-Tree树一次检索最多需要h-1次I/O(根节点是常驻内存)，渐进复杂度是O（h）=O（logdN），一般应用中出度d是非常大的数字，通常超过100，因此h很小，一般不超过3；

　　　B+Tree

　　B+Tree更适合外存索引，原因和内存节点出度d有关，如果d越大，出度越大相应的高度会变小，查找的渐进复杂度会很小；出度的上限取决于节点内key和data的大小；因为每个节点的内存是一个页的大小，所以出度和节点中键，数据节点和指针的大小和成反比；

　　由于上面的原因，B+树的内节点去掉了data域，所以会有更多的出度；相应的树的高度h就会小，查找的效率也就会提高；

二、mySql索引的实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

　　MyISAm索引实现

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

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。这里面还有一个就是主索引和辅助索引的概念，主索引是根据primaryKey作为key进行构造的B+树，要求key不能重复；相应的辅助索引的结构和主索引的树的结构是相同的，但是辅助索引中的key可以重复；同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式叫做“非聚集索引”

　　InnoDB索引实现

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

1.InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件存放的是数据记录的地址，而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的节点保存了完整的数据记录。这个索引key是数据表的主键，因此InnoDB表数据文件本身就是主索引；

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

2.MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。所以这样的话对于InnoDB引擎的辅助索引，进行检索的时候，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录；

三、索引的使用策略和优化

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query Optimization）

情况一：全列匹配。

当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。

情况二：最左前缀匹配。（https://blog.csdn.net/qq_39390545/article/details/108540362）

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。（填坑，和增加辅助索引）

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

情况四：查询条件没有指定索引第一列。

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

由于不是最左前缀，索引这样的查询显然用不到索引。

情况五：匹配某列的前缀字符串。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）

情况六：范围查询。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

情况七：查询条件中含有函数或表达式。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引

三、索引的选择性与前缀索引

1.表中记录数大于2000条或者，不重复的表记录数和总的表记录数的比值很大使用索引；

2.有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

https://www.cnblogs.com/studyzy/p/4310653.html

前缀索引似乎是MySQL中的一个概念，在SQL Server和Oracle中没提出这个概念。于是就安装了一个MySQL来做实验，搞清楚前缀索引。

前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。有点相当于Oracle中对字段使用Left函数，建立函数索引，只不过MySQL的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用left函数。

别的文章中提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法为：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

这里最关键的参数就是prefix_length，这个值需要根据实际表的内容，得到合适的索引选择性（Index Selectivity）。索引选择性就是不重复的个数与总个数的比值。

select 1.0*count(distinct column_name)/count(*)
from table_name

比如我们现在有个Employee表，其中有个FirstName字段，是varchar(50)的，我们查询该字段的索引选择性：

select 1.0*count(distinct FirstName)/count(*)
from Employee

得到结果0.7500，然后我们希望对FirstName建立前缀索引，希望前缀索引的选择性能够尽量贴近于对整个字段建立索引时的选择性。我们先看看3个字符，如何：

select 1.0*count(distinct left(FirstName,3))/count(*)
from Employee

得到的结果是0.58784，好像差距有点大，我们再试一试4个字符呢：

select 1.0*count(distinct left(FirstName,4))/count(*)
from Employee

得到0.68919，已经提升了很多，再试一试5个字符，得到的结果是0.72297，这个结果与0.75已经很接近了，所以我们这里认为前缀长度5是一个合适的取值。所以我们可以为FirstName建立前缀索引：

alter table test.Employee add key(FirstName(5))

建立前缀索引后查询语句并不需要更改，如果我们要查询所有FirstName为Devin的Employee，那么SQL仍然写成：

select *
from Employee e
where e.FirstName='Devin';

下面总结一下什么情况下使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%'
字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

InnoDB的主键选择与插入优化

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

posted @ 2017-05-24 17:17 Jin_c 阅读(205) 评论(0) 编辑收藏举报

刷新页面返回顶部

Jin_c

索引