我要评论大家好，今天归纳一些SQL优化的问题。每一条都亲测过。以后还会再补充。在实际的操作中可能会更复杂，数据量小的时候，可能你会感觉不对。哈哈哈！这时我们最好开启慢日志查询，在日常的工作中，尽量规范。
1 不使用子查询
子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询
mysql> SELECT t1.order_id FROM doub_order as t1 JOIN doub_order_details as t2 ON t1.order_id = t2.order_id limit 1 ;
+----------+
| order_id |
+----------+
| 412317 |
+----------+
1 rows in set (0.02 sec)
mysql> select order_id from doub_order where order_id = (select order_id from doub_order_details limit 1 );
+----------+
| order_id |
+----------+
| 412317 |
+----------+
1 rows in set (0.04 sec)
- 避免函数索引
由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。
mysql> select count(*) from doub_order where order_c_time>=1543593600 and order_c_time<=1546185600 order by order_id DESC;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 rows in set (0.19 sec)
mysql> select count(*) from doub_order where FROM_UNIXTIME(order_c_time,'%Y-%m-%d') >= date_add(curdate()-day(curdate())+1,interval -1 month) AND FROM_UNIXTIME(order_c_time,'%Y-%m-%d') <=last_day(date_sub(now(),interval 1 month)) order by order_id DESC;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 rows in set (0.35 sec)
3.用IN来替换OR
mysql> select order_id from doub_order where order_id= 643050 or order_id= 643049 or order_id= 643048 or order_id= 643046;
+----------+
| order_id |
+----------+
| 643046 |
| 643048 |
| 643049 |
| 643050 |
+----------+
4 rows in set (0.05 sec)
mysql> select order_id from doub_order where order_id in(643046, 643048, 643049, 643050);
+----------+
| order_id |
+----------+
| 643046 |
| 643048 |
| 643049 |
| 643050 |
+----------+
4 rows in set (0.02 sec)
- LIKE双百分号无法使用到索引
mysql> select order_id from doub_order where user_name like '宫%';
+----------+
| order_id |
+----------+
| 323187 |
| 323361 |
+----------+
30 rows in set (0.30 sec)
mysql> select order_id from doub_order where user_name like '%宫%';
+----------+
| order_id |
+----------+
| 323187 |
| 323361 |
| 567172 |
| 629362 |
+----------+
30 rows in set (0.32 sec)
- 避免数据的不一致
mysql> select order_id from doub_order where order_id = 629362;
+----------+
| order_id |
+----------+
| 629362 |
+----------+
1 rows in set (0.01 sec)
mysql> select order_id from doub_order where order_id = '629362';
+----------+
| order_id |
+----------+
| 629362 |
+----------+
1 rows in set (0.02 sec)
- 分组统计可以禁止排序
默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
mysql> select order_id,count(*) from doub_order_details where 1=1 group by order_id limit 10;
+----------+----------+
| order_id | count(*) |
+----------+----------+
| 107899 | 1 |
| 115008 | 1 |
| 117112 | 1 |
| 117113 | 1 |
+----------+----------+
10 rows in set (0.05 sec)
mysql> select order_id,count(*) from doub_order_details where 1=1 group by order_id order by null limit 10 ;
+----------+----------+
| order_id | count(*) |
+----------+----------+
| 412317 | 1 |
| 117122 | 1 |
| 117121 | 1 |
| 301905 | 1 |
+----------+----------+
10 rows in set (0.03 sec)
- 禁止不必要的排序和一些条件
- 批量插入数据，更快一些。
有时间在补充啊！
本文地址：https://blog.csdn.net/weixin_36851500/article/details/85937283
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论