mysql 优化

mysql 优化
1、group by 

    点击打开链接

2、order by 
1）一般情况下，可以使用一个索引(也可以是多列索引)来满足order by 子句，不要额外的排序。如果查询要关联多个表时，只有当order by 子句引用的字段全部为第一个表时，才能使用索引做排序。
2）where 条件和order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 字段都是升序或者降序。这种情况需要是组合索引，如果是多个索引order by 则不会用到索引。
示例：假如表tb_a，有key1、key2 是两个不同列的索引；key_part1，key_part2 是一个组合索引的两部分，顺序为(key_part1，key_part2)）
使用索引：
select * from tb_a order by key_part1, key_part2;  //索引顺序是组合索引的顺序
select * from tb_a order by key_part1 desc, key_part2 desc;  // 排序方式相同

select * from tb_a where key_part1=1 order by key_part1 desc, key_part2 desc; // where 条件和order by 的使用的索引顺序相同，并且排序方式相同。

select * from tb_a where key_part1=1 order by key_part2;  //B-Tree 索引是有序的

不能使用到索引：
select * from tb_a order by key_part1 desc, key_part2 asc;//排序方式不同
SELECT * FROM tb_a WHERE key2=constant ORDER BY key1;   // where 条件和排序的不同

SELECT * FROM t1 ORDER BY key1, key2;   // 两个不同key 的排序

order by 有两种排序算法
1、两次扫描：第一次取出排序字段和记录的指针，然后在排序内存（sort_buffer_size 设置大小）中进行排序，如果排序内存不够，会创建临时表，把排序结果存放在临时表中，排序完后根据记录指针再去查询数据。这种算法会产生大量随机I/O，CPU 使用率低，I/O 占用率高。优点是内存使用少。

对应的执行计划Extr:Useing index,Useing where(如果没有覆盖索引，要从数据文件中取数据)
2、一次扫描：一次把所有的数据都查询出来，再把数据进行排序。这种算法排序会在内存中，如果排序内存（sort_buffer_size）不够大的话就会把查询结果分块在磁盘中排序，然后再合并。
这种排序效率比两次扫描高。

mysql 通过系统变量 max_length_for_sort_data 设置，来确定使用那一种排序算法。
通过 max_length_for_sort_data 和查询结果的总字段大小来判断，如果max_length_for_sort_data 大，那么就使用一次扫描算法，否则使用两次扫描算法。
max_length_for_sort_data 设置的过大，就会使用cpu 使用率低，I/O 使用率过高（FileSort 排序），cpu  I/O不平衡。

sort_buffer_size  是mysql 内存排序使用内存的大小设置系统变量，通过设置这个变量可以让更多的排序在内存中进行。
每一个线程都会有这样一个排序内存大小占用，所以如果设置过大，就会造成SWAP 严重，服务器性能下降。设置这个参数要根据连接数和内存大小来设置。

3、or
对于 or 子句，如果要利用索引，则or 之间的每个条件列都必须用到索引

4、count  
MyISAM   可以记录每个表有多少行，如果查询没有条件可以很快把结果返回
InnoDB  只能通过别的方式（表，缓存等）记录表的行数，如果表的id是自增的且连续可以使用max(id)-min(id) 方式统计所有行（不太实用）

count(*) 和 count(1)
1）表有主键 count(1)、count(*)、count(主键) 没有区别（可以看执行计划）相差很小，会自动优化到某一行。如果count(col) 如果col 不是主键也没有索引就会全表扫描了。 
2）count(1)、count(*) 在相同的表是没有区别的，mysql 是提供优化的。（测试数据 140万、mysql5.7、InnoDB引擎，在没有主键，只有一个索引（把主键改成了一般索引），两种情况下测试所得，执行计划也没有任何的区别）

总结：count(*) 和 count(1) 没有区别的，mysql 会自动优化

count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行

