为了账号安全,请及时绑定邮箱和手机立即绑定

PHP面试之MySQL查询优化

标签:
PHP MySQL 面试

[TOC]

请简述项目中优化MySQL语句执行效率的方法,从哪些方面入手,SQL语句性能如何分析?

分析查询速度

  • 记录慢查询日志
    分析查询日志,使用pt-query-digest工具进行分析

  • 使用show profile
    set peofiling=1; # 服务器上执行的所有语句会检测消耗的时间、存到临时表中
    show profiles # 所有的SQL语句执行记录
    show profile for query 临时表ID # 查执行ID的SQL语句

mysql> set profiling=1;
Query OK, 0 rows affected

mysql> select * from user;
+----+------+-----+| id | name | age |+----+------+-----+|  1 | Jack |  23 |+----+------+-----+1 row in set

mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+| Query_ID | Duration   | Query                                                                                                                           |+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+  
|       1 |   8.675E-5 | set profiling=1                                                                                                                 ||       2|   0.000197 | select * from user                                                                                                              |+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+10 rows in set

mysql> show profile for query 2;
+----------------------+----------+| Status               | Duration |
+----------------------+----------+
| starting             | 4.3E-5   || checking permissions | 7E-6     |
| Opening tables       | 1.8E-5   || System lock          | 1E-5     |
| init                 | 1.4E-5   || optimizing           | 4E-6     |
| statistics           | 3.1E-5   || preparing            | 1E-5     |
| executing            | 2E-6     || Sending data         | 1E-5     |
| end                  | 3E-6     || query end            | 2E-6     |
| closing tables       | 5E-6     || freeing items        | 3.5E-5   |
| logging slow query   | 3E-6     || cleaning up          | 2E-6     |
+----------------------+----------+
16 rows in set
  • show status
    show status会返回一些计数器,show global status查看服务器级别的所有计数。

  • show processlist
    观察是否有大量的线程处于不正常的状态或特征。

  • explain
    分析单条的SQL语句

mysql> explain select * from user;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | system | NULL          | NULL | NULL    | NULL |    1 |       |+----+-------------+-------+--------+---------------+------+---------+------+------+-------+1 row in set

explain它有一个别名desc,所以使用explain select * from user;查询到的结果与上面的一样。

优化查询过程中的数据访问

  • 数据太多会导致查询性能下降。

  • 确定应用程序是否在检索大量超过需要的数据,可能太多行或列。

  • 确认MySQL服务器是否在分析大量不必要的数据行

避免使用以下SQL语句

  • 查询不需要的记录,使用limit解决

  • 多表管理返回全部列,需要制定列,如A.id、B.name等

  • 总是取出全部列,select *会让优化器无法完成索引覆盖扫描优化

  • 重复查询相同的数据,可以使用缓存,下次直接读取缓存

是否在扫描额外的记录
使用explain来进行分析,如果发现查询需要扫描大量的数据但是只返回少数的行,可以通过如下技巧去优化:

  • 使用索引覆盖扫描,把所有的列都放在索引中。

  • 改变数据库和表的结构,修改数据表范式

  • 重写SQL语句,让优化器可以以最优的方式执行查询

优化长难的查询语句

使用尽可能小的查询是好的,但有时将一个大的查询分解成多个小的查询是很有必要的。

  • 切分查询
    将一个大的查询分为多个小的相同的查询
    一次性删除1000万的数据比一次删除1万,暂停一会儿在执行删除1万条数据要较少更多的服务器开销。

  • 分解关联查询
    可以将一条关联语句分解成多个SQL语句执行
    让缓存的效率更高
    执行单个查询可以减少锁的竞争
    在应用层做关联可以更容易对数据库进行拆分

优化特定类型的查询语句

优化count()查询

  • count(*)占用的*会忽略所有的列,直接统计所有的列数,因此不要使用count(列名)

  • MyISAM中没有任何where条件的count(* )查询速度非常快

  • 有where条件时,MyISAM的count统计不一定比其他表引擎快

优化:

  • 可以使用explain查询近似值,用近似值代替count(*)

  • 增加汇总表

  • 使用缓存

优化关联查询

  • 确定on或者using子句的列上有索引;

  • 确保group byorder by中只有一个表中的列,这样MySQL才有可能使用索引;

优化子查询

尽量使用关联查询替代

优化group bydistinct

  • 使用索引来优化

  • 关联查询中,使用标识列(主键列)进行分组的效率会更高

  • 如果不需要order by,进行group by时使用order by NULL,MySQL不会进行文件排序

  • with rollup超级剧和,可以挪到应用程序处理

优化limit子句

limit偏移量大的时候,查询效率较低。

方案:

  • 记录上一次查询的最大ID,下次查询时直接根据ID来查询(还是使用limit查询,不过加一个where条件,id > ID)

优化union

union all 的效率高于union



作者:openoter
链接:https://www.jianshu.com/p/ab958a4823d1

点击查看更多内容
1人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消