MySQL性能优化篇

一、优化思路

开启慢查询日志，找出问题SQL
查看SQL的执行计划
优化SQL
使用show profile[s] 查看问题SQL的性能使用情况
调整操作系统参数
升级服务器硬件

二、慢查询日志

mysql> show variables like 'slow%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_launch_time    | 2                                                |
| slow_query_log      | OFF                                              |
| slow_query_log_file | C:\Program Files\MySQL\data\ZB-PF1H2APD-slow.log |
+---------------------+--------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

　　1. 临时开启慢查询

#打开慢查询
set global slow_query_log = ON;
#设置阈值时间
set global long_query_time = 1;

　　2. 修改配置文件

[mysqlId]
slow_query_log=ON
long_query_time=1

　　3. 分析慢查询日志的工具

　　　　MySQL自带的mysqldumpslow。请参考：https://blog.csdn.net/sunyuhua_keyboard/article/details/81204020

三、查看执行计划

　　explain + sql语句。前排提示：explain预估row会忽视limit，分析带limit的SQL语句，请使用 SHOW SESSION STATUS LIKE "Handler%";

mysql> explain select * from t where a = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)

　　3.1. id：编号

　　　　用于区分多个查询。

　　　　1. 有查询id相同：包含关联查询，执行顺序由上到下

　　　　2. 有查询id不同：包含子查询，id号自增，id越大，优先级越高越先执行

　　3.2. select_type：查询类型

　　　　1. simple：简单select查询。有关联查询时，外层查询为simple

　　　　2. primary：包含子查询或者合并查询时，最外层查询为primary

　　　　3. union：包含合并查询，内部查询为union

　　　　4. union result：包含合并查询，并且使用的是union，需要对结果进行去重，排序，最后返回查询结果，这个查询为union result

　　　　5. dependent union：union/union all之后的select为dependent union，并且该查询依赖外部查询。

mysql> explain select a from t as t3 where a in(select a from t as t1 union select a from t as t2);
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY            | t3         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where     |
|  3 | DEPENDENT UNION    | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

　　　　上述查询似乎没有显式依赖，实际是MySQL会将in优化成exists

mysql> explain select a from t t3 where exists(select 1 from t t1 where t1.a = t3.a union select 1 from t t2 where t2.a = t3.a);
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY            | t3         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where     |
|  3 | DEPENDENT UNION    | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
4 rows in set, 3 warnings (0.00 sec)

　　　　6. subquery：在select或where中包含子查询，且结果不依赖于外部查询

mysql> explain select * from t where a = (select a from t where a = 1);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where |
|  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

　　　　7. dependent subquery：子查询中的第一个select，取决于外部的查询。如下子查询的筛选需要先查出主查询。这种查询类型说明SQL性能差。但是在MySQL5.7.26版本下测试好像被优化了。

mysql> explain select * from t where a in (select a from test where test.b = t.b);
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | idx_a         | NULL | NULL    | NULL        | 1024 |   100.00 | Using where; Start temporary |
|  1 | SIMPLE      | t     | NULL       | ref  | b             | b    | 5       | test.test.b |    1 |    10.00 | Using where; End temporary   |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)

　　　　换一个查询试试，很奇怪哦

mysql> explain select(select a from test where test.b = t.b) from t;
+----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | t     | NULL       | index | NULL          | b    | 5       | NULL | 49410 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | test  | NULL       | ALL   | NULL          | NULL | NULL    | NULL |  1024 |    10.00 | Using where |
+----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

　　　　8. derived：from语句中出现的子查询。已被优化为 select * from t;

mysql> explain select * from (select * from t)t;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49410 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

　　3.3 table：查询使用的表

　　　　1. 如果使用了别名，显示别名

　　　　2. 如果不涉及对表的操作，显示null

　　　　3. 如果使用的是临时表，表名为<derived N>，N代表的是id，即该表是由这条查询语句N生成的

　　　　4. 如果是union查询结果产生的临时表，表名<union M,N>，表示由查询语句M和查询语句N合并查询产生的

　　3.4 type：查询的连接类型或者访问类型

　　　　system>const>eq_ref>ref>range>index_merge>index>ALL

　　　　1. const：使用唯一索引或者主键查询返回一行记录的等值查询

　　　　2. eq_ref：使用唯一索引或主键进行等值关联查询，InnoDB在数据量小时，会退化成ALL

