阿里云SQL优化挑战赛实战-190毫秒干到2毫秒

使用一个多表查询的例子：

首先创建表：

  

   

    

     

    
    

     

      CREATE DATABASE IF 
      NOT 
      EXISTS mysql_test_bruce 
      DEFAULT 
      CHARACTER 
      SET UTF8;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      CREATE 
      TABLE `a`(
     
    
   

    

     

    
    

     

        `id` 
      INT(
      11) 
      NOT 
      NULL AUTO_INCREMENT,
     
    
   

    

     

    
    

     

        `seller_id` 
      BIGINT(
      20) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `seller_name` 
      VARCHAR(
      100) 
      CHARACTER 
      SET UTF8 
      COLLATE UTF8_BIN 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `gmt_create` 
      VARCHAR(
      30) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     
  
      PRIMARY KEY (`id`)
     
    
   

    

     

    
    

     

      ) ENGINE
      =INNODB AUTO_INCREMENT
      =
      0 
      DEFAULT CHARSET
      =UTF8;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      CREATE 
      TABLE `b`(
     
    
   

    

     

    
    

     

        `id` 
      INT(
      11) 
      NOT 
      NULL AUTO_INCREMENT,
     
    
   

    

     

    
    

     

        `seller_name` 
      VARCHAR(
      100) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `user_id` 
      VARCHAR(
      50) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `user_name` 
      VARCHAR(
      50) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `sales` 
      BIGINT(
      20) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `gmt_create` 
      VARCHAR(
      30) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     
  
      PRIMARY KEY (`id`)
     
    
   

    

     

    
    

     

      ) ENGINE
      =INNODB AUTO_INCREMENT
      =
      0 
      DEFAULT CHARSET
      =UTF8;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      CREATE 
      TABLE `c`(
     
    
   

    

     

    
    

     

        `id` 
      INT(
      11) 
      NOT 
      NULL AUTO_INCREMENT,
     
    
   

    

     

    
    

     

        `user_id` 
      VARCHAR(
      50) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `order_id` 
      VARCHAR(
      100) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `state` 
      BIGINT(
      20) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     

        `gmt_create` 
      VARCHAR(
      30) 
      DEFAULT 
      NULL,
     
    
   

    

     

    
    

     
  
      PRIMARY KEY (`id`)
     
    
   

    

     

    
    

     

      ) ENGINE
      =INNODB AUTO_INCREMENT
      =
      0 
      DEFAULT CHARSET
      =UTF8;
     
    
  
 

  
 

查询语句如下：

explain SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create; 

 查询花费了190ms

查看查询计划，发现都是用了全表扫描，显然是需要优化的。 

怎样优化呢？

1.既然是关联查询，那么需要先找到驱动表，小表驱动大表

对3张表分别进行查询，查看那张表得到的数据最少，将其做为驱动表

从结果来看，a和c表都可以作为驱动表，但是因为要使用a表的gmt_create作为查询条件，如果从索引的利用率来说，使用a表作为驱动表更合适

2.建立索引 

查询中没有用到索引，所以这里需要先建立索引

查询语句：

SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create;

为了优化排序，给a表的gmt_create字段建立索引

b表的seller_name字段因为要与a表进行关联，所以是必不可少需要建立索引的

c表的user_id字段因为要与b表进行关联，所以也是要建立索引的

再次查询：

可以看到C表使用了user_id索引，但是a、b两表却没有用到索引，还是全表扫描，这是怎么回事呢？

再加一个参数extended去查看更加详细的执行计划：

通过上面执行计划的分析，发现问题如下：

主要是索引字段在使用的时候发生了隐式转换

采用如下解决方案：

再次执行sql语句：索引失效的问题被解决。

 以上就是一个完整的sql优化的思路，这种过程很少有人总结，希望能够对大家有帮助。