深入分解 MySQL 索引和 SQL 调优拭魅战

MySQL索引

MySQL支持诸多存储引擎，而各类存储引擎对索引的支持也各不沟通，因此MySQL数据库支持多种索引范例，如BTree索引，哈希索引，全文索引等等。

为了停止紊乱，本文将只存眷于BTree索引，由于这是泛泛行使MySQL时首要打交道的索引。

MySQL官方对索引的界说为：索引(Index)是辅佐MySQL高效获取数据的数据布局。提取句子骨干，就可以获得索引的本质：索引是数据布局。

MySQL索引道理

索引目标

索引的目标在于进步查询服从，可以类比字典，假如要查“mysql”这个单词，我们必定必要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

假如没有索引，那么你也许必要把全部单词看一遍才气找到你想要的，假如我想找到m开头的单词呢?可能ze开头的单词呢?是不是认为假如没有索引，这个工作基础无法完成?

咱们去图书馆借书也是一样，假如你要借某一本书，必然是先找到对应的分类科目，再找到对应的编号，这是糊口中活生生的例子，通用索引，可以加速查询速率，快速定位。

索引道理

全部索引道理都是一样的，通过不绝的缩小想要得到数据的范畴来筛选出最终想要的功效，同时把随机的变乱酿成次序的变乱，也就是我们老是通过统一种查找方法来锁定命据。

数据库也是一样，但显然要伟大很多，由于不只面对着等值查询，尚有范畴查询(>、<、between)、恍惚查询(like)、并集查询(or)、多值匹配(in【in本质上属于多个or】)等等。数据库应该选择怎么样的方法来应对全部的题目呢?

我们追念字典的例子，能不能把数据分成段，然后分段查询呢?

最简朴的假如1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……

这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

但假如是1万万的记录呢，分成几段较量好?

稍有算法基本的同窗会想到搜刮树，其均匀伟大度是lgN，具有不错的查询机能。

但这里我们忽略了一个要害的题目，伟大度模子是基于每次沟通的操纵成原来思量的，数据库实现较量伟大，数据生涯在磁盘上，而为了进步机能，每次又可以把部门数据读入内存来计较，由于我们知道会见磁盘的本钱或许是会见内存的十万倍阁下，以是简朴的搜刮树难以满意伟大的应用场景。

索引布局

任何一种数据布局都不是凭空发生的，必然会有它的配景和行使场景，我们此刻总结一下，我们必要这种数据布局可以或许做些什么

着实很简朴，那就是Ｔ媚课查找数据时把磁盘IO次数节制在一个很小的数目级，最好是常数数目级。那么我们就想到假如一个高度可控的多路搜刮树是否能满意需求呢?

就这样，b+树应运而生。

b+树的索引布局表明

浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包括几个数据项(深蓝色所示)和指针(黄色所示)

如磁盘块1包括数据项17和35，包括指针P1、P2、P3，P1暗示小于17的磁盘块，P2暗示在17和35之间的磁盘块，P3暗示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜刮偏向的数据项，如17、35并不真实存在于数据表中。

b+树的查找进程

如图所示，假如要查找数据项29，那么起首会把磁盘块1由磁盘加载到内存，此时产生一次IO，在内存顶用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时刻由于很是短(对比磁盘的IO)可以忽略不计

通过磁盘块1的P2指针的磁盘地点把磁盘块3由磁盘加载到内存，产生第二次IO，29在26和30之间，锁定磁盘块3的P2指针

通过指针加载磁盘块8到内存，产生第三次IO，同时内存中做二分查找找到29，竣事查询，总计三次IO。

真实的环境是，3层的b+树可以暗示上百万的数据，假如上百万的数据查找只必要三次IO，机能进步将是庞大的

假如没有索引，每个数据项都要产生一次IO，那么总共必要百万次的IO，显然本钱很是很是高。

b+树性子

1、通过上面的说明，我们知道间越小，数据项的数目越多，树的高度越低。

这就是为什么每个数据项，即索引字段要只管的小，好比int占4字节，要比bigint8字节少一半。

这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度降落，导致树增高。当数据项便是1时将会退化成线性表。

2、当b+树的数据项是复合的数据布局，好比(name,age,sex)的时辰，b+数是凭证从左到右的次序来成立搜刮树的，好比当(张三,20,F)这样的数据来检索的时辰，b+树会优先较量name来确定下一步的所搜偏向，假如name沟通再依次较量age和sex，最后获得检索的数据

但当(20,F)这样的没有name的数据来的时辰，b+树就不知道下一步该查哪个节点，由于成立搜刮树的时辰name就是第一个较量因子，必必要先按照name来搜刮才气知道下一步去那边查询。

好比当(张三,F)这样的数据来检索时，b+树可以用name来指定搜刮偏向，但下一个字段age的缺失，以是只能把名字便是张三的数据都找到，然后再匹配性别是F的数据了

这个长短常重要的性子，即索引的最左匹配特征。

MySQL 索引实现

在MySQL中，索引属于存储引擎级此外面念，差异存储引擎对索引的实现方法是差异的，本文首要接头MyISAM和InnoDB两个存储引擎的索引实现方法。

MyISAM索引实现

MyISAM引擎行使B+Tree作为索引布局，叶节点的data域存放的是数据记录的地点。

下图是MyISAM索引的道理图：

这里设表一共有三列，假设我们以Col1为主键，则上图即是一个MyISAM表的主索引(Primary key)表示图。

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!