目录:
- 插入数据优化
- 主键优化
- order by优化
- group by优化
- limit优化
- count优化
- update优化
1、插入数据优化
每条数据都采用一条insert语句效率慢,可采用多条批量插入的策略,也可在插入数据时手动开启事务和提交事务,也能进一步提升插入效率。按照主键顺序插入相对于主键乱序插入效率更高。
当大批量数据需要插入时(一般大于一百万条),可以使用load指令进行插入:
#客户端连接服务端时,加上参数--local-infile
mysql --local-infile -u -root -p
#设置全局参数local-infile为1(默认为0),开启从本地加载文件导入数据的开关,
set global local_infile=1;
#执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table 'user' fileds terminated by ',' lines terminated by '\n';//第一个引号为待导入文件地址,第二个为数据分隔符,第三个为行分隔符
2、主键优化
在InnoDB存储引擎中,表数据都是根据逐渐顺序组织存放的,这种存储方式的表成为索引组织表(index organized table IOT)
页分裂:
页可以为空,也可以填充一半,也可以满。每个页包含了2~n行数据(如果一行数据过大,会行溢出),根据主键排序。
页合并:
当删除一行数据时,实际上记录并没有被物理删除,只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD(默认为页的百分之五十,可以在建表时自己设置),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并并且优化空间使用。
主键设计原则:
- 满足业务需求的情况下,尽量降低主键长度
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号
- 业务操作时,避免对主键的修改
3、order by排序优化
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的都叫Filesort排序。
- Using index:通过有索引顺序扫描直接返回有序数据,这种情况即为Using index,不需要额外排序,操作效率高。
- 正序和倒序的索引不一样,可以在创建索引的时候加入关键字,默认为正序索引如:
create index idx_name on user(age asc, phone desc);
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现Filesort,大数据量排序时,可以适当增加排序缓冲区的大小sort_buffer_size(默认为256k)。
4、group by优化
在分组操作时,可通过索引提高分组效率。
5、limit(页查询)优化
一个常见又非常头疼的问题是limit 2000000,10,此时需要MySQL排序前20000010记录,仅仅返回20000000-2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够较好的提升性能,可以通过覆盖索引加子查询的形式进行优化。
6、count优化
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高,但如果后面跟着where语句,同样也会很慢。
InnoDB在执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
优化思路:自己计数(加一个自增累加器,在插入数据删除数据时维护这个累加器)。
7、update 优化
在更新数据时一定要根据索引字段进行更新(where后面跟索引字段),不跟索引的话update语句将会为整张表上表锁,阻塞其他用户操作表的其他数据,影响并发性,有索引时为行锁,只锁定修改行。
InnoDB的行锁是针对于索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
end