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 = '北京市';本文由传智教育博学谷 - 狂野架构师教研团队公布，转载请注明出处！
如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源