MySQL 语句优化

大家好，今天归纳一些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)

2. 避免函数索引
   由于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)

4. 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)

5. 避免数据的不一致

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)

6. 分组统计可以禁止排序
   默认情况下，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)

7. 禁止不必要的排序和一些条件
8. 批量插入数据，更快一些。

有时间在补充啊！

本文地址：https://blog.csdn.net/weixin_36851500/article/details/85937283