MySQL原理及优化（二）MySQL索引深入剖析

MySQL索引深入剖析

什么是索引

索引的定义

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。

索引的类型

• 普通索引（Normal）：也叫非唯一索引，是最普通的索引，没有任何限制。
• 唯一索引（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特使的唯一索引，它还多了一个限制条件，要求键值不能为空。
• 全文索引（FullText）：针对比较大的数据，比如存放的是消息内容。如果要解决查询like查询效率低的问题，可以创建全文索引。只有文本类型的字段可以创建全文索引。

全文索引的使用：

使用全文索引的格式：  MATCH (columnName) AGAINST ('string')
           SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
           当查询多列数据时：
                建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。
          SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

索引存储模型

平衡二叉树（AVL Tree）

由于二叉查找树可能会出现斜树的情况，因此不考虑，我们从平衡二叉树开始推演。
AVL Tree 可以通过左旋、右旋保证左右子数的深度差不能超过1。
AVL Tree中的结点应该存储三块内容：
1. 索引的键值。比如我们在id上面创建一个索引，我们用where Id = 1 的条件查询的时候就会找到索引里面的id的这个键值。
2. 数据的磁盘地址，因为索引的作用就是去查找数据的存放地址。
3. 子节点的引用。

当我们用数的结构来存储索引的时候，访问一个节点就要跟磁盘之间发一次IO。InnoDB操作磁盘的嘴角单位是页，大小是16k（16384字节）。那么一个树节点就是16k大小。
如果一个节点只存储一个键值+数据+引用，可能只使用了很少的字节，所以访问一个树节点，进行一次IO的时候浪费了大量的空间。

解决的方案：

1. 让每个节点存储更多的数据。
2. 节点上的关键字的数量越多，我们的指针数也就越多，有就是意味着可以有更多的分叉（路数）。

多路平衡查找树（B Tree）

B Tree 在节点中存储键值、数据地址、节点引用。它有一个特点：分叉书（路数）永远比关键字数多1。
B Tree通过分裂合并的方法在添加节点的时候保持平衡，所以我们不要在频繁更新的列上建索引。（节点的分裂合并，其实就是InnoDB页的分裂和合并）

B+ Tree

B+ Tree是MySQL在B Tree上进行改良的。拥有以下特点：

1. 它的关键字的数量跟路数相等。
2. B+ Tree的根节点和枝节点都不会存储数据，只用叶节点才存储数据。搜索到关键字不会直接返回，回到最后一层的叶节点。
3. B+ Tree的每个叶子节点增加了指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成一个有序链表的结构。
4. 它是根据左闭右开的区间[)检索的。

总结：

• B+ 树是B Tree的变种，BTree能解决的问题，B+树都能解决。BTree解决的两个问题：每个节点存储更多的关键字；路数更多。
• 扫表、扫库能力更强（如果要多表进行全表扫描，只需要遍历叶子节点即可，不需要遍历整棵树拿到所有的数据）
• B+ 数的磁盘读写能力更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
• 排序能力更强（因为叶子节点上友下一个区域的指针，数据形成链表）
• 效率更加稳定（B+ Tree永远是在叶子节点拿到数据，所以IO次数稳定）

B+ Tree落地形式，MySQL存储文件

MySQL的数据都是以文件的形式存放在磁盘中。InnoDB的表有两个文件（.frm和.ibd），MyISAM的表有三个文件（.frm、.MYD和.MYI）

MyISAM文件

• .MYD：是MyISAM的数据文件，存放数据记录。
• .MYI：是MyISAM的索引文件，存放索引。

MyISAM的索引和数据是两个独立的文件。MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从sy文件.MYI中找到键值后，回到数据文件.MYD中获取响应的数据记录。

MyISAM的辅助索引跟主键索引和检索数据的方式没有任何区别，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

InnoDB文件

在InnoDB只有一个文件(.ibd文件)，在InnoDB里，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件。
在InnoDB的主键索引的叶子结点上，直接存储了数据。

聚集索引（聚簇索引）：索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按照拼音排序的，内容也是按照拼音排序的，按照拼音排序的这种目录就叫做聚集索引）

在InnoDB里面，它组织数据的方式叫做（聚集）索引组织表，所以主键索引是聚集索引，非主键都是非聚集索引。

在InnoDB中，主键索引和辅助索引存在主次之分。辅助索引存储的是辅助索引和主键值，如果使用辅助索引，会根据主键值在主键索引中查询，最终取得数据。

索引的使用原则

列的离散度

离散度公式：count(distinct(column_name)):count(*)
列的全部不同值和所有数据行的比例。
建立索引，要使用离散较高的字段。

联合索引最左匹配

在多条件查询的时候，会创建联合索引。联合索引在B+ Tree中是复合的数据结构，按照从左到右的顺序来建立搜索数。
在创建联合索引的时候，把最常用的列放在最左边。

覆盖索引

非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫了一个搜索数，这个过程叫回表。
在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，就不必从数据区中读取，这个时候使用的索引叫做覆盖索引。这样可以避免回表。

索引条件下推（ICP）

建立联合索引：

alter table employees add index idx_lastname_firstname(last_name,first_name);

关闭ICP：

set optimizer_switch='index_condition_pushdown=off

现在查询姓wang，摒弃最后一个字值zi的员工：

select * from employees where last_name='wang' and first_name LIKE '%zi' ;

这条SQL有两种执行方式：

1. 根据连个索引查询出所有姓wang的二级索引数据，回到主键索引上查询全部符合条件的数据。然后返回给server层，在server层过滤出名字以zi结尾的员工。
2. 根据连个索引查出所有姓wang 的二级索引数据，然后从二级索引中筛出first_name以zi结尾的索引，然后回表，到之间索引上查询全部符合条件的数据，返回给server层。

注意：索引的鼻尖是在存储引擎中进行的，数据的比较是在Server层进行的。

当first_name的条件不能用于索引过滤是，server层不会把first_name的条件传递给存储引擎，所以读取了没有必要的数据。

执行SQL：using where，代表从存储引擎取回的数据不全部满足条件，需要在server层过滤。
先用last_name条件进行索引范围扫面，读取数据表记录，然后进行比较，检查是否符合first_name like '%zi’的条件。

开启ICP

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划：using index condition，
把first_name like '%zi’下推给存储引擎后，只会从数据表读取所需要的记录。
索引条件下推只适用于二级索引。

索引的创建和使用

索引的创建

1. 用于where判断order排序和join的（on）字段上创建索引。
2. 索引的个数不要太多（浪费空间，更新变慢）
3. 区分度低的字段，不要建索引（离散度太低，导致扫描行数过多）
4. 频繁更新的值，不要作为主键或者索引（页分裂）
5. 组合索引把散列性高（区分度高）的值放在前面。
6. 创建符合索引，而不是修改单列索引。
7. 过长的字段，建立全文索引。

什么时候用不到索引

1. 索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）
2. 字符串不加引号，出现隐式转换。
3. like条件中带有%
4. 负向查询