数据库调优三-索引调优

一 索引类型

1.1 从数据结构角度

• B-Tree,B+Tree索引
• Hash索引
• 空间索引（R-Tree）
• 全文索引

1.2 从功能逻辑角度

普通索引

普通索引是基础的索引，没有任何约束，主要用于提高查询的效率
示例：

CREATE INDEX index_name ON table(column(length))

唯一索引

唯一索引就是在普通索引的基础上增加了数据唯一性的约束，索引列的值必须唯一，允许有null值。如果一个唯一索引同时还是个组合索引，那么表示列值得组合必须唯一；在一张表里可以有多个唯一索引
示例：

CREATE UNIQUE INDEX indexName ON table(column(length))

主键索引

主键索引是一种特殊的索引，不允许有null值，并且一张表最多只有一个主键索引

组合索引

指多个字段上创建的索引，使用组合索引时遵循最左前缀原则。
示例：

CREATE index index_name CREATE table (column1, column2);

全文索引

全文索引，用来检索文本中的关键字，用的很少，一般应对这种需求用Elasticserch或者solr之类的全文索引引擎
示例：

CREATE FULLTEXT INDEX ...

1.3 从物理存储角度

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点不存储数据，而是指向对应数据块的指针。
Innodb的主键索引使用的是聚簇索引，而Myisam使用了非聚簇索引。

• 聚簇索引：表数据和主键一起存储的，聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据也一定是相邻的放在磁盘上。而由于无法同时把数据行同时存放在两个不同的地方，所以一张表只有一个聚簇索引。
  • 聚簇索引的二级索引：叶子节点不会保存引用的行的物理位置，而是保存行的主键值。
• 非聚簇索引：叶子节点存储的是数据块的指针。表数据和索引分开存储。查询时，先找到索引，再根据索引找到对应的数据行

聚簇索引的优点：

• 查找效率理论上要比非聚簇索引要高，但是插入修改，删除操作的性能比非聚簇索引要低
• 范围查询方便

聚簇索引缺点：

• 插入速度严重依赖于插入顺序，因此，对应Innodb表，一般都会定义一个自增主键
• 更新主键的代价很高，因为将会导致被更新的行移动

对于Innodb：

• 主键使用聚簇索引，并且一张表有且只有一个聚簇索引。如果创建的表没有主键，则Innodb会隐式的定义一个主键作为聚簇索引。
• 二级索引（非主键索引）叶子节点存储的是行的主键值，因此使用二级索引命中数据要查询两次，先用二级索引搜索到主键，再用主键查找数据。

二 索引数据结构

B-Tree索引(B+Tree)

2.1 二叉树查找

• 根节点左侧的子节点总是比根节点小，根节点右侧的子节点总是比根节点大；
• 这样查询性能会更快，因为对于二叉树而言，无需一次又一次进行遍历查找，最多只会进行树的深度，次数的查找

二叉树之间也有差距：如果查询‘8’这个元素，右边二叉树比左边性能更差
这种现象是因为：是否是平衡二叉树导致的；

2.2 平衡二叉搜索树—AVL树

• 每个节点的左子树和右子树的高度差不超过1；
• 对应n个节点而言，树的深度是log2n,查询的时间复杂度是O(log2n);
  但是对于更多的节点，树的深度还是很大的，这也意味着查询次数会很多

2.3 B-Tree(Balance Tree) --------平衡多路搜索树

图上是一个三阶B-Tree；
P1，P2代表指针；17,35表示关键字；Data表示数据；

B-Tree的查找方式：找到根节点的关键字和查找字对比，如果查找字比关键字小，则会去找关键字左边的P1指针，然后继续重复一下动作，直到找到数据

B-Tree特性：

• 根节点的子节点个数 2<=x<=m, m是数的阶;
  假设m=3； 根节点可以拥有2到3个子节点;
• 中间节点的子节点个数在 m/2<=y<=m之间；
  假设m=3，中间节点至少有两个子节点，最多有三个子节点;
• 每个中间节点允许包含 子节点个数-1个关键字，并且关键字按照升序进行排序;
• 一个磁盘节点包含关键字n个，那么同时他会包含n+1个磁盘指针

2.4 B+Tree

• B+Tree是B-Tree基础上的一种优化；
• MySQL中的Innodb存储引擎使用的就是B+Tree实现索引结构；
• 搜索过程，假设 我们搜索关键字n，先将n和磁盘根节点的关键字做比对，假设n等于8，关键字指针就会指向P1，找到字节点 磁盘块2；
• 然后继续做比对，发现8在 5-10之间，那么就会指向磁盘块2的P1指向指向下一节点;
• 如果下一节点为 叶子节点，那么就会去从叶子节点中，将关键字信息查找出来;

