DSLBQ'S_BLOG

作者： dslbq

  • MySql优化

    MySql优化

    索引是什么

    • 索引是帮助 MySql高效获取数据的数据结构
    • 索引存储在文件系统中
    • 索引的文件存储形式与存储引擎有关

    为什么要设计索引

    为了加快数据的访问，提高我们程序的效率，例如查字典，根据字典的编码就能定位到文字的位置，系统的数据可能成百万上千万，如何快速的定位到数据，此时就需要用到索引。

    索引文件结构

    Hash

    • 需要设置一个较好的哈希算法来保证数据的均匀存储（扰动函数）
    • 利用Hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间
    • 如果等值查询是比较快，但如果范围查询，需要一个个比较，效率就比较低

    二叉树

    • 无论是二叉树还是红黑树，都会因为树的深度过深而造成IO次数变多，影响数据读取效率

    B树

    • B树特点
      • 所有键值分布在整颗树中
      • 搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
      • 每个节点最多拥有m个子树
      • 根节点至少有2个子树
      • 分支节点至少拥有m/2颗子树（除根节点和叶子节点外，其余都是分支节点）
      • 所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列
    • 实例图说明：每个节点占用一个磁盘块（页），一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址，两个关键字划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为16和34，P1指针指向的子树的数据范围为小于16，P2指针指向的子树的数据范围为16到34，P3指针指向的子树的数据范围为大于34
    • 查找关键字【28】过程
      • 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
      • 比较关键字28在区间（16，34）找到磁盘块1的指针P2
      • 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
      • 比较关键字28在区间（25，31）找到磁盘块8的指针P2
      • 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
    • 主键和数据一起存储，假设页大小为16K，一行数据为1K，3层深度只能存储4096条数据

    B+树

    • B+树是在B树的基础之上做的一种优化，变化如下：
      • B+树每个节点可以包含更多的节点，这样做的原因有两个：第一，为了降低树的高度，第二，将数据范围变为多个区间，区间越多，数据检索越快
      • 非叶子节点存储主键，叶子节点存储主键+数据，假设页大小为16K，主键为10字节，一行数据为1K，3层深度能存储的数据为4096万笔
      • 叶子节点两两指针相互连接（符合磁盘的预计特性），顺序查询性能更高

    索引分类

    • 主键索引：当给一张表创建主键索引的时候，在组织数据时一定会按主键来创建，关系到数据的组织形式
    • 唯一索引：如果给一个列创建了唯一索引，意味着这个列不允许有重复值出现
    • 普通索引：二级索引或辅助索引，给除了主键和唯一列之外创建的索引，就是普通索引
    • 全文索引：较少使用，见相关文章，一般使用ES
    • 联合索引：多个非主键和唯一列之外创建的索引，就是联合索引

    存储引擎

    不同的数据文件在磁盘里不同的组织形式

    InnoDB,MyISAM使用的是B+树，MEMORY使用Hash,

    InnoDB支持自适应Hash（用户没办法进行干预）

    局部性原理

    程序和数据访问都有聚集成群的倾向，在一个时间段内，仅使用其中一小部分（称空间局部性），或者最近访问过的程序代码和数据，很快又被访问的可能性很大（称时间局部性）

    磁盘预读

    预读的长度一般为页（page）的整倍数，页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每一个存储块称为一页（在许多操作系统中，页的大小通常为4K），主存和磁盘以页为单位交换数据。

    名词解释

    • 聚簇索引：如果表设置了主键，则主键就是聚簇索引，如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引，如果都没有，则会默认创建一个隐藏的row_id作为聚簇索引
    • 普通索引：也叫二级索引，非聚簇索引，InnoDB的普通索引叶子节点存储的是主键（聚簇索引）值，而MyISAM的普通索引存储的是记录指针
    • 回表查询：先通过普通索引的值定位到聚簇索引值，再通过聚簇索引的值定位到记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低
    • 索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，通常使用联合索引的方式包含所需信息来实现索引覆盖
    • 最左匹配：在MySql中建立联合索引会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，例如创建一个(name,age)的联合索引，实际上MYSQL会建立一个nameage的普通索引，此时如果查询age的数据，并不会走联合索引，因为此索引的最左前缀为name
    • 索引下推：索引条件下推优化（Index Condition Pushdown）ICP是MySql5.6添加的，用于优化数据查询：
      • 不使用索引条件下推优化时，存储引擎通过索引检索到数据时，然后返回给MySql服务，服务再判断筛选符合条件的数据
      • 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySql服务会将这一部判断条件传递给存储引擎，然后由存储引擎判断索引是否符合MySql服务传递的条件，当索引符合条件时，直接将筛选完成的数据返回给MySql服务，MySql服务不需要再进行筛选
      • 由于筛选下放到磁盘，所以需要磁盘有更高的读取效率，索引下推的优点为减少IO量，减少内存占用
    • MRR：Multi Range Reader：主要用于在使用二级索引访问数据时减少随机读，启用了MRR优化后，MySQL首先会基于索引进行数据定位并收集满足条件的主键，然后对这些主键进行排序，这样可以以主键的顺序进行表行的读取，能够减少随机读的数量，MRR优化的目的就是通过对主键进行排序后的一定程度的顺序读，减少随机读的数量（如果可以基于索引得出查询结果，则不会进行MRR优化）。见相关文章
    • FIC: 快速索引创建（Fast Index Creation） 在添加或删除二级索引时，可以不用复制原表，而是在创建或删除二级索引的时候对原表加上一个S锁，允许其他会话操作，然后根据当前表数据创建索引，新索引创建完成后，解除S锁，允许写操作。见相关文章
    • 优化器：CBO：基于成本的优化，会优化SQL语句，使执行成本降至最低。RBO：基于规则的优化

    索引长度计算

    • 所有的索引字段，如果没有设置NOT NULL， 则需要加一个字节
    • 定长字段，int占4个字节，date占3个字节，char(n)占n个字符
    • 对于变长字段varchar(n)，则有n个字符 + 两个字节
    • 不同字符集，一个字符数占用的字节数不同，latin1编码的，一个字符占1个字节，gbk编码的，一个字符占2个字节，utf8编码的，一个字符占3个字节

    索引匹配方式

    全值匹配

    和索引中的所有的列进行匹配

    explain select * from staffs where name='张三' and age=23 and pos ='dev'

    匹配最左前缀

    只匹配前面的几列，最左优先，以最左边的为起点任何连续的索引都能匹配上，同时遇到范围查询（>,<,between,like）就会停止匹配。

    例如：如果查询条件b = 2 建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and a = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

    最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理,我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

    例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

    可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1 and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

    匹配列前缀

    可以匹配某一列值的开头部分

    --使用索引： 

explain select * from staffs where name like 'j%';
​
--索引失效： 
explain select * from staffs where name like '%j%';

    匹配范围值

    可以查找某一范围的值

    explain select * from staffs where name >'Mary'

    精确匹配某一列并范围匹配另外一列

    可以查询第一列的全部和第二列的部分

    explain select * from staffs where name='张三' and age > 23

    只访问索引的查询

    查询的时候只需要访问索引，不需要访问数据行，本质上就是索引覆盖

    explain select name，age，pos from staffs where name='张三' and age=23 and pos ='dev'