MySQL之索引原理与慢查询优化

一 介绍

二 索引的原理

三 索引的数据结构

四 聚集索引与辅助索引

五 MySQL索引管理

六 测试索引

七 正确使用索引

八 联合索引与覆盖索引

九 查询优化神器-explain

十 慢查询优化的基本步骤

十一 慢日志管理

B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项（深蓝色所示，一个磁盘块里面包含多少数据，一个深蓝色的块表示一个数据，其实不是数据，后面有解释）和指针（黄色所示，看最上面一个，p1表示比上面深蓝色的那个17小的数据的位置在哪，看它指针指向的左边那个块，里面的数据都比17小，p2指向的是比17大比35小的磁盘块），如磁盘块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.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h或者说层级，这个高度或者层级就是你每次查询数据的IO次数，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

　　　　　　比如：你每个叶子节点只存两个数据的情况下，你如果想多加两个数据，你怎么办

　　　　　　　　所以我们需要将树建的越低越好，因为每个磁盘块的大小是一定的，那么意味着我们单个数据库里面的单个数据的大小越大越好还是越小越好，你想啊，你现在叶子节点的磁盘块，两个数据就沾满了，你数据要是更大的话，你这一个磁盘块就只能放一个数据了亲，这样随着你数据量的增大，你的树就越高啊，我们应该想办法让树的层数低下来，效率才高啊，所以我们应该让每个数据的大小尽可能的小，那就意味着，你每个磁盘块存的数据就越多，你树的层级就越少啊，树就越低啊，对不对。并且数据的数量越大，你需要的磁盘块越多，磁盘块越多，你需要的树的层级就越高，所以我们应该尽可能的用更少的磁盘块来装更多的数据项，这样树的高度才能降下来，怎么才能装更多的数据项啊，当然是你的数据项越小，你的磁盘块盛放的数据量就越多了，所以如果一张表中有很多的字段，我们应该用什么字段来建立索引啊，如果你有id字段、name字段、描述信息字段等等的，你应该用哪个来建立索引啊，当然是id字段了，你想想对不对，因为id是个数字，占用空间最少啊。

　　　　2.索引的最左匹配特性：简单来说就是你的数据来了以后，从数据块的左边开始匹配，在匹配右边的，知道这句话就行啦~~~~，我们继续学下面的内容。当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

　　关于explain，如果大家有兴趣，可以看看这篇博客，他总结的挺好的：http://www.cnblogs.com/yycc/p/7338894.html

执行计划：让mysql预估执行操作(一般正确)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email
    
    慢：
        select * from userinfo3 where name='alex'
        
        explain select * from userinfo3 where name='alex'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

　　http://blog.itpub.net/29773961/viewspace-1767044/

回到顶部