MySQL语句优化策略（待完善）

╰+攻爆jí腚メ 2022-01-20 22:37 360阅读 0赞

# MySQL语句基础优化策略： #

**第一、不使用select \* ……这样的语句。**  
老老实实将需要的字段写出来，如select name……这样的，除非是整张表的数据字段都要使用。

**第二、永远不要讲or作为顶层条件。**  
有or的放在括号（）里，顶层条件必须用and。如：where a=1 and b=3 and … or ccc=33 … ， 这种是等于 where ( a=1 and b=3 and …) or ( ccc=33 … ) ,or 处理条件树的顶端，是绝对不行的。应该是where a=1 and b=3 and … (ccc=32 or ccc=33 … )。如果有where a=1 or a=2这样的，将or换成in。

**第三、数据量大的表，禁止直接使用分页。**  
如：limit 100000,100。它是查询100000行之后的100条记录。它依然是扫描100000之后，再获取100行数据的，还有就是一般的语句都有where语句，比如select name form table\_a where age=18 limit 100000,100。它会判断每条记录是不是where age=18，然后累计100000条之后，再取100条。  
当然，如果没有where的条件查询语句，limit 100000,100是快一些（不过也快不了多少），但是日常的查询中应该不存在不要where语句的查询吧。  
（如果非要使用分页，也有相应的优化方法，但是不能直接使用分页）

**第四、索引中不要有函数。**  
比如 a 有索引，那么用了函数比如sub(a,…) =1是不会走索引的。但是如果用a=sub(xxx) 是没问题的，因为锁引拿到的就是一个已知值 。

**第五、索引尽量重用，不要无限制建索引。**  
比如a b 需要建一个联合索引，a又需要建一个独立索引，那么a的独立索引是不需要建的，因为单独查a的话，也是会用的联合索引的  
联合索引的匹配顺序是从前到后，比如a b c 建议联全索引，查a, ab, ac, abc 都会走索引，必须要有a；但bc， cab 等无a的字段就不会走索引。

# 深入优化策略——分页和排序 #

**第一、排序优化。**  
MySQL中排序优化的核心点还是使用索引，使用索引的话排序的效率就会高一些。  
MySQL支持两种排序方式：Filesort和Index。  
Index的排序方式自然是效率高的排序了，如果出现Filesort的排序，那就是需要优化的排序了。  
排序order by如果想要使用索引，其实是和where语句一样。  
如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]  
当然，这时在MySQL中排序的情况下，其实我们还有一种选择，就是从数据库中取出数据后，在应用程序中进行排序（比如使用java的集合类）。  
当MySQL中有相应的索引的时候，直接在数据库中排序好了传给应用程序（比如java）自然是最好的。但是如果数据库的负载很高，而且有很多一样的并发排序查询，而且也没有相应的索引的时候，在数据库中排序好了再传给应用程序就有些得不偿失了，这时候，把排序工作交给应用程序是最好的。  
下面列举一些在数据库和应用程序中排序的场景。

**在数据库中排序：**

1、MySQL中已经存在这个排序的索引；  
2、MySQL中数据量较大，而结果集需要其中很小的一个子集，比如1000000行数据，取TOP10；  
3、复杂排序条件，比如按照几个字段进行排序，数据库就非常有优势了。  
4、对于一次排序、多次调用的情况，比如统计聚合的情形，可以提供给不同的服务使用，那么在MySQL中排序是首选的。另外，对于数据深度挖掘，通常做法是在应用层做完排序等复杂操作，把结果存入MySQL即可，便于多次使用。

**在应用程序中排序：**

1、数据源不在MySQL中，存在硬盘、内存或者来自网络的请求等；  
2、数据存在MySQL中，量不大，而且没有相应的索引，此时把数据取出来用PHP排序更快；  
3、数据源来自于多个MySQL服务器，此时从多个MySQL中取出数据，然后在PHP中排序更快；

**第二、分页优化**

策略：利用索引，加条件判断  
一般查询大批量数据按照时间区间查询，比如一次查几分钟数据出来，按时间递增；或者如果主键是自动（或人为）递增的也可以按这个limit 0,N，第二次查询加一个条件 id>上次最后一个再limit 0,N，保证mysql每次只扫描N+行。  
这样，像翻多少页都是一样快，因为它利用了时间这个锁引，但是不能满足的就是跳页。  
例如：  
表table\_a中有1000万条数据，我要查700万条数据之后的10条。如果直接：

select name,age,sex from table_a limit 7000000,10

会需要很久的时间，  
但如果我加上一个条件where id>7000000（假设id是自增的，从1开始，且是有主键索引的），变成：

select name,age,sex from table_a where id>7000000 limit 10

那么查询会非常快，因为有id上索引的帮助。

优化时，常用的SQL语句：  
**1、explain**  
例如：

explain select * from xuser order by xuser_id;

结果为：  
![在这里插入图片描述][20190606195906213.png]  
后面的Extra就说明了排序使用了什么方法  
可见Null（或者是Using index condition），就是正常的，下面Using filesort就是不正常的（需要优化）。  
![在这里插入图片描述][20190606195921635.png]

**2、show full processlist**

show full processlist

结果为：  
![在这里插入图片描述][2019060619592966.png]  
这条语句就是后悔药，找出线上正在运行的有哪些SQL。  
Time是已经执行的时长（没结束，结束就抓不到了）。  
注意：  
a、Time频繁大于0的客户端查询基本上都是有问题的，因为客户端触发，风险（并发）不可控。如果是服务器自己触发可视情况而定。  
b、多次show出同一个SQL，那么这种SQL要重点关注和优化。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]: /images/20220121/e57ac3ae37d14c9a8ce9df1f2c938d47.png
[20190606195906213.png]: /images/20220121/5205b1c7eb9e432d9a7c1aebd9b4104d.png
[20190606195921635.png]: /images/20220121/3278ae7f06784622899482d51b4a37d3.png
[2019060619592966.png]: /images/20220121/ceda934cabc542fcb08cc0b7fab4a8c5.png