MySQL数据库优化学习笔记---------单表优化 多表优化 避免索引失效原则

1.单表优化实例

首先创建数据表，并插入数据

create table book
(
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null 
);

insert into book values(1,'tjava',1,1,2) ;
insert into book values(2,'tc',2,1,2) ;
insert into book values(3,'wx',3,2,1) ;
insert into book values(4,'math',4,2,3) ;
commit;	

这是一个图书表，有bid（主键），name，authorid，publicid，typeid等字段

问题：查询authorid=1且 typeid为2或3的图书的bid

通常情况下，我们会写出这样一个sql语句：

select bid from book where typeid in(2,3) and authorid=1  

用explain执行，发现这个语句非常不好：

mysql> explain select bid from book where typeid in(2,3) and authorid=1  ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

因为没有使用索引，所以type=ALL，而且key的栏目都是null，还有using where字段。现在要优化，就得加上索引：

alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;

注意：这个索引的顺序是t a b，对应sql语句解析的顺序：where在前 select在后

• from … on… join …where …group by …having …select dinstinct …order by limit …

再次优化（之前是index级别）：思路。因为范围查询in有时会失效，因此交换 索引的顺序，将typeid in(2,3) 放到最后。此时索引顺序也改为 a t b

drop index idx_tab on book; //首先去掉刚才的索引
alter table book add index idx_atb (authorid,typeid,bid);
explain select bid from book where  authorid=1 and  typeid in(2,3) order by typeid desc ;

优化之后的结果：type变为ref 并且有using index提示 说明确实优化了

mysql> explain select bid from book where  authorid=1 and  typeid in(2,3) order by typeid desc ;
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | book  | ref  | idx_atb       | idx_atb | 4       | const |    2 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

总结：a.最佳左前缀，保持索引的定义和使用（SQL语句解析）的顺序一致性 b.索引需要逐步优化 c.将含In的范围查询 放到where条件的最后，防止失效。

本例中同时出现了Using where（需要回原表）; Using index（不需要回原表）：

• 原因，where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表（using where）；
• 例如以下没有了In，则不会出现using where

explain select bid from book where  authorid=1 and  typeid =3 order by typeid desc ;

mysql> explain select bid from book where  authorid=1 and  typeid =3 order by typeid desc ;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | book  | ref  | idx_atb       | idx_atb | 8       | const,const |    1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

2.多表优化实例

创建两张表 老师 和 课程

create table teacher2
(
	tid int(4) primary key,
	cid int(4) not null
);

insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2
(
	cid int(4) ,
	cname varchar(20)
);

insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'kotlin');
commit;

结构图如下

现在要查询课程为java的信息，左外连接查询

explain select *from teacher2 t left outer join course2 c
	on t.cid=c.cid where c.cname='java';

+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.01 sec)

问题：索引往哪张表加？

• 小表驱动大表：索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） 【一般情况对于左外连接，给左表加索引；右外连接，给右表加索引】
• 小表要放在等号左边：程序都是外层循环次数少，内层循环次数多

alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);

此时再次运行

+----+-------------+-------+------+---------------------+---------------------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref        | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+------------+------+-------------+
|  1 | SIMPLE      | c     | ref  | index_course2_cname | index_course2_cname | 23      | const      |    1 | Using where |
|  1 | SIMPLE      | t     | ref  | index_teacher2_cid  | index_teacher2_cid  | 4       | myDB.c.cid |    1 | Using index |
+----+-------------+-------+------+---------------------+---------------------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

发现type变为ref ， 并且有using index的提示

3.避免索引失效的原则

• 复合索引
  • a.复合索引，不要跨列或无序使用（最佳左前缀）
  • b.复合索引，尽量使用全索引匹配 尽量将索引都包括(a,b,c)
• 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效 注意：对于复合索引，如果左边失效，右侧全部失效。对于复合索引(a,b,c)，例如如果 b失效，则b c同时失效。
• 复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效。
• 我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 该结论不是100%正确。一般而言， 范围查询（> < in），之后的索引失效。
• 尽量使用索引覆盖（using index）
  （a,b,c）复合索引 select a,b,c from xx…where a= … and b =… ; 此查询是覆盖索引，必然提升性能
• like尽量以“常量”开头，不要以’%'开头，否则索引失效
• 尽量不要使用类型转换（显示、隐式），否则索引失效
• 尽量不要使用or，否则索引失效