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

　　　　　　　　　　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主键

　　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/daxian2012/articles/2767989.html

https://blog.csdn.net/kaka1121/article/details/53395587?utm_source=blogxgwz1

　　https://www.cnblogs.com/xwgcxk/p/8855469.html

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/liuyanqiangpk/article/details/79827239?utm_source=blogxgwz2