MySQL SQL 优化

最新推荐文章于 2024-09-21 11:29:25 发布
事后诸葛亮
最新推荐文章于 2024-09-21 11:29:25 发布
阅读量1.1k
点赞数
分类专栏： MySQL
本文链接：https://blog.csdn.net/zq9017197/article/details/50332355
版权
MySQL 专栏收录该内容
26 篇文章 0 订阅
订阅专栏
一、优化SQL 语句的一般步骤
1、通过show status 命令了解各种SQL 的执行频率
通过show [session|global]status 命令可以提供服务器状态信息，也可以在操作系统上使用mysqladmin extended-status 命令获得这些消息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示session 级（当前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
下面的命令显示了当前session 中所有统计参数的值：
mysql>  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           | 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_inserted：执行INSERT 操作插入的行数。
? Innodb_rows_updated：执行UPDATE 操作更新的行数。
? Innodb_rows_deleted：执行DELETE 操作删除的行数。
以下几个参数便于用户了解数据库的基本情况。
? Connections：试图连接MySQL 服务器的次数。
? Uptime：服务器工作时间。
? Slow_queries：慢查询的次数。

2、通过EXPLAIN 分析低效SQL 的执行计划
mysql> explain select * from emp \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra:
1 row in set (0.00 sec)

? select_type：表示SELECT 的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
? table：输出结果集的表。
? type：表示表的连接类型，性能由好到差的连接类型为system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如primary key 或者unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）、re（f 与eq_ref类似，区别在于不是使用primarykey 或者unique index，而是使用普通的索引）、ref_or_null（与ref 类似，区别在于条件中包含对NULL 的查询）、index_merge(索引合并优化)、unique_subquery（in的后面是一个查询主键字段的子查询）、index_subquery（与unique_subquery 类似，区别在于in 的后面是查询非唯一索引字段的子查询）、range（单表中的范围查询）、index（对于前面的每一行，都通过查询索引来得到数据）、all（对于前面的每一行，都通过全表扫描来得到数据）。
? possible_keys：表示查询时，可能使用的索引。
? key：表示实际使用的索引。
? key_len：索引字段的长度。
? rows：扫描行的数量。
? Extra：执行情况的说明和描述。

通过 explain extended 加上 show warnings，能够看到在sql真正被执行之前优化器做了哪些sql改写：
mysql> explain extended select * from emp \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra:
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`emp`.`id` AS `id`,`test`.`emp`.`ename` AS `ename`,`test`.`emp`.`hired` AS `hired`,
`test`.`emp`.`separated` AS `separated`,`test`.`emp`.`job` AS `job`,`test`.`emp`.`store_id` AS `store_id` from `test`.`emp`
1 row in set (0.00 sec)

可以通过 explain partition 命令查看sql所访问的分区：
mysql> explain partitions select * from emp where store_id=8 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p3
         type: ref
possible_keys: idx_emp_store_id
          key: idx_emp_store_id
      key_len: 4
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

3、通过 show profile 分析sql
默认profiling是关闭的，可以通过set语句在session级别开启profiling：
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

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

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

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

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

首先，在一个InnoDB引擎上，执行一个count（*）查询：
mysql> show create table country \G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

执行完毕后，通过show profiles语句，看到当前sql的query id为4：
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00076075 | show variables like '%profiling%' |
|        2 | 0.00014400 | select @@profiling                |
|        3 | 0.00014025 | SELECT DATABASE()                 |
|        4 | 0.00037250 | select count(*) from country      |
+----------+------------+-----------------------------------+
4 rows in set (0.00 sec)

通过show profile for query语句能够看到执行过程中线程的每个状态和消耗的时间：
mysql> show profile for query 4;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000065 |
| Opening tables     | 0.000045 |
| System lock        | 0.000004 |
| Table lock         | 0.000007 |
| init               | 0.000012 |
| optimizing         | 0.000005 |
| statistics         | 0.000012 |
| preparing          | 0.000007 |
| executing          | 0.000004 |
| Sending data       | 0.000118 |
| end                | 0.000004 |
| query end          | 0.000002 |
| freeing items      | 0.000082 |
| logging slow query | 0.000004 |
| cleaning up        | 0.000002 |
+--------------------+----------+
15 rows in set (0.00 sec)

注意：Sending data状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，mysql线程往往需要做大量的磁盘读取操作，所有经常是整个查询中最耗时的状态。
在获取最耗时间的线程状态后，进一步选择all、cpu、block io、context switch、page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间。例如，选择查看cpu的耗费时间：

mysql> show profile cpu for query 4;
+--------------------+----------+----------+------------+
| Status             | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| starting           | 0.000065 |     NULL |       NULL |
| Opening tables     | 0.000045 |     NULL |       NULL |
| System lock        | 0.000004 |     NULL |       NULL |
| Table lock         | 0.000007 |     NULL |       NULL |
| init               | 0.000012 |     NULL |       NULL |
| optimizing         | 0.000005 |     NULL |       NULL |
| statistics         | 0.000012 |     NULL |       NULL |
| preparing          | 0.000007 |     NULL |       NULL |
| executing          | 0.000004 |     NULL |       NULL |
| Sending data       | 0.000118 |     NULL |       NULL |
| end                | 0.000004 |     NULL |       NULL |
| query end          | 0.000002 |     NULL |       NULL |
| freeing items      | 0.000082 |     NULL |       NULL |
| logging slow query | 0.000004 |     NULL |       NULL |
| cleaning up        | 0.000002 |     NULL |       NULL |
+--------------------+----------+----------+------------+
15 rows in set (0.00 sec)

