MySQl高性能的索引

MySQL逻辑架构

MySQL各组件之间协同工作的架构图

    最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如联机处理、授权认证、安全等等。
    第二层是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
    第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信。存储引擎不会去解析sql，不同的存储引擎之间也不会相互通信，只是简单的相应上层服务器的请求。

sql执行的五个阶段

    阶段一 ：客户端/服务端通信;
    阶段二 ：查询缓存;
    阶段三 ：查询优化;
    阶段四 ：查询执行引擎；
    阶段五 ：返回客户端结果集;

第二阶段，当mysql接收到一条查询语句时，会先去缓存进行查询，如果找到对应查询语句数据就直接把数据返回给客户端。
    1. 缓存命中要求：SQL语句完全匹配；
    2. 缓存什么时候失效：表被修改、缓存模满了之后剔除；
    3. 查看缓存状态：Show variables like ‘query_cathe%’;
    4. 开启/关闭缓存：set query_cache_type =ON/set query_cache_type =OFF;
    5. 查看缓存命中情况：Show status like ‘Qcache%’;

    第三阶段，MySQL在执行时，并不一定就会按照我们写的顺序执行，同时也不需要我们人为的从左到右的安排where后面的条件，MySQL优化器会重写sql。explain extended sql语句，然后show warnings查看。show warnings会看到优化器重写后的sql。

索引

    索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。
    索引对于良好的性能非常关键。尤其是当表中数据量越来越大时，索引对性能影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。
    索引优化应该是对查询性能优化最有效的手段了。

索引的类型

    索引有很多类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以没有统一的标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一类型的索引，其低层实现也可能不同。

B-Tree索引

谈论索引的时候，没有特别说明，多半就是B-Tree索引。
    B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根节点槽中存放了指向子节点的指针，存储引擎根据这些指针乡下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了自节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。叶子节点比较特别，他们指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。树的深度和表的大小有关。

建立在B-Tree结构上的索引如图：

    B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。适用于全键值、键值范围或键前缀查找。键前缀查找只适用于根据最左前缀的查找。

B-Tree索引的限制：

•     不是按照索引的最左列开始查找，则无法使用索引。
•     不能跳过索引中的列。
•     如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。

哈希索引（hash index）

    哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。索引自身只存储对应的哈希值，索引结构十分紧凑，所以查找速度非常快。
哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序，只支持等值比较查询。在不出现哈希冲突的前提下速度非常快，出现哈希冲突，存储引擎必须遍历链表中的所有指针，逐行进行比较，直到找到所有符合条件的行。

哈希索引的限制：

•      哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
•      哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
•      哈希索引不支持部分索引列的匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
•      哈希索引只支持等值比较查询，包括 =、 in（）、<=>。不支持范围查找。
•      速度非常快，除非有哈希冲突，会遍历链表。
•      哈希冲突多的话，维护索引代价很高。

    InnoDB引擎不支持hash索引，InnoDB引擎又一个特殊的功能叫做自适应哈希索引，是一个完全自动的，内部的行为，用户无法控制或者配置，如果没有必要，可以关闭该功能。也可以创建自定义哈希索引，模拟InnoDB创建哈希索引。

    在B-Tree基础上创建一个伪哈希锁索引。和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，是使用哈希值而不是键本身进行索引查找。需要做的就是在where字句中手动指定使用哈希函数（CRC32（））。这样做的缺点就是需要手动维护哈希值，也可以使用触发器实现。还必须在where条件中带入哈希值对应的列值，可以避免冲突。另外MySQL优化器会使用选择性高而体积小的索引列来完成查找。

索引的优点：

    1. 索引大大减少了服务器需要扫描的数据量。
    2. 索引可以帮助服务器避免排序和临时表。
    3. 索引可以将随机I/O变为顺序I/O。

索引的选择性 

    是指不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询的效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。对于很长的varchar类型的列创建前缀索引，就依据选择性高原则。

聚簇索引和非聚簇索引

    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

    图中是聚簇索引中的数据存放，注意到叶子节点包含了全部数据，节点页只包含了索引列。InnoDb将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有那样的索引，InnoDB会隐式的定义一个主见来作为聚簇索引。

聚簇索引的优点：

•     可以把数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样还要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
•     数据访问快。
•     使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

