SQL优化
优化SQL语句的一般步骤：
通过SHOW STATUS了解各SQL执行效率：
SHOW STATUS;SHOW STATUS LIKE;
如，
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 |...具体各参数详解可见：
下面几个参数便于用户了解数据库的基本情况：
- 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, allpossible_keys: NULL --查询时可能使用的索引 key: NULL --实际使用的索引 key_len: NULL --索引字段的长度 ref: NULL --相对于key，若值为func，则表示从函数中获取的结果集 rows: 12 --扫描行的数量 Extra: NULL --执行情况的说明和描述1 row in set (0.00 sec)
具体详情可参考：
确定问题并采取相应措施：
- 当我们通过上面的一些措施发现性能问题所在后，就可以进行优化了，比如对一些查询字段添加索引等。
索引问题：
- 索引是数据库优化最常用也是最重要的手段之一。
索引的存储分类：
- MySQL中索引的存储类型目前只有BTREE和HASH；
- MyISAM和InnoDB存储引擎仅支持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: refpossible_keys: idx_sales_year key: idx_sales_year key_len: 4 ref: const rows: 6 Extra: NULL1 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where1 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where1 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: ALLpossible_keys: idx_sales_year key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where1 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: NULLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE1 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: refpossible_keys: idx_sales_country key: idx_sales_country key_len: 22 ref: const rows: 1 Extra: Using index condition1 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: ALLpossible_keys: idx_sales_country key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where1 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: ALLpossible_keys: idx_sales_country key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where1 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: refpossible_keys: idx_sales_country key: idx_sales_country key_len: 22 ref: const rows: 1 Extra: Using index condition1 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 dataALTER TABLE tb1_name ENABLE KEYS;
- 对于InnoDB表，可以这样优化：
1. 将导入的数据按主键的顺序排列，再导入；
2. 导入数据前关闭唯一性校验：：
SET UNIQUE_CHECKS=0;-- load dataSET UNIQUE_CHECKS=1;3. 导入数据前关闭自动提交：
SET AUTOCOMMIT=0;-- load dataSET 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 12 Extra: Using temporary; Using filesort1 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 12 Extra: Using temporary1 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: constpossible_keys: PRIMARY,idx_users_id key: PRIMARY key_len: 4 ref: const rows: 1 Extra: NULL1 row in set (0.00 sec)-- USE INDEX告诉MySQL使用indexmysql> EXPLAIN SELECT * FROM users USE INDEX (idx_users_id) WHERE id = 3\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: refpossible_keys: idx_users_id key: idx_users_id key_len: 4 ref: const rows: 1 Extra: NULL1 row in set (0.00 sec)其他:-- IGNORE INDEX告诉MySQL忽略索引-- FORCE INDEX告诉MySQL强制使用索引
不吝指正。