MySQL索引

一、索引

问：为什么要使用索引？

答：索引（Index）可以避免让我们全表扫描去查找数据，大幅提升检索效率。

问：什么样的信息能够成为索引

答：主键、唯一键等只要能让数据具有唯一区分性的字段都能成为索引

问：索引的数据结构

答：二叉查找树，B-Tree结构，B+Tree结构（MySQL），hash结构，以及bitmap等，其中MySQL不支持bitmap

1. 二叉查找树

二叉查找树规定父节点的左节点要小于父节点，右节点要大于父节点，查找的时间复杂度为O(logn)；平衡二叉树的每个节点的左子树和右子树的高度均不超过一。二叉查找树确实能够提高索引效率，但是在对节点进行增删改查之后，会打破树的平衡，转变为线性树，这样的树在时间复杂度方面会变为O(n)，不能满足查询效率的要求。

2. B-Tree

B-tree树的每个节点中包含了关键字和指向孩子节点的指针。

定义：

1.根节点至少包含两个孩子

2.树中每个结点最多有m个孩子结点（m>=2）；

3.除根结点外，其它结点至少有ceil(m/2)个孩子结点；

4.所有的叶结点都在同一层上。

        

3. B+Tree

B+Tree是B-Tree的变体，其基本定义与B-Tree相同，除了：

1. 非叶子节点的子树指针与关键字个数相同

2. 非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树

3. 非叶子节点节点只用来作为索引，数据全部保存在叶子节点中

4. 所有叶子节点均有一个链指针指向下一个叶子结点，方便做统计操作，即<、>、in等操作

 

B+Tree更适合用来做存储索引，原因：

1. B+Tree的磁盘读写代价更低，因为B+树的非叶子节点不存放数据，只有在叶子节点中才存放数据；

2. B+Tree的查询效率更加稳定，因为非叶子节点不存放数据，查询数据时必须走到叶子节点，所以每次查询的时间和效率都会稳定O(log(n));

3. B+Tree更利于对数据库的扫描，只需要遍历全部叶子节点，就可以得到全部关键字，对于范围查询有更高的性能;
 

                      

4. Hash索引

跟据hash运算，只需经过一次定位就可以找到所查数据的头。

缺点：

1. 仅仅能满足“=”，“in”，不能使用范围查询。因为比较的是计算之后的Hash值。

2. 无法被用来避免数据的排序操作,因为存放的是Hash值，Hash值的大小关系并不一定和 Hash 运算前的键值完全一样。

3. 不能利用部分索引键查询，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值。

4. 不能避免表扫描，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

5. 遇到大量Hash值相等的情况后性能并不一定就会比B树索引高。

5. BitMap位图索引

Bitmap位图，适用于字段值只是固定的几个，如男、女，颜色；便于高效统计。Oracle支持位图索引，数据结构类似B+树。锁很严重，可能因为某行修改都会锁。适合并发较少，统计较多的情况。，不是主流索引。

二、密集索引和稀疏索引的区别

密集索引文件中的每个搜索码值都对应一个索引值；

稀疏索引文件只为索引码的某些值建立索引项；

InnoDB：

1. 若一个主键定义，该主键为密集索引；

2. 若没有主键定义，该表第一个唯一非空索引为密集索引；

3. 不满足以上条件，innodb内部生成一个隐藏主键（密集索引）；

4. 非主键索引包含相关键位和其主键值，包含两次查找；

MyISAM的特点：

1. 表中所有的索引均为稀疏索引；

2. 表的记录和索引是单独存放的（Innodb中的表记录和索引是一起存放的），无论是主键索引还是辅助索引都是只查找一次，都通过索引中键对应的地址去表中查找到相应的记录；

密集索引采用B+树的存储方式，它的叶子节点中不仅保存键值，还包含当前键值所在记录行的其他键位的信息，也就是说它的每个叶子节点都存储着一条记录。一个表只能创建一个密集索引，通常将主键设置为密集索引。

稀疏索引也是采用B+树的存储方式，它的叶子节点只保存键位信息和对应行数据的地址，定位到叶子结点之后，通过叶子节点中的数据地址去获取表中的数据。

三、调优SQL

1. 根据慢日志定位到慢查询SQL

show variables like '%quer%';

 打开 slow_query_log，配置long_query_time;

set global slow_query_log = on;

show status like '%slow_queries%'; 显示本次慢查询数

2. 使用explain分析sql

在sql语句前加explain

extra 中出现以下2项意味着mysql根本不能是用索引，效率会受到重大影响，应尽可能进行优化。

extra项	说明
Using filesort	表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using tenporary	表示MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by

 

3. 修改sql或者尽量让sql走索引

四、联合索引的最左匹配原则

1. 最左匹配原则：MySQL会一直向右匹配直到遇到范围查询（<、>、between、like)就会停止匹配，比如a=3 and b = 4 and c >5 and d =6 如果建立（a,b,c,d)顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，a,b,d的顺序可以任意调整。

2. = 和in可以乱序，比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮助优化成索引可以是别的形式。

 

注意:

1、数据量小的表不需要建立索引，建立会增加额外的索引开销。

2、数据变更需要维护索引，因此更多的索引意味着更大的维护成本

3、更多的索引需要更多的存储空间