mysql> explain select * from t t1 left join t t2 on t1.a = t2.a;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      |   20 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

　　　　3. ref：跟eq_ref的区别是，使用非唯一索引

mysql> explain select * from t t1 left join t t2 on t1.b = t2.b;
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL      |   20 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | ref  | b             | b    | 5       | test.t1.b |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

　　　　4. range：索引范围查找，常见于>,<,is null,between,in,like等运算查询中

　　　　5. index_merge：MySQL5.7版本的优化，在查询中可以使用多个单独索引。看到这个说明需要优化索引了

　　　　6. index：在查询结果中使用了索引值，即索引覆盖

　　　　7. all：全表扫描

　　3.5 possible_keys：可能使用的索引

　　3.6 key：实际使用的索引

　　3.7 key_len：使用到的索引的长度

　　　　组合索引不一定用到所有的索引，根据长度可以判断

　　　　varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

　　　　varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

　　　　char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

　　　　char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)

　　　　bigint长度是8bytes，int长度是4，typeint长度是1，smallint长度2，middleint长度是3。

　　　　注意int(10)设置的是显示宽度，假如不足10位则补零，int类型存储的范围固定是-2的31次方到2的31次方。

　　3.8 ref

　　　　如果是常数等值查询，显示const。如果是关联查询，被驱动表会显示驱动表的关联字段。

　　3.9 row：结果行数，是一个估计值

　　3.10 extra

　　　　1. Using filesort：产生了无法使用索引的排序（请参考：https://www.jianshu.com/p/069428a6594e）

　　　　2. Using temporary：使用临时表保存中间结果。常见于排序和分组

　　　　3. Using index：覆盖索引，筛选条件从索引第一列开始

　　　　4. Using where：没有覆盖索引，筛选条件不是索引第一列开始或第一列的范围查找

　　　　5. Using where，Using index：覆盖索引，但筛选条件是索引，但不是索引第一列或是第一列的范围查找。即代表在索引树上进行了扫描

　　　　6. Using index condition：索引下推。没有覆盖索引，筛选条件从索引第一列开始

　　　　7. NULL：没有索引覆盖，筛选条件从索引第一列开始

　　　　参考：https://www.cnblogs.com/ivy-zheng/p/11121360.html

四、SQL语句优化

　　1. 索引优化，请见索引篇

　　2. LIMIT优化

　　　　1. 对于已知只有一行结果的查询，使用limit 1，使查询尽快中止

　　　　2. 分页查询。

mysql> explain select * from page a inner join (select id from page limit 1000,100) b on a.id = b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  1100 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |     1 |   100.00 | NULL        |
|  2 | DERIVED     | page       | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 11747 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+

　　　　还有一种方法是在程序缓存上一次查询的最大id，下次分页查询时，用id做查询条件，防止id不连续的情况，一般查询条件 id + page_size + n。确保查询结果足量包含一页的大小。

　　3. 小表驱动大表(in(小表查询)，exists(大表查询))

　　4. 关联表查询关联的字段需建立索引，并且注意字段的类型需要一致避免做类型转换

五、服务器层优化

　　1. 缓冲区优化，设置缓冲池大小，一般设置为总内存的3/4到4/5。

　　2. 降低磁盘写入次数，关闭通用查询日志、慢查询日志、错误日志。

posted @ 2021-03-25 13:40 walker993 阅读(47) 评论(0) 编辑收藏举报

刷新页面返回顶部

walker993

MySQL性能优化篇

一、优化思路

二、慢查询日志

三、查看执行计划

3.1. id：编号

3.2. select_type：查询类型

3.3 table：查询使用的表

3.4 type：查询的连接类型或者访问类型

3.5 possible_keys：可能使用的索引

3.6 key：实际使用的索引

3.7 key_len：使用到的索引的长度

3.8 ref

3.9 row：结果行数，是一个估计值

3.10 extra

四、SQL语句优化

五、服务器层优化

公告

　　3.1. id：编号

　　3.2. select_type：查询类型

　　3.3 table：查询使用的表

　　3.4 type：查询的连接类型或者访问类型

　　3.5 possible_keys：可能使用的索引

　　3.6 key：实际使用的索引

　　3.7 key_len：使用到的索引的长度

　　3.8 ref

　　3.9 row：结果行数，是一个估计值

　　3.10 extra