Mysql优化补充

2020-03-04 本文已影响0人笨鸡

1.查看sql执行频率

1.1.查询当前sql的执行频率

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 19    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

1.2.查询mysql开启后sql的执行频率

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 12    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 519   |
| Com_signal    | 0     |
| Com_update    | 13    |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

1.3.查询innodb的sql影响数据的行数

mysql> show global status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 1401  |
| Innodb_rows_read     | 1990  |
| Innodb_rows_updated  | 24    |
+----------------------+-------+
4 rows in set (0.00 sec)

2.定位低效率执行SQL

2.1.慢查询日志
2.2.show processlist 定位当前sql执行

mysql> show processlist;
+-----+------+-----------------+------------+---------+------+----------+------------------+
| Id  | User | Host            | db         | Command | Time | State    | Info             |
+-----+------+-----------------+------------+---------+------+----------+------------------+
|   2 | root | localhost:54845 | mysql_test | Sleep   | 2990 |          | NULL             |
|   3 | root | localhost:54857 | mysql_test | Sleep   | 2990 |          | NULL             |
| 144 | root | localhost:65226 | mysql_test | Query   |    0 | starting | show processlist |
+-----+------+-----------------+------------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

3.执行计划

3.1.explain 执行计划

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

3.2.explain [id] 关联表的执行顺序，id越大优先级越高

mysql> explain select * from user u, role r, user_role ur where u.id=ur.user_id and r.id=ur.role_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: ur
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: boot_mybatis_plus.ur.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)


mysql> explain select * from user where id = (select user_id from user_role where role_id=1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: user
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: user_role
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)


mysql> explain select * from role r,(select * from user_role where user_id=1) a where r.id=a.role_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_role
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: boot_mybatis_plus.user_role.role_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

3.3.select_type

select_type	说明
SIMPLE	普通
PRIMARY	子查询最外层
SUBQUERY	子查询内层
DERIVED	子查询在from块
UNION	union操作内层
UNION RESULT	union操作结果

3.4.table 查询的表
3.5.type

type	说明
NULL	MYSQL不访问任何表，索引，直接返回结果
system	表只有一行记录，const特例
const	索引一次找到，主键或唯一索引
eq_ref	类似ref，主键或唯一索引
ref	非唯一性索引扫描
range	检索给定返回的行
index	遍历了索引树
all	全数据扫描

优化至少到range，最好到ref

3.6.key

key	说明
possible_keys	显示可能应用在这张表的索引
key	实际使用的索引，如果为NULL，则没有使用索引
key_len	索引中使用字节数，越短越好

3.7.rows 扫描的行数
3.8.extra
- 3.8.1.Using filesort
  文件排序，不是按照表内的索引顺序进行读取
- 3.8.2.Using temporary
  对查询结果排序时使用了临时表，order by和group by
- 3.8.3.Using index
  查询列被索引覆盖,且where条件是索引前导列
  
  Using index.png
- 3.8.4.Using where Using index
  1.查询列被索引覆盖，where条件不是索引前导列
  
  Using where Using index_1.png
  
  2.查询列被索引覆盖，where条件是索引前导列的一个范围
  
  Using where Using index_2.png
- 3.8.5.NULL
  查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现
  
  NULL.png
- 3.8.6.Using where
  1.查询的列未被索引覆盖，where筛选条件非索引的前导列
  
  Using where_1.png
  
  2.查询的列未被索引覆盖，where筛选条件非索引列
  
  Using where_2.png
- 3.8.7.Using index condition
  1.查询的列不全在索引中，where条件中是一个前导列的范围
  
  Using index condition_1.png
  
  2.查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）
  
  Using index condition_2.png

4.Query Profiler

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.38714850 | select * from t_student |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000023 |
| Waiting for query cache lock   | 0.000006 |
| starting                       | 0.000001 |
| checking query cache for query | 0.000028 |
| checking permissions           | 0.000014 |
| checking permissions           | 0.000002 |
| Opening tables                 | 0.000010 |
| init                           | 0.000025 |
| System lock                    | 0.000007 |
| optimizing                     | 0.000002 |
| statistics                     | 0.000024 |
| preparing                      | 0.000011 |
| executing                      | 0.000002 |
| Sending data                   | 0.386937 |
| end                            | 0.000008 |
| query end                      | 0.000005 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000015 |
| cleaning up                    | 0.000025 |
+--------------------------------+----------+
19 rows in set, 1 warning (0.00 sec)


