mysql查询优化小技巧

1. 开启缓存

mysql> show variables like 'query_cache%';
mysql> set global query_cache_type=1;
mysql> set global query_cache_size=1024*1024*32

注意：

1. 查询缓存存在判断是严格依据select语句本身的：严格保证sql一致。
2. 如果查询时包含动态数据，则不能被缓存。
3. 如果不想使用缓存，可以使用 SQL_NO_CACHE 语法提示。

2. in型子查询

select goods_id,cat_id,goods_name from good where cat_id in(select cat_id form category where parent_id=6);
这条语句执行会非常慢，因为它会扫描goods全表，逐行与category表对照
原因：mysql的查询优化器，针对in型做了优化，优化成了exists的执行效果。
改进：用连接查询代替子查询
select goods_id,g.cat_id,g.goods_name from goods as g inner join (select cat_id from category where parent_id=6) as t;

3. from 子查询

内层 from 语句查到的临时表，没有索引，所以from返回的内容要尽量少

4. count()优化

没有查询条件时count(*)非常快，不需要查表。但当有查询条件时，速度将减慢。
可以使用缩小范围的方法优化查询。
eg.

需要统计good_id>100的总数时一般会写为：
select count(*) form goods where good_id>100;
优化为：
slect (select count(*) from goods)-(select count(*) from goods where id<100);
这样，就把范围由无限大缩小到了100

5. group by 优化

• 分组用于统计，而不用于筛选数据。
• 用索引避免产生临时表和文件排序
• A,B表连接查询，group by和order by 的列尽量相同，而且列应该为A的列

默认情况下，MySQL 对所有 group by col1, col2, …… 的字段进行排序。这与查询中指定 order by col1,col2,…… 类似。因此，如果显示包括一个包含相同列的 order by 子句，则对 MySQL 的实际执行性能没有什么影响。

如果查询包括 group by 但用户想要避免排序结果的消耗，则可以指定 order by null 禁止排序。

select col1 from table group by col2 order by null;

6. union 优化

union all 不过滤 效率提高,如非必须,请用union all
因为 union去重的代价非常高, 放在程序里去重.

7. limit & 分页优化

limit offset,n 当offset非常大时，效率极低。mysql并不是跳过offset行，然后单取n行，而是取offset+n行，返回放弃前offset行，返回n行。
优化：

1. 从业务上解决
   不允许翻过100页（百度也是如此）
2. 利用索引
3. id,name from goods inner join (select id from goods limit 5000000,10) as tmp using(id);
4. 记录上一次取出的最后一条数据，把 limit m, n 语句转化为 limit n。

8. 消除msyql内部临时表

在一些sql请求中，mysql会创建临时表，可能创建到内存中，也可能由内存中转存到磁盘。
会创建临时表的查询：

1. group by 的列没有索引，必创建临时表
2. order by 与 group by 为不同列时，或多表联查时order by,group by 包含的列不是第一张表的列，必产生临时表。
3. distinct 与 order by 一起使用可能会产生临时表
4. union合并查询时会用到临时表

9. 大批量插入数据

对于 myisam 引擎

如果是空的 myisam 表，默认就是先导入数据才创建索引的，不存在优化问题。
对于非空的 myisam 表，在一次性插入大量数据时，可以通过设置 disable keys 和 enable keys 来提高导入的效率。

# 假设给 test 表一次性插入大量数据  
alert table test disable keys;

loading the data ……

alert table test enable keys; 

对于 innodb 引擎

disable keys 的方式适用于 myisam 引擎，但不适用于 innodb 引擎。

1. 因为 innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
2. 在导入数据前执行 set unique_checks=0 , 关闭唯一性校验，在导入结束后执行 set unique_checks=1,恢复唯一性校验，可以提高导入的效率。
3. 如果应用使用自动提交的方式，建议在导入前执行 set autocommit=0，关闭自动提交，导入结束后再执行 set autocommit=1，打开自动提交，也可以提高导入的效率。

10. 优化 insert 语句

同一客户端一次插入多行

使用多个值表的 insert 语句,可以减少客户端与数据库之间的连接、关闭等资源消耗

insert into test values (1,1),(2,2),(3,3)……  

从不同客户插入很多行，可以使用 insert delayed 语句得到更高的素的。

delayed 的含义是让 insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正的写入磁盘，这比每条土局分别插入要快的多；
low_priority 刚好相反，在所有其他用户对表的读写完成后才进行插入（比如记录日志的场景）

将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）

如果进行批量插入，可以通过增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 myisam 表使用。

当从一个文本文件装载一个表时，使用 load data infile 。这通常比使用很多 insert 语句快 20 倍。

11. 优化 order by 语句

mysql 的两种排序方式

1. 通过有序索引顺序扫描直接返回有序数据，这种方式在使用 explain 分析查询时显示为 using index ，不需要额外的排序，操作效率极高。
2. 通过对返回的数据行进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫做 filesort 排序。filefort 并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小。

• filesort 是通过相应的排序算法，将取得的数据在 sort_buffer_size 系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size 设置的排序区是每个线程独占的，所以同一个时刻，mysql 中存在多个sort buffer 排序区。

优化思路

尽量减少额外的排序，通过索引直接返回有序数据。
where 条件和 order_by 使用相同的索引，并且 order_by 的顺序和索引的顺序相同，并且 order by 的字段都是升序或者降序。否则肯定需要额外的排序操作，这样就会出现 filesort 。

filesort 的优化

在某些不得不使用 filesort 的场景中，需要想办法加快 filesort 的操作。对于 filesort ，MySQL 有两种排序算法。

• 两次扫描算法：

首先根据条件取出排序字段和行指针信息，之后在排序区 sort_buffer 中排序。如果排序区 sort buffer 不够，则在临时表 temporary table 汇总存储排序结果，完成排序后根据行指针回表读取记录。这种算法需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机 I/O 操作；优点是排序的时候内存开销较少。

• 一次扫描算法：

一次性取出满足条件的行的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集，排序的时候内存开销较大，但是排序效率比两次扫描算法要高。

mysql 通过比较系统变量 max_length_for_sort_data 的大小和 query 语句取出的字段总大小来判断使用哪种排序算法。如果 max_length_for_sort_data 更大，那么使用第二种优化之后的算法，否则使用第一种算法。
适当加大系统变量 max_length_for_sort_data 的值，能够让 MySQL 选择更优化的 filesort 的排序算法，当然，设置过大，会造成cpu利用率过低和磁盘 I/O 过高

适当加大 sort_buffer_size 排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能不限制加大 sort_buffer_size 排序区，因为 sort_buffer_size 参数时每个线程独占的，所以要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。

尽量只使用必要的字段，select 具体的字段名称，而不是 select * 选择所有字段，这样可以减少排序区使用，提高 sql 性能。

12. 使用 sql 提示

sql 提示（sql hint） 是优化数据库的一个重要手段，简单来说就是在 sql 语句中加入一些人为的提示来达到优化操作的目的。

select sql_buffer_results * from ……

这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。
这能再遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。

常用的 sql 提示：

• use index 提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
• ignore index 忽略一个或者多个索引
• force index 强制 MySQL 使用一个特定的索引。