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 选项,对整个临时表做唯一性检查,这个代价非常高。