数据库索引原理及优化

哪些列应该创建索引

1、主键自动创建唯一索引

2、频繁作为where查询条件的字段应该创建索引

3、查询中与其他表关联的字段，外键关系建立索引

4、查询中排序的字段（order by），排序的字段若通过索引区访问将大大提高排序速度

5、查询中统计或者分组字段（group by）

注：order by/group by是先做排序再分组，因此可以建索引

不需要创建索引：

1、频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

2、WHERE条件里用不到的字段不创建索引

3、表记录太少，即小的数据表不应当使用索引

4、经常增删改的表

5、如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

6、数据值分布比较均匀的不适合建索引，比如性别

7、如果列中包含大数或者NULL值，不宜创建索引

Mysql中哪些Sql不走索引

 SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算，原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正则表达式不使用索引，这应该很好理解，所以这就是为什么在SQL中很难看到regexp关键字的原因 

-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引，同样也是使用了函数运算 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字 

-- MySQL内部优化器会对SQL语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引
————————————————
版权声明：本文为CSDN博主「公众号：流花鬼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_32331073/article/details/79041232

索引是帮助MySQL高效获取数据的排好序的数据结构，用于快速找出某个列中有一特定值的行。

通过上述定义可以理解索引三个基本特性：

1、索引的作用是为了追求高效查找；

2、索引是一种数据结构，且是有序的；

3、索引用于快速查找某一个特定值的行（非特定值情况，即模糊匹配的情况是无效的）。

不使用索引，MySQL必须从第一条记录开始读完整个表，直到找到相关的行，表越大，查询数据所花费的时间就越多（全表扫描）。如果表中查询的列有一个索引，MySQL能够快速到达一个表搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

1.1 隐式索引
隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。

之所以会存在隐式索引，是因为数据库总是优先考虑使用索引的这种情况，除非索引失效，但是前提是需要有字段建立索引，如果用户不手动显式指定索引字段，那么数据库就自动添加一个。一切都是为了尽可能的将能优化的情况都考虑到。

1.2 特点
优点：

1、提高数据检索效率，降低数据库的IO成本；

2、通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占空间的（空间换时间）；

2、虽然索引大大提高了查询速度，同时会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE操作。

02 分类
2.1 根据索引字段
1、单值索引

单值索引即一个索引只包含单个列，一个表可以有多个单列索引。

2、复合/联合索引

一个索引包含多个列，例如：INDEX Multidx(id,name.age)。

创建单列索引还是复合索引，要看每次查询中，哪些列在作为过滤条件的WHERE子句中最常出现。

如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的WHERE子句用到了两个或者更多的列，那么复合索引就是最好的选择。

2.2 根据索引字段类型
1、主键索引/聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引总是把数据行存储在叶子页中（即数据和索引都存储在叶子节点），因此一个表中只能有一个聚簇索引。

对于上述聚簇索引结构，主键a为索引，则叶子节点就是最终的数据存储节点，查找到叶子节点也就对应找到了对应的数据了。

并不是所有的存储引擎都支持聚簇索引。

InnoDB只有一个聚集索引：

默认会拿主键id作为聚集索引；

如果没有主键，会取非空的唯一索引作为聚集索引；

如果没有主键和非空唯一索引，则InnoDB会自己维护一个唯一row_id作为聚集索引。

聚簇索引的优点如下：

可以把相关数据保存在一起；

数据访问更快；

使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

当然，聚簇索引也有它的缺点：

聚簇索引最大限度提高了I/O密集型应用的性能，但如果所有的数据都存放在内存中，聚簇索引就没有优势了；

插入速度严重依赖插入顺序，这也是为什么InnoDB一般都会设置一个自增的int列作为主键；

更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移到新的位置；

如果不按顺序插入新数据时，可能会导致“页分裂”；

二级索引访问可能会需要进行回表查询。

2、唯一索引

索引列的值必须唯一，但允许有空值。

UNIQUE就是唯一索引，即要求每一行的数据必须唯一，但可以为空。

3、普通索引/二级索引/辅助索引

InnoDB 中的辅助索引在叶子节点中并不存储实际的数据，只会包含主索引的值（即索引和数据分开存储）。这就意味着如果使用辅助索引进行数据的查找，只能查到主索引，然后根据这个主索引再次扫描以下主索引的树，进行一次回表操作。

对于上述非聚簇索引，假设字段a、b、c为索引，a为主键，则最终查找行记录的时候，需要通过主键a在叶子节点的位置，获取具体数据的位置信息，然后再去具体地址查找行记录。即获取叶子节点的同时并不能直接获取最终结果，需要经过二次查找。

这种索引也可以叫二级索引或者辅助索引。

注：MyISAM是非聚集索引（对应文件MYI和MYD，分别存储索引和数据信息），InnoDB是聚集索引（对应文件ibd，数据和索引信息）。

4、全文索引

全文索引只有在MyISAM索引上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。

5、空间索引

空间索引是对空间数据类型的字段建立的索引。

03 原理

