MySQL SQL 优化

一、优化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系统表空间每次扩展大小

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值