SQL优化 - 案例（单表、两表、三表）+ 特殊优化

转载

mob604756fb13b1 2021-06-25 23:39:00

文章标签 mysql 字段数据 java 子查询 文章分类 MySQL 数据库

单表优化

【数据准备】

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;	
mysql> SELECT *
    -> FROM book;
+-----+-------+----------+----------+--------+
| bid | name  | authorid | publicid | typeid |
+-----+-------+----------+----------+--------+
|   1 | tjava |        1 |        1 |      2 |
|   2 | tc    |        2 |        1 |      2 |
|   3 | wx    |        3 |        2 |      1 |
|   4 | math  |        4 |        2 |      3 |
+-----+-------+----------+----------+--------+

查询authorid = 1 且 typeid为2或3的bid

mysql> EXPLAIN SELECT bid
    -> FROM book
    -> WHERE typeid IN(2,3)
    -> AND authorid = 1
    -> ORDER BY typeid DESC;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
-- using filesort 说明当前SQL语句性能消耗大、需要在进行一次额外的排序

优化：加索引

ALTER TABLE book AND INDEX idx_bta(bid,typeid,authorid);
mysql> EXPLAIN SELECT bid
    -> FROM book
    -> WHERE typeid IN(2,3) AND authorid = 1
    -> ORDER BY typeid DESC;
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | book  | index | NULL          | idx_bta | 12      | NULL |    4 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
-- 性能上没有改观

【进一步优化按照SQL语句的执行顺序】

SELECT DISTINCT... FROM ... JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
FROM ... ON ... JOIN ... WHERE ... GROUP BY ... HAVING ... SELECT ... ORDER BY ... LIMIT
故先对typeid检索之后是authorid 最后是bid 即 "tab" 的顺序

-- 【索引一旦需要升级优化，需要将之前废弃的索引删除掉，防止干扰】
DROP INDEX idx_bta ON book;
-- 【建立新的索引】
ALTER TABLE book ADD INDEX idx_tab(typeid, authorid, bid);
-- 【根据新建立的索引执行查询并分析】  
mysql> EXPLAIN SELECT bid
    -> FROM book
    -> WHERE typeid IN(2,3) AND authorid = 1
    -> ORDER BY typeid DESC;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | book  | index | idx_tab       | idx_tab | 12      | NULL |    4 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
-- 【VERY GOOD ???????????????????????????????? ┗|｀O′|┛ 嗷~~】 去掉了using filesort而且实现了覆盖索引
-- 【????： 进一步优化，因为WHERE 子句的IN【会导致后面的索引失效】所以要调节一下索引的顺序 IN放在最后面】

-- 删除之前的索引并创建优化后的索引
mysql> DROP INDEX idx_tab ON book;
mysql> ALTER TABLE book ADD INDEX idx_atb(authorid, typeid, bid);
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.01 sec)

To sum up：
a. 最左做前缀，保持索引的定义和使用的顺序一致性
b. 索引需要逐步优化
c. 将含IN的范围查询放到WHERE条件的最后，防止失效

上述例子中存在 using where -> 即需要authorid回表查询; 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

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)

两表优化

数据准备

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;

左连接

mysql> 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.00 sec)

Using join buffer:extra中的一个选项，作用：Mysql引擎使用了连接缓存。

索引该往哪张表加? ===> (小表 - 驱动 -> 大表) 索引被建立在哪些经常使用的字段上

[一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]

	小表：10
	大表：300
	where   小表.x 10 = 大表.y 300;  --循环了几次？10
		
		大表.y 300=小表.x 10	--循环了300次


	小表:10
	大表:300

	select ...where 小表.x10=大表.x300 ;
	for(int i=0;i<小表.length10;i++)
	{
		for(int j=0;j<大表.length300;j++)
		{
			...
		}
	}
	

	select ...where 大表.x300=小表.x10 ;
	for(int i=0;i<大表.length300;i++)
	{
		for(int j=0;j<小表.length10;j++)
		{
			...
		}
	}

--以上2个FOR循环，最终都会循环3000次；但是 对于双层循环来说：一般建议 将数据小的循环 放外层；数据大的循环放内层。

【优化分析】

**本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引 **
当编写 ..on t.cid=c.cid 时，将数据量小的表放左边假设此时t表数据量小

-- 常用的字段有 teacher2表中的tid和course2表中的cname
mysql> ALTER TABLE teacher2 ADD INDEX idx_teacher_cid(cid);
mysql> ALTER TABLE course2 ADD INDEX idx_course_cname(cname);
mysql> EXPLAIN SELECT *
    -> FROM teacher2 LEFT OUTER JOIN course2
    -> ON teacher2.cid = course2.cid
    -> WHERE course2.cname = "Java";