# show profile all(cpu) for query 1;

mysql> show profile cpu for query 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000023 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000006 | 0.000000 |   0.000000 |
| starting                       | 0.000001 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000028 | 0.000000 |   0.000000 |
| checking permissions           | 0.000014 | 0.000000 |   0.000000 |
| checking permissions           | 0.000002 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000010 | 0.000000 |   0.000000 |
| init                           | 0.000025 | 0.000000 |   0.000000 |
| System lock                    | 0.000007 | 0.000000 |   0.000000 |
| optimizing                     | 0.000002 | 0.000000 |   0.000000 |
| statistics                     | 0.000024 | 0.000000 |   0.000000 |
| preparing                      | 0.000011 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.386937 | 0.359375 |   0.031250 |
| end                            | 0.000008 | 0.000000 |   0.000000 |
| query end                      | 0.000005 | 0.000000 |   0.000000 |
| closing tables                 | 0.000006 | 0.000000 |   0.000000 |
| freeing items                  | 0.000015 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000025 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
19 rows in set, 1 warning (0.00 sec)

5.trace

mysql> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_student where id <= 4;
+----+------------+-----------+--------+-------+--------------------------------------+----------+
| id | first_name | last_name | gender | user  | password                             | class_id |
+----+------------+-----------+--------+-------+--------------------------------------+----------+
|  1 | e5bec      | 485e4     |      1 | NULL  | b4a7029f-4dd2-45ef-a58f-da85db58da8a |      875 |
|  2 | 8cd0a      | 35262     |      2 | NULL  | d236875f-ff7f-4f2c-a025-9e3bb00b48cd |      360 |
|  3 | d7013      | 69586     |      1 | 3dd-c | d45a9d11-5241-4096-9925-5e939443c538 |      906 |
|  4 | d4c6d      | 57f2a     |      2 | cff-4 | e272e138-4342-4712-b85a-fa011e494165 |      979 |
+----+------------+-----------+--------+-------+--------------------------------------+----------+
4 rows in set (0.00 sec)

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: select * from t_student where id <= 4
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          ...
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

6.索引

6.1.全值匹配，在索引上用精确值查询
6.2.最左前缀法则，例user表复合索引（name, age, address）

// 误区1    (走索引，但只走一半)
select * from user where name = 'a' and address = 'beijing';   走name
// 误区2  （where 条件只要包含就能生效）
select * from user where age = 20 and address = 'bj' and name = 'a';   走索引（name, age, address）

6.3.中间条件是一个范围

select * from user where age > 20 and address = 'bj' and name = 'a';   走索引（name，age）

6.4.索引列上做运算或函数操作

select * from user where age - 1 > 20 and name = 'a';    走索引（name）
select count(*) from user where month(create_time)=7;    失效
// 优化
select count(*) from user where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') 
or (t_modified >= '2017-7-1' and t_modified<'2017-8-1');

6.5.字符串不加单引号，索引失效（name，sex）

select * from user where name = 'a' and sex = 1;
// 优化
select * from user where name = 'a' and sex = '1';

6.6.尽量使用覆盖索引，避免select * （name, age, address）条件是id主键例外

select * from user where name = 'a';   Using index condition
select name, age, address from user where name = 'a'    Using where;Using index
select name, age, address, password from user where name = 'a'   Using index condition

6.7.用or分割条件时，所有条件均需满足有索引
6.8.like 通配符（%李，_李）在最左边失效

select * from user where name = '%李%';
// 用主键或复合索引来覆盖索引优化
select id, name, age, address from user where name = '%李%';
// 索引未全覆盖会回表查询
select id, name, age, address, password from user where name = '%李%';

6.9.数据类别少，如sex共就三类
6.10. is NULL， is NOT NULL 有时索引失效（原理同6.9）
6.11.in走索引，not in 不走索引
6.12.尽量使用复合索引替代单列索引，都是单列索引时走辨识度最高的索引
6.13.查询索引使用情况

mysql> show status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 7       |
| Handler_read_key      | 5       |
| Handler_read_last     | 0       |
| Handler_read_next     | 8       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1400074 |
+-----------------------+---------+
7 rows in set (0.00 sec)

Mysql优化补充

1.查看sql执行频率

2.定位低效率执行SQL

3.执行计划

4.Query Profiler

5.trace

6.索引

猜你喜欢

热点阅读