mysql调优二-索引优化

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>、叶子节点两两指针相互连接，顺序查询性能高。
   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个

总结

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