首页 > mysql教程 阅读:0更新时间:2020-12-13 05:41:31

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

文章目录

  • 6.0 前言
  • 6.1 为什么查询速度会慢
  • 6.2 慢查询基础:优化数据访问
    • 1. 是否向数据库请求了不需要的数据
    • 2. mysql是否在扫描额外的记录
      • 响应时间
      • 扫描的行数和返回的行数
      • 扫描的行数和访问类型
  • 6.3 重构查询的方式
    • 1. 一个复杂查询 or 多个简单查询?
  • 2. 切分查询
    • 3. 分解关联查询
  • 6.4 查询执行的基础
    • 3. 针对第3步:查询优化处理
      • 3.1 查询优化器
      • 3.2 MySQL如何执行关联查询
      • 3.3 执行计划
      • 3.4 关联查询优化器
  • 6.5 MySQL查询优化的局限性
    • 1.关联子查询
    • 2. union的限制
    • 3. 索引合并优化
    • 4. max() 和 min()优化
    • 5. 在同一个表上查询和更新
  • 6.6 查询优化器的提示(hint)
  • 6.7 优化特定类型的查询
    • 1. 优化count()查询
      • count()函数真正的作用?
      • MyISAM神话?
      • 简单的优化
      • 使用近似值
      • 更复杂的优化
    • 2.优化关联查询
    • 3.优化GROUP BY 和 DISTINCT
      • 优化group by with rollup
    • 4.优化limit分页
    • 5.优化SQL_CALC_FOUND_ROWS
    • 6.优化UNION查询
    • 7.静态查询查询
    • 8.使用用户自定义变量
      • 优化排名语句
      • 避免重复查询刚更新的数据
      • 统计更新和插入的数量
      • 确定取值的顺序
      • 编写偷懒的union
      • 其他用处
  • 6.8 案例学习
    • 1. 构建一个队列表
    • 2. 计算两点之间的距离
    • 3. 使用用户自定义函数
  • 6.9 总结

6.0 前言

查询优化、索引优化、库表结构优化需要齐头并进,一个不落!

chapter 5 学习了如何建立最好的索引,但是如果查询写得很糟糕,索引再好,也没办法实现高性能!

6.1 为什么查询速度会慢

真正重要的是响应时间。

查询可以看成一个任务,它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,本质是优化子任务,或消除子任务,或减少子任务的执行次数,或让子任务执行更快。

6.2 慢查询基础:优化数据访问

查询性能低下最基本的原因:访问数据太多。

大部分性能低下的查询都可以通过减少访问数据进行优化。对于低效查询,我们发现可以通过以下2步分析:

  1. 确认应用程序是否在检索大量超过需要的数据(访问了太多的行/列);
  2. 确认MySQL服务器层是否在分析超过需要的数据行。

1. 是否向数据库请求了不需要的数据

有时候查询会请求超过实际的数据,这些造成的后果有,MySQL服务器带来额外的负担、增加网络开销、消耗应用服务器的CPU和内存资源。

典型案例:

  • 查询不需要的记录:要记得在查询后面加limit;
  • 多表关联时返回全部列:尽量别写select *;
  • 重复查询相同的数据:初次查询将这个数据缓存,需要的时候从缓存取出。

2. MySQL是否在扫描额外的记录

在确定查询返回需要的数据后,接下来应该看看查询为了返回结果是否扫描了过多的数据,对于MySQL,最简单的衡量查询开销的三个指标如下:

  • 响应时间
  • 扫描的行数
  • 返回的行数

没有哪个指标能够完美的平衡查询的开销,但是大致反映了查询需要访问多少数据,推算出查询运行的时间。检查慢日志是找出扫描行过多的方法。

响应时间

响应时间=服务时间+排队时间

服务时间:指数据库处理这个查询真正花了多长时间。

排队时间:服务器因为等待某些资源而没有真正执行查询的时间,可能是等I/O操作完成,也可能是等待锁。
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

扫描的行数和返回的行数

查看扫描行数非常有帮助,一定程度上能说明效率!explainrows值。

理想情况下扫描的行数和返回的行数应该相同的,但是实际却难以达到,例如像关联查询时,服务器必须要扫描多行才能生成结果集中的一行。

详见:【数据库笔记】MySQL Explain解析

