首页 > 编程学习 > 【SQL】查询优化|排序优化|group by优化|分页查询优化|覆盖索引

【SQL】索引优化与查询优化

  • 外连接与内连接的查询优化
  • 子查询优化
  • 排序优化 order by
    • 案例实战
  • group by 优化
  • 分页查询优化 limit
  • 覆盖索引的使用

外连接与内连接的查询优化

# 左外连接: 左表的所有内容+左右表共有的内容
EXPLAIN SELECT SQL_NO_CACHE * FROM 'type' LEFT JOIN book ON type.card = book.card;
# 给book表添加索引
CREATE INDEX Y ON book(card);
# 此时,book表的type = ref,使用索引;type表的type = all,左表内容全要,所以全表查询

# 再给type表添加索引,查询时也会用到这个索引
CREATE INDEX X ON 'type'(card);
# 左表-驱动表,右表-被驱动表

# 注意:查询语句中相比较的两个字段type.card = book.card,名字可以不一样,但数据类型必须相同。

DROP INDEX X ON 'type';
DROP INDEX Y ON 'type';


# 内连接:两表共有的部分
EXPLAIN SELECT SQL_NO_CACHE * FROM 'type' INNER JOIN book ON type.card = book.card;
# 给book表添加索引
CREATE INDEX Y ON book(card);
# 此时,book表的type = ref,使用索引;type表的type = all

# 给type表加索引
CREATE INDEX X ON 'type'(card);
# 此时再执行上面的查询,两表都会用到索引。而哪个表作为驱动表哪个作为被驱动表,由查询优化器自行选择

# 结论:被驱动表加索引执行效率高。对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表
# 对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小结果集作为驱动表。小结果集驱动大结果集

小结:

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段,数据类型保持绝对一致。
  • LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表。减少外层循环的次数。
  • INNER JOIN 时,MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略
  • 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
  • 不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
  • 衍生表建不了索引

子查询优化

子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子查询的执行效率不高。原因:
① 执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能就会更好。

#################### 子查询的优化 ###########################
# 创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

# 子查询
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor 
FROM class c
WHERE monitor IS NOT NULL
);
# 将子查询改写成多表连接
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

# 查询不为班长的学生信息
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a
WHERE a.`stuno` NOT IN (
	SELECT monitor FROM class b
	WHERE monitor IS NOT NULL
);
# 子查询改为多表连接, xxx not in xxx 可改为下面的方式
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a LEFT JOIN class b
ON a.`stuno` = b.`monitor`
WHERE b.`monitor` IS NULL;
# 两表结合,去掉b表中有的,即 WHERE b.`monitor` IS NULL,剩仅a表有的

结论:尽量不要使用NOT IN 或者 NOT EXISTS,用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化 order by

问题:在 WHERE 条件字段上加索引,但是为什么在 ORDER BY 字段上还要加索引呢?
MySQL支持两种排序方式,filesort和index。index排序中索引可以保证数据的有序性,不需要再进行排序,效率高;filesort在内存中排序,占用cpu多,io频繁,效率低

优化建议:

  1. SQL 中,可以在 WHERE 子句和 ORDER BY 子句中使用索引,目的是在 WHERE 子句中避免全表扫描,在 ORDER BY 子句避免使用 FileSort 排序。当然,某些情况下全表扫描,或者 FileSort 排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引。
  3. 无法使用 Index 时,需要对 FileSort 方式进行调优。
########## 排序优化 ###############
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');
# 一:无索引的情况
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;	# 无索引时,Using filesort
EXPLAIN SELECT SQL_NO_CACHE * from studnet ORDER BY age,classid LIMIT 10; 	# filesort

# 二: 
# 建立联合索引
CREATE INDEX idx_age_cid_name ON student(age,classid,NAME);

# 执行sql语句
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;	# 索引失效。数据量十分庞大,若使用了索引,每遍历一次b+树都要回表
#优化器直接把所有内容放入内存进行排序

