大前端

主题

Sql 优化 ​

插入数据 insert 优化 ​

优先批量插入 ​

  • 若要插入多条数据，优先选择批量插入，因为一条一条插入的时候，每一次执行 sql 语句都会与数据库建立连接，造成性能问题

  • 批量插入一次数据量建议不要超过 1000 条，一般 500~1000 条时比较合适的

  • 若一次需要插入几万条数据，此时可以将其拆分成多条批量插入的 sql 语句进行操作

采用手动提交事务 ​

例如：

sql
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

建议主键顺序插入 ​

主键乱序插入：8、5、4、1、2、9、3 主键顺序插入：1、2、3、4、5、8、9（推荐）

大批量插入数据（百万+） ​

如果一次性需要插入大批量数据，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。

bash
# 1.客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

# 2.设置全局参数 local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile =1;

# 3.执行oad指令将准备好的数据，加载到表结构中
# 此处每行数据之间用 '\n'进行分割，行内字段之间用逗号分割
load data local infile '/root/sql1.log' into table `tb_user' fields terminated by ',' lines terminated by '\n';

也需要遵循主键顺序插入，可以提高性能

主键优化 ​

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

页分裂 ​

页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据（如果一行数据多大，会行溢出），根据主键排列。

  • 主键乱序插入时候会带来页分裂， 通俗讲就是主键乱序 插入过程中，主键进行了重新排序，导致重新组装链表

页合并 ​

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的 50%),InnoDB:会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优 化空间使用。

注意： MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

  • 删除数据后，空位会被临近的后面的数据前移给补位，导致重新组装链表

主键设计原则 ​

  • 满足业务需求的情况下，尽量降低主键的长度。

  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

  • 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。（会乱序插入，且长度长，增加索引长度）

  • 业务操作时，避免对主键的修改。

order by 优化 ​

索引对排序的影响

  1. Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直 接返回排序结果的排序都叫 FileSort 排序。

  2. Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index,不需要额外排序，操作效率高。

  • 排序性能：Using index > Using filesort

  • 创建索引时，若没有指定顺序，默认时按照升序进行排列的，具体可以查看表的索引，里面有一个Collation字段，若某个索引的 collation 为 A就表示 asc 升序，为D 表示 desc 降序排列

  • 索引的最左前缀法则在排序中依然适用（排序字段的顺序）

  • 优化order by应该尽量向着 Using index优化

sql
/* 优化前 */
-- 没有创建索引时，根据age,phone进行排序
explain select id,age,phone from tb_user order by age,phone;

/* 优化 */
-- 创建索引
create index idx user age_phone aa on tb_user(age,phone);

/* 优化后 */
-- 创建索引后，根据age,phone进行升序排序
explain select id,age,phone from tb_user order by age,phone;

-- 创建索引后，根据age,phone进行降序排序
explain select id,age,phone from tb_user order by age desc,phone desc;


/* 优化前 */
-- 根据age,phonei进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc,phone desc;

/* 优化 */
-- 创建索引
create index idx_user_age_phone_ad on tb_user(age asg,phone desc);

/* 优化后 */
-- 根据age,phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc,phone desc;

order by 优化原则 ​

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

  • 尽量使用覆盖索引。

  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

  • 如果不可避免的出现 filesort,大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认 256k)。

group by 优化 ​

索引对分组优化的影响

分组操作若直接操作， 一般都会全表扫描或用到临时表，也就是执行计划的type 为 ALL, Extra字段为Using temporary(临时表)

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

例如：

建立的索引为 name_age 的联合索引，在查询的时候，若先根据 name 进行 where 过滤后再根据 age 进行 group by 分组查询，也是满足最左前缀法则的，页会走索引

limit 优化 ​

什么时候需要考虑对 limit 分页进行优化？

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

  • limit 分页在大数据的情况下，随着数据量的增大，效率越低，耗时越长

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

例如：

sql
-- 正确示例
explain select t.* from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a wheret t.id=a.id;

-- 下面的语法mysql可能不支持
select * from tb_sku where id in(select id from tb_sku order by id limit 2000000,10);

count 优化 ​

count 用于统计数量

sql
select count(*) from tb_user;
  • 对于MyISAM存储引擎把一个表的总行数存在了磁盘上，因此执行cout(*)的时候会直接返回这个数，效率很高(前提是没有 where 条件)；
  • 对于InnoDB引擎就麻烦了，即使没有 where 条件，它执行count(*)的时候，也需要把数据一行一行地从引擎里面读出来，然后累积计数。

因此优化思路：自己计数（可以用非关系型数据库自己维护一个 count 总数）

count 的几种用法

  • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL,累计值就加 1，否则不加，最后返回累计值。

  • 用法：count(*)、count(主键)、count(字段)、count(1)或者count(-1)或者count(0)

  • count(主键)：InoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null）。
  • count(字段)：
    • 没有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null,不为 null,计数累加。
    • 有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
  • count(1)：InoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
  • count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

TIP

按照效率排序的话，count(字段) < count(主键id) < count(1) ≈ count(*)，所以尽量使用count(*)

update 优化 ​

InnoDB 更新数据的时候，默认开启的是行级锁，也就是在更新数据时候，会将这行数据锁住，直到事务提交完成；

但是若 update 更新数据时根据一个没有索引的字段来更新的，则会将行锁升级为表锁，导致并发性能降低

例如：下面的 sql 时根据 name 进行更新的数据，但是 name 没有索引，所以在事务提交之前会锁住整张表

sql
-- name无索引，导致行锁升级为表锁
update user set age=21 where name='张三';

优化点：对 name 字段建立索引

TIP

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