+----+-------------+----------+------+------------------+------------------+---------+------------------------+------+-------------+
| id | select_type | table    | type | possible_keys    | key              | key_len | ref                    | rows | Extra       |
+----+-------------+----------+------+------------------+------------------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | course2  | ref  | idx_course_cname | idx_course_cname | 63      | const                  |    1 | Using where |
|  1 | SIMPLE      | teacher2 | ref  | idx_teacher_cid  | idx_teacher_cid  | 4       | learn_demo.course2.cid |    1 | Using index |
+----+-------------+----------+------+------------------+------------------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

三表优化

------------------------------------
-    索引构建原则:                  
-    1. 小表 驱动 大表             
-    2. 索引建立在经常查询的字段上   
------------------------------------

一些其他的优化方法

WHERE子句中字段查询顺序要和复合索引构建的顺序一致

【数据准备】

create table test03
(
  a1 int(4) not null,
  a2 int(4) not null,
  a3 int(4) not null,
  a4 int(4) not null
);
-- 添加复合索引
ALTER TABLE test03 ADD INDEX idx_a1_a2_a3_a4(a1,a2,a3,a4);
-- 【推荐写法】
mysql> EXPLAIN SELECT a1, a2, a3, a4
    -> FROM test03
    -> WHERE a1 = 1 AND a2 = 2 AND a3 = 3 AND a4 = 4;
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 16      | const,const,const,const |    1 | Using index |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
1 row in set (0.00 sec)

-- 【当WHERE子句编写顺序和索引的构建不一致时，SQL优化器(Optimizer)会自动进行调整 其结果和上述写法一致】
mysql> EXPLAIN SELECT a1, a2, a3, a4
    -> FROM test03
    -> WHERE a4 = 4 AND a2 =2 AND a1 = 1 AND a3 =3;
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 16      | const,const,const,const |    1 | Using index |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
1 row in set (0.00 sec)

-- 【WHERE 和 ORDER BY联合使用的时候不要跨列、无序使用复合索引】
-- 【????未跨列使用】
mysql> EXPLAIN SELECT a1, a2, a3, a4
    -> FROM test03
    -> WHERE a1 = 1 AND a2 = 2 AND a3 = 3 AND a4 = 4
    -> ORDER BY a1; -- ✔ 未跨列使用复合索引
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 16      | const,const,const,const |    1 | Using index |
+----+-------------+--------+------+-----------------+-----------------+---------+-------------------------+------+-------------+
1 row in set (0.00 sec)

-- 【????跨列使用】
mysql> EXPLAIN SELECT *
    -> FROM test03
    -> WHERE a1 = 1
    -> ORDER BY a4;  -- 在复合索引中(a1,a2,a3,a4) 在查询中时 (a1,a4)， a1和a4跨列使用了
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref   | rows | Extra                                    |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_a1_a2_a3_a4 | idx_a1_a2_a3_a4 | 4       | const |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)

【案例】

	explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; 
	--以上SQL用到了a1 a2两个索引，该两个字段 不需要回表查询using index ;而a4因为跨列使用，造成了该索引失效，需要回表查询 因此是using where；以上可以通过 key_len进行验证

	explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3; 
	--以上SQL出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用)


	explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; --不会using filesort

总结： WHERE 和 ORDER BY 中的字段拼起来不要跨列无序使用

EXIT 和 IN的使用

	（1）
	exist和in
	select ..from table where exist (子查询) ;
	select ..from table where 字段 in  (子查询) ;

	如果主查询的数据集大，则使用In   ,效率高。
	如果子查询的数据集大，则使用exist,效率高。	

	exist语法： 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功）  ，
		    如果 复合校验，则保留数据；

	select tname from teacher where exists (select * from teacher) ; 
	--等价于select tname from teacher

	
	select tname from teacher where exists (select * from teacher where tid =9999) ;
	
	in:
	select ..from table where tid in  (1,3,5) ;
	

	（2）order by 优化
	using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
	MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序）   2：扫描其他字段 ）
		--IO较消耗性能
	MySQL4.1之后 默认使用 单路排序  ： 只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。
		注意：单路排序 比双路排序 会占用更多的buffer。
			单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：  set max_length_for_sort_data = 1024  单位byte

	如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路   （太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

	提高order by查询的策略：
	a.选择使用单路、双路 ；调整buffer的容量大小；
	b.避免select * ...  
	c.复合索引 不要跨列使用 ，避免using filesort
	d.保证全部的排序字段 排序的一致性（都是升序 或 降序）