EXPLAIN SELECT SQL_NO_CACHE age,classid FROM student ORDER BY age,classid;	# * 改为具体字段,用上了索引,没有回表 (覆盖索引)

# 增加limit过滤条件
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 	# 使用了索引,只取前十个,用索引效率低

# 三:
CREATE INDEX idx_age_classid_stuno ON student(age,classid,stuno);

EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;
EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;
EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10;
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

# 四:
EXPLAIN SELECT * FROM student ORDER BY age DESC,classid ASC LIMIT 10;	# desc 降序,索引升序,所以用不到索引
EXPLAIN SELECT * FROM student ORDER BY classid DESC,NAME DESC LIMIT 10;	# 没有age,不能索引
EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;	# age升序,classid降序,不能用索引
EXPLAIN SELECT * FROM student ORDER BY age DESC,classid DESC LIMIT 10;	# 都反,可以用索引
# 顺序错,不索引;方向反,不索引

# 五:
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid; 	# 使用到索引,但key_len = 5,只有age
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid,NAME;	#同上

# 先按age排序,然后找id=45的,接下来在索引中取前10个
EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age;	#索引失效
EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age LIMIT 10;	#索引可用,取前十个

CREATE INDEX idx_cid ON student(classid);
EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age;	#有了单独的classid索引,索引可用

案例实战

####### 案例实战 ########
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME;	#Using where; Using filesort
# 方案1:为了去掉filesort,可以把索引建成
CREATE INDEX idx_age_name ON student(age,NAME);	
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME;	# key_len = 5,只用到age字段

# 方案2:
CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME;	# key_len = 9,用到age,stuno字段,stuno后的字段无效
# Using index condition; Using filesort 使用了filesort,但效率并不比index低

结论:

  1. 两个索引同时存在,mysql自动选择最优的方案。但是, 随着数据量的变化,选择的索引也会随之变化的。
  2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。

group by 优化

  • group by 使用索引的原则几乎跟order by一致 ,group by 即使没有过滤条件用到索引,也可以直接使用索引。
  • group by 先排序再分组,遵照索引建的最佳左前缀法则
  • 当无法使用索引列,增大max_length_for_sort_data 和sort_buffer_size 参数的设置
  • where效率高于having,能写在where限定的条件就不要写在having中了
  • 减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做Order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
  • 包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。

分页查询优化 limit

这是一个常见又头疼的问题,有很庞大的一堆数据,要查看第200万条数据后的10条数据,此时需要MySQL排序前2000010条数据,从中返回第2000000~2000010的记录,其他记录丢弃,查询排序的代价非常大。

explain select * from student limit 2000000,10;

优化方案:
查询的是 * ,尽量使用主键索引。
通过主键索引直接定位到id>2000000的位置,取数据。id需要是自增的方式

explain select * from student where id>2000000 limit 10;

或许id不是自增的方式,这时考虑通过id排序,使用聚簇索引

explain select * from student t,(select id from student order by id limit 2000000,10) a
where t.id = a.id;

覆盖索引的使用

覆盖索引:一个索引包含了满足查询结果的数据就叫做覆盖索引。
简单说就是, 索引列+主键 包含 SELECT 到 FROM之间查询的列。

################# 覆盖索引 ##################
CALL proc_drop_index('atguigudb2','student');

# 例一:
CREATE INDEX idx_age_name ON student (age,NAME);
EXPLAIN SELECT * FROM student WHERE age <> 20;	# 没有用到索引,不等于<>本来就会导致索引失效

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;	# 用到索引,字段都在索引中,不需要回表

# 例二:
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';	# 没有用到索引,%在首部

EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';	# 用到索引,要查询的字段刚好都在索引列中,直接从索引中查就可以,不需要回表。

好处:

  1. 避免Innodb表进行索引的二次查询(回表)
  2. 可以把随机IO变成顺序IO加快查询效率(回表是随机io)

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是个常用的优化手段
弊端:
索引字段的维护总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA,或者称为业务数据架构师的工作。

Copyright © 2010-2022 dgrt.cn 版权所有 |关于我们| 联系方式