http://www.web008.net

蒙面索引

怎么叫做覆盖索引?

  • 批注生龙活虎: 正是select的数据列只用从索引中就可见赢得,不必从数量表中读取,换句话说查询列要被所使用的目录覆盖。
  • 解释二: 索引是便捷找到行的叁个办法,当能经过检索索引就足以读取想要的多寡,这就无需再到数据表中读取行了。假若三个索引包括了(或蒙蔽了)满足查询语句中字段与规范的数量就称为覆盖索引。
  • 表明三:是非聚焦组合索引的风流浪漫种样式,它总结在询问里的Select、Join和Where子句用到的具备列(即创设目录的字段正巧是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包涵了询问正在查究的持有数据)。

  不是享有品种的目录都能够成为隐瞒索引。覆盖索引必定要存款和储蓄索引的列,而哈希索引、空间引得和全文索引等都不存款和储蓄索引列的值,所以MySQL只可以动用B-Tree索引做覆盖索引

  当发起二个被索引覆盖的询问(也叫作索引覆盖查询卡塔尔国时,在EXPLAIN的Extra列可以观看“Using index”的音信

  图片 1

二种优化场景:

  1.无WHERE条件的询问优化:

图片 2

  试行安排中,type 为ALL,表示实行了全表扫描

  怎么着改进?优化措施很简短,正是对这些查询列创设目录。如下,

ALERT TABLE t1 ADD KEY(staff_id);

 

  •  再看一下实施安顿

    explain select sql_no_cache count(staff_id) from t1G *** 1. row ***

           id: 1
    

    select_type: SIMPLE

        table: t1
         type: index
    

    possible_keys: NULL

          key: staff_id
      key_len: 1
          ref: NULL
         rows: 1023849
    

          Extra: Using index

1 row in set (0.00 sec)

 

  possible_key: NULL,表明未有WHERE条件时查询优化器不可能通过索引检索数据,这里运用了目录的其余一个优点,即从目录中获取数据,减弱了读取的数据块的数码。 无where条件的询问,能够通过索引来完结索引覆盖查询,但前提条件是,查询重返的字段数丰硕少,更不用说select *等等的了。究竟,建构key length过长的目录,始终不是生机勃勃件好事情。

  • 询问消耗

  图片 3

  从岁月上看,小了0.13 sec

2、二次搜索优化

  如下这些查询:

select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)

 

  推行安顿:

explain select sql_no_cache rental_date from t1 where inventory_id<80000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id
          key: inventory_id
      key_len: 3
          ref: NULL
         rows: 153734
        Extra: Using index condition
1 row in set (0.00 sec)

 

  Extra:Using index condition 表示使用的目录情势为二级检索,即799玖拾玖个书签值被用来扩充回表查询。总之,依然会有必然的性质消耗的

  尝试针对那些SQL创设一起索引,如下:

alter table t1 add key(inventory_id,rental_date);

 

  施行陈设:

explain select sql_no_cache rental_date from t1 where inventory_id<80000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id,inventory_id_2
          key: inventory_id_2
      key_len: 3
          ref: NULL
         rows: 162884
        Extra: Using index
1 row in set (0.00 sec)

 

  Extra:Using index 代表还没会标查询的历程,完结了目录覆盖

3、分页查询优化

  如下这些查询现象

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.75 sec)

 

  在未优化此前,我们看来它的实行安顿是那般的不佳

  

explain select tid,return_date from t1 order by inventory_id limit 50000,10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1023675

1 row in set (0.00 sec)

  看出是全表扫描。加上而外的排序,品质消耗是不低的

  怎样通过覆盖索引优化呢?

  大家创设一个索引,包罗排系列以致重返列,由于tid是主键字段,由此,下边包车型大巴复合索引就隐含了tid的字段值

alter table t1 add index liu(inventory_id,return_date);

 

  那么,效果怎么着呢?

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.03 sec)

 

  可以发掘,增加复合索引后,速度进步0.7s!

  大家看一下更进一竿后的实行安排

explain select tid,return_date from t1 order by inventory_id limit 50000,10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: liu
      key_len: 9
          ref: NULL
         rows: 50010

    Extra: Using index

1 row in set (0.00 sec)

   实践布置也得以看出,使用到了复合索引,况且无需回表

  相比一下之类的改写SQL,观念是因而索引消释排序

select a.tid,a.return_date from  t1 a 
inner join 
(select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;

 

  并在这里幼功上,大家为inventory_id列创立索引,并删除在此以前的覆盖索引

alter table t1 add index idx_inid(inventory_id);
drop index liu;

 

  然后访谈总结新闻。

select a.tid,a.return_date from  t1 a inner join  (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;
+--------+---------------------+
| tid    | return_date         |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+

 

  这种优化花招较前面四个时间多消耗了大约140ms。这种优化手腕纵然应用索引清除了排序,但是还是要透过主键值回表查询。由此,在select再次回到列很少或列宽极小的时候,咱们得以通过建构复合索引的点子优化分页查询,效果更佳,因为它不供给回表!

参考文献:

[1] 袋鼠云技艺团队博客,

[2] Baron Schwartz等 著,宁海元等 译 ;《高品质MySQL》(第3版); 电子工业出版社 ,2011

郑重声明:本文版权归美高梅163888所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。