MySQL中SQL优化

insert优化

  • 批量插入
    insert into table_name values(1,'a'),(2,'b'),(3,'c');
  • 手动提交事务
start transaction;
insert into table_name values(1,'a'),(2,'b'),(3,'c');
insert into table_name values(4,'aa'),(5,'bb'),(6,'cc');
insert into table_name values(7,'aaa'),(8,'bbb'),(9,'ccc');
commit;
  • 主键顺序插入
    主键乱序插入:8 1 9 21 99 87 11
    主键顺序插入:1 2 3 6 88 100
    建议顺序,顺序插入的性能高于乱序。
  • 大批量插入数据
    如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。
#客户端连接服务端时,加上参数 --local-infile
mysql --local-infile -u root -p
#查看是否全局参数是否为1
select @@local_infile;
#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据,加载到表结构中
#将/root/sql1.log的数据加入table_name中,字段用‘,’分割,行用回车分隔
load data local infile '/root/sql1.log' into 'table_name' fields terminated by ',' lines terminated by '\n';

/root/sql1.log:
在这里插入图片描述

主键优化

  • 数据的组织方式
    在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)。

  • 页分裂
    页可以为空,也可以填充一半,也可以填充100%。每个页包含了2~N行数据(如果一行数据多大,会行溢出),根据主键排列。

  • 页合并
    当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许其他记录声明使用。
    当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用,
    MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。

  • 主键设计原则
    1、满足业务需求的情况下,尽量降低主键的长度。
    2、插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
    3、尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
    4、业务操作时,避免对主键的修改。

order by 优化

1、Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2、Using index:通过有序顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。

  • 尽量少出现 using filesort。
    1、根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
    2、尽量使用覆盖索引。
    3、多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(asc/desc)。
    4、如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小using sort_buffer_size(默认256K).

group by 优化

  • 在分组操作时,可以通过索引来提高效率。
  • 分组操作时,索引的使用也是满足最左前缀法则的。

limit 优化

一个常见又非常头疼的问题就是limit 2000000,10
此时需要MySQL排序前2000010记录,仅仅返回2000000~2000010的记录,其他记录丢弃,查询排序的代价非常大。

优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。

explain select * from table1 as t,(select id from table1 order by id limit 2000000,10) as a where t.id = a.id;

conunt优化

explain select count(*) from table1;
MySQL引擎把一个表的总行数存在了磁盘上,因此执行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(1) > count(主键 id) > count(字段)

update优化

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。

简单说where后面的字段一定要经过索引。
不然update时会将行锁升级为表锁。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL进行SQL优化可以提高查询性能和系统的整体效率。以下是一些常见的MySQL SQL优化技巧: 1. 索引优化: - 确保表的列使用合适的索引,特别是在经常用于WHERE和JOIN条件的列上创建索引。 - 避免创建过多的索引,因为每个索引都需要占用存储空间,并且在插入、更新和删除操作时会增加额外的开销。 - 定期进行索引维护,如重新构建索引、优化索引碎片等。 2. 查询优化: - 使用合适的查询语句,尽量避免全表扫描。使用WHERE子句、JOIN和子查询等来限制结果集的大小。 - 避免在查询使用通配符%,因为它会导致全表扫描。如果需要模糊匹配,可以考虑使用全文索引或者其他优化方法。 - 选择合适的字段,只查询需要的列,避免不必要的数据传输和处理。 3. 表结构优化: - 根据业务需求合理设计表结构,避免冗余字段和表的关联过多。 - 合理划分和拆分大表,以减少查询时的数据量。 - 使用合适的数据类型,减小存储空间和提高查询效率。 4. 配置优化: - 调整MySQL的配置参数,如缓冲区大小、连接数、并发数等,以适应实际的负载和需求。 - 合理设置慢查询日志和查询缓存,以便定位慢查询和重复查询。 5. 避免不必要的操作: - 避免在查询使用SELECT *,只选择所需的列。 - 尽量减少数据库的写操作,如更新和删除操作,因为它们会增加额外的开销和锁定。 需要根据具体情况进行优化,并且通过分析慢查询日志和使用MySQL自带的性能工具来定位问题并进行优化。同时,注意不要过度优化,应根据实际需求和性能要求来平衡优化与开发的成本。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值