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

http://www.jianshu.com/p/7f2b41d6687d
SQL如下：
CREATE TABLEIF 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_idFROM    articleWHERE    catrgory_id = 1AND comments > 1ORDER BY    views DESCLIMIT 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
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。