MYSQL(三) --mysql的索引

目录

一、索引概述

二、索引结构

三、索引语法

四、索引使用

五、索引设计原则

一、索引概述

1. 概念介绍：索引（index）是帮助MySQL 高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
2. 优缺点：

二、索引结构

mysql中存在多种索引结构，不同的存储引擎支持的索引方式也不同，具体如下：

         我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。下面详细介绍一下B+tree和Hash索引。

A、B+tree索引

         MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

为什么不用二叉树、红黑树呢？

• 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。 大数据量情况下，层级较深，检索速度慢。

• 红黑树：大数据量情况下，层级较深，检索速度慢。

B+tree和B树的区别？

• 所有的数据都会出现在叶子节点（对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；）
• 叶子节点形成一个单向链表

B、Hash索引

        哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

Hash索引的特点：

1. Hash索引只能用于对等比较(=， in)，不支持范围查询（between， >， < ， ...）
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

三、索引分类

按照索引字段进行常规分类如下：

按照存储方式分类（InnoDB）：

聚集索引选取规则:
➢ 如果存在主键，主键索引就是聚集索引。
➢ 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
➢ 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

三、索引语法

四、索引使用

• 最左使用原则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列， 索引将部分失效(后面的字段索引失效)。

• 范围查询

联合索引中，出现范围查询(>,<)， 范围查询右侧的列索引失效

• 索引列运算

不要在索引列上进行运算操作， 索引将失效。

• 字符串不加引号

字符串类型字段使用时，不加引号， 索引将失效。

• 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

• or连接的条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

•  数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

•  SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

• 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少 。

• 前缀索引

当字段类型为字符串（varchar， text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影
响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
 

五、索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪
个索引最有效地用于查询。