mysql调优二-索引优化

时间:2021-1-18 作者:admin

mysql调优-索引优化

文章目录


一、前置:索引相关知识

1、索引用途/优点/分类

        索引优点:
    1>、大大减少了服务器需要扫描的数据量。
    2>、帮助服务器避免排序和临时表。
    3>、将随机io变成顺序io。

        索引用途:
    1>、快速查找匹配WHERE子句的行。
    2>、如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引。
    3>、如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行。
    4>、当有表连接的时候,从其他表检索行数据。
    5>、查找特定索引列的min或max值。
    6>、如果排序或分组时在可用索引的最左前缀上完成的,则对表进行排序和分组。
    7>、在某些情况下,可以优化查询以检索值而无需查询数据行。

        索引分类:
    主键索引、唯一索引、普通索引、全文索引、组合索引

2、索引过程现象

        回表:当前使用的索引为普通索引,查询的列为非索引列,定位数据需要两步,首先通过普通索引定位到主键值,再根据主键值通过主键索引(聚集索引)来定位对应行记录。出现回表现象会降低查询效率,因为进行了两次索引扫描。
·
        覆盖索引:查询的字段为 主键 + 已建立索引的字段 时触发(where条件中通过建立索引列的字段查询)。因为在普通索引的date中存的是 主键id 和 该字段值。举例说明,表user表中id列为主键索引,name列为普通索引,那么select id,name from user where name=‘zhangsan’; 查询会触发覆盖索引。
·
        最左匹配:前置条件 使用索引为 组合索引。在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合,在建立组合索引的时候查询最频繁的条件要放在左边。
·
        索引下推:在5.6后出的索引下推,之前使用非主键索引进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器判断数据是否符合条件;
   推出索引下推后,存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
   索引下推可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

3、索引数据结构

索引具体的数据结构依据存储引擎的不同而不同

        Memory存储引擎:
   该存储引擎是基于内存的,采用的是hash表索引格式。利用hash存储需要将所有的数据文件添加到内存中,比较耗费内存空间。如果查询为等值查询,hash方式是极快的。

        Mysam/Innodb存储引擎:
   这两类存储引擎的索引数据结构都是B+树结构,B+树是在B树的基础上做的一种优化,主要变化:1>、B+树每个节点可以包含更多的节点,可以降低树的高度,将数据范围变为多个区间,区间越多,数据检索越快。2>、非叶子节点存储key,叶子节点存储key和数据。3>、叶子节点两两指针相互连接,顺序查询性能高。mysql调优二-索引优化
   Mysam存储引擎的索引是非聚簇索引,其索引文件和数据文件是分离的。data值存储的是该主键所对应的列的物理地址,再通过该地址查到对应的行数据。
   Innodb存储引擎的索引是一种聚簇索引,索引文件与数据文件不分离,数据文件本身就是索引文件,data域中完整的保存了所有的数据信息。

4、索引匹配方式

举例说明:user表建立组合索引 name,phone,age
      全值匹配:和索引中的所有列进行匹配 –explain select * from user where name = ‘zhangsan’ and phone = ‘13333333333’ and age = ‘22’;
      最左前缀匹配:只匹配前面的几列 –explain select * from user where name = ‘zhangsan’ and phone = ‘13333333333’; explain select * from user where name = ‘zhangsan’;
      匹配列前缀:匹配某一列的值的开头部分 –explain select * from user where name like ‘zhang%’;
      匹配范围值:查找某一个范围的数据 –explain select * from user where name > ‘zhangsan’;
      精确匹配某一列并范围匹配另外一列:查询第一列的全部和第二列的部分 –explain select * from user where name = ‘zhansgan’ and phone > ‘13333333333’;
      只访问索引的查询:相当于覆盖索引。

二、hash/(非)聚簇/覆盖 索引说明

1.哈希索引

   特点:
   1>、基于哈希表的实现,只有精确匹配索引所有列的查询才有效;也不支持任何范围查询;
   2>、在mysql中,只有memory的存储引擎显式支持哈希索引;
   3>、哈希索引自身只需存储对应的hash值,所以索引的结构十分紧凑,所以哈希索引查找的速度非常快;
   4>、哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引中的值来避免读取行;
   5>、哈希索引数据并不是按照索引值顺序存储的,所以无法进行排序;
   6>、哈希索引不支持部分列匹配查找,哈希索引是使用索引列的全部内容来计算哈希值;
   7>、访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行;

   应用场景:
   存储较多URL,需要根据URL的查找比较多,可以将url使用CRC32做哈希,可以使用使用体积很小的索引来完成查找;

2.(非)聚簇索引

   聚簇索引:
   是一种数据存储方式,指的是数据行跟相邻的键值紧凑的存储在一起。
   优点:把相关数据保存在一起;数据访问更快,因为索引和数据保存在同一个树中;使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
   缺点:1>、聚簇数据最大限度地提高了IO密集型应用的性能,如果数据全部在内存,那么聚簇索引就没有什么优势。
              2>、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式。
              3>、更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置。
              4>、基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题。
              5>、聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
   非聚簇索引:数据文件跟索引文件分开存放。

3.覆盖索引

   在上面已经介绍过,需要注意memory是不支持覆盖索引的。
   特点:
   1>、索引条目通常远小于数据行大小,如果只需要读取索引,那么mysql就会极大的较少数据访问量。
   2>、因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。
   3>、一些存储引擎如MYISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用,这可能会导致严重的性能问题。
   4>、由于INNODB的聚簇索引,覆盖索引对INNODB表特别有用。

三、索引优化

1、具体优化小细节

   1>、当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层。
   2>、尽量使用主键查询,而不是其他索引,因为主键查询不会触发回表查询。
   3>、使用前缀索引。如果某字段较长那么使用该列开始的部分字符串来作为索引 –alter table 表名 add key(字段名(6));
   4>、使用索引扫描来排序。尽可能使用同一个索引既满足排序,又用于查找行。当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方式都一样时,mysql才能够使用索引来对结果进行排序(需要满足最左前缀的要求)。
   5>、union all,in,or都能够使用索引,推荐使用in。
   6>、范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列。(<、<=、>、>=、between)
   7>、强制类型转换会全表扫描
   8>、更新十分频繁,数据区分度不高的字段上不宜建立索引。原因是更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。
   9>、创建索引的列,不允许为null,可能会得到不符合预期的结果。
   10>、当需要进行表连接的时候,最好不要超过三张表,需要join的字段,数据类型必须一致。
   11>、能使用limit的时候尽量使用limit
   12>、单表索引建议控制在5个以内,对于组合索引的索引字段数不允许超过5个

总结

索引的优化时针对表及相关查询操作的,不需要过早优化。在索引优化时要结合执行计划来看,这点尤为重要。

声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。