扫描的行数和访问类型

EXPLAIN语句中的TYPE列反应了访问类型,访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等等速度是从慢到快扫描的行数也是从大到小。如果查询没有办法找到合适的访问类型,那么解决的最好办法就是增加一个合适的索引。索引让MySQL以最高效、扫描行最少的方式找到需要的记录。

explain select * from sakila.film_actor where film_id=1\G

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
该查询返回10行数据,从explain的结果可以看到,MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询

一般的,MySQL可以从三种方式应用WHERE条件,从好到坏依次为:

  • 在索引中使用where条件来过滤不匹配的记录,这是存储引擎层完成的;
  • 使用索引覆盖查询(在Extra列中出现了Using inex 来返回记录)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果;
  • 从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务层完成,无须再回表查询记录。
select actor_id,count(*) 
from sakila.film_actor 
group by actor_id

这个查询需要扫描大量数据,却只返回10行。

如果发现查询需要扫描大量的数据但是只返回少数的行,那么通常可以尝试下面的技巧优化它:

  • 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无需回表获取对应的行就可以返回了;
  • 改变库表结构。例如使用单独的汇总表;
  • 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。

6.3 重构查询的方式

1. 一个复杂查询 or 多个简单查询?

有时候,将一个大查询分解为多个小查询是很多必要的,关键在于这么做会不会减少工作量。

不过在应用设计时,如果一个查询能够胜任多个独立查询,分解它是不明智!比如,对一个表做10次独立的查询来返回10行数据,每个查询只返回一条结果,需要查询10次。

2. 切分查询

有时候,将一个大查询需要“分而治之”,将它分解为多个小查询。
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

delete from messages where created < date_sub(now(),interval 3 month);

可以用类似如下的方式完成:

rows_affected=0
do {
	rows_affected=do_query(
		'delete from messages where created < date_sub(now(),interval 3 month) limit 10000')
} while rows_affected>0

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3. 分解关联查询

很多高性能应用会对关联查询进行分解。

简单地说,可以对每一个表进行一次单表查询然后将结果在应用程序中进行关联。

select * from tag
	join tag_post on tag_post.tag_id=tag.id
	join post on tag_post.post_id=post.id
where tag.tag='mysql'

可以分解成以下查询来代替:

select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,456,567,9098,8904);

到底为什么要这样做?咋一看,这样做并没有什么好处,原本一条查询,这里却变成了多条查询,返回结果又是一模一样。

事实上,用分解关联查询的方式重构查询具有如下优势:

  1. 让缓存的效率更高。 许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。

  2. 将查询分解后,执行单个查询可以减少锁的竞争。

  3. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。

  4. 查询本身效率也可能会有所提升

  5. 可以减少冗余记录的查询。

  6. 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套环关联,某些场景哈希关联的效率更高很多。

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

6.4 查询执行的基础

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3. 针对第3步:查询优化处理

3.1 查询优化器

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

explain select film.flim_id,film_actor.actor_id
from sakila.film
inner join sakila.film_ator using(film_id)
where film.flim_id=1;

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3.2 MySQL如何执行关联查询

MySQL中“关联”一词包含的意义比一般意义上理解的更广泛。总的来说,MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要两个表匹配才叫关联!。

MySQL中,每个查询,每个片段(子查询、单表select)都可能是关联。

select * from t1 inner join t2 on t1.col3=t2.col3;
--等价于
select * from t1 inner join t2 using(col3);

MySQL的嵌套循环关联操作

select tbl1.col1,tbl2.col2
from tbl1 inner join tbl2 using(col3)
where tbl1.col1 in (5,6);

假设MySQL按照查询中的表顺序进行关联操作,则可以用伪代码表示MySQL如何完成这个查询:

outer_iter=iterator over tbl1 where col1 in (5,6)
outer_row=outer_iter.next
while outer_row
	inner_iter=iterator over tbl2 where col3=outer_row.col3
	inner_row=inner_iter.next
	while inner_row
		output [outer_row.col1,inner_row.col2]
		inner_row=inner_iter.next
	end
	outer_row=outer_iter.next
end

该执行计划对于单表查询和多表关联查询均适用,单表查询只要完成上面的外层outer操作。

同理,我们把上面的查询修改成外连接:

select tbl1.col1,tbl2.col2
from tbl1 left join tbl2 using(col3)
where tbl1.col1 in (5,6);

对应的伪代码:

outer_iter=iterator over tbl1 where col1 in (5,6)
outer_row=outer_iter.next
while outer_row
	inner_iter=iterator over tbl2 where col3=outer_row.col3
	inner_row=inner_iter.next
	if inner_row # 唯一不同之处,加了if...else...end
		while inner_row
			output [outer_row.col1,inner_row.col2]
			inner_row=inner_iter.next
		end
	else # 唯一不同之处
		output [outer_row.col1,NULL]
	end # 唯一不同之处
	outer_row=outer_iter.next
end

可视化查询执行计划,绘制“泳道图”,从左至右,从上至下地看图:
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

从本质上说,MySQL对所有类型的查询都以同样的方式运行。有一个例外:全外连接,无法通过嵌套循环和回溯的方式完成,因为会发现关联表中没有找到任何匹配行的时候,可能是因为关联是恰好从一个没有任何匹配的表开始的。
详见:【数据库笔记】MySQL&Oracle JOIN方法图码总结

3.3 执行计划

和很多其他关系数据库不同,MySQL不会生成查询字节码来执行查询,MySQL生成查询的一颗指令树,然后通过存储引擎执行完成这颗指令树并返回结果

任何多表查询都可以使用一颗树表示,MySQL总是从一个表开始嵌套循环、回溯完成所有表关联。因此,MySQL的执行计划总是如下(一颗左侧深度优先的树)
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3.4 关联查询优化器

MySQL优化器最重要的一部分:关联查询优化,他决定了多个表关联时的顺序。

通常,多表关联可以有多种不同的关联顺序,来获得相同的执行结果。如下例子:

select film.film_id,film_title,film.release_year,actor.actor_id,actor.first_name,actor.last_name
from sakila.film
inner join sakila.film_actor using(film_id)
inner join sakila.actor using(actor_id);

容易看出,可以通过一些不同的执行计划来完成上面的查询。比如说,MySQL可以从film表开始,用film_actor表的索引film_id来找对应的actor_id值,再根据actor表的主键找到对应记录。

explain查看MySQL如何执行这个查询:
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
显然,它的顺序和刚计划的不一样,MySQL先从actor表开始,那么MySQL为啥这么做?我们可以用straight_join关键字,令MySQL按我们之前的顺序执行:

explain select straight_join film.film_id,film_title,film.release_year,actor.actor_id,actor.first_name,actor.last_name
from sakila.film
inner join sakila.film_actor using(film_id)
inner join sakila.actor using(actor_id)\G

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
结果显而易见:MySQL为啥倒转顺序呢?因为倒转之后,第一个关联表只要扫描很少的行数。第二个和第三个查询都是根据索引查,速度都贼快。

为了验证优化器的选择是不是正确,我们可以单独执行这两个查询,并看看对应的last_query_cost

show status like 'last_query_cost';

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

一般来说,人的判断没有优化器的准确。

如果有n个表要进行关联,那么就要检查n!(n的阶乘)种关联顺序——所有可能的执行计划的“搜索空间”,“搜索空间”增速非常快。
此时,优化器选择是有“贪婪”搜索的方式,找到“最优的关联顺序”

6.5 MySQL查询优化的局限性

1.关联子查询

最糟糕的一类查询:where条件中包含in()的子查询语句。

实例:从sakila数据库中,找到actor_id=1的演员参演过的所有影片信息。

很自然会这么写:

select * from sakila.film
where film_id in (
select film_id from sakila.film_actor where actor_id=1);

因为MySQL对in()列表的选项有专门的优化策略,一般会认为MySQL会先执行子查询,返回所有包含actor_id=1film_id

--select group_concat(film_id) from sakila.film_actor where actor_id=1;
--Result:1,23,25,106,140,166,277,361,438,499,506,605
select * from sakila.film
where film_id in (
1,23,25,106,140,166,277,361,438,499,506,605);

Unfortunately,u think too much!

MySQL不是这样搞的,它会将相关的外层表压到子查询中,因为MySQL觉得这样会效率更高:

select * from sakila.film
where exists (
select * from sakila.film_actor where actor_id=1
and film_actor.film_id=film.film_id);

这时,子查询需要根据film_id来关联外层表film,所以MySQL没法先执行这个子查询。不信,explain一下看看:

explain select * from sakila.film
where exists (
select * from sakila.film_actor where actor_id=1
and film_actor.film_id=film.film_id);

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
重写查询,方法1——使用内连接:

select film.* from sakila.film
	inner join sakila.film_actor using(film_id)
where actor_id=1);

重写查询,方法2——使用GROUP_CONCAT()[有时比内连接更快]:

select * from sakila.film
where film_id in (
	select group_concat(
		select film_id from sakila.film_actor where actor_id=1)
	from dual
);

详见:mysql之group_concat函数详解

重写查询,方法3——使用exists()
in() 加子查询,性能经常很糟糕,索引通常建议用exists()来改写查询,得到更好的效率。

select * from sakila.film
where exists (
	select * from sakila.film_actor where actor_id=1
	and film_actor.film_id=film.film_id);

如何用好关联子查询?

explain select film_id,language_id from sakila.film
where not exists(
	select * from sakila.film_actor
	where film_actor.film_id=film.film_id
	)\G

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
一般用左外连接改写,但执行计划基本不会变:

explain select film.film_id,film.language_id from sakila.film
left join sakila.film_actor using(film_id)
where film_actor.film_id is not null\G

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

QPS(每秒Query量)
QPS = Questions(or Queries) / seconds

show global status like 'Question%';
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

通过上面两个例子,我们可以得到:

  • 要尝试才知道是关联查询更快,还是子查询更快;
  • 测试来验证对子查询的执行计划(explain)和响应时间(QPS)的假设。

2. union的限制

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3. 索引合并优化

详见chapter 5:【数据库笔记】高性能MySQL:chapter 5 创建高性能的索引

4. max() 和 min()优化

MySQL在max()min()优化上做得不好。

select min(actor_id)
from sakila.actor
where first_name='mary';

因为在first_name没有索引,所以MySQL会先全表扫面一次。如果MySQL能进行主键扫描,理论上当MySQL读到第一个满足条件的记录就是我们需要的最小值,因为主键是严格按照actor_id字段的大小顺序排列。

但是,MySQL现在要先全表扫描一次。我们可以通过show status的全表扫描计数器来验证。

详见:MySQL运行状态show status中文详解

优化方法——曲线救国,移除min()

select actor_id
from sakila.actor use index(primary)
where first_name='mary' limit 1;

--最大值
select actor_id
from sakila.actor use index(primary)
where first_name='mary' 
order by actor_id desc
limit 1;

这个策略能让MySQL扫描尽可能少的行。其实是告诉MySQL如何去获取我们的数据,通过sql确实无法一眼看出我们要的是min().
有时为了更高的性能,不得不放弃一些原则。

5. 在同一个表上查询和更新

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

6.6 查询优化器的提示(hint)

详见:mysql查询优化之三:查询优化器提示(hint)

6.7 优化特定类型的查询

1. 优化count()查询