3.1 页
Mysql的基本存储结构是页（记录都存在页里边），数据页的特点：

1、各个数据页可以组成一个双向链表；

2、而每个数据页中的记录又可以组成一个单向链表；

1）每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录；

2）以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以，select * from user where username='xxxx'，默认会这样做：

1、定位到记录所在的页：需要遍历双向链表，找到所在的页；

2、从所在的页中查找相应的记录。

3.2 二叉树
如果用二叉树建立索引的数据结构，那么每个节点存储K-V，即索引字段和索引字段所在行的磁盘地址的指针，时间复杂度为O(logN)。

但是，实际应用中数据库不使用二叉树作为索引的数据结构，主要是因为二叉树会出现极度不平衡的情况：

3.3 红黑树
为了应对极端不平衡的情况，尝试使用红黑树作为索引的数据结构。红黑树是二叉平衡树。

红黑树不适合作为索引的特殊场景：存储数据非常大的时候，树的高度h太大，查找的数据如果在叶子节点，极端的情况下需要查询高度h次才可以。

我们需要控制查找次数，即树的高度，而红黑树每个节点只存储一个K，则为了降低高度，可以在一个节点存储多个数据，从而可以将树的高度降低，而B+树能够控制树的高度在3~5。

3.4 B Tree
B类树的一个很鲜明的特点就是树的层数比较少，而每层的节点都非常多，树的每个叶子节点到根节点的距离都是相同的（这也是为什么叫Balance Tree的原因）。

另外，树的每一个节点都是一个数据页（B+树是叶子结点是数据页，其余全部为索引页），这样每个节点只需要一次IO就可以全部读取。这样的结构保证了查询数据时能尽量少地进行磁盘IO，同时保证IO的稳定性。

特点：

1、叶子节点具有相同的深度，叶子节点的指针为空；

2、所有索引元素不重复；

3、节点中的数据索引从左到右递增排列。

注：为了能够存储足够多的数据的同时控制树的高度，一个节点存储数据的个数由表数据和高度确定。如果数据较多，则每个节点存储数据多一些，这样高度就降下来了。

模拟查找关键字29的过程：

1、根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

2、比较关键字29在区间（17,35），找到磁盘块1的指针P2。

3、根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

4、比较关键字29在区间（26,30），找到磁盘块3的指针P2。

5、根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

6、在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存。

但如果我们想进行范围查找，查询10~79之间的数据，就需要从跟节点一个一个往下查，范围跨度越大，则磁盘IO的次数就越多，性能越差。

由于B树的节点除了存储Key还存储Data，这样每一层存储的Key有限，也就是说这样导致层高不可控，磁盘IO较多。如果除了叶子节点都存储Key值，这样就可以很快找到叶子节点，进而找到Data，这就引入B+树。

应用：MongoDB使用B树。

MongoDB是一个通用的、面向文档的分布式数据库，区别于传统的关系型数据库 MySQL、Oracle 和SQL Server，MongoDB最重要的一个特点就是面向文档：

1、作为非关系型的数据库，MongoDB对于遍历数据的需求没有关系型数据库那么强，它追求的是读写单个记录的性能（即MongoDB是为了迅速获取对应的键值对信息，所以使用B树可以更好满足需求，如果使用B+树就可能出现回表的情况，性能反而比较差）；

2、大多数OLTP的数据库面对的都是读多写少的场景，B 树与 LSM 树在该场景下有更大的优势。

上述的两个场景都是MongoDB需要面对和解决的，所以我们会在这两个常见场景下对不同的数据结构进行比较。

因此，并不是说B树就不好，性能就差，B+树就优于B树，抛开具体的应用场景，无法讨论具体数据结构的优劣性！只不过我们在数据库这种场景下需要B+树。

3.5 B+ Tree
B+ Tree和 B Tree不同，B+ Tree中，只能将数据存储在叶子结点中，内部节点将只包含指针，而B Tree可以将数据存储在内部的叶节点中。

因此B+ Tree的关键优势是中间节点不包含数据，因此B+ Tree的大小远小于B Tree，并且可以将更多数据存储到存储器中。另外，B+ Tree的每一个叶子节点包含了到相邻的节点的链接，这样可以快速地进行范围遍历。

B+树结构如下：

MySQL真正使用的数据结构不是B树，而是B树的变种B+树，B+树是一个平衡二叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。其特点：

1、非叶子节点不存储data，只存储索引（冗余），可以放更多的索引；

2、叶子节点包含所有索引字段（非叶子节点也有一个相同的值，存在冗余，B树则没有冗余）；

3、叶子节点用指针连接，提高区间访问的性能（B树叶子节点是没有这个指针的）。

注：在非叶子节点不存储data，因为InnoDB对于页大小有限制（16KB），所以只存储索引可以在每一行存储更多的索引信息（B树非叶子节点存储data信息，浪费内存），将data放到叶子节点。例如，bigint的索引，则15占用8Byte，后面地址信息6Byte，则一个索引占8+6=14Byte，页大小16KB，则大概可以放16KB/14Byte=1170个节点。如果叶子节点data占据1KB，则页大小16KB的时候叶子节点可以存储16个索引，则最终可以存储的索引为1170117016（假设是3层），这是千万级别的数据。