5、limit
测试数据：表tb_a，主键为id，有50列，94万数据。
1）select * from tb_a limit 800000,10;  
耗时5秒多，mysql 会扫描80万行后取出 10条记录
优化：
select a.* from tb_a a join (select id from tb_a limit 800000,10) b on a.id = b.id;  
耗时不到1秒。通过子查询直接扫描主键索引，不用扫描数据文件。再根据主键去查询数据文件，就比较快了。

2）排序分页优化思路，如order by col1,col2,...  limit start,len。 主要优化的是 where 条件和 order by  优化，请参考order by 优化
a) order by  col ,  col 列是索引列，则可以使用到索引（注意和where条件关系），索引必须是有序索引。如果是组合索引注意要遵守“最左匹配”原则
下面这个是最优的查询了（id 是主键，如果不对请指正），95万数据，运行耗时0.5秒
SELECT a.* FROM tb_a a

JOIN (SELECT id  FROM tb_a ORDER BY idLIMIT 800000,10) b ON a.id=b.id;

3） 比如知道第N 页的id 是最大的值10000，那么limit 可以写成：

select * from tb1
where id>100000
limit 20

4） limit 程序设计
1、如果20一页，每次可以取出21条数据，如果有第21条就显示下一页。
2、还有就是获取并缓存较多的数据（如缓存1000条数据），然后分页从缓存中获取。
3、使用explain 的结果中的rows 列的值作为结果集的总数近似值。这个值不够精确。

6、批量插入：
1）MYISAM 表：
关闭表的唯一索引，然后导入数据，再打开表的唯一索引。命令如下：
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
导入数据到一个MYISAM 表默认是导入数据才创建索引的，所以导入数据到空表时不用设置。
2）InnoDB 表：
a) InnoDB 表的数据是按照主键的顺序保存的，所有导入数据按照主键排列好。如果表没有主键，InnoDB 会创建一个内部列作为主键。
b) 导入数据前关闭唯一性校验（SET UNIQUE_CHECKS=0），导入数据后开启唯一性校验（SET UNIQUE_CHECKS=1）
c) 关闭mysql 的自动提交 SET AUTOCOMMIT=0，导入数据后开启自动提交 SET AUTOCOMMIT=1。导入数据后不要忘记提交
3) 同一客户端插入多行时，使用 insert into tb_name(col,col1,col2) values(v,v1,v2),(v,v1,v2),(v,v1,v2),(v,v1,v2)  这种方式比较快
4) 不同客户端插入多行时，使用INSERT DELAYED 语句，能让insert 马上执行，其实数据被放在内存队列中，并没有真正的写入磁盘。LOW_PRIORITY 刚好相反，在其它的用户执行完读写后才执行insert
5) 批量可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，这只能对 myisam 表使用
6) 当从一个文件装载一个表时，使用load data file ,比insert 快

7、join

mysql 执行嵌套循环关联操作，即mysql 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有的行。然后根据各个表的行，返回查询是需要的列。mysql 会在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行，mysql 会返回到上一个层次关联表，看看是否能找到更多的匹配数据，依此类推迭代执行。
如UNION 查询，mysql 先将一系列单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION 查询。
mysql 的执行计划如图：

执行计划的关联顺序和sql的关联顺序一致，重新定义关联的顺序是mysql优化器重要的一部分功能，不过有时候优化器给出的不是最优的关联顺序，可以通过STRAIGHT_JOIN 关键字重写查询，让优化器按照自己定义的关联顺序执行。

1、确保ON 或者USING 子句上的列上有索引。在创建索引的时候要考虑到关联的顺序。一般来说，除非有其它的理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2、确保任何group by  和  order by  中的表达式只有一个表中的列，这样mysql 才能使用索引来优化这个过程。

8、UNION

mysql 通过创建并填充临时表的方式来执行UNION查询，因此要将where ， LIMIT，ORDER　BY　写到每一个UNION 子句中，以便优化器可以充分利用这些条件优化。
除非确实需要服务器消除重复的行，否则一定要使用UNION ALL，如果没有ALL   mysql 会在临时表中加上DISTINCT 选项，对整个临时表做唯一性检查，这个代价非常高。