2.5 B-Tree和B+Tree之间的差异

• B+Tree有n个子节点的节点，它可以包含n个关键字
  B-Tree有n个子节点的节点，它可以包含n-1个关键字

• B+Tree中，所有的叶子节点包含了全部关键字信息；并且叶子节点是按照关键字大小从小到大顺序连接，构成一个有序链表；

  • B-Tree的叶子节点不包括全部关键字，它的关键字可能出现在中间节点和根节点

• B+Tree中，非叶子节点仅用于索引，不保存数据记录

  • B-Tree中，非叶子节点既可以保持索引，也保存数据；

• 由于B+Tree的中间节点只存放索引，所以对于相同的空间，B+Tree中间节点存放的关键字更多;所以B+Tree稍微矮胖一些;

• B-Tree的查询效率不稳定，可能会在根节点都找到数据，也可能在叶子节点找到数据；
  B+Tree不管怎么样都只能在叶子节点查询到数据

• B+Tree查询氛围查询比B-Tree更好; B-Tree只能一次一次查询，B+Tree可以一次性查询;

2.6 B-Tree(B+Tree)特性

• 完全匹配: index(name) ==> 使用 where name = ‘ ’是可以用到索引的;
• 范围匹配: index(age) ==> 使用 where age > 5一样可以使用到索引;
• 前缀匹配: index(name) ==> where name like 'xxx%'一样可以使用索引
  注意: %放在前面 就无法使用索引了

2.7 B-Tree(B+Tree)索引的限制

• index(name，age，sex) 组合索引限制; (最左前缀原则)
  • 查询条件不包括最左列，无法使用 索引
    • 比如，这里最左列是 ‘name’，语句 where age=5 and sex=1无法使用索引;
  • 查询条件不能跳过索引中的列，则无法完全使用索引;
    • 比如，where name = ‘xx’ and sex = 32 ===> 只能使用name这一列索引;
  • 查询中有某个列的范围(模糊)查询，则它的右边的所有列都无法使用索引;
    • where name = ‘xx’ and age>32 and sex = 1, 这是sex无法使用索引，因为age使用了范围查询

2.8 最左前缀原则:

• 索引按照最左优先的方式匹配索引，不满足上面所说的三个条件的时候，则无法完全使用索引;
• 当使用B-Tree(B+Tree)索引的时候，索引列的顺序是非常重要的;
• 在针对这列索引进调优的时候，索引列的顺序是非常重要的，甚至我们需要取创建一下，索引的列相同，但是索引列的顺序不同的索引；

2.9 Innodb存储方式

• 使用B+Tree
• 对于主键索引；叶子节点会存储主键以及主键所对应的内容；
• 对应非主键索引（二级索引，辅助索引）：叶子节点存储索引以及这条数据对应的主键；
  需要先通过非主键索引查到主键，然后通过主键查询出数据；

2.10 MyIsam存储方式

• 使用的B+Tree
• 主键/非主键索引 的叶子节点都是存储指向数据块的指针；

Hash索引

keys:创建索引的字段
buckets：索引字段计算出来的hash值，以及索引字段存放的物理位置所组成的hash表；
entries：数据；

通过关键字计算出hashcode；找到对应的buckethash表的值，如果发生了hash冲突，也就是两个元素计算出来的hashcode相同，会形成一个物理地址数组，之后查找的时候，会从物理地址数组从查找；

可以看出，发生了hash冲突后的hash索引，比未发生hash冲突的hash索引性能要低一点，所以需要尽量避免hash冲突的发生

hash索引支持的情况：
memory引擎支持显式的hash索引

• Innodb引擎支持“自适应hash索引”
  • 当Innodb发现某些索引值使用非常频繁；
  • 他会在内存中，基于B+Tree之上，再建立hash索引
  • 用show variables like innodb_adaptive_hash_index查看开关情况
  • 关闭这个功能 set global innodb_adaptive_hash_index = ‘OFF’

Hash索引的特性

    一般比B-Tree(B+Tree)的性能稍微好一些，只要hash不冲突，那么他的时间复杂度就是O(1)

Hash索引的限制

• Hash索引不是按照索引值进行排序的，所以没法使用排序
  • 你的索引条件中包含 Order By 是不支持Hash索引的;
• 不支持部分索引列匹配查找
  • Hash索引是使用索引列的全部 值去计算的，不支持部分匹配 hash(a,b),不支持只有a的条件查询 where a = ？;
