MySQL（十一）SQL优化详细讲解（插入数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）

SQL 优化1.插入数据1.1 普通插入采用批量插入一次插入的数据不建议超过1000条手动提交事务主键顺序插入1.2 大批量插入如果一次性需要插入大批量数据使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令插入# 客户端连# 客户端连接服务端时加上参数 --local-infile这一行在bash/cmd界面输入 mysql --local-infile -u root -p # 设置全局参数local_infile为1开启从本地加载文件导入数据的开关 set global local_infile 1; select local_infile; # 执行load指令将准备好的数据加载到表结构中 load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n;在Windows的cmd命令行中进行load导入数据时/root/sql1.log路径应使用绝对路径格式为C:\\path\\to\\file.log注意转义反斜杠或C:/path/to/file.log使用正斜杠而且需要根据不同文件的默认换行符使用合适的换行符替换\n2.主键优化数据组织方式在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表Index organized table, IOT2.1 页分裂页可以为空也可以填充一半也可以填充100%每个页包含了2-N行数据如果一行数据过大会行溢出根据主键排列2.1.1 主键顺序插入当主键是顺序递增时InnoDB会将新记录插入到当前页的末尾。如果当前页已满或剩余空间不足以插入这条记录则分配一个新页并维护一个双向指针建立两个页的联系然后继续插入依次类推直到插入完毕按照顺序插入1,2,3,4,5,6,7,8,9,10,11,12,13,142.1.2 主键乱序插入当主键无序时InnoDB需要找到合适的插入位置进行插入。如果目标页已满或剩余空间不足以插入这条记录会触发页分裂即从当前页中间处分裂成两个子页并维护双向指针建立页之间的联系然后继续插入依次类推直到插入完毕插入502.1.3 应插入位置不在页末尾的情况若插入点位于页中间且页内有空间InnoDB会执行以下操作页内空间充足移动页内现有记录为新记录腾出空间页内空间不足触发页分裂2.2 页合并当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前后看看是否可以将这两个页合并将要合并的页合并到被删除元素的页中以优化空间使用。MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或创建索引时指定2.3 主键设计原则满足业务需求的情况下尽量降低主键的长度插入数据时尽量选择顺序插入选择使用 AUTO_INCREMENT 自增主键尽量不要使用 UUID 做主键或者是其他的自然主键如身份证号业务操作时避免对主键的修改3.order by优化Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区 sort buffer 中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序Using index通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高如果order by字段全部使用升序排序或者降序排序则都会走索引但是如果一个字段升序排序另一个字段降序排序则不会走索引explain的extra信息显示的是Using index, Using filesort如果要优化掉Using filesort则需要另外再创建一个索引如create index idx_user_age_phone_ad on tb_user(age asc, phone desc);此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引对于语句explain select id,age,phone from tb_user order by phone,age;Using filesort和Using index都会出现原因是底层会先排序phone字段由于缺少age不满足最左前缀法则不会使用idx_user_age_phone索引出现Using filesort然后排序age字段满足最左前缀法则使用索引idx_user_age_phone出现Using index总结根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则尽量使用覆盖索引多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC如果不可避免出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size默认256k4.group by优化在分组操作时可以通过索引来提高效率分组操作时索引的使用也是满足最左前缀法则的如索引为idx_user_pro_age_stat则句式可以是select ... where profession软件工程 group by age这样也符合最左前缀法则5.limit优化常见的问题如limit 2000000, 10此时需要 MySQL 排序前2000010条记录但仅仅返回2000000 - 2000010的记录其他记录丢弃查询排序的代价非常大。疑问这里明明没有使用order by为什么要先排序前2000010条记录MySQL并不能保证数据插入顺序和读取顺序一致。因为必须存在一个聚集索引所以数据在插入时实际是按照主键顺序插入到B树索引底层结构就是一个树中所以实际存储是按照主键顺序存储的但是插入时主键不一定有序例如插入1、3、5、4、2但select * from table却是1、2、3、4、5因此select * from table limit 2000000, 10本质上是select * from table order by id limit 2000000, 10所以这里需要先排序再返回。优化方案一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化-- 此语句耗时很长 select * from tb_sku limit 9000000, 10; -- 通过覆盖索引加快速度直接通过主键索引进行排序及查询 select id from tb_sku order by id limit 9000000, 10; -- 下面的语句是错误的因为 MySQL 不支持 in 里面使用 limit -- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10); -- 通过连表查询即可实现第一句的效果并且能达到第二句的速度 select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id a.id;6.count优化MyISAM 引擎把一个表的总行数存在了磁盘上因此执行count(*)的时候会直接返回这个数效率很高前提是不适用whereInnoDB 在执行 count(*) 时需要把数据一行一行地从引擎里面读出来然后累计计数。优化方案自己计数如创建key-value表存储在内存或硬盘或者使用redis。6.1 count的几种用法如果count函数的参数count里面写的那个字段不是NULL字段值不为NULL累计值就加一最后返回累计值用法count(*)、count(主键)、count(字段)、count(1)count(主键)跟count(*)一样因为主键不能为空count(字段)只计算字段值不为NULL的行count(1)引擎会为每行添加一个1然后就count这个1返回结果也跟count(*)一样也可用其他非0数字代替1count(null)返回06.2 各种用法的性能count(主键)InnoDB引擎会遍历整张表把每行的主键id值都取出来返回给服务层服务层拿到主键后直接按行进行累加主键不可能为空count(字段)没有not null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加有not null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加count(1)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一层放一个数字 1 进去直接按行进行累加count(*)InnoDB 引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加按效率排序count(字段) count(主键) count(1) count(*)所以尽量使用count(*)7.update优化避免行锁升级为表锁InnoDB 的行锁是针对索引加的锁不是针对记录加的锁并且该索引不能失效否则会从行锁升级为表锁。如以下两条语句update student set no 123 where id 1;这句由于id有主键索引所以只会锁这一行update student set no 123 where name test;这句由于name没有索引所以会把整张表都锁住进行数据更新解决方法是给name字段添加索引