当前位置: 首页>编程语言>正文

sql语句 增加索引 sql表加索引

先limit再join

SELECT a.prj_id,a.pre_doc_id,a.out_time, a.inactive_user, a.inactive_time,
  u.use_nm, GROUP_CONCAT(Distinct d.job_no) jb_nos,ifnull(sum(b.qty),0) pre_qty,
  ifnull(sum(c.qty),0) qty, GROUP_CONCAT(Distinct e.rq_id) rq_ids FROM prepare_doc a
  LEFT JOIN pre_doc_item b ON a.pre_doc_id=b.pre_doc_id
  LEFT JOIN assign_item c ON b.pre_item_id= c.pre_item_id
  LEFT JOIN out_job_item d ON c.job_item_id=d.job_item_id
  LEFT JOIN ref_rq_pre e ON a.pre_doc_id= e.pre_doc_id
  LEFT JOIN user u ON a.inactive_user= u.use_id where true AND a.prj_id ='1' AND a.inactive is null
 group by a.pre_doc_id order by a.out_time desc limit 0, 15

用了0.6s

select a.*, u.use_nm, GROUP_CONCAT(Distinct d.job_no) jb_nos,ifnull(sum(b.qty),0) pre_qty,
  ifnull(sum(c.qty),0) qty, GROUP_CONCAT(Distinct e.rq_id) rq_ids
 from (SELECT a.prj_id,a.pre_doc_id,a.out_time, a.inactive_user, a.inactive_time FROM prepare_doc a
 where true AND a.prj_id ='1' AND a.inactive is null order by a.out_time desc limit 0, 15) a
  LEFT JOIN pre_doc_item b ON a.pre_doc_id=b.pre_doc_id
  LEFT JOIN assign_item c ON b.pre_item_id= c.pre_item_id
  LEFT JOIN out_job_item d ON c.job_item_id=d.job_item_id
  LEFT JOIN ref_rq_pre e ON a.pre_doc_id= e.pre_doc_id
 LEFT JOIN user u ON a.inactive_user= u.use_id group by a.pre_doc_id  order by a.out_time desc

用了0.07s性能差10倍

添加索引优化器更高效率地执行语句 

假设我们有两个数据表t1和t2,每个有1000行,包含的值从1到1000。下面的查询查找出两个表中值相同的数据行:

mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
 +------+------+
 | i1 | i2 |
 +------+------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 3 |
 | 4 | 4 |
 | 5 | 5 |
 ...

  两个表都没有索引的时候,EXPLAIN产生下面的结果:

mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra:
 *************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: t2
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra: Using whe

 类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引(键、键长度和参考列都是NULL也是因为缺少合适的索引)。Using where表明使用WHERE子句中的信息来识别合格的数据行。
  这段信息告诉我们,优化器没有为提高执行查询的效率找到任何有用的信息:
  · 它将对t1表进行全表扫描。

  · 对于t1中的每一行,它将执行t2的全表扫描,使用WHERE子句中的信息识别出合格的行。
  行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000,因为 1000可以完成全表扫描。相似地,t2的估计值也是1000,但是这个值是对于t1的每一行的。换句话说,优化器所估计的处理该查询所需要检查的数据行 组合的数量是1000×1000,也就是一百万。这会造成很大的浪费

为了使这个查询的效率更高,给其中一个联结列添加索引并重新执行EXPLAIN语句:

mysql> ALTER TABLE t2 ADD INDEX (i2);
 mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1000
 Extra:
 *************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: t2
 type: ref
 possible_keys: i2
 key: i2
 key_len: 5
 ref: sampdb.t1.i1
 rows: 10
 Extra: Using where; Using index

  我们可以看到性能提高了。T1的输出没有改变(表明还是需要进行全表扫描),但是优化器处理t2的方式就有所不同了:
  · 类型从ALL改变为ref,意味着可以使用参考值(来自t1的值)来执行索引查找,定位t2中合格的数据行。
  · 参考值在参考(ref)字段中给出了:sampdb.t1.i1。
  · 行数值从1000降低到了10,显示出优化器相信对于t1中的每一行,它只需要检查t2中的10行(这是一个悲观的估计值。实际上,在t2中只有一行与 t1中数据行匹配。我们在后面会看到如何帮助优化器改善这个估计值)。数据行组合的全部估计值使1000×10=10000。它比前面的没有索引的时候估计出来的一百万好多了。

  对t1进行索引有价值吗?实际上,对于这个特定的联结操作,扫描一张表是必要的,因此没有必要对t1建立索引。如果你想看到效果,可以索引t1.i1并再次运行EXPLAIN:

