MySQL 单表访问

MySQL执行单表查询

  1.访问方法:

          Const

        MySQL直接利用主键值在聚簇索引中定位对应的用户记录.并且唯一的二级索引也可以在常数时间内定位到一条记录. 这种const访问方法只能在主键列或者唯一二级索引与一个常数进行等值比较才有效.

         ref

        普通的二级索引列与常数进行等值比较,会形成一个单点扫描区间.这种方法方法被称为ref.采用二级索引来执行查询时,如果无法使用索引下推,则每获取一条二级索引记录,就会立刻对其执行回表操作.

• 二级索引列允许存储NULL值,无论是普通的二级索引,还是唯一二级索引,它们的索引列并不限制NULL值的数量,所以在执行包含 "KEY IS NULL" 形式的搜索条件查询时,最多使用ref方法
• 对于索引列中包含多个列的二级索引来说,只要最左边连续的列是与常数进行等值比较就可以使用ref方法.

       ref_or_null

        如果想找出某个二级索引类的值等于某个常数的记录,而且还想把该列中的值为NULL的记录也找出来. 如: SELECT * from table where key1 = 1 OR key1 IS NULL .  则会生成两个扫描区间 [NULL , NULL] 以及 [1,1].只是比上面的多了一个[NULL , NULL]的扫描区间.  值为NULL的记录会被放在索引的最左边.

       range

        上面的三种访问方法都是索引列等值查询时的情况,当执行的是索引列非等值查询会生成非单点的扫描区间,这种访问方法被称为range.

        index

一种比较特殊的情况 如: 对(key1 , key2 , key3) 建一个联合索引 idx_key_part. 然后下面的查询语句:

        SELECT key1 , key2 , key3 from table where key2 = 'a'

        key2并不是联合索引idx_key_part. 最左侧的列,所以无法形成合适的扫描区间来减少需要扫描的记录数量.但是这个查询符合下面的两个条件:

• 它查询的列刚好是联合索引的三个列
• 搜索条件中的key2列也是联合索引中的列

        对于这种情况可以直接便利联合索引的所有二级索引记录,针对获取到的每一条二级索引记录,都判断key2 = 'a' 是否成立.如果成立,则读取 key1 , key3的值,发送给客户端.虽然它对应的扫描区间是(-∞,+∞) 但是,二级索引的记录只需要存放索引列和主键 , 记录要比聚簇索引小的多,而且这个过程是不需要回表操作的.

        对于上面这种情况,扫描全部二级索引记录的访问方法称为index方法.

   2.索引合并

        Intersection 索引合并

SELECT * from table where key1 = 'a' AND key3 = 'b' (key1 , key3均为索引列)

• 如果使用 key1列 , 则会生成 ['a','a']的扫描区间.对于获取到的每一条记录,根据主键 回表查询获取到完整的用户记录 , 在判断 key3 是否满足等于 'b'. 
• 只是用 key3列,和上面相同
• 可以同时使用key1 和 key3 两个索引列执行查询. 即 在key1的索引中扫描 key1值在['a','a']区间中的二级索引记录 ,  同时在key3的索引列中扫描key3值在['b','b']区间中的二级索引记录,然后从两者的操作中找出 主键 列值相同的记录,在根据这些共有的主键值,回表操作.

        Union 索引合并

        SELECT * from table where key1 = 'a' OR key3 = 'b' 

        对于上面的查询语句,无论是使用key1列的索引还是key3列的索引,对应的扫描区间都是 (-∞ , +∞) , 其每一条记录都需要回表操作.

        但是上面的查询也不会用到全表扫描,可以同时使用 key1的索引和key3列的索引来执行查询.即 在key1的索引中扫描 key1值在['a','a']区间中的二级索引记录 ,  同时在key3的索引列中扫描key3值在['b','b']区间中的二级索引记录,然后根据二级索引记录的id值在两者结果中去重,在根据去重后的id值执行回表操作,这样重复的id只需要回表一次.

         Sort-Union合并

        SELECT * from table where key1 < 'a' OR key3 > 'b'

 查询方法: 

1. 先根据 key1 < 'a' 从 key1的二级索引中获取二级索引记录,并将获取到的二级索引记录的主键值进行排序.
2. 在根据 key3 > 'b' 从 key3的二级索引中获取二级索引记录,并将获取到的二级索引记录的主键值进行排序.
3. 排好序之后的操作步骤 和 Union一样.