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 affectedmysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|1 | Jack |23 |
+----+------+-----+
1 row in setmysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration| Query|
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+
|1 |8.675E-5 | set profiling=1|
|2|0.000197 | select * from user|
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+
10 rows in setmysql> 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服务器是否在分析大量不必要的数据行
- 查询不需要的记录，使用
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 by和order by中只有一个表中的列，这样MySQL才有可能使用索引；
优化
group by和distinct
- 使用索引来优化
- 关联查询中，使用标识列（主键列）进行分组的效率会更高
- 如果不需要
order by，进行group by时使用order by NULL，MySQL不会进行文件排序 -
with rollup超级剧和，可以挪到应用程序处理
limit子句
limit偏移量大的时候，查询效率较低。方案：
- 记录上一次查询的最大ID，下次查询时直接根据ID来查询（还是使用limit查询，不过加一个where条件，id > ID）
union
union all 的效率高于union。推荐阅读
- PMSJ寻平面设计师之现代(Hyundai）
- 杜月笙的口才
- Linux下面如何查看tomcat已经使用多少线程
- 皮夹克
- thinkphp|thinkphp 3.2 如何调用第三方类库
- CGI，FastCGI，PHP-CGI与PHP-FPM
- 解读《摩根集团》（1）
- 绘本与写作
- 麦田社群
- 面对苦难——如何化解