MySQL(3)---MySQL优化

一、单表、双表、三表优化

1、单表

   首先结论就是，range类型查询字段后面的索引全都无效

（1）建表

create table if not exists article(
    id int primary key auto_increment,
    author_id int not null,
    category_id int not null,
    views int not null,
    comments int not null,
    title varchar(255) not null,
    content text not null
  );
 insert into article values(null,1,1,1,1,'1','1');
 insert into article values(null,2,2,2,2,'2','2');
 insert into article values(null,1,1,3,3,'3','3');

（2）未创建索引查询

explain select  id,author_id,views from  article
 where category_id = 1
 and comments > 1
 order by views desc limit 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
  -- 总结上面出现的情况:type=all,产生了全表扫描, 并且出现了Using filesort,使用了外部的索引排序,所以优化是必须的

（3）创建category_id,comments,views复合索引

  create index ind_article_ccv on article(category_id,comments,views);
  
  --再次执行如下指令:
  explain select  id,author_id,views from  article
  where category_id = 1
  and comments > 1
 order by views desc limit 1;
+----+-------------+---------+-------+