1.Join查询优化
注意:
这里type为驱动表,book为被驱动表,下文会提到
不需要搞清两个表的结构
1.1 左外连接时
-- 不使用索引时
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
首先要考虑,添加被驱动表的索引,可以避免全表扫描
ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】,可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
添加驱动表的索引
ALTER TABLE `type` ADD INDEX X (card); #【驱动表】,无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
注意:链接条件的字段名可以不一样,但类型要相同
1.2 内连接时
查询优化器可以决定谁作为驱动表,谁作为被驱动表
对于内连接,如果表的连接条件中只能有一个字段有索引,有索引的字段会作为被驱动表
两个表都存在索引,查询优化器会选择小表作为驱动表
1.3 几条结论
-
使用join语句,性能比强行拆成多个单表执行SQL语句的性能更好
-
如果使用join语句,需要让小表驱动大表
-
保证被驱动表的JOIN字段已经创建了索引
-
需要JOIN 的字段,数据类型保持绝对一致
-
LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表 。减少外层循环的次数。
-
INNER JOIN 时,MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
-
能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
-
不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
-
衍生表建不了索引
注意:什么叫作“小表”?
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各
个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
2 子查询优化
使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结 果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。
子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,**子 查询的执行效率不高。**原因:
-
执行子查询时,MySQL需要为内层查询语句的查询结果 建立一个临时表 ,然后外层查询语句从临时表 中查询记录。查询完毕后,再 撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
-
子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会 受到一定的影响。
-
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询 不需要建立临时表 ,其 速度比子查询 要快 ,如果查询中使用索引的话,性能就会更好。
结论:尽量不要使用NOT IN 或者 NOT EXISTS,用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
3 排序优化
- SQL 中,可以在 WHERE 子句和 ORDER BY 子句中使用索引,目的是在 WHERE 子句中 避免全表扫 描 ,在 ORDER BY 子句 避免使用 FileSort 排序 。当然,某些情况下全表扫描,或者 FileSort 排 序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。
- 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列; 如果不同就使用联合索引。
- 无法使用 Index 时,需要对 FileSort 方式进行调优。
INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,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 /*对于排序来说,多个相等条件也是范围查询*/
- 实战
-- 慢
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
-- 优化:
-- 方案1.为了去掉filesort我们可以创建以下索引
CREATE INDEX idx_age_name ON student(age,NAME);
-- 方案2.尽量让where的过滤条件和排序使用上索引
DROP INDEX idx_age_name ON student;
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 ;
-- 结论使用方案2快很多
结论:
-
两个索引同时存在,mysql自动选择最优的方案。(对于这个例子,mysql选择 idx_age_stuno_name)。但是, 随着数据量的变化,选择的索引也会随之变化
-
当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过 滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段
上。反之,亦然。
4 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会很慢。
5 分页查询的优化
- 方案一
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;
- 方案二
该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询 。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
6 覆盖索引
索引字段覆盖了查询语句涉及的字段,直接通过索引文件就可以返回查询所需的数 据,不必通过回表操作。
-
理解方式一:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数 据,那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
-
理解方式二:非聚簇复合索引的一种形式,它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 (即建索引的字段正好是覆盖查询条件中所涉及的字段)。
简单说就是, 索引列+主键
包含 SELECT 到 FROM
之间查询的列 。
-
覆盖索引的利弊 好处:
-
-
避免Innodb表进行索引的二次查询(回表)
-
可以把随机IO变成顺序IO加快查询效率
-
-
弊端: 索引字段的维护 总是有代价的。
- 因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务
DBA,或者称为业务数据架构师的工作。
- 因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务
7 给字符串添加索引
使用前缀索引
alter table teacher add index index2(email(6));
优缺点与权衡
-
优点:使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。前面 已经讲过区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。
-
缺点:使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考 虑的一个因素。
8 索引(条件)下推
在根据索引查询过程中就根据查询条件过滤掉一些记录,减少最后的回表操作
举例:假如执行select * from stu where name=? and age=?
没有索引下推先再存储引擎根据name筛选数据返回给server层,然后server层再根据age过滤
有索引下推直接根据name和age在存储引擎层就筛选得到结果
-
使用前后的成本差别
-
使用前,存储层多返回了需要被index filter过滤掉的整行记录
-
使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。
-
ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。
ICP的使用条件 ICP的使用条件:
-
① 只能用于二级索引(secondary index)
-
②explain显示的执行计划中type值(join 类型)为 range 、 ref 、 eq_ref 或者 ref_or_null 。
-
③ 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录 到server端做where过滤。
-
④ ICP可以用于MyISAM和InnnoDB存储引擎
-
⑤ MySQL 5.6版本的不支持分区表的ICP功能,5.7版本的开始支持。
-
⑥ 当SQL使用覆盖索引时,不支持ICP优化方法。
9 普通索引与唯一索引的选择
-
查询比较
- 查询会以页为单位将数据页加载进内存,不需要一条记录一条记录读取磁盘。然后唯一索 引根据条件查询到记录时就返回结果,普通索引查到第一条记录往后遍历直到不满足条 件,由于都在内存中,不需要磁盘读取那么大开销,带来的额外查询开销忽略不计,所以 查询性能几乎一致
-
更新比较
-
唯一索引由于更新时要检查唯一性,所以需要将数据页先加载进内存才能判断,此时直接 操作内存,不需要操作change buffer
-
补充:普通索引若数据再内存中直接内存中更新,否则会将更新操作先记录到channge buffer中,等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数 据返回,这样一来,再写多读少的情况下就减少了磁盘IO,若写完就马上查询,就大可不 必用change buffer,不但没提高多少效率还造成维护change buffer额外消耗
-
将change buffer的操作对应到原始数据页的操作称为merge(可以查询来时读到内存再 修改数据,后台线程也会merge,数据库正常关闭也会merge)
-
-
适合场景
- 写多读少,选用普通索引更好,可以利用change buffer进行性能优化减少磁盘IO,将更新操作记录到change bufer,等查询来了将数据读到内存再进行修改.
其他查询优化策略
EXISTS 和 IN 的区分
不太理解哪种情况下应该使用 EXISTS,哪种情况应该用 IN。选择的标准是看能否使用表的索引吗?
还是依据小表驱动大表的原则,那个表小就用那个表来驱动,A表小就用Exists,B表小就用IN
count()相关查询
在innoDB引擎中如果采用count(具体字段)来统计相关行数,尽量采用二级索引,因为主键采用的时聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)。对于count(*)和count(1),他们不需要查找只是统计具体的行数,系统会自动采用占用空间更小的二级索引来统计
Select(*)
不建议使用的原因:
-
MySQL解析过程中,会通过查询数据字典将*转换成所有列名,会消耗很大的时间
-
无法使用覆盖索引
LIMIT 1 对优化的影响
针对的是会扫描全表的 SQL 语句,如果你可以确定结果集只有一条,那么加上 LIMIT 1 的时候,当找 到一条结果的时候就不会继续扫描了,这样会加快查询速度。
如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加
上 LIMIT 1 了。
多使用COMMIT
只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT 所释放 的资源而减少。
COMMIT 所释放的资源:
-
回滚段上用于恢复数据的信息
-
被程序语句获得的锁
-
redo / undo log buffer 中的空间
-
管理上述 3 种资源中的内部花费