mysql 优化

mysql 优化
1、group by 
sql语句中如果有group by c1,c2,c3 相当于在sql 中指定order by c1,c2,c3 。如果显示指定一个order by子句，mysql 会对group by 进行优化。 如果查询包括group by ，但是要避免排序的消耗，可以指定order by null 来禁排序

2、order by 
1）一般情况下，可以使用一个索引来满足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 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 的排序

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;

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 (待续)