MySQL 语句优化

一、创建表数据

在查询优化前，首先需要创建表和数据。

创建sales表：

create table sales (
id int not null auto_increment,
moneys int not null,
year varchar(100) not null,
company_id int,
primary key (id)
);

为了熟悉mysql的存储过程，批量插入数据使用了存储过程：

mysql> delimiter $$
mysql> create procedure insert_sales()
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE i<1000
    -> DO
    -> INSERT INTO sales(`year`,moneys,company_id) values (CONCAT("year-",i),i+100,i);
    -> SET i = i+1;
    -> END WHILE;
    -> commit;
    -> END $$;
Query OK, 0 rows affected

然后再查询编辑器里调用存储过程：

CALL insert_sales();

[SQL] CALL insert_sales();
受影响的行: 0
时间: 26.004ms

至此，表和数据已经建立好。

二、查询优化

1、优化 SQL 语句的一般步骤：

1）通过 show [session | global] status了解各种 SQL 的执行频率， session 级表示当前链接，global 级（自数据库上次启动至今）的统计结果，默认是session级

show status like 'Com_%'，表示当前 session 中所有统计参数的值

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

Com_select：执行 SELECT操作的次数，一次查询只累加 1。

Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。

Com_update：执行 UPDATE 操作的次数。

Com_delete：执行 DELETE 操作的次数。

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

1）通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries [=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

2）慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

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

如：explain select moneys from sales; 

 

id：数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，为NULL表示这是一个结果集，不需要使用它来进行查询

select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等。

table：输出结果集的表。

type：表示表的连接类型，性能由好到差的连接类型为 system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）、ref（与eq_ref类似，区别在于不是使用primary key 或者 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：执行情况的说明和描述。

4、确定问题并采取相应的优化措施

三、优化之索引

mysql索引类型目前只有两种BTREE和HASH,具体跟存储引擎有关，MyISAM存储引擎和InnoDB存储引擎只支持BTREE类型，MEMORY和HEAP存储引擎支持BTREE和HASH

1、创建索引

create index ind_sales_year on sales(`year`)

2、 使用索引

1）对创建的多列索引，只要查询条件中用到了最左边的列，该索引一般会被使用

2）对于使用like查询，如果后面接的是常量或者'%'符号不位于第一个字符时，索引才有可能被使用，如果like后面跟的是一个列的名字，那么索引也不会被使用

3）如果对大的文本进行搜索，应该使用全文索引，而不是like '%..%'

4）如果列名是索引，则使用column_name is null 将使用索引

3、查看索引使用情况

1）如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用

2）Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引

 show status like 'Handler_read%'

使用以上命令来查看索引使用情况。

四、两个简单实用的优化方法

1、定期分析表和检查表
1）analyze talbe table_name:本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划，
2）check table table_name 检查表的作用是检查一个表或多个表是否有错误，也可以检查视图是否有错误

2、定期优化表

optimize table table_name 对含有可变长度的表进行多次修改后，可以使用这个命令进行表优化，这个命令可以消除删除或者更新后的空间浪费
注意：analyze, check, optimize命令执行期间会对表进行锁定

五、常用 SQL 的优化

1、大批量插入数据。（暂时还没有实战经验，可以参考《深入浅出MySQL全文》--18.4.1章节）

2、优化 INSERT 语句

尽量使用多个值表的 INSERT 语句，如：insert into sales values("1990",1,2),("1990",1,3),("1990",1,4)…

3、优化 GROUP BY 语句

默认情况下，MySQL 对所有 GROUP BY col1，col2....的字段进行排序。这与在查询中指定ORDER BY col1，col2...类似。

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

4、优化 ORDER BY 语句

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。

以下语句可以使用到索引：

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以下语句将不会使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2；
--对不同的关键字使用 ORDER BY

上面两个标红的部分不是特别理解，key_part1,key_part2代表的是相同的字段？和下面的5,6有区别？

5、优化嵌套查询

有些情况下，子查询可以被更有效率的连接（JOIN）替代

6、优化 OR 条件

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

7、使用 SQL 提示

1）USE INDEX。explain select * from sales use index (ind_sales_id) where id = 3。添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引

2）IGNORE INDEX  如果用户只是单纯地想让 MySQL 忽略一个或者多个索引

3）FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。 注意与1）的区别，1）是参考，这里是强制。