Linux培训
美国上市Linux培训机构

400-111-8989

热门课程

如何针对具体的SQL做优化?

  • 时间:2016-12-02 15:21
  • 发布:linux培训
  • 来源:每日一问

问题:如何针对具体的SQL做优化

答:

使用Explain分析SQL语句执行计划

MySQL> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789;
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_online_group_records | ALL  | NULL          | NULL | NULL    | NULL |   47 | Using where |
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

如上面例子所示,重点关注下type,rows和Extra:

type:使用类别,有无使用到索引。结果值从好到坏:… > range(使用到索引) > index > ALL(全表扫描),一般查询应达到range级别

rows:SQL执行检查的记录数

Extra:SQL执行的附加信息,如”Using index”表示查询只用到索引列,不需要去读表等

使用Profiles分析SQL语句执行时间和消耗资源

MySQL> set profiling=1; (启动profiles,默认是没开启的)
MySQL> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (执行要分析的SQL语句)
MySQL> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00043250 | select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789 |
+----------+------------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL> show profile cpu,block io for query 1; (可看出SQL在各个环节的耗时和资源消耗)
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
...
| optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000076 | 0.000000 |   0.000000 |            0 |             0 |
...

SQL优化的技巧 (只提一些业务常遇到的问题)

最关键:索引,避免全表扫描。

对接触的项目进行慢查询分析,发现TOP10的基本都是忘了加索引或者索引使用不当,如索引字段上加函数导致索引失效等(如where UNIX_TIMESTAMP(gre_updatetime)>123456789)

+----------+------------+---------------------------------------+
| Query_ID | Duration   | Query                                 |
+----------+------------+---------------------------------------+
|        1 | 0.00024700 | select * from mytable where id=100    |
|        2 | 0.27912900 | select * from mytable where id+1=101  |
+----------+------------+---------------------------------------+

另外很多同学在拉取全表数据时,喜欢用select xx from xx limit 5000,1000这种形式批量拉取,其实这个SQL每次都是全表扫描,建议添加1个自增id做索引,将SQL改为select xx from xx where id>5000 and id;

+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.00415400 | select * from mytable where id>=90000 and id91000 |
|        2 | 0.10078100 | select * from mytable limit 90000,1000              |
+----------+------------+-----------------------------------------------------+

合理用好索引,应该可解决大部分SQL问题。当然索引也非越多越好,过多的索引会影响写操作性能

只select出需要的字段,避免select

+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.02948800 | select count(1) from ( select id from mytable ) a   |
|        2 | 1.34369100 | select count(1) from ( select * from mytable ) a    |
+----------+------------+-----------------------------------------------------+

尽量早做过滤,使Join或者Union等后续操作的数据量尽量小

把能在逻辑层算的提到逻辑层来处理,如一些数据排序、时间函数计算等

…….

PS:关于SQL优化,已经有足够多文章了,所以就不讲太全面了,只重点说自己1个感受:索引!基本都是因为索引!

上一篇:MySQL负载高时,如何找到是由哪些SQL引起的?
下一篇:MySQL如何做主从数据同步?

MySQL负载高时,如何找到是由哪些SQL引起的?

选择城市和中心
贵州省

广西省

海南省