索引案例一：单表优化案例

 
 

  
  
http://www.jianshu.com/p/7f2b41d6687d

  
  
SQL如下：

  
  
CREATE TABLE
IF NOT EXISTS article (
    id INT (10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    author_id INT (10) UNSIGNED NOT NULL,
    catrgory_id INT (10) UNSIGNED NOT NULL,
    views INT (10) UNSIGNED NOT NULL,
    comments INT (10) UNSIGNED NOT NULL,
    title VARBINARY (255) NOT NULL,
    content TEXT NOT NULL
);

INSERT INTO `test`.`article` (
    `author_id`,
    `catrgory_id`,
    `views`,
    `comments`,
    `title`,
    `content`
)
VALUES
(
        1,
        1,
        1,
        1,
        '1',
        '1'
    ),
(
        2,
        2,
        2,
        2,
        '2',
        '2'
    ),  
(
        1,
        1,
        3,
        3,
        '3',
        '3'
    );

  
  
说明：这里只有三条数据，这是一个新闻表，会有百万级别的数据。
  
  
问题：
查询catrgory_id为1且comments大于1的情况下，views最多的article_id
  
  
SQL如下
  
  
EXPLAIN SELECT
    id,
    author_id
FROM
    article
WHERE
    catrgory_id = 1
AND comments > 1
ORDER BY
    views DESC
LIMIT 1;

  
  

  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  

  
  结论：很显然type是ALL，即最坏的情况。Extra里居然还出现了Using filesort，也是最坏的情况，数据量大的情况必须优化。
  
  
开始优化
  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  

  
  
创建完上步骤索引，再次explain，如下结果：
  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  
为什么还出现Using filesort？显然不可接受。原理如下：
  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  
显然索引创建的不合适，需要删除重新建立
  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  
为什么不建立comments索引直接跳过了呢？
因为经过上面分析可知comments是个范围值(>、<、in、between...and等)，mysql无法利用索引在对后面的views部分进行检索，即range类型查询字段后面的索引无效，因为是按照BTREE索引工作原理来排序的，先排序catrgory_id，在排序comments，comments匹配多个值，索引直接卡壳了，无法继续后面的了。
  
  

   
   
   
   

   
   

    
    Paste_Image.png
   
   
  
  
  
  
若有兴趣，欢迎来加入群，【Java初学者学习交流群】：458430385，此群有Java开发人员、UI设计人员和前端工程师。有问必答，共同探讨学习，一起进步！
欢迎关注我的微信公众号【Java码农社区】，会定时推送各种干货：
 
 

作者：编程界的小学生
链接：http://www.jianshu.com/p/7f2b41d6687d
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。