MySQL(二) —— MySQL性能优化之 SQL语句优化_性能测试中mysql优化时,查询列表性能测试,为啥会出现2条sql语句-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41677196/article/details/83542405

　　　　　　　　　　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；

mysql> EXPLAIN SELECT COUNT(1) FROM account.user_info WHERE channel='mmo_no_platform' AND serverid='5020' AND id>=8;
+----+-------------+-----------+-------------+--------------------------+------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table     | type        | possible_keys            | key              | key_len | ref  | rows | Extra                                                       |
+----+-------------+-----------+-------------+--------------------------+------------------+---------+------+------+-------------------------------------------------------------+
|  1 | SIMPLE      | user_info | index_merge | PRIMARY,channel,serverid | serverid,channel |   36    | NULL |  562 | Using intersect(serverid,channel); Using where; Using index |
+----+-------------+-----------+-------------+--------------------------+------------------+---------+------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

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‘

上面两条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> EXPLAIN SELECT MAX(createdate) FROM account.user_info;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user_info | ALL  | NULL          | NULL | NULL    | NULL | 1828 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


mysql> SELECT MAX(createdate) FROM account.user_info;
+---------------------+
| MAX(createdate)     |
+---------------------+
| 2018-11-01 14:34:39 |
+---------------------+
1 row in set (0.00 sec)

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

mysql> CREATE INDEX createdate on account.user_info(createdate);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT MAX(createdate) FROM account.user_info;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

子查询的优化：

通常情况下，需要把子查询优化为 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主键