文章目录
- 一、大批量插入数据
- 二、优化insert
- 三、优化order by
- 四、优化group by
- 五、优化子查询
- 六、优化OR
- 七、优化分页查询
一、大批量插入数据
当使用load 命令导入数据的时候,适当的设置可以提高导入的效率
1) 主键顺序插入
load data local infile '/root/data1.log' into table `tb_user` fields terminated by ',' lines terminated by 'n';
插入ID顺序排列数据:
插入ID无序排列数据:
2) 关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率
3) 手动提交事务
如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行 SET AUTOCOMMIT=1,服务器托管网打开自动提交,也可以提高导入的效率
二、优化insert
- 逐条插入改为批量插入
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
优化后的方案为 :
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
- 在事务中进行数据插入
start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;
- 数据有序插入
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(2,'Cat');
优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');
三、优化order by
MySQL中有两种排序方式:using filesort 和 using index
- filesort :对返回数据进行排序,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
- index:有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高
我们现在有联合索引A、B,当A相等时,B升序排列,比如:
A:1 1 1 1 2 2 3 3 3
B:2 3 4 5 3 4 5 1 2
对于Filesort , MySQL 有两种排序算法:
1) 两次扫描算法 :MySQL4.1 之前,使用该方式排序。首先根据条件取出排序字段和行指针信息,然后在排序区 sort buffer 中排序,如果sort buffer不够,则在临时表 temporary table 中存储排序结果。完成排序之后,再根据行指针回表读取记录,该操作可能会导致大量随机I/O操作。
2)一次扫描算法:一次性取出满足条件的所有字段,然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大,但是排序效率比两次扫描算法要高。
通过创建合适的索引,能够减少 Filesort 的出现,但是在某些情况下,条件限制不能让Filesort消失,那就需要加快 Filesort的排序操作
MySQL 通过比较系统变量 max_length_for_sort_data
的大小和Query语句取出的字段总大小, 来判定使用哪种排序算法,如果max_length_for_sort_data
更大,那么使用第二种优化之后的算法;否则使用第一种。
可以适当提高 sort_buffer_size
和 max_length_for_sort_data
系统变量,来增大排序区的大小,提高排序的效率。
四、优化group by
由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。既然会排序,就可以用到索引
于是我们有两种方式优化group by:
- order by null,手动禁止排序
- 给group by字段创建索引,优化排序
五、优化子查询
用更高效的连接(JOIN)替代子查询
示例 ,查找有角色的所有的用户信息 :
explain select * from t_user where id in (select user_id from user_role);
explain select * from t_user u , user_role ur where u.id = ur.user_id;
六、优化OR
建议使用 union 替换 or
这就把type字段从range优化为了const
七、优化分页查询
一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ,此时My服务器托管网SQL需要排序前2000010 记录,仅仅返回2000000 – 2000010 的记录,其他记录丢弃,查询排序的代价非常大 。
-- 耗时2.12s
select * from tb_item limit 2000000, 10;
优化思路一:在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容
-- 耗时1.51s
select * from tb_item t, (select id from tb_item order by id limit 2000000,10) a where t.id = a.id;
优化思路二:该方案适用于主键自增且连续的表,可以把limit查询转换成某个位置的查询
-- 耗时0.00s
select * from tb_item where id > 2000000 limit 10;
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net 机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net相关推荐: innerHTML与innerText(1)innerHTML对div标签的控制 d…