乐趣区

关于java:SQL优化的一些建议希望可以帮到和我一样被SQL折磨的你

jiezi

6.1 优化 insert 语句

当进行数据的 insert 操作的时候，能够思考采纳以下几种优化计划。

  • 如果须要同时对一张表插入很多行数据时，应该尽量应用多个值表的 insert 语句

    这种形式将大大的缩减客户端与数据库之间的连贯、敞开等耗费。

    使得效率比离开执行的单个 insert 语句快。

    示例，原始形式为：

    insert into xxx values(1,'Tom');
insert into xxx values(2,'Cat');
insert into xxx values(3,'Jerry');

    优化后的计划为：

    insert into xxx values(1,'Tom'),(2,'Cat')，(3,'Jerry');

  • 数据有序插入

    insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
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');

6.2 优化 order by 语句

6.2.1 环境筹备
CREATE TABLE `emp` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);
6.2.2 两种排序形式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序

tips

不是通过索引间接返回排序后果的排序都叫 FileSort 排序。

explain  select  *  from emp  order by  age  desc;
explain  select  *  from emp  order by  age  asc;

2). 第二种通过有序索引程序扫描间接返回有序数据，这种状况即为 using index，

不须要额定排序，操作效率高。

explain  select  id from emp  order by  age  asc;
explain  select  id,age  from emp  order by  age  asc;
explain  select  id,age,salary  from emp  order by  age  asc;

多字段排序

-- 多字段排序
explain  select  id,age,salary  from emp  order by  age, salary;
explain  select  id,age,salary  from emp  order by  age desc, salary desc;
 
explain  select  id,age,salary  from emp  order by  salary desc, age desc;
explain  select  id,age,salary  from emp  order by  age desc, salary asc ;

总结：

理解了 MySQL 的排序形式，优化指标就清晰了：

尽量减少额定的排序，通过索引间接返回有序数据。

where 条件和 Order by 应用雷同的索引，并且 Order By 的程序和索引程序雷同，并且 Order by 的字段都是升序，或者都是降序。否则必定须要额定的操作，这样就会呈现 FileSort。

6.2.3 Filesort 的优化原理

tips

理解即可

通过创立适合的索引，可能缩小 Filesort 的呈现，然而在某些状况下，条件限度不能让 Filesort 隐没，那就须要放慢 Filesort 的排序操作。对于 Filesort，MySQL 有两种排序算法：

1）两次扫描算法：MySQL4.1 之前，应用该形式排序。首先依据条件取出排序字段和行指针信息，而后在排序区 sort buffer 中排序，如果 sort buffer 不够，则在长期表 temporary table 中存储排序后果。实现排序之后，再依据行指针回表读取记录，该操作可能会导致大量随机 I / O 操作。

2）一次扫描算法：一次性取出满足条件的所有字段，而后在排序区 sort buffer 中排序后间接输入后果集。排序时内存开销较大，然而排序效率比两次扫描算法要高。

MySQL 通过比拟零碎变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小，来断定应用哪种排序算法，如果 max_length_for_sort_data 更大，那么应用第二种优化之后的算法；否则应用第一种。

能够适当进步 sort_buffer_size 和 max_length_for_sort_data 零碎变量，来增大排序区的大小，进步排序的效率。

show  variables like 'max_length_for_sort_data';
show  variables  like 'sort_buffer_size';

6.3 优化 group by 语句

tips

连续应用 6.2 优化 order by 语句中创立的表 emp

创立索引（筹备工作）：

create index idx_emp_age_salary on emp(age,salary);

因为 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 次要只是多了排序之后的分组操作。

当然，如果在分组的时候还应用了其余的一些聚合函数，那么还须要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也能够利用到索引。

如果查问蕴含 group by 然而用户想要防止排序后果的耗费，则能够执行 order by null 禁止排序。如下：

drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;

优化后

explain select age,count(*) from emp group by age order by null;

从下面的例子能够看出，第一个 SQL 语句须要进行 ”filesort”，而第二个 SQL 因为 order by null 不须要进行 “filesort”，而上文提过 Filesort 往往十分消耗工夫。

6.4 优化嵌套查问

tips

连续应用 4.3 应用 explain 剖析执行打算中创立的表

Mysql4.1 版本之后，开始反对 SQL 的子查问。这个技术能够应用 SELECT 语句来创立一个单列的查问后果，而后把这个后果作为过滤条件用在另一个查问中。应用子查问能够一次性的实现很多逻辑上须要多个步骤能力实现的 SQL 操作，同时也能够防止事务或者表锁死，并且写起来也很容易。然而，有些状况下，子查问是能够被更高效的连贯（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;

连贯 (Join) 查问之所以更有效率一些，是因为 MySQL 不须要在内存中创立长期表来实现这个逻辑上须要两个步骤的查问工作。

6.5 应用 SQL 提醒

tips：

连续应用 5.2.1 筹备环境中创立的表

SQL 提醒，是优化数据库的一个重要伎俩，简略来说，就是在 SQL 语句中退出一些人为的提醒来达到优化操作的目标。

6.5.1 USE INDEX

在查问语句中表名的前面，增加 use index 来提供心愿 MySQL 去参考的索引列表，就能够让 MySQL 不再思考其余可用的索引。

create index idx_seller_name on tb_seller(name);

explain select  *  from tb_seller  where name='小米科技'
explain select  *  from tb_seller use index(idx_seller_name)  where name='小米科技'
6.5.2 IGNORE INDEX

如果用户只是单纯的想让 MySQL 疏忽一个或者多个索引，则能够应用 ignore index 作为 hint。

explain select * from tb_seller ignore index(idx_seller_name_sta_addr) where name = '小米科技';
6.5.3 FORCE INDEX

为强制 MySQL 应用一个特定的索引，可在查问中应用 force index 作为 hint。

create index idx_seller_address on tb_seller(address);
explain select * from tb_seller force index(idx_seller_address) where address = '北京市';

本文由传智教育博学谷 – 狂野架构师教研团队公布，转载请注明出处！

如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源

退出移动版