SQL优化
Insert插入数据优化
-
批量插入
-
手动提交事务
-
主键顺序插入
-
大批量插入数据
-
如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入
-
#客户端连接服务端时,加上参数--local-infile mysql --local-infile -u root -p #设置全局参数local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile=1; #执行load指令将准备好的数据,加载到表结构中 load data infile '/root/sql.log' into table tb_user fields terminated by ',' lines terminated by '\n';
-
主键优化
- 数据组织方式
- 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
- 页分裂
- 乱序插入时可能出现页分裂现象
- 页可以为空,也可以填充一半,也可以填充100%
- 每个页包含了2~N行 数据(如果一行数据过大,会行溢出),根据主键排列
- 页合并
- 当删除一行数据时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除,并且它的空间变得允许被其他记录声明使用
- 当页中删除的记录达到阈值MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用
- 主键设计原则
- 满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键 ,如身份证号
- 业务操作时,避免对主键的修改
order by优化
using index #直接通过索引返回数据,性能高
using filesort #需要将返回的结果在排序缓冲区排序,性能相对较低
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
- 如果不可避免的出现
filesort
,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
group by优化
- 在分组操作时,可以通过索引来提高效率
- 分组操作时,索引的使用也是满足最左前缀法则的
limit优化
一个常见又非常头疼的问题比如limit 2000000,10
,此时需要MySQL排序前2000010条记录,仅仅返回2000000~2000010的记录,其他记录丢弃,查询排序的代价非常大
- 优化思路
- 一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
count优化
- MyISAM引擎把一个表的总行数存在看磁盘上,因此执行
count(*)
的时候会直接返回这个数,效率很高 - InnoDB引擎就麻烦了,它执行
count(*)
的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数 - 优化思路
- 自己计数
- count使用方法的性能比较
count(字段)
<count(主键id)
<count(1)
<count(*)
update优化
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
故尽量根据主键/索引字段进行数据更新