了解SQL索引

Posted on 2023-03-08 09:01 池塘鱼阅读(46) 评论(0) 编辑收藏举报

一、为什么要用索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题。最容易出问题的，就是查询操作。而优化查询，一般都需要用到索引。

索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

索引太多不利于增删改操作，每次做增删改都需要同步变更索引。索引太少不一定能保证性能优化到位。索引的设计应该基于代码逻辑、实际数据、业务场景等多个因素合理设置。

二、索引原理

索引的本质：优化查询方法，通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。

索引的数据结构：B+树，即高度可控的多路平衡查询树。

（1）B+树是查询树，符合左子树小于当前结点，右子树大于当前结点的结构，因此在查找数据时是类二分算法，其非叶子结点是按聚簇索引的排序结果（即一般就是按主键排序）存放键值，时间复杂度为O（logN）级别；
（2）因为非叶子结点只存放键值，只在叶子结点存放全部数据，因此找数据必到树的最后一层，能将时间复杂度稳定在O（logN）；
（3）同时因为叶子结点存放了全部数据并且用链表顺序连接，因此在范围查询时即为顺序查询。总之对于单值查询和范围查询都能极大提升效率。

读取数据时的磁盘IO和部预读性原理：我们的数据是存储在磁盘上，需要的时候读到内存中。而磁盘IO次数取决于B+树的高度h（因为数据全都存放在叶子结点），h=㏒(m+1)N，每页能存放的数据数量为底m，总数据量为幂数N。因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每次IO时，会读取一页数据磁盘块的大小也就是一个数据页的大小，页大小是固定的，具体大小和操作系统有关，如果数据项占的空间越小，数据项的数量越多，树的高度越低。因此索引字段要尽量的小。

索引的最左匹配特性：B+树是按照从左到右的顺序来建立搜索树的，比如下图，我们称第一个数为a，第二个数为b，可以看到数据是先按照a再按照b排序的。因此我们建立索引（a,b），查找a和ab都能用到索引，查找b则不能，因为按b无序排列。

三、MYSQL索引管理

1.索引分类

（1）物理存放顺序：聚集索引和辅助索引

聚集索引与辅助索引相同的是，都是采用B+树结构组织数据，即高度是平衡的，叶子结点存放着所有的数据。在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，当前一般的机械硬盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。

聚集索引与辅助索引不同的是，叶子结点存放的是否是一整行的信息，聚集索引是，辅助索引不是。

nnoDB存储引擎表是索引组织表，按聚集索引的组织顺序（优先级：主键/非空唯一索引列/生成隐藏ID值为key构造一颗B+树）存放。

聚集索引：照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。利于根据主键的值匹配（叶子节点即为数据）和范围查询（数据页按主键物理存放）。

辅助索引：表中除了聚集索引外其他索引都是辅助索引，叶子节点存储的不是整行数据，而是为InnoDB存储引擎指向数据行的聚集索引键。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。数据组织是逻辑结构，与物理存放位置无关，可以有多个辅助索引。

（2）限制效果：普通索引和唯一索引

MySQL常用的索引：

普通索引INDEX：加速查找，索引字段可以是一个，也可以是多个
唯一索引：
- 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
- 唯一索引UNIQUE：加速查找+约束（不能重复）

（3）覆盖索引

MySQL数据库版本为5.0以上的InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

覆盖索引的另外一个好处是对某些统计问题而言的。例如对于count(*)来说，只需要对任意一个字段做统计即可，因此优化器会选择辅助索引而非聚集索引来减少IO次数，相当于覆盖索引。

对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引。

2.索引类型

Hash与BTree：

