MySql性能调优01-[数据结构和索引]

什么是索引

索引的种类

在Mysql中索引是在存储引擎层实现的，而不是在服务层实现的

• 按数据结构分：B+tree索引、Hash索引、Full-text索引
• 按存储结构分：聚簇索引、非聚簇索引
• 按字段特性分：主键索引、唯一索引、普通索引、全文索引
• 按字段个数分：单例索引、联合索引

常见索引数据结构和区别

• 二叉树、红黑树、B树、B+数
• 区别：树的高度影响获取数据的性能(每一个树节点就是一次磁盘IO)

二叉树

特点：每一个节点最多有两个子节点，左小右大。在数据随机性情况下树杈越明显。

将以下表的id设置为索引
就是将id列按照二叉树的数据结构存储起来

如果数据是顺序一次进入
树的高度会很高(就是一个链表结构)，此时元素的查找效率就等于链表查询O(n)，数据检索效率将极为低下。

红黑树（平衡二叉树）

为了解决二叉树，顺序进入导致单链表的问题，引入了红黑树(平衡二叉树)

B树

B树的出现可以解决树高度的问题，之所以是B树，而并不是名称中 "xxx二叉树"，就是它不再限制一个父节点中只能有两个子节点，而是允许M个子节点(M>2)。不仅如此，B树的一个节点可以存储多个元素，相比较于前面那些二叉树又把树的高度给降低了

在B树中有一个 "阶"的概念，每个节点，最多有多少个子节点就是多少阶，一个三阶B树每个节点最多有两个元素。

• 比如下面的就是3阶B树。比如查询id为7的数据：
  • 7在4和8之间，拿到p2指针，指向磁盘页3
  • 7大于6拿到p2指针，指向7

相较于二叉树和红黑树，B树有了质的提示为了解决树的高度，1、对子节点数目不再限制；2、之前的二叉树和红黑树每个节点只能存储一个元素，B树可以存储多个元素，又大大降低了树的高度

B+树

对比B树可以看到最大的一个区别就是B+树的数据只能存放到叶子节点，非叶子节点只存储键值，而B树所有节点都可以存放数据，B+树的空间利用大大提高

B树和B+树的区别

针对B树的缺点：

• 1、空间利用率不高
  B+树在存储数据的时候，数据只存储到叶子节点，非叶子节点存储键值
• 2、范围查找慢
  将页子节点进行双向链表

如果一个表没有主键索引，那还会创建B+树嘛

聚簇索引和非聚簇索引(重要)

按物理存储分类：InnoDB的存储方式是聚集索引，MyLSam的存储方式是非聚簇索引

在数据结构上聚集索引，数据和索引放在一起。非聚集索引数据和索引是分开的，叶子节点只存放索引和指向数据的指针。所以在检索的时候聚集找到索引就找到了数据，非聚集索引找到索引还要再根据索引去找数据，效率比聚集索引慢

二级索引(重要)

二级索引也是一个聚集索引,不过叶子没有存储所有行数据的聚集索引，只存储索引和主键的值，不存储所有行的数据，非主键索引都是二级索引
在MySQL中，创建一张表时会默认为主键创建聚集索引，B+树将所有的数据组织起来，即数据就是索引所以在InnoDB里，主键索引也被称为聚集索引，索引的叶子节点存的是整行数据。而出了聚集索引以外的所有索引都称为"二级索引"，二级索引叶子节点的数据就是主键的值

回表(面试必问)

要说明白回表，得知道聚集索引，如果是用主键去where查询，因为主键索引是聚集索引，叶子节点包含行的所有数据，此时不回表也能查到。如果用非主键索引去查，比如一个表user id、name、age，id为主键索引执行sql：select *From user where name ='ddd'，设置name为索引，此时，name走索引，name索引叶子节点存放的是主键值id和name索引，此时一次查询找到了id和name，但是age没有，此时又拿着id主键索引去查age，这就是回表，也是为什么不推荐select*的原因，为了减少回表

覆盖索引

要查询的数据在索引中

索引下推

不使用索引下推：有多少条数据就回表多少次

索引下推就是，回表的时候批量查找，而不是一个一个查找

单例索引、组合索引(最左前缀原则)

最左前缀原则

根本原因就是联合索引在排序的时候也是从左到右进行排序的

单例索引和联合索引的各自优势

建一个联合索引(a,b,c)，实际上相当于建了(a),(a,b),(a,b,c)三个索引

联合索引的优势

1、建一个联合索引(a,b,c),实际相当于建了(a),(a,b),(a,b,c)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于大量数据的表，使用联合索引会大大的减少开销！

索引的优缺点