insert 优化
- 批量插入(最多1000行)
- 手动控制事务
- 主键顺序插入(性能较高)
- 大批量插入数据(使用mysql提供的load指令,进行插入)
-
- 客户端连接服务时,加上参数--load-infile
-
-
- mysql --local-infile -u root -p
-
-
- 设置全局参数local_file为1,开启从本地加载文件导入数据的开关
-
-
- set global local_infile=1;
-
-
- 执行load指令将准备好的数据,加载到表结构中。
-
-
- load data local infile '/root/sql1.log' (文件位置) into table 'tb_user' (表名)fields terminated by ',' (每个字段之间,分割) lines terminated by '\n' (每一行 换行)
-
主键优化
- 数据组织方式,表数据都是根据主键顺序组织存放的,这种存放方式的表称为索引组织表
- 逻辑存储结构
- 页分裂
-
- 页可以为空,可以填一半,也可以全填。每页包含了2-n行数据(行数据过多,会造成溢出),根据主键排列
- 主键顺序插入时:
-
- 主键乱序插入(可能造成页分列)
-
-
- 两个页存满时,再来个两个页中间的数据,不会开启一个新的页,只会把第一个页中超出50%的数据,移动到新开辟的页中,在将数据放入。
-
- 改变页的顺序,把链表指针重新设置
-
-
- 页合并
-
- 删除记录时,实际上只是被标记为删除,并没有物理删除,当页中删除的记录达到Merge_threshold(默认页的50%),Innodb会开始寻找最靠近的页(前或后)看看是否将两个页合并优化空间使用
-
- 主键设计原则
-
- 满足业务需求下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择自增主键,无序插入会导致页分裂
- 尽量不要使用uuid做主健或者是其他自然主键,如身份证号,可能会造成页分裂
- 业务操作时,避免对主键的修改
order by优化
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序,一个降序,需要注意联合索引创建时的规则(ASC/DESC)
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
- Using fileSort :通过表的索引或者全表扫描,读取满足条件的数据行,然后再排序缓冲区sort buffer中,完成排序操作,所有不是通过索引直接返回结果的排序都叫filesort排序
- Using index:通过有序索引顺序扫描,直接返回有序数据,不需要额外排序,效率高
- 未走索引时
- 走索引
- 查询结果不能通过索引直接返回数据的排序
- 索引创建时,默认为升序,全部降序查询时,也会走索引
- 排序的联合索引也会满足最左前缀法则,查询数据时会从索引树上获取数据,排序时,索引不满足最左前缀排序,索引失效,就会再缓冲区中进行排序,
-
- 创建指定不同排序的索引
-
- create index idx_user_age_pho_add on t_user (age asc ,phone desc);
-
- 默认升序创建的索引结构
- 设置不同的索引排序(age asc,phone desc)
group by优化
- 分组操作时,可以通过索引提高效率
- 分组操作时,索引的使用也是满足最左前缀法则的
- 未走索引
- 使用索引
- 不满足最左前缀法则时
-
- using index是指使用了覆盖索引,即不需要通过回表查询就能获得select后面的数据,但是根据最左前缀法则,age是乱序的,需要建立临时表
- 满足最左前缀发则,并且走索引树
limit优化
- 当数据量过多时,再分页,mysql会遍历所有的数据并排序,获取指定的数据,其他的数据丢弃,导致越往后分页,效率越慢
- 子查询不支持limit,可以当作一张表
- 优化方法:使用子查询和覆盖索引的形式来进行优化
-
- 先分页查询指定的id值,
- 再根据id值,查数据,这时就会走聚集索引,找到响应的数据返回
-
count()优化
- 使用redis 添加删除数据的时候,加一或者减一
- count(id):遍历整张表,每一行的id取出来,返回给服务层,服务层拿到之后加1
- count(字段)
-
- 没有not null约束:会把每一行的字段值取出来,返回给服务层,服务层判断是都为null,不是加1
- 有not null约束:会把每一行的字段值取出来,返回给服务层,直接进行累加
- count(1):不取值,服务层对于返回的每一行,放一个数字1进去,直接进行累加
- count(*):不会把字段取出来,做了专门优化,服务层直接按行进行累加
- 效率:count(*)约等于 count(1)>count(主键id)>count(字段)
update优化
- 执行更新数据时,要根据索引来更新数据,否则行锁就会变为表锁
- 行锁:锁住当前行,另一个事务更新时,会阻塞。等更新完数据时,才会执行。