MySql索引原理和SQL优化方式

-- 主键索引
PRIMARY KEY(key1, key2)

--唯一索引
UNIQUE(key)

--普通索引
INDEX(key)
-- OR
KEY(key[,...])

--组合索引
INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

约束：为了实现数据的完整性，对于 innodb，提供了以下几种约束，primary key，unique key，foreign key，default，not null；

其中外键约束：用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作 用；而 innodb 完整支持外键，并具备事务性；

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

数据结构

索引包括多种数据结构，其中最常用的就是B+数索引，hash索引，全文索引。我们本文主要讨论的是在InnoDB引擎中所使用的B+数索引。那么为什么我们不使用红黑树呢？

首先B+树全称：多路平衡搜索树。对于瘦高的红黑树来说B+树是胖矮的。我们把所有的数据存放在叶子节点中，而且叶子节点还串联在一起，一个页中可以存放几个叶子节点，而非叶子节点存放索引内容，并且也放在页中，我们可以看下图。

当我们查找一个数据的时候，可以使用更少的磁盘IO就可以获得想要的数据。比如我们想要查找25这个节点的数据，先查找第一个页，找到25的位置，在看第二个页，，在找到第一个叶子节点，然后平移过去找到25这个节点，一共有4次磁盘IO（每次查找页就是一次IO）。

但是使用的是红黑树的话，那么就不止4次的磁盘IO了。当然除了更少的磁盘IO后，也是为了方便范围查找。对于B+树来说，他的叶子节点都串联在一起，当找到第一个节点之后，就可以相继找出其他节点。但是红黑树来说的话，每次都要重新查找叶子节点。

总结：可以减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页； 平衡二叉树（红黑树、AVL 树） 特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范围查询； 每个索引对应着一个 B+ 树；

索引实现-物理存储

innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由 64 个 连续页构成）；页的默认值为 16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页连续，存储引擎一般一次从磁盘中申请 4~5 个区；

3、使用索引的场景

我们每次搜索数据都是通过索引来实现的，其中在哪里可以使用到索引呢？是在where，group by，order by后面使用索引的。那么哪些场景不适合使用索引呢？首先就是没有where，group by，order by的地方，还有区分度不高的列，需要经常修改的列，表数据量少。

我们创建B+树类型的索引就是为了通过比较来找到我们所需要的数据，但是当区分度不高的时候，反而会降低速度，如果经常修改这个列，那么我们的B+的结构就要经常变化，更加影响速率，表的数据较少的时候，没有必要去创建索引，创建索引反而会浪费空间。

学习了上面所讲述的B+树和索引之后来想一下下面几个经典的面试题吧：

• 为什么采用多路的树结构？一个节点有多条链路，相较于平衡二叉搜索树是一个更加矮胖的结构，树的高度更低，可以较少的磁盘io次数来索引数据。
• 为什么非叶子节点只存储索引信息？B+树节点映射固定的大小磁盘数据，可以包含更多的索引信息。能快速锁定数据所在叶子节点的位置。
• 为什么叶子节点依次相连？便于范围查询，避免中序遍历回溯回去查找下一个节点。

二、索引方式

1、聚集索引

按照主键构造的 B+ 树，叶子节点中存放数据页中，数据也是索引的一部分。

一般来说主键索引就可以作为聚集索引，当没有主键的时候，如果有唯一索引，那唯一索引也可以作为聚集索引。18