写在前面:前面学习了通过 explain
查看 SQL
语句的执行计划,然后对其进行必要的索引优化。那么我们又该怎么获取到这些 SQL
语句呢🤔🤔🤔?
获取慢SQL的方法
1:开启慢查询日志,设置 阈(yù)值
,比如超过 3 秒就定义为慢SQL,然后进行记录
2:观察。在指定的环境(生产)运动指定时间的应用程序,看看产生慢SQL的情况。
3:使用 explain + 慢SQL
查看SQL语句的执行计划进行分析。
4:show profile
查询 sql 语句在数据库服务器中的执行细节和生命周期情况。
5:MySQL 数据库服务器参数调优(这个基本和我们没有半毛钱关系)
优化原则之 小表驱动大表
即是使用小的结果集驱动大的结果集。
- 当 B 表的数据集小于 A 表的数据集时,使用
in
优于 exists
。
1 2 3 4 5 6 7 8
| select * from A where id in(select id from B)
for select id from B
for select * from A where A.id = B.id
|
- 当 A 表的数据集小于 B 表的数据集时,使用
exists
优于 in
。
1 2 3 4 5 6
| select * from A where exists (select 常量(eg:1) from B WHERE B.id = A.id);
for select id from A
for select * from B where A.id = B.id
|
当子查询需要用到主查询的数据的时候,主查询一次就执行一次子查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| mysql> select * from websites;
+ | id | name | url | alexa | country | + | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | + 5 rows in set (0.00 sec)
mysql> select * from access_log;
+ | aid | site_id | count | date | + | 1 | 1 | 45 | 2016-05-10 | | 2 | 3 | 100 | 2016-05-13 | | 3 | 1 | 230 | 2016-05-14 | | 4 | 2 | 10 | 2016-05-14 | | 5 | 5 | 205 | 2016-05-14 | | 6 | 4 | 13 | 2016-05-15 | | 7 | 3 | 220 | 2016-05-15 | | 8 | 5 | 545 | 2016-05-16 | | 9 | 3 | 201 | 2016-05-17 | | 10 | 6 | 2011 | 2016-05-17 | | 11 | 7 | 2012 | 2016-05-18 | | 12 | 8 | 2013 | 2016-05-19 | + 12 rows in set (0.00 sec)
mysql> select * from websites where id in (select site_id from access_log); + | id | name | url | alexa | country | + | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | + 4 rows in set (0.00 sec)
mysql> explain select * from websites where id in (select site_id from access_log); + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | SIMPLE | websites | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 4 | runoob.websites.id | 1 | 100.00 | NULL | | 2 | MATERIALIZED | access_log | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | NULL | + 3 rows in set, 1 warning (0.00 sec)
mysql> select * from websites where exists (select 1 from access_log where websites.id=access_log.site_id); + | id | name | url | alexa | country | + | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | + 4 rows in set (0.00 sec)
mysql> explain select * from websites where exists (select 1 from access_log where websites.id=access_log.site_id); + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + | 1 | PRIMARY | websites | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | access_log | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 10.00 | Using where | + 2 rows in set, 2 warnings (0.01 sec)
|
根据小表驱动大表的原则,上面的两个 SQL语句
第二个使用 exists
更合适