数据库【整理】

一、聚集索引与非聚集索引      

       

       索引就是二叉树，数据真实存储在叶子节点，非叶子节点存储的事引用。Mysql使用的事B+Tree

       聚集索引是包含所有列的物理存储连续，所以很庞大，新插入数据主要耗时在物理排序上面，所以相对较慢。非聚集索引只有当前列和主键列的数据，而且物理路径不连续，不用排序，所以插入较快。同理因为物理路径是否连续的问题，快慢也不尽相同。

        

      

 

 二、回表查询

        查询语句：select * from table where name = ‘小明’    【回表查询】

        因为name列虽然有索引，可以通过非聚集索引查询到id和name，但是因为使用了*，导致要回到聚集索引里面去查询到所有数据。这个过程叫做回表查询。           

三、部分索引和索引覆盖

       3.1、部分索引

                select id from table where name = ‘小明’ 

                其中name是非聚集索引列。

       3.2、索引覆盖

                select id，name from table where name = ‘小明’ 【覆盖查询，单列索引】

                只在索引里面就能查询，不用回表再次查询。

                select id，name，age from table where name = ‘小明’ 【用联合索引】【最左前缀原则】

四、索引下推

         4.1、基础原理                  

                

                【索引下推】：MySql 5.6 的新特性，简称ICP，Mysql默认开启。指将部分上层（服务层）负责的事情，交给下层（引擎层）去处理。

                【无ICP优化查询】：

                                              1、存储引擎读取索引记录；

                                              2、根据索引中的主键值，定位并读取完整的行记录；

                                              3、存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

                 【有ICP优化查询】：

                                               1、存储引擎读取索引记录（不是完整的行记录）；

                                               2、判断where条件能否用索引中额列来追检查，条件不满足，则处理下一行索引记录；

                                               3、条件满足，使用索引中的组件去定位并读取完整的行记录（就是所谓的回表）

                                               4、存储引擎把记录交给server层，server层检测该记录是否满足where条件的其余部分。

             4.2、实践理解

                     假设查询语句如下：

           mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。                

　　　　　　　　     

                                                                                            （图一）    

                             

                                                                                             （图二）

 

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

              4.3、使用条件

                       1、只能用于range、 ref、 eq_ref、ref_or_null访问方法；

                       2、只能用于InnoDB和 MyISAM存储引擎及其分区表；

                       3、对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）；索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说

                      4、引用了子查询的条件不能下推；

                      5、引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数；

              4.4、切换状态

                       set optimizer_switch="index_condition_pushdown=off"

                       set optimizer_switch="index_condition_pushdown=on"