mysql> ALTER TABLE t1 ADD INDEX (i1);
 mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
 possible_keys: i1
 key: i1
 key_len: 5
 ref: NULL
 rows: 1000
 Extra: Using index
 *************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: t2
 type: ref
 possible_keys: i2
 key: i2
 key_len: 5
 ref: sampdb.t1.i1
 rows: 10
 Extra: Using where; Using index

  上面的输出与前面的EXPLAIN的输出相似,但是添加索引对t1的输出有一些改变。类型从NULL改成了index,附加(Extra)从空的改成了 Using index。这些改变表明,尽管对索引的值仍然需要执行全表扫描,但是优化器还是可以直接从索引文件中读取值,根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果,在这种情况下,优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果,在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。

我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值:

mysql> ANALYZE TABLE t1, t2;
 mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
 possible_keys: i1
 key: i1
 key_len: 5
 ref: NULL
 rows: 1000
 Extra: Using index
 *************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: t2
 type: ref
 possible_keys: i2
 key: i2
 key_len: 5
 ref: sampdb.t1.i1
 rows: 1
 Extra: Using where; Using inde

在这种情况下,优化器估计在t2中与t1的每个值匹配的数据行只有一个。

 

mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B where A . catid = B . id ;
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
 | id | select_type | table | type    | possible_keys | key      | key_len | ref                  | rows   | Extra        |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
 |  1 | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                 | 46585 |             |
 |  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid |      1 | Using where |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
2 rows in set ( 0.00 sec )

这个是我们经常使用的一种查询方式,对B表的联接类型使用了eq_ref,索引使用了PRIMARY,但是对于A表,却没有使用任何索引,这可能不是我们想要的。
查看以上SQL语句,我们可能会想到,有必要给A.catid加个索引了。



mysql 
    > 
    alter 
    table 
    jos_content 
    add 
    index 
    idx_catid 
    ( 
    ` 
    catid 
    ` 
    ) 
    ;
 
    Query 
    OK 
    , 
    46585 
    rows 
    affected 
    ( 
    0.75 
    sec 
    ) 
    
 
    Records 
    : 
    46585 
      
    Duplicates 
    : 
    0 
      
    Warnings 
    : 
    0 
    
  
 
    mysql 
    > 
    explain 
    select 
    A 
    . 
    id 
    , 
    A 
    . 
    title 
    , 
    B 
    . 
    title 
    from 
    jos_content 
    A 
    , 
    jos_categories 
    B 
    where 
    A 
    . 
    catid 
    = 
    B 
    . 
    id 
    ;
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
 | 
    id 
    | 
    select_type 
    | 
    table 
    | 
    type 
       | 
    possible_keys 
    | 
    key 
         | 
    key_len 
    | 
    ref 
                     | 
    rows 
      | 
    Extra 
           |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
 |  
    1 
    | 
    SIMPLE 
          | 
    A 
         | 
    ALL 
        | 
    idx_catid 
         | 
    NULL 
        | 
    NULL 
        | 
    NULL 
                    | 
    46585 
    |             |
 |  
    1 
    | 
    SIMPLE 
          | 
    B 
         | 
    eq_ref 
    | 
    PRIMARY 
           | 
    PRIMARY 
    | 
    4 
           | 
    joomla_test 
    . 
    A 
    . 
    catid 
    |      
    1 
    | 
    Using 
    where 
    |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
 
    2 
    rows 
    in 
    set 
    ( 
    0.00 
    sec 
    )



这样表A便使用了idx_catid索引。下面我们做一次三个表的联合查询



mysql 
      > 
      explain 
      select 
      A 
      . 
      id 
      , 
      A 
      . 
      title 
      , 
      B 
      . 
      title 
      from 
      jos_content 
      A 
      , 
      jos_categories 
      B 
      , 
      jos_sections 
      C 
      where 
      A 
      . 
      catid 
      = 
      B 
      . 
      id 
      and 
      A 
      . 
      sectionid 
      = 
      C 
      . 
      id 
      ;
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
 | 
      id 
      | 
      select_type 
      | 
      table 
      | 
      type 
         | 
      possible_keys 
      | 
      key 
           | 
      key_len 
      | 
      ref 
                       | 
      rows 
        | 
      Extra 
                                |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
 |  
      1 
      | 
      SIMPLE 
            | 
      C 
           | 
      index 
        | 
      PRIMARY 
             | 
      PRIMARY 
      | 
      4 
             | 
      NULL 
                      |      
      2 
      | 
      Using 
      index 
                          |
 |  
      1 
      | 
      SIMPLE 
            | 
      A 
           | 
      ALL 
          | 
      idx_catid 
           | 
      NULL 
          | 
      NULL 
          | 
      NULL 
                      | 
      46585 
      | 
      Using 
      where 
      ; 
      Using 
      join 
      buffer 
      |
 |  
      1 
      | 
      SIMPLE 
            | 
      B 
           | 
      eq_ref 
      | 
      PRIMARY 
             | 
      PRIMARY 
      | 
      4 
             | 
      joomla_test 
      . 
      A 
      . 
      catid 
      |      
      1 
      | 
      Using 
      where 
                          |
 +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
 
      3 
      rows 
      in 
      set 
      ( 
      0.00 
      sec 
      )



