十二、MySQL中常用的SQL优化 - 系统的撸一遍MySQL

2019独角兽企业重金招聘Python工程师标准>>>

插入优化

MyISAM批量导入

非空的表导入的时候会同时处理索引，导致导入效率低下，所以通过以下方式可以快速导入。

//关闭非唯一索引
ALTER TABLE table_name DISABLE KEYS;
load data infile 'path' in to table table_name;
//开启非唯一索引
ALTER TABLE table_name ENABLE KEYS;

InnoDB批量导入

1、按主键顺序导入，InnoDB的数据按主键排列的B+Tree结构，所以数据按主键排序可以提升效率。

2、关闭唯一性校验

//关闭唯一校验
SET UNIQUE_CHECKS=0;
load data infile 'path' in to table table_name;
//开启唯一校验
SET UNIQUE_CHECKS=1;

3、关闭自动提交

//关闭自动提交
SET AUTOCOMMIT=0;
load data infile 'path' in to table table_name;
//提交数据
COMMIT;
//开启自动提交
SET AUTOCOMMIT=1;

INSERT优化

1、合并多条INSERT语句，减少客户端与MySQL的通讯次数，减少SQL解析次数。

2、采用 INSERT DELAYED立即执行SQL语句（在内存中，之后写入到硬盘）。

3、表分区、独立表空间，将数据分布在不同硬盘提高IO吞吐量。

查询优化

ORDER BY语句

两种排序方式

排序工作如果查询的数据在索引中，可以直接通过索引返回数据（using index），否则进行filesort。

filesort会在每个线程的 sort_buffer_size规定的排序区进行排序，如果超过size大小，会在硬盘进行归并排序。

两种filesort排序方式

1、两次排序

读取排序字段和条件字段在sort_buffer中进行排序，如果超过大小会在硬盘中排序。排序完成后读取硬盘数据。这种方式节省内存，但是可能造成大量IO。

2、一次扫描

一次去处所有满足条件的字段，然后在sort_buffer中排序，排序后直接返回。这种方式效率高但是消耗内存比较大，容易导致大量swap。

MySQL通过 max_length_for_sort_data来决定使用哪一种方式进行排序。

排序优化方式

1、尽量使用索引进行排序，同时减少排序返回的字段

2、根据实际情况权衡后，调整sort_buffer的大小

3、通过调整排序字段顺序，充分利用索引排序。

4、适当的limit语句来避免全表扫描。

GROUP BY 语句

group by默认会对分组字段进行排序，所以使用 order by null可以减少排序损耗。

上图中第二个语句没有了filesort。

子查询

使用 join 来代替字查询。

可以看到rows减少了非常多。

索引控制

//建议使用索引
SELECT * FROM user use index(索引名);
//忽略某个索引
SELECT * FROM user ignore index(索引名);
//强制使用索引
SELECT * FROM user force index(索引名);

 

转载于:https://my.oschina.net/fuckphp/blog/787442