MySQL 优化

优化SQL步骤

了解SQL的执行频率

1
show [session|global] status;

执行命令提供服务器状态信息,session表示显示session(当前连接)级别的统计结果,global表示显示global(从服务器启动至今)级别的统计结果,默认为session级别。

1
2
3
4
5
6
7
8
9
10
11
MariaDB [(none)]> show global status like 'Com_%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
... ...

Com_xxx表示每个xxx语句执行的次数:

  • Com_select:执行select操作的次数,一次查询只累加1
  • Com_insert:执行insert操作的次数,对于批量插入的insert操作,只累计一次
  • Com_update:执行update操作的次数
  • Com_delete:执行delete操作的次数

Com_select、Com_insert、Com_update、Com_delete对所有存储引擎都进行累计。

只准对InnoDB存储引擎:

  • Innodb_rows_read:select查询返回的行数
  • Innodb_rows_inserted:执行insert操作插入的行数
  • Innodb_rows_updated:执行update操作插入的行数
  • Innodb_rows_deleted:执行delete操作删除的行数

对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累计

Com_commit和Com_rollback可以了解事物提交和回滚的情况,对于回滚操作非常频繁的数据库,意味着程序编写存在问题。

了解数据库基本情况:

  • Connections:试图连接MySQL服务器的次数
  • Uptime:服务器工作时间
  • Slow_queries:慢查询的次数

定位执行效率较低的SQL语句

  1. 通过慢查询日志定位
  2. show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,通过对一些锁表操作优化

通过EXPLAIN分析低效SQL的执行计划

查询到低效的SQL语句后,通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [test]> desc select * from test_int;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test_int | ALL | NULL | NULL | NULL | NULL | 4 | |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

MariaDB [test]> explain select * from test_int;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test_int | ALL | NULL | NULL | NULL | NULL | 4 | |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

  • select_type:表示select查询的类型,常见的取值有SIMPLE(简单表,即不使用表连接或子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等
  • table:数据结果集的表
  • type:表示MySQL在表中找到所需行的方式,或者叫访问类型.
  • possible_keys:表示查询时可能使用的索引
  • key:表示实际使用的索引
  • key_len:使用到索引字段的长度
  • rows:扫描行的数量
  • Extra:执行情况的说明和描述,包括不适合在其他列中显示但是对执行计划非常重要的额外信息

type类型,从左至右,性能由最差到最好

1
2
3
+-----+-------+-------+-----+--------+--------------+------+
| ALL | index | range | ref | eq_ref | const,system | NULL |
+-----+-------+-------+-----+--------+--------------+------+

  1. ALL 全表扫描,MySQL遍历全表来找到匹配的行
  2. index 索引全扫描,MySQL遍历整个索引来查询匹配的行
  3. range 索引范围扫描,常见于\<、\<=、>、>=、between等操作符
  4. ref 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行
  5. eq_ref 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录记录匹配;简单说,多表连接中使用primary key或者unique index作为关联条件
  6. const/system 单表中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当错常量来处理
  7. NULL MySQL不用访问表或索引,直接就能够得到结果

通过explain extended命令和show warnings可以看到SQL真正被执行之前优化器做了哪些SQL改写.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [test]> explain extended select * from test_int;
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_int | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`test_int`.`a` AS `a`,`test`.`test_int`.`b` AS `b` from `test`.`test_int` |
+-------+------+-----------------------------------------------------------------------------------------+

filtered
通过warning的message字段可以看到优化器优化后的SQL. 碰到复杂SQL时,可以利用explain extended的结果迅速获取更清晰易读的SQL

通过show profile分析SQL

通过have_profiling参数可以看到当前MySQL是否支持profile:

1
2
3
4
5
6
7
MariaDB [(none)]> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set (0.00 sec)

通过profiling参数可以看到当前profiling是否打开

1
2
3
4
5
6
7
MariaDB [(none)]> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)

通过set语句在session级别开启profiling

1
2
MariaDB [(none)]> set profiling=1;
Query OK, 0 rows affected (0.04 sec)

通过profile可以清楚地了解SQL执行的过程.
在执行完SQL后,使用show profiles.查看执行SQL的Query_ID,再使用show profile for query查看执行过程中线程的每个状态和消耗时间.

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
MariaDB [test]> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------+
| 1 | 0.00007221 | select * from test_int |
| 2 | 0.00012501 | SELECT DATABASE() |
| 3 | 0.00029614 | show databases |
| 4 | 0.00014315 | show tables |
| 5 | 0.00038331 | select * from test_int |
+----------+------------+------------------------+
5 rows in set (0.00 sec)