根节点一般事先加载到内存，所以在根节点可以快速定位加载的页，从磁盘加载对应的数据页到内存，实现快速查找。

在B+树叶子节点保存了所有的索引数据（不是所有的表数据），所有非叶子结点都会在叶子节点保留一份备份，所以B+树节点树比实际存储的数据的个数要多。

B树与B+树：

1、B树非叶子节点存储data（索引数据的地址），B+树非叶子节点只存储索引信息；

2、B树不存在数据冗余，B+树存在冗余（普通索引会存储主键，主键索引会存储所有数据，这样就存在数据冗余了），叶子节点存储所有数据。

3.6 Hash
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

Hash索引结构的特殊性，其检索效率非常高，索引的检查可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到叶节点。这样多次的IO访问，所以哈希索引的效率要高于B-Tree索引。

哈希索引局限性：

1、hash函数计算后的结果是随机的，如果是磁盘上放置数据，比如主键id，那么随着id的增长，id对应的行在磁盘上随机放置，而随机查询非常慢；

2、哈希索引也没办法利用索引完成排序；

3、无法对范围查询进行优化；

4、在有大量重复键值情况下，哈希索引的效率也是极低的（哈希碰撞问题）；

5、无法利用前缀索引，比如在B-Tree中，filed列的值“helloword”并添加索引，查询xx=helloword时自然可以利用索引，xx=hello也可以利用索引（左前缀索引），因为hash(“helloword”和hash(“hello”)两者的关系仍为随机；

6、排序无法优化；

7、必须回行，就是说，通过索引拿到的数据位置，必须回到表中取数据。

3.7 LSM
LSM Tree技术出现的一个最主要的原因就是磁盘的随机写速度要远远低于顺序写的速度，而数据库要面临很多写密集型的场景，所以很多数据库产品就把LSM Tree的思想引入到了数据库领域。

LSM Tree顾名思义，就是The Log-Structured Merge-Tree的缩写。从这个名称里面可以看到几个关键的信息：

1、log-structred，通过日志的方式来组织的

2、Merge，可以合并的

3、Tree，一种树形结构

实际上它并不是一棵树，也不是一种具体的数据结构，它实际上是一种数据保存和更新的思想。简单地说，就是将数据按照key来进行排序（在数据库中就是表的主键），之后形成一颗一颗的树形结构，或者不是树形结构，是一张小表也可以，这些数据通常被称为基线数据；之后把每次数据的改变（也就是log）都记录下来，也按照主键进行排序，之后定期的把log中对数据的改变合并（merge）到基线数据当中。

注：OceanBase采用LSM。

04 基本操作
4.1 创建索引
CREATE INDEX 索引名称 ON table (column1,…);

4.2 删除索引
DROP INDEX 索引名称 ON 表名;

4.3 查看索引
SHOW INDEX FROM 表名;

05 索引失效
5.1 最左前缀原则/ABC问题
即便表建立了索引，where查询语句中存在索引，也不一定走索引，是不是走索引遵循最左前缀原则：

索引对应的数据结构B+树生成的时候就是按照最左的字段排序生成的，如果不指明该字段的取值，那么就无法确定走索引B+树的左还是右，进而只能全表扫描。

5.2 索引失效的情况
1、列类型是字符串，查询条件未加引号

2、未使用该列作为查询条件

3、使用like时通配符在前

4、在查询条件中使用OR

5、索引字段做计算（格式转换，运算）

6、字符类型索引=数值类型字段值

7、不符合最左前缀/联合索引ABC问题

07 索引优化
7.1 设计高效索引策略，避免索引失效
设计高效索引策略的时候需要考虑以下的情况：

1、索引长度以及区分度；

2、联合索引需要考虑索引先后顺序。

7.2 考虑索引覆盖
索引覆盖是指，如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据（即避免回表操作），这种查询速度非常快，称为“索引覆盖”。

覆盖索引是一个非常有用的工具，可以极大提升性能。试想一下，如果一个查询只需要扫描索引而无需二次回表查询，会带来什么好处：

1、索引行通常远小于数据行的大小，所以如果只需要索引，那么MySQL就会极大地减少数据访问量；

2、因为索引是按照顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多；

3、由于InnoDB的聚簇索引，所以覆盖索引对InnoDB特别有用。

7.3 索引下推
Index Condition Pushdown（索引下推），MySQL 5.6引入了索引下推优化，是一种在存储引擎层使用索引过滤数据的一种优化方式，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。

官方文档中给的例子和解释如下：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MySQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

上述仅仅是索引优化的一小部分，对于具体的SQL语句，仍然存在诸多优化点，比如ON字段设置索引，WHERE条件查询使用索引，ORDER BY字段优先使用主键或索引等等。