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,'','');
 insert into article values(null,2,2,2,2,'','');
 insert into article values(null,1,1,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;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                 |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | article | range | ind_article_ccv | ind_article_ccv | 8       | NULL |    1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
--创建索引之后type=range, 但是Using filesort 依然存在.

索引创建了,为什么在排序的时候没有生效?

这是因为先排序category_id, 如果遇到相同的category_id,则再排序comments, 如果遇到相同的comments则再排序views,

当comments字段在联合索引处于中间位置时,因为comments>1条件是一个范围值,所以type=range,mysql无法再利用索引对后面的views部分进行检索.

即range类型查询字段后面的索引全都无效

(4)、创建category_id,views复合索引

   -- 删除上面创建的索引:
   drop index ind_article_ccv on article;
   -- 重新创建索引:
   create index ind_art_cb on article(category_id,views);
   -- 重新执行如下代码
   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 | ref  | ind_art_cb    | ind_art_cb | 4       | const |    2 | Using where |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
--完美！

2、两张表

记住一句话：左连接索引建在右表，右连接索引做在左表

select * from class left join book on class.card = book.card;--这是左连接，那么索引应该建在book表中的card（create index X on book(card);）

3、三张表

select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
 --这里就可以见两个索引，一个在book表，一个在phone表中
 -- 创建索引:
 create index Y on book(card);
 create index Z on phone(card);

二、经典案例分析

1、建表，插数据，建索引

--建表
 create table test3(
      id int primary key auto_increment,
        c1 char(10),
        c2 char(10),
        c3 char(10),
        c4 char(10),
        c5 char(10)
  );
--插入数据
 insert into test3 values(null,'a1','a2','a3','a4','a5');
 insert into test3 values(null,'b1','b2','b3','b4','b5');
 insert into test3 values(null,'c1','c2','c3','c4','c5');
 insert into test3 values(null,'d1','d2','d3','d4','d5');
 insert into test3 values(null,'e1','e2','e3','e4','e5');
--查看数据
select * from test3;
-- 创建索引
 create index ind_test3_c1234 on test3(c1,c2,c3,c4);
 show index from test3;

2、分析下面索引情况

 explain select * from test3 where c1='a1';
 explain select * from test3 where c1='a1' and c2='a2';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4' and c5='a5';
 --上面五条所以肯定都有用，而且越到下面越精准 -- 请执行如下问题SQL,分析会出现的问题
 --(1)
   explain select * from test3 where c1='a1' and c2='a2' and c4='a4' and c3='a3' ;
 --四个字段索引都有效 -- (2)
    explain select * from test3 where c4='a1' and c3='a2' and c2='a4' and c1='a3' ;
 --四个字段索引都有效 -- (3)
   explain select * from test3 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
 --c1、c2、c3索引有效， -- (4)
  explain select * from test3 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
 --四个字段索引都有效 -- (5)
   explain select * from test3 where c1='a1' and c2='a2' and c4='a4' order by c3;
 --c1、c2有效，c3仅对排序有效 -- (6)
   explain select * from test3 where c1='a1' and c2='a2' order by c3;
 --c1、c2有效，c3仅对排序有效 -- (7)
    explain select * from test3 where c1='a1' and c2='a2' order by c4;
 --c1、c2有效，因为中间缺少c3,所以索引排序无效。会报：Using filesort -- (8)
   explain select * from test3 where c1='a1' and c5='a5' order by c2,c3;
 --c1有效，但是c2,c3用于排序,无filesort --(9)
   explain select * from test3 where c1='a1' and c5='a5' order by c3,c2;
 -- 只用了c1这个字段索引,但是由于c3,c2顺序颠倒了,所以无法使用索引排序,出现filesort --(10)
   explain select * from test3 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
 --因为排序字段c2已经是一个常量了，所以对于后面c2排序其实没有啥意义，所以不会出现filesort --(11)
   explain select * from test3 where c1='a1' and c4='c4' group by c2,c3;
 --用到了c1字段索引,但用到c2、c3排序 --(12)
   explain select * from test3 where c1='a1' and c4='c4' group by c3,c2;
 -- 索引字段顺序不正确，出现了Using temporary; Using filesort

三、in和exists区别

1、 优化规则: 小表驱动大表,即小的数据集驱动大的数据集

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。 所以如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in。 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

    另外IN时不对NULL进行处理。

    -- 当A的数据集大于B的数据集时, in 优于 exists
  select * from A where id in (select id from B);
  --等价于:
      for select id from B
          for select * from A where A.id = B.id;  -- 当A的数据集小于B的数据集时, exists优于in
  select * from A where exists(select 1 from B where B.id = A.id);
  --等价于:
    for select * from A
         for select * from B where B.id = A.id

2、not in 和not exists

   如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

四、Order by 排序优化

1、单路排序、排序

    order by 子句,尽量使用index方式排序,避免使用Filesort方式排序，尽可能再索引列上完成排序操作,遵照索引建的最佳左前缀， 如果不在索引列上。filesort算法:双路排序,单路排序。

(1)双路排序: mysql4.1之前使用的双路排序,字面意思就是扫描两次磁盘,从而得到最终的数据,读取行指针和order by 列,对它们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出. 从磁盘取排序字段,在buffer进行排序,再从磁盘取其它字段

(2)单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存到内存中

2、结论:

(1)单路排序,优于双路排序
   (2)在sort_buffer中,单路要比双路占用更多的空间,因为单路是把所有的字段都取出来,有可能导致取出的数据总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp,多路合并),排完再取sort_buffer容量大小,再排…从而导致了多次IO. (本来想节省一次IO操作,反而导致了大量的IO操作,得不偿失)

3、优化策略

(1)增大sort_buffer_size参数设置
  (2)增大max_length_for_sort_data参数设置

4、why

(1)order by时,select * 时一个大忌,只查询需要的字段,这点非常重要。当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用单路排序,否则会用多路排序
       两种算法的数据都有可能超过sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
(2)尝试调高sort_buffer_size: 不管使用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去调高,因为这个参数是针对每个进程的
(3)尝试调高max_length_for_sort_data: 调高这个参数,会增加使用单路算法的概率,但是如果设置太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率

5、总结

--mysql有两种排序方式: 文件排序和扫描有序索引排序
  -- mysql能为排序和查询使用相同的索引  index abc(a,b,c)
  order by --能使用最左前缀
  order by a
  order by b
  order by a,b,c
 order by a desc,b desc,c desc --如果where使用索引的最前缀定义为常量,则order by能使用索引
 where a=const order by b,c
 where a=const and b = const order by c
 where a=const order by b,c
 where a=const and b > const order by b,c --不能使用索引进行排序
 order by a asc,b desc, c desc  /*排序顺序不一致*/
 where g=const order by b,c     /*丢失a索引*/
 where a=const order by c       /*丢失b索引*/
 where a=const order by a,d     /*d不是索引*/
 where a in(…) order by b,c     /*对于排序来说,in 相当于是范围查询*/
 ```

参考

1.索引优化

 想太多，做太少，中间的落差就是烦恼。想没有烦恼，要么别想，要么多做。上尉【16】