MySQL索引基础-容易被混淆的地方

MySQL索引基础-容易被混淆的地方

    本文来梳理一下MySQL索引中一些容易被混淆或者弄错的地方。

    1、主键

    在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表，在InnoDB引擎表中，每张表都有个主键（Primary Key）,如果在创建表时没有显示地定义主键， 则InnoDB存储引擎会按照如下方式选择或创建主键：

    1） 首先判断表中是否有非空的唯一索引（Unique NOT NULL）,如果有，则该列即为主键。

    2）如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

    当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

    为了方便测试，我们先创建一个表，再插入几条数据：

CREATE TABLE `z` (
  `a` INT NOT NULL,
  `b` INT NULL,
  `c` INT NOT NULL,
  `d` INT NOT NULL,
  UNIQUE KEY (`b`),
  UNIQUE KEY (`d`),
  UNIQUE KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入几条数据
INSERT INTO `z` (`a`,`b`,`c`,`d`) VALUES (1,2,3,4);
INSERT INTO `z` (`a`,`b`,`c`,`d`) VALUES (5,6,7,8);
INSERT INTO `z` (`a`,`b`,`c`,`d`) VALUES (9,10,11,12);

   由于没有显式地定义主键，因此会选择非空的唯一索引，可以通过下面的SQL语句判断表的主键值：

   _rowid 可以显示表的主键，因此通过上述查询可以找到表z的主键。此外，虽然c、d列都是非空唯一索引，都可以作为主键的候选。但是在定义的过程中，由于d列首先定义为唯一索引，故InnoDB存储引擎将其视为主键。

  另外需要注意的是：_rowid只能用于查看单个列为主键的情况，对于多列组成的主键就显得无能为力了。

CREATE TABLE `f` (
  `a` INT ,
  `b` INT ,
  PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO `f` (`a`,`b`) VALUES (1,1);

   执行上面的sql语句后，如下图：

  

      2、Cardinality值

      1） 什么是cardinality ?

      并不是所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。

      对于性别字段、地区字段、类型字段，他们可取值的范围很小，称为低选择性。按照性别进行查询时，可取值的范围一般只有 1 和 0 。因此上述SQL语句得到的结果可能是该表50%的数据（假设男女比例1:1），这时添加B+树索引是完全没有必要的。

      相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最合适的。例如，对于姓名字段（name），基本上在一个应用中不允许重复的出现。

      怎样查看索引是否是高选择性的呢？可以通过show index 结果中的列Cardinality 来观察。

      

      Cardinality 的值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。

      在实际应用中，cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引时非常有必要的。

      2）Cardinality 统计

      在 InnoDB 存储引擎中，cardinality 统计信息的更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时就去更新cardinality信息，

      这样会增加数据库系统的负荷，同时对于大表的统计，时间上也不允许数据库这样去操作。因此，InnoDB存储引擎内部对更新cardinality信息的策略为：

      1）表中1/16的数据已发生过变化

      2）stat_modified_counter>20 0000 0000

      3、explain 各参数含义以及作用

     

       1）id

       select识别符。这个是select查询序列号。查询序号即为sql语句执行的顺序。

       id和table这两个字段一起，可以完全判断出每一条SQL语句的执行顺序和表的查询顺序。

       2）select_type

       select_type主要有下面几个值：

       simple -它表示简单的select,没有union和子查询
       primary - 最外面的select,在有子查询的语句中，最外面的select查询就是primary
       union - union语句的第二个或者说是后面那一个.
       dependent union - UNION中的第二个或后面的SELECT语句，取决于外面的查询
       union result -UNION的结果

         3）table
       输出的行所用的表

        4）type

        显示连接使用了何种类型。从最好到最差的连接类型为：NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

        a.  stystem  表中只有一行记录

        b.  const  

        表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。

        简单来说，const是直接按主键或唯一键读取。

        c.  eq_ref

        用于联表查询的情况，按联表的主键或唯一键联合查询。多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

        d.  ref

        可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键。

        e.  ref_or_null

        ref_or_null 类似ref，但是可以搜索值为NULL的行。

        f.   index_merge

       表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

        g.   range  

        索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

        h.   index

•         当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；
•         以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；
•         如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；
•         如果单独出现，则是用读索引来代替读行，但不用于查找

     说明：all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。通常比all快，因为索引文件通常比数据文件小。

        i.   all  全表扫描

        5）possible_keys

       显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

       6）key

            实际使用到的索引，如果为NULL，则没有使用索引。

      7）key_len

      表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。在不损失精确性的情况下，长度越短越好。

     分析：varchar(N)，指的是N字符，无论存放的是数字、字母还是UTF8，都可以存放N个，与字符集无关。

     a. varchar使用额外的1~2字节来存储值的的长度，如果列的最大长度小于或者等于255，则用1字节，否则用2字节

     b. char和varchar跟字符编码也有密切的联系，latin1中1个字符占用1个字节，gbk中1个字符占用2个字节，utf8中1个字符占用3个字节。utf8mb4中,每个字符最多占4个字节(比如存储emoji表情)（不同字符编码占用的存储空间不同）

     c. 允许为null，占用1个字节

     d.  数值类型的基本知识

          如下图：

     

    总结一下，key_len的计算方法：

    通常情况下， key_len=字段字符数*字符集每个字符所占字节数，然后额外的情况是 varchar需要1-2个字节来存储字符串本身的长度，如果允许为null,还需要占用1个字节。

    下面举了几个栗子，如下图：

   

     注意：  int(m)  数字m表示字段的显示宽度，不足指定宽度，默认空格填充，不会影响实际存储字节数。如果使用ZEROFILL，就会用数字0取代空格来填充显示宽度不足的数字。

     8）ref

     显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

     9）rows

     MySQL 认为必须检查的用来返回请求数据的行数,数值越大越不好，说明没有用好索引。

     10）filtered

     这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

     11）extra

     包含不适合在其它列中显示，但十分重要的额外信息。

     a.  using index

     使用覆盖索引的时候就会出现，很优秀。

     说明：使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。

     b.  using where

     表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

     c.   using index condition

     这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

     查找使用了索引，但是需要回表查询数据，意味着查询列的某一个部分无法直接使用索引。

     d.  using index & using where

     查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

     e.  using filesort

     表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。

     f. using tempporary

    表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by

    说明：using temporary和 using filesort是最差的情况，意思mysql根本不能使用索引，结果是检索会很慢。

     4、回表

      要说回表，先要从InnoDB的索引实现说起。InnoDB有两大类索引，一类是聚集索引(Clustered Index)，一类是普通索引(Secondary Index)。

      1）InnoDB的聚集索引

      InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。

      2）InnoDB的普通索引

      InnoDB普通索引的叶子节点存储主键值(MyISAM则是存储的行记录头指针)。

      3）举个栗子说明回表查询：

      假设有个t表(id PK, name KEY, sex, flag)，这里的id是聚集索引，name则是普通索引。

      查询sql为：select * from t where name = 'lisi';

      查询过程分析：

      1）因为这里的普通索引name列，无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。第一遍先通过普通索引定位到主键值id=N，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

       2）为什么要避免回表查询？

       访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O

      由于索引对应的B+树会按照索引列的值进行排序，所以索列之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O。

      根据第一步中获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id(也就是主键)的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以成为随机I/O。

     一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行可能很快，而步骤二就要慢一些。因此，如果回表次数太多，就会非常影响性能。

     扩展：

     从磁盘结构的角度来进一步分析为什么顺序I/O性能优于随机I/O：

      

     我们知道:磁盘读取数据时，磁头通过盘面径向移动到磁道上，然后盘面旋转到目标扇区的时候开始读取数据，如果数据都存在相邻的扇区，相比于数据存储在不同的磁道的扇区上来讲，磁盘读取数据的效率就会高很多（不需要寻道时间，只需要很少的旋转时间)，这就是顺序I/O性能优于随机I/O的原因。

      5、索引下沉/推（ICP）

      Mysql5.6版本提出了索引下推（index condition pushdown ）的原则，「用于查询优化，主要是用于like关键字的查询的优化」。

      条件：联合二级索引

      在索引遍历过程中，通过索引中已有字段值过滤掉不满足条件的记录，减少回表的次数。

      1）在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

      2）在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

      索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

      下面来测试一下：

       

     根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

    总结：

     1）innodb引擎的表，索引下推只能用于二级索引。

     2）索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

     3）关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

　　set optimizer_switch='index_condition_pushdown=off';

     4） 如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

需要特别注意的是：

      1）存储引擎是基于表的，而不是数据库。

      2）B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读到内存，再在内存中进行查找，最后得到要查找的数据。

      3）不论查询语句有多复杂，包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL设计者规定explain语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

参考资料：姜承尧 《MySQL技术内幕 - InnoDB存储引擎第2版》