sql优化的一般步骤
1、通过show status 命令了解各种sql的执行频率
show [session|global] status 命令可以提供服务器状态信息，session表示当前连接，global 表示自数据库上次启动至今的统计结果，如果不写默认是session.
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26mysql> show status like 'Com_%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 2 | | Com_alter_tablespace | 0 | | Com_alter_user | 0 | | Com_analyze | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 1 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | ...
Com_xxx表示每个xxx语句执行的次数，通常关心以下几个统计参数：
-
Com_select:执行select操作的次数，一次查询只累加1.
-
Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加一次。
-
Com_update:执行update操作的次数。提交或者回滚都会累加
-
Com_delete:执行delete操作的次数。
上面的参数对于所有的存储引擎的表操作都会累加。下面的参数只针对Innodb存储引擎，累加的算法叶略有不同。 -
Innodb_rows_read:select查询返回的行数。
-
Innodb_rows_insert:执行insert 操作插入的行数。
-
Innodb_rows_update:执行update操作更新的行数。
-
Innodb_rows_delete:执行delete操作删除的行数。
通过以上参数，可以找出数据库的应用是以插入更新为准还是查询操作为准。
Com_commit和Com_rollback 可以了解事物提交和回滚操作的情况，如果回滚非常频繁，则可能应用编写不正确。
以下参数可以了解数据库的基本情况。 -
Connections:试图连接MYSQL服务器的次数；
-
Uptime:服务器工作时间。
-
Slow_queries:慢查询的次数。
2、定位执行效率较低的sql语句
可以通过以下两种方式定位执行效率较低的SQL语句。
- 通过慢查询日志定位，用–log-slow-queries[=file_name]选项启动mysql时，mysqld会写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。
- 慢查询日志是在查询结束以后才记录，不能很好的定位，可以通过使用show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等。
3、通过EXPLAIN分析抵消SQL的执行计划
通过以上步骤查到效率慢的sql后，可以用EXPLAIN或者desc获取如何执行select语句的信息。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25mysql> explain select sum(a.score) from student a,teacher b where a.id=b.id G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ALL possible_keys: key_index key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: yly.a.id rows: 1 Extra: Using where; Using index 2 rows in set (0.02 sec)
每个列简单介绍下：
- select_type:表示select的类型，常见的取值有SIMPLE(简单表,即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句）、SUBQUERY(子查询中的第一个select)等。
- table:输出结果集的表。
- type:表示MySQL在表中找到所需行的方式，或者叫访问类型，常见如下：
复制代码
1
2
3
4ALL（全盘扫描） | index（索引全扫描） | range（索引范围扫描） | ref（非唯一索引扫描或者唯一索引的前缀扫描） | eq_ref(使用唯一索引扫描) | const,system （单表中最多有一个匹配行） | NULL（不用访问表或索引）
从左到右，性能由最差到最好。
type =ALL（全盘扫描）,如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> explain select * from student G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: student type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: NULL 1 row in set (0.00 sec) ERROR: No query specified
type=index（索引全扫描）,如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> explain select id from student G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: student type: index possible_keys: NULL key: key_index key_len: 5 ref: NULL rows: 2 Extra: Using index 1 row in set (0.00 sec) ERROR: No query specified
type=range (索引范围扫描)，如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> explain select * from student where id <10 G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: student type: range possible_keys: key_index key: key_index key_len: 5 ref: NULL rows: 1 Extra: Using index condition 1 row in set (0.00 sec) ERROR: No query specified
type=ref,使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行，ref还经常出现在join操作中，如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> explain select * from student where id =1 G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: student type: ref possible_keys: key_index key: key_index key_len: 5 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec) ERROR: No query specified
type=eq_ref,类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是夺标连接中使用primary key 或者unique index作为关联条件，如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28mysql> explain select sum(a.score) from student a,teacher b where a.id=b.id G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ALL possible_keys: key_index key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: yly.a.id rows: 1 Extra: Using where; Using index 2 rows in set (0.00 sec) ERROR: No query specified
type=const/system,单表中最多有一个匹配行，例如，根据主键primary key 或者唯一索引unique index进行的查询；
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34mysql> alter table student add unique index uk_id(id); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from (select * from student where id='1'); ERROR 1248 (42000): Every derived table must have its own alias mysql> explain select * from (select * from student where id='1') a G; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: NULL *************************** 2. row *************************** id: 2 select_type: DERIVED table: student type: const possible_keys: uk_id,key_index key: uk_id key_len: 5 ref: const rows: 1 Extra: NULL 2 rows in set (0.00 sec) ERROR: No query specified
type=null,不用访问表或者索引，直接就能够得到结果，如：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> explain select 1+2 from dual G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: No tables used 1 row in set (0.00 sec) ERROR: No query specified
type还有其他值，如ref_or_null（于ref类似，区别在于条件中中包含对null的查询）、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询）、index_subquery(in 的后面是查询非唯一素银字段的子查询）等。
- possible_keys:表示查询时可能用到的索引。
- key:表示实际使用的索引。
- key_len:使用到索引字段的长度。
key_len字节的计算规则：
字符串：char(n) - n字节， varchar(n)- n字节 + 2字节(变长), ， 多字节charset * [1~4]字节（utf8为3字节，utf8mb4为4字节计算）
数值类型： TINYINT-1字节，SMALLINT-2字节， MEDIUMINT-3字节， INT-4字节，BIGINT-8字节
时间类型：DATE-3字节， TIMESTAMP-4字节， DATETIME-8字节
字段属性：NULL属性+ 1字节 - rows:扫描行的数量。
- Extra:执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
常见的有以下几种内容
Using filesort
MySQL需额外的排序操作，不能通过索引顺序达到排序效果；又叫”文件排序“，易错误理论为排序结果过大，内存中不够需写磁盘文件排序。
一般有filesort，都建议优化去掉，CPU资源消耗大。
Using index
”覆盖索引扫描“，表示查询在索引树中就可查找所需数据，不用回表数据文件(回表操作），往往说明性能不错
Using temporary
查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化
explain extented 输出结果多了filtered 字段，同时可以使用show warning 来查看更清晰易读的sql.
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28mysql> explain extended select * from student G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: student type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR: No query specified mysql> show warnings G; *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `yly`.`student`.`id` AS `id`,`yly`.`student`.`username` AS `username`,`yly`.`student`.`password` AS `password`,`yly`.`student`.`date` AS `date`,`yly`.`student`.`score1` AS `score1`,`yly`.`student`.`score` AS `score` from `yly`.`student` 1 row in set (0.00 sec) ERROR: No query specified
explain partitions 可以查看sql所访问的分区；
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18mysql> explain partitions select * from teacher G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: teacher partitions: p0,p1,p2,p3,p4,p5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: NULL 1 row in set (0.00 sec) ERROR: No query specified
4、通过show profile 分析SQL
通过have_profiling 查看是否支持profile.
复制代码
1
2
3
4
5
6
7
8mysql> select @@have_profiling; +------------------+ | @@have_profiling | +------------------+ | YES | +------------------+ 1 row in set, 1 warning (0.00 sec)
默认profiling是关闭的，可以通过set开启。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> 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.01 sec)
下面通过一个具体的例子来说明show profile 的分析过程：
1、在一个innodb引擎的学生表student上，执行一个count(*)查询：
复制代码
1
2
3
4
5
6
7
8mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)
2、执行查询完毕后，通过show profiles 来找出当前SQL的query id为：3；
复制代码
1
2
3
4
5
6
7
8
9
10
11mysql> show profiles; +----------+------------+----------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------+ | 1 | 0.00007850 | select @@version_comment limit 1 | | 2 | 0.00008125 | SELECT DATABASE() | | 3 | 0.00018900 | select @@profiling | | 4 | 0.00029200 | select count(*) from user | +----------+------------+----------------------------------+ 4 rows in set, 1 warning (0.00 sec)
3、使用show profile for query 4 来查看SQL执行过程中线程的每个状态和消耗的时间：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22mysql> show profile for query 4; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000085 | | checking permissions | 0.000011 | | Opening tables | 0.000022 | | init | 0.000011 | | System lock | 0.000007 | | optimizing | 0.000005 | | statistics | 0.000009 | | preparing | 0.000008 | | executing | 0.000002 | | Sending data | 0.000101 | | end | 0.000006 | | query end | 0.000005 | | closing tables | 0.000006 | | freeing items | 0.000009 | | cleaning up | 0.000009 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
可以看到主要消耗在sending data这个状态上，为了更清晰额看到排序结果，可以查询INFORMATION_SCHAEMA.PROFILING表病按照时间做DESC排序：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27mysql> select state,sum(duration)as total_r,round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id=4),2) as pct_r,count(*) as calls, -> sum(duration)/count(*) as 'R/call' -> from information_schema.profiling -> where query_id=4 -> group by state -> order by total_r desc; +----------------------+----------+-------+-------+--------------+ | state | total_r | pct_r | calls | R/call | +----------------------+----------+-------+-------+--------------+ | Sending data | 0.000101 | 34.12 | 1 | 0.0001010000 | | starting | 0.000085 | 28.72 | 1 | 0.0000850000 | | Opening tables | 0.000022 | 7.43 | 1 | 0.0000220000 | | checking permissions | 0.000011 | 3.72 | 1 | 0.0000110000 | | init | 0.000011 | 3.72 | 1 | 0.0000110000 | | statistics | 0.000009 | 3.04 | 1 | 0.0000090000 | | cleaning up | 0.000009 | 3.04 | 1 | 0.0000090000 | | freeing items | 0.000009 | 3.04 | 1 | 0.0000090000 | | preparing | 0.000008 | 2.70 | 1 | 0.0000080000 | | System lock | 0.000007 | 2.36 | 1 | 0.0000070000 | | end | 0.000006 | 2.03 | 1 | 0.0000060000 | | closing tables | 0.000006 | 2.03 | 1 | 0.0000060000 | | query end | 0.000005 | 1.69 | 1 | 0.0000050000 | | optimizing | 0.000005 | 1.69 | 1 | 0.0000050000 | | executing | 0.000002 | 0.68 | 1 | 0.0000020000 | +----------------------+----------+-------+-------+--------------+ 15 rows in set (0.02 sec)
show profile 能够在做SQL优化时帮助我们了解时间都消耗在哪里。
5、通过trace 分析优化器如何选择执行计划
从MySQL5.6版本开始，可支持把MySQL查询执行计划树打印出来，对DBA深入分析SQL执行计划，COST成本都非常有用，打印的内部信息比较全面；
功能支持动态开关，因为对性能有20%左右影响，只建议分析问题时，临时开启
使用方式：首先打开trace,设置格式为JSON,设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过程过小而不能够完整显示。
复制代码
1
2
3
4
5
6mysql> 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)
然后执行你要分析的SQL,如
复制代码
1
2
3
4
5
6
7
8mysql> select id from student where id >1 and id <10; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.01 sec)
最后，检查information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的；
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204mysql> select * from information_schema.optimizer_trace G; *************************** 1. row *************************** QUERY: select id from student where id >1 and id <10 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `student`.`id` AS `id` from `student` where ((`student`.`id` > 1) and (`student`.`id` < 10))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))" }, { "transformation": "constant_propagation", "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))" } ] /* steps */ } /* condition_processing */ }, { "table_dependencies": [ { "table": "`student`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`student`", "range_analysis": { "table_scan": { "rows": 2, "cost": 3.5 } /* table_scan */, "potential_range_indices": [ { "index": "uk_id", "usable": true, "key_parts": [ "id" ] /* key_parts */ }, { "index": "key_index", "usable": true, "key_parts": [ "id" ] /* key_parts */ } ] /* potential_range_indices */, "best_covering_index_scan": { "index": "key_index", "cost": 1.401, "chosen": true } /* best_covering_index_scan */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "uk_id", "ranges": [ "1 < id < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.21, "chosen": true }, { "index": "key_index", "ranges": [ "1 < id < 10" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 1, "cost": 2.21, "chosen": false, "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "uk_id", "rows": 1, "ranges": [ "1 < id < 10" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 1.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`student`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 1, "cost": 1.41, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.41, "rows_for_plan": 1, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`student`.`id` > 1) and (`student`.`id` < 10))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`student`", "attached": "((`student`.`id` > 1) and (`student`.`id` < 10))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`student`", "access_type": "range" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.01 sec) ERROR: No query specified
经过以上步骤，基本就可以找出sql的问题所在。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。
最后
以上就是谨慎棒球最近收集整理的关于MySQL优化(一）sql优化的一般步骤的全部内容，更多相关MySQL优化(一）sql优化内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复