MYSQL-查询优化

2019-04-25  本文已影响0人  AGEGG

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

1.分析查询速度

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
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 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;查询到的结果与上面的一样。

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

避免使用以下SQL语句
是否在扫描额外的记录

3.优化长难的查询语句

MYSQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多
使用尽可能小的查询是好的,但有时将一个大的查询分解成多个小的查询是很有必要的。

切分查询

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

分解关联查询

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

4.优化特定类型的查询语句

优化count()查询

count()占用的会忽略所有的列,直接统计所有的列数,因此不要使用count(列名)
MyISAM中没有任何where条件的count(* )查询速度非常快
有where条件时,MyISAM的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。
union all 会把重复数据筛选出来,但查询效率比union高

上一篇下一篇

猜你喜欢

热点阅读