MySQL优化技巧

* 不断完善中

 

GROUP BY默认会对字段排序

 

explain select * from tbl_name group by col;

Explain的结果是：Extra: Using temporary; Using filesort

若业务逻辑上无需排序，则加上order by null，这时explain的结果就只有：Using temporary

少了Using filesort会快很多哦！

 

=========================

 

大批量数据插入优化方法

 

最高效的大批量插入数据的方法：

load data infile ‘/path/to/file’ into table tbl_name;

如果没有办法先生成文本文件或者不想生成文本文件，可以一次插入多行：

insert into tbl_name values (1,2,3),(4,5,6),(7,8,9)…

一条sql语句的最大长度是有限制的。若还不想这样，可尝试MySQL的prepare，应该都会比硬生生的逐条插入要快许多。
如果数据表有索引，建议先暂时禁用索引：

alter table tbl_name disable keys;
插入完毕之后再激活索引：

alter table tbl_name enable keys;
对MyISAM表尤其有用。避免每插入一条记录系统更新一下索引。

 

警示：传统开发中，所实现的批量插入方法，都是通过循环单条插入方法来完成的，这就造成了性能问题。

 

=========================

 

最快复制表结构和/或数据的方法

 

create table clone_tbl select * from tbl_name limit 0;
只会复制表结构，索引不会复制，如果还要复制数据，把limit 0去掉即可。

 

=========================

 

加单/双引号查询 整型和字符型 字段时的索引作用

 

模拟环境，表：person，字段：id(int 主键 自增) name(varchar) age(int 索引)。

以下三句均能查出结果并使用age索引

explain select * from person where age="27"

explain select * from person where age='27';

explain select * from person where age=27;

若将age改为varchar，仍然执行以上三句SQL，其中最后一句，虽能查出结果但未使用上age索引。

总结：在字符串类型的字段上查询，必须加单或双引号才能正确使用索引，但在整型字段上加或不加单双引号无差别。

 

=========================

 

对大长度字符串类型的字段建前缀索引

 

有时候我们的表中有varchar(255)这样的字段，而且我们还要对该字段建索引，一般没有必要对整个字段建索引，建立前8~12个字符的索引应该就够了，很少有连续8~12个字符都相等的字段。为什么？更短的索引意味索引更小、占用CPU时间更少、占用内存更少、占用IO更少和很更好的性能。

建立前缀索引方法：create index col on tbl_name(col(index_length));

注：比如实际查询的值超出index_length时，并非无法查询正确结果，也并非无法使用该索引，只是会扫描更多的行数。

 

=========================

 

重构分页优化思想

 

常规分页大页码SQL分析：

explain SELECT * FROM tbl_name ORDER BY col DESC LIMIT 10000,20

... rows: 10020 ...

LIMIT 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。

所以这类分页算法，在大并发应用中是相当消耗性能的，因为翻页页码大时，总是要扫描这么多行。

 

优化思想：

给分页提供线索，尽量减小LIMIT m,n中m的值，比如：

SELECT * FROM tbl_name WHERE col > 9527 ORDER BY col ASC LIMIT 20,20;

SELECT * FROM tbl_name WHERE col < 9500 ORDER BY col DESC LIMIT 40,20;

其中9527和9500是当前页的最大和最小值(线索)，LIMIT m,n是相较于当前页的偏移量值；

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。