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

最新推荐文章于 2023-07-14 22:55:28 发布

weixin_43751710

最新推荐文章于 2023-07-14 22:55:28 发布

阅读量114

点赞数 1

分类专栏：数据库文章标签： MySQL 优化

数据库专栏收录该内容

39 篇文章 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语句一样。
如下图：
在这里插入图片描述
当然，这时在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;

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

2、show full processlist

show full processlist

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