MySQL管理与优化(14):SQL优化

原创
2014/07/13 02:35
阅读数 373

SQL优化

优化SQL语句的一般步骤:

通过SHOW STATUS了解各SQL执行效率:

SHOW STATUS;
SHOW STATUS LIKE <pattern>;

如,

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_alter_tablespace      | 0     |
| Com_alter_user            | 0     |
| Com_analyze               | 0     |
| Com_begin                 | 0     |
| Com_binlog                | 0     |
| Com_call_procedure        | 0     |
| Com_change_db             | 0     |
| Com_change_master         | 0     |
...
具体各参数详解可见:

http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

下面几个参数便于用户了解数据库的基本情况:

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

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

  • 可通过慢查询日志定位那些执行效率较低的SQL语句,用--log-slow-queries[=file_name]选项启动。
  • 慢查询日志会在查询结束后才记录,可通过SHOW PROCESSLIST查看当前MySQL的线程,锁表等状态。

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

  • 通过EXPLAIN关键字可分析sql语句执行过程,如:
mysql> EXPLAIN SELECT * FROM sales \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE --查询类型, 有SIMPLE,PRIMARY,UNION,SUBQUERY等值
        table: sales  --输出结果集的表
         type: ALL    --表的连接类型:性能由高到低为:system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery,index_subquery, range, index, all
possible_keys: NULL   --查询时可能使用的索引
          key: NULL   --实际使用的索引
      key_len: NULL   --索引字段的长度
          ref: NULL   --相对于key,若值为func,则表示从函数中获取的结果集
         rows: 12     --扫描行的数量
        Extra: NULL   --执行情况的说明和描述
1 row in set (0.00 sec)

具体详情可参考:

http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_ref

确定问题并采取相应措施:

  • 当我们通过上面的一些措施发现性能问题所在后,就可以进行优化了,比如对一些查询字段添加索引等。

索引问题:

  • 索引是数据库优化最常用也是最重要的手段之一。

索引的存储分类:

  • MySQL中索引的存储类型目前只有BTREEHASH
  • MyISAMInnoDB存储引擎仅支持BTREE
  • MEMORY/HEAP存储引擎支持BTREE和HASH。

使用索引:

  • 多列索引中,仅查询第一列时才可能用到索引,如:
--创建多列索引
mysql> CREATE INDEX idx_sales_year on sales(year, profit);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

--仅第一列有可能使用索引
mysql> EXPLAIN SELECT * FROM sales WHERE year = 2005\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ref
possible_keys: idx_sales_year
          key: idx_sales_year
      key_len: 4
          ref: const
         rows: 6
        Extra: NULL
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE profit = 2012\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
1 row in set (0.05 sec)
  • 对于LIKE子句,仅当%不在第一个字符时,才有可能使用到索引:
mysql> EXPLAIN SELECT * FROM sales WHERE year LIKE '%2'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE year LIKE '2%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: idx_sales_year
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
1 row in set (0.00 sec)
  • 对于大文本进行查询,使用全文索引而不使用LIKE %...%;
  • 对于NULL不会使用索引:
mysql> EXPLAIN SELECT * FROM sales WHERE year is null\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: Impossible WHERE
1 row in set (0.00 sec)

存在索引但不使用索引:

有些情况下,虽然存在索引,MySQL却不会使用索引:

  • 当key_part1均匀分布在1和100之间时,如:
SELECT * FROM table_name WHERE key_part1 > 1 AND key_part2 < 100;
  • 如果使用MEMORY/HEAP表并且WHERE条件中不使用"="进行索引列,那么不会用到索引,HEAP表只有在"="的条件下才会使用索引。
  • or分割开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引不会被用到,如:
mysql> EXPLAIN SELECT * FROM sales WHERE country = 'ch%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ref
possible_keys: idx_sales_country
          key: idx_sales_country
      key_len: 22
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE country = 'ch%' OR product = 'tn1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: idx_sales_country
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
1 row in set (0.00 sec)
  • 不是多列索引的第一列,也不会用到索引。
  • LIKE以%开始,也不会用到索引。
  • 若索引列类型是字符串,则一定要加上引号,否则也不会用到索引:
mysql> EXPLAIN SELECT * FROM sales WHERE country = 121\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: idx_sales_country
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE country = '121'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ref
possible_keys: idx_sales_country
          key: idx_sales_country
      key_len: 22
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

查看索引使用情况:

mysql> SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 3     | 如果索引正在工作,值会增加
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 76    | 值越大,查询效率越慢
+-----------------------+-------+

两个简单实用的优化方法:

定期分析表和检查表:

  • 分析表分析的结果可以得到准确的统计信息,分析期间会使用读取锁对表加锁,仅对MyISAM, DBD, InnoDB表有作用,其语法为:
ANALYZE [LOCA | NO_WRITE_TO_BINLOG] TABLE table1, table2, ...

如:

mysql> ANALYZE TABLE sales;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| test.sales | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.18 sec)
  • 检查表的作用是检查一个或多个表是否有错误,对MyISAM和InnoDB有作用,其语法为:
CHECK TABLE tb1_name [, tb1_name] ... [option] ... option = {QUICK|FASE|EXTENDED|CHANGED}
如:
mysql> CHECK TABLE sales;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| test.sales | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.00 sec)
  • 检查表对视图也试用,如视图引用的表不存在了,就能检查出错误。

定期优化表:

  • 优化表具有进行一些空间碎片整合,删除浪费的空间等作用,其语法:
OPTIMIZE [LOCAL | NO WRITE_TO_BINLOG] TABLE tb1_name, tb2_name,...
  • ANALIZE, CHECK, OPTIMIZE等操作都会对表进行锁定,所以应在数据库不繁忙时进行。

常用的SQL优化:

大批量插入数据:

  • 对于MyISAM的表,可通过关闭MyISAM表的非唯一索引的更新来提高插入速度,语法为:
ALTER TABLE tb1_name DISABLE KEYS;

--loading data

ALTER TABLE tb1_name ENABLE KEYS;
  • 对于InnoDB表,可以这样优化:

      1. 将导入的数据按主键的顺序排列,再导入;

      2. 导入数据前关闭唯一性校验::

SET UNIQUE_CHECKS=0;
-- load data
SET UNIQUE_CHECKS=1;
      3. 导入数据前关闭自动提交:
SET AUTOCOMMIT=0;
-- load data
SET AUTOCOMMIT=1;

优化INSERT语句:

  • 对于同一客户端连接,使用多值插入
  • 索引文件和数据文件分在不同的磁盘上存放
  • 如果进行批量插入,可以增加bulk_insert_buffer_size变量值的方法来提高速度,但这只能对MyISAM表使用。
  • 当从一个文本文件载入一个表示=时,使用LOAD DATA FILE会比INSERT快很多。

优化GROUP BY语句:

  • 默认GROUP BY会执行排序操作,可以通过ORDER BY NULL来禁止排序:
mysql> EXPLAIN SELECT * FROM sales GROUP BY product\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sales GROUP BY product ORDER BY NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using temporary
1 row in set (0.04 sec)

优化ORDER BY语句:

  • MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。当WHERE和ORDER BY使用相同的索引,且ORDER BY的顺序与索引顺序相同,并且ORDER BY的字段都是升序或都是降序。

优化嵌套查询:

  • 可用JOIN等子句来替换嵌套查询。

优化OR子句:

  • 应考虑为OR中的列添加索引

使用SQL提示:

  • 使用临时结果集,在遇到表锁定或花很长时间将结果传给客户端时有所帮助。

SELECT SQL_BUFFER_RESULTS * FROM ...



  • 针对INDEX来告诉MySQL执行相关的索引操作:

mysql> EXPLAIN SELECT * FROM users WHERE id = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: const
possible_keys: PRIMARY,idx_users_id
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

-- USE INDEX告诉MySQL使用index
mysql> EXPLAIN SELECT * FROM users USE INDEX (idx_users_id) WHERE id = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: ref
possible_keys: idx_users_id
          key: idx_users_id
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

其他:
-- IGNORE INDEX告诉MySQL忽略索引
-- FORCE INDEX告诉MySQL强制使用索引



不吝指正。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
5 收藏
分享
打赏
0 评论
5 收藏
0
分享
返回顶部
顶部