MySQL索引

前言

索引的优缺点

1）优点

2）缺点

索引的分类

1）按照储存类型区分

2）按照逻辑区分

3）按照实际使用区分

索引失效        

索引选择原则 

关于MySQL索引的常见问题：

1.MySQL为什么采用B+Tree作为储存引擎？

采用B+Tree的原因：

2.索引优化

 

---

 

前言

        MySql作为常用的关系型数据库之一，通常有两种方式来访问数据库的行数据。

1）全表扫描

        从头到尾依次遍历，效率低，严重影响数据库的性能。

2）索引访问

        索引是一种特殊的数据库结构。由数据表中的一列或多列组合而成。由于保存了数据库中指定字段的位置，所以利用索引查询时，不用进行全表扫描，而是通过遍历索引来访问表中的记录行。

---

索引的优缺点

1）优点

        1.极大的加快了查询的速率，减少了查询时，数据库的扫描行数。

        2.通过创建唯一索引，来确保数据行的唯一性

2）缺点

        1.创建和维护索引需要大量时间，尤其是在增删改时，需要动态的修改SQL

        2.索引会占据一定的物理内存空间。

        3.当数据库中数据量小的时候，使用索引反而会降低效率。

---

索引的分类

        索引的类型与储存引擎有关，不同的储存引擎支持的索引类型有不同。索引的区分可以从储存方式，逻辑类型与实际使用分为三类。

1）按照储存类型区分

B+Tree索引与Hash索引

        B+Tree索引：B+Tree是B树（多路平衡查找树）的加强版。将数据都储存在叶子节点中，比B树的高度低很多，就保证了每一次读写磁盘加载的数据都比B树多。在叶子节点上储存数据，也能保证磁盘每次IO时更稳定。

        Hash索引：也被称为散列索引。MySQL数据库中目前只有Memoy储存引擎与HEAP储存引擎可以使用。Hash就是把任意长度的Hash值通过哈希算法转为固定长度的输出。

        Hash索引的特点

（1）无法进行排序与分组。

（2）只能进行精确查询，不能用于范围查询，或者全查。

（3）在InnoDB储存引擎中，有一种“自适应哈希索引”的功能，当某个索引值被大量使用时，会在B+Tree树上，在自动生成一个该索引值的哈希索引，使得其具有hash索引的特点，比如快速查询等。

2）按照逻辑区分

分为主键索引，唯一索引，普通索引，全文索引

        主键索引：专门为主键字段而生，添加后，所在列数据不允许重复值或空值。

        唯一索引：目的不是为了提高查询速率，而是为了避免数据重复。

        普通索引：没有限制，单纯就是为了提高查询速度。

        全文索引：用于查找文本中的关键字，只能使用在char，varchar，text类型的数据列中。

3）按照实际使用区分

分为单列索引，组合索引

        单列索引：该索引只对应一个字段，可以是主键索引，唯一索引，也可以是主键索引。

        组合索引：在表中多个字段创建一个索引。可以通过多个字段进行查询，但是必须使用这些字段中第一个字段后，组合索引才会被使用。

---

索引失效        

• （1）使用Like关键字进行模糊查询（在查询字符串中第一个出现通配符%）。
• （2）使用多列索引时，只有查询条件中出现这些字段中的第一个字段时，才会使用索引。
• （3）查询语句中，出现or关键字，却无法保证or两边的数据列都包含索引。
• （4）查询语句中出现函数。

---

索引选择原则 

        索引的选择遵循一些已有的原则，可以使数据库的查询操作更加高效。

1. 保证列的离散型，列的离散性越高，说明索引最终确定的范围越小，查询效率越高。（列的离散型：去重后的数据个数：数据总数）
2. 为经常需要排序，分组，联合操作的字段建立索引。
3. 为常作为查询条件的字段添加索引
4. 限制索引的数量，同时避免索引冗余（索引A与索引B，在命中A后，B绝对会命中，那么索引B就属于索引冗余）。
5. 尽量使用数据量少的索引。
6. 数据量少的数据库尽量不要设置索引
7. 删除使用频率底或者不用的索引。
8. 如果字段太长，尽量使用值的前缀作为索引。

---

关于MySQL索引的常见问题：

1.MySQL为什么采用B+Tree作为储存引擎？

        由于数据是储存在物理磁盘上，我们查询数据时，就需要读取磁盘，一次数据查询需要多次磁盘IO，相比于内存操作，磁盘IO速度相对较慢，所以我们设计索引数据结构时，就是尽可能减少磁盘的IO次数。

        由于树的高度越高，磁盘IO的次数就越多，查询自然就越慢，我们给普通的二叉树增加条件，平衡左右子树，然后就延申出了很多平衡树，例如：平衡二叉查找树，红黑树，多路平衡查询树（B树），B+树等。

        B树：

对于一个m阶的B树       

（1）根节点至少有俩个子节点。

（2）每个中间节点都包含k-1个元素，k个子节点。（m/2 <= k <=m）

（3）每个叶子节点都包含k-1个元素。

（4）中间节点的数据，按照升序排列。

（5）所有的叶子节点都位于同一层。

  特点：高度比较低，每个叶子节点都含有多个元素，减少了IO次数。

B+树：  
        

B+树特点： 

（1）子节点数量=元素数量，每个元素不保存数据，只用于保存索引。所有数据都保存在叶子节点上。

（2）叶子节点包含元素的全部信息，并且叶子节点按照元素大小形成有序列表。

采用B+Tree的原因：

（1）每个节点储存的数据更多，比B树更矮胖，磁盘IO次数更少。

（2）非叶子节点储存索引，便于日后优化。

（3）叶子节点储存数据，磁盘IO次数更稳定。

（4）叶子节点之间形成有序链表，更方便范围查找，值需要遍历链表中相邻元素，更适合做索引结构。

2.索引优化

（1）选择合适的字段创建索引。

• 不为null的字段，否则数据库以后很难优化
• 被做条件查询的字段
• 频繁排序的字段
• 频繁用于连接的字段

（2）被频繁更新的字段，作为索引的话，维护成本较大。

（3） 尽可能选择联合索引。可以节约磁盘空间，且修改数据的效率也会提升。

（4）注意要避免冗余索引。

（5）避免在where子句中使用函数，导致索引失效。

（6）禁止使用SELECT*，避免全表扫描，选择SELECT 字段1，字段2等。