MariaDB [test]> show profile for query 5;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000058 |
| checking permissions | 0.000011 |
| Opening tables | 0.000026 |
| After opening tables | 0.000023 |
| System lock | 0.000006 |
| Table lock | 0.000043 |
| After table lock | 0.000009 |
| init | 0.000021 |
| optimizing | 0.000011 |
| statistics | 0.000025 |
| preparing | 0.000015 |
| executing | 0.000005 |
| Sending data | 0.000083 |
| end | 0.000006 |
| query end | 0.000007 |
| closing tables | 0.000010 |
| freeing items | 0.000012 |
| updating status | 0.000010 |
| cleaning up | 0.000005 |
+----------------------+----------+
19 rows in set (0.00 sec)

Sending data状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回结果给客户端.在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询中耗时最长的状态.
可以使用show profile查看更加详细的资源消耗情况,可选择all、cpu、block io、context switch、page faults等

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
MariaDB [test]> show profile all for query 5;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| starting | 0.000058 | 0.000034 | 0.000018 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000011 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 4911 |
| Opening tables | 0.000026 | 0.000017 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5048 |
| After opening tables | 0.000023 | 0.000015 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5240 |
| System lock | 0.000006 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 307 |
| Table lock | 0.000043 | 0.000028 | 0.000015 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 312 |
| After table lock | 0.000009 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 326 |
| init | 0.000021 | 0.000014 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3075 |
| optimizing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 990 |
| statistics | 0.000025 | 0.000016 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 1233 |
| preparing | 0.000015 | 0.000010 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 1258 |
| executing | 0.000005 | 0.000003 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 2242 |
| Sending data | 0.000083 | 0.000054 | 0.000029 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 2873 |
| end | 0.000006 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3110 |
| query end | 0.000007 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4520 |
| closing tables | 0.000010 | 0.000007 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | close_thread_tables | sql_base.cc | 1466 |
| freeing items | 0.000012 | 0.000008 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5944 |
| updating status | 0.000010 | 0.000006 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1469 |
| cleaning up | 0.000005 | 0.000003 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1486 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
19 rows in set (0.00 sec)

MariaDB [test]> show profile cpu for query 5;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000058 | 0.000034 | 0.000018 |
| checking permissions | 0.000011 | 0.000006 | 0.000003 |
| Opening tables | 0.000026 | 0.000017 | 0.000009 |
| After opening tables | 0.000023 | 0.000015 | 0.000008 |
| System lock | 0.000006 | 0.000004 | 0.000002 |
| Table lock | 0.000043 | 0.000028 | 0.000015 |
| After table lock | 0.000009 | 0.000006 | 0.000003 |
| init | 0.000021 | 0.000014 | 0.000007 |
| optimizing | 0.000011 | 0.000007 | 0.000004 |
| statistics | 0.000025 | 0.000016 | 0.000008 |
| preparing | 0.000015 | 0.000010 | 0.000005 |
| executing | 0.000005 | 0.000003 | 0.000002 |
| Sending data | 0.000083 | 0.000054 | 0.000029 |
| end | 0.000006 | 0.000004 | 0.000002 |
| query end | 0.000007 | 0.000004 | 0.000002 |
| closing tables | 0.000010 | 0.000007 | 0.000003 |
| freeing items | 0.000012 | 0.000008 | 0.000004 |
| updating status | 0.000010 | 0.000006 | 0.000004 |
| cleaning up | 0.000005 | 0.000003 | 0.000001 |
+----------------------+----------+----------+------------+
19 rows in set (0.00 sec)

通过trace分析优化器如何选择执行计划

  1. 打开trace,设置格式为JSON,设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能完整显示.
  2. 执行想要trace的SQL语句
  3. 检查INFORMATION_SCHEMA.OPTIMIZER_TRACE
    1
    2
    3
    SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
    SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
    SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

确认问题并采取相应的优化措施

简单使用的优化方法

定期分析表和检查表

分析表

1
ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name]...

本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得SQL能够生成正确的执行计划

检查表

1
CHECK TABLE tbl_name[, tbl_name]...[option]...option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

检查表的作用是检查一个或多个表是否有错误.
CHECK TABLE也可以检查是否是否有错误.

定期优化表

1
OPTIONMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]...

优化表可以对表中的空间碎片进行合并.
InnoDB引擎的表可以设置innodb_file_per_table参数,设置InnoDB为独立表空间模式,每个数据库的每个表都会生成单独的ibd文件,用于存储表的数据和索引,减轻InnoDB表的控件回收问题.
删除大量数据后,InnoDB表可以通过alter table但不修改引擎的方式回收不用的空间

1
alter table tbl_name engine=innodb;

ANLYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定,因此一定注意在数据库不繁忙时执行相关操作