这里显示了Mysql先将C表读入查询,并使用PRIMARY索引,然后联合A表进行查询,这时候type显示的是ALL,可以用的索引有idx_catid,但是实际没有用。
原因非常明显,因为使用的连接条件是A.sectionid=C.id,所以我们给A.sectionid加个索引先。



mysql 
      > 
      alter 
      table 
      jos_content 
      add 
      index 
      idx_section 
      ( 
      ` 
      sectionid 
      ` 
      ) 
      ;
 
      Query 
      OK 
      , 
      46585 
      rows 
      affected 
      ( 
      0.89 
      sec 
      ) 
      
 
      Records 
      : 
      46585 
        
      Duplicates 
      : 
      0 
        
      Warnings 
      : 
      0 
      
  
 
      mysql 
      > 
      explain 
      select 
      A 
      . 
      id 
      , 
      A 
      . 
      title 
      , 
      B 
      . 
      title 
      from 
      jos_content 
      A 
      , 
      jos_categories 
      B 
      , 
      jos_sections 
      C 
      where 
      A 
      . 
      catid 
      = 
      B 
      . 
      id 
      and 
      A 
      . 
      sectionid 
      = 
      C 
      . 
      id 
      ;
 +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
 | 
      id 
      | 
      select_type 
      | 
      table 
      | 
      type 
         | 
      possible_keys 
               | 
      key 
               | 
      key_len 
      | 
      ref 
                       | 
      rows 
        | 
      Extra 
             |
 +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
 |  
      1 
      | 
      SIMPLE 
            | 
      C 
           | 
      index 
        | 
      PRIMARY 
                     | 
      PRIMARY 
           | 
      4 
             | 
      NULL 
                      |      
      2 
      | 
      Using 
      index 
      |
 |  
      1 
      | 
      SIMPLE 
            | 
      A 
           | 
      ref 
          | 
      idx_catid 
      , 
      idx_section 
      | 
      idx_section 
      | 
      4 
             | 
      joomla_test 
      . 
      C 
      . 
      id 
          | 
      23293 
      | 
      Using 
      where 
      |
 |  
      1 
      | 
      SIMPLE 
            | 
      B 
           | 
      eq_ref 
      | 
      PRIMARY 
                     | 
      PRIMARY 
           | 
      4 
             | 
      joomla_test 
      . 
      A 
      . 
      catid 
      |      
      1 
      | 
      Using 
      where 
      |
 +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
 
      3 
      rows 
      in 
      set 
      ( 
      0.00 
      sec 
      )



这时候显示结果告诉我们,效果很明显,在连接A表时type变成了ref,索引使用了idx_section,如果我们注意看后两列,对A表的查询结果后一次明显少了一半左右,而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的,可以得知,用记录数少的表做为基础表进行联合,将会得到更高的效率。



对于上面的语句,我们换一种写法



mysql 
        > 
        explain 
        select 
        A 
        . 
        id 
        , 
        A 
        . 
        title 
        , 
        B 
        . 
        title 
        from 
        jos_content 
        A 
        left 
        join 
        jos_categories 
        B 
        on 
        A 
        . 
        catid 
        = 
        B 
        . 
        id 
        left 
        join 
        jos_sections 
        C 
        on 
        A 
        . 
        sectionid 
        = 
        C 
        . 
        id 
        ;
 +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
 | 
        id 
        | 
        select_type 
        | 
        table 
        | 
        type 
           | 
        possible_keys 
        | 
        key 
             | 
        key_len 
        | 
        ref 
                             | 
        rows 
          | 
        Extra 
               |
 +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
 |  
        1 
        | 
        SIMPLE 
              | 
        A 
             | 
        ALL 
            | 
        NULL 
                  | 
        NULL 
            | 
        NULL 
            | 
        NULL 
                            | 
        46585 
        |             |
 |  
        1 
        | 
        SIMPLE 
              | 
        B 
             | 
        eq_ref 
        | 
        PRIMARY 
               | 
        PRIMARY 
        | 
        4 
               | 
        joomla_test 
        . 
        A 
        . 
        catid 
             |      
        1 
        |             |
 |  
        1 
        | 
        SIMPLE 
              | 
        C 
             | 
        eq_ref 
        | 
        PRIMARY 
               | 
        PRIMARY 
        | 
        4 
               | 
        joomla_test 
        . 
        A 
        . 
        sectionid 
        |      
        1 
        | 
        Using 
        index 
        |
 +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
 
        3 
        rows 
        in 
        set 
        ( 
        0.00 
        sec 
        )



Mysql读入表的顺序被改变了,这意味着,如果我们用left join来做连接查询,Mysql会按SQL语句中表出现的顺序读入,还有一个有变化的地方是联接B和C的type都变成了eq_ref,前边我们说过,这样说明Mysql可以找到唯一的行,这个效率是比ref要高的。


https://www.xamrdz.com/lan/54w1962192.html

相关文章: