mysql学习ppt摘抄笔记

 

目录

二、SQL优化原则与方法原则

三、SQL执行顺序：

 

四、order by查询的两种情况：

 

等价改写、反嵌套

 

SELECT优化-limit

 

事务的使用与优化

 

---

 

一、✓减少磁盘IO

全表扫描

磁盘临时表

日志、数据块fsync

✓ 减少网络带宽

返回太多数据

交互次数过多

✓ 降低CPU消耗

排序分组。order by, group by

聚合函数。max,min,sum...

逻辑读

二、SQL优化原则与方法原则

⚫ 减少访问量： 数据存取是数据库系统最核心功能，所以IO是数据库系统中最容易出现性能瓶颈，减少SQL访

问IO量是SQL优化的第一步；数据块的逻辑读也是产生CPU开销的因素之一。

• 减少访问量的方法：创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。

⚫ 减少计算操作 ：计算操作进行优化也是SQL优化的重要方向。SQL中排序、分组、多表连接操作等计算操作

都是CPU消耗的大户。

• 减少SQL计算操作的方法：排序列加入索引、适当的列冗余、SQL拆分、计算功能拆分。

方法 • 创建索引减少扫描量

• 调整索引减少计算量

• 索引覆盖（减少不必访问的列，避免回表查询）

• SQL改写

• 干预执行计划

三、SQL执行顺序：

 

(8) SELECT (9) DISTINCT <select_list>

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP <group_by_list>

(6) WITH {CUBE|ROLLUP}

(7) HAVING <having_condition>

(10) ORDER BY <order_by_list>

(11) LIMIT <limit_number>

四、order by查询的两种情况：

 

• Using index。MySQL直接通过索引返回有序记录，不需要额外的排序操作，操作效率较高

• Using filesort。无法只通过索引获取有序结果集，需要额外的排序，某些特殊情况下，会出现

Using temporary

优化目标：尽量通过索引来避免额外的排序，减少CPU资源的消耗

✓ where条件和order by使用相同的索引

✓ order by的顺序和索引顺序相同

✓ order by 的字段同为升序或降序

Version:0.9 StartHTML:0000000105 EndHTML:0000000809 StartFragment:0000000141 EndFragment:0000000769

注：当where条件中的过滤字段为覆盖索引的前缀列，而order by字段是第二个索引列时，只有

where条件是const匹配时，才可以通过索引消除排序，而between...and或>?、<?这种range匹配

都无法避免filesort操作

Version:0.9 StartHTML:0000000105 EndHTML:0000003048 StartFragment:0000000141 EndFragment:0000003008

当无法避免filesort操作时，优化思路就是让filesort的操作更快

排序算法：

• 两次扫描算法。两次访问数据，第一步获取排序字段的行指针信息，在内存中排序，第二步根据

行指针获取记录

• 一次扫描算法。一次性取出满足条件的所有记录，在排序区中排序后输出结果集。是采用空间换

时间的方式

注：需要排序的字段总长度越小，越趋向于第二种扫描算法，MySQL通过

max_length_for_sort_data参数的值来进行参考选择

优化策略：

1、适当调大max_length_for_sort_data这个参数的值，让优化器更倾向于选择第二种扫描算法

2、只使用必要的字段，不要使用select *的写法

3、适当加大sort_buffer_size这个参数的值，避免磁盘排序的出现（ 线程参数，不要设置过大 ）

➢ 子查询会用到临时表，需尽量避免

➢ 可以使用效率更高的join查询来替代

优化策略：

等价改写、反嵌套

 

如下SQL：

select * from customer where customer_id not in (select customer_id from payment)

改写形式：

select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null

 

SELECT优化-limit

 

➢ 分页查询，就是将过多的结果在有限的界面上分好多页来显示。

➢ 其实质是每次查询只返回有限行，翻页一次执行一次。

优化目标：

1、消除排序

2、避免扫描到大量不需要的记录

SQL场景（film_id为主键）：

select film_id,description from film order by title limit 10000,20

此时MySQL排序出前10020条记录后仅仅需要返回第10001到10020条记录，前

10000条记录造成额外的代价消耗

 

select * from sbtest1 where id > #max_id# order by id limit n;

• 适用顺序翻页的场景，每次记录上一页#max_id#带入下一次查询中

➢ select * from sbtest1 as a inner join ( select id from sbtest1 order by id limit m, n ) as b

on a.id = b.id order by a.id;

• 适用只按照id进行分页,无where条件

➢ select * from sbtest1 as a

inner join ( select id from sbtest1where col=xxxx order by id limit m, n ) as b

on a.id = b.id order by a.id;

• 适用于带where条件，同时按照id顺序分页

• 此时，需要在where条件上创建二级索引

 

 

事务的使用与优化

 

大事务

1.大事务拆分为小事务

2.DDL拆分（无锁变更）

长事务

1.合并为大事务（特别是写入场景）

2.事务分解 （不必要的请求摘除）