对比MyISAM表的count（*）操作：
mysql> show create table ai \G
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> select count(*) from ai;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00076075 | show variables like '%profiling%' |
|        2 | 0.00014400 | select @@profiling                |
|        3 | 0.00014025 | SELECT DATABASE()                 |
|        4 | 0.00037250 | select count(*) from country      |
|        5 | 0.00022375 | select count(*) from ai           |
+----------+------------+-----------------------------------+
5 rows in set (0.00 sec)

mysql> show profile for query 5;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000097 |
| Opening tables     | 0.000020 |
| System lock        | 0.000006 |
| Table lock         | 0.000008 |
| init               | 0.000014 |
| optimizing         | 0.000007 |
| executing          | 0.000014 |
| end                | 0.000004 |
| query end          | 0.000002 |
| freeing items      | 0.000051 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000001 |
+--------------------+----------+
12 rows in set (0.00 sec)

InnoDB引擎的表再count（*）时经历了Sending data状态，存在访问数据的过程，而MyISAM引擎的表在executing之后直接就结束查询，完全不需要访问数据。
这是因为MyISAM表有表元数据的缓存（例如行数，即count（*）值），那么对一个MyISAM表的count（*）是不需要消耗太多资源的，而对于InnoDB来说，就没有这种元数据缓存，count（*）执行的较慢。
mysql5.6则通过trace文件进一步向我们展示了优化器是如何选择执行计划的。

4、通过trace分析优化器如何选择执行计划
首先打开trace，设置格式为json，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能完整显示：
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

接下来执行想做trace的sql语句；select * from test where id=10;

最后，检查information_schema.OPTIMIZER_TRACE就可以知道mysql是如何执行sql的：
select * from information_schema.OPTIMIZER_TRACE \G

二、定期分析表和检查表
注意：ANALYZE、CHECK、OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

##分析表：统计信息
mysql> analyze table test;

##检查表：检查错误
mysql> check table test;

##优化表：碎片整理
mysql> optimize table test \G

##回收空间：alter table但是不修改引擎的方法
mysql> alter table test ENGINE=InnoDB;

三、使用SQL 提示
##在查询语句中表名的后面，添加USE INDEX 来提供希望MySQL 去参考的索引列表：
mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G;

##让MySQL 忽略一个或者多个索引，则可以使用IGNORE INDEX 作为HINT：
mysql> explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;

##强制MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为HINT：
mysql> explain select * from sales2 force index (ind_sales2_id) where id > 0 \G;

四、参数配置优化
1、连接相关参数
max-connections         ##最大并发连接数，默认值为151.
max-connect-errors      ##允许连接不成功的最大尝试次数
interactive-timeout     ##指定关闭交互连接前等待的时间，单位是秒，默认值为28800，即8个小时。
wait-timeout            ##指定关闭非交互连接前等待的时间，单位是秒，默认值为28800，即8个小时。
skip-name-resolve       ##禁用DNS解析，如果指定了这个参数，那么创建用户及授权时，HOST列必须是IP而不能使主机名。
back-log                ##连接请求队列中存放的最大连接请求数量。

2、文件相关参数
sync-binlog             ##每进行n次事务后，就触发同步其binlog到磁盘。设置为0时，表示完全由MySQL自己的缓存机制决定何时刷新磁盘文件。
expire-logs-days        ##指定binlog日志文件保留的天数。设置为0时，表示一直保存，需要DBA手工进行清理。
max-binlog-size         ##指定单个binlog日志文件的大小。
local-infile            ##是否允许从客户端本地加载数据。
open-files-limit        ##允许mysqld进场使用的文件描述符数量
lower_case_table_names=1   #linux下mysql安装完后是默认：表名区分大小写，列名不区分大小写； 0：区分大小写，1：不区分大小写
log-error              ##错误日志保存路劲和文件名称
log-bin                ##binlog日志保存路劲和文件名称
general-log=TRUE       ##启用查询日志
slow-query-log=TRUE    ##启用慢查询日志
log-output=FILE,TABLE  ##日志保存在表和文件中（慢查询日志是slow_log表）
long-query-time=2      ##查询超过2秒的SQL都会被记录
min-examined-row-limit=10000    ##查询记录超过10000行的SQL都会被记录（long-query-time和min-examined-row-limit时“并且”关系）
log-slow-admin-statements=TRUE  ##记录管理SQL，默认不记录。
log-queries-not-using-indexes=TRUE  ##记录没有使用索引的SQL，默认不记录。