count()函数真正的作用?

  • 统计某个列值的数量(不计null
  • 统计行数 (确保count()括号内的表达式不为null时),或使用count(*)

MyISAM神话?

MyISAM的count()函数总是非常快,然而这是有前提的!即只有没有任何where条件的count(*)才快。 如果带where,那么MyISAM没有任何优势了,就会和其他引擎一样,或者更慢,受很多因素影响。

如果MySQL知道count(col)col不可能含有null,那么MySQL内部会把count(col)优化为count(*)

简单的优化

优化前:

select count(*) from world.city
where ID>5;

优化后(取反):

select (select count(*) from world.city)-count(*)
from world.city
where ID<=5;

这样做可以大大减少需要扫描的行数,是因为在查询优化阶段会将其中的子查询直接当一个常数处理。

不信就explain一下看看:
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
优化前:
在一个查询中统计同一列的不同值的数量,此时不能用OR语句,如此操作无法区分不同颜色的商品数量,也不能在where中指定color

select count(color=''blue' or color='red') 
from items;

优化后:

select  sum(if(color='blue',1,0)) as blue,
		sum(if(color='red',1,0)) as red
from items;

也可以用count,将满条件设置为真,不满足的设置成null

select  count(color='blue' or null) as blue,
		count(color='red' or null) as red
from items;

使用近似值

有时不要求完全精确的count值,也可以用近似值代替。

explain出来的优化器估算的行数就是一个不错的近似值,执行explain并不需要真正地去执行查询,所以成本很低。

更复杂的优化

count()需要扫描大量的行(意味着要访问大量的数据)才能获得精确的结果,因此是很难优化的。

除了前面的方法,MySQL还能做的只有索引覆盖扫描

再进一步,就要考虑修改应用的架构。

2.优化关联查询

  • 确保on或者using子句中的列上有索引。 在创建索引的时候就要考虑到关联的顺序:当表A和表B用列C关联的时候,如果优化器的关联顺序是B、Aselect a.id,b.name from b left join a on b.id=a.id,那么就不需要在B的对应列上建立索引。没用到的索引只会带来额外负担。除非有其他理由,否则只要在关联顺序中的第二个表上建立相应索引。
  • 确保任何的group by和order by中的表达式只涉及到一个表中的列。

3.优化GROUP BY 和 DISTINCT

最有效的优化方法:使用索引。

无法使用索引时,GROUP BY 使用两种策略:

  • 使用临时表
  • 文件排序做分组

可以通过使用提示SQL_BIG_RESULTSQL_SMALL_RESULT来让优化器按照你希望的方式运行。

优化前:

select  actor.first_name,
		actor.last_name,
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.first_name,actor.last_name;

优化后:

如果需要对关联查询做分组group by,并且是按照查找表中的某个列进行分组,那么 通常采用查找表的标识列 分组的效率会比其他列更高:

select  actor.first_name,
		actor.last_name,
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.actor_id;

在分组查询的select中,直接使用非分组列通常不是什么好主意,因此上述语句还能用min()max() 改写,这样做需要列不在意这个值是啥,或者值唯一:

select  min(actor.first_name),
		max(actor.last_name),
		count(*)
from sakila.film_actor
	inner join sakila.actor using(actor_id)
group by actor.actor_id;

当使用group by的时候,结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序,而这种自动排序又导致了需要文件排序,则可以使用order by null,让MySQL不再进行文件排序!

优化group by with rollup

分组查询的另一个变种:对分组结果再做一次超级聚合。

可以使用with rollup子句来实现这种逻辑,但可能会不够优化。

可以通过explain来观察执行计划,特别注意分组是否是通过文件排序或临时表实现的。然后,再去掉with rollup来看执行计划是不是相同。

很多时候,如果可以用超级聚合是更好的。

4.优化limit分页

在偏移量大的时候,如limit 1000,20,需要查询10020条记录,然后只返回最后20条…其实是offset的问题,导致扫描大量不用的数据又给抛弃掉。

显然,这样的代价非常高…优化手段:

  • 限制分页数量
  • 优化大偏移量的性能

最简单的方法:尽可能用索引覆盖扫描,而不是查询所有列.

优化前:

select film_id,description
from sakila.film
order by title
limit 50,5;

优化后:

select film.film_id,film.description
from 	sakila.film
	inner join
		(select film_id 
	     from sakila.film
	     order by title 
	     limit 50,5) as lim
	using(film_id)

这里“延迟关联”将大大提升查询效率,它让MySQL扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列。

或者预先知道了位置,且位置上有索引:

select film_id,description
from sakila.film
where position between 50 and 54
order by position;

5.优化SQL_CALC_FOUND_ROWS

limit语句中加上SQL_CALC_FOUND_ROWS提示就可以获得去掉limit后满足条件的行数,可以作为分页的总数

这个提示的代价非常高!!
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

6.优化UNION查询

除非要去重,否则一定用union all

7.静态查询查询

pt-query-advisor能够给出所有可能潜在问题和建议,像健康检查。

8.使用用户自定义变量

非常强大!

可以用setselect来定义它们。

set @one:=1;
set @min_actor:=(select min(actor_id) from sakila.actor);
set @last_week:=current_date-interval 1 week;

然后可以再任何使用表达式的地方使用它们:

select ... where col<=@last_week;

不能使用用户自定义变量的情况:
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

优化排名语句

eg1.实现行号的功能:

set @rownum:=0;
select  actor_id,
		@rownum:=@rownum+1 as rownum
from sakila.actor limit 3;

eg2.更复杂的情况: 编写一个查询获取演过最多电影的前10名演员,然后根据他们的出演次数做一个排名,如果出演的数量一样则排名相同。

select 	actor_id,
		count(*) as cnt
from sakila.film_actor
group by actor_id
order by cnt desc
limit 10;

再把排名加上去:

set @curr_cnt:=0,@prev_cnt:=0,@rank:=0;

select 	actor_id,
		@curr_cnt:=count(*) as cnt,
		@rank	 :=if(@prev_cnt<>@curr_cnt,@rank+1,@rank) as rank,
		@prev_cnt:=@curr_cnt as dummy
from (
	select  actor_id,
			count(*) as cnt
	from sakila.film_actor
	group by actor_id
	order by cnt desc
	limit 10
) as der;

避免重复查询刚更新的数据

客户希望更高效地更新一个时间戳,同时希望查询当前记录中存放的时间戳是什么:

update t1 set lastupdated=now() where id=1;
select lastupdatedfrom t1 where id=1;

用变量写:

update t1 set lastupdated=now() where id=1 and @now:=now();
select @now;

看上去要2次查询,其实第二行无需访问任何表,会快很多!!!

统计更新和插入的数量

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

确定取值的顺序

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

编写偷懒的union

假设需要编写一个UNION查询,其第一个子查询作为分支条件先执行,如果找到了匹配的行,则跳过第二个分支。例如先在一个频繁访问的表查找热数据,找不到再去另外一个较少访问的表查找冷数据。
案例:先在一个频繁访问的表中查找“热”数据,找不到再去另一个较少访问的表找“冷”数据。

select greatest(@found:=-1,id) as id,'users' as which_tbl
from users where id =1
union all
	select id,'users_archived' 
	from users_archived where id=1 and @found is null
union all
	select 1,'reset' from dual where (@found:=null) is not null;

其他用处

通过一些实践,可以了解所有用户自定义变量能够做的有趣的事情,例如下面这些用法:

  • 查询运行时计算总数和平均值
  • 模拟GROUP语句中的函数FIRST()LAST()
  • 对大量数据做一些数据计算
  • 计算一个大表的MD5散列值
  • 编写一个样本处理函数
  • 模拟读/写游标
  • SHOW语句的WHERE子句中加入变量值

6.8 案例学习

1. 构建一个队列表

2. 计算两点之间的距离

案例:查找某个点附近所有可以出租的房子

create table locations(
	id   int not null primary,
	name varchar(30),
	lat  float not null,
	lon  float not null
);
insert into locations(name,lat,lon)
	values('charlottesville,virginia',38.03,-78.48),
		  ('chicago,illinois',41.85,-87.65),
		  ('washington,DC',38.89,-77.04);

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

select * from locations
where lat between 38.03-degrees(0.0253) and 38.03+degrees(0.0253)
and lon between -78.48-degrees(0.0253) and -78.48+degrees(0.0253)

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

select  floor(38.03-degrees(0.0253)) as lat_lb,
		ceiling(38.03+degrees(0.0253)) as lat_ub,
		floor(-78.48-degrees(0.0253)) as lon_lb,
		ceiling(-78.48+degrees(0.0253)) as lon_ub;

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

select * from locations
where lat between 38.03-degrees(0.0253) and 38.03+degrees(0.0253)
and lon between -78.48-degrees(0.0253) and -78.48+degrees(0.0253)
and lat_floor in(36,37,38,39,40) and lon_floor in (-80,-79,-78,-77);

【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引
【数据库笔记】高性能MySQL:chapter 6 创建高性能的索引

3. 使用用户自定义函数

6.9 总结

优化:不做、少做、快速的做。

beylze编程学院,一个分享编程知识和seo优化知识的网站。跟着beylze一起学习,每天都有进步。

通俗易懂,深入浅出,一篇文章只讲一个知识点。

文章不深奥,不需要钻研,在公交、在地铁、在厕所都可以阅读,随时随地涨姿势。

文章不涉及代码,不烧脑细胞,人人都可以学习。

当你决定关注beylze(公众号:beylze),你已然超越了90%的其他从业者!

相关文章