MySQL数据库索引及调优

MySQL数据库索引及调优

1、概述：

索引是对数据库表中一个或多个列的值进行排序的结构。

索引的数据结构：

• 二叉树
• 红黑树
• Hash表
• B-Tree

MySQL底层是用B+Tree。

数据结构模拟器网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

对于以下数据：

select * from table where Col1=6

二叉树

二叉树的右侧节点比左侧节点大，构建的索引如下：

这样去查和去扫描表的效率其实都差不多（扫描表和索引都需要6次）。

红黑树

红黑树往里面插入元素时，它会自动平衡、进行旋转（红黑树也可以称为二叉平衡树）。执行上面的sql用索引就只需要3次。这样比二叉树要好一点，但是当我们数据量比较大时（例如有500W数据，这样红黑树的层高可能有20层），红黑树的层很高，如果我们查询的数据在最下层，也需要查询20次，效率还是不高。

hash

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候hash索引要比B+树索引更高效（如where name='jack’只需要做一次hash就可以找到,有相同的则会依次遍历链表）
• 仅能满足“=”、“IN”，不支持范围查询（如where name>‘jack’，这种查询hash索引是没有用的）
• hash冲突问题(哈希冲突：数据量较多时，通过哈希函数产生的哈希值是有限的，还是有很多不同的数据对应相同的哈希值)

B-Tree（高度控制在小的情况下能存大量的数据）

• 叶节点具有相同的深度，叶节点的指针为空

• 所有索引元素不重复

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

  如下是一颗B-Tree（图中Max.Degree为区间最大存放元素个数减一）：
  

B+Tree

• 所有数据都在叶子节点上

• 叶子节点有顺序访问指针
  

B-Tree和B+Tree的区别（都是从左到右依次排好序的）

1、B-Tree的data存在各个节点上，而B+Tree的data只存在叶子节点上，对于每个分层（16k）来限制的话，每一层B+Tree就能存更多的索引，这个样树的高度就能更低，查找的效率就更高。

2、B+Tree的叶子节点之间是有文件访问区间的指针的，这样对于范围查找就可以依次向后遍历；但对于B-Tree来说，如果出现断层，则又要从最上层开始查找。

B+Trees（B-Tree的升级）

• 非叶子节点不存储data，值存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

如下是一颗B+Trees：

2、InnoDB索引实现（聚集）

两个不同的存储引擎：

InnoDB：数据跟索引放在一个文件里（聚集索引和非聚集索引都可以有，非聚集索引存放的是聚集索引的key值，查找时找key再回表）

myisam：数据和索引分开在不同的文件（所以只有非聚集索引）

表数据文件本身就是按B+Tree组织的一个索引文件

聚集索引

叶节点包含了完整的数据记录（既有索引，又有索引所在行的所有数据，相对于非聚集索引：非聚集索引的索引和数据是在不同的文件）

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键

​ 1、当建表不设主键时：InnoDB方式，索引和数据是存在一起的，所以必须要有主键来维护B+Tree的树形结构。没有主键时，MySQL会选取一个不重复的列来作为主键维护B+Tree来存储，如果全部不重复，就会使用rowid的隐藏列来维护B+Tree。所以推荐来建主键，不用交给MySQL来帮我们做。

​ 2、整型数据肯定比字符串查找比对速度更快，且整型存储更节约空间。

​ 3、使用递增，则叶子节点会一直往后添加数据；如果不是，则会先找到该节点的位置，导致该节点分裂，再提到上层，插入的效率就变低，对于高并发来说影响更大。

为什么非主键索引（二级索引）结构叶子节点存储的是主键值（一致性和节省存储空间）

二级索引的索引值为主键索引的值，查询到二级索引的值后回表再找到对应主键对应的所有字段，二级索引和数据data是没有存在一起的，故也是一个非聚集索引

联合（复合）索引

我们很少在一个表中建多个单值索引，索引是需要占空间的，每次插入的时候都需要维护索引树，推荐建一两个联合索引。

比如说有以下的联合索引，三个字段name、age、position则会按照从左到右依次排序来建立索引。

当我们跳过前面的索引去查询的时候是不会用到索引的（符合最左原则）。如下：

where name=‘Bill’ and age=31 （会用到索引）

where age=30 and position=‘dev’ （不会用到索引）

where position=‘manage’ （不会用到索引）

案例

案例1

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候，优化器会 帮我们调整where后a,b,c的顺序，让我们用上索引。

案例2

SELECT * FROM table WHERE a > 1 and b = 2;

对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。 如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。

案例3

SELECT * FROM table WHERE a > 1 and b = 2 and c > 3;

(b,a)或者(b,c)都可以，要结合具体情况具体分析，a和c看区分度。

案例4

SELECT * FROM table WHERE a = 1 ORDER BY b;

对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！

SELECT * FROM table WHERE a > 1 ORDER BY b;

对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。

案例5

SELECT * FROM table WHERE a IN (1,2,3) and b > 1;

还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!