•     插入速度严重依赖于插入顺序。按照主键的顺序插入是最快的。如果不是按照主键的顺序加载，可以使用OPTIMIZE TABLE重新组织表。
•     更新聚簇索引代价很大。
•     基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。
•     聚簇索引可能导致全表扫描变慢。
•     二级索引（非聚簇索引）可能比想象的更大二级索引的叶子节点包含了引用行的主键列。
•     二级索引访问需要两次索引查找，而不是一次。
  ​​​​​​​

并不是所有的存储引擎都支持聚簇索引，下面针对InnoDB和MyISAM存储引擎比较一下 聚簇索引和非聚簇索引的区别，用下面的表来看一下。    

CREATE TABLE layout_test（
        col1 int not null，
        col2 int not null，
        PRIMARY KEY（col1），
        KEY（col2）
    ）

    该表的主键值（col1）为1~100000,但是按随机顺序插入并使用OPTIMIZE TABLE命令优化。列col2的值是1~100之间随机赋值，所以有很多重复的值

    layout_test表的数据分布图

    MyISAM表layout_test的主键分布 索引中每个叶子节点包含行号。

 

    MyISAM表layout_test的col2列索引分布 和主键一样包含了行号。

MyISAM逐渐索引和其他索引在结构上没什么区别，主键索引就是一个为PRIMARY的唯一非空索引。

    InnoDB表layout_test的主键分布，从图中看，该图显示了整张表，而不只是索引。因为在InnoDB中，聚簇索引就是“表”。聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。

    InnoDB表layout_test的二级索引分布 从图中可以看出，InnoDB的二级索引的叶子结点中存储的不是“指针行”，而是主键值，这样的好处是，InnoDB在移动行时无需更新二级索引的这个“指针”。上面说的二级索引查找要进行两次索引查找，就是因为这个。

聚簇和非聚簇索引的对比图

    InnoDB表中索引使用自增列AUTO INCREMENT，避免随机列。随机列新的主键值并不一定比之前插入的大，所以InnoDB无法简单地将新行插入到索引的最后，而是寻找合适的位置，通常是一有数据的中间，并分配空间，导致增加很多额外的工作，并导致数据分布并不够优化。但是可以使用OPTIMIZE TABLE来优化重建表。

索引覆盖

    一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“索引覆盖”。执行计划里Extra列为Using index即表示查询使用了索引覆盖。

慢查询

MySQL可以开启慢查询日志，记录系统中的慢查询。
1、查看慢查询日志开启状态 show variables like 'slow_query_log';
2、找到my.cnf，添加如下内容 slow_query_log        
    慢查询开启状态 slow_query_log_file  
    慢查询日志存放的位置 long_query_time        
    查询超过多少秒才记录
3、重启mysql

针对系统中的慢查询，可以对其进行优化，这里可以利用执行计划的帮助。

执行计划

explain 在查询语句前面加上explain，执行后会显示这条sql查询的详情。

关键字：
    select_type：    
        查询分类，用于区分普通查询(SIMPLE)、联合查询，子查询；
        1. SIMPLE(简单SELECT,不使用UNION或子查询等)
        2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
        3. UNION(UNION中的第二个或后面的SELECT语句)
        4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
        5. UNION RESULT(UNION的结果)
        6. SUBQUERY(子查询中的第一个SELECT)
        7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
        8. DERIVED(派生表的SELECT, FROM子句的子查询)
        9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
    table:
        查询所作用的表。
    type : 
        查询表所用的方式 ；
        常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
        1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
        2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树，使用了索引扫描来排序。
        3. range:只检索给定范围的行，使用一个索引来选择行
        4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
        5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
        6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
        7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
    possoble_key:
        指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
    key: 
        实际使用的索引。
    key_len: 
        索引的长度，不损失精确性的情况下，长度越短越好。
    ref: 
        表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 。
    row:
        扫描数据行数。
    Extra：
        该列包含MySQL解决查询的详细信息,有以下几种情况：
        1. Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
        2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
        3. Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
        4. Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
        5. Impossible where：这个值强调了where语句会导致没有符合条件的行。
        6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
        7. “Using index”索引覆盖查询和type 的“index”完全不一样。

转载于:https://my.oschina.net/u/4149810/blog/3081533