微信号:linux178

介绍:一群LINUX开源爱好者致力为大陆开源事业发展献力献技;为LINUX为生;为开源而舞~

高性能的MySQL查询慢与重构查询

2016-10-17 07:13 运维部落

只有好的库表结构、合理的索引还不够,我们还需要合理的设计查询,齐头并进,一个不少才能充分发挥MySQL的优势。

一、查询为什么会慢?

每一个查询由一系列的子任务组成,每个子任务都会消耗一定的时间。这个我们在之前的单个查询分析时已经简单介绍了,当然还有额外的因素,比方说包括网络,CPU计算,统计信息,执行计划,锁等待等操作,或者底层引擎在调用内存,CPU操作,I/O操作等上的消耗时间。

优化查询的目的就是减少和消除这些操作所花费的时间。查询性能低下的最基本原因是访问的数据太多,大部分的性能低下的查询可以通过减少访问的数据量进行优化,一般有2个简单的分析方法:

1、确认应用程序是否返回了大量超过需要的数据,这就是说访问了太多的行,也有时候是因为访问了太多的列,这会增加很多额外的开销,包括,网络,CPU,内存等,一些常见的例子:
  • a、查询不需要的记录

    一个常见的错误是常常会以为MySQL会只返回需要的数据,实际上却是先返回全部的查询结果再进行计算,一个简单有效的解决方法是在查询后面加上LIMIT。

  • b、多表关联时返回全部列

    比如,要查找所有在电影hreo中出现的演员,不要这样去写

    select * from actor
    inner join film_actor using(actor_id)
    inner join film using(film_id)
    where film.title = "hreo";

    这会返回三个表的全部列,应该只返回需要的列

    select actor.* from actor
    inner join film_actor using(actor_id)
    inner join film using(film_id)
    where film.title = "hreo";
  • c、不要总是取出全部的列
    select * from actor....
  • d、重复查询相同的数据
    例如在用户评论的地方需要查询用户的头像,如果用户多次评论,可能会反复查询这个数据,可以先缓存起来,这样会更好。
2、确认MySQL服务层是否在返回前检索大量超过需要的数据行。

如果查询为了返回结果扫描过多的数据,那么就不合适了,一般看3个指标:

  • a、响应时间

    响应时间分为服务时间和排队时间。这个很难细分,如果是在一个合理的值,那就可以接受

  • b、扫描的行和返回的行

    这个在一定程度上能够说明该查询找到需要的数据效率怎么样。理想的情况下,扫描的行和返回的行是相同的,不过实际中这是很难的,特别是做关联查询时

  • 扫描的行和访问类型

    在explain语句中的type列反应了访问类型。从全表扫描到索引扫描,范围扫描,唯一索引查询,常数引用等,速度从慢到快,扫描的行从大到小。一般我们增加一个合适的索引就可以很高效了。

    CREATE TABLE `emp5` (
    `id` int(11) NOT NULL DEFAULT '0',
    `name` varchar(100) NOT NULL,
    `job` varchar(100) NOT NULL,
    `num1` int(10) DEFAULT NULL,
    `num2` int(10) DEFAULT NULL,
    `num3` int(10) DEFAULT NULL,
    `job_num` int(10) DEFAULT NULL,
    `d` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `job_num` (`job_num`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


这里说明使用了索引类型,如果去掉了索引


访问类型为全表扫描(ALL)

3、一般MySQL能够使用如下三种方式应用where,从好到坏依次是:
  • a、在索引中使用where过滤不匹配的数据,引擎层完成。
  • b、使用索引覆盖扫描,在Extra中出现了Using index,直接从索引中过滤掉不需要的记录,服务层完成,不需回表取数据。
  • c、从数据表返回数据,然后过滤,在Extra中出现了Using where ,在服务层完成。

二、重构查询

一个复杂查询还是多个简单查询,是否需要将一个复杂的查询分成多个简单的查询,这是一个需要好好衡量的问题了。

1、切分查询

删除数据就是一个很好的例子。定期的清楚大量的数据,可能需要锁住大量的数据,占满整个事务,耗尽资源,阻塞很多小的查询,切分是一个很好的办法。比方说:把如下的句子

delete from message where create_time < date_sub(now(),interval 3 month);

换成如下:

rows_affected=0
do{
    rows_affected = do_query(
    "delete from message where create_time < date_sub(now(),interval 3 month limit 10000")
    )
}while rows_affected > 0
一次删除10000行,影响就会很小,压力就会分担开来了。
2、分解关联查询

把关联查询进行分解,例如下面的查询:

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 = 'hreo';

分解为:

select * from tag where tag = 'hreo';
select * from tag_post where tag_id=1234;
select * from post where id in (123,546,432);

乍一看,我们好像复杂化了,但是分解后还是有很多好处的,有的时候我们的却是需要这样做的 。

  • a、让缓存的效率更高,如果第一个查询的结果已经缓存了,那么就可以跳过第一个查询,另外对一MySQL的查询缓存Query Cache来说,如果关联的表发生了修改,就无法使用缓存了,拆分后,那么一个表的改变不会影响其他表的缓存。
  • b、单个查询可以减少锁的竞争。
  • c、在应用层做关联,有更好的扩展性。
  • d、可以减少冗余记录的查询,因为数据库关联查询时,可能需要重复的访问一部分数据。
  • e、这个相当于实现了哈希关联,而不是MySQL的嵌套循环关联,某些时候哈希关联效率高很多,这点以后会有介绍。这个效果,在负载均衡,或者数据分布在不同的数据库是更明显。

以上是今天为大家带来的内容,如果有任何问题,大家也可以添加以下QQ群参与问题的讨论。
  • Ansible中文权威群:372011984(已满)
  • AWK&SED企业实战: 260039357
  • docker企业架构实践:491533668
  • Jumpserver交流群 :399218702
  • AAnsible中文权威-2号群:486022616

关于我们


 
运维部落 更多文章 Nginx+FPM结构模型剖析及优化 图解数字签名和数字证书 MongoDB集群解决方案-分片技术 Percona XtraBackup热备份实践 实战自制Linux操作系统
猜您喜欢 Linux内核中从进程PID到容器PID Namespace设计之演变及实现 当我们在谈论前端加密时,我们在谈些什么【岂安低调分享】 用户故事驱动的敏捷开发 – 1. 规划篇 淘宝首页性能优化实践 Google性能专家Ilya Grigorik:速度是我们的核心信条