常用SQL的优化

大批量插入数据

使用load命令导入数据,可以通过适当的设置提高导入速度.
对于MyISAM存储引擎的表,可以使用DISABLE KEYS关闭MyISAM表非唯一索引的更新,读入完成后,使用ENABLE KEYS打开MyISAM表非唯一索引的更新.

1
2
3
alter table tbl_name disable keys;
load data infole '/filepath';
alter table tbl_name enable keys;

对于InnoDB存储引擎的表:

  1. 将导入的数据按照主键的顺序排序
  2. 导入数据前执行SET UNIQUE_CHECKS=0关闭唯一性校验,导入结束再执行SET UNIQUE_CHECKS=1,恢复唯一性校验
  3. 关闭自动提交,等导入完成再打开(SET AUTOCOMMINT=0)

优化INSERT语句

  1. 同一客户端同时插入很多行,应尽量使用一条语句,减少客户端与数据库之间的连接、关闭等消耗
  2. 不同客户端插入很多行,使用INSERT DELAYED语句让INSERT语句马上执行,实际数据都被放在内存队列中,并没有真正写入磁盘
  3. 将索引文件和数据文件分在不同的磁盘上存放
  4. 批量插入可以通过增加bulk_insert_buffer_size变量值的方法提高速度,只对MyISAM表有用
  5. 当从文本文件装载一个表时,使用LOAD DATA INFILE,比使用INSERT语句快

优化ORDER BY语句

MySQL两种排序方式

  1. 通过使用有序索引顺序扫描直接返回有序数据,在使用explain分析查询语句时显示为Using Index,不需要额外排序操作效率较高
  2. 通过对返回数据进行排序(Filesort排序),所有不是通过索引直接返回排序结果的排序都叫Filesort排序.

ORDER BY语句优化目标:尽量减少额外的排序,通过索引直接返回有序数据.

Filesort优化

通过建立合适的索引能减少Filesort的出现.如果条件限制导致Filesort不能消失,则需要加快Filesort的操作
对Filesort,MySQL的两种排序算法:

  1. 两次扫描算法:根据条件取出排序字段和行指针信息,然后在排序区sort buffer中排序,如果sort buffer不足,则在临时表Temporary Table中存储排序结果.完成排序后再根据行指针回表读取记录.优点是排序的时候内存开销少,缺点是第二次读取记录会有大量随机I/O操作,MySQL4.1以前使用此方法
  2. 一次扫描算法:一次性取出所有满足条件行的所有字段,然后排序,优点是速度快,缺点是内存开销大

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小判断使用那种排序算法.如果max_length_for_sort_data更大,则使用一次扫描算法,否则使用二次扫描算法
适当加大max_length_for_sort_data可以让MySQL选择一次扫描算法,提高效率,设置max_length_for_sort_data过大,会造成CPU利用率过低和磁盘I/O过高,CPU和I/O利用平衡即可.
尽量使用必要的字段,SELECT具体的字段名称,减少排序区的使用,提高SQL性能.

优化GROUP BY语句

如果查询包括GROUP BY但用户想避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序.

优化嵌套查询

使用连接JOIN替代子查询,因为JOIN不需要在内存中创建临时表

MySQL优化OR条件

对于含有OR条件的查询子句,如果想利用索引,则OR之间的每个条件列都必须使用索引,如果没有索引则应该考虑增加索引.

分页查询优化

  1. 在索引上完成排序分页操作,最后根据主键关联回原表查询所需额其他列内容
  2. 将LIMIT查询转化为某个位置的查询(将LIMIT m,n转化为LIMIT n的查询)

使用SQL提示

USE INDEX

在查询语句中表名的后面,添加USE INDEX来提供希望MySQL去参考的索引列表,让MySQL不再考虑其它可以索引

IGNORE INDEX

让MySQL忽略一个或多个索引

FORCE INDEX

强制MySQL使用一个特定的索引

常用SQL技巧

正则表达式的使用

MySQL利用REGEXP命令提供给用户扩展的正则表达式功能.REGEXP在进行模式匹配时区分大小写.

序列 序列说明
^ 在字符串的开始处进行匹配
$ 在字符串的末尾处进行匹配
. 匹配任意单个字符,包括换行符
[…] 匹配出括号内的任意字符
... 匹配不出括号内的任意字符
a* 匹配零个或多个a(包括空串)
a+ 匹配1个或多个a(不包括空串)
a? 匹配1个或零个a
a1 | a2 匹配a1或a2
a(m) 匹配m个a
a(m,) 匹配m个或更多个a
a(m,n) 匹配m到n个a
a(,n) 匹配0到n个a
(…) 将模式元素组成单一元素