MySQL(二) —— MySQL性能优化之 SQL语句优化

小鱼儿 2022-05-15 15:21 435阅读 0赞

# 　　　　　　　　　　SQL语句优化 #

## ##

### MySQL优化的目的 ###

1、**避免**出现页面访问**错误**：或由于数据库连接超时 timeout 产生页面5xx错误；或由于慢查询造成页面无法加载；或由于阻　　      塞造成数据无法提交；

2、增加**数据库**的**稳定性**：避免由于低效查询引起的数据库问题；

3、优化**用户体验**：提高页面访问的流畅度。

## ##

### 一、SQL语句优化（20种） ###

**1、**使用 **EXPLAIN** 关键字检测 **SELECT** 查询。语法格式：**EXPLAIN + SELECT 语句**

EXPLAIN  SELECT 可以看到SQL的执行计划，知道MySQL如何处理该SQL语句，这样可以帮助我们分析 SELECT 查询语句或是表结构的性能瓶颈。查看 EXPLAIN 的查询结果可以知道索引主键被如何利用、数据表如何被搜索和排序……等信息。  
　　举例：

**mysql>  EXPLAIN SELECT COUNT(1) FROM account.user\_info WHERE channel='mmo\_no\_platform' AND ** 　　　　　　　　　　**serverid='5020' AND id>=8；**

EXPLAIN 查询结果分析：

select\_type：查询类型

table：查询的表

type：连接的类型。好→差：const、eq\_reg、ref、range、index、ALL。

possible\_keys：可能使用到的索引。若为NULL，则无可能索引；

key：实际使用的索引（主键）。若为NULL，则无使用索引；

key\_len：使用的索引的长度。在不损失精确性的情况下，索引长度越短越好。

ref：被使用的索引的列，可能是一个常数

rows：MySQL认为必须检查的用来返回请求数据的行数

Extra：当有 使用文件排序 Using filesort  或者  使用临时表 Using temporary 时，则需要优化。

**2、**使用 **查询缓存** 优化查询

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

将`query_cache_size = 32M` 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：

1：SELECT username FROM user WHERE    signup\_date >= CURDATE()  
2：SELECT username FROM user WHERE    signup\_date >= '2014-06-24‘

![1555214649-0.jpg][]

上面两条SQL语句的差别就是CURDATE()，MySQL的查询缓存对这个函数不起作用。所以，像NOW()和RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

Count() 和MAX() 的优化方法

Count(\*)和Count(id)的区别：Count(\*)会计入id=NULL的行数，得到的结果可能会比Count(id)的值大。

mysql> EXPLAIN SELECT MAX(createdate) FROM account.user\_info;

建立索引：mysql> create index createdate on account.user\_info(createdate)；

子查询的优化：

通常情况下，需要把子查询优化为 join 连接查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。可以使用distinct 关键字进行去重查询。

group by 查询的优化：避免出现使用文件排序 Using filesort  或者  使用临时表 Using temporary 的情况。在子查询内部增加过滤条件，而不是在外部加过滤条件group by。

LIMIT查询的优化：limit 常用于分页处理，时常会伴随Order By从句使用，因此大多时候会使用 file sorts ，这样会造成大量的IO问题。优化方式1、使用主键或有索引的列进行Order By 操作；2、当只要一行数据时使用LIMIT 1

使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

3、当只要一行数据时使用LIMIT 1

4、为搜索字段建立索引

5、在join表的时候使用相当类型的列，并将其索引

6、切记不要使用ORDER BY RAND()

7、避免使用SELECT \*

8、永远为每张表设置一个ID主键

9、使用ENUM而不是VARCHAR

10、尽可能的不要赋值为NULL

11、固定长度的表会更快

12、垂直分割

数据库结构优化：

11选择合适的数据类型

1、使用可以存下对应数据的最小的数据类型；

2、使用简单的数据类型。如int要把varchar类型在mysql处理上简单；

使用int来存储日期时间，利用FROM\_UNIXTIME()，UNIX\_TIMESTAMP()两个函数来进行转换。

3、尽可能的使用not null 定义字段；

4、尽量少用test类型，必要用时最好考虑分表。

22表的垂直拆分：

所谓的垂直拆分，就是把原来一个很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下三个原则进行：

1.把不常用的字段单独存到一个表中；

2。把大字段（如text类型字段）独立存放到一个表中；

3.把经常一起使用的字段放到一个表中。

33表的水平拆分：表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

** 数据库结构优化**

·  **表的水平拆分**  
常用的水平拆分方法为：  
1.对 customer\_id进行 hash运算，如果要拆分成5个表 则使用mod(customer\_id,5)取出0-4个值  
2.针对不同的 hashID 把数据存到不同的表中。  
**挑战:**  
1.跨分区表进行数据查询  
2.统计及后台报表操作

13、拆分大的DELETE或INSERT

14、越小的列会越快

15、选择正确的存储引擎

16、小心永久链接

[https://www.cnblogs.com/zhaobingqing/p/7071331.html][https_www.cnblogs.com_zhaobingqing_p_7071331.html]

[https://www.cnblogs.com/daxian2012/articles/2767989.html][https_www.cnblogs.com_daxian2012_articles_2767989.html]

[https://blog.csdn.net/kaka1121/article/details/53395587?utm\_source=blogxgwz1][https_blog.csdn.net_kaka1121_article_details_53395587_utm_source_blogxgwz1]

[https://www.cnblogs.com/xwgcxk/p/8855469.html][https_www.cnblogs.com_xwgcxk_p_8855469.html]

[https://blog.csdn.net/yananwang/article/details/7030029?utm\_source=blogkpcl7][https_blog.csdn.net_yananwang_article_details_7030029_utm_source_blogkpcl7]

[https://blog.csdn.net/zh521zh/article/details/76273383?utm\_source=blogxgwz0][https_blog.csdn.net_zh521zh_article_details_76273383_utm_source_blogxgwz0]

[https://blog.csdn.net/liuyanqiangpk/article/details/79827239?utm\_source=blogxgwz2][https_blog.csdn.net_liuyanqiangpk_article_details_79827239_utm_source_blogxgwz2]

[1555214649-0.jpg]: /images/20220503/0d5bf934df0b4c19be42156903e0aa75.png
[https_www.cnblogs.com_zhaobingqing_p_7071331.html]: https://www.cnblogs.com/zhaobingqing/p/7071331.html
[https_www.cnblogs.com_daxian2012_articles_2767989.html]: https://www.cnblogs.com/daxian2012/articles/2767989.html
[https_blog.csdn.net_kaka1121_article_details_53395587_utm_source_blogxgwz1]: https://blog.csdn.net/kaka1121/article/details/53395587?utm_source=blogxgwz1
[https_www.cnblogs.com_xwgcxk_p_8855469.html]: https://www.cnblogs.com/xwgcxk/p/8855469.html
[https_blog.csdn.net_yananwang_article_details_7030029_utm_source_blogkpcl7]: https://blog.csdn.net/yananwang/article/details/7030029?utm_source=blogkpcl7
[https_blog.csdn.net_zh521zh_article_details_76273383_utm_source_blogxgwz0]: https://blog.csdn.net/zh521zh/article/details/76273383?utm_source=blogxgwz0
[https_blog.csdn.net_liuyanqiangpk_article_details_79827239_utm_source_blogxgwz2]: https://blog.csdn.net/liuyanqiangpk/article/details/79827239?utm_source=blogxgwz2