Hash类型的索引：为索引列计算出一个较小的哈希值，为该哈希值和行数据所在的位置做索引映射。如果出现哈希冲突（不同的值有极小概率可能计算出相同的哈希值），则用链表解决。
- 因为是对索引列值做的哈希，因此只能用于精准查询，不能用于匹配、范围查询和排序。即只适用于： =、<>、in。
- 因为是指向存储的指针而非直接存储的数据，因此不能避免行查询。查找某个数据很快，操作的平均时间复杂度为O（1）。但是如果出现很多哈希冲突查询就会比较慢，因为需要通过链表里的指针一行行去查询，不像正常情况下拿到指针后只需访问一行数据即可。增删改操作同理。冲突越多，代价越大。
Btree类型的索引：B+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）。

3.存储引擎支持的索引类型

不同的存储引擎支持的索引类型也不一样。
MYSQL使用InnoDB存储引擎，InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引（虽然在数据库软件中给了这个选项，但即使选Hash索引，创建后再看会发现变成了BTree索引）（InnovationDB支持的是无法认为干预的自适应Hash索引，即MYSQL根据表的使用情况自动生成哈希索引来进行优化，而无法人为的创建真正的哈希索引）；

其他暂略。

四、使用和设计索引

1.原理

（1）需要注意范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between…and…、like，这样查询出的结果就会有很多条，影响查询速度。尽量让查出的数据条数很少就能很快。比如有一个大于小于画出的具体区间，或者不等于一个不存在的值。

（2）尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例。一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高。这与全表扫描的IO次数没有多大区别，所以速度很慢。

（3）索引列不能在条件中参与计算，保持列“干净”。错误：from_unixtime(create_time) = ’2014-05-29’，正确：create_time = unix_timestamp(’2014-05-29’)。

（5）制作联合索引(d,a,b,c)。

a = 10 and b = 'xxx' and c > 3 and d =4：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序。
a = 10 or b = 'xxx' or c > 3 or d =4：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d。在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询。and条件下只要有一个有索引且区分度高的字段就能加速查询，不管其他字段啥样，这个条件不成立的话就不会继续执行了。因此我们很多时候可能在多条件的时候没必要为每个字段都添加索引。

（6）最左前缀匹配原则（详见第八小节），非常重要的原则。

一方面，后面的字段无法单独匹配到索引。例如组合索引为：(name,email) name and email -- 命中索引 name -- 命中索引 email -- 未命中索引。
另一方面，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.原则

（1）明确查询条件，尽量缩小查询范围，让查询结果条数尽量少。

（2）尽量选择区分度高的列作为索引，即重复比例低的字段。散列值少的字段不适合建立索引，如性别。

（3）索引列不能在条件中参与计算，保持列“干净”。

（4）排序条件为索引，则select字段必须也是索引字段，否则无法命中。除非是对主键排序则不用加，速度还是很快。

（5）and和or的逻辑：

对于多个and拼接的where条件，所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立。因此最少保证有一个高区分度的索引字段（如果区分度够高可能一个就够了，可以锁定很小的范围）。不强求顺序，在and条件下SQL查询优化器会自动寻找这种字段加速查询。
对于多个or拼接的where条件，只要有一个条件成立则最终结果就成立。不会像and那么优化，or条件只是按照顺序从左到右依次查询。因此要将成立可能小的条件放在前面。

（6）建立联合索引时，将区分度高且在条件里范围明确的字段放在前面。因为优化器会调整字段顺序，将范围小的条件在前面再去匹配索引，因此如果建立索引时不注意顺序就无法匹配到鄋索引字段了。

（7）查询和函数都指定明确的列，避免select *。统计时尽量使用使用count(*)。

（8）建表时尽量为字段设计固定长度，尽量使用 char 代替 varchar。并且尽量为表的字段顺序固定长度的字段优先。

（9）组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）。而且联合索引在第一个键相同的情况下，已经对第二个键进行了排序处理，便于在第一个条件的基础上对第二个条件查询。

（10）尽量使用短索引。

（11）使用连接（JOIN）来代替子查询(Sub-Queries)。

（12）连表时注意条件类型需一致。

（13）尽量实现覆盖索引。