温馨提示PC端浏览体验感最佳~~SQL优化插入数据insert优化批量插入Insertintotb_testvalues(1,Tom),(2,Cat),(3,Jerry);手动提交事务starttransaction;insertintotb_testvalues(1,Tom),(2,Cat),(3,Jerry);insertintotb_testvalues(4,Tom).(5,Cat),(6,Jerry);insertintotb_testvalues(7,Tom),(8,Cat),(9,Jerry);commit;主键顺序插入主键乱序插入8、1、9、21、88、2、4、15、89、5、7、3主键顺序插入1、2、3、4、5、7、8、9、15、21、88、89大批量插入数据如果一次性需要插入大批量数据使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令进行插入。操作如下#客户端连接服务端时加上参数 --local-infilemysql--local-infile -u root -p#设置全局参数local_infile为1开启从本地加载文件导入数据的开关setgloballocal_infile1;#执行load指令将准备好的数据加载到表结构中loaddatalocalinfile/root/sql1.logintotable表名fieldsterminatedby,linesterminatedby\n;注意主键顺序插入性能高于乱序插入。主键优化数据组织方式在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表index organized table IOT)。页分裂页可以为空也可以填充一半也可以填充100%。每个页包含了2-N行数据如果一行数据多大会行溢出根据主键排列。主键顺序插入的情况主键乱序插入的情况假如当前数据页的情况如下图现在我们要插入一条id为50的新数据当前存在的两个Page都无法再存放下这条新数据那么就会发生页分裂页分裂后的结果如下图所示页分裂过程说明由于叶子节点是有序的所以id为50的这条新数据应该插入在47这条数据之后但是47这条数据所在的Page已经没有足够的空间存放50这条数据此时就会开启一个新的数据页然后找到47这条数据所在Page的50%的位置将超出50%位置的数据移动至新的数据页并将50这条数据也插入到这个新的数据页最后重新设置链表指针。页合并当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。假设当前数据页的情况如下图其中id为13、14、15、16的这4条数据都被标记为删除且这4条数据占用数据页的空间大于等于50%此时InnoDB发现第二个数据页及其相邻的第三个数据页的剩余空间都大于等于50%MERGE_THRESHOLD就会将第三个数据页的数据都迁移至第二个数据页这个过程就叫做页合并。页合并之后数据页的情况如下图若现在要新插入一条id为20的新数据就会直接将数据存放在第三个数据页如下图MERGE_THRESHOLD合并页的阈值默认为页的50%可以自己设置在创建表或者创建索引时指定。主键设计原则满足业务需求的情况下尽量降低主键的长度。说明二级索引的叶子节点存放主键且二级索引可以有多个主键长度越长占用的磁盘空间越大搜索时也会耗费大量的磁盘IO。插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。说明主键乱序插入容易出现页分裂现象。尽量不要使用UUID做主键或者是其他自然主键如身份证号。说明UUID、身份证号等自然主键无序且长度较长。业务操作时避免对主键的修改。说明修改主键需要同时修改索引结构代价较大。order by优化看Explain执行计划中的Extra列Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。可以通过类似以下SQL语句进行验证#没有创建索引时根据agephone进行排序Extra列的信息为Using filesortexplainselectid,age,phonefromtb_userorderbyage,phone;#创建索引createindexidx_user_age_phone_aaontb_user(age,phone);#创建索引后根据agephone进行升序排序Extra列的信息为Using indexexplainselectid,age,phonefromtb_userorderbyage,phone;#创建索引后根据agephone进行降序排序倒序扫描索引Extra列的信息为Backward index scan; Using indexexplainselectid,age,phonefromtb_userorderbyagedesc,phonedesc;#根据agephone进行排序但一个升序一个降序Extra列的信息为Using index; Using filesortexplainselectid,age,phonefromtb_userorderbyageasc,phonedesc;#创建索引createindexidx_user_age_phone_adontb_user(ageasc,phonedesc);#根据agephone进行排序但一个升序一个降序走idx_user_age_phone_ad索引Extra列的信息为Using indexexplainselectid,age,phonefromtb_userorderbyageasc,phonedesc;注意以上排序规则都有一个前提条件使用覆盖索引。若不使用覆盖索引则需要回表查询数据然后在数据缓冲区中对数据进行排序。优化总结根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。尽量使用覆盖索引。多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC / DESC。如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。说明可以通过 show variables like ‘sort_buffer_size’; 查看排序缓冲区的大小。若排序时排序缓冲区占满了就会在磁盘文件中进行排序性能低。group by优化执行类似以下SQL语句并观察Extra列的信息#删除掉目前不用的索引避免影响#执行分组操作根据profession字段分组Extra列出现了Using temporary性能较差explainselectprofession,count(*)fromtb_usergroupbyprofession;#创建索引Createindexidx_user_pro_age_staontb_user(profession,age,status);#执行分组操作根据profession字段分组Extra列没有出现Using temporary性能较好explainselectprofession,count(*)fromtb_usergroupbyprofession;#执行分组操作根据profession字段分组符合最左前缀法则Extra列没有出现Using temporary性能较好explainselectprofession,count(*)fromtb_usergroupbyprofession,age;#执行分组操作先根据profession字段过滤再根据age字段分组符合最左前缀法则Extra列没有出现Using temporary性能较好explainselectage,count(*)fromtb_userwhereprofession软件工程groupbyage;优化总结在分组操作时可以通过索引来提高效率。分组操作时索引的使用也是满足最左前缀法则的。limit优化一个常见又非常头疼的问题就是limit 2000000,10此时需要MySQL排序前2000010记录仅仅返回2000000-2000010的记录其他记录丢弃查询排序的代价非常大。优化思路一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。例如explainselect*fromtb_sku t,(selectidfromtb_skuorderbyidlimit2000000,10)awheret.ida.id;count优化explainselectcount(*)fromtb_user;count操作的效率主要取决于存储引擎的处理方式对于上面的这条SQL语句MylSAM引擎把一个表的总行数存在了磁盘上因此执行count(*)的时候会直接返回这个数效率很高前提是没有查询条件InnoDB引擎就麻烦了它执行count(*)的时候即使没有查询条件也需要把数据一行一行地从引擎里面读出来然后累积计数。优化思路自己计数。了解即可实现起来比较麻烦count的几种用法count() 是一个聚合函数对于返回的结果集一行行地判断如果count函数的参数不是NULL累计值就加1否则不加最后返回累计值。用法count(*)、count(主键)、count(字段)、count(1)count(主键)InnoDB引擎会遍历整张表把每一行的主键id值都取出来返回给服务层。服务层拿到主键后直接按行进行累加主键不可能为null)。count(字段)没有not null约束InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加。有not null约束InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(1)InnoDB引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”进去直接按行进行累加。count(*)InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。总结按照效率排序的话count(字段) count(主键id) count(1) ≈ count(*)所以尽量使用count( * )。效率主要看是否需要取值update优化InnoDB的行锁是针对索引加的锁不是针对记录加的锁并且该索引不能失效否则会从行锁升级为表锁我们在跟新数据时最好根据索引字段去更新且索引不能失效这样InnoDB只会对符合条件的数据加上行锁而不会对整张表加锁并发访问性能较好。例如以下SQL语句#假设id为主键主键索引name字段没有索引#id字段有索引此时只对id为1的这一行数据加锁。并发情况下即使当前事务还没有提交其他事务也可以操作其他行的数据updatestudentsetno2000100100whereid1;#name字段没有索引此时会对整张表上锁。并发情况下在当前事务提交之前其他事务都不可以对这张表的任何数据进行操作updatestudentsetno2000100105wherename韦一笑;#对name字段建立索引createindexidx_student_nameonstudent(name);#此时name字段有索引只会对name为“韦一笑”的行数据加锁。并发情况下即使当前事务还没有提交其他事务也可以操作其他行的数据updatestudentsetno2000100105wherename韦一笑;