• 支持等值查询( = 和 IN)都可以，范围查询和模糊查询都不支持
• Hash冲突越严重，性能下降越厉害

空间索引（R-Tree）

存储GIS数据，基于R-Tree;
Mysql5.7之前，只有MyISAM引擎支持; 在5.7版本之后InnoDB也开始支持了;
目前mysql对GIS支持不完善，所以不怎么常用;
对GIS支持比较好的是 POSTGreSQL(地图);

全文索引

• 适应全文搜索的需求
• 5.7之前，全文索引不支持中文；经常搭配Sphinx使用；
• 5.7开始mysql内置了解析器，ngram，支持中文

目前来说，应对全文搜索的需求，通常会使用一些搜索引擎

三 创建索引的原则

3.1 哪些场景建议创建索引

• select语句，对于某些字段经常作为where语句的查询条件；那么可以为该字段创建索引
  • 比如 where age = 1，where age > 1 等等经常被用到，那么就可以为age创建索引;
  • 同理如果根据两个字段经常筛选数据，那么就应该创建一个组合索引;
    • 创建组合索引需要考虑最左前缀原则: 如果age是必选条件，name是可选条件，那么index(age,name)是可以满足需求的;
• 对于Update/delete语句的where条件的字段，一般也需要创建索引；
  • 主键不需要额外创建索引
• 需要分组，排序的字段，一般也需要创建索引
• distinct所使用的字段，一般也需要创建索引
• 如果字段的值，有唯一性约束，可以创建索引
  • 对于某些字段，要求他不能重复，比如(用户名)，那么是可以创建唯一索引、主键索引的
• 对于多表查询，连接字段应该创建索引，并且类型务必保持一致；避免隐式转换；
  • 隐式转换可能会导致索引无法使用

3.2 哪些场景不建议创建索引

• where子句中用不到的字段；
  • 索引的作用是快速定位到想要的数据；
• 表中数据非常少，是否创建索引对查询的效率影响并不大
• 有大量重复数据，选择性低-创建索引作用不大
  • 索引选择性越高，可以让mysql在查询时过滤更多的行，提升查询效率;
  • 性别sex字段，不建议创建索引
• 对于更新频繁的字段，如果创建索引需要考虑其索引维护开销
  • 索引的更新维护是 有开销的
  • 某一字段修改非常频繁，查询很少，不建议创建索引

四，索引失效及解决方案

索引字段失效的情况：

• 索引字段参加了表达式计算（解决：事先计算好表达式的值再传过来）
• 索引字段是函数的参数（解决：预先计算好结果再传过来，不使用函数；使用等价的SQL去实现）
• like查询使用左模糊（解决：使用搜索引擎）
• 使用or查询的部分字段没有索引（解决：把没有索引的字段加上索引，两个索引会各自去查询，最后进行合并）
• 字符串赋值时未使用单引号（解决：规范SQL）
• 不符合最左前缀查询（解决：调整索引的顺序）
• 索引字段建议添加not null约束：
  • 单列索引无法存储null值，复核索引无法存储全为null的值；
  • 查询时，采用is null条件时，不能利用到索引，只能全表扫描
• 隐式转换导致索引失效，如把varchar类型转换为int类型

简化：1.表达式计算 2.函数参数 3.左模糊 4.or查询部分字段无索引 5.字段串无引号，6最左前缀原则 7字段定义为null 8字段类型隐式转换

五，长字段的索引调优

• 额外创建一个hash字段CRC32(“字符串”)
  • 字段长度应该比较小
  • 尽量避免hash冲突，流行使用crc32或fnv64
• mysql支持前缀索引
  • 长度确定有一个计算公式
  • 局限性，无法做order by, group by, 无法使用覆盖索引
• 后缀索引
  • 将数据翻转保持，再增加一个前缀索引

六，单列索引 VS 组合索引

-单列索引会产生一个求交集的开销，这会导致单列的索引性能稍微低于组合索引（可以再OPTIMIZER TRACE中求出来）

七，覆盖索引

概念：对于索引X，select的字段只需从索引中就能获得，而无需到表数据里获取，这样的索引就叫覆盖索引

• 尽量只返回想要的字段
  • 使用覆盖索引,减少网络传输的开销

八，重复索引，冗余索引和未使用的索引如何处理

• 重复索引：在相同的列上按照相同的顺序创建的索引。应当尽量避免重复索引，如果发现重复索引应该删除。
• 冗余索引：如果已经存在索引index(A,B)，又创建了index(A),那么index(A)就是index(A,B)的冗余索引，index(A)就是index(A,B)的前缀索引。Hash索引并没有这样的概念。
• 未使用的索引：直接删掉