3、缓存控制参数
binlog-cache-size       ##指定binlog日志事务缓存区的大小。这个参数是为每一个连接的客户端分配内存。该参数设置为8MB或16MB即可瞒住绝大多数场景。
max-binlog-cache-size   ##指定binlog能够使用的最大内存区。一般建议为binlog-cache-size的两倍大小。
binlog-stmt-cache-size  ##指定binlog日志非事务缓存区的大小。其它方面与binlog-cache-size完全相同。
max-binlog-stmt-cache-size  ##指定binlog 非事务缓存区能够使用的最大内存区。一般建议为binlog-stmt-cache-size的两倍大小。
table-open-cache        ##指定MySQL同时能够打开的表对象的数量。
thread-cache-size       ##指定MySQL为快速重用而缓存的线程数量。
query-cache-size        ##指定用于缓存查询结果集的内存区大小。
query-cache-limit       ##用来控制查询缓存，能够缓存的单条SQL语句生成的最大结果集，超出的就不要进入查询缓存。
query-cache-min-res-unit    ##指定查询缓存最小分配的块大小。
query-cache-type        ##0：不使用查询缓存，还需要将query-cache-size参数设置为0；1：缓存除SELECT SQL_NO_CACHE之外的查询结果；2：只缓存SELECT SQL_CACHE的查询结果。
sort-buffer-size        ##指定单个回话能够使用的排序区的大小。（都是针对单个session）全局设置得保守些，对于特殊的查询，可以单独设置session级别的合适参数值。
read-buffer-size        ##指定顺序读取时的数据缓存区大小。（都是针对单个session）
read-rnd-buffer-size    ##指定随机读取时的数据缓存区大小。（都是针对单个session）
join-buffer-size        ##指定表join操作时的缓存区大小。全局设置得保守些，对于特殊的查询，可以单独设置session级别的合适参数值。
net-buffer-length       ##指定单个客户端与服务端交互时，相关信息的缓存区大小。
max-allowed-packet      ##指定网络传输时，单次最大传输的数据包大小。
bulk-insert-buffer-size ##指定批量插入时的缓存区大小。（都是针对单个session）
max-heap-table-size     ##指定内存表（memory引擎表对象）的最大可用空间。
tmp-table-size          ##指定内部内存临时表的最大可用空间。

4、MyISAM专用参数
key-buffer-size         ##指定MyISAM表索引的缓存区大小。（全局参数，所有线程公用）
key-cache-block-size    ##指定索引缓存的块大小。
myisam-sort-buffer-size ##指定MyISAM引擎排序时的缓存区大小。
myisam-max-sort-file-size    #当重建MyISAM索引时，MySQL允许操作的临时文件最大空间。
myisam-repair-threads   ##指定修复MyISAM表时的线程数。

5、InnoDB专用参数
innodb-buffer-pool-size ##指定InnoDB引擎专用的缓存区大小。（全局参数，所有线程公用）
innodb-buffer-pool-instances  ##指定InnoDB缓存池分为多少个区域来使用。
innodb-max-dirty-pages-pct    ##指定InnoDB缓存池中的脏页（即已被修改，但未同步到数据文件）比例。
innodb-thread-concurrency     ##指定InnoDB内部的最大线程数。
innodb-flush-method     ##用来控制InnoDB刷新数据文件及日志文件的方式，仅作用于linux、unix操作系统。
innodb-data-home-dir    ##指定InnoDB数据文件保存的路径，默认将保存在datadir参数指定的路径下。
innodb-data-file-path   ##指定InnoDB数据文件名及文件大小。
innodb-file-per-table   ##指定是否将每个InnoDB表对象存储到独立的数据文件。
innodb-undo-directory   ##指定InnoDB引擎的undo表空间数据文件存储路径。
innodb-undo-logs        ##指定InnoDB引擎的undo 表空间中回滚段的数量
innodb-undo-tablespaces ##指定InnoDB引擎的undo 表空间的数量，每个undo 表空间都是独立的.idb文件，因此可以理解为undo 数据文件的数量。
innodb-log-files-in-group    ##innodb日志文件组数
innodb-log-group-home-dir    ##innodb日志文件目录
innodb-log-file-size         ##innodb日志文件大小
innodb-log-buffer-size       ##innodb日志缓存区大小
innodb-flush-log-at-trx-commit  ##指定InnoDB刷新log buffer中的数据到日志文件的方式。
innodb-flush-log-at-timeout     ##指定每隔n秒刷新日志。
innodb-lock-wait-timeout     ##指定innoDB事务等待行锁的超时时间，以秒为单位。
innodb-fast-shutdown         ##这个系统参数是用来控制innoDB的关闭模式。若指定为0模式，则要等到回话关闭、所有事物结束、缓冲区的数据刷新到磁盘等，类似oracle数据库中的shutdown normal；若指定为1模式，类似oracle数据库中的shutdown immediate，也是默认设置值；若指定为2模式，类似oracle数据库中的shutdown abort。若要修改日志文件配置，那么首先检查innodb_fast_shutdown系统参数的值。若当前值为2，则必须先将其修改为1，才可以修改日志文件配置。
innodb-autoextend-increment  ##innodb系统表空间每次扩展大小